Table of MySQL Parameters

Posted in: MySQL, Technical Track

I got tired of going all over the place to get information about mysqld variables, so I decided to make a summary of most of them (for version 5.0.27 linux). (Due to lack of horizontal space, I had to make two tables — the second one containing a short description of the variable.)

Corrections, additions, and amplifications are welcome. Enjoy!

Table 1: MySQL Variables

auto_increment_increment numeric OFFLINE
auto_increment_offset numeric OFFLINE
autocommit boolean SESSION
automatic_sp_privileges boolean GLOBAL
back_log numeric OFFLINE
basedir string OFFLINE
bdb_cache_size numeric OFFLINE
bdb_home string OFFLINE
bdb_log_buffer_size numeric OFFLINE
bdb_logdir string OFFLINE
bdb_max_lock numeric OFFLINE
bdb_shared_data boolean OFFLINE
bdb_tmpdir string OFFLINE
big_tables boolean SESSION
binlog_cache_size numeric GLOBAL
bulk_insert_buffer_size numeric GLOBAL SESSION
character_set_client string GLOBAL SESSION
character_set_connection string GLOBAL SESSION
character_set_database string OFFLINE
character_set_filesystem string GLOBAL SESSION
character_set_results string GLOBAL SESSION
character_set_server string GLOBAL SESSION
character_set_system string OFFLINE
character_sets_dir string OFFLINE
collation_connection string GLOBAL SESSION
collation_database string OFFLINE
collation_server string GLOBAL SESSION
completion_type numeric GLOBAL SESSION
concurrent_insert numeric GLOBAL
connect_timeout numeric GLOBAL
datadir string OFFLINE
date_format OFFLINE
datetime_format OFFLINE
default_week_format numeric GLOBAL SESSION
delay_key_write OFF | ON | ALL GLOBAL
delayed_insert_limit numeric GLOBAL
delayed_insert_timeout numeric GLOBAL
delayed_queue_size numeric GLOBAL
div_precision_increment numeric GLOBAL SESSION
engine_condition_pushdown boolean GLOBAL SESSION
error_count numeric SESSION
expire_logs_days numeric GLOBAL
flush boolean GLOBAL
flush_time numeric GLOBAL
foreign_key_checks boolean SESSION
ft_boolean_syntax string GLOBAL
ft_max_word_len numeric OFFLINE
ft_min_word_len numeric OFFLINE
ft_query_expansion_limit numeric OFFLINE
ft_stopword_file string OFFLINE
group_concat_max_len numeric GLOBAL SESSION
have_archive boolean OFFLINE
have_bdb boolean OFFLINE
have_blackhole_engine boolean OFFLINE
have_compress boolean OFFLINE
have_crypt boolean OFFLINE
have_csv boolean OFFLINE
have_dynamic_loading boolean OFFLINE
have_example_engine boolean OFFLINE
have_federated_engine boolean OFFLINE
have_geometry boolean OFFLINE
have_innodb boolean OFFLINE
have_isam boolean OFFLINE
have_merge_engine boolean OFFLINE
have_ndbcluster boolean OFFLINE
have_openssl boolean OFFLINE
have_query_cache boolean OFFLINE
have_raid boolean OFFLINE
have_rtree_keys boolean OFFLINE
have_symlink boolean OFFLINE
init_connect string OFFLINE
init_file string OFFLINE
init_slave string OFFLINE
innodb_additional_mem_pool_size numeric OFFLINE
innodb_autoextend_increment numeric GLOBAL
innodb_buffer_pool_awe_mem_mb numeric OFFLINE
innodb_buffer_pool_size numeric OFFLINE
innodb_checksums boolean OFFLINE
innodb_commit_concurrency numeric GLOBAL
innodb_concurrency_tickets numeric GLOBAL
innodb_data_file_path string OFFLINE
innodb_data_home_dir string OFFLINE
innodb_doublewrite boolean OFFLINE
innodb_fast_shutdown 0,1 or 2 OFFLINE
innodb_file_io_threads numeric OFFLINE
innodb_file_per_table boolean OFFLINE
innodb_flush_log_at_trx_commit 0,1 or 2 OFFLINE
innodb_flush_method Enum OFFLINE
innodb_force_recovery 1 to 6 OFFLINE
innodb_lock_wait_timeout numeric OFFLINE
innodb_locks_unsafe_for_binlog boolean OFFLINE
innodb_log_arch_dir string OFFLINE
innodb_log_archive OFFLINE
innodb_log_buffer_size numeric OFFLINE
innodb_log_file_size numeric OFFLINE
innodb_log_files_in_group numeric OFFLINE
innodb_log_group_home_dir string OFFLINE
innodb_max_dirty_pages_pct numeric GLOBAL
innodb_max_purge_lag numeric GLOBAL
innodb_mirrored_log_groups numeric OFFLINE
innodb_open_files numeric OFFLINE
innodb_support_xa boolean GLOBAL SESSION
innodb_sync_spin_loops numeric GLOBAL
innodb_table_locks boolean GLOBAL SESSION
innodb_thread_concurrency numeric GLOBAL
innodb_thread_sleep_delay numeric GLOBAL
interactive_timeout numeric GLOBAL SESSION
join_buffer_size numeric GLOBAL SESSION
key_buffer_size numeric GLOBAL
key_cache_age_threshold numeric OFFLINE
key_cache_block_size numeric OFFLINE
key_cache_division_limit 1 to 100 OFFLINE
language string OFFLINE
large_files_support boolean OFFLINE
large_page_size numeric OFFLINE
large_pages boolean OFFLINE
lc_time_names string GLOBAL SESSION
license string OFFLINE
local_infile boolean GLOBAL
locked_in_memory boolean OFFLINE
log boolean OFFLINE
log_bin boolean OFFLINE
log_bin_trust_function_creators boolean GLOBAL
log_error string OFFLINE
log_queries_not_using_indexes boolean GLOBAL
log_slave_updates boolean OFFLINE
log_slow_queries boolean OFFLINE
log_warnings numeric GLOBAL
long_query_time numeric GLOBAL SESSION
low_priority_updates boolean GLOBAL SESSION
lower_case_file_system boolean OFFLINE
lower_case_table_names numeric OFFLINE
max_allowed_packet numeric GLOBAL SESSION
max_binlog_cache_size numeric GLOBAL
max_binlog_size numeric GLOBAL
max_connect_errors numeric GLOBAL
max_connections numeric GLOBAL
max_delayed_threads numeric GLOBAL
max_error_count numeric GLOBAL SESSION
max_heap_table_size numeric GLOBAL SESSION
max_insert_delayed_threads numeric GLOBAL
max_join_size numeric GLOBAL SESSION
max_length_for_sort_data numeric OFFLINE
max_prepared_stmt_count numeric GLOBAL
max_relay_log_size numeric GLOBAL
max_seeks_for_key numeric GLOBAL SESSION
max_sort_length numeric GLOBAL SESSION
max_sp_recursion_depth numeric OFFLINE
max_tmp_tables numeric GLOBAL SESSION
max_user_connections numeric GLOBAL
max_write_lock_count numeric GLOBAL
multi_range_count numeric GLOBAL SESSION
myisam_data_pointer_size numeric GLOBAL
myisam_max_sort_file_size numeric GLOBAL SESSION
myisam_recover_options boolean OFFLINE
myisam_repair_threads numeric GLOBAL SESSION
myisam_sort_buffer_size numeric GLOBAL SESSION
myisam_stats_method enum GLOBAL SESSION
net_buffer_length numeric GLOBAL SESSION
net_read_timeout numeric GLOBAL SESSION
net_retry_count numeric GLOBAL SESSION
net_write_timeout numeric GLOBAL SESSION
new boolean OFFLINE
old_passwords boolean GLOBAL SESSION
open_files_limit numeric OFFLINE
optimizer_prune_level numeric GLOBAL SESSION
optimizer_search_depth numeric GLOBAL SESSION
pid_file string OFFLINE
port numeric OFFLINE
preload_buffer_size numeric GLOBAL SESSION
prepared_stmt_count numeric OFFLINE
protocol_version numeric OFFLINE
query_alloc_block_size numeric GLOBAL SESSION
query_cache_limit numeric GLOBAL
query_cache_min_res_unit numeric OFFLINE
query_cache_size numeric GLOBAL
query_cache_type enumeration GLOBAL SESSION
query_cache_wlock_invalidate boolean GLOBAL SESSION
query_prealloc_size numeric GLOBAL SESSION
range_alloc_block_size numeric GLOBAL SESSION
read_buffer_size numeric GLOBAL SESSION
read_only numeric GLOBAL
read_rnd_buffer_size numeric GLOBAL SESSION
relay_log_purge boolean OFFLINE
relay_log_space_limit numeric OFFLINE
rpl_recovery_rank numeric GLOBAL
secure_auth boolean GLOBAL
server_id numeric GLOBAL
skip_external_locking boolean OFFLINE
skip_networking boolean OFFLINE
skip_show_database boolean OFFLINE
slave_compressed_protocol boolean GLOBAL
slave_load_tmpdir string OFFLINE
slave_net_timeout numeric GLOBAL
slave_skip_errors boolean OFFLINE
slave_transaction_retries numeric GLOBAL
slow_launch_time numeric GLOBAL
socket string OFFLINE
sort_buffer_size numeric GLOBAL SESSION
sql_auto_is_null boolean SESSION
sql_big_selects boolean SESSION
sql_big_tables boolean SESSION
sql_buffer_result boolean SESSION
sql_log_bin boolean SESSION
sql_log_off boolean SESSION
sql_log_update boolean SESSION
sql_low_priority_updates boolean GLOBAL SESSION
sql_max_join_size numeric GLOBAL SESSION
sql_mode enumeration GLOBAL SESSION
sql_notes boolean SESSION
sql_quote_show_create boolean SESSION
sql_safe_updates boolean SESSION
sql_select_limit numeric SESSION
sql_slave_skip_counter numeric GLOBAL
sql_warnings boolean SESSION
ssl_ca string OFFLINE
ssl_capath string OFFLINE
ssl_cert string OFFLINE
ssl_cipher string OFFLINE
ssl_key string OFFLINE
storage_engine enumeration GLOBAL SESSION
sync_binlog numeric GLOBAL
sync_frm boolean GLOBAL
system_time_zone string OFFLINE
table_cache numeric GLOBAL
table_lock_wait_timeout numeric OFFLINE
table_type enumeration GLOBAL SESSION
thread_cache_size numeric GLOBAL
thread_stack numeric OFFLINE
time_format OFFLINE
time_zone string GLOBAL SESSION
timed_mutexes boolean OFFLINE
tmp_table_size enumeration GLOBAL SESSION
tmpdir string OFFLINE
transaction_alloc_block_size numeric GLOBAL SESSION
transaction_prealloc_size numeric GLOBAL SESSION
tx_isolation enumeration GLOBAL SESSION
updatable_views_with_limit enumeration GLOBAL SESSION
version string OFFLINE
version_bdb string OFFLINE
version_comment string OFFLINE
version_compile_machine string OFFLINE
version_compile_os numeric OFFLINE
wait_timeout numeric GLOBAL SESSION

