When a MySQL table was last touched

Thu, 2011-06-02 20:56 — Shinguz

In our last customer project we had around 600 Gbyte of data in a MySQL database. Because this database consumed a significant amount of our disk space and backups with the InnoDB backup tool took pretty long we wanted to find out if we could get rid of some of the tables.

This application was growing over the last 10 years and it was not clear if some tables are still in use or not.

But how to find out when a table was touched last? MySQL/InnoDB theoretically could know about but does not report this information.

Fortunately the operating system command stat knows when a file was last accessed (read) and modified (written).

For example stat on the InnoDB log file:

stat ib_logfile0
  File: `ib_logfile0'
  Size: 5242880         Blocks: 10240      IO Block: 4096   regular file
Device: 811h/2065d      Inode: 14689226    Links: 1
Access: (0660/-rw-rw----)  Uid: ( 1001/   mysql)   Gid: ( 1001/   mysql)
Access: 2011-05-29 11:53:30.787909003 +0200
Modify: 2011-05-29 11:59:33.697909059 +0200
Change: 2011-05-29 11:59:33.697909059 +0200

Because we want to run several different SQL queries on these information we created a little script to gather those information and store them in a central place. The script was named filesystem_table.php.

Before you gather the information about your tables you have to create somewhere a data collection table to store all the data:

./filesystem_table.php --database=test --user=admin --password=secret \
--port=3306 --host=192.168.1.40 --create

And then you can gather the information about all your MySQL databases:

for i in $(cd /home/mysql/data ; ls) ; do
  ./filesystem_table.php --database=test --user=admin --password=secret \
--port=3306 --host=192.168.1.40 --datadir=/home/mysql/data/$i --instance=$i
done

It is important to realize, that this script must be run local on the machine where your MySQL instance resides because we read the information from the local disk. The data collection table can be somewhere remote.

The parameters of the script you can get with:

./filesystem_table.php --help

So what information can we get out of this data collection now?

For example we can get out which MySQL instance has how many files and how big is it in size:

SELECT instance, COUNT(*) AS cnt, ROUND(SUM(size)/1024/1024, 0) AS size_mb
  FROM file_access
 GROUP BY instance
 ORDER BY size DESC
 LIMIT 10;
+-------------------------+------+---------+
| instance                | cnt  | size_mb |
+-------------------------+------+---------+
| mysql-5.5.9             |  509 |    5709 |
| mysql-5.1.53            |  634 |    1060 |
| mysql-5.5.8-test        |   89 |     637 |
| mysql-5.1.54            | 1313 |     316 |
| mariadb-5.2.4           |   74 |     267 |
| mysqld5141              |   81 |     134 |
| mysql-5.1.51-ndb-7.1.9a |   99 |      75 |
| mysql-5.1.44            | 1002 |      66 |
| mysql-5.5.8             |   91 |      29 |
| mysql-5.6.2             |  101 |      29 |
+-------------------------+------+---------+

Or which tables in which schema we did not read anymore in the last 90 days:

SELECT instance, `schema`, COUNT(*) AS cnt
     , ROUND(SUM(size)/1024/1024, 0) AS size_mb
  FROM file_access
 WHERE atime < DATE_SUB(CURRENT_DATE(), INTERVAL 90 day)
 GROUP BY instance, `schema`
 ORDER BY size DESC
 LIMIT 10;
+-------------------------+----------------+-----+---------+
| instance                | schema         | cnt | size_mb |
+-------------------------+----------------+-----+---------+
| mariadb-5.2.4           |                |   1 |     128 |
| mysqld5141              | test           |  10 |     114 |
| mysql-5.1.51-ndb-7.1.9a |                |   1 |      32 |
| mysql-5.1.44            | shinguz_topodb | 201 |      28 |
| mysql-5.1.44            |                |   3 |      28 |
| mysqld5140              |                |   3 |      20 |
| mysql5142               |                |   3 |      20 |
| mysqld543               |                |   3 |      20 |
| mariadb-5.1.44          |                |   3 |      20 |
| mysqld3710              |                |   3 |      20 |
+-------------------------+----------------+-----+---------+

Or how many megabytes of data were not written anymore in the last 90 days:

SELECT ROUND(SUM(size)/1024/1024, 0) AS size_mb
  FROM file_access
 WHERE mtime < DATE_SUB(CURRENT_DATE(), INTERVAL 90 day)
;
+---------+
| size_mb |
+---------+
|    2111 |
+---------+

Or which tables of schema zabbix in instance mysql-5.1.54 where not written anymore in the last 32 days:

SELECT table_name, file_type, size
  FROM file_access
 WHERE instance = 'mysql-5.1.54'
   AND `schema` = 'zabbix'
   AND mtime < DATE_SUB(CURRENT_DATE(), INTERVAL 32 day)
   AND file_type IN ('MYI', 'MYD', 'ibd')
 ORDER BY size DESC
 LIMIT 10;
+--------------+-----------+-----------+
| table_name   | file_type | size      |
+--------------+-----------+-----------+
| history_uint | MYD       | 133391874 |
| history_uint | MYI       | 118855680 |
| history_str  | MYD       |   2163004 |
| history      | MYD       |   1617357 |
| history      | MYI       |   1443840 |
| trends_uint  | MYD       |   1334181 |
| history_str  | MYI       |   1231872 |
| items        | MYD       |    947836 |
| trends_uint  | MYI       |    616448 |
| items        | MYI       |    482304 |
+--------------+-----------+-----------+

These and many many more information you can get out of the collected data.

A few things you should consider before dropping tables:

Do not trust the tool without any proper verification.
Before dropping tables make sure application can handle it properly.
When you found some tables to clean up. Either rename them first for some time or make at least a good backup.
If you have mounted your file system with the noatime option the atime field does not reflect correct values!

And now have fun cleaning up your database(s)....