MySQL system optimization and problem search

MySQL system optimization and problem search performance optimization related STATUS parameters show status like 'value '; connections uptime start time show_queries slow Query Count com_select query operation count com_insert insert operation count com_update operation count com_delete delete operation count www.2cto.com analysis query statement EXPLAIN/desc select; DISABLE/ENABLE index alter table table DISABLE/enable keys; DISABLE unique index SET UNIQUE_CHECK = 0/1 ANALYZE, check and optimize table analyze table table1 [, table2...] check table table1 [, table2...] optimize table table1 [, table2. ..] analysis S QL statement explain select count (*), max (id), min (id) from user \ G analyze the SQL statement through explain, when Profiling is used, open the profiling parameter> set profiling = 1; after executing some SQL statements, you can view the query profile information> show profiles;> show profiles cpu, blockio for query 6; # view the cpu I/O resources used by query 6 Restrictions in www.2cto.com Index 1. The total length of MyISAM engine indexes cannot exceed 1000 bytes. 2. You can only create prefix indexes for BLOB and TEXT columns. 3, mySql does not support function indexing. 4. Use is not equal (! = Or <>), the index cannot be used. 5. After a filter field uses a function (for example, abs (column )) if the types of Join condition fields in the Join statement are inconsistent, indexes cannot be used. 7. If the Like operation is used, the condition starts with a wildcard ('% abc... ') indexes cannot be used. 8. When non-equivalent queries are used, you cannot use the hash index query efficiency test tool mysqlslap $ mysqlslap -- create-schema = example -- query = "select * from group_message where user_id = 3 AND subject like 'weiurezs % '-- iterations = 10000 # used to test the query execution efficiency, average, maximum, and minimum execution time are given. Force index (INDEX name) enforces the use of index explain select * from group_message force index (idx_group_message_author_subject) where user_id = 3 AND author = '3' AND subject like 'weiurazs % '\ g performance tuning -- log sets the Mysql log items: error log, Update log, binary log, query log, slow query log Binlog> show variables like '% binlog %'; slow query> show variables Ables like 'Log _ slow % ';> show variables like 'long _ query % '; the minimum value of long_query_time is 1 second. To further shorten the time limit for slow queries, you can use the microslow-path ( http://www.mysqlperformanceblog.com/2008/04/20/updated-msl-microslow-path-installation-walk-through/ ) Www.2cto.com Performance Optimization-Query Cache viewing Query Cache system variables> show variables like '% query_cache %'; Understanding Query Cache Usage> show status like 'qcache % '; cache hit rate = Qcache_hits/(Qcache_hits + Qcache_inserts) Qcache_hits/(Qcache_hits + Com_select) should be more accurate. Disadvantages: 1. the hash operation and search resource of the Query statement increase the CPU resource consumption. 2. Query Cache failure problem (when the table is updated frequently, it will cause a very high failure rate. 3. result Set, instead of pages, it may cause excessive memory consumption and a decrease in the hit rate caused by too many swap-in and swap-out due to insufficient memory. Countermeasures: 1. Specify the SQL Hint of SQL _NO_CACHE for frequently updated records, and force MySQL not to cache. 2. Specify SQL _CACHE for most static data and use the CACHE. 3. For those queries with large Result sets, either use SQL _NO_CACHE to force no CACHE, or Set the query_cache_limit parameter to control the maximum Result Set in the Query. The default value is 1 MB, result Set greater than this value will not be cached. Query Cache restrictions 1. Versions earlier than 5.1.17 cannot Cache variable queries. However, from 5.1.17, Query Cache has started to support variable Query; 2. External Query SQL statements in all subqueries cannot be cached. 3. Query statements in Procedure, Function, and Trigger cannot be cached; 4. the Query that contains many other functions that may obtain different results each time cannot be cached. Www.2cto.com Performance Optimization-other commonly used optimization max_connections (maximum number of connections): generally set to around-max_user_connections (maximum number of connections allowed by each user): Generally, net_buffer_length (network transmission cache) is not restricted ): the default value of 16 KB is basically enough to use thread_cache_size (number of connection threads that the Thread Cache pool should store): it should not be smaller than the actual number of concurrent requests to the database by the application system, generally between 50. It works well for short connections. Related system setting values and status values> show variables like 'thread % ';> show status like 'connections';> show status like' % thread % '; thread Cache hit rate: (90% or more) Thread_Cache_hit = (Connections-Threads_created)/Connections * 100%; MyISAM engine optimization MyISAM engine applicable scenarios: Read-based non-transactional Data Systems, high data accuracy requirements provide excellent performance. System parameter: key_buffer_size: Index Cache Size key_buffer_block_size: Cache Block Size in the index Cache: Demarcation value between Hot Area and Warm Area in the LRU linked list (range: 1-100 ), the default value is 100, and only the Warm Cache is used. Key_cache_aeg_threshold: limits the performance parameters for controlling the Cache Block from Hot Area to Warm Area: key_block_not_flushed: the number of key_read_requests Cache blocks requested to be read by key_block_not_flushed that have been changed but have not been refreshed to the disk's Dirty Cache Block key_blocks_unused, the Key information to be read is not found in the Cache Block. the number of times key_write_requests are read in the MYI file, and the total number of times the Cache Block is requested to be modified. key_writes cannot find the Key information to be modified in the Cache Block, and then go. rationality of the number of times of reading and modifying in the myi file: Key_buffer_UsageRatio = (1-Key_bloc Ks_used/(key_blocks_used + key_blocks_unused) * 100% (it should be above 99%. If this value is too low, it indicates that key_buffer_size is too large and MySQL cannot be used up) bytes = (1-Key_reads/key_read) * 100% (the value should be greater than 99%. If the value is too low, it indicates that key_buffer_size is set too small and needs to be increased. It may also be due to improper key_cache_age_threshold and key_cache_division_limit settings, leading. In general, in actual application scenarios, few people adjust the values of the delimiter and key_cache_division_limit parameters, most of which use the default value of the system) Key_buffer_write_HitRatio = (1-Key_writes/key_Write_requests) * For more than 100% Cache systems, MySQL officially recommends that you set three Key caches on busy systems: A Hot Cache uses 20% of the size to store indexes of tables that are frequently used and rarely updated; a Cold Cache uses 20% of the size to store indexes of tables that are frequently updated; A Warm Cache uses the remaining 60% space as the default Key Cache of the entire system. The Mutex problem of the Key Cache: currently, MySQL is very prone to Cache when the number of Active threads is high. Block lock problem www.2cto.com Key Cache is pre-loaded in MySQL, in order to prevent the system from being overloaded for a short period of time due to no data in the Cache after it is started, or the response is not timely enough. MySQL provides the Key Cache pre-loading function. You can use the relevant commands (load index into cache tb_name_list ...), load all indexes of the specified table to the memory. You can also use relevant parameters to determine whether to Load only the root node and the branch node, or Load all the page nodes, this is mainly used to consider the capacity of the Key Cache. You can use the init_file parameter of MySQL to set related commands for loading operations immediately after startup, as follows: mysql @ sky :~ $ Cat/usr/local/mysql/etc/init. sqlSET GLOBAL hot_cache.key_buffer_size = 16777216 set global cold_cache.key_buffer_size = 16777216 cache index example. top_message in hot_cacheCACHE INDEX example. event in cold_cacheLOAD index into cache example. top_message, example. event ignore leaves here, in my init file, we first set the two Key caches (hot Cache and cold cache) to 16 MB each, then, Cache the indexes of the tables with few changes in top_message to Hot Cache, and then run ev The INDEX of the table with frequent changes of ent is cached in the Cold Cache, And the top_message is preloaded through the load index into Cache command, the data of all the nodes and non-leaf nodes indexed by the event and user tables in the groups table is sent to the Key Cache to improve the response capability at the beginning of the system startup. Www.2cto.com can be optimized. 1. Run the OPTIMIZE command to sort out files in the MyISAM table. This is like we use the Windows operating system to perform disk fragmentation every time after a period of time, so that the files in the system can use continuous space as much as possible to improve the file access speed. MyISAM uses OPTIMIZE to OPTIMIZE and organize the entire file. It also cleans up the fragments caused by data deletion and update. Generally, an OPTIMIZE operation is required after a large data deletion operation. In addition, there should be one OPTIMIZE maintenance operation every quarter. 2. Setting myisam_max _ [extra] _ sort_file_size is large enough, which may greatly improve the repair table efficiency. 3. You can adjust the value of the myisam_sort_buffer_size parameter at the session level before executing create index or repair table that requires large sorting operations to improve the efficiency of sorting operations. 4. Enable the delay_key_write function to reduce IO synchronization operations and improve write performance. 5. Adjust bulk_insert_buffer_size to improve the overall performance of the INSERT... SELECT... bulk insert operation. The performance of load data infile... can also be improved. Of course, when setting this parameter, we should not blindly pursue a great deal. In many cases, the extreme pursuit of transition will affect the overall system performance. After all, the system performance is from the overall perspective, instead of simply targeting one or more types of operations. MySql MyISAM and innodb table fragments are optimized. For MyISAM TABLE types, optimize table table_name SQL statements are used to clear fragments. the Clustered Index used by InnoDB is bound together with the Index and data, which is unrealistic. therefore, the MyISAM OPTIMIZE is not supported, but bound to the alter table command. you can execute the following statement to sort the shards and improve the index speed: alter table table_name ENGINE = Innodb; this is actually a NULL operation. On the surface, nothing is done, and actually the fragments are reorganized. during the optimization operation, an empty ALTER command is actually executed, but this command also plays an optimization role. It will recreate the entire table and delete unused blank space. there are four main differences between innodb Storage engine optimization and MyISAM storage engine. The first is the cache mechanism, and the second is transaction support. The third is locking implementation, and the last is the difference in data storage methods. In terms of overall performance, the two storage engines Innodb and MyISAM differ significantly in different scenarios, mainly because of the four main differences above. Www.2cto.com Innodb_buffer_pool_size is assumed to be a single host for MySQL. The total physical memory size is 8 GB, the maximum number of MySQL connections is 500, and the MyISAM storage engine is also used, how should we allocate the overall memory? The memory is allocated to the following parts: 1. The system is used. Assume that 800 mb is reserved; 2. The thread is exclusive, about 2 GB = 500*(1 MB + 1 MB + 1 MB + 512KB + 512KB). The composition is as follows: sort_buffer_size: 1 MB join_buffer_size: 1 MB read_buffer_size: 1 MB read_rnd_buffer_size: 1.5 kb thread_statck: KB 3. MyISAM Key Cache, which is approximately GB. 4. Maximum Innodb Buffer Pool usage: 8 GB-800 MB-2 GB-1.5 GB = 3.7 GB the real-time status information of the Buffer Pool is used to determine whether InnoDB Buffer Pool is used efficiently:> show status like 'innodb _ buffer_poo L _ % '; Innodb_Buffer_pool_HitRatio = (bytes-Innodb_buffer_pool_reads)/usage * 100% buffer pool usage = usage/usage * 100% innodb_log_buffer_size parameter, as the name suggests, this parameter is used to set the Log Buffer size of Innodb. The default value is 1 MB. The main function of Log Buffer is to Buffer Log data and improve the I/O performance of Log writing. Generally, if your system does not have a very high write load and a large transaction volume, the size within 8 MB is enough.> Show status like 'innodb _ log % '; (check whether innodb_log_buffer_size is set properly) physical storage structure of the innodb Storage engine: minimum unit: page (16 KB by default) --> extent (64 consecutive pages) --> segment (one or more extent) --> tablespace (the largest physical structure unit, composed of multiple segments) innoDB performance monitoring> show innodb status \ G: create table innodb_monitor (a int) engine = innodb; after creating an innodb_monitor empty table, innoDB outputs information every 15 seconds and records it in the Error Log. by deleting the table, we can also enable and disable innodb_tablespace_monitor, innodb_lock_monitor in the same way, innodb_table_monitor these three monitoring functions MySQL high availability solution 1, MySQL Replication 2, MySQL Cluster 3, DRDB