Table 2: The comment column

Variable Name COMMENT
auto_increment_increment Controls the interval between successive column values
auto_increment_offset Determines the starting point for the AUTO_INCREMENT column value
autocommit Statement forms a single transaction on its own
automatic_sp_privileges Server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine
back_log Number of outstanding connection requests MySQL can have
basedir MySQL installation base directory
bdb_cache_size Size of the buffer allocated for caching indexes and rows for BDB tables
bdb_home Base directory for BDB tables
bdb_log_buffer_size Size of the buffer allocated for caching indexes and rows for BDB tables
bdb_logdir Directory where the BDB storage engine writes its log files
bdb_max_lock Maximum number of locks that can be active for a BDB table
bdb_shared_data Start Berkeley DB in multi-process mode
bdb_tmpdir BDB temporary file directory
big_tables Exactly the same as using SQL_BIG_TABLES for all queries
binlog_cache_size Size of the cache to hold the SQL statements for the binary log during a transaction
bulk_insert_buffer_size Limits the size of the cache tree in bytes per thread
character_set_client Character set for statements that arrive from the client
character_set_connection Character set used for literals that do not have a character set introducer and for number-to-string conversion
character_set_database Character set used by the default database
character_set_filesystem Used to interpret string literals that refer to filenames
character_set_results Character set used for returning query results to the client
character_set_server Server’s default character set
character_set_system Character set used by the server for storing identifiers
character_sets_dir Directory where character sets are installed
collation_connection Collation of the connection character set
collation_database Collation used by the default database
collation_server Server’s default collation
completion_type Transaction completion type
concurrent_insert Allows INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file
connect_timeout Number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake
datadir MySQL data directory
date_format Not implemented
datetime_format Not implemented
default_week_format Default mode value to use for the WEEK() function
delay_key_write ONLY MyISAM. If DELAY_KEY_WRITE is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the table is closed.
delayed_insert_limit After inserting <delayed_insert_limit> delayed rows, the INSERT DELAYED handler thread checks whether there are any SELECT statements pending. If so, it allows them to execute before continuing to insert delayed rows.
delayed_insert_timeout How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating
delayed_queue_size Per-table limit on the number of rows to queue when handling INSERT DELAYED statements
div_precision_increment Number of digits of precision by which to increase the result of division operations performed with the / operator
engine_condition_pushdown Applies to NDB
error_count Read-only variable
expire_logs_days Number of days for automatic binary log removal
flush If ON, the server flushes (synchronizes) all changes to disk after each SQL statement.
flush_time All tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk
foreign_key_checks Do not check for foreign key problems
ft_boolean_syntax List of operators supported by boolean full-text searches performed using IN BOOLEAN MODE
ft_max_word_len Maximum length of the word to be included in a FULLTEXT index
ft_min_word_len Minimum length of the word to be included in a FULLTEXT index
ft_query_expansion_limit Number of top matches to use for full-text searches performed using WITH QUERY EXPANSION
ft_stopword_file File from which to read the list of stopwords for full-text searches
group_concat_max_len Maximum allowed result length for the GROUP_CONCAT() function
have_archive YES if mysqld supports ARCHIVE tables, NO if not.
have_bdb YES if mysqld supports BDB tables. DISABLED if –skip-bdb is used
have_blackhole_engine YES if mysqld supports BLACKHOLE tables, NO if not
have_compress YES if the zlib compression library is available to the server, NO if not. If not, the COMPRESS() and UNCOMPRESS() functions cannot be used
have_crypt YES if the crypt() system call is available to the server, NO if not. If not, the ENCRYPT() function cannot be used
have_csv YES if mysqld supports CSV tables, NO if not
have_dynamic_loading YES if mysqld can dynamically load data
have_example_engine YES if mysqld supports EXAMPLE tables, NO if not
have_federated_engine YES if mysqld supports FEDERATED tables, NO if not
have_geometry YES if the server supports spatial data types, NO if not
have_innodb YES if mysqld supports InnoDB tables. DISABLED if –skip-innodb is used
have_isam For backward compatibility. Always NO because ISAM tables are no longer supported.
have_merge_engine YES if mysqld supports MERGE tables. DISABLED if –skip-merge is used
have_ndbcluster YES if mysqld supports NDB Cluster tables. DISABLED if –skip-ndbcluster is used
have_openssl YES if mysqld supports SSL connections, NO if not
have_query_cache YES if mysqld supports the query cache, NO if not
have_raid For backward compatibility. Always NO because RAID tables are no longer supported
have_rtree_keys YES if RTREE indexes are available, NO if not
have_symlink YES if symbolic link support is enabled, NO if not
init_connect String to be executed by the server for each client that connects
init_file Name of the file specified with the –init-file option when you start the server. This should be a file containing SQL statements that you want the server to execute when it starts
init_slave String to be executed by a slave server each time the SQL thread starts
innodb_additional_mem_pool_size Size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures
innodb_autoextend_increment Increment size (in MB) for extending the size of an auto-extending tablespace when it becomes full
innodb_buffer_pool_awe_mem_mb Size of the buffer pool (in MB), if it is placed in the AWE memory. Relevant only in 32-bit Windows
innodb_buffer_pool_size Size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables
innodb_checksums InnoDB can use checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files
innodb_commit_concurrency Number of threads that can commit at the same time. A value of 0 disables concurrency control.
innodb_concurrency_tickets Number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable
innodb_data_file_path Paths to individual data files and their sizes
innodb_data_home_dir Common part of the directory path for all InnoDB data files
innodb_doublewrite InnoDB stores all data twice, first to the doublewrite buffer, and then to the actual data files
innodb_fast_shutdown Full purge and an insert buffer merge before a shutdown OR NOT
innodb_file_io_threads Number of file I/O threads in InnoDB
innodb_file_per_table If this variable is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the shared tablespace
innodb_flush_log_at_trx_commit See documentation
innodb_flush_method See documentation
innodb_force_recovery Crash recovery mode
innodb_lock_wait_timeout Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back
innodb_locks_unsafe_for_binlog Controls next-key locking in InnoDB searches and index scans
innodb_log_arch_dir Path to arch dir
innodb_log_archive Present for historical reasons, but unused
innodb_log_buffer_size Size in bytes of the buffer that InnoDB uses to write to the log files on disk
innodb_log_file_size Size in bytes of each log file in a log group
innodb_log_files_in_group Number of log files in the log group
innodb_log_group_home_dir Directory path to the InnoDB log files
innodb_max_dirty_pages_pct Main thread in InnoDB tries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value
innodb_max_purge_lag Controls how to delay INSERT, UPDATE and DELETE operations when the purge operations are lagging
innodb_mirrored_log_groups Number of identical copies of log groups to keep for the database
innodb_open_files Relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of .ibd files that InnoDB can keep open at one time
innodb_support_xa Enables InnoDB support for two-phase commit in XA transactions
innodb_sync_spin_loops Number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended
innodb_table_locks If 1, means that LOCK TABLES causes InnoDB to lock a table internally if AUTOCOMMIT=0
innodb_thread_concurrency InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to this limit
innodb_thread_sleep_delay How long InnoDB threads sleep before joining the InnoDB queue, in microseconds
interactive_timeout Number of seconds the server waits for activity on an interactive connection before closing it
join_buffer_size Size of the buffer used for joins that do not use indexes and thus perform full table scans
key_buffer_size Size of the buffer used for index blocks (also known as the key cache)
key_cache_age_threshold Controls the demotion of buffers from the hot sub-chain of a key cache to the warm sub-chain
key_cache_block_size Size in bytes of blocks in the key cache
key_cache_division_limit Division point between the hot and warm sub-chains of the key cache buffer chain. The value is the percentage of the buffer chain to use for the warm sub-chain
language Language used for error messages
large_files_support Whether mysqld was compiled with options for large file support
large_pages Whether large page support is enabled
lc_time_names Specifies the locale that controls the language used to display day and month names and abbreviations
license Type of license the server has
local_infile Whether LOCAL is supported for LOAD DATA INFILE statements
locked_in_memory Whether mysqld was locked in memory with –memlock
log Whether logging of all statements to the general query log is enabled
log_bin Whether the binary log is enabled
log_bin_trust_function_creators Applies when binary logging is enabled. Controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log.
log_error Location of the error log
log_queries_not_using_indexes Whether queries that do not use indexes are logged to the slow query log
log_slave_updates Whether updates received by a slave server from a master server should be logged to the slave’s own binary log
log_slow_queries Whether slow queries should be logged
log_warnings Whether to produce additional warning messages
long_query_time If a query takes longer than this many seconds, the query is logged to the slow query log file
low_priority_updates If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table
lower_case_file_system Describes the case sensitivity of filenames on the filesystem where the data directory is located
lower_case_table_names If set to 1, table names are stored in lowercase on disk and table name comparisons are not case sensitive. If set to 2 table names are stored as given but compared in lowercase
max_allowed_packet Maximum size of one packet or any generated/intermediate string
max_binlog_cache_size If a multiple-statement transaction requires more than this many bytes of memory, the server generates a multi-statement transaction requiring more than ‘max_binlog_cache_size’ bytes of storage error
max_binlog_size If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one)
max_connect_errors If there are more than this number of interrupted connections from a host, that host is blocked from further connections
max_connections Number of simultaneous client connections allowed
max_delayed_threads Do not start more than this number of threads to handle INSERT DELAYED statements
max_error_count Maximum number of error, warning, and note messages to be stored for display by the SHOW ERRORS and SHOW WARNINGS statements
max_heap_table_size Sets the maximum size to which MEMORY tables are allowed to grow
max_insert_delayed_threads Synonym for max_delayed_threads
max_join_size Do not allow SELECT statements that probably need to examine more than max_join_size rows (for single-table statements) or row combinations (for multiple-table statements) or that are likely to do more than max_join_size disk seeks
max_length_for_sort_data Cutoff on the size of index values that determines which filesort algorithm to use
max_prepared_stmt_count Limits the total number of prepared statements in the server
max_relay_log_size If a write by a replication slave to its relay log causes the current log file size to exceed the value of this variable, the slave rotates the relay logs (closes the current file and opens the next one)
max_seeks_for_key Limit the assumed maximum number of seeks when looking up rows based on a key
max_sort_length Number of bytes to use when sorting BLOB or TEXT values
max_sp_recursion_depth Number of times that a stored procedure may call itself
max_tmp_tables Maximum number of temporary tables a client can keep open at the same time
max_user_connections Maximum number of simultaneous connections allowed to any given MySQL account
max_write_lock_count After this many write locks, allow some pending read lock requests to be processed in between
multi_range_count Maximum number of ranges to send to a table handler at once during range selects
myisam_data_pointer_size Default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified
myisam_max_sort_file_size Maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE)
myisam_recover_options Value of the –myisam-recover option
myisam_repair_threads If greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process
myisam_sort_buffer_size Size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE
myisam_stats_method How the server treats NULL values when collecting statistics about the distribution of index values for MyISAM tables
net_buffer_length Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed
net_read_timeout Number of seconds to wait for more data from a connection before aborting the read
net_retry_count If a read on a communication port is interrupted, retry this many times before giving up
net_write_timeout Number of seconds to wait for a block to be written to a connection before aborting the write
new Retained for backward compatibility
old_passwords Whether the server should use pre-4.1-style passwords for MySQL user accounts
open_files_limit Number of files that the operating system allows mysqld to open
optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space
optimizer_search_depth Maximum depth of search performed by the query optimizer
pid_file Pathname of the process ID (PID) file
port Number of the port on which the server listens for TCP/IP connections
preload_buffer_size Size of the buffer that is allocated when preloading indexes
prepared_stmt_count Current number of prepared statements
protocol_version Version of the client/server protocol used by the MySQL server
query_alloc_block_size Allocation size of memory blocks that are allocated for objects created during statement parsing and execution
query_cache_limit Don’t cache results that are larger than this number of bytes
query_cache_min_res_unit Minimum size (in bytes) for blocks allocated by the query cache
query_cache_size Amount of memory allocated for caching query results
query_cache_type Sets the query cache type
query_cache_wlock_invalidate Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to the table. This forces other clients that attempt to access the table to wait while the lock is in effect.
query_prealloc_size Size of the persistent buffer used for statement parsing and execution
range_alloc_block_size Size of blocks allocated when doing range optimization
read_buffer_size Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans
read_only When this variable is set to ON, the server allows no updates except from users that have the SUPER privilege, or (on a slave server) from updates performed by slave threads
read_rnd_buffer_size When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks
relay_log_purge Disables or enables automatic purging of relay log files as soon as they are no longer needed
relay_log_space_limit Places an upper limit on the total size in bytes of all relay logs on the slave
rpl_recovery_rank Variable is unused
secure_auth Blocks connections from all accounts that have passwords stored in the old (pre-4.1) format
server_id Used for replication to enable master and slave servers to identify themselves uniquely
skip_external_locking This is OFF if mysqld uses external locking, ON if external locking is disabled
skip_networking This is ON if the server allows only local (non-TCP/IP) connections
skip_show_database Prevents users from using the SHOW DATABASES statement if they do not have the SHOW DATABASES privilege
slave_compressed_protocol Whether to use compression of the slave/master protocol if both the slave and the master support it
slave_load_tmpdir Name of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements
slave_net_timeout Number of seconds to wait for more data from a master/slave connection before aborting the read
slave_skip_errors Replication errors that the slave should skip (ignore)
slave_transaction_retries If a replication slave SQL thread fails to execute a transaction, it automatically retries <slave_transaction_retries> times before stopping with an error
slow_launch_time If creating a thread takes longer than this many seconds, the server increments the Slow_launch_threads status variable.
socket On Unix platforms, this variable is the name of the socket file that is used for local client connections
sort_buffer_size Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations.
sql_auto_is_null If set to 1 (the default), you can find the last inserted row for a table that contains an AUTO_INCREMENT column.
sql_big_selects If set to 0, MySQL aborts SELECT statements that are likely to take a very long time to execute (that is, statements for which the optimizer estimates that the number of examined rows exceeds the value of max_join_size).
sql_big_tables Renamed BIG_TABLES
sql_buffer_result If set to 1, SQL_BUFFER_RESULT forces results from SELECT statements to be put into temporary tables.
sql_log_bin If set to 0, no logging is done to the binary log for the client. The client must have the SUPER privilege to set this option.
sql_log_off If set to 1, no logging is done to the general query log for this client. The client must have the SUPER privilege to set this option.
sql_log_update Deprecated, and is mapped to SQL_LOG_BIN
sql_low_priority_updates Renamed low_priority_updates
sql_max_join_size Renamed max_join_size
sql_mode Current server SQL mode
sql_notes If set to 1 (the default), warnings of Note level are recorded. If set to 0, Note warnings are suppressed
sql_quote_show_create If set to 1 (the default), the server quotes identifiers for SHOW CREATE TABLE and SHOW CREATE DATABASE statements. If set to 0, quoting is disabled
sql_safe_updates If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause
sql_select_limit Maximum number of rows to return from SELECT statements
sql_slave_skip_counter Number of events from the master that a slave server should skip
sql_warnings Controls whether single-row INSERT statements produce an information string if warnings occur
ssl_ca Path to a file with a list of trusted SSL CAs
ssl_capath Path to a directory that contains trusted SSL CA certificates in PEM format
ssl_cert Name of the SSL certificate file to use for establishing a secure connection
ssl_cipher List of allowable ciphers to use for SSL encryption
ssl_key Name of the SSL key file to use for establishing a secure connection
storage_engine Default storage engine (table type)
sync_binlog If the value is positive, the MySQL server synchronizes its binary log to disk (fdatasync()) after every sync_binlog writes to this binary log.
sync_frm If set to 1, when any non-temporary table is created its .frm file is synchronized to disk (using fdatasync())
system_time_zone Server system time zone
table_cache Number of open tables for all threads
table_lock_wait_timeout Specifies a wait timeout for table-level locks, in seconds
table_type Synonym for storage_engine
thread_cache_size How many threads the server should cache for reuse
thread_stack Stack size for each thread
time_format Not implemented
time_zone Current time zone. Used to initialize the time zone for each client that connects.
timed_mutexes Controls whether InnoDB mutexes are timed
tmp_table_size Maximum size of in-memory temporary tables
tmpdir Directory used for temporary files and temporary tables
transaction_alloc_block_size Amount in bytes by which to increase a per-transaction memory pool that needs memory
transaction_prealloc_size See documentation
tx_isolation Default transaction isolation level
updatable_views_with_limit Controls whether updates to a view can be made when the view does not contain all columns of the primary key defined in the underlying table, if the update statement contains a LIMIT clause
version Version number for the server
version_bdb BDB storage engine version
version_comment Allows a comment to be specified when building MySQL
version_compile_machine Type of machine or architecture on which MySQL was built
version_compile_os Type of operating system on which MySQL was built
wait_timeout Number of seconds the server waits for activity on a non-interactive connection before closing it

Interested in working with Yannick? Schedule a tech call.

4 Comments. Leave new

Martin MC Brown
April 23, 2007 3:51 am


Nice tables, but we’ve had summary tables of this information in the MySQL online documentation since September 06 for 5.0/5.1 and have just added the 4.1 table:


Have you considered putting these into a DB install script to have them available in the information schema? It can prove awful handy in the DBMS itself. I’d be happy to put them into a script to put into docs if there was interest.


Very nice! I haven’t seen that before on the MySQL Online docs, and I do browse them often enough! Hopefully more people will find the links from here.


Martin MC Brown
April 23, 2007 1:59 pm


Yes funnily enough we have been discussing the potential for putting this information into the SQL help tables that the documentation team also produce.

I’ll keep you posted on when that happens.



Leave a Reply

Your email address will not be published. Required fields are marked *