1

in the company where I started working on 1. July, I inherited, among others, a webserver that runs a MySQL/Apache stack on which a Website built in Symfony is running.

The server runs openSuSE 12.1, and has an intel Xeon 3470 CPU, with 12GB Ram. Except for running the website, the server does not have that much to do.

We have been getting some serious server load spikes, which has become particularly noticeable over the last couple of weeks. At these times, the load can go as high as 40 to 60 (occasionally even higher). When this happens outside of office hours, I see that the server does not crash as such, although browsers cannot access the site, and in about 30 mins, the server gets back to its normal load. When it happens during office hours, I manually shutdown apache and MySQL, let the server calm down a little, and then start everything again.

From my access logs and query logs I don't see anything particularly out of the ordinary. I have no reason to suspect a DOS and I can see that queries which sometimes take a minute or more to run during those peak loads run quite normally when I rung them on my local MySQL server, or when they're run by the website under 'normal' circumstances.

Long story short - from what I've gathered so far, the problem seems to be that at some times, the server starts spawning large amounts of Apache processes, together with a large number of MySQL connections. Most of the connections and processes don't seem to do that much, CPU-wise, but they fill up the RAM.

I have read my way through the net and in particular it seems that mysql-wise max_connections and wait_timeout, as well as maybe max_allowed_packet and apache-wise ServerLimit, MaxClients. I don't know if there's a timeout for idle apache processes, but I could imagine that would also help.

Apropos max_allowed_packet ... this is set to 209715200 (200 MB). This seems rather high, or at least it seems to explain that with 40+ connections open, I run out of memory (at least, I understood that MySQL allocates these 200 MB per connection, no matter if it's sleeping or not.

A general problem I have is that I'm not really the biggest hero when it comes to server tuning :/ mainly, I am quite worried that any twiddling with the config parameters mentioned above may kill running Queries or Requests, or cause whatever Havoc in the running of the website.

Ok, that all was not really a specific question, but more of a problem description. Nevertheless, any advice or help is welcome, since I'm not really making any progress ...

Cheers, Constantijn

Constantijn Blondel
  • 11
  • 1
  • Hi Constantijn, it's really hard to tell you where is the problem without details. change kernel.shmmax value to the 70% of server's memory (don't be afraid, and yes, it's neccessary), then try to tune mysql with this: `https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl` if it wont help at this moment - please provide MORE details like `ps auxwf`, test memory usage with `http://www.pixelbeat.org/scripts/ps_mem.py` etc – GioMac Aug 21 '13 at 21:45
  • Hi GioMac, thanks for your advice! I'm a bit hesitant playing around with shmmax ... not that I don't trust you :) but I feel uncomfortable with playing around with kernel parameters for which I don't understand precisely what they do (I didn't have much time yet to read up on the subject). I did however try the mysqltuner.pl, which advised me to increase query_cache_limit, tmp_table_size, and max_heap_table_size, on which I'm doing some research. Two immediate measures I took is to lower the query cache from 1G to 256K,since the hit ratio is abominable anyway. – Constantijn Blondel Aug 23 '13 at 08:10
  • I also deactivated the slow-query log, which was also busy logging non-indexed queries. Even though it's clear that the symfony app is not exactly optimized, I still suspect some configuration problems viz. MySQL and the system as a whole. In particular, I have noticed whilst monitoring that when the system starts swapping and is seemingly running out of memory ( ~100MB marked as free in top), there seems to be plenty of memory in the cache. I'm still collecting more data, and monitoring the system during the day. I'll be trying out some of the tweaks and will post with more info. – Constantijn Blondel Aug 23 '13 at 08:21

0 Answers0