MySQL Server Variables – SQL layer or Storage Engine specific.Peter Zaitsev
MySQL Server has tons of variables which may be adjusted to change behavior or for performance purposes. They are documented in the manual as well as on new page Jay has created. Still I see constant confusion out where which of variables apply to storage engines only and which are used on SQL layer and apply to all storage engines.
Lets start with a bit of history. When MySQL was just written there was not that many storage engines – there was MyISAM for disk tables and HEAP for in memory tables. So first MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key_buffer” for example. The other excurse Monty used to make was – it is not really MyISAM specific. key_buffer was also used by now retired ISAM storage engine and could be used by something else. True in theory but in practice for years now when we speak about key_buffer we mean MyISAM.
The other important thing to consider is which storage engines you’re really using. So for example if you only have Innodb tables in your system does it mean MyISAM variables are not important ? Not really. You still have some tables in MyISAM format in “mysql” database. Plus HEAP/MEMORY tables are used for temporary tables which may become MyISAM if they are large enough. So even though you do not want to allocate many resources to MyISAM tables if you do not use them actively you might with to allocate some.
List of variables which are often confused.
bulk_insert_buffer_size – Used by MyISAM tables only to optimize bulk inserts (multiple value inserts). Really helps if you have really bulk inserts 100-1000+ values per single insert statement.
concurrent_insert – Enables concurrent insert (while Selects are running) for MyISAM storage engine. Other Storage engine ether always have it (ie Innodb) or do not support it (HEAP/MEMORY). Note in 5.0 you now have very nice value of 2 which allows concurrent insert to work also if tables have “holes” (deleted rows) which can help significantly.
delay_key_write Delay updating indexes for MyISAM tables. Good for performance but tables will be badly corrupted on crash.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size Delayed inserts configuration. Technically it is not storage engine dependent, in practice however not all storage engines support it. For example Innodb tables do not support them. So in practice it is MyISAM related.
expire_logs_days This is replication/binlogging related options – when to delete binary logs from master. It does not affect Innodb and other storage engines transaction logs, slow query log or general log.
flush, flush_time Flushing (closing) of tables after query is complete or every flush_time seconds. This is technically variable which affects all tables but it was designed for MyISAM tables. It makes little sense to use it with Innodb tables for example. Note “flush” means “close file” in this case it does not do fsync() or anything like it to make sure data hits the disk
ft_boolean_syntax, ft_max_word_len, ft_min_word_len, ft_query_expansion_limit, ft_stopword_file FullText search related variables. As FullText currently works with MyISAM tables these are MyISAM related.
join_buffer_size Buffer used for joins without indexes and few other cases. It is used by SQL layer so applies to all storage engines.
key_buffer_size Key Buffer used to buffer Index blocks (row data is not cached). Used only for MyISAM tables. If you do not use MyISAM tables still keep it 4-32MB to be used for temporary tables.
key_cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache/Key Buffer replacement algorithm configuration variables. Obviously only apply to MyISAM as key_buffer itself.
large_pages – Use of Large Pages for allocation of large global areas such as Innodb Buffer Pool, Key Buffer etc. It works for certain storage engines which support large pages in general. Currently MyISAM and Innodb. Some SQL level items as Query Cache also could use this feature.
low_priority_updates If enabled selects will get higher priority than updates and waiting update will not stall all new selects to the table until it is executed. It works for storage engines with table locks or if LOCK TABLES are used. So in practice this option is used for MyISAM and MEMORY tables.
max_write_lock_count Variable which may save you from read starvation if there are constant writes to the table. Works with table locks so again practically applies to MYISAM and MEMORY tables.
preload_buffer_size – Buffer used for Key preloading, which works for MYISAM tables only.
read_buffer_size, read_rnd_buffer_size – Variables used as read buffer for MyISAM tables for Full Table Scan (read_buffer) and for reading rows in sorted order (read_rnd_buffer_size). Other storage engines such as Innodb to not use this variable.
sort_buffer_size Buffer used to sort result set (allocated by each thread once sorting needs to be done) SQL Layer so works for all storage engines.
timed_mutexes Show statistics by mutexes in SHOW MUTEX STATUS. Was intended to work for all storage engines but currently implemented only for Innodb.
tmp_table_size This variable specifies maximum size implicit temporary table (created during query execution automatically) will grow as MEMORY table before it will be converted to on disk MyISAM table. It does not limit size of temporary table, neither it applies to tables created as TEMPORARY TABLE, even in memory tables.