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

Variable NameVariable TypeSET OFFLINE ONLYSET GLOBALSET SESSION
auto_increment_incrementnumericOFFLINE
auto_increment_offsetnumericOFFLINE
autocommitbooleanSESSION
automatic_sp_privilegesbooleanGLOBAL
back_lognumericOFFLINE
basedirstringOFFLINE
bdb_cache_sizenumericOFFLINE
bdb_homestringOFFLINE
bdb_log_buffer_sizenumericOFFLINE
bdb_logdirstringOFFLINE
bdb_max_locknumericOFFLINE
bdb_shared_databooleanOFFLINE
bdb_tmpdirstringOFFLINE
big_tablesbooleanSESSION
binlog_cache_sizenumericGLOBAL
bulk_insert_buffer_sizenumericGLOBALSESSION
character_set_clientstringGLOBALSESSION
character_set_connectionstringGLOBALSESSION
character_set_databasestringOFFLINE
character_set_filesystemstringGLOBALSESSION
character_set_resultsstringGLOBALSESSION
character_set_serverstringGLOBALSESSION
character_set_systemstringOFFLINE
character_sets_dirstringOFFLINE
collation_connectionstringGLOBALSESSION
collation_databasestringOFFLINE
collation_serverstringGLOBALSESSION
completion_typenumericGLOBALSESSION
concurrent_insertnumericGLOBAL
connect_timeoutnumericGLOBAL
datadirstringOFFLINE
date_formatOFFLINE
datetime_formatOFFLINE
default_week_formatnumericGLOBALSESSION
delay_key_writeOFF | ON | ALLGLOBAL
delayed_insert_limitnumericGLOBAL
delayed_insert_timeoutnumericGLOBAL
delayed_queue_sizenumericGLOBAL
div_precision_incrementnumericGLOBALSESSION
engine_condition_pushdownbooleanGLOBALSESSION
error_countnumericSESSION
expire_logs_daysnumericGLOBAL
flushbooleanGLOBAL
flush_timenumericGLOBAL
foreign_key_checksbooleanSESSION
ft_boolean_syntaxstringGLOBAL
ft_max_word_lennumericOFFLINE
ft_min_word_lennumericOFFLINE
ft_query_expansion_limitnumericOFFLINE
ft_stopword_filestringOFFLINE
group_concat_max_lennumericGLOBALSESSION
have_archivebooleanOFFLINE
have_bdbbooleanOFFLINE
have_blackhole_enginebooleanOFFLINE
have_compressbooleanOFFLINE
have_cryptbooleanOFFLINE
have_csvbooleanOFFLINE
have_dynamic_loadingbooleanOFFLINE
have_example_enginebooleanOFFLINE
have_federated_enginebooleanOFFLINE
have_geometrybooleanOFFLINE
have_innodbbooleanOFFLINE
have_isambooleanOFFLINE
have_merge_enginebooleanOFFLINE
have_ndbclusterbooleanOFFLINE
have_opensslbooleanOFFLINE
have_query_cachebooleanOFFLINE
have_raidbooleanOFFLINE
have_rtree_keysbooleanOFFLINE
have_symlinkbooleanOFFLINE
init_connectstringOFFLINE
init_filestringOFFLINE
init_slavestringOFFLINE
innodb_additional_mem_pool_sizenumericOFFLINE
innodb_autoextend_incrementnumericGLOBAL
innodb_buffer_pool_awe_mem_mbnumericOFFLINE
innodb_buffer_pool_sizenumericOFFLINE
innodb_checksumsbooleanOFFLINE
innodb_commit_concurrencynumericGLOBAL
innodb_concurrency_ticketsnumericGLOBAL
innodb_data_file_pathstringOFFLINE
innodb_data_home_dirstringOFFLINE
innodb_doublewritebooleanOFFLINE
innodb_fast_shutdown0,1 or 2OFFLINE
innodb_file_io_threadsnumericOFFLINE
innodb_file_per_tablebooleanOFFLINE
innodb_flush_log_at_trx_commit0,1 or 2OFFLINE
innodb_flush_methodEnumOFFLINE
innodb_force_recovery1 to 6OFFLINE
innodb_lock_wait_timeoutnumericOFFLINE
innodb_locks_unsafe_for_binlogbooleanOFFLINE
innodb_log_arch_dirstringOFFLINE
innodb_log_archiveOFFLINE
innodb_log_buffer_sizenumericOFFLINE
innodb_log_file_sizenumericOFFLINE
innodb_log_files_in_groupnumericOFFLINE
innodb_log_group_home_dirstringOFFLINE
innodb_max_dirty_pages_pctnumericGLOBAL
innodb_max_purge_lagnumericGLOBAL
innodb_mirrored_log_groupsnumericOFFLINE
innodb_open_filesnumericOFFLINE
innodb_support_xabooleanGLOBALSESSION
innodb_sync_spin_loopsnumericGLOBAL
innodb_table_locksbooleanGLOBALSESSION
innodb_thread_concurrencynumericGLOBAL
innodb_thread_sleep_delaynumericGLOBAL
interactive_timeoutnumericGLOBALSESSION
join_buffer_sizenumericGLOBALSESSION
key_buffer_sizenumericGLOBAL
key_cache_age_thresholdnumericOFFLINE
key_cache_block_sizenumericOFFLINE
key_cache_division_limit1 to 100OFFLINE
languagestringOFFLINE
large_files_supportbooleanOFFLINE
large_page_sizenumericOFFLINE
large_pagesbooleanOFFLINE
lc_time_namesstringGLOBALSESSION
licensestringOFFLINE
local_infilebooleanGLOBAL
locked_in_memorybooleanOFFLINE
logbooleanOFFLINE
log_binbooleanOFFLINE
log_bin_trust_function_creatorsbooleanGLOBAL
log_errorstringOFFLINE
log_queries_not_using_indexesbooleanGLOBAL
log_slave_updatesbooleanOFFLINE
log_slow_queriesbooleanOFFLINE
log_warningsnumericGLOBAL
long_query_timenumericGLOBALSESSION
low_priority_updatesbooleanGLOBALSESSION
lower_case_file_systembooleanOFFLINE
lower_case_table_namesnumericOFFLINE
max_allowed_packetnumericGLOBALSESSION
max_binlog_cache_sizenumericGLOBAL
max_binlog_sizenumericGLOBAL
max_connect_errorsnumericGLOBAL
max_connectionsnumericGLOBAL
max_delayed_threadsnumericGLOBAL
max_error_countnumericGLOBALSESSION
max_heap_table_sizenumericGLOBALSESSION
max_insert_delayed_threadsnumericGLOBAL
max_join_sizenumericGLOBALSESSION
max_length_for_sort_datanumericOFFLINE
max_prepared_stmt_countnumericGLOBAL
max_relay_log_sizenumericGLOBAL
max_seeks_for_keynumericGLOBALSESSION
max_sort_lengthnumericGLOBALSESSION
max_sp_recursion_depthnumericOFFLINE
max_tmp_tablesnumericGLOBALSESSION
max_user_connectionsnumericGLOBAL
max_write_lock_countnumericGLOBAL
multi_range_countnumericGLOBALSESSION
myisam_data_pointer_sizenumericGLOBAL
myisam_max_sort_file_sizenumericGLOBALSESSION
myisam_recover_optionsbooleanOFFLINE
myisam_repair_threadsnumericGLOBALSESSION
myisam_sort_buffer_sizenumericGLOBALSESSION
myisam_stats_methodenumGLOBALSESSION
net_buffer_lengthnumericGLOBALSESSION
net_read_timeoutnumericGLOBALSESSION
net_retry_countnumericGLOBALSESSION
net_write_timeoutnumericGLOBALSESSION
newbooleanOFFLINE
old_passwordsbooleanGLOBALSESSION
open_files_limitnumericOFFLINE
optimizer_prune_levelnumericGLOBALSESSION
optimizer_search_depthnumericGLOBALSESSION
pid_filestringOFFLINE
portnumericOFFLINE
preload_buffer_sizenumericGLOBALSESSION
prepared_stmt_countnumericOFFLINE
protocol_versionnumericOFFLINE
query_alloc_block_sizenumericGLOBALSESSION
query_cache_limitnumericGLOBAL
query_cache_min_res_unitnumericOFFLINE
query_cache_sizenumericGLOBAL
query_cache_typeenumerationGLOBALSESSION
query_cache_wlock_invalidatebooleanGLOBALSESSION
query_prealloc_sizenumericGLOBALSESSION
range_alloc_block_sizenumericGLOBALSESSION
read_buffer_sizenumericGLOBALSESSION
read_onlynumericGLOBAL
read_rnd_buffer_sizenumericGLOBALSESSION
relay_log_purgebooleanOFFLINE
relay_log_space_limitnumericOFFLINE
rpl_recovery_ranknumericGLOBAL
secure_authbooleanGLOBAL
server_idnumericGLOBAL
skip_external_lockingbooleanOFFLINE
skip_networkingbooleanOFFLINE
skip_show_databasebooleanOFFLINE
slave_compressed_protocolbooleanGLOBAL
slave_load_tmpdirstringOFFLINE
slave_net_timeoutnumericGLOBAL
slave_skip_errorsbooleanOFFLINE
slave_transaction_retriesnumericGLOBAL
slow_launch_timenumericGLOBAL
socketstringOFFLINE
sort_buffer_sizenumericGLOBALSESSION
sql_auto_is_nullbooleanSESSION
sql_big_selectsbooleanSESSION
sql_big_tablesbooleanSESSION
sql_buffer_resultbooleanSESSION
sql_log_binbooleanSESSION
sql_log_offbooleanSESSION
sql_log_updatebooleanSESSION
sql_low_priority_updatesbooleanGLOBALSESSION
sql_max_join_sizenumericGLOBALSESSION
sql_modeenumerationGLOBALSESSION
sql_notesbooleanSESSION
sql_quote_show_createbooleanSESSION
sql_safe_updatesbooleanSESSION
sql_select_limitnumericSESSION
sql_slave_skip_counternumericGLOBAL
sql_warningsbooleanSESSION
ssl_castringOFFLINE
ssl_capathstringOFFLINE
ssl_certstringOFFLINE
ssl_cipherstringOFFLINE
ssl_keystringOFFLINE
storage_engineenumerationGLOBALSESSION
sync_binlognumericGLOBAL
sync_frmbooleanGLOBAL
system_time_zonestringOFFLINE
table_cachenumericGLOBAL
table_lock_wait_timeoutnumericOFFLINE
table_typeenumerationGLOBALSESSION
thread_cache_sizenumericGLOBAL
thread_stacknumericOFFLINE
time_formatOFFLINE
time_zonestringGLOBALSESSION
timed_mutexesbooleanOFFLINE
tmp_table_sizeenumerationGLOBALSESSION
tmpdirstringOFFLINE
transaction_alloc_block_sizenumericGLOBALSESSION
transaction_prealloc_sizenumericGLOBALSESSION
tx_isolationenumerationGLOBALSESSION
updatable_views_with_limitenumerationGLOBALSESSION
versionstringOFFLINE
version_bdbstringOFFLINE
version_commentstringOFFLINE
version_compile_machinestringOFFLINE
version_compile_osnumericOFFLINE
wait_timeoutnumericGLOBALSESSION

