Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

I have a Ubuntu 10.04 Server and for some reason it always seems to exhibit an intermittent delay whenever performing CRUD operations on the mySQL database. Most Post commands are executed within a normal amount of milliseconds, but when there is a delay it always seems to be for about 4.5 seconds. This is not a busy, public server so it shouldn't be a matter of server load being the problem. The best way for you to understand what I am trying to explain is for me to show you. So without further delay, please take a quick look at these couple of short videos:

video 1
video 2

Can anyone give me any ideas why this is happening and what to try to resolve it?

share|improve this question
    
Have you played with the options in the my.cnf file? for example the max allow packet, query cache limit and others? I use this tool raw.github.com/rackerhacker/MySQLTuner-perl/master/… when I want to tune the settings –  Luis Alvarado Jan 31 '13 at 14:20
    
The video indicate HTTP requests being sent. This does not indicate an issue in MySQL at this time. An Apache web server not configured correctly will simply drop connections once the number of connections is larger than the amount of workers available. So, please share more about your thoughts why MySQL is the cause here. As far as I can tell this is an unconfigured Apache woker issue. –  gertvdijk Jan 31 '13 at 14:25
    
@LuisAlvarado - I have never played with the my.cnf file, but I will try that now. Thanks for the tip. –  DanielAttard Jan 31 '13 at 14:36
    
@gertvdijk - your analysis makes sense. I agree with what you are saying. There is no reason to believe that MySQL is the problem. You are probable right that this is an unconfigured Apache issue. What would be your suggestion for the best way to try and debug this Apache issue? Any tools you would recommend? Thanks. –  DanielAttard Jan 31 '13 at 14:38
    
@DanielAttard What MPM (prefork, worker, event, etc.) are you using in Apache? Or simply tell what you installed to build your web application with, e.g. PHP as Apache module, PHP-FCGI, PHP-FPM, Python, Ruby/Passenger, etc. etc. I wrote some answers earlier on for some, otherwise I can provide a new answer probably. –  gertvdijk Jan 31 '13 at 14:40
show 1 more comment

1 Answer

up vote 2 down vote accepted

As I mentioned in the comments, I see no reason to believe MySQL is the bottleneck here, but I do think the webserver configuration can cause this.

Installing a regular LAMP setup done by for example apt-get install lamp-server^, will install PHP as an Apache module and pull in the prefork MPM. By default, this only starts with a limited number of workers (forks) to handle incoming connections. Any new connections will simply not be processed (though not refused). So, I think what you're seeing is simply that the "pool" of workers is to small to handle all the connections.

An error similar to this one will probably be in your Apache error logs somewhere when hitting this limit:

[error] server reached MaxClients setting, consider raising the MaxClients setting

To configure the prefork MPM in Apache, you can see the current values in /etc/apache2/apache2.conf:

# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_prefork_module>
    StartServers          5
    MinSpareServers       5
    MaxSpareServers      10
    MaxClients          150
    MaxRequestsPerChild   0
</IfModule>

In order to change this, I'd recommend to create a new file in /etc/apache2/conf.d, e.g. prefork-mpm-tuning.conf and just "overload" the settings from their defaults (and restart Apache):

<IfModule mpm_prefork_module>
    StartServers          20      # |-- important to increase
    MinSpareServers       20      # |
    MaxSpareServers       50
    MaxClients           70       # too high will get you out-of-memory here
    MaxRequestsPerChild   0
</IfModule>

Important note here is that Apache only scales up per second, starting with only one (hardcoded, not configurable) and never scales down unless you explicitly configure it to. As explained in the Apache documentation on performance tuning:

If the machine is busy spawning children it can't service requests. But it has such a drastic effect on the perceived performance of Apache that it had to be replaced. As of Apache 1.3, the code will relax the one-per-second rule. It will spawn one, wait a second, then spawn two, wait a second, then spawn four, and it will continue exponentially until it is spawning 32 children per second. It will stop whenever it satisfies the MinSpareServers setting.

This appears to be responsive enough that it's almost unnecessary to twiddle the MinSpareServers, MaxSpareServers and StartServers knobs. When more than 4 children are spawned per second, a message will be emitted to the ErrorLog. If you see a lot of these errors then consider tuning these settings. Use the mod_status output as a guide.

While the last paragraph may apply to general cases, I find it very confusing in testing that Apache seems unresponsive after restarting. For this reason my recommendation is to raise the StartServers setting.

So, to summarize:

  • You probably want to increase the StartServers to accept all the subsequent connections you fire from the clients. Apache's prefork MPM is not very dynamic worker - to be able to have it perform well with a lot of simultaneous connections, you'll need to simply start a lot.
  • Allow it to allocate enough memory (roughly 20MB per fork in practice). Yes you'll need loads of it just to serve 100 concurrent visitors in this design. (look at PHP-FPM if you need more performance with less memory)
  • Limit the MaxClients to prevent Apache to claim all memory of the server and crash it. Do math and testing here.
  • Setting StartServers too low to save memory while maintaining a larger MaxClients is a bad thing: your site will appear to be slow for the one-per-second reason and memory usage will grow later anyway. This will happen then.

References:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.