I was recently experiencing a considerable slow access issue to my Drupal webapp which was working smoothly so far.
CPU usage was very high as well as the MySQL server process but nothing unusual in the show processlist.
After digging around trying to determine what was the root cause, I ran the following SQL query just to know what the main tables in terms of disk size usage were:
SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10;
It shows the top10 tables in terms of disk space used:
Out of that table I quickly realised the cache_form table was using an unexpected high size which eventually was slowing down the access to the webapp.
Truncating that table solved the issue.