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 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 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 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 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 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.
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
|
|
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 [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 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 [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 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 [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 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 | +---------------------------------------------------------------------+
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=MyISAMSHOW CREATE TABLE will quote table and column names according to the SQL_QUOTE_SHOW_CREATE option. See Section 5.5.6.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
