What would be the optimal MySQL configuration for a Drupal 7 site?

Question

Recently, I had some performance problems with my Drupal site, and I have discovered that one important part is to optimize your MySQL configurations according with the dimensions of the site.

What would be the best MySQL configuration for a Drupal site?

I am more interested in InnoDB tables than in MyISAM.

Here is how I configured /etc/mysql/my.cnf so far:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP

query_cache_limit       = 1M
query_cache_size        = 16M

log_error                = /var/log/mysql/error.log

expire_logs_days        = 10
max_binlog_size         = 100M

innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

UPDATE: I need more opinions and configurations related to this question.

Answer 1

For a highly trafficked site, you should tune all buffers for the content that is in place now. Regardless of the version of Drupal, the MySQL layer can have its configuration computed.

In fact, if you have InnoDB data without enabling innodb_file_per_table, you need to cleanup InnoDB by segmenting each table into its own physical tablespace. It is possible to do decent MySQL tuning even if you have a limited hardware. There are many scenarios for doing such InnoDB optimizations.

IMHO, you cannot plan good settings for my.cnf without knowing the amount of data to configure for. You would have to periodically load a current dataset from production into a staging environment, perform optimizations and come away with the numbers to configure in the my.cnf of the production server.

Answer 2

Here are is part of my my.cnf for a production site with lots of accesses:

key_buffer = 128M # use 1/4 of system memory
query_cache_size = 128MB
query_cache_limit = 4MB
table_cache = 512
sort_buffer_size = 32M
myisam_sort_buffer_size = 32M
tmp_table_size = 64MB
delay_key_write = 1
wait_timeout = 60

I also commented out the binary logging line (once its in production)

Another thing to do if you're really concerned about performance would be to activate the slow query log and see if there are any queries (particularly from views module) that are slowing down the site. If you find any you can use hook_views_query_alter() to tweak those queries.

That said, a static page cache such as Varnish or Boost will give you much better results than any kind of database optimization.

References:

http://www.notesbit.com/index.php/web-mysql/mysql/mysql-tuning-optimizing-my-cnf-file/

http://docs.cellblue.nl/2007/03/17/easy-mysql-performance-tweaks/

http://www.ducea.com/2006/11/06/identifying-mysql-slow-queries/

Answer 3

http://london2011.drupal.org/conference/sessions/damn-quick-drupal-how-make-drupal-perform-and-scale-rockstar

Drupal Con London had a dedicated talk on this. Essentially answers this question. There are too many variables to make one definative answer, but he lays out the guidelines for awesome out the box performance.

Answer 4

Be sure to also run tuning-primer.sh (can't find a link right now, but there are a bunch of them) after your site has run for at least 48 hours. It can give good indications on what settings need changing.

There are also numerous threads on drupal.org with all sorts of example configurations for different types of servers, so be sure to check that out as well.

Answer 5

InnoDB has a lot of specific parameters that shouldn't be neglected. This is my.cnf InnoDB parameters for a Web + Database server running MariaDB 5.5.30 with 32GB RAM largely following the recommendations of Innodb Performance Optimization Basics.

# InnoDB
innodb_buffer_pool_size = 4028M # 1/8th of total system memory
innodb_log_buffer_size  = 4M
innodb_file_per_table   = 1
innodb_open_files       = 256
innodb_io_capacity      = 512
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 120