Table 2: The comment column

Variable NameCOMMENT
auto_increment_incrementControls the interval between successive column values
auto_increment_offsetDetermines the starting point for the AUTO_INCREMENT column value
autocommitStatement forms a single transaction on its own
automatic_sp_privilegesServer 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_logNumber of outstanding connection requests MySQL can have
basedirMySQL installation base directory
bdb_cache_sizeSize of the buffer allocated for caching indexes and rows for BDB tables
bdb_homeBase directory for BDB tables
bdb_log_buffer_sizeSize of the buffer allocated for caching indexes and rows for BDB tables
bdb_logdirDirectory where the BDB storage engine writes its log files
bdb_max_lockMaximum number of locks that can be active for a BDB table
bdb_shared_dataStart Berkeley DB in multi-process mode
bdb_tmpdirBDB temporary file directory
big_tablesExactly the same as using SQL_BIG_TABLES for all queries
binlog_cache_sizeSize of the cache to hold the SQL statements for the binary log during a transaction
bulk_insert_buffer_sizeLimits the size of the cache tree in bytes per thread
character_set_clientCharacter set for statements that arrive from the client
character_set_connectionCharacter set used for literals that do not have a character set introducer and for number-to-string conversion
character_set_databaseCharacter set used by the default database
character_set_filesystemUsed to interpret string literals that refer to filenames
character_set_resultsCharacter set used for returning query results to the client
character_set_serverServer’s default character set
character_set_systemCharacter set used by the server for storing identifiers
character_sets_dirDirectory where character sets are installed
collation_connectionCollation of the connection character set
collation_databaseCollation used by the default database
collation_serverServer’s default collation
completion_typeTransaction completion type
concurrent_insertAllows INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file
connect_timeoutNumber of seconds that the mysqld server waits for a connect packet before responding with Bad handshake
datadirMySQL data directory
date_formatNot implemented
datetime_formatNot implemented
default_week_formatDefault mode value to use for the WEEK() function
delay_key_writeONLY 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_limitAfter 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_timeoutHow many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating
delayed_queue_sizePer-table limit on the number of rows to queue when handling INSERT DELAYED statements
div_precision_incrementNumber of digits of precision by which to increase the result of division operations performed with the / operator
engine_condition_pushdownApplies to NDB
error_countRead-only variable
expire_logs_daysNumber of days for automatic binary log removal
flushIf ON, the server flushes (synchronizes) all changes to disk after each SQL statement.
flush_timeAll tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk
foreign_key_checksDo not check for foreign key problems
ft_boolean_syntaxList of operators supported by boolean full-text searches performed using IN BOOLEAN MODE
ft_max_word_lenMaximum length of the word to be included in a FULLTEXT index
ft_min_word_lenMinimum length of the word to be included in a FULLTEXT index
ft_query_expansion_limitNumber of top matches to use for full-text searches performed using WITH QUERY EXPANSION
ft_stopword_fileFile from which to read the list of stopwords for full-text searches
group_concat_max_lenMaximum allowed result length for the GROUP_CONCAT() function
have_archiveYES if mysqld supports ARCHIVE tables, NO if not.
have_bdbYES if mysqld supports BDB tables. DISABLED if –skip-bdb is used
have_blackhole_engineYES if mysqld supports BLACKHOLE tables, NO if not
have_compressYES if the zlib compression library is available to the server, NO if not. If not, the COMPRESS() and UNCOMPRESS() functions cannot be used
have_cryptYES if the crypt() system call is available to the server, NO if not. If not, the ENCRYPT() function cannot be used
have_csvYES if mysqld supports CSV tables, NO if not
have_dynamic_loadingYES if mysqld can dynamically load data
have_example_engineYES if mysqld supports EXAMPLE tables, NO if not
have_federated_engineYES if mysqld supports FEDERATED tables, NO if not
have_geometryYES if the server supports spatial data types, NO if not
have_innodbYES if mysqld supports InnoDB tables. DISABLED if –skip-innodb is used
have_isamFor backward compatibility. Always NO because ISAM tables are no longer supported.
have_merge_engineYES if mysqld supports MERGE tables. DISABLED if –skip-merge is used
have_ndbclusterYES if mysqld supports NDB Cluster tables. DISABLED if –skip-ndbcluster is used
have_opensslYES if mysqld supports SSL connections, NO if not
have_query_cacheYES if mysqld supports the query cache, NO if not
have_raidFor backward compatibility. Always NO because RAID tables are no longer supported
have_rtree_keysYES if RTREE indexes are available, NO if not
have_symlinkYES if symbolic link support is enabled, NO if not
init_connectString to be executed by the server for each client that connects
init_fileName 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_slaveString to be executed by a slave server each time the SQL thread starts
innodb_additional_mem_pool_sizeSize in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures
innodb_autoextend_incrementIncrement size (in MB) for extending the size of an auto-extending tablespace when it becomes full
innodb_buffer_pool_awe_mem_mbSize of the buffer pool (in MB), if it is placed in the AWE memory. Relevant only in 32-bit Windows
innodb_buffer_pool_sizeSize in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables
innodb_checksumsInnoDB can use checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files
innodb_commit_concurrencyNumber of threads that can commit at the same time. A value of 0 disables concurrency control.
innodb_concurrency_ticketsNumber of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable
innodb_data_file_pathPaths to individual data files and their sizes
innodb_data_home_dirCommon part of the directory path for all InnoDB data files
innodb_doublewriteInnoDB stores all data twice, first to the doublewrite buffer, and then to the actual data files
innodb_fast_shutdownFull purge and an insert buffer merge before a shutdown OR NOT
innodb_file_io_threadsNumber of file I/O threads in InnoDB
innodb_file_per_tableIf 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_commitSee documentation
innodb_flush_methodSee documentation
innodb_force_recoveryCrash recovery mode
innodb_lock_wait_timeoutTimeout in seconds an InnoDB transaction may wait for a lock before being rolled back
innodb_locks_unsafe_for_binlogControls next-key locking in InnoDB searches and index scans
innodb_log_arch_dirPath to arch dir
innodb_log_archivePresent for historical reasons, but unused
innodb_log_buffer_sizeSize in bytes of the buffer that InnoDB uses to write to the log files on disk
innodb_log_file_sizeSize in bytes of each log file in a log group
innodb_log_files_in_groupNumber of log files in the log group
innodb_log_group_home_dirDirectory path to the InnoDB log files
innodb_max_dirty_pages_pctMain 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_lagControls how to delay INSERT, UPDATE and DELETE operations when the purge operations are lagging
innodb_mirrored_log_groupsNumber of identical copies of log groups to keep for the database
innodb_open_filesRelevant 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_xaEnables InnoDB support for two-phase commit in XA transactions
innodb_sync_spin_loopsNumber of times a thread waits for an InnoDB mutex to be freed before the thread is suspended
innodb_table_locksIf 1, means that LOCK TABLES causes InnoDB to lock a table internally if AUTOCOMMIT=0
innodb_thread_concurrencyInnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to this limit
innodb_thread_sleep_delayHow long InnoDB threads sleep before joining the InnoDB queue, in microseconds
interactive_timeoutNumber of seconds the server waits for activity on an interactive connection before closing it
join_buffer_sizeSize of the buffer used for joins that do not use indexes and thus perform full table scans
key_buffer_sizeSize of the buffer used for index blocks (also known as the key cache)
key_cache_age_thresholdControls the demotion of buffers from the hot sub-chain of a key cache to the warm sub-chain
key_cache_block_sizeSize in bytes of blocks in the key cache
key_cache_division_limitDivision 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
languageLanguage used for error messages
large_files_supportWhether mysqld was compiled with options for large file support
large_page_size
large_pagesWhether large page support is enabled
lc_time_namesSpecifies the locale that controls the language used to display day and month names and abbreviations
licenseType of license the server has
local_infileWhether LOCAL is supported for LOAD DATA INFILE statements
locked_in_memoryWhether mysqld was locked in memory with –memlock
logWhether logging of all statements to the general query log is enabled
log_binWhether the binary log is enabled
log_bin_trust_function_creatorsApplies 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_errorLocation of the error log
log_queries_not_using_indexesWhether queries that do not use indexes are logged to the slow query log
log_slave_updatesWhether updates received by a slave server from a master server should be logged to the slave’s own binary log
log_slow_queriesWhether slow queries should be logged
log_warningsWhether to produce additional warning messages
long_query_timeIf a query takes longer than this many seconds, the query is logged to the slow query log file
low_priority_updatesIf 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_systemDescribes the case sensitivity of filenames on the filesystem where the data directory is located
lower_case_table_namesIf 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_packetMaximum size of one packet or any generated/intermediate string
max_binlog_cache_sizeIf 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_sizeIf 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_errorsIf there are more than this number of interrupted connections from a host, that host is blocked from further connections
max_connectionsNumber of simultaneous client connections allowed
max_delayed_threadsDo not start more than this number of threads to handle INSERT DELAYED statements
max_error_countMaximum number of error, warning, and note messages to be stored for display by the SHOW ERRORS and SHOW WARNINGS statements
max_heap_table_sizeSets the maximum size to which MEMORY tables are allowed to grow
max_insert_delayed_threadsSynonym for max_delayed_threads
max_join_sizeDo 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_dataCutoff on the size of index values that determines which filesort algorithm to use
max_prepared_stmt_countLimits the total number of prepared statements in the server
max_relay_log_sizeIf 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_keyLimit the assumed maximum number of seeks when looking up rows based on a key
max_sort_lengthNumber of bytes to use when sorting BLOB or TEXT values
max_sp_recursion_depthNumber of times that a stored procedure may call itself
max_tmp_tablesMaximum number of temporary tables a client can keep open at the same time
max_user_connectionsMaximum number of simultaneous connections allowed to any given MySQL account
max_write_lock_countAfter this many write locks, allow some pending read lock requests to be processed in between
multi_range_countMaximum number of ranges to send to a table handler at once during range selects
myisam_data_pointer_sizeDefault pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified
myisam_max_sort_file_sizeMaximum 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_optionsValue of the –myisam-recover option
myisam_repair_threadsIf 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_sizeSize 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_methodHow the server treats NULL values when collecting statistics about the distribution of index values for MyISAM tables
net_buffer_lengthEach 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_timeoutNumber of seconds to wait for more data from a connection before aborting the read
net_retry_countIf a read on a communication port is interrupted, retry this many times before giving up
net_write_timeoutNumber of seconds to wait for a block to be written to a connection before aborting the write
newRetained for backward compatibility
old_passwordsWhether the server should use pre-4.1-style passwords for MySQL user accounts
open_files_limitNumber of files that the operating system allows mysqld to open
optimizer_prune_levelControls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space
optimizer_search_depthMaximum depth of search performed by the query optimizer
pid_filePathname of the process ID (PID) file
portNumber of the port on which the server listens for TCP/IP connections
preload_buffer_sizeSize of the buffer that is allocated when preloading indexes
prepared_stmt_countCurrent number of prepared statements
protocol_versionVersion of the client/server protocol used by the MySQL server
query_alloc_block_sizeAllocation size of memory blocks that are allocated for objects created during statement parsing and execution
query_cache_limitDon’t cache results that are larger than this number of bytes
query_cache_min_res_unitMinimum size (in bytes) for blocks allocated by the query cache
query_cache_sizeAmount of memory allocated for caching query results
query_cache_typeSets the query cache type
query_cache_wlock_invalidateSetting 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_sizeSize of the persistent buffer used for statement parsing and execution
range_alloc_block_sizeSize of blocks allocated when doing range optimization
read_buffer_sizeEach thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans
read_onlyWhen 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_sizeWhen reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks
relay_log_purgeDisables or enables automatic purging of relay log files as soon as they are no longer needed
relay_log_space_limitPlaces an upper limit on the total size in bytes of all relay logs on the slave
rpl_recovery_rankVariable is unused
secure_authBlocks connections from all accounts that have passwords stored in the old (pre-4.1) format
server_idUsed for replication to enable master and slave servers to identify themselves uniquely
skip_external_lockingThis is OFF if mysqld uses external locking, ON if external locking is disabled
skip_networkingThis is ON if the server allows only local (non-TCP/IP) connections
skip_show_databasePrevents users from using the SHOW DATABASES statement if they do not have the SHOW DATABASES privilege
slave_compressed_protocolWhether to use compression of the slave/master protocol if both the slave and the master support it
slave_load_tmpdirName of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements
slave_net_timeoutNumber of seconds to wait for more data from a master/slave connection before aborting the read
slave_skip_errorsReplication errors that the slave should skip (ignore)
slave_transaction_retriesIf a replication slave SQL thread fails to execute a transaction, it automatically retries <slave_transaction_retries> times before stopping with an error
slow_launch_timeIf creating a thread takes longer than this many seconds, the server increments the Slow_launch_threads status variable.
socketOn Unix platforms, this variable is the name of the socket file that is used for local client connections
sort_buffer_sizeEach 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_nullIf set to 1 (the default), you can find the last inserted row for a table that contains an AUTO_INCREMENT column.
sql_big_selectsIf 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_tablesRenamed BIG_TABLES
sql_buffer_resultIf set to 1, SQL_BUFFER_RESULT forces results from SELECT statements to be put into temporary tables.
sql_log_binIf 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_offIf 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_updateDeprecated, and is mapped to SQL_LOG_BIN
sql_low_priority_updatesRenamed low_priority_updates
sql_max_join_sizeRenamed max_join_size
sql_modeCurrent server SQL mode
sql_notesIf set to 1 (the default), warnings of Note level are recorded. If set to 0, Note warnings are suppressed
sql_quote_show_createIf 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_updatesIf 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_limitMaximum number of rows to return from SELECT statements
sql_slave_skip_counterNumber of events from the master that a slave server should skip
sql_warningsControls whether single-row INSERT statements produce an information string if warnings occur
ssl_caPath to a file with a list of trusted SSL CAs
ssl_capathPath to a directory that contains trusted SSL CA certificates in PEM format
ssl_certName of the SSL certificate file to use for establishing a secure connection
ssl_cipherList of allowable ciphers to use for SSL encryption
ssl_keyName of the SSL key file to use for establishing a secure connection
storage_engineDefault storage engine (table type)
sync_binlogIf the value is positive, the MySQL server synchronizes its binary log to disk (fdatasync()) after every sync_binlog writes to this binary log.
sync_frmIf set to 1, when any non-temporary table is created its .frm file is synchronized to disk (using fdatasync())
system_time_zoneServer system time zone
table_cacheNumber of open tables for all threads
table_lock_wait_timeoutSpecifies a wait timeout for table-level locks, in seconds
table_typeSynonym for storage_engine
thread_cache_sizeHow many threads the server should cache for reuse
thread_stackStack size for each thread
time_formatNot implemented
time_zoneCurrent time zone. Used to initialize the time zone for each client that connects.
timed_mutexesControls whether InnoDB mutexes are timed
tmp_table_sizeMaximum size of in-memory temporary tables
tmpdirDirectory used for temporary files and temporary tables
transaction_alloc_block_sizeAmount in bytes by which to increase a per-transaction memory pool that needs memory
transaction_prealloc_sizeSee documentation
tx_isolationDefault transaction isolation level
updatable_views_with_limitControls 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
versionVersion number for the server
version_bdbBDB storage engine version
version_commentAllows a comment to be specified when building MySQL
version_compile_machineType of machine or architecture on which MySQL was built
version_compile_osType of operating system on which MySQL was built
wait_timeoutNumber of seconds the server waits for activity on a non-interactive connection before closing it
email

Interested in working with Yannick? Schedule a tech call.

4 Comments. Leave new

Martin MC Brown
April 23, 2007 3:51 am

Hi,

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:

http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html
http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html
http://dev.mysql.com/doc/refman/4.1/en/mysqld-option-tables.html

Reply

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.

Reply

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.

–Raj.

Reply
Martin MC Brown
April 23, 2007 1:59 pm

Hi,

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.

MC

Reply

Leave a Reply

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