Discussion about optimise MySQL to handle a high traffic website.

 

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!)

Help with my.cnf settings for high traffic site {stressed}

 

I have a fairly high traffic and very dynamic site and would like some tips on fine-tuning my my.cnf settings.

I use a combination of both MyIsam and Innodb tables. My more intensive queries are mostly on innodb.

My VPS server has 750MB dedicated Ram available. At peak periods my site has very little ram available. MySQL service reports it usually uses about 35% MEM, the bulk of the rest is usually httpd. CPU usage of mysqld in peak periods can perk up to 70%+

Here is my current file settings:

max_connections=300
key_buffer=64M
table_cache=512
query_cache_type=1
query_cache_size=32M
query_cache_limit=1M
read_buffer_size=2M
sort_buffer_size=2M
thread_cache=1024
myisam_sort_buffer_size=32M
join_buffer_size=1M
read_rnd_buffer_size=1M
concurrent_insert=2
long_query_time=3
wait_timeout=20
connect_timeout=6
interactive_timeout=120
max_allowed_packet=8M
max_connect_errors=20

set-variable = innodb_buffer_pool_size=256M
set-variable = innodb_additional_mem_pool_size=8M
set-variable = innodb_log_buffer_size=8M
set-variable = innodb_thread_concurrency=4
set-variable = innodb_log_file_size=128M

I would really appreciate any tips people can offer.

 

Advertisements
Leave a comment

Leave your opinion

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: