…mysql> pager grep sequence PAGER set to ‘grep sequence’ mysql> show engine innodb status\Gselect sleep(60);show engine innodb… mysql> select count(*) from sakila; ERROR 1146 (42S02): Table ‘sakila.sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL This customer was running… the INFORMATION_SCHEMA.INNODB_SYS_INDEXES table, which exists in Percona Server 5.1, but doesn’t appear in MySQL until 5… Percona Server or MysqL 5.6. Another person (I’m looking at you, Baron) was adverse to trusting INNODB_SYS_INDEXES from…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…innodb table which is same but uses Innodb format: mysql> insert into antest_innodb…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the …innodb_change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… `j` (`j`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show table status like “sample” \G; *************************** 1. row *************************** Name: sample Engine: InnoDB Version: 10 Row…) mysql> select count(i) from sample lock in share mode; +———-+ | count(i) | +———-+ | 1638400 | +———-+ 1 row in set (7.02 sec) Looking at SHOW INNODB…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.69 sec) mysql> explain select count…
Post: Make your file system error resilient
August 24, 2011 By 8 Comments
… Errors behavior: Continue Filesystem OS type: Linux Inode count: 226918400 Block count: 3630694400 Reserved block count: 0 Free blocks: 3616208434 Free inodes: 226918374…” either as Innodb is very sensitive to corruptions and if any of file system errors are reported back to MySQL/Innodb it will…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…InnoDB DEFAULT CHARSET=latin1 mysql> show table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine: InnoDB…Aware comparison operator: mysql> select count(*) from cbool where c1NULL; +———-+ | count(*) | +———-+ | 1048576 | +———-+ …
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… table instances (note – same table gets counted twice) in use but zero tables are locked. Innodb does not need any row locks…” hence eliminating conflicts on MySQL level table locks for most queries. Summary: MySQL Table level locks and Innodb Table Level locks are two… Innodb chances are it is row level locks or auto increment table level lock (mostly with MySQL 5.0 and older MySQL versions). MySQL…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
….08 sec) mysql> set global innodb_stats_on_metadata=0; Query OK, 0 rows affected (0.00 sec) mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count…
