Tuning MySQL to Improve Performance
January 14, 2013
Had a lovely error occur on my main mySQL server and basically the problem was too many connections. Now, this could just mean I’m having all kinds of traffic, but the more likely culprit is that the connections aren’t be freed up when they were done.
So I looked into the system and found my max_connections set at 500 and upped it to 1000 while I investigated further.
I found the command SHOW PROCESSLIST; returned quite a few processes in the Locked state.
I also found the SHOW STATUS WHERE variable_name LIKE “%con%”; returned a climbing list of threads connected because of those Locked threads.
After some frantic Google work, I found a great script called MySQLTuner which gives you recommendations of overrides to add to your my.cnf file to tune things a little better.
# cd MySQLTuner-perl
# perl mysqltuner.pl
After making it’s changes and restarting I noticed one of its output lines read:
So I found this great little script which runs OPTIMIZE STATUS [tablename]; on each table that needs to get it’s act together.
echo -n “MySQL username: ” ; read username
echo -n “MySQL password: ” ; stty -echo ; read password ; stty echo ; echo
mysql -u $username -p”$password” -NBe “SHOW DATABASES;” | grep -v ‘lost+found’ | while read database ; do
mysql -u $username -p”$password” -NBe “SHOW TABLE STATUS;” $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
if [ “$datafree” -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo “$database.$name is $fragmentation% fragmented.”
mysql -u “$username” -p”$password” -NBe “OPTIMIZE TABLE $name;” “$database”
fi
done
done
<paste the code above and save + exit>
# sh mysql_optimize.sh
This will prompt you for your admin user/password and then optimize each table that’s fragmented. As soon as that was done mysqltuner.pl returned total fragmented tables: 0 and I had no more locked threads.
Combining both of the above scripts really kicked that server into gear, the load average is now .02 instead of .20 which isn’t a big deal but definitely better.
Leave a Reply