… be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The… statistics we run: xtrabackup –stats –tables=art.link* –datadir=/mnt/data/mysql/ which will show something like this: table: art/link_out104, index: PRIMARY, space id: 12, root page 3 estimated statistics in…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
Post: Google's user_statistics V2 port and changes
September 12, 2008 By 9 Comments
… EMPTY_QUERIES: 70748 and INDEX/TABLE statistics: mysql> select * from information_schema.index_STATISTICS limit 10; +————–+————+—————–+———–+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +————–+————+—————–+———–+ | art119…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… 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… 10 rows in set (0.00 sec) mysql> SHOW INDEX_STATISTICS LIKE ‘hosting2%’; +——————————————–+———–+ | Index | Rows_read | +——————————————–+———–+ | hosting2.wp_options.option_name…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index… the tables’ statistics. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from MySQL Community 5…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is… there is no correlation statistics available. mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index(i2) where i1=50… it is right plan considering the statistics MySQL had available. It is very easy to disable index merge if you do not want…
Post: MySQL Binaries Percona build10
December 11, 2008 By 12 Comments
…/mysql/slow_query.log | for compatibility with MySQL 5.1 and | userstat_running | OFF | to control gathering of user statistics. With…_scan_* | | userstatsv2.patch | SHOW USER/TABLE/INDEX statistics | V2 | Google | GPL | Added INFORMATION_SCHEMA.*_STATISTICS | | show_patches.patch | SHOW PATCHES | 1….
Post: check-unused-keys: A tool to interact with INDEX_STATISTICS
June 26, 2009 By 10 Comments
With the growing adoption of Google’s User Statistics Patch**, the need for supporting scripts has become clear. To …> The target MySQL server host –ignore-databases Comma-separated list of databases to ignore –ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name –ignore-tables Comma-separated…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… index is woeful, but provided that the application is always only sending query 1 to MySQL it’s actually a pretty good index… In this case, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really… helpful patch in 5.0-percona called INDEX_STATISTICS that can then show you which indexes were touched and which were not. If…
Post: Percona Server 5.1.55-12.6
March 7, 2011 By 5 Comments
… – The ROWS_READ columns of TABLE_STATISTICS and INDEX_STATISTICS were not properly updated when a query involved index lookups on an InnoDB table…, on servers with huge buffer pools, adding or removing an index even on an empty InnoDB table could take a long…
