MySQL has a large number of configuration options. Adjusting these can make an enormous difference to the performance of your database. Unfortunately, there is no single “best” set of settings. The optimal configuration depends on the version of MySQL, the server resources, other server processes, etc. It is also dependent on the server role; development, testing or live operation.
The default configuration supplied on many installations is very poor. In particular, the default configuration with XAMPP is based on a server with 64MB of RAM and a very old version of MySQL.
The settings suggested here are a compromise between performance, features, data-integrity and logging, and work well with typical web/database applications such as phpGedView or webtrees.
Configuration File
The configuration settings are stored in a plain text file. This file is typically called my.ini on Windows and my.cnf on Linux and MacOS-X. MySQL reads this file when it is started. If you change your settings, you must restart MySQL for any changes to take effect. Make a backup copy of the original file before you start.
If there are any errors in my.ini, MySQL will refuse to start, and write a message explaining why to the error file, which is typically called mysql.err.
my.ini Structure
my.ini files are divided into a number of sections. We are only concerned with the section relating to the MySQL daemon process, mysqld. This may be the only section. Lines beginning with # are comments, empty lines are ignored and configuration lines take the format setting-name=value. This example shows each type of line.
# Configuration settings for the MySQL daemon process [mysqld] port=3306
Before You Start
You should make a copy of you existing my.ini file before changing any settings. It is probably a good idea to back up your entire MySQL data directory.
You should find out the version of your MySQL server (which can be different to the version of your MySQL client). Some of the configuration settings depend on your MySQL version. This is displayed on the home page of phpMyAdmin.
NOTE: While most configuration options can be changed by simply editing my.ini, there is one (innodb-log-file-size) that requires some corresponding file-system changes. Do not change this particular setting without reading and following the associated comments.
Suggested Settings
All these settings belong in the [mysqld] section of the my.ini configuration file.
Connection and Configuration
MySQL needs the current time, to generate timestamps, etc. SYSTEM means take the value from the operating system.
default-time-zone=SYSTEM
Files and directories can be specified with absolute or relative paths. If you have more than one disk, set tmpdir to a location on a different disk to datadir. These values show a typical XAMPP installation.
basedir=c:/xampp/mysql/ datadir=data/ tmpdir=c:/windows/temp/ # Windows pid-file=mysql.pid # Linux #pid-file=/var/run/mysqld/mysqld.pid
This is the size of the largest query that the server will accept. Due to a bug in some versions of MySQL, it also restricts the size of BLOB columns that can be processed. If your application needs to store large media files, etc., then setting this to a value larger than the largest file should prevent any problems. If you are not storing BLOB data, then leave this at the default of 1M.
max-allowed-packet=32M
On a webserver, 60 seconds is a reasonable time to wait for another process to release a lock. If you run other database applications, you may want higher/lower values.
wait-timeout=60
Although MySQL is primarily a network based server, you can communicate with it directly, using a socket (linux) or pipe (windows). To use this, set the servername to “.” instead of “localhost” or “127.0.0.1” in your database connection parameters. It bypasses the network protocol, giving slightly better performance.
# For Windows socket=MySQL enable-named-pipe=1 # For Linux #socket=/var/run/mysqld/mysql.sock #bind-address=127.0.0.1
Logging
log-error=mysql.err
The general log includes every query that MySQL runs. Developers may enable it to investigate specific problems.
general-log=0
The slow query log (as the name suggests) is a log of all queries that take longer than a certain number of seconds to run. Developers may enable this while investigating performance issues. The option to log queries not using indexes may be useful, but remember that for small tables, full-table-scans can be quicker than index access.
# MySQL 5.1.29 and later slow-query-log=0
#MySQL 5.1.28 and earlier
#log-slow-queries=1
log-queries-not-using-indexes=0
long-query-time=1
Performance and Caching
These settings provide a reasonable amount of memory to MySQL for query processing.
# MySQL 5.1.3 and later
table-open-cache=1024
# MySQL 5.1.2 and earlier
table-cache=1024
key-buffer-size=64M
sort-buffer-size=16M
This option saves the result of each query you run, so that if you run it again, the cached values are used. Setting this too high can be counter-productive.
query-cache-size=64M
InnoDB Settings
InnoDB is a database storage engine that offers many benefits over the default MyISAM engine. It will become the default engine in MySQL 5.5 and you should use it unless you have a reason not to do so.
The innodb-log-file-size setting should correspond to sizes of the files ib_logfile0 and ib_logfile1 which live in the directory specified by the datadir setting. If you want to change this setting and these files exist (they will if you have already used InnoDB tables), then you must take the following steps;
- Shutdown, Restart, Shutdown MySQL. This will ensure all data is flushed to disk
- Delete (move to a temporary directory!) these two files
- Start MySQL. This will recreate the files
- If MySQL starts, and you can access your MySQL tables, then delete the old files from step 2.
- If MySQL fails to start, restore both your original my.ini file and these two files. MySQL should now start, and you can review the mysql.err file to find out what problems were found.
innodb=1
innodb-data-file-path=ibdata1:32M:autoextend
innodb-buffer-pool-size=256M
innodb-log-file-size=64M
innodb-log-buffer-size=8M
innodb-fast-shutdown=0
innodb-rollback-on-timeout=1
By default, MySQL flushes every InnoDB transaction to disk. If your application performs many updates in separate transactions, this can be major bottleneck. By setting the value to 0, MySQL only flushes to disk every second. This makes updates much faster, but the downside is that a server failure can result in the last one second of updates being lost. If you are hosting other (perhaps more critical) database applications, then leave this at the default value of 1.
innodb-flush-log-at-trx-commit=0
For very slow systems, uncomment these for extra performance. They remove certain checks and disk-writes, which only become important when you have server faults and hardware failures. On a development machine, performance is generally the key, so you can live without them.
#innodb-support-xa=0
#innodb-checksums=0
#innodb-doublewrite=0