Optimize your mysql by viewing mysql configuration parameters and status

Mysql monitoring methods are roughly divided into two categories: 1. connect to the mysql database and use showstatus, showvariables, and flushstatus to view various mysql performance indicators. 2. directly use mysqladmin to view its performance indicators, for example, UserParameter = mysql. uptime, mysqladmin-urootstatus | cut mysql monitoring methods are roughly divided into two categories: 1. connect to the mysql database and use show status, show variables, and flush status to view various mysql performance indicators. 2. directly use mysqladmin to view its performance indicators, for example, UserParameter = mysql. uptime, mysqladmin-uroot status | cut-f2-d ":" | cut-f1-d "T" mysqladmin two parameters: status, extended-statusshell> mysqladmin-uroot-ppassword variables status. you can obtain The following information (detailed description later): -------------------------------------------------------------------------------------------------------------------------- Uptime: 4557887 # Number of mysql running seconds Threads: 1 # number of connections Questions: 1684130 # The num Ber of questions (queries) from clients since the server was started. slow queries: 0 # The number of queries that have taken more than long_query_time secondsOpens: 221872 # The number of tables the server has opened. flush tables: 1 # The number of flush-*, refresh, and reload commands the server has executed. open tables: 64 # The number of tables that currently are open. queries per second avg: 0.369 # From the last run, average number of queries per second export Questions = Com _ * + Qcache_hits the most complete information shell> mysqladmin-uroot-ppassword variables extended-status Other information shell>/usr/libexec/mysqld -- verbose -- help (this command generates a list of all mysqld options and configurable variables) mysql> show status; (server STATUS variable, running server statistics and STATUS indicators) mysql> show variables; (server system variable, actually used variable value) or mysql> SHOW STATU S like '% variable name %'; description of configuration parameters: the configuration parameter format is as follows: (shell> mysqladmin-uroot-ppassword variables extended-status) ----------------------------- + hour + | Variable_name | Value | + ----------------------------------------- + hour + | auto_increment_increment | 1 | auto _ Increment_offset | 1 | automatic_sp_privileges | ON | ......... note: The unit of the value is byte. to obtain M, the value must be divided by the number of seconds that the 1024 running Uptime 20175546mysql server has run ----------------------------- auto_increment_increment 1 auto_increment_offset 1 variable values can only be an integer between 1 and 65,535. If this parameter is set to a non-integer value, an error is returned. These two variables affect the AUTO_INCREMENT column. The auto_increment_increment control column's value increment value (step quantity ). Auto_increment_offset determines the initial value of the AUTO_INCREMENT column value. Generally, do not change it. Change Method: mysql> SET @ auto_increment_offset = 5; relationship between max_connections 100table_cache parameters 1024 Open_tables 64Opened_tables 187690 parameters: table_cache * 2 + max_connections = defaults to 100 mysql> show processlist; mysql> show full processlist ;---------------------------------------- -------- Max_open_files is determined by the open_files_limit parameter. The maximum number of files opened by mysql is affected by two parameters: the maximum number of files opened by the system (ulimit-n) and open_files_limit. Increase the value of max_open_files ----------------------------------------------------------- in/etc/my. cnf is added to open_files_limit = 8192 at/etc/security/limits. add * soft nofile 8192 * hard nofile 8192 in conf -------------------------------------------------------------------- it is best to use sysctl or modify/etc/sysctl. in the conf file, the open_files_limit parameter must be added to the configuration file. for 4G memory servers, open_files_limit must be increased to at least 4096. in special cases, set it to 8192. Table_cache MySQL 5.0 is upgraded to 5.1, and table_cache is renamed table_open_cache to set the table cache quantity. Table cache description: when Mysql accesses a table, if the table has been opened in the cache, it can directly access the cache. if the table has not been cached, however, there is still space in the Mysql table buffer, so this table will be opened and put into the table buffer. if the table cache is full, the unused table will be released according to certain rules, or temporarily expand the table cache for storage. the advantage of using the table cache is that you can access the table content more quickly. Each connection will open at least one table cache. Therefore, the size of table_cache should be related to the settings of max_connections. For example, for 200 concurrent connections, the table cache should be at least 200 × N. here, N is the maximum value of the table used for one query by the website program. Each thread holds the file descriptor of a data file, while the file descriptor of the index file is public. When the table cache is insufficient, MySQL uses the LRU algorithm to kill the table that has not been used for the longest time. If the table_cache setting is too small, MySQL will repeatedly open and close the frm file, resulting in a certain performance loss. If the table_cache settings are too large, MySQL will consume a lot of CPU to perform table cache algorithm operations. The metadata management of InnoDB is implemented in the shared tablespace. Therefore, you do not need to parse the frm file repeatedly to obtain the table structure, which is better than MyISAM. Even if the table_cache setting is too small, it has little impact on InnoDB because it does not need to open or close the frm file repeatedly to obtain metadata. Reasonably set the size of table_cache: compare the values of open_tables, Opened_tables, and Flush tables. Check the current table cache: shell> mysqladmin-uroot-ppassword variables status ---------------------------------- Opens: 221872 indicates the number of opened tables. Flush tables: 1 Open tables: 64 indicates the number of opened tables ------------------------------------ mysql> show global status like 'open % _ tables '; tables open_tables indicates the number of opened tables, opened_tables indicates the number of opened tables -------------------------------- clear the table cache mysql> flush tables. if open_tables is found to be close to table_cache, if Opened_tables increases rapidly as the show status command is re-run, this indicates that the cache hit rate is insufficient. If you execute flush tables multiple times (through shell> mysqladmin-uroot-ppassword variables status), it means that the table_cache settings may be too small and cache TABLES are frequently cleared, add a new table to the cache. you can increase the size of this parameter to improve the access efficiency. If Open_tables is much smaller than table_cache, it means table_cache is too large. The value of table_cache is between 256 and 512 by default on machines with less than 2 GB of memory. if the machine has 4 GB of memory, the default value is 2048, but this definitely means that the machine has a larger memory, the value should be larger, because the increase in table_cache makes mysql's SQL response faster, and more deadlocks are inevitable ), this slows down the entire set of database operations, seriously affecting performance. Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure. For machines with 1 GB memory, the recommended value is 128-256. ------------------------------------------------------------------ Key_buffer_size 67108864 (/1024/1024 = 64 M) Key_read_requests 40944 number of data block requests for the read key from the cache. Key_reads 2711 number of data blocks read from the hard disk. Number of requests that Key_write_requests writes the key data block to the cache. The number of physical writes performed by Key_writes to the data block that writes the key to the hard disk. (Obtain information: shell> mysqladmin-uroot-ppassword variables extended-statusshell> mysqladmin-uroot-ppassword variable statusmysql> show status like '% key_read % ';) key_buffer_size sets the cache size of the index block and stores the index block of the MyISAM table. It is shared by all threads, which determines the database index processing speed, especially the index reading speed. Ideally, requests for these blocks should come from memory, not from disks. It only works for the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. Key_buffer_size: If the MyISAM storage engine is not used, 16 MB is sufficient to cache some system table information. If the MyISAM storage engine is used, put all indexes into the memory whenever possible, simply put, the greater the memory is, the better, the reasonable key_buffer_size setting method: view the ratio of Key_read_requests to Key_reads, key_reads indicates the number of requests that hit the disk, and Key_read_requests indicates the total number. The number of read requests that hit the disk divided by the total number of read requests is the ratio of not to the middle. If the number of disks hit by each 1,000 requests exceeds 1, you should consider increasing the keyword buffer. The key_reads/key_read_requests value should be as low as possible, such. This parameter can be set to 384 M or M for servers with around 4 GB of memory. Note: If this parameter value is set too large, the overall efficiency of the server will be reduced! Limit MB memory and many tables. to achieve maximum performance in a medium number of customers, use: shell> mysqld_safe -- key_buffer_size = 64 M -- table_cache = 256 -- sort_buffer_size = 4 M -- read_buffer_size = 1 M & bytes -------------------- each connection to My All SQL server threads need to have their own buffer. by default, 256 K is allocated to them. After the transaction starts, more space is required. Running a small query may only add a small amount of memory consumption to the specified thread, such as the storage space of the query statement. However, complex operations on data tables are complicated. for example, a temporary table is required for sorting. in this case, a memory space of about read_buffer_size, sort_buffer_size, read_rnd_buffer_size, and tmp_table_size is allocated. However, they are allocated only when needed and released after those operations are completed. Myisam_sort_buffer_size 8388608 when sorting the buffer allocated by MyISAM indexes during repair table, create index, or alter table. Sort_buffer_size 2097144 the size of the buffer allocated by each sort thread. Adding this value can speed up the order by or group by operation. Note: The allocated memory corresponding to this parameter is exclusive to each connection. if there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 MB. Mysql> show status like "sort %"; ------------------------- Sort_merge_passes 1 Sort_range 79192 Sort_rows 2066532 Sort_scan 44006 bytes if sort_merge_passes is large, you need to pay attention to sort_buffer_size. When MySQL needs to sort data, it will allocate a sort buffer when reading data from the disk to store the data rows. If the data to be sorted is too large, the data must be saved to the temporary files on the disk and sorted again. If the sort_merge_passes status variable is large, it indicates disk activity. Read_buffer_size 131072 (show variables like 'read % ';) -------------- read_buffer_size 1048576read_rnd_buffer_size 524288 --------------- read_buffer_size is the size of the buffer read from MySql. Requests that perform sequential scans on the table will be allocated with a read buffer, and MySql will allocate it with a memory buffer. The read_buffer_size variable controls the size of the buffer. The size (in bytes) of the buffer allocated to each table scanned during continuous scanning by each thread ). If you perform multiple consecutive scans, you may need to add this value. The default value is 131072. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection. Read_rnd_buffer_size is the random read buffer size of MySql. When a row is read in any order (for example, in the sorting order), a random read cache is allocated. During sorting query, MySql first scans the buffer to avoid disk search and increase the query speed. if you need to sort a large amount of data, you can increase the value accordingly. The allocated memory corresponding to this parameter is also exclusive to each connection. Join_buffer_size 131072 the buffer size that can be used by the joint query operation. like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection. -------------------------------------------------------------------------------------------------------- Max_allowed_packet 1048576net_buffer_length 16384 packet message buffer is initialized to net_buffer_length bytes, but can be increased to max_allowed_packet bytes as needed. This value is small by default to capture large (possibly incorrect) packets. -------------------------------------------------------------------------------------------------------- Thread_stack 196608 the size of each thread's stack. Many of the limitations detected by the crash-me test depend on this value. The default value is large enough for normal operations. Export thread_cache_size 0query_cache_size 0tmp_table_size limit 8max_connections limit 10 (obtain information: shell> mysqladmin-uroot-ppassword variables extended-statusshell> mysqladmin-uroot-ppassword variable status) thread_cache_size (mysql> show status LIKE 'Threads % ';) --------------------------- Threads_cached 27 Threads_connected 15 Threads_created 838610 Threads_running 3 -------------------------- thread cache. Mysqld will generate a thread as needed when receiving connections. On a busy server with fast connection changes, caching threads for later use can speed up the initial connection. The important value here is Threads_created. this value increases every time mysqld needs to create a new thread. If this number increases rapidly when the show status command is executed continuously, you should try to increase the thread cache. Query_cache_sizemysql> show variables like 'have _ query_cache '; mysql> show variables like' % query % '; limit 20 have_query_cache YES long_query_time 10.000000 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type ON limit OFF query_prealloc_size 8192 slow_quer Y_log OFF slow_query_log_file/var/run/mysqld/mysqld-slow.log ------------------------------------- have_query_cache whether the query cache query_cache_limit specifies the buffer size that a single query can use. the default value is 1Mquery_cache_type, which affects the working mode. This variable can be set to the following values: 0 or OFF to prevent caching or query cache results. 1 or ON will allow caching, except for query statements starting with SELECT SQL _NO_CACHE. 2 or DEMAND. only cache is enabled for query statements starting with SELECT SQL _CACHE. If innodb storage engine is used, it is recommended that it be 0. if MyISAM storage engine is used, it is recommended that 2query_cache_min_res_unit be introduced after version 4.1, which specifies the minimum unit for allocating buffer space, the default value is 4 K. Check the status value Qcache_free_blocks. if the value is very large, it indicates that there are many fragments in the buffer. This indicates that the query results are relatively small. in this case, reduce query_cache_min_res_unit. Query_cache_size the amount of memory allocated to store the old query results (specified in bytes ). If it is set to 0, the query buffer is disabled (the default value is 0 ). Adjusted based on the hit rate (Qcache_hits/(Qcache_hits + Qcache_inserts) * 100). generally, it is not recommended that the hit rate be too large, and MB may be similar, large configuration static data can be adjusted to mysql> show status like 'qcache % '; Qcache_free_blocks 5216 Qcache_free_memory 14640664 Qcache_hits 2581646882 Qcache_inserts 360210964 limit 281680433 limit 79740667 limit 16927 ----------------- ------------------------ Qcache_free_blocks the number of adjacent memory blocks in the cache. A large number of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block. Qcache_free_memory idle memory in the cache. Qcache_hits increases when the query hits the cache. Qcache_inserts increases every time a query is inserted. Miss and insert. If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is insufficient frequently. if the Qcache_hits value is very large, it indicates that the query buffer is frequently used. in this case, you need to increase the buffer size, if the value of Qcache_hits is small, it indicates that the query repetition rate is very low. in this case, the query buffer will affect the efficiency, so you can consider not to use the query buffer. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The free_blocks and free_memory above can tell you what the situation is ). Qcache_not_cached is not suitable for the number of cache queries, usually because these queries are not SELECT statements. Qcache_queries_in_cache the number of queries (and responses) cached currently. Qcache_total_blocks. Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached. query hit rate = Qcache_hits-Qcache_inserts/Qcache_hits query insert rate = Qcache_inserts/Com_select; no insert rate = Qcache_not_cached/Com_select; many LAMP applications rely heavily on databases, however, the same query is executed repeatedly. Each time a query is executed, the database must perform the same job-analyze the query, determine how to execute the query, load the information from the disk, and then return the result to the client. MySQL has a feature called query cache. the query cache stores the text of a SELECT query and the results sent to the client. If the same query is received later, the server will retrieve the result from the query cache, instead of re-analyzing and executing the same query. In many cases, this will greatly improve the performance. However, the problem is that the query cache is disabled by default. Normally, you can see the difference when the variables are displayed several seconds later, which helps determine whether the cache is being effectively used. Running flush status can reset some counters. if the server has been running for a while, this will be very helpful. The idea of using a very large query cache to cache everything is very attractive. However, if the table changes, you must first set all the Query_cache statements related to the table to invalid, and then write updates. If Query_cache is very large, the query structure of the table is large, and the query statement becomes invalid, an update or Insert operation will be slow, in this way, we can see how the Update or Insert operation is so slow. This parameter is not suitable for systems with a large number of database writes or updates. This function is disabled for systems with high concurrency and large write volumes. As a rule, if the flush query cache takes a long time, it indicates that the CACHE is too large. Wait wait_timeout 28800 the number of seconds before the server closes the non-interactive connection. When a thread starts, the wait_timeout value of the session is initialized based on the global wait_timeout value or the global interactive_timeout value, depending on the number of seconds that the client-type connect_timeout 10mysqld server waits for the connection package before responding with Bad handshake. Interactive_timeout 28800 the number of seconds the server waits for activity before closing the interactive connection. Interactive clients are defined as clients that use the CLIENT_INTERACTIVE option in mysql_real_connect. Using mysql> show status like "com_select"; ------------------------------- Com_select 318243 ----------------------------------- mysql> show status like "handler_read_rnd_next ";