Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Are the tables using MyISAM, InnoDB or both ??? –  RolandoMySQLDBA Aug 2 '13 at 19:38
    
Nearly all tables are MyISAM. I think it would suffice to check those –  rubo77 Aug 2 '13 at 19:56
add comment

1 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.

share|improve this answer
    
thanks! that works (only a typo: one "AND" too much). I will try to make cron-script out of that –  rubo77 Aug 2 '13 at 22:20
    
I fixed it, thanks !!! –  RolandoMySQLDBA Aug 2 '13 at 22:21
    
I would add SET @MinTableRows = 10000; and AND table_rows>@MinTableRows to show only relevant tables –  rubo77 Aug 4 '13 at 2:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.