If Opened_tables is big, then your table_cache variable is probably too small.

If Key_reads is big, then your key_buffer_size variable is probably too small. The cache miss rate can be calculated with Key_reads/Key_read_requests.

If Handler_read_rnd is big, then you probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

If Threads_created is big, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections.

If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size variable to get the temporary tables memory-based instead of disk based.

ansi_mode. Is ON if mysqld was started with -ansi. Section 1.7.2, “Running MySQL in ANSI Mode ”.

back_log The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time.

In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. The manual page for the Unix listen(2) system call should have more details. Check your OS documentation for the maximum value for this variable. Attempting to set back_log higher than your operating system limit will be ineffective.

basedir The value of the -basedir option.

bdb_cache_size The buffer that is allocated to cache index and rows for BDB tables. If you don't use BDB tables, you should start mysqld with -skip-bdb to not waste memory for this cache.

bdb_log_buffer_size The buffer that is allocated to cache index and rows for BDB tables. If you don't use BDB tables, you should set this to 0 or start mysqld with -skip-bdb to not waste memory for this cache.

bdb_home The value of the -bdb-home option.

bdb_max_lock The maximum number of locks (10,000 by default) you can have active on a BDB table. You should increase this if you get errors of type bdb: Lock table is out of available locks or Got error 12 from ... when you have do long transactions or when mysqld has to examine a lot of rows to calculate the query.

bdb_logdir The value of the -bdb-logdir option.

bdb_shared_data Is ON if you are using -bdb-shared-data.

bdb_tmpdir The value of the -bdb-tmpdir option.

binlog_cache_size. The size of the cache to hold the SQL statements for the binary log during a transaction. If you often use big, multi-statement transactions you can increase this to get more performance. Section 6.7.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax ”.

bulk_insert_buffer_size (was myisam_bulk_insert_tree_size) MyISAM uses special tree-like cache to make bulk inserts (that is, INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE) faster. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 will disable this optimisation. Note: this cache is only used when adding data to non-empty table. Default value is 8 MB.

character_set The default character set.

character_sets The supported character sets.

concurrent_inserts If ON (the default), MySQL will allow you to use INSERT on MyISAM tables at the same time as you run SELECT queries on them. You can turn this option off by starting mysqld with -safe or -skip-new.

connect_timeout The number of seconds the mysqld server is waiting for a connect packet before responding with Bad handshake.

datadir The value of the -datadir option.

delay_key_write Option for MyISAM tables. Can have one of the following values:

If DELAY_KEY_WRITE is enabled this means that the key buffer for tables with this option will not get flushed on every index update, but only when a table is closed. This will speed up writes on keys a lot, but you should add automatic checking of all tables with myisamchk -fast -force if you use this.

delayed_insert_limit After inserting delayed_insert_limit rows, the INSERT DELAYED handler will check if there are any SELECT statements pending. If so, it allows these to execute before continuing.

delayed_insert_timeout How long a INSERT DELAYED thread should wait for INSERT statements before terminating.

delayed_queue_size What size queue (in rows) should be allocated for handling INSERT DELAYED. If the queue becomes full, any client that does INSERT DELAYED will wait until there is room in the queue again.

flush This is ON if you have started MySQL with the -flush option.

flush_time If this is set to a non-zero value, then every flush_time seconds all tables will be closed (to free up resources and sync things to disk). We only recommend this option on Windows 9x/Me, or on systems where you have very little resources.

ft_boolean_syntax List of operators supported by MATCH ... AGAINST(... IN BOOLEAN MODE). Section 6.8, “MySQL Full-text Search ”.

ft_min_word_len The minimum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable. (This option is new for MySQL 4.0.)

ft_max_word_len The maximum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable. (This option is new for MySQL 4.0.)

ft_max_word_len_for_sort The maximum length of the word in a FULLTEXT index to be used in fast index recreation method in REPAIR, CREATE INDEX, or ALTER TABLE. Longer words are inserted the slow way. The rule of the thumb is as follows: with ft_max_word_len_for_sort increasing, MySQL will create bigger temporary files (thus slowing the process down, due to disk I/O), and will put fewer keys in one sort block (again, decreasing the efficiency). When ft_max_word_len_for_sort is too small, instead, MySQL will insert a lot of words into index the slow way, but short words will be inserted very quickly.

