Problem with too many mysqld processes created

[Jakotsu]

Hi, I have a server with an Intel Quad-Core Xeon XR210 processor (4 x 2.13 GHz) and 2GB of RAM, running Apache and MySQL 5.

Normally there are something like 300 httpd processes and 20 mysqld ones, however during peak hours, with nearly 1000 active users, the amount of mysqld processes increases to the point that there are as much mysqld proceesses as httpd ones, and all DB connections are refused from that point. The only solution is to manually stop and start mysqld (which takes 3 minutes to stop under this circumstances)

The question I have is, why are so many mysqld handles created, and how to reduce its amount so the server keeps responding. Thanks in avance!

[enigma1]

You could check from the process list what queries take long time to complete. If there is a problem with few you could optimize the queries. If there is no problem perhaps you could use other means like caching at the html level and the sql level.

For example if a particular query say brings up 100 products for a page, its information that won't change often. Then you do the query once you store all records as an array in the dbase and you retrieve them the next time the same query occurs. You will retrieve just one record (the array with the info to the 100 products).

Depends what kind of web app you're using for the site. Some have a separate class to process mysql requests and makes optimization easier. Others are harder but you may only have to do that on certain pages that are heavy to load.

HTML cache can also help and is far easier. In this case you send some 304 headers with a signature for pages with static content and keep the cache signature with a session. This is because you need to flush the cache if a form is posted for instance.

There are also other methods to split the traffic load from the server end as well as some php modules that may help with caching. For example: [php.net...]

[maximillianos]

With such a database-heavy application, I would look into using "memcached". I was able to scale my site for much higher traffic levels by moving the majority of my heavy traffic pages into direct memory, instead of having them pull from a database for every request.

It is really a neat product and it is free. You just need to do some minor custom development to integrate your site with the memcache api. There are very easy to use methods for adding your content to memory, updating the cache, and retrieving it for display.

If you are looking at a growing site and traffic base, I would spend some time investigating memcached. It is used by all the big sites (ie - facebook, slashdot, digg, etc) to handle large traffic without killing their database/servers .

[Jakotsu]

I have enabled the slow queries log adding this to the file /etc/my.cnf

long_query_time = 1
log-slow-queries = /var/log/mysql-slow.log

and restarted Apache and mysqld, but the log file is still empty after 3 days. It seems there are no slow queries, the CPU is really fast and the largest table has only 240.000 records, while the users table has 110.000 records.

I'll try to reduce the KeepAliveTimeout value because it's currently in its default, in fact everything in that server is set to its default value and I should tweak a lot of Apache settings which I'm still learning about.

I think there's something else slowing down everything. It seems using a .htaccess file makes things slower, so I think I'll embed it on the httpd.conf file to avoid the .htaccess lookup on each request.

Caching content is not a good idea, because 90% of my site has dynamic content, similar to a social network. I'll try to implement the memcached tool.