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;
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";