… 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 of…
Post: Userstats patches with information schema support
August 4, 2008 By 16 Comments
… added information schema support to Google’s userstats patch. There are three information schema tables added: user_statistics, table_statistics, index_statistics. One can now use select * from information_schema.user_statistics along with show user_statistics. Links: Patch for…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
… – if you need to add/remove indexes and do other schema changes you probably would be looking at some methods involving… and run following query: mysql> select * from information_schema.user_statistics where user=”#mysql_system#” \G *************************** 1. row *************************** USER: #mysql_system# TOTAL_CONNECTIONS: 1 CONCURRENT_CONNECTIONS…
Post: Three ways to know when a MySQL slave is about to start lagging
October 8, 2008 By 8 Comments
… server, which gives you a USER_STATISTICS table. mysql> SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS WHERE USER=’#mysql_system#’\G *************************** 1. row *************************** USER: #mysql_system# TOTAL_CONNECTIONS: 1…
Post: New patches, new builds
September 5, 2008 By 24 Comments
… | Yasufumi Kinoshita | BSD | | | userstats.patch | SHOW USER/TABLE/INDEX statistics | 1.0 | Google | GPL | Added INFORMATION_SCHEMA.*_STATISTICS | | show_patches.patch | SHOW PATCHES | 1.0… Next, we added tables USER_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS to INFORMATION_SCHEMA mysql> select * from information_schema.table_statistics; +————————-+———–+————–+———————-+ | TABLE_NAME | ROWS_READ…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA AND… first. Note this query requres MySQL with Percona Extensions and user statistics running.
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS) When 1 / 16th of the table or 2Billion rows…. # Time: 110923 1:48:17 # User@Host: user1[user1] @ [172.20.6.1] # Thread_id: 10140441 Schema: db1 Last_errno: 0 Killed…
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 … script to provide a nicer interface than directly querying the INFORMATION_SCHEMA database. check-unused-keys can be invoked and used as… Display summary information -u, –username=
Post: MySQL Binaries Percona build10
December 11, 2008 By 12 Comments
… 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…_io_pattern.patch | Information schema table of InnoDB IO counts for each datafile pages | 1.0 | Percona | GPL | INFORMATION_SCHEMA.INNODB_IO_PATTERN | | microsec…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
….) limiting command availability to SUPER users only or restricting regular users to see their own statistics only information_schema support to allow SELECT query transformations, joins and better filtering for the statistics Also…
