O'Reilly logo

MySQL Reference Manual by Kaj Arno, David Axmark, Michael Widenius

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Database Administration Language Reference

OPTIMIZE TABLE Syntax

OPTIMIZE TABLE tbl_name[,tbl_name]...

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

For the moment, OPTIMIZE TABLE only works on MyISAM and BDB tables. For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. See Section 4.5.2.

You can get OPTIMIZE TABLE to work on other table types by starting mysqld with --skip-new or --safe-mode, but in this case OPTIMIZE TABLE is just mapped to ALTER TABLE.

OPTIMIZE TABLE works the following way:

  • If the table has deleted or split rows, it repairs the table.

  • If the index pages are not sorted, it sorts them.

  • If the statistics are not up to date (and the repair couldn’t be done by sorting the index), it updates them.

OPTIMIZE TABLE for a MyISAM table is equivalent to running myisamchk --quick --check-only-changed --sort-index --analyze on the table.

Note that the table is locked during the time OPTIMIZE TABLE is running!

ANALYZE TABLE Syntax

ANALYZE TABLE tbl_name[,tbl_name...]

ANALYZE TABLE analyses and stores the key distribution for the table. During the analysis, the table is locked with a read lock. This works on MyISAM and BDB tables.

This is equivalent to running myisamchk -a on the table.

MySQL uses the stored key distribution to decide in which order tables should be joined when one does a join on something other than a constant.

The command returns a table with the following columns:

Column

Value

Table

Table name

Op

Always “analyze”

Msg_type

One of status, error, info, or warning

Msg_text

The message

You can check the stored key distribution with the SHOW INDEX command. See Section 4.5.6.1.

If the table hasn’t changed since the last ANALYZE TABLE command, the table will not be analysed again.

FLUSH Syntax

FLUSH flush_option [,flush_option] ...

You should use the FLUSH command if you want to clear some of the internal caches MySQL uses. To execute FLUSH, you must have the reload privilege.

flush_option can be any of the following:

Option

Description

HOSTS

Empties the host cache tables. You should flush the host tables if some of your hosts change IP number or if you get the error message Host ... is blocked. When more than one max_connect_errors error occurs in a row for a given host while connection to MySQL server, MySQL assumes something is wrong and blocks the host from further connection requests. Flushing the host tables allows the host to attempt to connect again. See Section A.2.4. You can start mysqld with -O max_connection_errors=999999999 to avoid this error message.

DES_KEY_FILE

Reloads the DES keys from the file that was specified with the --des-key-file option at server startup time.

LOGS

Closes and reopens all log files. If you have specified the update log file or a binary log file without an extension, the extension number of the log file will be incremented by one relative to the previous file. If you have used an extension in the filename, MySQL will close and reopen the update log file. See Section 4.9.3. This is the same thing as sending the SIGHUP signal to the mysqld server.

PRIVILEGES

Reloads the privileges from the grant tables in the mysql database.

QUERY CACHE

Defragment the query cache to better utilise its memory. This command will not remove any queries from the cache, unlike RESET QUERY CACHE.

TABLES

Closes all open tables and forces all tables in use to be closed. This also flushes the query cache.

[TABLE | TABLES] tbl_name [,tbl_name...]

Flushes only the given tables.

TABLES WITH READ LOCK

Closes all open tables and locks all tables for all databases with a read until one executes UNLOCK TABLES. This is a very convenient way to get backups if you have a filesystem, like Veritas, that can take snapshots in time.

STATUS

Resets most status variables to zero. This is something one should only use when debugging a query.

You can also access each of the preceding commands with the mysqladmin utility, using the flush-hosts, flush-logs, reload, or flush-tables commands.

Also take a look at the RESET command used with replication. See Section 4.5.4.

RESET Syntax

RESET reset_option [,reset_option] ...

The RESET command is used to clear things. It also acts as a stronger version of the FLUSH command. See Section 4.5.3.

To execute RESET, you must have the reload privilege.

Option

Description

MASTER

