Configuration Basics
Syntax, Scope, and Dynamism
Configuration settings are written in all lowercase, with words separated by underscores or dashes.
- The query_cache_size variable is globally scoped.
- The sort_buffer_size variable has a global default, but you can set it per-session as well.
- The join_buffer_size variable has a global default and can be set per-session, but a single query that joins several tables can allocate one join buffer per join, so there might be several join buffers per query.
Side Effects of Setting Variables
Setting variables dynamically can have unexpected side effects, such as flushing dirty blocks from buffers.
- Query_cache_size
MySQL allocates and initializes the specified amount of memory for the query cache all at once when the server starts. If you update this variable (even if you set it to its current value), MySQL immediately deletes all cached queries, resizes the cache to the specified size, and reinitializes the cache’s memory.
Getting Started
A good idea is to place your configuration file under version control.
General Tuning
Tuning Memory Usage
The following are the most important caches to consider for majority of installations:
- The operating system caches for MyISAM data
- MyISAM key caches
- The InnoDB buffer pool
- The query cache
The MyISAM Key Cache
The MyISAM key caches are also referred to as key buffers; there is one by default, but you can create more. Unlike InnoDB and some other storage engines, MyISAM itself caches only indexes, not data (it lets the OS cache the data).
The most important option is the key_buffer_size, which you should try setting to between 25% and 50% of the amount of memory you reserved for caches.
By default MyISAM caches all indexes in the default key buffer, but you can create multiple named key buffers.
You can use the CACHE INDEX command to map tables to caches.
You can also preload the tables’ indexes into the cache with the LOAD INDEX command.
You can monitor the performance and usage of the key buffers with information from SHOW STATUS and SHOW VARIABLES.
Even if you don’t use any MyISAM tables, bear in mind that you still need to set key_buffer_size to a small amount of memory, such as 32M. The MySQL server sometimes uses MyISAM tables for internal purpose, such as temporary tables for GROUP BY queries.
The MyISAM key block size
- Read-around writes
If the key block size is too small, you may encounter read-around writes, which are writes that the operating system cannot perform without first reading some data from the disk.
The InnoDB Buffer Pool
InnoDB buffer pool doesn’t just cache indexes; it also holds row data, the adaptive hash index, the insert buffer, locks, and other internal structures.
InnoDB also uses the buffer pool to help it delay writes, so it can merge many writes together and perform them sequentially.
The MySQL manual suggests using up to 80% of the machine’s physical memory for the buffer pool on a dedicated server.
There is no equivalent of LOAD INDEX INTO CACHE for InnoDB tables. However, if you’re trying to warm up a server and get it ready to handle a heavy load, you can issue queries that perform full table scans or full index scans.
You can change the innodb_max_dirty_pages_pct variable to instruct InnoDB to keep more of fewer dirty pages in the buffer pool. If you allow a lot of dirty pages, InnoDB can take a long time to shutdown, because it writes the dirty pages to the data files upon shutdown.
The Thread Cache
The thread cache holds threads that aren’t currently associated with a connection but are ready to serve new connections.
The thread_cache_size variable specifies the number of threads MySQL can keep in the cache.
We generally try to keep the thread cache large enough that we see fewer than 10 new threads created each second, but it’s often pretty easy to get this number lower than 1 per second.
In general, you should keep your thread cache large enough that Threads_created doesn’t increase very often.
The Table Cache
Each object in the cache contains the associated table’s parsed .frm file, plus other data.
In MySQL 5.1, the table cache is separated into two parts: a cache of open tables and a table definition cache (configured via the table_open_cache and table_definition_cache variables).
The InnoDB Data Dictionary
InnoDB has its own per-table cache, variously called a table definition cache or data dictionary, which you cannot configure. Each table can take up 4KB or more of memory.
In contrast to MyISAM, InnoDB doesn’t store statistics in the tables permanently; it recomputes them each time it starts. This operation is serialized by a global mutex in current version of MySQL, so it can’t be done in parallel.
Tuning MySQL’s I/O Behavior
MyISAM I/O Tuning
LOCK TABLES can defer writes until you unlock the tables.
You can also defer index writes by using the delay_key_write variable. If you do this, modified key buffer blocks are not flushed until the table is closed.
InnoDB I/O Tuning
The InnoDB transaction log
The overall log file size is controlled by innodb_log_file_size and innodb_log_files_in_group, and it’s very important for write performance. The total size is the sum of each file’s size.
To change the log file size, shut down MySQL cleanly, move the old logs away, reconfigure, and restart.
Log file size and log buffer
InnoDB flushes the log buffer to the log files on disk when the buffer gets full, when a transaction commits, or once per second – whichever comes first. The variable that controls the buffer size is called innodb_log_buffer_size.
How InnoDB flushes the log buffer
When InnoDB flushes the log buffer to the log files on disk, it locks the buffer with a mutex, flushes it up to the desired point, and then moves any remaining entries to the front of the buffer.
InnoDB has a group commit feature that can commit all of them to the log in a single I/O operation, but this is broken in MySQL 5.0 when the binary log is enabled.
innodb_flush_log_at_trx_commit – (0, 1, 2)
It’s important to know the difference between writing the log buffer to the log file and flushing the log to durable storage. In most operating systems, writing the buffer to the log simply moves the data from InnoDB’s memory buffer to the operating system’s cache, which is also in memory. In contrast, flushing the log to durable storage means InnoDB asks the operating system to actually flush the data out of the cache and ensure it is written to the disk.
The best configuration for high-performance transactional needs is to leave innodb_flush_log_at_trx_commit set to 1 and place the log files on a RAID volume with a battery-backed write cache. This is both safe and very fast.
How InnoDB opens and flushes log and data files
Innodb_flush_method: lets you configure how InnoDB actually interacts with the filesystem.
Possible values:
- fdatasync
- 0_DIRECT
- 0_DYSNC
- async_unbuffered: default value on Windows
- unbuffered: Windows-only
- normal: Windows-only
- nosync and littlesync: For development use only.
The InnoDB tablespace
InnoDB keeps its data in a tablespace, which is essentially a virtual filesystem spanning one or many files on disk. InnoDB uses the tablespace for storing tables and indexes, keeping its undo log, insert buffer, doublewrite buffer, and other internal structures in the tablespace.
Configuring the tablespace
Innodb_data_file_path
Innodb_data_home_dir
InnoDB will fill the first file, then the second when the first is full, and so on; the load isn’t really spread in the fashion you need for higher performance. A RAID controller is a smater way to spread load.
Old row versions and the tablespace
Bear in mind that unpurged row versions impact all queries, because they effectively make your tables and indexes larger.
The doublewrite buffer
InnoDB uses a doublewrite buffer to avoid data corruption in case of partial page writes. A partial page write occurs when a disk write doesn’t complete fully, and only a portion of a 16KB page is written to disk.
Thedoublewrite buffer is a special reserved area of the tablespace, large enough to hold 100 pages in a contiguous block. It is essentially a backup copy of recently written pages. When InnoDB flushes pages from the buffer pool to the disk, it writes (and flushes) them first to the doublewrite buffer, then to the main data area where they really belong. This ensures that every page write is atomic and durable.
This means every page is written twice. But because InnoDB writes several pages to the doublewrite buffer sequentially and only then calls fsync() to sync them to disk, the performance impact is relatively small -- generally a few percentage points.
If there’s a partial page write to the doublewrite buffer itself, the original page will still be on disk in its real location. When InnoDB recovers, it will use the original page instead of the corrupted copy in the doublewrite buffer. However, if the doublewrite buffer succeeds and the write to the page’s real location fails, InnoDB will use the copy in the doublewrite buffer during recovery.
Upon recovery, therefore, InnoDB just reads each page in the doublewrite buffer and verifies the checksums. If a page’s checksum is incorrect, it reads the page from its original location.
You can disable doublewrite buffer by setting innodb_doublewrite to 0.
Other I/O tuning options
Tuning MySQL Concurrency
MyISAM Concurrency Tuning
Delete operations doesn’t rearrange the entire table; they just mark rows as deleted, leaving “holes” in the table. MyISAM prefers to fill the holes if it can, reusing the spaces for inserted rows. If there are no holes, it appends new rows to the end of the table.
Even though MyISAM has table-level locks, it can append new rows concurrently with reads. It does this by stopping the reads at the last row that existed when they began. This avoids inconsistent reads.
MyISAM doesn’t support MVCC, so it doesn’t support concurrent inserts unless they go at the end of the table.
You can configure MyISAM’s concurrent insert behavior with the concurrent_insert variable, which can have the following values:
- 0: allow no concurrent inserts; every insert locks the table exclusively
- 1: default value. Allows concurrent inserts, as long as they are no holes in the table.
- 2: New in 5.0 and newer. Forces concurrent inserts to append to the end of the table, even when there are holes. If there are no threads reading from the table, MySQL will place the new roles in the holes.
InnoDB Concurrency Tuning
You can often see whether InnoDB is having concurrent issues by inspecting the SEMAPHORES section of the SHOW INNODB STATUS output.
The most basic way to limit concurrency is with the innodb_thread_concurrency variable.
In the theory, the following formula gives a good value of concurrency:
Concurrency = Number of CPUs * Number of Disks *2
But in practice, it can be better to use a much smaller value.
Workload-Based Tuning
The first thing should do is become familiar with your server. Know what kinds of queries run on it. Monitor it with innotop or other tools.
Optimizing for BLOB and TEXT Workloads
For long variable-length columns (e.g. BLOB, TEXT, and long character columns), InnoDB stores a 768-byte prefix in-page with the rest of the row. This is long enough to create a 255-character index on a column, even if it’s utf8, which might require up to 3 bytes per character.
The server cannot use in-memory temporary tables for BLOB values. Thus, if a query involving BLOB values requires a temporary table – no matter how small—it will go to disk immediately.
There are two ways to ease this penalty: convert the value to VARCHAR with the SUBSTRING() function, or make temporary tables faster. The best way to make temporary tables faster is to place them on a memory-based filesystem (tmpfs on GNU/Linux).
The server setting that controls where temporary tables are placed is tmpdir. If your BLOB columns are very large and you use InnoDB, you might also want to increase InnoDB’s log buffer size.
- Optimizing for filesorts
Max_length_for_sort_data
Max_sort_length
Inspecting MySQL Server Status Variables
SHOW GLOBAL STATUS
- Aborted_clients
- Aborted_connects
- Binlog_cache_disk_use and Binlog_cache_use
- Bytes_received and Bytes_sent
- Com_*
- Connections
- Created_tmp_disk_tables
- Created_tmp_tables
- Handler_read_rnd_next
- Key_blocks_used
- Key_reads
- Max_used_connections
- Open_files
- Open_tables and Opened_tables
- Qcache_*
- Select_full_join
- Select_full_range_join
- Select_range_check
- Slow_launch_threads
- Sort_merge_passes
- Table_locks_waited
- Thread_created
Tuning Pre-Connection Settings
Sort_buffer_size
Read_buffer_size
Read_rnd_buffer_size
Tmp_table_size
Myisam_sort_buffer_size
