High load average is an issue which is familiar to almost all server owners who have popular sites and a lot of traffic. Usually it indicates that server can’t properly handle visitors’ requests. This short article aims to describe several simple items to consider while analyzing high load average on your Linux server.

1. MySQL slow query log

For highload projects it’s always better to keep MySQL slow query log enabled to analyze. It could be done this way.

[mysqld]
slow_query_log
slow_query_log_file=/var/log/mysqld-slow.log
long-query-time = 1
log-queries-not-using-indexes
log-warnings
expire_logs_days=7
[mysqld_safe]
log-error=/var/log/mysqld.log

Make sure MySQL has permissions to write slow log. Also note that by MySQL server doesn’t rotate slow log itself. So you would need to do it. It could consume a lot of gigabytes. It’s convenient to analyze slow log with use of mysqlsla tool.

2. Analyzing slow queries

To analyze the slow queries it’s possible to use SQL operator EXPLAIN. It allows to get information on how MySQL server performs the query, if it uses indexes and so on. Here’s an example:

mysql> explain SELECT 1 FROM `archive`.`log` LIMIT 1;

3. MySQL settings, InnoDB, MyISAM

MyISAM and InnoDB are the most often used MySQL storage engines. There are too many differences between them to describe here. But one the most important one is that MyISAM doesn’t support transactions while InnoDB does. Use mysqltuner.pl to get information on MySQL settings and follow the optimization tips. Make sure you understand parameters meaning before changing. If you don’t you’d better ask someone who understands. First optimization tune could be increasing key buffer pool and InnoDB buffer pool. MyISAM keeps in memory only indexes while InnoDB ties to keep the data along with the indexes. So InnoDB would perform the best if its data fits into memory (i.e. if you have 4G of InnoDB data, set innodb_buffer_pool at least to 5-6G). Besides you can use mysqreport and innotop to get more information on what is going on.

4. Swap, IO wait

If your server swaps a lot it will be a one of the sources of performance issues. Since traditional HDD are much slower than RAM the processes could wait for a quite long time to get a response. You can check swap using with top or free commands. One of the most important parameters here is “IO wait”. Here is a sample output of top:

Cpu(s): 12.1%us, 1.8%sy, 0.0%ni, 85.1%id, 0.3%wa, 0.5%hi, 0.2%si, 0.0%st
Mem: 8166644k total, 7773496k used, 393148k free, 165328k buffers
Swap: 8387572k total, 259428k used, 8128144k free, 5404860k cached

If its value is more then 30-40% you might want to optimize your server and/or add more RAM.

5. Apache and nginx

During last several years nginx has gained a high popularity. More and more users decide to use nginx instead of Apache. Although Apache is still one of the most popular web servers and the most featurer one. Since nginx uses epoll for working with the sockets states and file AIO to serve static files, often it performs much better then Apache. So if your application can be deployed with nginx you might want to consider the migration. At present almost all popular CMS and frameworks can be deployed with nginx: Django, Yii, WordPress and so on. Be aware that nginx doesn’t support .htaccess files so if you have them you will need to rewrite them for nginx. Fortunately there are free online converters which can help you with this.

6. Unnecessary services

If don’t need some services it would be better to disable them. It also better from security point of view. On Centos server you can use chkconfig to disable the service. On latest version of Fedora it can be done by systemctl. If you don’t use NFS you can disable all related services and so on.

7. Use performance monitoring software to track the metrics.

There are a lot of both self-hosted and web solutions to track the performance metrics. The graphics allows to analyze the dynamics much easier.