Cheat Sheets MySQL


System Info

Top processes

SELECT id, command, substring(info,1,200), time
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE db='db_name' and user='user_name'
ORDER by time desc LIMIT 5; 

Log CPU usage for mysqld process, every 2 seconds

>log; while :; do date >> /path/to/log/file; top -b -U mysql -n 1 | tail -n 3 >> /path/to/log/file; sleep 2; done 

Database size

SELECT table_schema "DB NAME",
sum(data_length + index_length)/1024/1024/1024 "DB SIZE (GB)"
FROM information_schema.TABLES
GROUP BY table_schema; 
SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),'Mb') total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),'Mb') data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),'Mb') data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/
((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
COUNT(table_name) total_tables
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
WHERE s.schema_name <> 'information_schema' and s.schema_name <> 'mysql'
GROUP BY s.schema_name ORDER BY pct_used DESC; 

Table size

SELECT s.schema_name, t.table_name,
CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024/1024, 2), 0.00), " GB") data_size,
CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024, 2), 0.00), " MB") index_size,
t.ENGINE ENGINE, t.table_rows table_rows, t.row_format row_format,
date(t.update_time) FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema
WHERE s.schema_name NOT IN ("mysql", "information_schema")
GROUP BY s.schema_name, t.table_name, row_format,
ENGINE ORDER BY TABLE_ROWS DESC , data_size DESC , index_size DESC  

Tables with more than 20% fragmentation

SHOW TABLE STATUS WHERE Data_free/Data_length > 0.2 AND Data_free > 102400; 

Config

How big should the innodb buffer pool be?

SELECT SUM(data_length + index_length) / power(1024,1) "Size (KB)"
FROM information_schema.tables
WHERE ENGINE = "InnoDB";