How many mysql processes should there be?

How many mysql processes should there be?

by Gustav W Delius -
Number of replies: 2
When I run the command 'top' on my ubuntu server I see that there are lots of apache2 processes but always only one msyql process. Is that correct? I have turned off persistent connections. Furthermore this one mysql process uses a measly 7% of the available memory. I had thought that I had set the parameters for mysql so that it would use a lot of memory for buffers and caches. Here are the relevant settings from my.cnf (My server has 1.5Gb of memory).

#
# * Fine Tuning
#
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 16M
thread_stack = 128K

I am interested in finding out what I may have done wrong because of lately users occasionally get the database overloaded error.

Average of ratings: -
In reply to Gustav W Delius

Re: How many mysql processes should there be?

by Samuli Karevaara -
This can be due to many reasons, but this will happen if there are more Apache connections than MySQL connections. Even though you only have one mysqld process, inside MySQL you would see more active user connection threads.

The cure is to allow less Apache connections than MySQL connections. Check the Apache setting "MaxClients" and the MySQL setting "max_connections". 1.5 GB of memory might mean something like 50 Apache connections and 55 MySQL connections (yes, that low).
Average of ratings: -
In reply to Samuli Karevaara

Re: How many mysql processes should there be?

by Gustav W Delius -
Samuli, thank you very much for this reply, it was very helpful. The problem was that by default mysql threads stay around for a very long time (8 hours), waiting to see if the client still needs them. That way a lot of threads can build up (many more than the number of apache processes). So I reduced the variable wait_timeout to 30. My suspicion is that really the wait_timeout of a mysql connection does not have to be longer than the KeepAliveTimeout of an apache connection. But I really don't know enough about how these connections work to be sure.
Average of ratings: -