With Apache 1.3.19, MySQL 3.23.43, and PHP4.0.6 compiled into Apache as a module, what determines how many MySQL processes are running at any given time? I have seen from 4-28 MySQL processes running at different times on the server.

Is it related to "thread_cache_size" (Mine is 128) setting? Is there a way to limit/control this? Just wondering how MySQL determines how many children to spawn of itself. Thanks.

- John C.

Originally posted by JohnCrowley
I have seen from 4-28 MySQL processes running at different times on the server.


On my server, I have seen around 140-180 mysql processes running :( I want to know how I would be able to lower this number also ...

cheers,
:beer:

edit the max connections in your my.cnf file and restart mysql

Editing max_connections to restrict the number rof running processes is not the solution, as even if there are no connections to MySQL, I can still have 20 processes running. With Apache, for example, you can control the min and max spare servers at any time, as well as max connections. This type of control or a specific explanation of what drives the running mysql processes is the information I am seeking :)

- John C.

The number of open MySQL connections depends on your PHP code.

Each time you have a MySQL_connect or MySQL_pconnect statement, a connection is opened (in the case of pconnect, it attempts to use an existing idle connect instead of opening a new one). Each time you have a MySQL_close statement, a connection is closed (unless it was opened with pconnect).

A problem I ran into was that I had several different scripts opening several different databases, so a single page on that site could cause 3 or 4 mysql connections to open. If you multiply that over, say, 100 active apache connections, you can see how it quickly adds up to a lot of mysql connections.

The mysql setting "max_connections" puts an upper limit on the number of mysql connections allowed. But if you max out, your visitors will see MySQL errors all over your web page.

If you are using persistent connections (pconnect), you can shorten their idle time with the mysql setting "wait_timeout". The default for wait_timeout is 28800 seconds -- that's 20 hours! I would suggest something like 300 (5 minutes) as more reasonable.

The idea behind persistent connections is to improve performance by not having to re-establish a mysql connection with every web page. Nice theory, but it doesn't usually work that way. Instead, you end up with a lot of idle connections sitting around doing nothing but taking up memory. This is especially true because of apache's non-threaded mode of operation -- a mysql connection opened by one apache process cannot be handed over to other apache processes, so if you have 100 apache processes running, you can have 99 open idle mysql connections, and still have to open a new one if you are being serviced by the 100th apache process.

By the way, if you are doing virtual hosting a server, the wait_timeout setting is even more important, because each of your virtual hosts will have their own username and password to mysql, so they will not be able to share each other's persistent connections. That means if you have 100 apache processes, and 50 virtual hosting customers all using pconnect, you could theorectically have 100 * 50 = 5000 mysql connections, with nearly all of them sitting idle. Since wait_timeout defaults to 20 hours, each mysql connection opened with mysql_pconnect will sit around for 20 hours doing nothing before it goes away.

Just reduce wait_timeout significantly (I recommend setting it to 300 seconds) and you should see a big reduction in the number of idle mysql connections.

Thanks Gunzour for the explanation. I have set my wait_timeout to 300, and have Apache children dying off after 3000 requests. So, if I set max_connections to 150, MySQL will continue to start new processes when called via pconnect until it reaches the max # of open Apache processes or max_connections, whichever is smaller? I do have a bunch of different sites and db's/user/pw on the machine, so I am just trying to tweak things to support any increase in MySQL usage in the future.
:)

- John C.

MySQL will open new connections as needed until it reaches max_connections. It doesn't pay any attention to your MaxClients setting in Apache, so it's possible (although probably not likely with your wait_timeout set low) for the number mySQL processes running to be greater than the number of Apache processes running.

Doug