Deletes all binary logs listed in the index file, resetting the binlog index file to be empty. In pre-3.23.26 versions, FLUSH MASTER (Master).

SLAVE

Makes the slave forget its replication position in the master logs. In pre-3.23.26 versions the command was called FLUSH SLAVE(Slave).

QUERY CACHE

Removes all query results from the query cache.

KILL Syntax

KILL thread_id

Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST command and kill a thread with the KILL thread_id command.

If you have the process privilege, you can see and kill all threads. Otherwise, you can see and kill only your own threads.

You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads.

When you do a KILL, a thread-specific kill flag is set for the thread.

In most cases it may take some time for the thread to die, as the kill flag is only checked at specific intervals.

  • In SELECT, ORDER BY, and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

  • When doing an ALTER TABLE, the kill flag is checked before each block of rows is read from the original table. If the kill flag was set, the command is aborted and the temporary table is deleted.

  • When doing an UPDATE TABLE and DELETE TABLE, the kill flag is checked after each block is read and after each updated or deleted row. If the kill flag is set the statement is aborted. Note that if you are not using transactions, the changes will not be rolled back!

  • GET_LOCK( ) will abort with NULL.

  • An INSERT DELAYED thread will quickly flush all rows it has in memory and die.

  • If the thread is in the table lock handler (state: Locked), the table lock will be quickly aborted.

  • If the thread is waiting for free disk space in a write call, the write is aborted with a “disk full” error message.

SHOW Syntax

   SHOW DATABASES [LIKE wild]
or SHOW [OPEN] TABLES [FROM db_name] [LIKE wild]
or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW STATUS [LIKE wild]
or SHOW VARIABLES [LIKE wild]
or SHOW LOGS
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR user
or SHOW CREATE TABLE table_name
or SHOW MASTER STATUS
or SHOW MASTER LOGS
or SHOW SLAVE STATUS

SHOW provides information about databases, tables, and columns, as well as status information about the server. If the LIKE wild part is used, the wild string can be a string that uses the SQL % and _ wildcard characters.

Retrieving information about databases, tables, columns, and indexes

You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax. These two statements are equivalent:

mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;

SHOW DATABASES lists the databases on the MySQL server host. You can also get this list using the mysqlshow command.

SHOW TABLES lists the tables in a given database. You can also get this list using the mysqlshow db_name command.

Note: if a user doesn’t have any privileges for a table, the table will not show up in the output from SHOW TABLES or mysqlshow db_name.

SHOW OPEN TABLES lists the tables that are currently open in the table cache. See Section 5.4.7. The Comment field tells how many times the table is cached and in_use.

SHOW COLUMNS lists the columns in a given table. If you specify the FULL option, you will also get the privileges you have for each column. If the column types are different from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. See Section 6.5.3.1.

The DESCRIBE statement provides information similar to SHOW COLUMNS. See Section 6.6.2.

SHOW FIELDS is a synonym for SHOW COLUMNS, and SHOW KEYS is a synonym for SHOW INDEX. You can also list a table’s columns or indexes with mysqlshow db_name tbl_name or mysqlshow -k db_name tbl_name.

SHOW INDEX returns the index information in a format that closely resembles the SQLStatistics call in ODBC. The following columns are returned:

Column

Meaning

Table

Name of the table.

Non_unique

0 if the index can’t contain duplicates.

Key_name

Name of the index.

Seq_in_index

Column sequence number in index, starting with 1.

Column_name

Column name.

Collation

How the column is sorted in the index. In MySQL, this can have values A (Ascending) or NULL (Not sorted).

Cardinality

Number of unique values in the index. This is updated by running isamchk -a.

Sub_part

Number of indexed characters if the column is only partly indexed. NULL if the entire key is indexed.

Null

Contains ‘YES’ if the column may contain NULL.

Index_type

Index method used.

Comment

Various remarks. For now, it tells in MySQL < 4.0.2 whether index is FULLTEXT.

Note that as the Cardinality is counted based on statistics stored as integers, it’s not necessarily accurate for small tables.

The Null and Index_type columns were added in MySQL 4.0.2.

SHOW TABLE STATUS

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW TABLE STATUS (new in Version 3.23) works likes SHOW STATUS, but provides a lot of information about each table. You can also get this list using the mysqlshow --status db_name command. The following columns are returned:

Column

Meaning

Name

Name of the table.

Type

Type of table. See Chapter 7.

Row_format

The row storage format (Fixed, Dynamic, or Compressed).

Rows

Number of rows.

Avg_row_length

Average row length.

Data_length

Length of the data file.

Max_data_length

Max length of the data file.

Index_length

Length of the index file.

Data_free

Number of allocated but not used bytes.

Auto_increment

Next auto-increment value.

Create_time

When the table was created.

Update_time

When the data file was last updated.

Check_time

When the table was last checked.

Create_options

Extra options used with CREATE TABLE.

Comment

The comment used when creating the table (or some information why MySQL couldn’t access the table information).

InnoDB tables will report the free space in the tablespace in the table comment.

SHOW STATUS

SHOW STATUS provides server status information (like mysqladmin extended-status). The output resembles that shown here, though the format and numbers probably differ:

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 0          |
| Bytes_received           | 155372598  |
| Bytes_sent               | 1176560426 |
| Connections              | 30023      |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 8340       |
| Created_tmp_files        | 60         |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_delete           | 462604     |
| Handler_read_first       | 105881     |
| Handler_read_key         | 27820558   |
| Handler_read_next        | 390681754  |
| Handler_read_prev        | 6022500    |
| Handler_read_rnd         | 30546748   |
| Handler_read_rnd_next    | 246216530  |
| Handler_update           | 16945404   |
| Handler_write            | 60356676   |
| Key_blocks_used          | 14955      |
| Key_read_requests        | 96854827   |
| Key_reads                | 162040     |
| Key_write_requests       | 7589728    |
| Key_writes               | 3813196    |
| Max_used_connections     | 0          |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 1          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Opened_tables            | 44600      |
| Questions                | 2026873    |
| Select_full_join         | 0          |
| Select_full_range_join   | 0          |
| Select_range             | 99646      |
| Select_range_check       | 0          |
| Select_scan              | 30802      |
| Slave_running            | OFF        |
| Slave_open_temp_tables   | 0          |
| Slow_launch_threads      | 0          |
| Slow_queries             | 0          |
| Sort_merge_passes        | 30         |
| Sort_range               | 500        |
| Sort_rows                | 30296250   |
| Sort_scan                | 4650       |
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_created          | 30022      |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

The preceding status variables have the following meaning:

Variable

Meaning

Aborted_clients

Number of connections aborted because the client died without closing the connection properly. See Section A.2.9.

Aborted_connects

Number of tries to connect to the MySQL server that failed. See Section A.2.9.

Bytes_received

Number of bytes received from all clients.

Bytes_sent

Number of bytes sent to all clients.

Com_xxx

Number of times each xxx command has been executed.

Connections

Number of connection attempts to the MySQL server.

Created_tmp_ disk_tables

Number of implicit temporary tables on disk created while executing statements.

Created_tmp_ tables

Number of implicit temporary tables in memory created while executing statements.

Created_tmp_ files

How many temporary files mysqld has created.

Delayed_insert_ threads

Number of delayed insert handler threads in use.

Delayed_writes

Number of rows written with INSERT DELAYED.

Delayed_errors

Number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key).

Flush_commands

Number of executed FLUSH commands.

Handler_delete

Number of times a row was deleted from a table.

Handler_read_ first

Number of times the first entry was read from an index. If this is high, it suggests that the server is doing a lot of full index scans. For example, SELECT col1 FROM foo, assuming that col1 is indexed.

Handler_read_key

Number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed.

Handler_read_next

Number of requests to read next row in key order. This will be incremented if you are querying an index column with a range constraint. This also will be incremented if you are doing an index scan.

Handler_read_rnd

Number of requests to read a row based on a fixed position. This will be high if you are doing a lot of queries that require sorting of the result.

Handler_read_ rnd_next

