High server load caused by mysql

Question

We are running a VPS and experience a high load caused by the mysql server. Currently we are unable to find the cause of this problem and therefore I hope someone can point me in the right direction.

The VPS has 4 cpus and 4GB (18/11 EDIT: now 8GB) of RAM available. Disk information is not available but I believe they are not the fastest. On this VPS we run 1 magento CE 1.7.0.2 installation with 20 webshops and 8 wordpress installations (connected to the magento system). We do have some custom extensions installed in the magento system. We use Ubuntu 13.04 with Nginx 1.2.6, mysql 5.5.34, PHP 5.4.9, varnishd 3.0.4 and use APC as an opcode cacher.

When running top:

top - 13:58:21 up 17:51,  2 users,  load average: 4.40, 4.09, 3.91
Tasks: 119 total,   3 running, 116 sleeping,   0 stopped,   0 zombie
%Cpu(s): 94.0 us,  3.5 sy,  0.0 ni,  2.0 id,  0.2 wa,  0.0 hi,  0.0 si,  0.3 st
KiB Mem:   4049220 total,  3101744 used,   947476 free,   253548 buffers
KiB Swap:  1044476 total,    22324 used,  1022152 free,  1442356 cached
PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND
22378 mysql     20   0 3439m 588m 7888 S 224.0 14.9  73:21.99 mysqld
24650 eemeega6  20   0  532m  56m  28m S  26.0  1.4   0:11.25 php5-fpm
24658 eemeega6  20   0  534m  57m  27m S  25.8  1.5   0:02.80 php5-fpm
24649 eemeega6  20   0  529m  58m  33m S  25.4  1.5   0:12.95 php5-fpm
24652 eemeega6  20   0  532m  61m  33m R  22.2  1.5   0:05.00 php5-fpm
24659 eemeega6  20   0  538m  59m  25m R  16.6  1.5   0:00.83 php5-fpm
24661 eemeega6  20   0  533m  55m  27m S  16.2  1.4   0:00.81 php5-fpm
24648 eemeega6  20   0  535m  65m  34m S  15.4  1.7   0:14.46 php5-fpm
24653 eemeega6  20   0  536m  64m  32m S  11.8  1.6   0:04.55 php5-fpm
24662 eemeega6  20   0  533m  49m  21m S   6.2  1.3   0:00.31 php5-fpm
1236 nobody    20   0  731m 369m  76m S   1.0  9.4   6:38.74 varnishd
22478 www-data  20   0 90532  10m 1044 S   0.4  0.3   0:07.56 nginx
10 root      20   0     0    0    0 S   0.2  0.0   2:29.32 rcu_sched
247 root      20   0     0    0    0 S   0.2  0.0   1:20.05 jbd2/dm-0-8`

Our my.cnf file has the following values:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 127.0.0.1

key_buffer      = 64M
max_allowed_packet  = 1M
thread_stack        = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 50
table_cache            = 2048
table_definition_cache = 1024
#thread_concurrency     = 10
thread_cache_size       = 24
wait_timeout        = 60
interactive_timeout = 60

query_cache_limit   = 1M
query_cache_size        = 64M

log_error = /var/log/mysql/error.log
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 8
#log-queries-not-using-indexes = /var/log/mysql/mysql-not-indexes.log

expire_logs_days    = 10
max_binlog_size         = 100M

#InnoDB
innodb_buffer_pool_size = 1280M
innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size = 1M
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 60

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

Our output from mysqltuner.pl:

[--] Reads / Writes: 97% / 3%
[--] Total buffers: 1.4G global + 2.7M per thread (50 max threads)
[OK] Maximum possible memory usage: 1.6G (40% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 42% (21/50)
[OK] Key buffer size / total MyISAM indexes: 64.0M/35.4M
[OK] Key buffer hit rate: 99.8% (902K cached / 1K reads)
[!!] Query cache efficiency: 1.2% (16K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 739K sorts)
[!!] Joins performed without indexes: 129
[OK] Temporary tables created on disk: 5% (56K on disk / 1M total)
[OK] Thread cache hit rate: 99% (21 created / 16K connections)
[OK] Table cache hit rate: 24% (940 open / 3K opened)
[OK] Open file limit used: 9% (378/4K)
[OK] Table locks acquired immediately: 100% (4M immediate / 4M locks)
[OK] InnoDB data size / buffer pool: 339.7M/1.2G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 128.0K, or always use indexes with joins)

We ran mysql longer then 24hours and used optimized most settings according to mysqltuner and tuning-primer as well. Used repair and optimize functions to optimize all databases.

Unfortunately mysql is swapping to disk and I think therefore the high CPU load.

I hope someone can point me in the right direction of finding the cause of the swapping/high load. We are not experiencing slow log queries a lot (only when reindexing magento).

If anyone needs additional information please ask me.

[SOLUTION]:

So basically we resolved this issue by turning off persistent connections in php.ini: mysql.allow_persistent = Off We noticed a drop in CPU usage from mysql. Tuning-prmier is not complaining anymore about our applications not closing their connections. We do have some improvements to make since not all our queries are using indexes properly but for now this fix helps us keeping our server up.

Kind regards, Sander.

Answer 1

I found that server performance can be "upgraded" by a various of things, some of them are:

1. Figuring out how much memory you need to let MySQL go through all your data but also providing a cash of executed SQL queries. You can read all memory related stuff here and understand how it works. After that you can Enabling Large Page Support

2. I found that you can improve performance by enabling APC where you can actually see how it improves your performance. Also have a look at this.

3. Using varnish, but thats a long story to type out here. It comes down to it that you need a very good config for it to work perfectly.

4. If you're still having problems, try using Xdebug to find out what is holding up each process. You could do that last one from the start though, as your MySQL will try to execute everything u ask from it, but 1 process at a time. Therefor it will store every process it can't execute in the memory and if that happens allot, it will overflow your memory.

My conclusion was that, how more php/mysql related stuff (what your server needs time/resources for) that you can re-use by getting it from your cash (Varnish, APC, MySQL mem), the more connections it can handle without creating high server load.