MySQL performance optimization

This page describes how MySQL server performance can be optimized, mainly in context how MySQL server is used in ETICS.

At the end of the page you will find some suggested configurations for ETICS machines.

InnoDB log file

MySQL 5.0 user guide - InnoDB tuning
Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint. Small log files cause many unnecessary disk writes. The drawback of big log files is that the recovery time is longer. Make the log buffer quite large as well (on the order of 8MB).

System variables:

• innodb_log_file_size = 5M by default
• innodb_log_buffer_size = 1M by default

How to change log file size
MySQL 5.0 user guide - managing InnoDB files
To change log file size, old DB transaction log files have to be removed:

1. Stop the MySQL server and make sure that it shuts down without errors.
2. Copy the old log files into a safe place just in case something went wrong in the shutdown and you need them to recover the tablespace.
3. Delete the old log files from the log file directory (/var/lib/mysql/ib_logfile?).
4. Edit my.cnf to change the log file configuration, and start the MySQL server again.
5. MySQL daemon sees that no log files exist at startup and tells you that it is creating new ones (check log files).

InnoDB flush method

MySQL 5.0 user guide - InnoDB tuning
In some versions of GNU/!Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and other similar methods is surprisingly slow. If you are dissatisfied with database write performance, you might try setting the innodb_flush_method parameter to O_DSYNC. Although O_DSYNC seems to be slower on most systems, yours might not be one of them.

System variables:

• innodb_flush_method

Transaction isolation level

MySQL performance blog - InnoDB optimization
Check if your application can run in READ-COMMITED isolation mode - if it does - set it to be default as transaction-isolation=READ-COMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.

SQL command:

• SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Query cache

MySQL forum - how to set and monitor query cache
If you often have recurring queries for tables that are not updated frequently, use the query cache.

You can monitor cache usage by:
   show status like "qcache%";
Where Qcache_lowmem_prunes shows how many times a query had to be removed from the cache as there was no space in it. High value indicates that cache size is too small.

System variables:

• query_cache_size = 0 by default
• query_cache_type = 0 (disabled) by default
• query_cache_limit = 1M by default
• query_cache_min_res_unit = 4K by default

Temporary tables

MySQL 5.0 user guide - tuning
If you create a lot of disk-based temporary tables, increase the size of tmp_table_size if you can do so safely. Keep in mind that setting the value too high may result in excessive swapping or MySQL running out of memory if too many threads attempt to allocate in-memory temporary tables at the same time. Otherwise, make sure that tmpdir points to a very fast disk that's not already doing lots of I/O.

You can monitor temporary space usage by:
   show status like 'Created_tmp_%';

64bit machines

MySQL performance blog - InnoDB optimization
First - run 64bit operating system. We still see people running 32bit Linux on 64bit capable boxes with plenty of memory. Do not do this.

Slow queries monitoring

MySQL 5.0 user guide - the slow query log
The slow query log consists of all SQL statements that took more than long_query_time seconds to execute. To run MySQL with slow query log:
   mysqld --log-slow-queries[=file_name]
Note: mysql home is usually /var/lib/mysql

System variables:

1. long_query_time = 10 by default (in secounds)

JDBC parameters

MySQL 5.0 user guide - JDBC connection parameters

1. defaultFetchSize - actually this value does not matter as MySQL JDBC driver transfers all result to client side in one go by default.

Memory buffers and system variables

MySQL performance blog - InnoDB optimization - describes the most important variables in context of performance, with hints how to adjust them.

MySQL 5.0 user guide - system variables The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running (but not all).

You can browse current values of system variables using :
   show variables;

System variables:

1. innodb_buffer_pool_size = 8M by default
2. innodb_thread_concurrency = 8 by default, some sources suggest to set it to (2*number of cores)
3. innodb_additional_mem_pool_size = 1M by default
4. join_buffer_size = 122880 by default
5. sort_buffer_size = 2097144 by default

Optimize tables

MySQL 5.0 user guide - optimize table
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows. For InnoDB tables, optimize table is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

Commands for ETICS data-base:

  optimize table _system;
  optimize table _systemUser;
  optimize table account;
  optimize table accountDelete;
  optimize table buildCommand;
  optimize table component;
  optimize table componentConfiguration;
  optimize table componentUser;
  optimize table configurationBuildCommand;
  optimize table configurationConfiguration;
  optimize table configurationDependency;
  optimize table configurationEnvironment;
  optimize table configurationProperty;
  optimize table configurationTestCommand;
  optimize table configurationUser;
  optimize table configurationVcsCommand;
  optimize table configurationVersion;
  optimize table environment;
  optimize table job;
  optimize table jobPlatform;
  optimize table jobRun;
  optimize table message;
  optimize table platform;
  optimize table platformMapping;
  optimize table project;
  optimize table projectComponent;
  optimize table projectConfiguration;
  optimize table projectSubsystem;
  optimize table projectUser;
  optimize table property;
  optimize table role;
  optimize table schemaInfo;
  optimize table subsystem;
  optimize table subsystemComponent;
  optimize table subsystemConfiguration;
  optimize table subsystemSubsystem;
  optimize table subsystemUser;
  optimize table testCommand;
  optimize table user;
  optimize table vcsCommand;

---

Suggested MySQL configuration for test machine

Machine configuration:

1. etics-test.cern.ch
2. 2 x Xeon 2.4GHz
3. 1GB phisical RAM
4. 32bits operating system

Suggested configuration (/etc/my.cnf):

[mysqld]
innodb_buffer_pool_size=150M
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
innodb_additional_mem_pool_size=20M
join_buffer_size=2M
sort_buffer_size=2M
long_query_time=1
query_cache_size=48M
query_cache_type=1
query_cache_limit=2M
query_cache_min_res_unit=4K

---

Suggested MySQL configuration for production machine

Machine configuration:

1. etics.cern.ch
2. 2 x Xeon 3.0GHz
3. 4GB phisical RAM
4. 32bits operating system

Comments:

1. With default MySQL configuration, in average 800M of physical memory is free on the machine.
2. Changing log file size requires removing old transaction log files and it does not gives any performance improvements in our case (ETICS does much more read operations than write)
3. Cache size set experimentally using results from test machine.

Suggested configuration (/etc/my.cnf):

[mysqld]
innodb_buffer_pool_size=400M
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
innodb_additional_mem_pool_size=20M
join_buffer_size=2M
sort_buffer_size=2M
long_query_time=10
query_cache_size=48M
query_cache_type=1
query_cache_limit=2M
query_cache_min_res_unit=4K

---

Optimization of backups with mysqldump

MySQL 5.0 user guide - mysqldump
During the backup with mysqldump some optimizations can be introduced as options in the command:
--add-drop-database to insert the DROP DATABASE in the output
--add-drop-table to insert the DROP TABLE before each table in the output
--compact to produce less verbose output
--quick Retrieve rows for a table from the server a row at a time. Very good for large tables because it does not lock the table during the complete dump
--skip-comments to skip comments in the output

-- TomaszKokoszka - 26 Feb 2008 -- LorenzoDini - 26 Mar 2008