Number of requests to read the next row in the data file. This will be high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Handler_update

Number of requests to update a row in a table.

Handler_write

Number of requests to insert a row in a table.

Key_blocks_used

The number of used blocks in the key cache.

Key_read_ requests

The number of requests to read a key block from the cache.

Key_reads

The number of physical reads of a key block from disk.

Key_write_ requests

The number of requests to write a key block to the cache.

Key_writes

The number of physical writes of a key block to disk.

Max_used_ connections

The maximum number of connections in use simultaneously.

Not_flushed_ key_blocks

Keys blocks in the key cache that have changed but haven’t yet been flushed to disk.

Not_flushed_ delayed_rows

Number of rows waiting to be written in INSERT DELAY queues.

Open_tables

Number of tables that are open.

Open_files

Number of files that are open.

Open_streams

Number of streams that are open (used mainly for logging).

Opened_tables

Number of tables that have been opened.

Select_full_join

Number of joins without keys (should be 0).

Select_full_ range_join

Number of joins where we used a range search on reference table.

Select_range

Number of joins where we used ranges on the first table. (It’s normally not critical even if this is big.)

Select_scan

Number of joins where we scanned the first table.

Select_range_ check

Number of joins without keys where we check for key usage after each row (should be 0).

Questions

Number of queries sent to the server.

Slave_open_ temp_tables

Number of temporary tables currently open by the slave thread.

Slow_launch_ threads

Number of threads that have taken more than slow_launch_time to connect.

Slow_queries

Number of queries that have taken more than long_query_time. See Section 4.9.5.

Sort_merge_ passes

Number of merges the sort has to do. If this value is large you should consider increasing sort_buffer.

Sort_range

Number of sorts that were done with ranges.

Sort_rows

Number of sorted rows.

Sort_scan

Number of sorts that where done by scanning the table.

Table_locks_ immediate

Number of times a table lock was acquired immediately. Available after 3.23.33.

Table_locks_ waited

Number of times a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimise your queries, and then either split your table(s) or use replication. Available after 3.23.33.

Threads_cached

Number of threads in the thread cache.

Threads_connected

Number of currently open connections.

Threads_created

Number of threads created to handle connections.

Threads_running

Number of threads that are not sleeping.

Uptime

How many seconds the server has been up.

Some comments about the preceding variables:

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

  • If Key_reads is big, your key_cache is probably too small. The cache hit rate can be calculated with Key_reads/Key_read_requests.

  • If Handler_read_rnd is big, 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.

SHOW VARIABLES

SHOW VARIABLES [LIKE wild]

SHOW VARIABLES shows the values of some MySQL system variables. You can also get this information using the mysqladmin variables command. If the default values are unsuitable, you can set most of these variables using command-line options when mysqld starts up. See Section 4.1.1.

The output resembles that shown here, though the format and numbers may differ somewhat:

