How to detect inactive tables?

Question

I started working on a project whose former developer ragequited, and it left a complete mess both in the code and database. The server is using MySQL 5.5

There are a lot of tables created for backup or test purposes. For example, from all this tables, only two are really being used.

Is there any way to check which tables weren't used in the last XX hours, to be able to safely delete them? Can I set a log for that, besides from the general log, which is really big and fills the available space too quickly?

I cant trust names as in some cases, the table being used is the one that ends with _test or _bk.

Answer 1

Unfortunately, using the update_time column from INFORMATION_SCHEMA.TABLES only works for MyISAM tables. It does not work for InnoDB.

The most effective way to get the last time a table was written is to rely on the OS. You must check the timestamp of the .ibd or .MYD files within a database folder.

I wrote earlier posts about how to do this:

• Dec 21, 2011 : Fastest way to check if InnoDB table has changed
• Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?
• Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?
• Sep 25, 2014 : want to find out which databases are used in last 30 days or not
• Jan 16, 2015 : How can I determine which Innodb table is being written?

Answer 2

I would ensure that MySQL query logging is enabled and then duplicate the query log. In the duplicated log I would find and replace FROM [table_name] one-by-one in order to determine which tables aren't being actively used.

Good luck! Those table names do not reflect a positive path for that developer. :-( Let me know if you need to figure out how to enable query logging in MySQL and I'll find the answer for you on that as well.

Answer 3

You can use the OS level stat command. Locate the ibd file for that particular table and run the below command stat file_location If the Table is being queried by SELECT, You can find the timestamp of when it was accessed with under the Access field.

Answer 4

SELECT update_time FROM information_schema.tables WHERE table_name='yourtablename'

Or if you want a list of all tables, with last accessed time in order

SELECT table_schema ,
  table_name,
  update_time as LastAccessed
FROM information_schema.tables
GROUP BY table_schema
ORDER BY update_time ASC 

For tables weren't used in the last XX hours add WHERE clause

WHERE update_time < 'yyyy-mm-dd'