Is there a way to find the least recently used tables in a schema?

Question

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and INFORMATION_SCHEMA is always NULL.

Answer 1

Given the following:

• Database mydb
• datadir is /var/lib/mysql

Database with all MyISAM Tables

SELECT * FROM
(
    SELECT
        table_name,
        IFNULL(update_time,create_time) LastTimeTouched
    FROM
        information_schema.tables
    WHERE
        table_schema = 'mydb'
) A
ORDER BY LastTimeTouched LIMIT 20;

Database with all or some InnoDB Tables

This is impossible to do with innodb_file_per_table disabled because all the files reside in a single system tablespace (ibdata1).

Besides data and index pages, there are other things written in the system tablespace:

• Data Dictionary
• Double Write Buffer
  • Safety Net to Prevent Data Corruption
  • Helps Bypass OS for Caching
• Insert Buffer (Streamlines Changes to Secondary Indexes)
• Rollback Segments
• Undo Logs
• Click Here to see a Pictorial Representation of the system tablespace ibdata1

With innodb_file_per_table disabled, there is no way to tell. As far as I know, the InnoDB Storage Engine does not update the UPDATE_TIME column at all.

With innodb_file_per_table enabled, once an InnoDB table exists outside of ibdata1, you can only check the timestamp of the .ibd from the OS.

NEEDED SCRIPT

The following is a script that can tell you the LastUpdated Time for MyISAM and InnoDB tables together in one Database (provided you are using innodb_file_per_table)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
FILEDAT=/tmp/GetFileTimestamps.txt
FILESQL=/tmp/GetFileTimestamps.sql
DATABASE_TO_SEARCH=${1}
cd /var/lib/mysql/${DATABASE_TO_SEARCH}
ls -l --time-style=+%s *.[iM][bY][dDI] | awk '{print $7,$6}' > ${FILEDAT}
NUM=1
SELECT_TAG="SELECT"
HEADER1=" Filename"
HEADER2=" LastUpdated"
echo "SELECT * FROM (" > ${FILESQL}
echo "SELECT TableName,MAX(LastUpdated) LastUpdated" >> ${FILESQL}
echo "FROM (SELECT REPLACE(REPLACE(REPLACE(FileName,'.ibd','')" >> ${FILESQL}
echo ",'.MYD',''),'.MYI','') " >> ${FILESQL}
echo "TableName,LastUpdated FROM (" >> ${FILESQL}
for TAG in `cat ${FILEDAT}`
do
    (( NUM = 1 - NUM ))
    if [ ${NUM} -eq 0 ] ; then FILENAME=${TAG} ; fi
    if [ ${NUM} -eq 1 ]
    then
        UNIXTIME=${TAG}
        SQLSTMT="${SELECT_TAG} '${FILENAME}'${HEADER1},"
        SQLSTMT="${SQLSTMT} FROM_UNIXTIME(${UNIXTIME})${HEADER2}"
        echo ${SQLSTMT} >> ${FILESQL}
        SELECT_TAG="UNION SELECT"
        HEADER1=""
        HEADER2=""
    fi
done
echo ") AAA) AA GROUP BY TableName) A ORDER BY LastUpdated" >> ${FILESQL}
mysql ${MYSQL_CONN} --table < ${FILESQL}

This script is designed to check the timestamp of every table. The only parameter the table needs is the database.

• For MyISAM, it uses the more recent timestamp of one of the following:
  • .MYD file
  • .MYI file
• For InnoDB, it uses the .ibd timestamp