Tuning MySQL

 Discussion about optimise MySQL to handle a high traffic website. 

Tim 
CTO, Digital Content Solutions

MySQL settings, many concurrent users. 
I run a site for a client that has over 3000 users that log in for about 5-7 hours 
per day each. So, at peak times, we have to handle about 2000 concurrent users. 
When configured correctly, PHP and MySQL can handle this load wonderfully on fairly 
cheap Intel architecture. First off, hardware. 

1) It is better to have 2 separate servers for Apache/PHP and MySQL with the Linux of your choice. 
2) Try not to run too much else on either box; leave the resources for Apache/PHP and MySQL. 

Here are the specs on each box in my config: 
1) Apache/PHP: Pentium 3, 600 MHZ, 512 megs ram. 
2) MySQL: Dual Pentium 3, 750 MHZ (1500 MHZ total), 2 gigs ram. 

 Tuning MySQL 

Discussion about optimise MySQL to handle a high traffic website.

Tim
CTO, Digital Content Solutions

MySQL settings, many concurrent users.
I run a site for a client that has over 3000 users that log in for about 5-7 hours
per day each. So, at peak times, we have to handle about 2000 concurrent users.
When configured correctly, PHP and MySQL can handle this load wonderfully on fairly
cheap Intel architecture. First off, hardware.

1) It is better to have 2 separate servers for Apache/PHP and MySQL with the Linux of your choice.
2) Try not to run too much else on either box; leave the resources for Apache/PHP and MySQL.

Here are the specs on each box in my config:
1) Apache/PHP: Pentium 3, 600 MHZ, 512 megs ram.
2) MySQL: Dual Pentium 3, 750 MHZ (1500 MHZ total), 2 gigs ram.

The reason for this configuration is that it is very database heavy;
it is a members only web site with username and password required for login, fully personalized.
It is an online school, so each student has their suite of tools for attending school,
their courses, report cards, time logging, and much more. Teachers have web based tools to create
their courses, including lessons, text to speech audio, and more.

1) PHP coding: be sure to use persistent connections!
Opening and closing a connection from your Apache/PHP box to your MySQL box is a very heavy load.
By using persistent connections, a high capacity site will open connections and share them to
exchange data rather than opening a connection on each page request, sending the data, then closing,
and repeating that process at least once for every user click!
Be sure to use "mysql_pconnect" instead of "mysql_connect" and also that appropriate changes are made
in "php.ini" or overridden by using the command "ini_set".
You can find more documentation on doing this at the php web site.

2) Apache set up ("httpd.conf"): I've changed these various settings, and played with them until they
seem to keep the most "idle %" reported in "top".
MinSpareServers 10
MaxSpareServers 20
StartServers 70
MaxClients 255

3) Mysql set up ("my.cnf"). The MySQL config file, my.cnf.
Here is what to add under the [mysqld] heading.
The two lines, "max_connections" and "max_user_connections" are where the magic happens.
Since your Apache/PHP box is connecting to MySQL, it appears as a single user.
MySQL defaults to 1 max connection, with 1 max connection per user. The following lines make
it so your Apache/PHP box can connect to your MySQL box up to the number you have set "MaxClients"
to in the Apache config above. By using persistent connections, you can pretty much get Apache up,
have it connect to MySQL upon start up, and just use the persistent connections to pass data between
the two boxes rather than opening connections. Its much more efficient that way.
set-variable = max_connections = 300
(this must be higher than "MaxClients" set in Apache, or you won't fully maximize use)
set-variable = max_user_connections = 300
set-variable = table_cache=1200
(max number of tables in join multiplied by max_user_connections)

A few other MySQL tunings:
set-variable = max_allowed_packet=1M (sanity check to stop runaway queries)
set-variable = max_connect_errors=999999
(stop mysqld from shutting down if there are connect errors - this defaults to 1 error and mysqld stops!)

Mail comments to:[email protected]

Last Modified  September 23, 2003