OPTIMIZE TABLE SyntaxOPTIMIZE 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.
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, repair the table.
• If the index pages are not sorted, sort them.
• If the statistics are not up to date (and the repair couldn’t be done by sorting the
index), update them.
OPTIMIZE TABLE for MyISAM tables is equvialent of running myisamchk --quick --check-
changed-tables --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...]
Analyse and store the key distribution for the table. During the analyse 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 else 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.
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:
HOSTS Empties the host cache tables. You should ush the host tables if some of your hosts change IP number or if you get the error message Host ... is blocked. When more than ax_connect_errors errors occur in a row for a given host while connection to the 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.
You can start mysqld with -O max_connection_errors=999999999 to avoid this error message.
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 file name, MySQL will close and reopen the update log file.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.
TABLES Closes all open tables and force all tables in use to be closed.
[TABLE |
TABLES]
table_name
[,table_
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 very convenient way to get backups if you have a file system, 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 commands shown above with the mysqladmin utility, using
the flush-hosts, flush-logs, reload, or flush-tables commands.
Take also a look at the RESET command used with replication.
KILL SyntaxKILL 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 ag is only checked at
specific intervals.
• In SELECT, ORDER BY and GROUP BY loops, the ag is checked after reading a block of
rows. If the kill ag is set the statement is aborted
• When doing an ALTER TABLE the kill ag is checked before each block of rows are
read from the original table. If the kill ag was set the command is aborted and the
temporary table is deleted.
• When doing an UPDATE TABLE and DELETE TABLE, the kill ag is checked after each
block read and after each updated or delete row. If the kill ag 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 ush 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 an
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, columns, or status information about
the server. If the LIKE wild part is used, the wild string can be a string that uses the SQL
‘%’ and ‘_’ wild-card 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. . 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 di erent than
you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes
changes column types.
The DESCRIBE statement provides information similar to SHOW COLUMNS.
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.
Comment
Various remarks. For now, it tells whether index is
FULLTEXT or not.
Note that as the Cardinality is counted based on statistics stored as integers, it’s not
necessarily accurate for small tables.
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 [Table types], page 441.
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 autoincrement 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 below, though the format and numbers probably di er:
+--------------------------+------------+
| 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 status variables listed above have the following meaning:
Variable
Meaning
Aborted_clients Number of connections aborted because the client died without closing the connection properly.
Aborted_connects Number of tries to connect to the MySQL server that failed.
Bytes_received Number of bytes received from all clients.
Bytes_sent
Number of bytes sent to all clients.
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 have 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 datafile.
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 has changed but hasn’t
yet been ushed 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 refer-
ence 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).
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 [Slow query log], page 284.
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 where 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.
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.
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 above:
• If Opened_tables is big, then your table_cache variable is probably too small.
• If key_reads is big, then 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, then you probably have a lot of queries that require MySQL
to scan whole tables or you have joins that don’t use keys properly.
• If Threads_created is big, you may want to increase the thread_cache_size variable.
• 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 [Command-line options], page 164.
The output resembles that shown below, though the format and numbers may di er some-
what:
+------------------------------+---------------------------+
| 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
|
| 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
|
| 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
|
+------------------------------+---------------------------+
bytes. You can specify values with a su x of ‘K’ or ‘M’ to indicate kilobytes or megabytes.
For example, 16M indicates 16 megabytes. The case of su x letters does not matter; 16M
and 16m are equivalent:
ansi_mode. Is ON if mysqld was started with --ansi.
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 ine ective.
basedir The value of the --basedir option.
0 commenti:
Post a Comment