Parâmetros de Performance do MySQL

Olá galera!

Essa semana discutindo com um colega desenvolvedor sobre aplicações Web, me foi questionado quais o parâmetros necessários para deixar uma aplicação Web mais rápida, tendo como base os parâmetros do Database MySQL para deixar o BD afiado para pesquisas, diversas, levando em consideração um SQL bem escrito, entre outras coisas que são importantes.Pesquisando alguns parâmetros de performance do MySQL para aplicações web, me deparei com essa tabela abaixo, que julgo muito importante  e decidi compartilha-la para conhecimento, caso se tenha sob sua administração o MySQL

O MySQL contém diversos parâmetros para melhorar a performance do servidor e bancos de dados para aplicações Web, Móvel, etc. A planilha contem muitos parâmetros específicos para os mecanismos de armazenamento(ENGINE) que você escolher (MyIsam, InnoDB,etc) que são identificados pelo uso de ferramentas como o MySQL Administrator, phpMysqladmin e MySQL Workbench.

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

Como já viram são muitos parâmetros, próximo post trarei alguns exemplos de utilizaçao dos mesmo.


Emerson Martins

Referencias:
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://www.debianhelp.co.uk/mysqlperformance.htm
http://www.slideshare.net/osscube/mysql-performance-tuning-top-10-tips
Book High-Performance-MySQL-Optimization-Replication

Read More