…_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 of tables that current user has…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… others) using the User Statistics patch. By enabling ‘userstat_running’, we start to get information in the INFORMATION_SCHEMA.INDEX_STATISTICS table. This data collection… indexes (if our original index statistics are good) are candidates to be dropped: create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table…
Post: How expensive is USER_STATISTICS?
June 2, 2012 By 14 Comments
… so-called USER_STATISTICS features of Percona Server in a heavy-use production server with many tens of thousands of tables. If you… feature, it creates some new INFORMATION_SCHEMA tables that add counters for activity on users, hosts, tables, indexes, and more. This is very helpful…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS) When 1 / 16th of the table… of SHOW TABLE STATUS FROM schema taking away a couple of seconds on execution time. # Time: 110923 1:48:17 # User@Host…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
…_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA AND s.TABLE_NAME=iz.TABLE_NAME AND s.INDEX_NAME=iz.INDEX_NAME) WHERE iz.TABLE_SCHEMA… first. Note this query requres MySQL with Percona Extensions and user statistics running.
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 … ignore –ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name –ignore-tables Comma-separated list of tables to ignore… hostname localhost ignore-databases No default value ignore-indexes No default value ignore-tables No default value options-file ~/.my.cnf password…
Post: Percona-XtraDB-9.1: released and new coming features
March 17, 2010 By 14 Comments
… long waited features from 5.0: extended slow.log USER/TABLE/INDEX/CLIENT_STATISTICS + THREAD_STATISTICS ( coming in release-10) Extended slow.log is now… on fast storage systems SHOW TEMPORARY TABLES, patch from Venu Anuganti INFORMATION_SCHEMA.SYS_TABLES and SYS_INDEXES to show internal InnoDB data…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… per-user and per-table accounting to the database, accessible with a new set of commands 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…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
…table, every column, and every index in that table. Once you get the output, you just DROP the table… hundred user queries in SHOW PROCESSLIST that were stuck in the ‘Opening Tables‘ state, …table, it calls dict_update_statistics and appears to force table statistics to be regenerated for each table…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as …right plan considering the statistics MySQL had available. It is very easy to disable index merge if you… values it gets from the single table. I have reverted table to have i1 and i2 as …
