You are here

Part V: MySQL

Database performance optimization is one of the harder things to explain as configuring MySQL right is no easy task and the values given here are all dependent on your web application and the amount of memory available. So the values given here are more guidelines then anything else and there is a whole book (High Performance MySQL) just about MySQL performance. I known that was not what you wanted to hear, so I'll try to explain the basics in relation to Drupal and want I have experienced as giving a performance boots.

When it comes to database performance one of the biggest bottlenecks is disk I/O which can be levitated by adjusting buffer sizes and memory usage. Getting them right can have a huge impact on performance, but getting them wrong can have a huge negative effect.The default configuration that comes with MySQL on most installations is meant to be all-around and is not optimized for high performance web hosting.

I'm assuming that you are using the InnoDB storage engine for all your tables as that seams to be the best fit for Drupal. If not you can use the Percona Toolkit to convert them by using the following command.

~$ apt-get install percona-toolkit
~$ pt-find DATABASENAME --ask-pass --user root --engine MyISAM --exce "ALTER TABLE %D.%N ENGINE=INNODB" --print

MySQL has many memory related variables that can be adjusted, but some are engine related and has no effect on InnoDB and should be ignored (See Jenny Chen's Weblog) for an in-depth overview. The following lists the settings that I usually changes.

MySQL's configuration file is located in /etc/mysql/my.cnf and will start by updating these values. Not all options may exist in your current configuration file in which case you should just add them to the file.

~$ nano -w /etc/mysql/my.cnf
query_cache_type = 1 
query_cache_size = 64M

table_open_cache = 1024

tmp_table_size = 16M
max_heap_table_size = 16M

join_buffer_size = 512K
read_buffer_size = 512K
sort_buffer_size = 1M

The first two lines enables the query cache and gives it 64 Mb to work with. This caches stores previous executed queries and speeds to selects in the database by using the result of already executed queries that yield the same results.

  • query_cache_type = 1
  • query_cache_size = 64M

The table cache is used to store open tables, so the database don't have to access the disk each time a table is need to querying. The value can be is calculated by multiplying the number of most frequently used tables by the average number of concurrent connections (_ max_connections_ option, which defaults to 151 connections). You can run mysqladmin status to see how many tables you have open. For more information see How MySQL Opens and Closes Tables.

  • table_open_cache = 1024

The table definition cache stores the table definitions (.frm files) and should match the number of tables you have.

  • table_definition_cache = 300

This sets the maximum size of in-memory temporary tables and the limit it determined as the as the minimum of tmp_table_size and max_heap_table_size, hence we need to increase both. You should look server status variables Created_tmp_disk_tables, Created_tmp_files and Created_tmp_tables to see if you need to increase these values. See the section below about status variables for more information.

  • tmp_table_size = 16M
  • max_heap_table_size = 16M

These buffers are used to speed up different query types and they are allocated for each connection and should not be larger then 1Mb as the full amount is always allocated.

  • join_buffer_size = 512K
  • read_buffer_size = 512K
  • sort_buffer_size = 1M

Now lets look at the variables that are directly related to InnoDB. I normally create a new configuration file for these.

~$ nano -w /etc/mysql/conf.d/innodb.cnf
[mysqld]
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=8

Choosing the correct size for your InnoDB buffer pool size might be one of the most important performance optimizations that you can do with your MySQL configuration. It's used to store cached version of the data stored in your InnoDB database tables and with lots of readers can reduces the disk overhead. As a basic rule you should give it the size of your database table space plus 10%, but as it can be hard to guess the further size of your database you can always give it as much as you can spare. The extra 10% is to store insert buffers, hash indexes and locks.

  • innodb_buffer_pool_size = 1G

The additional pool size is used to store internal dictionaries and structural data about your database and 20 Mb should be fin for most Drupal installations.

  • innodb_additional_mem_pool_size = 20M

The flush log at a transaction commit value can be changed to 0, but with the side effect that any MySQL processes that crashes may not have saved the last second of transactions. I most cases this will be a fine trade-off for a little less IO.

  • innodb_flush_log_at_trx_commit=0;

By setting the flush method to O_DERECT you by-pass the operation systems disk cache and ensures that there is no double buffering. MySQL will handle the cache when writing files to disk. There is some talk around the net that this can have a negative effect on some system setups but I have never experienced any problems on the relative simple setups that I have used.

  • innodb_flush_method=O_DIRECT

This setting will limit the number of active threads that InnoDB can use at once, which may be use full to protect yourself against thread thrashing.

  • innodb_thread_concurrency=8

MySQL status variables

To obtain more information about the currently running MySQL server and its current configuration variables these commands can be used.

~$ mysql -u root -p -e 'SHOW ENGINE InnoDB STATUS;'
~$ mysql -u root -p -e 'SHOW variables;'
~$ mysql -u root -p -e 'SHOW GLOBAL STATUS;'

Slow query log

You can use the Percona toolkit to analyse the slow queries log and get advises on how to make the queries execute more efficient. You can of cause use the same trick to analyse all your queries to optimize your application.

~$ pt-query-advisor /path/to/slow-query.log

Moving temporary tables into memory

If you have problems with slow queries do to large joins from views or other modules a performance boots can be gained by moving MySQL temporary tables into a RAM disk. See this blog post: Reduce your server's resource usage by moving MySQL temporary directory to tmpfs from 2bits.

Tuning primer

The following script can be used to analyse the current configuration for problems. The database should have been running for at least 48 hours before the scripts output is reliable. Download the last version of MySQL tuning primer.

~$ apt-get install bc
~$ wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
~$ chmod u+x tuning-primer.sh
~$ ./tuning-primer.sh

Read more

You should really consider reading the High Performance MySQL book as mentioned in the beginning of this section.

References

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.