Detailed MySQL Database optimization method

Process with analyze and deal with them on a regular basis

ANALYZE [Local | No_write_to_binlog] TABLE tb1_name[, Tbl_name] ...

Define Tables Analyze Table table_name

CHECK TABLE Tb1_name[,tbl_name] ... [option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

Optimize tables on a regular basis

OPTIMIZE [Local | No_write_to_binlog] TABLE tb1_name [, Tbl_name] ...

You should use the Optimize Table command for table optimization. This command merges space debris in a table and eliminates space waste due to deletions or updates, but the Optimize Table command works only on MyISAM, BDB, and InnoDB tables.

For example: Optimize table table_name

Here's how the MySQL tutorial server optimizes configuration

(1), Back_log:
Require MySQL to have the number of connections. This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit very short) to check the connection and start a new thread.
The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of this queue. Attempting to set Back_log above your operating system will be ineffective.
When you look at your host process list, find a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | NULL to connect process, it is necessary to increase the value of Back_log. The default value is 50, and I'll change it to 500.

(2), Interactive_timeout:
The number of seconds the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the client_interactive option for Mysql_real_connect (). The default value is 28800, and I'll change it to 7200.

(3), Key_buffer_size:
The index block is buffered and is shared by all threads. Key_buffer_size is the size of the buffer used for the index block, adding that it can be better processed by indexing (for all read and multiple writes), to the extent that you can afford that much. If you make it too big, the system will start to change pages and it really slows down. The default value is 8388600 (8M), my MySQL host has 2GB memory, so I changed it to 402649088 (400MB).

(4), Max_connections:
The number of simultaneous customers allowed. Increase this value to increase the number of file descriptors required by mysqld. This number should be increased, otherwise you will often see Too many connections errors. The default value is 100, and I'll change it to 1024.

(5), Record_buffer:
Each thread that carries out a sequential scan assigns a buffer of this size to each table it scans. If you do a lot of sequential scans, you might want to add that value. The default value is 131072 (128K) and I change it to 16773120 (16M)

(6), Sort_buffer:
Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by action. The default value is 2097144 (2M) and I change it to 16777208 (16M).

(7), Table_cache:
The number of tables opened for all threads. Increasing this value can increase the number of file descriptors required by mysqld. MySQL requires 2 file descriptors for each unique open table. The default value is 64, and I'll change it to 512.

