Improving Mysql Performance For A High Traffic Drupal Site

Last Updated on

IMPROVING MYSQL PERFORMANCE

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 MYSQL PERFORMANCE ENGINES: MYISAM VS INNODB

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.

Picture 9

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]

Do you have any other suggestion?

WHICH ENGINE FOR WHICH TABLE?

Thu, 04/15/2010 – 12:24 — Anonymous

It would be nice to have a set of condition which will help to decide if for a certain table MyISAM or InnpDB is the better choice.

You told that InnDB tables don’t support FULLTEXT searches. What is abou normal read operations? Are there advantages for a site with higher traffic (read operations) to switch tables to InnoDB. And which tables are best for this engine?

Is it a good idea to switch watchdog/statistic tables to InnoDB. Have MyISAM tables a better performance on read operations?

Note: Learn to do: mysql string replace

Blog Uncategorized
Previous reading
Drupal Memcache Performance Statistics For Sessions And Data Caching: Taking The Full
Next reading
Drupal 6 Performance Optimization Techniques