3

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?

  • Wouldn't you just use performance_schema for that? – mustaccio Jan 16 '15 at 21:39
2

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
  • hi, thanks. 2 things: 1. i have about 10 different DB's that i need to check on - the innodb tables are spread amongst them. 2. i get no output from the watch command. what is the "grep ibd$" for? - none of the tables have ibd as part of their filename. – pjv Jan 16 '15 at 18:31
  • Please run SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';. What is it ??? – RolandoMySQLDBA Jan 16 '15 at 18:32
  • .ibd is the file extension of the physical file for any InnoDB table, provided you have innodb_file_per_table enabled. – RolandoMySQLDBA Jan 16 '15 at 18:35
  • innodb_file_per_table OFF - can i turn it on on a production DB without messing things up? – pjv Jan 16 '15 at 18:40
  • I have bad news for you. All the InnoDB data and index pages are inside /var/lib/mysql/ibdata1. My answer can only help if that is enabled and all the tables are extracted from it. – RolandoMySQLDBA Jan 16 '15 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.