June 20, 2013

Post: The perils of InnoDB with Debian and startup scripts

January 28, 2009 By 13 Comments

…tables, but it is far worse on InnoDB. There are a few reasons why this happens — access to open an InnoDB table…user | | NULL | 6356 | user | statistics | select … | 6357 | user | statistics | select … | 6358 | user | Sending data | select … | 6359 | user | statistics | select … | 6360 | …

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush

February 27, 2013 By 3 Comments

… some engines like InnoDB and they are based in statistics calculations therefore sometimes some tune is needed. In InnoDB these statistics are calculated automatically…/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/ There are some variables to tune how that statistics are calculated but…

Filed Under: MySQL Tagged With: , , , ,

Post: InnoDB: look after fragmentation

November 5, 2009 By 12 Comments

…=InnoDB Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from…: 38530 InnoDB_IO_r_bytes: 631275520 InnoDB_IO_r_wait: 0.204893 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages… query affected by fragmentation you can use Innodb_scan_pages_contiguous ; Innodb_scan_pages_jumpy statistics in 5.0-percona releases (coming to…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine

May 24, 2008 By Leave a Comment

…INNODB%’; +—————————————-+ | Tables_in_INFORMATION_SCHEMA (INNODB%) | +—————————————-+ | INNODB_CMP | | INNODB_CMP_RESET | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | | INNODB_LOCK_WAITS | | INNODB_LOCKS | | INNODB…

Filed Under: Insight for DBAs Tagged With: ,

Post: Learning about MySQL Table Fragmentation

May 1, 2008 By 20 Comments

… even if table is not logically fragmented and Innodb is reading data in large blocks – this can happen in case Innodb table file is itself fragmented. To check if this is the case I usually do “cat table.ibd > /dev….S It would be cool to get Innodb objects (data and Index) fragmentation statistics which actually should not be that hard to…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…statistics about the FT indexes on that table by querying the various I_S.INNODB_FT_* tables. In particular, the INNODB_FT_CONFIG table is supposed to “display metadata about the FULLTEXT index and associated processing for an InnoDB table…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

…TABLE for Innodb couple of more times to see how values change: mysql> analyze table antest_innodb; +——————–+———+———-+———-+ | Table | Op | Msg_type | Msg_text | +——————–+———+———-+———-+ | test.antest_innodb…handling the statistics comes …

Filed Under: Insight for DBAs Tagged With: ,

Post: Why InnoDB index cardinality varies strangely

September 28, 2009 By 5 Comments

… to look into something interesting with the index cardinality statistics reported by InnoDB tables. The cardinality varies because it’s derived from estimates… the others; it looks like the random choice of which statistic to use is not evenly distributed. I mentioned this to… that bug seemed to have fixed the skew in the statistics. Case solved, and all I had to do was wait…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: New patches, new builds

September 5, 2008 By 24 Comments

…tables USER_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS to INFORMATION_SCHEMA mysql> select * from information_schema.table_statistics; +————————-+———–+————–+———————-+ | TABLE…

Filed Under: Percona Software Tagged With:

Post: The new cool MySQL patch has landed! Check your queries performance!

October 31, 2007 By 16 Comments

… temporary files. INNODB USAGE The final part are the InnoDB usage statistics. MySQL currently allows you to see many per-session statistics for operations…_join: No Tmp_table: No Disk_tmp_table: No # Filesort: No Disk_filesort: No Merge_passes: 0 # No InnoDB statistics available for this query…

Filed Under: Percona Software Tagged With: , ,
« Older Posts
Newer Posts »