ft_stopword_file The file from which to read the list of stopwords for full-text searches. All the words from the file will be used; comments are not honored. By default, built-in list of stopwords is used (as defined in myisam/ft_static.c). Setting this parameter to an empty string ("") will disable stopword filtering. Note: FULLTEXT indexes must be rebuilt after changing this variable. (This option is new for MySQL 4.0.10)

have_innodbYES if mysqld supports InnoDB tables. DISABLED if -skip-innodb is used.

have_bdbYES if mysqld supports Berkeley DB tables. DISABLED if -skip-bdb is used.

have_raidYES if mysqld supports the RAID option.

have_opensslYES if mysqld supports SSL (encryption) on the client/server protocol.

init_file The name of the file specified with the -init-file option when you start the server. This is a file of SQL statements you want the server to execute when it starts.

interactive_timeout The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.

join_buffer_size The size of the buffer that is used for full joins (joins that do not use indexes). The buffer is allocated one time for each full join between two tables. Increase this value to get a faster full join when adding indexes is not possible. (Normally the best way to get fast joins is to add indexes.)

key_buffer_size Index blocks are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks.

Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common. If you, however, make this too big (for instance more than 50% of your total memory) your system may start to page and become extremely slow. Remember that because MySQL does not cache data reads, you will have to leave some room for the OS filesystem cache.

You can check the performance of the key buffer by doing SHOW STATUS and examine the variables Key_read_requests, Key_reads, Key_write_requests, and Key_writes. The Key_reads/Key_read_request ratio should normally be 0.01. The Key_write/Key_write_requests is usually near 1 if you are using mostly updates/deletes but may be much smaller if you tend to do updates that affect many at the same time or if you are using DELAY_KEY_WRITE. Section 4.5.7, “SHOW Syntax ”.

To get even more speed when writing many rows at the same time, use LOCK TABLES. Section 6.7.3, “LOCK TABLES and UNLOCK TABLES Syntax ”.

language The language used for error messages.

large_file_support If mysqld was compiled with options for big file support.

locked_in_memory If mysqld was locked in memory with -memlock

log If logging of all queries is enabled.

log_update If the update log is enabled.

log_bin If the binary log is enabled.

log_slave_updates If the updates from the slave should be logged.

long_query_time If a query takes longer than this (in seconds), the Slow_queries counter will be incremented. If you are using -log-slow-queries, the query will be logged to the slow query logfile. This value is measured in real time, not CPU time, so a query that may be under the threshold on a lightly loaded system may be above the threshold on a heavily loaded one. Section 4.9.5, “The Slow Query Log ”.

lower_case_table_names If set to 1 table names are stored in lowercase on disk and table name comparisons will be case-insensitive. From version 4.0.2, this option also applies to database names. From 4.1.1 this option also applies to table alias. Section 6.1.3, “Case Sensitivity in Names ”.

max_allowed_packet The maximum size of one packet. The message buffer is initialised to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use. The protocol limits for max_allowed_packet is 16M in MySQL 3.23 and 1G in MySQL 4.0.

max_binlog_cache_size If a multi-statement transaction requires more than this amount of memory, one will get the error "Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage".

max_binlog_size Available after 3.23.33. If a write to the binary (replication) log exceeds the given value, rotate the logs. You cannot set it to less than 4096 bytes (1024 in MySQL versions older than 4.0.14), or more than 1 GB. Default is 1 GB. Note if you are using transactions: a transaction is written in one chunk to the binary log, hence it is never split between several binary logs. Therefore, if you have big transactions, you may see binlogs bigger than max_binlog_size. If max_relay_log_size (available starting from MySQL 4.0.14) is 0, then max_binlog_size will apply to relay logs as well.

max_connections The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors that mysqld requires. See below for comments on file descriptor limits. Section A.2.6, “Too many connections Error ”.

max_connect_errors If there is more than this number of interrupted connections from a host this host will be blocked from further connections. You can unblock a host with the command FLUSH HOSTS.

max_delayed_threads Don't start more than this number of threads to handle INSERT DELAYED statements. If you try to insert data into a new table after all INSERT DELAYED threads are in use, the row will be inserted as if the DELAYED attribute wasn't specified. If you set this to 0, MySQL will never create a max_delayed thread.

max_heap_table_size Don't allow creation of heap tables bigger than this.

max_join_size Joins that are probably going to read more than max_join_size records return an error. Set this value if your users tend to perform joins that lack a WHERE clause, that take a long time, and that return millions of rows.

max_relay_log_size Available starting from 4.0.14. If a write to the relay log (a kind of log used by replication slaves, Section 4.10.3, “Replication Implementation Details ”) exceeds the given value, rotate the relay log. This variable enables you to put different size constraints on relay logs and binary logs. However, setting the variable to 0 will make MySQL use max_binlog_size for both binary logs and relay logs. You have to set max_relay_log_size to 0 or more than 4096, and less than 1 GB. Default is 0.

max_seeks_for_key Limit assumed max number of seeks when looking up rows based on a key. The MySQL optimiser will assume that when searching after matching rows in a table through scanning a key, we will not cause more than this number of key seeks independent of the cardinality of the key. By setting this to a low value (100 ?) you can force MySQL to prefer keys instead of table scans.

max_sort_length The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored).

max_user_connections The maximum number of active connections for a single user (0 = no limit).

max_tmp_tables (This option doesn't yet do anything.) Maximum number of temporary tables a client can keep open at the same time.

max_write_lock_count After this many write locks, allow some read locks to run in between.

myisam_recover_options The value of the -myisam-recover option.

myisam_sort_buffer_size The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE.

myisam_max_extra_sort_file_size. If the temporary file used for fast index creation would be bigger than using the key cache by the amount specified here, then prefer the key cache method. This is mainly used to force long character keys in large tables to use the slower key cache method to create the index. Note that this parameter is given in megabytes before 4.0.3 and in bytes beginning with this version.

myisam_repair_threads. If this value is greater than one, MyISAM table indexes during Repair by sorting process will be created in parallel - each index in its own thread. Note: multi-threaded repair is still alpha quality code.

myisam_max_sort_file_size The maximum size of the temporary file MySQL is allowed to use while recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. If the file-size would be bigger than this, the index will be created through the key cache (which is slower). Note that this parameter is given in megabytes before 4.0.3 and in bytes beginning with this version.

net_buffer_length The communication buffer is reset to this size between queries. This should not normally be changed, but if you have very little memory, you can set it to the expected size of a query. (That is, the expected length of SQL statements sent by clients. If statements exceed this length, the buffer is automatically enlarged, up to max_allowed_packet bytes.)

net_read_timeout Number of seconds to wait for more data from a connection before aborting the read. Note that when we don't expect data from a connection, the timeout is defined by write_timeout. See also slave_net_timeout.

net_retry_count If a read on a communication port is interrupted, retry this many times before giving up. This value should be quite high on FreeBSD as internal interrupts are sent to all threads.

net_write_timeout Number of seconds to wait for a block to be written to a connection before aborting the write.

open_files_limit Number of files the system allows mysqld to open. This is the real value given for the system and may be different from the value you gave mysqld as a startup parameter. This is 0 on systems where MySQL can't change the number of open files.

pid_file The value of the -pid-file option.

port The value of the -port option.

protocol_version The protocol version used by the MySQL server.

read_buffer_size (was record_buffer) Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value.

read_rnd_buffer_size (was record_rnd_buffer) When reading rows in sorted order after a sort, the rows are read through this buffer to avoid a disk seeks. Can improve ORDER BY by a lot if set to a high value. As this is a thread-specific variable, one should not set this big globally, but just change this when running some specific big queries.

query_cache_limit Don't cache results that are bigger than this. (Default 1M).

query_cache_size The memory allocated to store results from old queries. If this is 0, the query cache is disabled (default).

query_cache_type This may be set (only numeric) to

safe_show_database Don't show databases for which the user doesn't have any database or table privileges. This can improve security if you're concerned about people being able to see what databases other users have. See also skip_show_database.

server_id The value of the -server-id option.

skip_locking Is OFF if mysqld uses external locking.

skip_networking Is ON if we only allow local (socket) connections.

skip_show_database This prevents people from doing SHOW DATABASES if they don't have the PROCESS privilege. This can improve security if you're concerned about people being able to see what databases other users have. See also safe_show_database.

slave_net_timeout Number of seconds to wait for more data from a master/slave connection before aborting the read.

slow_launch_time If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented.

socket The Unix socket used by the server.

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. Section A.4.4, “Where MySQL Stores Temporary Files ”.

table_cache The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check if you need to increase the table cache by checking the Opened_tables variable. Section 4.5.7.3, “SHOW STATUS”. If this variable is big and you don't do FLUSH TABLES a lot (which just forces all tables to be closed and reopenend), then you should increase the value of this variable.

For more information about the table cache, see Section 5.4.7, “How MySQL Opens and Closes Tables ”.

table_type The default table type.

thread_cache_size How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created status variables (Section 4.5.7.3, “SHOW STATUS” for details) you can see how efficient thread cache is.

thread_concurrency On Solaris, mysqld will call thr_setconcurrency() with this value. thr_setconcurrency() permits the application to give the threads system a hint for the desired number of threads that should be run at the same time.

thread_stack The stack size for each thread. Many of the limits detected by the crash-me test are dependent on this value. The default is large enough for normal operation. Section 5.1.4, “The MySQL Benchmark Suite ”.

timezone The timezone for the server.

tmp_table_size If an in-memory temporary table exceeds this size, MySQL will automatically convert it to an on-disk MyISAM table. Increase the value of tmp_table_size if you do many advanced GROUP BY queries and you have lots of memory.

tmpdir The directory used for temporary files and temporary tables. Starting from MySQL 4.1, it can be set to a list of paths separated by colon : (semicolon ; on Windows). They will be used in round-robin fashion. This feature can be used to spread load between several physical disks.

version The version number for the server.

wait_timeout The number of seconds the server waits for activity on a not interactive connection before closing it.

On thread startup SESSION.WAIT_TIMEOUT is initialised from GLOBAL.WAIT_TIMEOUT or GLOBAL.INTERACTIVE_TIMEOUT depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option). See also interactive_timeout.

