There are many online MySQLperformance optimization scheme ,however ,MySQL optimization with SQL compared to server, more trouble with complex ,the same setting ,in a different environment ,due to memory, access ,read and write data difference frequency ,etc.
,may show different results ,so simply according to a given scheme to configure the MySQL is not feasible, preferably using status information for MySQL specific optimization ,online to find an article ,paging share at sixes and sevens ,can only go to the blog .
Mysql> ;show global status ;MySQL server can list run a variety of State & # 20540 ;,in addition, query the MySQL server configuration information statement: mysql> ;show variables ;a slow queries ,mysql> ;show variables like slow% ;& # 43 ;these & # 43 ;- - - - - - - & # 43 ;variable_name value & # 43 this # 43 & ;;- - - - - - - & # 43 ;log_slow_queries on slow_launch_time 2 & 43 & 43 # ;this # ;- - - - - - - & # 43 ;mysql> ;show global status like slow% ;& # 43 ;--------------------- & # 43 ;- - - - - - - & # 43 ;variable_name value & # 43 ;--------------------- & # 43 ;- - - - - - - & # 43 ;slow_launch_threads 0 slow_queries 4148 & # 43 ;--------------------- & # 43 ;- - - - - - - & # 43 ;configuration opens the recording of slow query ,the execution time for more than 2 seconds is the slow query ,system shows there are 4148 slow query ,you can analyze the slow query log ,find out the Problem SQL statement ,slow query time not set too long ,otherwise meaningless ,preferably within 5 seconds Or less, if you need a microsecond level slow query ,can consider to patch your MySQL :http: / / www.
percona.com / docs / wiki / release: start ,remember to find the corresponding version .Open the slow query log may have a little effect on the performance of the system ,if your MySQL is the master-slave structure ,can consider to open one from the server is slow query log ,which can monitor the slow query ,influence on system performance and small .
Two ,number of connections often meet MySQL: error 1040: too many connections is a visit quantity is high ,the MySQL server cannot withstand ,this time will be considered to increase from the server scatter read pressure ,another is the MySQL configuration file in max_connections& 20540 :mysql> # ;too small ;show variables like max_connections ;& # 43 ;----------------- & # 43 ;- - - - - - - & # 43 ;variable_name value & # 43 ;----------------- & # 43 ;- - - - - - - & # 43 ;max_connections 256 & # 43 ;----------------- & # 43 ;- - - - - - - & # 43 ;this MySQL server is the maximum number of connections is 256 ,then check the server in response to the maximum number of connections :mysql> ;show global status like MySQL server in the past ;the maximum number of connections is 245 ,did not reach the server connection limit 256 ,1040 should be no mistake ,the ideal set max_used_connections / max_connections * 100% = 85% the maximum number of connections for connecting the upper limit number around 85%, if it is found that the ratio below 10% ,Moncler Coats,the MySQL server connection limit set too high .
In three ,key_buffer_size key_buffer_size MyISAM table is to influence the performance of one of the largest parameters ,following a to MyISAM as the main storage engine server configuration :mysql> ;show variables like key_buffer_size ;& # 43 ;----------------- & # 43 ;- 43 & # ;variable_name value & # 43 ;----------------- & # - 43 ;& # 43 ;key_buffer_size 536870912 & 43 ----------------- & # # ;43 ;- 43 & # ;assign a 512MB memory for key_buffer_size ,we look at the key_buffer_size usage :mysql> show global ;status like key_read% ;& # 43 ;------------------------ & # 43 ;------------- & # 43 ;variable_name value & # 43 ;------------------------ & # 43 ;------------- & # 43 ;key_read_requests 27813678764 key_reads 6798830 & 43 & 43 # ;------------------------ # ;------------- & # 43 ;a total of 27813678764 index read requests ,have 6798830 requests in memory is not looking for To directly read from the hard disk index ,calculating the index cache misses the probability :key_cache_miss_rate = key_reads / key_read_requests * 100% such as the above data ,the key_cache_miss_rate is 0.
0244% ,a 4000 index read request is a direct reading of the hard disk ,is BT ,key_cache_miss_rate is in 0.1% the following are good ( each 1000 requests a direct reading disk ) ,if the key_cache_miss_rate is in 0.
01% the following words ,key_buffer_size assigned too much ,can appropriately reduce .The MySQL server also provides key_blocks _ * parameter :mysql> ;show global status like key_blocks_u% ;& # 43 ;------------------------ & # 43 ;------------- & # 43 ;variable_name value & # 43 ;------------------------ & # 43 ;------------- & # 43 ;key_blocks_unused 0 key_blocks_used 413543 & 43 & 43 # ;------------------------ # ;------------- & # 43 ;key_blocks_unused said unused cache cluster ( blocks ) ,key_blocks_used said that once used the largest blocks number,Nike Shoes, such as the server ,all of the cache are used, either to increase key_buffer_size ,or transition index ,the cache fills .
Ideal settings: key_blocks_used / ( key_blocks_unused & # 43 ;key_blocks_used ) * 100% = 80% four ,mysql> show global temporary table ;status like created_tmp% ;& # 43 ;------------------------- & # 43 - & ;# 43 ;variable_name value & # 43 ;------------------------- & # 43 - & # 43 ;created_tmp_disk_tables ;21197 created_tmp_files 58 created_tmp_tables 1771587 & 43 ------------------------- & # # ;43 ;- 43 & # ;each time you create a temporary table ,created_tmp_tables increased ,if the disk is created on a temporary table, created_tmp_disk_tables also increases ,said created_tmp_files MySQL services to create a temporary file number, compared to the ideal configuration is :created_tmp_disk_tables / created_tmp_tables * 100% < ;= 25% for example above the server created_tmp_disk_tables / created_tmp_tables * 100% = 1.
20% ,is supposed to be good .We then look at the MySQL server on a temporary table configuration :mysql> ;show variables where variable_name in ( , ) ;& # 43 ;--------------------- & # 43 - & # ;43 ;variable_name value & # 43 ;--------------------- & # 43 - & ;# max_heap_table_size 268435456 tmp_table_size 536870912 & 43 ;# 43 ;--------------------- & # 43 ;43 - & # ;only 256MB following the temporary table to put all of the hard disk memory ,than will be used temporary table .
In five ,open table mysql> show global status ;like open%tables% ;& # 43 ;this & # 43 ;- - - - - - - & # 43 ;variable_name value & # 43 ;this & # 43 ;- - - - - - - & # 43 ;open_tables 919 opened_tables 1951 & 43 & 43 # ;this # ;- - - - - - - & # 43 ;open_tables opened tables ,said opened_tables opened table number ,if the opened_tables number is too large ,the instructions to configure table_cache ( 5.
1.3 after this & # 20540 ;called table_open_cache) & # 20540 ;may be too small ,we check the server table_cache& # 20540 ;mysql> ;show variables like table_cache ;& # 43 this # 43 & ;;- - - - - - - & # 43 ;variable_name value & # 43 ;this & # 43 ;- - - - - - - & # 43 ;table_cache 2048 & 43 & 43 # ;this # ;- - - - - - - & # 43 ;more suitable & # 20540 ;for :open_tables / opened_tables * 100% > = 85% open_tables / table_cache ;* 100% < = 95% ;six ,the use of mysql> process ;show global status like thread% ;& # 43 ;------------------- & # 43 ;- - - - - - - & # 43 ;variable_name Value & # 43 ;------------------- & # 43 ;- - - - - - - & # 43 ;threads_cached 46 threads_connected 2 threads_created 570 threads_running 1 & # 43 ;------------------- & # 43 ;- - - - - - - & # 43 ;if we are in MySQL server configuration file set thread_cache_size ,when the client disconnects ,server processes the client thread will be cached in response to a customer rather than destroy (the premise is cache have reached the upper limit) .
Threads_created said a number of threads ,if it is found that the threads_created& # 20540 ;large, showed that the MySQL server has been to create a thread ,which is more expensive ,can be appropriate to increase the profile of thread_cache_size& 20540 ,thread_cache_size # ;query server configuration: mysql> ;show variables like thread_cache_size ;& # ------------------- & 43 ;# 43 ;- - - - - - - & # 43 ;variable_name value & # 43 ;------------------- & # 43 ;- - - - - - - & # 43 ;thread_cache_size 64 & # 43 ;------------------- & # 43 ;- - - - - - - & # 43 ;sample server or quite healthy .
In seven ,the query cache ( query cache ) mysql> ;show global status like qcache% ;& # 43 ;------------------------- & # 43 - & ;# 43 ;variable_name value & # 43 ;------------------------- & # 43 - & # ;43 ;qcache_free_blocks 22756 qcache_free_memory 76764704 qcache_hits 213028692 qcache_inserts 208894227 qcache_lowmem_unes 4010916 qcache_not_cached 13385031 qcache_queries_in_cache 43560 qcache_total_blocks 111212 & # 43 ;------------------------- & # 43 - & # ;43 ;MySQL query cache explanation: qcache_free_blocks :cache adjacent memory block number .
The large number of instructions may have fragments .Flush query cache to cache fragments were collated ,and thus get a free block .Qcache_free_memory :free memory .Qcache_hits :each query in the cache hit increases qcache_inserts :each insertion when a query is increased .
Hits divided by the number of times is not inserted in ratio .Qcache_lowmem_prunes :cache memory and must be cleaned so as to provide more space for the times .This digital best long time ;if this number is in constant growth ,said the possibility of debris is very serious ,or memory is very low .
( the above free_blocks and free_memory can tell you what kind of situation ) qcache_not_cached :not suitable for caching query number, is usually the result of the query is not a select statement or now ( ) functions such as .
Qcache_queries_in_cache :the current cache query ( and the number of response ) .Qcache_total_blocks :cache block number .We will check the server on the query_cache :mysql> show variables like configuration ; ;& # 43 ;------------------------------ & # 43 - & ;# 43 ;variable_name value & # 43 ;------------------------------ & # 43 - & # ;43 ;query_cache_limit 2097152 query_cache_min_res_unit 4096 query_cache_size 203423744 query_cache_type on query_cache_wlock_invalidate off & # 43 ;------------------------------ & 43 & 43 - # ;# ;in the field of interpretation :query_cache_limit :more than the size of the query will be cached query_cache_min_res_unit :cache block size query cache size :query_cache_size :query_cache_type cache type ,decide what kind of query cache ,sample not cached select sql_no_cache query quer Y_cache_wlock_invalidate :when there are other client is on the MyISAM table to write ,if the query in query cache,Outlet Coach Store, returning cache results or a write operation is performed to read table that gets results .
The query_cache_min_res_unit configuration is a double-edged sword ,the default is 4KB ,setting & # 20540 ;for large data query is good, but if your query are small data query ,it is easy to cause memory debris and waste .
Query cache fragmentation rate = qcache_free_blocks / qcache_total_blocks * 100% if the query cache fragmentation rate exceeds 20% ,can use the flush query cache cache fragmentation ,or try to reduce the query_cache_min_res_unit, if your query is a small amount of data .
Query cache utilization rate = ( query_cache_size - qcache_free_memory ) / query_cache_size * 100% query cache utilization rate is in 25% the following words query_cache_size set is too large ,can be properly reduced ;the query cache utilization rate more than 80% and qcache_lowmem_prunes > 50 query_cache_size ;it may be a bit small ,Moncler jacket,but is too fragmented .
Query cache hit rate = ( qcache_hits - qcache_inserts ) / qcache_hits * 100% example server query cache fragmentation rate = 20.46% ,the query cache utilization rate = 62.26% ,query cache hit ratio = 1.
94% ,hit the target rate is very poor, may write operations are relatively frequent ,but there may be some fragments .Eight ,the use of show global sort mysql> ;status like sort% ;& # 43 ;------------------- & # 43 ;- 43 & # ;variable_name value & # 43 ;------------------- & # 43 ;- 43 & # ;sort_merge_passes 29 sort_range 37432840 sort_rows 9178691532 sort_scan 1860569 & # 43 ;------------------- & # - & 43 ;# 43 ;sort_merge_passes includes two steps .
MySQL would first try to sort in memory ,using the memory size by the system variable sort_buffer_size decided ,if its size enough to throw all the records are read into memory, the MySQL will turn every sort the results stored in memory to a temporary file ,such as Mysql to find all the records, then in the temporary file recorded a sort .
Again this sort will increase sort_merge_passes .In fact ,the MySQL could use another temporary files to store to sort the results, so usually see sort_merge_passes increased the number # & 20540 ;is built temporary file number two times .
Because used the temporary files ,so the speed may be slower ,increased sort_buffer_size can reduce sort_merge_passes and create a temporary file number .But the blind increase of sort_buffer_size does not necessarily improve the speed ,see how fast can you sort data with MySQL ?(source ,appearance & # 20284 ;by the wall ) in addition ,increased read_rnd_buffer_size ( 3.
2.3 record_rnd_buffer_size ) & # 20540 ;on the sorting operation is a bit good ,see :nine ,open files ( open_files ) mysql> ;show global status like open_files ;& # 43 ;this & # 43 ;- - - - - - - & # 43 ;variable_name value & # 43 ;this & # 43 ;- - - - - - - & # 43 ;open_files 1410 & 43 & 43 # ;this # ;- - - - - - - & # 43 ;mysql> ;show variables like the ;& # 43 ;these & # 43 ;- - - - - - - & # 43 ;variable_name value & # 43 ;these & # 43 ;- - - - - - - & # 43 ;open_files_limit 4590 & 43 & 43 # ;this # ;- - - - - - - & # 43 ;more suitable setting :open_files / open_files_limit = 100% < = 75% ;lock table table in ten ,mysql> ;show global status like table_locks% ;& # 43 ;and # 43 & ;& # - 43 ;variable_name value & 43 & 43 # ;and # - & # ;43 ;table_locks_immediate 490206328 table_locks_waited 2084912 & 43 and 43 & # # ;;- & # 43 ;table_locks_immediate represents the immediate release of table lock number, table_locks_waited needs to wait for a table lock number, if the table_locks_immediate / table_locks_waited > ;5000 ,the best use of InnoDB engine ,because InnoDB is a row lock and MyISAM is a table lock ,for the high concurrent writing of application of InnoDB effect will be better .
Examples of server table_locks_immediate / table_locks_waited = 235 ,Coach Outlet Online,MyISAM is enough .Eleven ,table scan mysql> ;show global status like handler_read% ;& # 43 & 43 ;and # ;------------- & # 43 ;variable_name value & # 43 & 43 ;and # ;------------- & # 43 ;handler_read_first 5803750 handler_read_key 6049319850 handler_read_next 94440908210 handler_read_prev 34822001724 handler_read_rnd 405482605 handler_read_rnd_next 18912877839 & 43 & # # ;and 43 ;------------- & # 43 ;each field explanation see ,V server to complete the query request times :mysql> ;show global status like com_select ;& # 43 ;this & # 43 - & ;# 43 ;variable_name value & # 43 ;this # 43 & ;& # - 43 ;com_select 222693559 & # 43 ;43 - # --- & ;& # 43 ;calculation table scan rate :table scan rate of handler_read_rnd_next / com_select if Table scan rate exceeds 4000 ,notes were too much of a table scan, likely index has not built up, increased read_buffer_size& # 20540 ;there will be some benefits ,but preferably not more than 8mb .
Postscript: mentioned in some numbers are 20540 ,# reference & ;understand the basic principle can ,in addition to MySQL provided by status& # 20540 ;and operating system ,some performance
indexes are also important ,such as the commonly used top ,iostat ,especially iostat ,now the system bottlenecks are generally in the disk IO ,on the iostat use ,can refer to :- - - - - from
Related articles：
没有评论:
发表评论