… to finish this query: “SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE …” After setting innodb_stats_on_metadata = 0, that… use the MySQL INFORMATION_SCHEMA ) allowing it to momentarily set innodb_stats_on_metadata=0 while running (or just before running those…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 5 Comments
…_SCHEMA tables available in XtraDB: It is 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 internal style (‘database/table‘) | | rows | estimated…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… table (in any database you choose), like this: CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB; This, primitively, tells Innodb that it should start the Table…, which is required for opening tables in Innodb. It would be better for the Table Monitor if: Table stats weren’t regenerated unless it was…
Post: COUNT(*) for Innodb Tables
December 1, 2006 By 29 Comments
… by simple singe counter for Innodb tables as different transactions may see different number of rows in the table. If you have query… for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on… tools which may show it in table statistics, it may also be used in application stats to show something like “We have…
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… column. These stats look much closer to what we get for Innodb table with same data though we can see Innodb stats are a bit…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
… table_id = innodb_sys_tables.id JOIN innodb_index_stats ON innodb_index_stats.table_name = innodb_sys_tables.name AND innodb_sys_indexes.name = innodb_index_stats.index_name AND innodb_index_stats.table_schema = innodb_sys_tables…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… statistics we run: xtrabackup –stats –tables=art.link* –datadir=/mnt/data/mysql/ which will show something like this: table: art/link_out104, index…% It says that PRIMARY key (which is the table by itself, as InnoDB is clustering data by primary key) takes 497839 pages… feature, InnoDB creates indexes by sort, so page fill factor should be quite good. To check that, there is xtrabackup –stats for…
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… | Innodb_scan_pages_contiguous | 37864 | | Innodb_scan_pages_jumpy | 574 | +——————————+——-+ 2 rows in set (0.00 sec) and extended stats…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table… to update all the tables‘ statistics. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from MySQL Community 5.1.17 a similar variable exist called innodb_stats_on_metadata, for…
Post: What is the longest part of Innodb Recovery Process ?
December 20, 2007 By 2 Comments
… and above the longest part of recovery after crash for Innodb tables could be UNDO stage – it was happening in foreground and… normal restarts as well – “Opening Tables“. As Innodb has to recompute the stats first time it opens the table this can take significant amount… table can be opened at the time as of MySQL 5.0 It would be great if Innodb would finally optionally store stats…
