…over in both directions. Aside from MySQL Cluster, which is more special-purpose…query: select table_schema, table_name, page_type, count(*) from information_schema.innodb_…insert buffer. As Peter explained in his recent post on the insert buffer, the other thing the SELECTs do is keep the insert…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… different order of execution. The MySQL manual actually says this, but…inserts are converted to normal inserts for CREATE … SELECT or INSERT … SELECT statements. If you use mysqladmin debug, you’ll see an ordinary SELECT… SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB …
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… table with the new structure is created and then INSERT INTO new_table SELECT * FROM old_table is done and then RENAME… null auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) session1 > insert into t1 values(null); Query… int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE would just fail immediately…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…is collation specific. Q: ORDER By optimization issues: select * from table where A=xxx and B …down your inserts and make primary key significantly fragmented. I also would note there are some MySQL …”order by desc” indicates a double linked list. Innodb has double linked list – each leaf page …
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
…recovery process on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers. # innobackupex –defaults-file=/etc/…the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql> SELECT * FROM …
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…- choosing innodb_buffer_pool_size Q36: There have been several MySQL bugs opened about multi-core scalability (concurrent queries, autoincrement, concurrent inserts, …or not showed up in “show innodb statusâ€. select * from table where id=5 show innodb status: Hash table size 10624987, …
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
After playing yesterday a bit with INSERT … SELECT I decided to check is Innodb locks are relly as efficient in terms of low…, however we can see locking overhead is not that large: mysql> select count(i) from sample; +———-+ | count(i) | +———-+ | 1638400 | +———-+ 1 row in…
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
…is how to get Percona Server for MySQL up and running: Insert the SD card into a slot on… | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…mysql> show processlist; …global read lock | insert into C values …SELECT queries to let backup to proceed, but resolving server gridlock one way or another. If you’re just using Innodb…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…`parent` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO parent(parent_id) VALUES(1,”"); INSERT INTO child(parent_id)… sec) mysql> select * from child; +———–+——+ | parent_id | v | +———–+——+ | 1 | NULL | +———–+——+ 1 row in set (0.00 sec) mysql> update…
