how to optimize mysql for high traffic drupal site

Configuring Mysql could be a daunting task if your Drupal site begins to scale and you start getting "too many connections" error or mysql server down errors. We had this opportunity to scale a Drupal site to 2 million page views a month and we improved this with success. So, lets start and share what we found out. I would be glad to hear from other people's experiences as well in the comment section.

 

Drupal 6 comes with MyISAM engine default. First thing you would notice with your drupal site would be your tables locking. Site would begin to go down and when you look at the mysql processes (using mysql tools or command line), I know this would feel like cursing Drupal or Mysql, but before you begin to hit at Drupal and feeling bad about Drupal performance, please try and change your Drupal tables to InnoDB. 

 

You would not be able to change all the tables like node revision if similar module is used. Node revision is dependent on MySQL's FULLTEXT indexing for MyISAM tables if similar module is enabled. FULLTEXT does not work with InnoDB.

 

    ALTER  TABLE  `node`   ENGINE  = InnoDB

Locking would go away. MYISAM locks table whenever an update/insert/delete operation is happening on a node table for example. This keeps other operations on wait. When we have too many such operations in queue, this locking builds up and brings the site to halt. InnoDB does locking at the record level, thus preventing the above case.

 

Discover Mysql Slow Queries:

 

This should be the next step to improve mysql performance. Find out the sql queries that are slow or repeating. This is done as follows:

 

a. Use Devel module. This would list the sql queries that are being executed when a page loads. Monitor the unusal slow sql query or queries repeating. Ask yourself: Is there an sql query that can be optimized or can be removed?

 

b. Log Mysql slow queries as follows in /etc/my.cnf

log-slow-queries=/var/lib/mysqllogs/slow-log

long_query_time=2

log-queries-not-using-indexes

 

Configuring my.cnf:

Here comes the complex part. This may be tricky. 

 

Here is my my.cnf:

 

a.  Limit mysql connections. This is important to set. If it is too high, your server would stop responding and you would have to reboot.  If it too less, you would start getting "too many connections" error on your Drupal site.  I keep it high enough that I never get too many connections error.

 

max_connections=100

 

 

b. If you see too many sleep connections in your mysql processes (SHOW PROCESSLIST), this means that the there is no mysql operation but the mysql connection is still retained. This prevents the mysql connection to be released and taken up by some other process. You would need to reduce the wait_timeout.

wait_timeout=600

 

c. If you changed to InnoDB, make the following changes to my.cnf:

[mysqld]
 # InnoDB settings
 innodb_data_home_dir = /var/lib/mysql/
 innodb_data_file_path = ibdata1:100M:autoextend
 set-variable = innodb_buffer_pool_size=100M
 set-variable = innodb_additional_mem_pool_size=10M
 innodb_flush_log_at_trx_commit=1 

 

The innodb_data_home_dir setting defines the location where InnoDB should create data files. The innodb_data_file_path setting defines the name of the data files. In this case, it will create a 100MB data file called ibdata1 and will extend the size as needed. A data file in InnoDB parlance is a tablespace.

 

d.Logging slow sql queries:

 

log-slow-queries=/var/lib/mysqllogs/slow-log

long_query_time=2

log-queries-not-using-indexes
 

e. Max allowed packet to be increased from 32M to 128M. Typically, if you are not storing your cache in memcache and in mysql tables, you would need to increase this size.

 

 

max_allowed_packet=128M

 

f. Query Cache size to be adjusted:

query_cache_size=4M
query_cache_limit = 2M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M

 

For a high traffic site which is database intensive and has a dedicated 16GB server to mysql, my my.cnf looks like:

 

[mysqld]
datadir=/var/lib/mysql
tmpdir=/var/lib/mysqltmp
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
table_cache=2048
thread_cache_size=32
back_log=100
max_connect_errors=10000
open-files=10000
interactive_timeout=400
wait_timeout=300
max_connections=500
skip-bdb
log-slow-queries=/var/lib/mysqllogs/slow-log
long_query_time=2
log-queries-not-using-indexes
max_allowed_packet=128M
tmp_table_size=256M
max_heap_table_size=256M
query_cache_size=32M
query_cache_limit = 6M
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=16M
join_buffer_size=2M
default-storage-engine=InnoDB
key_buffer_size=128M
myisam_sort_buffer_size=64M
innodb_log_file_size=100M
innodb_buffer_pool_size=6G
innodb_additional_mem_pool_size=20M
innodb_support_xa = 0
[mysql.server]
user=mysql
[mysqld_safe]