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.

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.

share|improve this question
    
Is your database all InnoDB, all MyISAM, or a mixture of both ? –  RolandoMySQLDBA Jun 3 '13 at 17:15
    
in this schema both- not file-per-table either –  JShean Jun 3 '13 at 17:19
    
It cannot be done for InnoDB tables embedded in ibdata1. I wrote an answer as to how you can with innodb_file_per_table enabled. –  RolandoMySQLDBA Jun 3 '13 at 18:52

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

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
share|improve this answer
    
Gee, I wish the INFORMATION_SCHEMA metadata were more useful here. And many thanks for this handy script! –  JShean Jun 4 '13 at 13:42

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.