MySQL Disk IO Writes – How to Identify Causes

MySQL

I have a Ubuntu 14.04 server running half a dozen wordpress sites. Total traffic to the server is on the order of 30k page views a day.

Over the past couple months, with page views and network traffic roughly flat, my disk I/O has been trending up significantly. It has doubled over the past month. When I run iotop, I can see that mysqld (v. 5.5) is constantly writing to disk at a rate of around 5MB per minute. I can't think of anything happening on any of the sites that would generate so much writing.

I have spent a lot of time reading mysql tuning tutorials and adjusting variables (mostly to do with tmp table size, and query cache parameters) and nothing I have tried has made any difference. My current guess is that there is probably a bad query in one of the wordpress plugins in use. But there are a lot of plugins spread over half a dozen wordpress installs and I don't know where to look.

Is there some way that I can pinpoint what exactly is causing mysqld to be writing to the disk so much?

UPDATE : This question was explored in better detail and answered thoroughly here.

Best Answer

Take a look at the mysql processlist. mysql -p -e "show processlist". Or even a "show full processlist". It will give you a list of currently running queries.

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

MySQL, RAM disk for tmpdir – how to figure out what size it should be

This is a really interesting question.

You could check the

SHOW GLOBAL STATUS like 'Created_tmp_disk_tables';

But that only gives you the overall number of tables created (or the delta, if you subtract that value between two points of time), but not the actual size needed.

You could go the "logical" wayin MySQL 5.6, configuring performance_schema in order to log a certain amount of queries, enough to check how many temporary tables are being created. You can find those on

performance_schema.events_statements_history/events_statements_history_long

On the CREATED_TMP_DISK_TABLES and CREATED_TMP_TABLES. That will give you more information about the type of temporary tables created, but still no information on the actual number or rows written or the size at a moment in time.

I would probably check it the physical way (assuming a UNIX-like OS), by something like this:

sudo lsof -s | awk '{if ($1 == "mysqld" && $9 ~ /\/tmp/) size+=$7} END {print size}'

Checking the total size of files that the mysqld process has open on /tmp along time may give you a better indication, assuming you monitor that at your peak time, or a significant amount of time.