Parametros
|
Variáveis
|
Descrição(inglês)
|
Current Connections
| | |
| Maximum Allowed | Max_connections | Currently open connections as a percentage of maximum allowed connections. |
| Open Connections | Threads_connected |
| Connection Usage | Threads_connected/ Max_connections |
| Currently running threads | Threads_running | Higher value indicates that you may run out of connections soon and new clients may be refused connection. |
| Highest no. of Concurrent Connections | Max_used_connections |
| Percentage of maximum allowed reached | (Max_used_connections/ Max_connections) *100 |
MyISAM Key Cache
| | |
| Allocated Memory | Key_buffer_size | If not using MyISAM tables, set it to 8-64M so as to use for internal temporary disk tables. |
| Block Size | Key_cache_block_size | |
| No. of Blocks | Key_buffer_size/Key_cache_block_size | |
| Used Blocks | (Key_buffer_size/ Key_cache_block_size)- key_blocks_unused | |
| Percent of Used Blocks | 1-(Key_blocks_unused/No. of Blocks)*100 | Key cache in use. |
| Blocks requested from Cache | Key_read_requests | |
| Blocks served from disk | Key_reads | |
| Cache Hit Rate | 1- (Key_reads/Key_read_requests)*100 | For optimum efficiency it should be more than 99%. |
| Blocks Written to Cache | Key_write_requests | |
| Blocks Written to Disk | Key_writes | |
| Cache Writes Ratio | Key_writes/Key_write_requests | It is generally 1 in an OLTP. |
Innodb Cache
| | |
| Allocated Memory | Innodb_buffer_pool_size | It can be 70-80% of available server memory for innodb only installations. |
| Cache Hit Rate | 1- (innodb_buffer_pool_reads/innodb_buffer_pool_read-requests) *100 | |
| Cache Write Wait Required | Innodb_buffer_pool_wait_free/ innodb_buffer_pool_write_requests | Innodb should not have to wait before writing pages, for optimal performance. |
| Additional Pool Size | Innodb_additional_mem_pool_size | Stores innodb data dictionary and other internal structures. |
Innodb Logs
| | |
| Log Buffer Size | Innodb_log_buffer_size | 8-16 M recommended. |
| Log Waits Required | Innodb_log_waits/ innodb_log_wites | |
| No. of log files | Innodb_log_files_in_group | |
| Size of each log file | Innodb_log_file_size | Size of each log file in a log group. |
| Total size of all log files | Innodb_log_file_size * Innodb_log_files_in_group | You should set the combined size of log files to about 25%-100% of your buffer pool size to avoid unneeded buffer pool flush activity on log file overwrite. However, note that a larger log file size will increase the time needed for the recovery process. |
| Total log file size as percentage to innodb buffer pool size | Total size of all log files/ innodb_buffer_pool_size | Increase the size of innodb log files to improve performance. |
| Log flush interval | Innodb_flush_log_at_trx_commit | If set to 1, InnoDB will flush (fsync) the transaction logs to the disk at each commit, which offers full ACID behavior. If you are willing to compromise this safety, and you are running small transactions, you may set this to 0 or 2 to reduce disk I/O to the logs. Value 0 means that the log is only written to the log file and the log file flushed to disk approximately once per second. Value 2 means the log is written to the log file at each commit, but the log file is only flushed to disk approximately once per second. |
Innodb
| | |
| Data Pages | Innodb_buffer_pool_pages_data | The number of pages containing data (dirty or clean). |
| Dirty Pages | Innodb_buffer_pool_pages_dirty | The number of pages currently dirty. |
| Pages Flushed | Innodb_buffer_pool_pages_flushed | The number of buffer pool pages that have been requested to be flushed. |
| Pages Free | Innodb_buffer_pool_pages_free | The number of free pages. |
| Innodb_buffer_pool_pages_latched | The number of latched pages in InnoDB buffer pool. These are pages currently being read or written or that can’t be flushed or removed for some other reason. |
| Innodb_buffer_pool_pages_misc | The number of pages busy because they have been allocated for administrative overhead such as row locks or the adaptive hash index. It can also be calculated as Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free – Innodb_buffer_pool_pages_data. |
| Buffer Pool Pages | Innodb_buffer_pool_pages_total | Total size of buffer pool, in pages. |
| Innodb_buffer_pool_read_ahead_rnd | The number of “random” read-aheads InnoDB initiated. This happens when a query is to scan a large portion of a table but in random order. |
| Innodb_buffer_pool_read_ahead_seq | The number of sequential read-aheads InnoDB initiated. This happens when InnoDB does a sequential full table scan. |
| Innodb_buffer_pool_read_requests | The number of logical read requests InnoDB has done. |
| Innodb_buffer_pool_reads | The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read. |
| Innodb_buffer_pool_wait_free | Normally, writes to the InnoDB buffer pool happen in the background. However, if it’s necessary to read or create a page and no clean pages are available, it’s necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size was set properly, this value should be small. |
| Innodb_buffer_pool_write_requests | The number writes done to the InnoDB buffer pool. |
| Innodb_data_fsyncs | The number of fsync() operations so far. |
| Innodb_data_pending_fsyncs | The current number of pending fsync() operations. |
| Innodb_data_pending_reads | The current number of pending reads. |
| Innodb_data_pending_writes | The current number of pending writes. |
| Innodb_data_read | The amount of data read so far, in bytes. |
| Innodb_data_reads | The total number of data reads. |
| Innodb_data_writes | The total number of data writes. |
| Innodb_data_written | The amount of data written so far, in bytes. |
| Innodb_log_waits | The number of waits we had because log buffer was too small and we had to wait for it to be flushed before continuing. |
| Innodb_log_write_requests | The number of log writes requests. |
| Innodb_log_writes | The number of physical writes to the log file. |
| Innodb_os_log_fsyncs | The number of fsyncs writes done to the log file. |
| Innodb_os_log_pending_fsyncs | The number of pending log file fsyncs. |
| Innodb_os_log_pending_writes | Pending log file writes. |
| Innodb_os_log_written | The number of bytes written to the log file. |
| Innodb_page_size | The compiled-in InnoDB page size (default 16KB). Many values are counted in pages; the page size allows them to be easily converted to bytes. |
| Innodb_pages_created | The number of pages created. |
| Innodb_pages_read | The number of pages read. |
| Innodb_pages_written | The number of pages written. |
| Innodb_row_lock_current_waits | The number of row locks currently being waited for. |
| Innodb_row_lock_time | The total time spent in acquiring row locks, in milliseconds. |
| Innodb_row_lock_time_avg | The average time to acquire a row lock, in milliseconds. |
| Innodb_row_lock_time_max | The maximum time to acquire a row lock, in milliseconds. |
| Innodb_row_lock_waits | The number of times a row lock had to be waited for. |
| Innodb_rows_deleted | The number of rows deleted from InnoDB tables. |
| Innodb_rows_inserted | The number of rows inserted in InnoDB tables. |
| Innodb_rows_read | The number of rows read from InnoDB tables. |
| Innodb_rows_updated | The number of rows updated in InnoDB tables. |
Thread Cache
| | |
| Number of threads that can be cached | Thread_cache_size | |
| Number of threads in the cache | Threads_cached | |
| Threads created to handle connections | Threads_created | |
| Thread cache hit rate | (Threads_created/ Threads_connected) * 100 | |
Table Cache
| | |
| No. of tables that can be cached | Table_cache | The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. |
| Tables currently open | Open-tables | |
| No. of table cache misses | Opened_tables | If the number increases rapidly, consider increasing the table_cache. |
Query Cache
| | |
| Query cache enabled | Have_query_cache | |
| Query Cache Type | Query_cache_type | ‘Off’: query has been disabled; ‘Always’: all statements are cached except those beginning with SELECT SQL_NO_CACHE; ‘Demand’: Caching is done only for those beginning with SELECT SQL_CACHE. |
| Cache Size | Query_cache_size | |
| Max size of cached query | Query_cache_limit | The maximum size of individual query results that can be cached. |
| Free Memory | Qcache_free_memory | The amount of free memory in the query cache. |
| Query parsing and execution buffer | Query_prealloc_size | Better performance by increasing this value, if you execute a lot of complex queries. |
| Block Size | Query_cache_min_res_unit | For queries with large resultset, larger block size means better performance; but for queries with small resultset, a large block size leads to memory fragmentation. |
| Total Blocks | Qcache_total_blocks | Total number of blocks in the cache |
| Free Blocks | Qcache_free_blocks | Total number of free blocks in the cache |
| Fragmentation | Qcache_free_blocks *100/ Qcache_total_blocks | Extent of fragmentation in the query cache. |
| Queries in cache | Qcache_queries_in_cache | |
| Queries not cached | Qcache_not_cached | Queries that cannot be cached, since it contains UDFs, user variables or it accesses system database. |
| Queries inserted to cache | Qcache_inserts | |
| Cache Hits | Qcache_hits | Number of queries that served successfully by the query cache. |
| Queries pruned from cache | Qcache_lowmem_prunes | Queries that were inserted into the query cache, but subsequently got pruned, because of low memory. |
| Pruned as percentage of inserts | Qcache_lowmem_prunes* 100/ Qcache_inserts | If this rate is high, consider increasing Query_cache_size system variable. |
| Cache Hit Rate | Qcache_hits * 100 / (Qcache_hits + Qcache_inserts) | A high rate indicates that queries in cache are being reused by other threads; a low rate shows either not enough memory allocated to query cache or identical queries are not repeatedly issued to the server. |
Sort Buffers
| | |
| Buffer for in-memory sorting | Sort_buffer_size | It is allocated per client, so don’t make it too large; recommended 1 M. |
| Temporary files created because of insufficient Sort_buffer_size | Sort_merge_passes | If this value is high, increase the value of Sort_buffer_size. |
| Buffer for fast rebuilds of MyISAM indexes | Myisam_sort_buffer_size | It is allocated per client. |
| Buffer for reading rows in sorted order after a sort operation | Read_rnd_buffer_size | It is allocated per client. |
Table Locks
| | |
| Acquired immediately | Table_locks_immediate | Number of times table locks are acquired immediately. |
| Wait was needed | Table_locks_waited | Number of times a wait was required before getting a table lock. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables. |
| Lock contention | Table_locks_waited * 100 / (Table_locks_waited + Table_locks_immediate) | |
Full Table Scans
| | |
| Number of requests to read a row based on a fixed position | handler_read_rnd | This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don’t use keys properly. |
| Number of requests to read the next row in the data file. | handler_read_rnd_next | This value is 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. |
| Number of times the first entry was read from an index | handler_read_first | If this value is high, it suggests that the server is doing a lot of full index scans. |
| Number of requests to read the next row in key order. | handler_read_next | This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan. |
| Number of requests to read a row based on a key. | handler_read_key | If this value is high, it is a good indication that your tables are properly indexed for your queries. |
| Number of requests to read the previous row in key order. | handler_read_prev | This read method is mainly used to optimize ORDER BY … DESC. |
| Percentage of full table scans | 1-(handler_read_rnd_next + handler_read_rnd) / (handler_read_rnd_next + handler_read_rnd + handler_read_first + handler_read_next + handler_read_key + handler_read_prev ) | Ratio of the number of rows read via full table scans to the sum of all handler variables (which denotes all row accesses). A high value indicates excessive table scans. You should examine tables and queries for proper use of indexes. Turn on the slow query log, identify the queries using full table scan and tune it. |
| Buffer for full table scans of MyISAM tables | Read_buffer_size | This value is per client. |
| SELECTs requiring full table scan | Select_scan | A high value indicates that the queries are not tuned to use indexes. |
| Buffer for JOINs requiring full table scan | Join_buffer_size | This value is per client. |
| Joins requiring full scan of second and subsequent tables | Select_full_join | If the value is high, optimize the queries to use indexes. |
Temporary Tables
| | |
| Maximum table size | Tmp_table_size | The maximum size of an internal table that can be created in memory. If an internal table is larger than this size, it is converted into a MyISAM table on disk. |
| Maximum size of a memory table | Max_heap_table_size | MySQL uses the lowest value of tmp_table_size and max_heap_table_size. |
| Total tables created | Created_tmp_tables | Number of temporary tables created in memory. |
| Temporary tables created on disk | Created_tmp_disk_tables | If this value is high, try increasing the values of Tmp_table_size and Max_heap_table_size. |
Slow Launch Threads
| | |
| Minimum launch time for a thread to be considered slow | Slow_launch_time | |
| Number of slow launch threads | Slow_launch_threads | General system overload; may be caused by non-optimal queries. |
Network Traffic
| | |
| Bytes Received from all clients | Bytes_received | |
| Bytes Sent to all clients | Byets_sent | |
Binary Log
| | |
| Binary log enabled? | Log_bin | |
| Synchronized to disk at each write? | Sync_binlog | Synchronizing is the safest choice since in the event of a crash you lose at most the last transaction. |
| Binary log cache size | Binlog_cache_size | It stores all SQL statements for the binary log during a transaction; used only with transactional engines. |
| Transactions that used cache | Binlog_cache_use | Transactions that used the binary log cache. |
| Transactions that got saved in temporary file | Binlog_cache_disk_use | Number of transactions that used a temporary file because of insufficient Binlog_cache_size. |
| Percentage of transactions that got saved in temporary file | Binlog_cache_disk_use * 100 / Binlog_cache_use | |
Slow Query Log
| | |
| Enabled? | Log_slow_queries | Identifies queries running slow. |
| Min. execution time for a query to be considered slow | Long_query_time | |
| No. of slow queries | Slow_queries | Number of queries that took more than long_query_time to execute. |
Statements
| | |
| All Statements | Questions | Total number of statements sent to the server by all clients. |
| SELECTs | Com_select + Qcache_hits | |
| INSERTs | Com_insert + Com_replace | |
| UPDATEs | Com_update | |
| DELETEs | Com_delete | |
| Percentage of total Data Manipulation Statements | | |
System
| | |
| CPU Usage | | |
| Total Memory | | |
| Memory Used by MySQL | | |
| Memory Free | | |
| Memory Requirement by MySQL if max users are connected | Innodb_buffer_pool_size + Key_buffer_size + Max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size) + max_connections * 2MB |