(, Thread_cache_size:
The number of threads stored in that can be reused. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the client's line is placed in the cache. If there are a lot of new threads, in order to improve performance you can have this variable value. By comparing the variables of connections and threads_created states, we can see the effect of this variable. I set it to 80.

(10), Wait_timeout:
The number of seconds the server waits for action on a connection before closing it. The default value is 28800, and I'll change it to 7200.

Note: Parameters can be adjusted by modifying the/etc/my.cnf file and restarting the MySQL implementation. This is a more cautious work, the results above is only a few of my views, you can be based on your own host hardware conditions (especially memory size) to further modify.

In the Apache, PHP, MySQL architecture, MySQL has the greatest impact on performance and the key core. For the discuz! Forum program is also the case, MySQL settings are reasonable optimization, directly affect the speed and load of the forum! At the same time, MySQL is also the most difficult part of optimization, not only need to understand some MySQL expertise, but also need a long time to observe statistics and based on experience to judge, and then set reasonable parameters. Below we understand some MySQL optimization foundation, the MySQL optimization I divide into two parts, one is the server physical hardware optimization; the second is the optimization of MySQL itself (MY.CNF).

(1) The impact of server hardware on MySQL performance
(a) Disk seek capability (disk I/O), with the current high speed SCSI hard drive (7200 rpm), for example, the hard drive theoretically seeks 7,200 times per second, which is determined by the physical properties and has no way to change. MySQL every second in a large number of complex query operations, on the disk read and write volume imaginable. As a result, disk I/O is generally considered to be one of the biggest constraints on MySQL performance, and for discuz! forums with daily average visits above 1 million PV, MySQL performance will be very low due to disk I/O constraints! To address this constraint, consider the following solutions: Use a raid-0+1 disk array, and be careful not to try to use Raid-5,mysql on a RAID-5 disk array not as fast as you expect; discard traditional hard drives and use faster flash storage devices. Through the discuz! company's technical engineering test, the use of flash storage devices can be 6-10 times higher than the traditional hard drive speed.
b CPU for MySQL applications, recommend the use of S.M.P. Architecture of the multi-channel symmetric CPU, for example: two Intel Xeon 3.6GHz CPU can be used.
C Physical memory for a database server using MySQL, server memory is not recommended to be less than 2GB, and it is recommended to use more than 4GB of physical memory.

(2) MySQL's own factors when the above server hardware constraints are resolved, let's see how MySQL's own optimization is operating. The optimization of MySQL itself is mainly to adjust the parameters of its configuration file MY.CNF. Here are some of the parameters that have a greater impact on performance. Since the optimization settings of the my.cnf file are closely related to the server hardware configuration, we specify an imaginary server hardware environment:
Cpu:2 Intel Xeon 2.4GHz Memory: 4GB DDR hard drive: SCSI 73GB
Below, we are based on the above hardware configuration combined with a MY.CNF has been optimized to explain:
# VI/ETC/MY.CNF the contents of the [MYSQLD] paragraph in the my.cnf file are listed below and the other paragraphs have little impact on the performance of the MySQL operation, so ignore it.
[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-locking
# avoid MySQL external locking, reduce error probability and enhance stability.
Skip-name-resolve disables MySQL for DNS resolution of external connections, which eliminates the time that MySQL can perform DNS resolution. Note, however, that if this option is turned on, all remote host connection authorizations should be in the IP address mode, otherwise MySQL will not handle the connection request properly!

Back_log = 384 Specifies the number of possible connections to MySQL. When the MySQL main thread receives a very wide number of connection requests within a short time, this parameter takes effect, and the main thread takes a short time to check the connection and start a new thread.
The value of the Back_log parameter indicates how many requests can be on the stack in the short time before MySQL temporarily stops responding to the new request. If the system has many connections in a short time, you need to increase the value of the parameter, which specifies the size of the incoming TCP/IP connection's listening queue. The different operating systems have their own limits on the size of this queue. Attempting to set Back_log above your operating system will be ineffective. The default value is 50. For Linux systems It is recommended to set to an integer less than 512.

Key_buffer_size = 256M
# KEY_BUFFER_SIZE Specifies the size of the buffer to use for the index, increasing it for better index processing performance. This parameter can be set to 256M or 384M for servers with around 4GB. Note: The value of this parameter set too large will be the overall efficiency of the server down!
Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K
Sort_buffer_size = the size of the buffer that can be used when the 6M query is sorted. Note: This parameter corresponds to the allocated memory is per connection exclusive! If there are 100 connections, the total sorted buffer size that is actually allocated is 100x6 = 600MB. Therefore, for the existence of around 4GB server recommended set to 6-8m.

read_buffer_size = buffer size that can be used by the 4M read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

Join_buffer_size = 8M the size of the buffer used by the Federated query operation, as in Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!

Myisam_sort_buffer_size = 64M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64M Specifies the size of the MySQL query buffer. You can observe this by performing the following command at the MySQL console:
# > Show VARIABLES like '%query_cache% ';
# > Show STATUS like ' qcache% ';
# If the value of the qcache_lowmem_prunes is very large, it shows that the buffer is often not enough; if the value of qcache_hits is very large, the query buffer is used very frequently, if the value is small but it will affect efficiency, then you can consider not to query buffer; qcache _free_blocks, if the value is very large, it indicates that there are a lot of fragments in the buffer.

SQL Optimization method

, the result format of the EXPLAIN output changes, making it more appropriate for example UNION statements, subqueries, and the structure of derived tables. More notably, it adds 2 fields: IDs and Select_type. These fields are not visible when you use a version earlier than MySQL 4.1.

Each row of the EXPLAIN results shows information about each table, and each row contains the following fields:

Id

The identifier for this SELECT. In a query, each SELECT has a numeric value in the order.

Select_type

The type of SELECT may have the following:

Simple

Simple SELECT (no UNION or subquery used)

PRIMARY

The outermost SELECT.

UNION

The second layer, which uses UNION after the select.

DEPENDENT UNION

The second SELECT in the UNION statement relies on the outer subquery

Subquery

The first SELECT in a subquery

DEPENDENT subquery

The first subquery in a subquery relies on an external subquery

DERIVED

Derived table SELECT (subquery in FROM clause)

Table

Records the tables referenced by the query.

Type

Table connection type. Here are a list of different types of table joins, from best to worst in turn:

System

The table has only one row of records (equal to the system table). This is a special case of the const table join type.

Const

The table has at most one row matching records that are read at the beginning of the query. Because there is only one row of records, the field values of the row records in the remainder of the optimizer can be treated as a constant value. The Const table query is very fast because it is read once!const used for a fixed value comparison in a PRIMARY KEY or a UNIQUE index. In the following few queries, the tbl_name is the const table:

　　
Select*fromtbl_namewhereprimary_key=1;
Select*fromtbl_name
whereprimary_key_part1=1andprimary_key_part2=2;

Eq_ref

A row of records from the table is read to unite with records read from the previous table. Unlike the const type, this is the best connection type. It is used in all parts of the index to make a connection and this index is a PRIMARY KEY or a UNIQUE type. Eq_ref can be used to retrieve a field when a "=" comparison is made. The value of the comparison can be either a fixed value or an expression, and the fields in the table can be used in the expression, and they are ready before they are read. In the following examples, MySQL uses a eq_ref connection to handle ref_table:

　　
Select*fromref_table,other_table
Whereref_table.key_column=other_table.column;
Select*fromref_table,other_table
Whereref_table.key_column_part1=other_table.column
Andref_table.key_column_part2=1;

Ref

All records that match the retrieved values in the table are pulled out and combined with records taken out of the previous table. Ref is used by the connector to use the leftmost prefix of a key, or if the key is not a PRIMARY key or a UNIQUE index (in other words, the connection program cannot obtain only one record based on the key value). This is a good connection type when only a few matching records are queried based on the key value. Ref can also be used to retrieve the time when the field is compared using the = operator. In the following few examples, MySQL will use ref to process ref_table:

　　
select*fromref_tablewherekey_column=expr;
Select*fromref_table,other_table
Whereref_table.key_column=other_table.column;
Select*fromref_table,other_table
Whereref_table.key_column_part1=other_table.column
Andref_table.key_column_part2=1;

Ref_or_null

This type of connection is similar to ref, and MySQL will search for additional records that contain NULL values at the time of retrieval. This type of connection is optimized from the MySQL 4.1.1, which is often used for subqueries. In the following example, MySQL uses the ref_or_null type to handle ref_table:

　　
Select*fromref_table
Wherekey_column=exprorkey_columnisnull;

Index_merge

This type of connection means that the Index Merge optimization method is used. In this case, the key field includes all the indexes used, and Key_len includes the longest portion of the keys used. Please see "7.2.5 how MySQL optimizes OR clauses" for details.

Unique_subquery

This type replaces ref with, for example, a form in subquery:

Value in (SELECT primary_key from single_table WHERE some_expr)

Unique_subquery is more efficient than the index lookup function that is used to completely replace a subquery.

Index_subquery

This type of connection is similar to Unique_subquery. It replaces in with a subquery, but it is used in cases where there is no unique index in the subquery, such as the following:

Value in (SELECT key_column from single_table WHERE some_expr)

Range

Only records in a given range are taken out and the index is used to get a record. The key field indicates which index is used. The Key_len field includes the longest part of the key used. The REF field value is NULL when this type. Range is used to compare a field and a colonization with any of the following operators =, <>, >=,,, <=, is NULL, <=>, BETWEEN, or in:
Tmp_table_size = 256M
Max_connections = 768 Specifies the maximum number of connection processes allowed by MySQL. You need to increase the value of a too Many connections error message when you visit the forum frequently.

Max_connect_errors = 10000000
Wait_timeout = 10 Specifies the maximum connection time for a request, and the server with about 4GB of memory can be set to 5-10.

Thread_concurrency = 8 This parameter takes the value of the server logical CPU number x2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4x2 = 8

Skip-networking turn on this option to completely shut down MySQL TCP/IP connection, if the Web server is a remote connection access to the MySQL database tutorial server do not open this option! Otherwise you will not be able to connect properly!