MySQL Configuration Variables — Chapter 5
Much can be learned about the internal workings of the MySQL server by studying its configuration variables.
In continuation to below post
Highlights from chapter 5
MySQL has over 200 different options. Every one of them tells a story. Some reveal the presence of a feature. Some show the richness of MySQL optimization algorithms. Some demonstrate MySQL’s ability to self-administer. Some are there because some platform-specific bug needed to be tracked down or worked around at some point. Others exist just to allow the user to choose a file or a directory used for some internal operation, but their very existence permits us to take a peek at what MySQL is doing behind the scenes.
Configuration File and Command-Line Options
mysqld can receive configuration variable settings on the command line, or it can read them from configuration files. There can be multiple configuration files, and their contents can be merged with the command-line configuration options. The first file to be examined is /etc/my.cnf.
The configuration file follows the format informally defined as follows:
[section_name]
option_name=option_value#comment
option_name=option_value
#comment
option_with_no_argument
Interesting Aspects of Specific Configuration Variables
big-tables
The MySQL optimizer tries as hard as it can to avoid using a temporary table when resolving a query. However, in some cases this grim task just has to be done. Then, if at all possible, it will try to use an in-memory temporary table. Unfortunately, the size of the table cannot always be estimated in advance. Sometimes in the process of populating the table, the maximum in-memory table size limit is reached (the limit is controlled by the tmp_table_size setting). When this happens, the temporary table needs to be converted to a disk type (i.e., MyISAM). This means re-creating the table and repopulating it with the rows collected in the in-memory table up to this point.
For a typical MySQL usage, the need to convert an in-memory table to disk is a rare occurrence. However, there are applications that run into this situation a lot. If you know in advance that the temporary result is going to be more than can be stored in memory, the big-tables option comes in handy. It tells the server to not even bother creating an in-memory table and to start with a disk-based table right away.
concurrent-insert
A frequent complaint about MyISAM tables, especially back in the early days, was that the use of table locks (as opposed to only row or page locks) caused serious performance degradation due to unnecessarily high lock contention. The degradation in performance under high load was believed to be caused by the lock contention. The problem was attributed to the inability of LinuxThreads to deal efficiently with frequently acquired and released mutexes, something MySQL server had to do a lot of. After a patch was applied to LinuxThreads, the benchmarks that performed a heavy mix of reads and write scaled just fine as long as both types of queries were properly optimized.
While the general case for a minimal conflict type of lock would have been fairly difficult, in one special case the lock contention could be minimized with only a few changes to the code. When a record is inserted into the table, the MyISAM storage engine first tries to find a previously deleted record whose space is large enough for the new record and overwrites that space with the new record. However, if there are no records marked as deleted, the
record is written at the end of the data file. In the latter case, it turned out not to be so difficult to allow the INSERT and the SELECT operations to proceed concurrently.
When this option is enabled, the MyISAM storage engine attempts to use this optimization whenever possible.
core-file
Debugging a threaded program can be quite a bit of a challenge. It is even more challenging when a crash happens, but no core file is produced. Sometimes you need that core file badly, as the crash cannot be duplicated in a debugger. And some platforms are not particularly anxious to generate a core file when threads are used.
In the unfortunate event of a crash, this option will engage the full power of the voodoo black magic known as MySQL in order to coax the uncooperative kernel to write out a core file.
default-storage-engine
In the past, this option was known as default-table-type, which is still supported. As MySQL AB made the transition from being a small company just trying to make a good database to a bigger entity trying to make an impression in the corporate world, it was discovered that IT managers respond to the term “storage engine” much better than “table type,” which is perhaps a more intuitive term for a MySQL hacker.
One type of storage engine, MEMORY, stores tables only in memory. MyISAM provides persistent storage and a number of fancy features such as full-text search and spatial indexing but does not have transactions or row-level locks. InnoDB provides transactions and row-level locks, but it is slower on some operations than MyISAM and requires more disk space. Depending on the need of your application, you can pick the right type of storage engine on a per-table basis. The storage engine can be specified when creating a table. If omitted, the one specified by default-storage-engine is used. It can also be changed for existing tables with the ALTER TABLE command. The default value of default-storage-engine is MyISAM.
delay-key-write
This option was added to optimize the queries that update keys ( INSERT, UPDATE, and DELETE ) in MyISAM tables. Normally, the server flushes the changed key blocks out of the MyISAM key cache at the end of every query. This could cause severe performance degradation in some cases.
One approach to this performance problem is to delay the key block flushing. When the key writes are delayed, the changed blocks are not flushed out at the end of a query. The flushing happens later under one of the following circumstances:
• All of the tables are removed from the table cache with FLUSH TABLES .
• The table is removed from the table cache with FLUSH TABLE .
• The table cache is flushed during server shutdown.
• The table is displaced from the table cache with a new table.
• The changed key blocks are displaced from the key cache with new blocks.
The advantage of using this option is the performance gain. The disadvantage is a higher risk of table corruption should a crash happen.
ft_stopword_file
MyISAM tables support full-text keys, which allow the storage engine to look up records quickly by words in the middle of character strings. In contrast, a regular B-tree index can only be used to look up records based on the entire value or at least a prefix of the key.
Full-text search capabilities are highly customizable. This option represents one of the many full-text search configuration options. During full-text indexing, in order to improve the quality of the index, some frequently used words are ignored. For example, if the text column contains regular English sentences, there is little value to indexing the word, as it will appear in an overwhelming majority of the records. Such words are called stop words.
innodb_buffer_pool_size
This buffer setting is one of the most important InnoDB variables. It controls how much memory is used to cache both InnoDB table data and indices. Note that InnoDB differs from MyISAM in the way the table data is cached. MyISAM caches only the keys, and simply hopes the OS will do a good job caching the data. InnoDB does not put any faith in the OS and takes the matter of caching the data into its own hands.
innodb_flush_log_at_trx_commit
InnoDB by design has much more stringent data safety requirements than MyISAM. It tries very hard to make sure that the data is still consistent with the absolute minimum loss even if you turn the power off in the middle of a transaction. However, a fine balance must be achieved between performance and data safety, and each application has its own standards.
InnoDB maintains a transaction log that is used for recovery during server startup. The recovery is attempted regardless of whether there was a crash or not. In the case of a crash, the log has pending transactions to redo. If there was no crash, no pending transactions are found in the log, so there is nothing to be done.
However, if the application performs short transactions frequently, this becomes a performance killer. Each log flush implies at least one disk write, and even with modern disks, you can only do so many of them per second, although InnoDB can group commits to overcome this limitation to a certain extent.
This problem can be addressed by slightly reducing the stringency of the data safety requirements, and flushing the log to disk only once per second. With this approach, under the assumption of intact disk I/O, our data is still consistent but could be up to one second old after the recovery. For many applications, this is a negligible risk and is worth the hundred-fold or so improvement in performance that comes from a dramatic reduction in disk writes.
innodb_file_per_table
MyISAM tables have had the advantage of easy backup and copying on a per-table basis without any involvement on the part of the server. This is possible because a MyISAM table is stored in three files: table_name .frm for the table definition, table_name.MYD for the data, and table_name.MYI for the keys.
When InnoDB was introduced into MySQL, many users missed the convenience of table manipulation on the file system level. However, version 4.1.1 added the ability to place each table in its own file.
When enabled, innodb_file_per_table causes new tables to have their index and data stored in a separate file, table_name .ibd. Nevertheless, this does not give the user the freedom to manipulate those files like MyISAM. As of this writing, InnoDB still stores a lot of meta-information in its global tablespace, which makes such manipulations impossible.
At this point, innodb_file_per_table only helps with backing up and restoring individual tables on the same server, and even that requires some tricks. The backup must be taken either when the server is down, or after all transactions have been committed and no new ones have started.
innodb_lock_wait_timeout
Unlike the MyISAM storage engine, which supports only table locks, InnoDB can lock individual records, which is known as row-level locking. This can bring great performance benefits for a wide variety of applications, but it unfortunately also introduces a problem: potential deadlocks. Let’s say, for example, that thread 1 acquires an exclusive lock on record A. In the meantime, thread 2 acquires an exclusive lock on record B. Thread 1 then, while still holding the lock on A, attempts to acquire a lock on B, but has to wait for thread 2 to release it. In the meantime, thread 2 is trying to lock record A while still holding the lock on B. Thus neither one can progress, and we have a deadlock condition.
Normally deadlocks are very rare, especially if the application was written with some awareness of the problem. Therefore, instead of preventing them, InnoDB just lets them happen, but it periodically runs a lock detection monitor that frees the deadlock “prisoner” threads and allows them to return
and report to the client that they have been aborted because they’ve been waiting for their lock longer than the value of this option.
innodb_force_recovery
There are times when the tablespace gets corrupted so badly that
the standard recovery algorithm fails. Usually, the fatal corruption destroys only a couple of pages, while the rest of the data is intact. It is, therefore, possible, perhaps by way of a semi-educated guess at times, to recover most of the lost data.
This option tells InnoDB how hard to try to recover the lost data. 0 means not to go beyond the standard recovery algorithm, while 6 means to bring the database up at all costs and then try hard to run the queries without crashing. If the value of this option is greater than 0, no queries that update the tables are allowed. The user is expected to dump the tables salvaging the data, and then re-create a clean tablespace and repopulate it.
init-file
This option runs a set of SQL commands from the specified file on the server startup. One of the possible uses is to load the data from disk-based tables into in-memory (i.e., MEMORY) tables for faster access. init-file can also be used to verify the integrity of certain data, perform a cleanup, ensure that the important tables exist, or do something else of that nature.
log
This option enables the general activity log, which records every command. Sometimes MySQL developers call it the query log. It is very helpful for debugging clients, but on the other hand, the log grows very fast on active servers, and therefore the option should be used with care.
log-bin
This option enables the update log in binary format (thus -bin in the name). It is primarily used for replication on a replication master, but it can also be used for incremental backup. The logging happens on the logical level; i.e., queries along with some meta information are being logged.
log-isam
This option keeps track of the low-level MyISAM storage engine operations, such as opening and closing tables, writing or reading the records, index file status queries and updates, and other functions.
log-slow-queries
This option enables the logging of queries that the optimizer believes are less than optimal. There are two criteria: execution time (controlled by the long_query_time option) and key use. If you ask a MySQL expert to help you troubleshoot a performance problem, probably the first thing he will tell you to do is to enable log-slow-queries along with log-queries-not-using-indexes ( log-long-format for versions older than 4.1), and examine it to account for every query that hits that log.
max_allowed_packet
MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory — a cap on the size of the packet, which this option accomplishes.
max_connections
Each new client connection consumes a certain amount of system resources. Many operating systems do not fare well when the resources are limited. This applies, particularly to memory. This option puts a cap on the number of maximum connections the server is willing to take. The idea is to have the MySQL server throttle itself down before it hijacks the system in case of some unexpected load spike.
max_heap_table_size
A heap table is MySQL jargon for an in-memory table. The name comes from the fact that it is allocated from the program’s heap. In-memory tables are very fast. However, they require a precaution — they can be quite easily populated to the point of having the system run out of memory. This option puts a cap on how big each in-memory table can get.
max_join_size
This is another option designed primarily to keep buggy applications and inexperienced users from taking the server down. It tells the optimizer to abort the queries that it believes would require it to examine more than the given number of record combinations.
max_sort_length
The MySQL record-sorting algorithm (known as filesort) uses fixed-size key values for sorting. This requires memory allocations in proportion to the maximum possible size of a given key. If sorting were to be done using the full length of a blob or text column, it could require enormous amounts of memory allocation, since those columns could potentially be as big as 4 GB (for a LONGBLOB ). To solve the problem, MySQL puts a limit on the length of the key prefix it will use for sorting. The trade-off is that the sort results are correct only to the prefix values.
myisam-recover
This option enables the automatic repair of corrupted MyISAM tables as soon as the MyISAM storage engine discovers the corruption. Normally corruption should never happen. However, power can fail, the operating system may crash or have a bug in the I/O code, and MySQL itself may crash or have a bug in the MyISAM storage engine. While the MyISAM tables lack the robustness of InnoDB for recovery from such crashes, most of the time even the most severe problems can be overcome with a table repair, often losing no more than just one record. The disadvantage is that this option could potentially trigger a large CPU- and I/O-intensive repair without your knowledge, making things a lot worse for the end-user during that time.
query_cache_type
MySQL has a fairly unique feature: it can cache the results of queries. One may ask why in the world an application would run the same query over and over on the data that has not changed. However, MySQL users reported on average about a 60 percent improvement in performance in their applications after this feature appeared for the first time in version 4.0.
This option sets the caching strategy. The possible values are 0 for no cache, 1 to cache all queries except the ones with the SQL_NO_CACHE flag, and 2 to cache only the ones with the SQL_CACHE flag.
read_buffer_size
Although the MyISAM storage engine does not cache data rows in general, a read-ahead buffer is used when performing sequential scans. This option controls its size.
relay-log
MySQL replication uses a master/slave paradigm. The master logs its updates. The slave stays connected to the master and continuously reads the contents of the master update log, known in MySQL jargon as the binary log. The slave then applies the updates it reads from the master to its copy of the data and thus is able to stay in sync. The slave now has two threads: one for network I/O, and the other for applying the SQL updates. The I/O thread reads the updates from the master and appends them to the so-called relay log. The SQL thread in turn reads the contents of the relay log and applies them to the slave data.
server-id
This option assigns a numeric ID to the server to be identified among its replication peers on the network. The need for it arose from the following situation. Suppose server A is a slave of server B, which in turn is a slave of server C, which is a slave of server A. An update happens on server A. B picks it up from the binary log of A, applies it and logs it to its own binary log. C picks it up from the binary log of B, applies it, and again logs it to its own binary log. Then A sees it in the binary log of C. It should not apply it. There has to be some way to tell A that the update it sees in the binary log of C originated from A, and therefore should be ignored. The solution was to assign each server participating in replication a unique 32-bit ID, similar in concept to an IP address. Each binary log event is tagged with the ID of the
server that originated it.This breaks potentially infinite update loops in a circular replication topology.
skip-grant-tables
This option tells the server to start without loading the access privilege tables. This means two things. First, they do not need to exist. Second, since they are not used, the server will positively authenticate any set of credentials from any host that can establish a connection to the server. This option is particularly useful when you have lost the MySQL root user password. You can start the server with skip-grant-tables, connect to it, use SQL statements to manually edit the privilege tables, and then either issue FLUSH PRIVILEGES or just restart the server.
skip-stack-trace
No matter how hard you try to avoid them, crashes happen. Having proper debugging information is critical to making sure the same type of crash does not happen again. The MySQL server binary is capable of unwinding its own
stack and printing the stack trace when it receives a fatal signal such as SIGSEGV. In addition, the postmortem diagnostic message includes the query that was executed as well as the settings of the variables that are most likely to cause a crash. If the server crashed already, the memory could very well be seriously corrupted, making the reported data absolutely bogus.
By default, the stack tracing takes place when a fatal signal is received; however, sometimes it is not desirable (e.g., if you are trying to debug the crash in a debugger). This option turns off this post-crash self-diagnostic.
slave-skip-errors
The slave replication algorithm was originally designed to stop replicating if an error was encountered when the replicated query failed on the slave. Indeed, if it succeeded on the master, and the slave has the same data as the master did when it succeeded, there is no reason for it to fail. This approach, however, proved undesirable in many situations. A table may contain millions of records, if one record is incorrect or gone altogether, the problem can be fixed manually or even simply ignored. In those situations, it is more important for the replication to progress in a timely manner than for
the data on the slave to always be a perfect replica of the master. And if this is a priority, errors such as a duplicate key error can be simply ignored as the replication continues.
This option tells the slave server which error codes it should ignore. The error codes to ignore can be specified in a comma-delimited list, or one could just use the keyword all to ignore all errors.
sort_buffer_size
This option indirectly controls the size of the chunk sorted in memory with the radix sort by specifying how much memory the radix sort is allowed to use.
sql-mode
This option is an accent adjuster. By setting it to different values, you can tell MySQL that a REAL is an alias for FLOAT instead of DOUBLE; space is allowed between the database and table names; || means string concatenation rather logical OR; and other tweaks needed to port an application from some other database to MySQL without changes in its code.
table_cache
table_cache is one of the core parts of the MySQL code. It caches table descriptors, which greatly increases the speed of the queries. Each time a table is referenced in query, the table cache may already have the needed descriptor, and the expensive operation of initializing one does not need to be done. This option controls how many table descriptors (not tables!) can be cached at the same time. You can view the contents of the table cache with the SHOW OPEN TABLES command.
temp-pool
When a process repeatedly creates and removes files with unique names, the kernel ends up allocating large amounts of memory that it never releases. MySQL may on occasion need to create a temporary file to resolve a query. On a large site with a lot of traffic and a wide diversity of queries, this may take place frequently enough to cause serious problems. For most users, it did not until MySQL was put to use on one very loaded site with a number of frequently executing, sophisticated queries. MySQL developers responded with a workaround by adding an option to limit the possibilities for the name of the temporary table to a smaller set of names.
transaction-isolation
This option was primarily the result of the introduction of InnoDB into the MySQL codebase. When two or more different transactions occur in parallel, there are several different models or sets of rules for what a read operation should return when some data was written by another transaction but not yet committed. This set of rules is known by the term transaction isolation level. Many transactional engines, including InnoDB, give the user an option to select a desired transaction isolation level for a given transaction. This option allows you to set a global transaction isolation level for the whole server.
