9

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.

Sander
  • 99
  • 1
  • 1
  • 5
  • 1
    Are you sure slow queries aren't an issue? `Joins performed without indexes: 129` I saw cases when adding an index the CPU load dropped from 200 to 30%. I would start with checking third party tables. – Simon H Nov 18 '13 at 14:08
  • Thanks Simon H, I am looking into the slow queries. I must admit there are a lot of queries without indexes being executed. I don't really know yet how to correctly add the indexes (on which fields). Is increasing the join_buffer_size a good idea? – Sander Nov 20 '13 at 09:22
  • In general adding an index on the columns which are used for the joins is a good idea. To identify the queries you can use the slow query log or a query like `SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 51200) AS Info FROM information_schema.PROCESSLIST;` to identify currently running queries. – Simon H Nov 20 '13 at 10:54
  • I was already looking into the non-indexed queries inside the mysql-slow.log file after enabling log-queries-not-using-indexes, hence my observation that there were a lot of queries without indexes. And indeed most queries are from third party extensions (good guess!), which makes it difficult to improve. I will go through the file of non-indexed queries and add the indexes. Will post the results later. Thanks so far for your help! – Sander Nov 20 '13 at 11:09

3 Answers3

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.

gelleby
  • 108
  • 8
  • Hello Gelleby, 1. okay I will look into that. 2. we have apc-enabled. I will add it to my original post, forgot to mention it. 3. we have varnish installed version 3.0.4. 4. Okay good tip I will try it. – Sander Nov 15 '13 at 16:08
  • Hi Sander, what does APC tell you? Can you post the graph please? What config do you use for varnish? And finally are u sure each WP and Magento site is running with good coding? – gelleby Nov 15 '13 at 16:32
  • Hello Gelleby, Please see our APC diagram here: http://www.emega.nl/apc-diagram.jpg Regarding our Varnish configuration we have integrated the Phoenix PageCache extension and it comes with a configuration file which we modified to our ports and ip address. I can post the configuration file above if you would like but I think there is something wrong in our mysql configuration which I am unable to find, hence my post here. – Sander Nov 17 '13 at 12:38
  • Have u tried using other settings for MySQL? Followed the example on [this page](http://chrisgilligan.com/consulting/tuning-apache-and-mysql-for-best-performance-in-a-shared-virtual-hosting-environment/)? Or try giving MySQL more memory? – gelleby Nov 18 '13 at 12:45
  • We use tuning-primer and mysqltuner for optimizing the mysql configuration. We have upgraded to 8GB of memory and I have set mysql to use 4GB, but mysqltuner and tuning-primer were not complaining about all memory being used. I have tried mysqlreport and this was showing that 99% of memory was used. But after increasing memory the problem is still persisting. Do you have any suggestions regarding the mysql configuration? Thanks for your cooperation so far! – Sander Nov 18 '13 at 13:10
  • Did you try using Xdebug to find out what is holding up each process? You said "all of a sudden mysql was eating up CPU", something must have changed then? WP/Magento updates, bad scripting, PHP loop code,... MySQL couldn't be the problem because it usually never is ;) – gelleby Nov 18 '13 at 13:21
  • Sander, I thought of something, did you have suPHP installed on your server? If so, try uninstalling that... – gelleby Nov 19 '13 at 09:57
0

Sander,

Not sure about the server things, although working in magento found something useful, you might wanna look into.

Some hosting companies offer high quality Magento hosting solutions with optimized server to ensure best Magento performance. Here are some examples that you might consider:

  • Simplehelix
  • Nexcess
  • Peer1Hosting
  • Zerolag

Also once i read about clustering magento with large data and customer access (large db transactions), which is done at the web, database and file-system level.

Here is a guide to do so:

http://www.severalnines.com/blog/how-cluster-magento-nginx-and-mysql-multiple-servers-high-availability

Also few simple, yet useful tips are to :

  • Enable js, css file merging
  • Turn off the log
  • Enable the compilation
  • Set a indexer cron

I believe there must same things available for wordpress as well.

Hope above can help a bit if not completely.

Simon H
  • 2,495
  • 4
  • 30
  • 38
Sunil Verma
  • 2,490
  • 1
  • 14
  • 15
  • Hello SKV, thank you for your input. We have js/css merging enabled, the log is turned off since it is a live server. Compilation is currently disabled since we have apc and we needed to compile really often due to changes. We have indexer set up with cron indeed and use NICE to prioritize the indexes which are running overnight. – Sander Nov 15 '13 at 16:11
  • Sander, my pleasure may be then you should shift some of your site either WP or Magento to different locations, may be and i hope so all of your sites are generating buisness.. and the server isn't able to take up the load. – Sunil Verma Nov 15 '13 at 16:57
  • Thanks SKV, but I believe our server should be able to to handle this kind of load. We are seeing around 3k unique visitors daily (according to analytics) and have 20-30 orders daily. I think the server should be able to handle this and it was doing so a couple of weeks ago. But then all of a sudden mysql was eating up CPU and now we are figuring out how to resolve this issue. The server load increased from 100-150% to 250-300% without a big difference in traffic or orders. – Sander Nov 17 '13 at 12:45
0

Have you checked your available space? We had some issues one day because of huge log txt files that magento creates.Check for your magento sites that they have disabled the log error from Developer->Log Settings. There is no limit how big these files can be! At least that's what i know.. Hope that helps you. Cheers!

vbak
  • 349
  • 1
  • 7
  • 18
  • Hello vbak,Thank you for your reply. We have log settings disabled since it is a live environment. We have 120GB free disk space so that shouldn't be the problem. Thanks for your input though. – Sander Nov 15 '13 at 16:04