…information_schema.tables t join information_schema.statistics i using (table_schema, table_name) join information_schema.innodb_index_stats s using (table_schema, table_name, index_name) where t.table_schema…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
…information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; +——–+——————–+——-+——-+——-+————+———+ | tables | table_schema | rows…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
…tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema…FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows |…
Post: Getting History of Table Sizes in MySQL
December 8, 2010 By 13 Comments
… * * * mysql -u root -e “INSERT INTO stats.tables SELECT DATE(NOW()),TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES” Though if you’re looking to keep it completely inside MySQL…
Post: Using INFORMATION_SCHEMA instead of shell scripting
November 24, 2008 By 7 Comments
INFORMATION_SCHEMA, in particular by favorite TABLES table is not only helpful to understand tables you have on the system, but…mysql> select concat(“mysqldump “,table_schema,” “,table_name, ” >> “,table_schema,”.sql”) from tables where engine=’innodb’ into outfile ‘/tmp/dump.sh’; Query OK, 328 rows …
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…_union=on,inde | +—————————————————+——————————————+——————————————+ 56 rows in set (0….tables in this system. innodb_stats_on_metadata is disabled by default in MySQL 5.6 Welcome to much faster information_schema…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
… an example: Enable the log: general_log_file = /var/log/mysql/mysql.log general_log = 1 User correctly authenticated: 121227 8:31… sent, rows accessed, commands executed and so on. Very valuable information. It is important to mention that these tables are stored in INFORMATION_SCHEMA and…
Post: MySQL 5.6: Improvements in the Nutshell
January 27, 2013 By 16 Comments
… outline of improvements available in MySQL 5.6 which I thought …Row based Replication – Replication Utilities for Failover and Admin Transparency – Many new INFORMATION_SCHEMA Tables – - INNODB_METRICS – - Meta Data Information Tables – - Buffer Pool Information Tables – Improved PERFORMANCE_SCHEMA…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list; 997 rows…INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id ), 2) AS Pct_R, COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS “R/Call” FROM INFORMATION_SCHEMA… | removing tmp table…
Post: Announcing Percona Server 5.1.65-14.0
September 4, 2012 By Leave a Comment
…as a result, lost data due to some rows being updated rather than inserted) with the… has been created with MySQL 5.0 or older, importing that table could crash Percona Server…FROM INNODB_TABLE_STATS or INNODB_INDEX_STATS. Bug fixed #896439 (Stewart Smith). Removed the INFORMATION_SCHEMA table INNODB_…