File shows the full path to the log file

Type shows the type of the log file (BDB for Berkeley DB log files)

Status shows the status of the log file (FREE if the file can be removed, or IN USE if the file is needed by the transaction subsystem)

Checking table The thread is performing [automatic] checking of the table.

Closing tables Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, then you should check that you don't have a full disk or that the disk is not in very heavy use.

Connect Out Slave connecting to master.

Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory-based temporary table to a disk based one to save memory.

Creating tmp table The thread is creating a temporary table to hold a part of the result for the query.

deleting from main table When executing the first part of a multi-table delete and we are only deleting from the first table.

deleting from reference tables When executing the second part of a multi-table delete and we are deleting the matched rows from the other tables.

Flushing tables The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.

Killed Someone has sent a kill to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it may still take a short time for the thread to die. If the thread is locked by some other thread, the kill will take affect as soon as the other thread releases it's lock.

Sending data The thread is processing rows for a SELECT statement and is also sending data to the client.

Sorting for group The thread is doing a sort to satisfy a GROUP BY.

Sorting for order The thread is doing a sort to satisfy a ORDER BY.

Opening tables This simply means that the thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example an ALTER TABLE or a LOCK TABLE can prevent opening a table until the command is finished.

Removing duplicates The query was using SELECT DISTINCT in such a way that MySQL couldn't optimise that distinct away at an early stage. Because of this MySQL has to do an extra stage to remove all duplicated rows before sending the result to the client.

Reopen table The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table and is now trying to reopen it.

Repair by sorting The repair code is using sorting to create indexes.

Repair with keycache The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.

Searching rows for update The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.

Sleeping The thread is wating for the client to send a new command to it.

System lock The thread is waiting for getting to get a external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the -skip-external-locking option.

Upgrading lock The INSERT DELAYED handler is trying to get a lock for the table to insert rows.

Updating The thread is searching for rows to update and updating them.

User Lock The thread is waiting on a GET_LOCK().

Waiting for tables The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. To be able to reopen the table it must however wait until all other threads have closed the table in question.

This notification happens if another thread has used FLUSH TABLES or one of the following commands on the table in question: FLUSH TABLES table_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE or OPTIMIZE TABLE.

waiting for handler insert The INSERT DELAYED handler has processed all inserts and are waiting to get new ones.