… such as SHOW TABLE_STATISTICS, SHOW INDEX_STATISTICS and SHOW USER_STATISTICS. The first two can interest anyone to periodically check what data or which index are the… 10 rows in set (0.00 sec) mysql> SHOW INDEX_STATISTICS LIKE ‘hosting2%’; +——————————————–+———–+ | Index | Rows_read | +——————————————–+———–+ | hosting2.wp_options.option_name | 287…
Post: Google's user_statistics V2 port and changes
September 12, 2008 By 9 Comments
… shows statistics per client’s hostname, not per user FLUSH USER_ and CLIENT_STATISTICS commands Our port includes: INFORMATION_SCHEMA tables – USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS Access protection – USER_STATISTICS, CLIENT_STATISTICS available only for user with SUPER or PROCESS privileges. INDEX_STATISTICS, TABLE_STATISTICS shows only information…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show… commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS) When 1…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The…/ which will show something like this: table: art/link_out104, index: PRIMARY, space id: 12, root page 3 estimated statistics in dictionary: key…
Post: Why InnoDB index cardinality varies strangely
September 28, 2009 By 5 Comments
… time I started to look into something interesting with the index cardinality statistics reported by InnoDB tables. The cardinality varies because it… file: baron@kanga:~$ while true; do mysql sakila -N -e ‘show index from film’ | head -n 2 | tail -n 1 | awk ‘{print… the others; it looks like the random choice of which statistic to use is not evenly distributed. I mentioned this to…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… | Using where | +—-+————-+———+——-+—————+————+———+——+——+————-+ 1 row in set (0.00 sec) The index on query 3 had high cardinality but should not be… a helpful patch in 5.0-percona called INDEX_STATISTICS that can then show you which indexes were touched and which were not. If… parse and EXPLAIN all results, then subtract the indexes that were mentioned from all indexes known. There’s an old tool…
Post: New patches, new builds
September 5, 2008 By 24 Comments
… Kinoshita | BSD | | | userstats.patch | SHOW USER/TABLE/INDEX statistics | 1.0 | Google | GPL | Added INFORMATION_SCHEMA.*_STATISTICS | | show_patches.patch | SHOW PATCHES | 1.0 | Jeremy Cole | N/A | | +————————————————–+——————————————————–+———+—————————-+———+—————————————+ Next, we added tables USER_STATISTICS, INDEX_STATISTICS…
Post: MySQL Binaries Percona build10
December 11, 2008 By 12 Comments
… 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… locked records in SHOW INNODB STATUS | 1.0 | Baron Schwartz | GPL | Bug #29126 fix | | microslow_innodb.patch | Extended statistics in slow.log…
Post: InnoDB TABLE/INDEX stats
March 20, 2010 By 5 Comments
… available in XtraDB: It is INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables ( taken from InnoDB data dictionary… you can estimate how big is index is, and also what is statistics per index (or at least what InnoDB thinks about statistics in index)
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about transaction… index is hash index Innodb builds for some pages to speed up row lookup replacing btree search with hash search. This section shows… hash index. You can also see number of hash index lookups and number of non-hash index lookups which is indication of hash index…