+------------------------------+---------------------------+
| Variable_name                | Value                     |
+------------------------------+---------------------------+
| ansi_mode                    | OFF                       |
| back_log                     | 50                        |
| basedir                      | /my/monty/                |
| bdb_cache_size               | 16777216                  |
| bdb_log_buffer_size          | 32768                     |
| bdb_home                     | /my/monty/data/           |
| bdb_max_lock                 | 10000                     |
| bdb_logdir                   |                           |
| bdb_shared_data              | OFF                       |
| bdb_tmpdir                   | /tmp/                     |
| binlog_cache_size            | 32768                     |
| concurrent_insert            | ON                        |
| connect_timeout              | 5                         |
| datadir                      | /my/monty/data/           |
| delay_key_write              | ON                        |
| delayed_insert_limit         | 100                       |
| delayed_insert_timeout       | 300                       |
| delayed_queue_size           | 1000                      |
| flush                        | OFF                       |
| flush_time                   | 0                         |
| ft_min_word_len              | 4                         |
| ft_max_word_len              | 254                       |
| ft_max_word_len_for_sort     | 20                        |
| ft_boolean_syntax            | + -><( )~*                 |
| have_bdb                     | YES                       |
| have_innodb                  | YES                       |
| have_raid                    | YES                       |
| have_openssl                 | NO                        |
| init_file                    |                           |
| interactive_timeout          | 28800                     |
| join_buffer_size             | 131072                    |
| key_buffer_size              | 16776192                  |
| language                     | /my/monty/share/english/  |
| large_files_support          | ON                        |
| log                          | OFF                       |
| log_update                   | OFF                       |
| log_bin                      | OFF                       |
| log_slave_updates            | OFF                       |
| long_query_time              | 10                        |
| low_priority_updates         | OFF                       |
| lower_case_table_names       | 0                         |
| max_allowed_packet           | 1048576                   |
| max_binlog_cache_size        | 4294967295                |
| max_connections              | 100                       |
| max_connect_errors           | 10                        |
| max_delayed_threads          | 20                        |
| max_heap_table_size          | 16777216                  |
| max_join_size                | 4294967295                |
| max_sort_length              | 1024                      |
| max_tmp_tables               | 32                        |
| max_write_lock_count         | 4294967295                |
| myisam_bulk_insert_tree_size | 8388608                   |
| myisam_recover_options       | DEFAULT                   |
| myisam_sort_buffer_size      | 8388608                   |
| net_buffer_length            | 16384                     |
| net_read_timeout             | 30                        |
| net_retry_count              | 10                        |
| net_write_timeout            | 60                        |
| open_files_limit             | 0                         |
| pid_file                     | /my/monty/data/donna.pid  |
| port                         | 3306                      |
| protocol_version             | 10                        |
| record_buffer                | 131072                    |
| query_buffer_size            | 0                         |
| query_cache_limit            | 1048576                   |
| query_cache_size             | 16768060                  |
| query_cache_startup_type     | 1                         |
| safe_show_database           | OFF                       |
| server_id                    | 0                         |
| skip_locking                 | ON                        |
| skip_networking              | OFF                       |
| skip_show_database           | OFF                       |
| slow_launch_time             | 2                         |
| socket                       | /tmp/mysql.sock           |
| sort_buffer                  | 2097116                   |
| table_cache                  | 64                        |
| table_type                   | MYISAM                    |
| thread_cache_size            | 4                         |
| thread_stack                 | 65536                     |
| tmp_table_size               | 1048576                   |
| tmpdir                       | /tmp/                     |
| version                      | 3.23.29a-gamma-debug      |
| wait_timeout                 | 28800                     |
+------------------------------+---------------------------+

Each option is described here. Values for buffer sizes, lengths, and stack sizes are given in bytes. You can specify values with a suffix of K or M to indicate kilobytes or megabytes. For example, 16M indicates 16 megabytes. The case of suffix letters does not matter; 16M and 16m are equivalent:

ansi_mode

Is ON if mysqld was started with --ansi. See Section 1.7.2.

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 indexes and rows for BDB tables. If you don’t use BDB tables, you should start mysqld with --skip-bdb so as not to waste memory for this cache.

bdb_log_buffer_size

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

bdb_home

The value of the --bdb-home option.

bdb_max_lock

