… be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The… feature to address this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a… statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 5 Comments
… INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables ( taken from InnoDB data dictionary). INNODB_TABLE_STATS is | table_name | table name in InnoDB… judge whether statistics recalculation should be done | INNODB_INDEX_STATS is | table_name | table name in InnoDB internal style (‘database/table’) | | index_name | index name | | fields…
Post: Why InnoDB index cardinality varies strangely
September 28, 2009 By 5 Comments
… I started to look into something interesting with the index cardinality statistics reported by InnoDB tables. The cardinality varies because it’s derived… 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…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… Innodb really did have all the indexes or not. I collected the data just fine, and I found that indeed the indexes that Innodb… dict_update_statistics is called twice for each table, at least in the latest Percona Server 5.1 in: storage/innodb_plugin… again in: storage/innodb_plugin/dict/dict0dict.c: line 4883 So, the io-wait spike is explained by table statistics being regenerated…
Post: New patches, new builds
September 5, 2008 By 24 Comments
…-threads.patch | InnoDB patch to control count of IO threads | 1.0 | Yasufumi Kinoshita | BSD | | | userstats.patch | SHOW USER/TABLE/INDEX statistics | 1… tables USER_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS to INFORMATION_SCHEMA mysql> select * from information_schema.table_statistics; +————————-+———–+————–+———————-+ | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_INDEXES…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…. Full_join means any of the joins didn’t use indexes. If a temporary table is required for query execution Tmp… temporary files. INNODB USAGE The final part are the InnoDB usage statistics. MySQL currently allows you to see many per-session statistics for operations…: No # Filesort: No Disk_filesort: No Merge_passes: 0 # No InnoDB statistics available for this query SELECT COUNT(1) FROM t2; Important…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
…those processes in ‘statistics‘ status. Why is that happening? Look at SHOW INNODB STATUS: ===================================== 090128 8:29:03 INNODB MONITOR OUTPUT … no 30034, OS thread id 1159956816 starting index read, thread declared inside InnoDB 500 mysql tables in use 7, …
Post: MySQL Binaries Percona build10
December 11, 2008 By 12 Comments
… InnoDB scan | 1.0 | Percona | GPL | The names are Innodb_scan_* | | userstatsv2.patch | SHOW USER/TABLE/INDEX statistics | V2 | Google | GPL | Added INFORMATION_SCHEMA.*_STATISTICS | | show_patches.patch | SHOW PATCHES | 1.0 | Jeremy Cole | N/A | | | innodb_io…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…: 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… control it (tough it is possible in XtraDB / InnoDB-plugin with FAST INDEX creation) so be careful with queries scan many records… query affected by fragmentation you can use Innodb_scan_pages_contiguous ; Innodb_scan_pages_jumpy statistics in 5.0-percona releases (coming to…
