Mysql – How identify tables with millions of entries

linuxmonitoringMySQLphpmyadmin

On a debian server with apache and mysql, how can I find out if any one table gets spammed?

I have lots of different blogs, wordpress, wikis,… on my server of different customers.

It seems like some PHP aplications are not protected against spamming, so some tables get really big and slow down the whole server.

I would need a script, that monitors all tables. Or is there a simple tool I could install to get a report if something weird happens?

Best Answer

Since the tables you want to check are MyISAM, you can use just one query. For any given table or tables using MyISAM, here is that query:

SET @SecondsAgo = 60;
SELECT
    table_schema,table_name,
    data_length MYDSize,index_length MYISize,
    table_rows,update_time
FROM information_schema.tables
WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','performance_schema','mysql')
    AND update_time > (NOW() - INTERVAL @SecondsAgo SECOND)
ORDER BY update_time DESC;

For each MyISAM table, you will get the following:

  • table name and database
  • .MYD size
  • .MYI size
  • number of rows in the table
  • last time the table was updated

You can set @SecondsAgo to choose how granular to scan for changes.

If many tables or the same set of tables appear from this query, you will see which tables are experiencing the most frequent writes and the most growth.

I will leave it to your to determine the weirdness from the results.

Related Question