The maximum number of locks (1000 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 done 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. See Section 6.7.1.

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 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

If enabled (is ON by default), MySQL will honor the DELAY_KEY_WRITE option for CREATE TABLE. 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. Note that if you start mysqld with the --delay-key-write-for-all-tables option this means that all tables will be treated as if they were created with the delay_key_write option. You can clear this flag by starting mysqld with --skip-new or --safe-mode.

delayed_insert_limit

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

delayed_insert_timeout

How long an 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, 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_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.

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.

ft_max_word_len_sort

The maximum length of the word in a FULLTEXT index to be used in fast index re-creation method in REPAIR, CREATE INDEX, or ALTER TABLE. Longer words are inserted the slow way. The rule of thumb is as follows: with ft_max_word_len_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_sort is too small, MySQL will insert a lot of words into the index the slow way, but short words will be inserted very quickly.

ft_boolean_syntax

List of operators supported by MATCH ... AGAINST(... IN BOOLEAN MODE). See Section 6.8.

have_innodb

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

have_bdb

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

have_raid

YES if mysqld supports the RAID option.

have_openssl

YES 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 examining 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. See Section 4.5.6.

To get even more speed when writing many rows at the same time, use LOCK TABLES. See Section 6.7.2.

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. See Section 4.9.5.

lower_case_table_names

If set to 1, table names are stored in lowercase on disk and table names will be case-insensitive. See Section 6.1.3.

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 are 16M in MySQL 3.23 and 2G 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 1024 bytes, or more than 1G. Default is 1G.

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 are covered later in this section. See also Section A.2.5.

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.

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_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

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

max_write_lock_count

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

myisam_bulk_insert_tree_size

MySQL 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 optimization. Note: this cache is only used when adding data to non-empty table. Default value is 8M.

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 created for fast index creation would be bigger than using the key cache by the amount specified here, use 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!

myisam_max_sort_file_size

The maximum size of the temporary file MySQL is allowed to use while re-creating the index (during REPAIR, ALTER TABLE, or LOAD DATA INFILE. If the file-size will be bigger than this, the index will be created through the key cache (which is slower). Note that this parameter is given in megabytes!

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_read_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

If this is not 0, mysqld will use this value to reserve file descriptors to use with setrlimit( ). If this value is 0, mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of files. You should try increasing this if mysqld gives you the error ‘Too many 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.

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.

record_rnd_buffer

When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. If not set, it’s set to the value of record_buffer.

query_buffer_size

The initial allocation of the query buffer. If most of your queries are long (like when inserting blobs), you should increase this!

query_cache_limit

Don’t cache results that are bigger than this. (Default is 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_startup_type

This may be set (only numeric) as shown in the following table:

Value

Alias

Comment

0

OFF

Don’t cache or retrieve results.

1

ON

Cache all results except SELECT SQL_NO_CACHE ... queries.

2

DEMAND

Cache only SELECT SQL_CACHE ... queries.

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_read_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

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. See Section A.4.4.

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. See Section 4.5.6. If this variable is big and you don’t do FLUSH TABLES a lot (which just forces all tables to be closed and reopened), you should increase the value of this variable.

For more information about the table cache, see Section 5.4.7.

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 examining the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.

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. See Section 5.1.4.

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.

version

The version number for the server.

wait_timeout

The number of seconds the server waits for activity on a connection before closing it. See also interactive_timeout.

For information on how to tune these variables, see Section 5.5.2.

SHOW LOGS

SHOW LOGS shows you status information about existing log files. It currently only displays information about Berkeley DB log files.

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).

SHOW PROCESSLIST

SHOW [FULL] PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the process privilege, you can see all threads. Otherwise, you can see only your own threads. See Section 4.5.5. If you don’t use the FULL option, only the first 100 characters of each query will be shown.

This command is very useful if you get the ‘too many connections’ error message and want to find out what’s going on. MySQL reserves one extra connection for a client with the process privilege to ensure that you should always be able to log in and check the system (assuming you are not giving this privilege to all your users).

Some states commonly seen in mysqladmin processlist include the following:

Checking table

The thread is performing an [automatic] check 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, 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 you 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 effect as soon as the other thread releases its 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 an ORDER BY.

Opening tables

This simply means that the thread is trying to open a table. This should be a very fast procedure, unless something prevents it from 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 optimize 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 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 to get an 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-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 is 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 is waiting to get new ones.

Most states are very quick operations. If threads last in any of these states for many seconds, there may be a problem that needs to be investigated.

There are some other states that are not mentioned previously, but most of these are only useful to find bugs in mysqld.

SHOW GRANTS FOR

SHOW GRANTS FOR user lists the grant commands that must be issued to duplicate the grants for a user.

mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

SHOW CREATE TABLE

Shows a CREATE TABLE statement that will create the given table:

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id int(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM

SHOW CREATE TABLE will quote table and column names according to the SQL_QUOTE_SHOW_CREATE option. See Section 5.5.6.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required