Show MySql Runtime Variables | Show MySQL System Variables | Show MySQL Processes | System Information |
SHOW STATUS --- Running on MySQL Version 3.23.45 | |||
Variable_name | Value | Stats | Notes, Advice from MySql.com Manual. |
Aborted_clients | 4 | Number of connections aborted because the
client died without closing the connection properly. This is incremented if one of the following has happened: The client program did not call mysql_close() before exit. The client had been sleeping more than wait_timeout or interactive_timeout without doing any requests. The client program ended abruptly in the middle of the transfer. (Docs) |
|
Aborted_connects | 0 | % of Total Connections = 0.000% | Number of tries to connect to the MySQL server
that failed, The server variable Aborted_connects is incremented when: When a connection packet doesn't contain the right information. When the user didn't have privileges to connect to a database. When a user uses a wrong password. When it takes more than connect_timeout seconds to get a connect package. Note: if this is high it could indicate that someone is trying to break into your database! (Docs) |
Bytes_received | 756420855 | KB Received/Second = 2.346 Per Hour = 8444.071 |
Number of bytes received from all clients. |
Bytes_sent | 54603109 | KB Sent/Second = 0.169 Per Hour = 609.541 |
Number of bytes sent to all clients. |
Connections | 1098633 | Connections/Second = 3.488 Per Hour = 12558.597 |
Number of connection attempts to the MySQL server. |
Created_tmp_disk_tables | 13 | Number of implicit temporary tables on disk created while executing statements. If this is big, you may want to increase the tmp_table_size variable to get the temporary tables memory based instead of disk based. | |
Created_tmp_tables | 310657 | Temporary Tables Created/Second = 0.986 Per Hour = 3551.155 |
Number of implicit temporary tables in memory created while executing statements. |
Created_tmp_files | 2 | How many temporary files mysqld have created. | |
Delayed_insert_threads | 0 | Number of delayed insert handler threads in use. | |
Delayed_writes | 0 | Number of rows written with INSERT DELAYED. | |
Delayed_errors | 0 | Number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key). | |
Flush_commands | 1 | Number of executed FLUSH commands. | |
Handler_delete | 275871 | Number of times a row was deleted from a table. | |
Handler_read_first | 359761 | 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 | 6471279 | 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 | 1288025320 | 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_prev | 98694 | How many times after "Handler_read_key" we realized we need one more record, and went back to look at the index, looked up the previous record position, and read it. | |
Handler_read_rnd | 15116120 | 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. If this 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. | |
Handler_read_rnd_next | 1418157775 | 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 | 2575722 | Number of requests to update a row in a table. | |
Handler_write | 199321349 | Number of requests to insert a row in a table. | |
Key_blocks_used | 23436 | Approx. 95% of key_buffer in use Key Buffer Size = 24 Mb. |
The number of used blocks in the key cache. If "percentage of key_buffer in use is 100% you may want to increase your "key_buffer=" variable in the my.cnf file, if the percentage is low you may want to reduce your key_buffer variable to save some memory. |
Key_read_requests | 174517739 | The number of requests to read a key block from the cache. | |
Key_reads | 26452 | Key Reads/Key Read Requests = 0.000152 Cache hit = 99.999848% |
The number of physical reads of a key block from disk. |
Key_write_requests | 3655518 | The number of requests to write a key block to the cache. | |
Key_writes | 1917203 | Key Writes/Key Write Requests = 0.524468 | The number of physical writes of a key block to disk. |
Max_used_connections | 53 | The maximum number of connections in use simultaneously. | |
Not_flushed_key_blocks | 0 | Keys blocks in the key cache that has changed but hasn't yet been flushed to disk. | |
Not_flushed_delayed_rows | 0 | Number of rows waiting to be written in INSERT DELAYED queues. | |
Open_tables | 155 | 44% of table_cache in use | Number of tables that are open. More Info .... |
Open_files | 256 | Number of files that are open. | |
Open_streams | 0 | Number of streams that are open (used mainly for logging). | |
Opened_tables | 264 | Number of tables that have been opened. | |
Questions | 9052590 | Queries/Second = 28.745 Queries/Hour = 103481.167 |
Number of queries sent to the server. |
Select_full_join | 132 | % of Total Queries = 0.001% | Number of joins without keys (Should be 0). |
Select_full_range_join | 0 | Number of joins where we used a range search on reference table. | |
Select_range | 320995 | Number of joins where we used ranges on the first table. (It's normally not critical even if this is big.) | |
Select_range_check | 0 | Number of joins without keys where we check for key usage after each row (Should be 0). | |
Select_scan | 559099 | Number of joins where we scanned the first table. | |
Slave_running | OFF | This tells whether the slave is running. | |
Slave_open_temp_tables | 0 | Number of temporary tables currently open by the slave thread | |
Slow_launch_threads | 0 | Number of threads that have taken more than slow_launch_time to connect. | |
Slow_queries | 0 | Slow Queries/Second = 0.000 Slow Queries/Hour = 0.000 % of Total Queries = 0.000% |
Number of queries that have taken more than long_query_time. (Docs) |
Sort_merge_passes | 1 | Number of merges the sort has to do. If this value is large you should consider increasing sort_buffer. | |
Sort_range | 41479 | Number of sorts that where done with ranges. | |
Sort_rows | 15116120 | Number of sorted rows. | |
Sort_scan | 314776 | Number of sorts that where done by scanning the table. | |
Table_locks_immediate | 7471678 | % of total locks 99.941146% Locks/Second = 23.725 Locks/Hour = 85409.586 |
Number of times a table lock was acquired immediately. Available after 3.23.33. |
Table_locks_waited | 4400 | % of total locks 0.058854% Locks/Second = 0.014 Locks/Hour = 50.297 |
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 | 15 | Number of threads in the thread cache. | |
Threads_created | 60 | Cache hit Rate = 99.994539% | Number of threads created to handle connections. If Threads_created is big or the cache hit rate is low, you may want to increase the thread_cache_size variable. |
Threads_connected | 1 | Number of currently open connections. | |
Threads_running | 1 | Number of threads that are not sleeping. | |
Uptime | 314930 | 3 days 15 hrs 28 mins 50 secs | How many seconds the server has been up. |
Current "my.cnf" Settings
[mysqld]
skip-locking
set-variable = key_buffer=24M
set-variable = join_buffer=124K
set-variable = record_buffer=1020K
set-variable = sort_buffer=1M
set-variable = max_allowed_packet=1023K
set-variable = tmp_table_size=32M
set-variable = max_connections=100
set-variable = table_cache=350
set-variable = thread_cache_size=16
set-variable = connect_timeout=10
(Experimental) Recommended Settings
[mysqld]
skip-locking
set-variable = key_buffer=32M
set-variable = join_buffer=128K
set-variable = record_buffer=1M
set-variable = sort_buffer=1M
set-variable = tmp_table_size=32M
set-variable = table_cache=300
set-variable = max_connections=100
set-variable = thread_concurrency=2
set-variable = thread_cache_size=16
# max_allowed_packet - only increase this value if you are using big BLOB columns.
set-variable = max_allowed_packet=1M
set-variable = connect_timeout=10