One of our clients’ servers recently saw a huge drop in speed. None of the hardware had been changed, and while the total number of users has been steadily increasing for the last few years, it has not seen any kind of spike that I thought might cause this type of problem.
In order to try to find the fault or problem, I had to look in many different places and discovered a lot about Linux and MySQL. I enjoy these fact finding missions, even though there are always a lot of dead ends.
I thought I would use this blog as a place to write down all the areas that are worth checking, in case anyone else finds themselves in this situation.
Things to check:
- The Process List
- MySQL Slow Query Log
- Server free disk space
- Server memory usage
- Server load
- Your queries
- Your indexes
Our server was running on Linux Centos 3, and many of the commands below are Linux only. For executing the MySQL commands and queries I suggest getting something like WebYog’s excellent SQLYog, or using MySQL’s own MySQL Workbench.
Listing active MySQL queries
MySQL contains the ability to show a list of currently running queries. From within MySQL – execute the following query:
SHOW PROCESSLIST; |
This will show a list that details which users, hosts, databases are being used to execute queries – how long they have been going for, and what state they are in. Look for queries that have been running for a long time, or have the state of ‘locked’. For more information see here.
Breaking down your queries
It is crucial that you enable slow query logging and it is quite simple to set up. Edit your my.cnf file, and make sure the following is present:
log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 3 |
(Adjust the log_query_time setting to suit the length of the query you are trying to detect)
Once this is in place, restart the MySQL server and start checking the new log file.
The MySQL command EXPLAIN allows you to see a breakdown of the steps taken by MySQL to get your query results, including information about joins and the sequence of queries called. You can use the information it outputs to help optimise your own queries.
The utility is easy to use. Simply prefix your SELECT statement with the word EXPLAIN. eg.
EXPLAIN SELECT userName, firstName, lastName FROM users WHERE firstName LIKE 'da%'; |
top
This command shows you a list of applications and services running on the machine, along with some basic information about the resources they are using.
From here you can see the system load, free memory and the % of CPU for each process.
To quit and return to the shell, use CTRL+C.
df -h
This will show you how much free space is on your mounted drives, in human readable form.
[sam@webserver1 /]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/md1 9.8G 3.9G 5.9G 40% / none 1014M 0 1014M 0% /dev/shm /dev/md2 57G 31G 27G 54% /disk1 |
tail
This is a quick way of looking at the last few lines of a log file. The following will show you the last 10 lines of the mysql log.
tail /var/log/mysql.log |
You can also specify that you want to view a specific number of lines by using the -n parameter. eg.
tail -n 50 /var/log/mysql.log |
Eventually, after going through lots of checking and tests, we discovered that the server was simply at capacity. It hadn’t been upgraded for 8 years and the number of users had quadrupled during that time – with users from 2 other continents now logging on regularly. The database was on the same hardware as the web-server software and the system was just becoming overloaded.
We managed to speed things up considerably before we were forced to upgrade, mostly by examining the slow-query-log and adding indexes where appropriate. Also, many of our queries were refined and tidied up – so we were definitely not wasting our time running the examinations. It has made me realise that it is probably worth going back over old code every now and then to check for potential improvements – something we only tend to do if there is a client requirement, or an OS upgrade required.
We have now performed a complete upgrade to the latest hardware available, with 8GB of memory, more CPU cores than I suspect are really needed, the latest Centos 5 OS and all the most recent MySQL/Apache and PHP versions – which has made the system run incredibly quickly.