MySQL – How to Determine Which InnoDB Table is Being Written

innodbMySQLmysql-5.5

I am running Mysql 5.5.

I have about 100 Innodb tables spread over 10 DB's (mostly wordpress sites) and I am recently seeing a lot more disk IO than I have been, without any increase in traffic. The disk IO is from mysqld, and in monitoring mysqld, I am seeing what seem to me to be a lot of Innodb writes and specifically, I am seeing a lot of activity in the Innodb_rows_updated status variable that I can't easily explain.

Is there a way that I can determine which of the ~100 Innodb tables is having those rows updated?

Best Answer

SINGLE DATABASE

DATABASE_TO_VIEW=mydb
cd /var/lib/mysql/$(DATABASE_TO_VIEW)
watch "ls -lt | grep ibd$ | head -20"

If you want just the table names

DATABASE_TO_VIEW=mydb
cd /var/lib/mysql/$(DATABASE_TO_VIEW)
watch "ls -lt | grep ibd$ | head -20 | awk '{print $9}' | sed 's/\.ibd//'"

MULTIPLE DATABASES

Create a script called /root/InnoDBChanges.sh with these lines

DATADIR=/var/lib/mysql
IBD1=/tmp/InnoDBFiles.txt
IBD2=/tmp/InnoDBFiles.srt
echo -n > ${IBD1}
cd ${DATADIR}
for DB in `ls -l | grep ^drw | awk '{print $9}' | grep -v mysql | grep -v performance_schema`
do
    FLDR=${DATADIR}/${DB}
    ls -l --time-style="+%s %Y-%m-%d %H:%M:%S" ${FLDR}/*.ibd|awk '{print $6,$7,$8,$9}' >> ${IBD1}
done
sort -nr < ${IBD1} > ${IBD2}
head -32 ${IBD2} | awk '{print $2,$3,$4}'

Then, you can monitor all the databases for changes at the same time

chmod +x /root/InnoDBChanges.sh
watch -n 5 /root/InnoDBChanges.sh

GIVE IT A TRY !!!

CAVEAT

If innodb_file_per_table is disabled, this will not work for you.

You will have to extract the tables from ibdata1. Then, you can try my solution.

STEP #1

Set innodb_file_per_table to 1 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 1

STEP #2

service mysql restart

STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql
Related Question