1

Currently, it has none set... It is a debian machine running apache, php and mysql. What is the best way to determine the best value for this settings? Bonus points if you can also elaborate on how mysql handles conecttions for web apps...

Improve this question

3 Answers 3

Reset to default
3

If the max_connections setting is not explicitly set, then it is set to the default value, which I think is 151.

When using mod_php on Apache, and assuming you're using persistent connections (use mysql_pconnect if you use plain old MySQL API), then you should have max_connections in MySQL set to as many concurrent requests as you expect for your application, with some to spare for administration and such - so that if you expect to have 500 concurrent requests on the web application, then I'd set max_connection to something like 550 or 600.

Please note a few things:

  1. Concurrent requests are not users: if you have 1000 users logged in to the web site, and each is clicking something about once every 10 seconds, then you may have only 100 concurrent requests (or less - see the next point).

  2. Concurrent requests is not requests per seconds: its the number of requests that are live on the application at any given moment, regardless of how fast each request is. So for example, assuming a constant load of 1000 requests per second, and if each requests takes 0.5 seconds to be processed by your application, then you have 500 concurrent requests. With each Apache worker serving 2 requests per second, you only need 500 workers to server 1000 requests per second.

Improve this answer
2
  • 2
    I think you mean 151 not 511? dev.mysql.com/doc/refman/5.1/en/…
    – HTTP500
    Jul 13, 2011 at 14:55
  • You are right - I didn't remember correctly. 151 is too low for almost any serious use, so you would do well to start by just increasing it to something reasonable around 500.
    – Guss
    Jul 13, 2011 at 19:24
2

The way to determine is to collect metrics with Munin or Cacti and based on historical data give yourself a bit of a cushion (25-50% whatever the historical max is).

Improve this answer
0

MySQL doesn't handle connections for web apps any different than for any other client. The main difference lies in the fact that in a web application connections usually only live as long as the server needs to process a particular page (unless you use connection pools). In consequence web applications use a lot of very short lived connections (whereas desktop apps use few long-lived connections).

Often the default values are a good starting point. You can then monitor the number of connections that are actually alive by using the MySQLAdministrator. This provides a list of all active connections (under "Server Connections") and also graphs showing (by default) connection usage, traffic and numbers of queries (under "Health", first tab). These graphs are highly configurable, just right-click on one of them and explore.

when you see the graph for connection usage creep towards the max_connections level, you'll have to make some decisions (Do I have enough RAM for more connections? Should I just increase? Do I need another server). Obviously other factors play a role here, too, such as whether the hardware can be upgraded, and overall server performance and optimization.

Improve this answer
1
  • With mod_php, using "persistent connections" in your database configuration means that connections don't die - every worker process that is kept waiting to client reqeusts keeps 1 connection alive and ready.
    – Guss
    Jul 13, 2011 at 19:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .