If your MySQL server has hundreds of clients (applications) and tens of thousands of queries per second,  MySQL default network settings may NOT be for you.  Network performance is not often a significant factor in the performance of MySQL.  That said, there are things to consider.

If you are building new applications make these changes now.  Developer expectations are hard to change.  My example below will break your application if  developers open a database connections and then spend ten minutes playing with their play doe before making a query.  If you have working applications make changes slowly.

Middleware provides database connections pooling. This allows database connections to be used by multiple applications. Any program that runs as a deamon or service can use database pooling using libdbi. Applications written in PHP or CGI code don’t pool DB connections. They open and closes the connection each time a user hit a page.

The Apache module mod_dbd can manages database connections. On non-threaded platforms, it provides a persistent connection. On threaded platform, it provides an altogether more scalable and efficient connection pool, as described in this article at ApacheTutor. Note that

supersedes the modules presented in that article.

PHP provides mysql_pconnect. It first tries to find a (persistent) link that’s already open with the same host, username and password (NOT PORT). If one is found, an identifier for it will be returned instead of opening a new connection.  Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).

Back to MySQL.

Max Connections has a default of 100 (<= 5.1.14) to 151 (>= 5.1.15).  If you exceed this number you will receive a “Too many connections error”.  This is often the first setting people find they have to changed as their usage grows.

Max User Connections controls the maximum number of simultaneous connections allowed per MySQL user account. By default this is set to unlimited (zero).  The default is fine. Your system will not run out of connections or memory if you have set Max Connections correctly.  Except a bad applications can “steel” all the connections from all the others.  Hopefully you have assigned a different user for each application.  Set this to the maximum number of connections any given user (application) will make.

Wait timeout is the number of seconds the server waits for activity on a non-interactive connection before closing it. The default is eight hours. (28800 seconds)  This is a crazy amount of time.  How long would you stay on the phone if no one was speaking?  Fifteen seconds is a good place to start. If it gives you problems increase it by fifteen until your good. Applications that open a connections and then process their data for minutes before making a request will be effected by a short time setting.

Interactive timeout is the number of seconds the server waits for activity on an interactive connection before closing it. This is only used by applications using the  mysql_real_connect() function of the MySQL API. I set this to 600. My idea is to give more sophisticated users more grace.  (Think connection pools have to reconnect.)

Connect timeout is the number of seconds that the MySQL server waits for a connect packet before responding with Bad handshake. The default is ten seconds.  Five would be better.

Max connections errors is a strange setting. IO stops after this number of bad connections is made from a host. You can unblock blocked hosts with the

statement.  The default for this is ten (10). On a “good” network this shouldn’t have to be changed.  Because “the dabase should never stop” most DBAs make this a very large number. If you have a over loaded network you should put your database on a network of its own.  Or, you can try increasing this value.

max_allowed_packet controls the size of your BLOBs.  If you are storing pictures in your database, you may need to set this.  The default is one megabyte (1M). The protocol limit for

is 1GB.  Set it to the biggest

you need to store. The value should be a multiple of 1024 bytes.

Here are my my.cnf settings for a server with over 200 PHP and Perl applications and peeking at 4,000 connections per second.

# Network management
port                    = 3306
max_connections         = 5000  # number of simultaneous client connections allowed
max_user_connections    = 200   # number of connections a user can make, 0 = Unlimited
wait_timeout            = 15    # seconds to waits for activity on non interactive connection
interactive_timeout     = 600   # seconds to waits for activity on interactive connection
connect_timeout         = 5     # on connect seconds to waits for a connect packet
max_connect_errors      = 1000000 # IO stops after this number of bad connections one good connect reset
max_allowed_packet      = 10M   # How big is your BLOB?

Tweet