One of the MySQL optimization series

The two bottlenecks common to MySQL databases are CPU and I/O. CPU typically occurs when the data is loaded into memory or read from disk, when the data loaded is much larger than

Memory capacity, I/O bottlenecks can occur at this time, if it is a distributed application, the bottleneck may appear on the network when the query volume is large. We can use the common Linux

command to view the performance status of the system.

1, mpstat system monitoring tools, CPU statistics, the information stored in the/proc/stat file, the same applies to the multi-CPU system. Mpstat command to view multi-core CPUs

Statistical data for each compute core.

2, Vmstat is also a monitoring tool, you can display the status of the server in a given time interval information, including CPU utilization, memory usage, virtual memory exchange situation, IO Read and write situation.

3, the IOSTAT monitoring equipment IO load situation

4. Sar file read and write, system call usage, disk I/O, CPU efficiency, memory usage, process activity and IPC (interprocess communication) related activities

5. The resource occupancy status of each process in top system, the command will monopolize the foreground until the user terminates the program. This command provides real-time status monitoring of the system's processor

Query and Index analysis methods and optimization methods

Common analysis methods include slow query logs, explain analysis queries, profiling analysis, and show commands and system variables

Show status shows state information show ' the Status like ' xxx '

Show variables first system variable show variable like ' xxx '

Show InnoDB status shows the state of the InnoDB storage engine

Show Processlist view current SQL execution including execution status, lock table, etc.

Mysqladmin variables-u xxx-p XXX Display System variables

Mysqladmin extended-status-u xxx-p XXX Show status information

Slow query log Analysis

Slow log Query Open method: In the configuration file my.cnf or My.ini mysqld the following line to add two configuration parameters

Log-slow-queries=/data/myssqldata/show-query.log//Slow query log storage location

long_query_time=2//Set query time-out

Log-queries-not-using-indexes//record queries that do not use an index

Explain field Property Description

Table shows which table this row of data is about

Possible_keys displays the indexes that may be applied in this table. If it is empty, there is no possible index

The index that the key actually uses, or null if it is not used

Key_len use the length of the index, without loss of accuracy, the shorter the better

Ref display index which column is used, if possible, is a constant

Rows returns the number of lines for the requested data

Type shows which type of query is used, from best to worst connection type.

System Const: Both convert the queried variable to a constant

Eq_reg: Accesses the index, returning data for a single row, usually when a query is connected

REG: Accesses an index, returns data for a value, usually occurs when an equal sign is used

Range: This type uses the index to return rows in a range, such as using a query condition greater than or less than the one, and the field is indexed

Index: Full table scan in indexed order with the advantage of not sorting, disadvantage full table scan

All: Full table scan, try to avoid

Extra Parsing query additional information

Using index only uses indexes to avoid access to the table

Using Where to filter data

The using temporary uses a temporary table

The using Filesort uses an extra sort when the order by XXX is not used to the index.

Profiling can get more accurate information of SQL execution consuming system resources, profiling is off by default, open set profiling=1;

Index type

Normal index: This is the basic index type, no restrictions such as uniqueness

Uniqueness Index: The index column values used remain unique

Primary key: is a unique index that specifies promary key

Full-Text indexing: MySQL supports full-text indexing and full-text retrieval starting with version 3.23.23. Full-text index type Fulltext can be created on a varchar or text type

Single-column index and multicolumn Index (composite index)

A multi-column index has one feature, the leftmost prefix. If a multi-column index is key (x y z). MySQL uses multi-column indexes when the search condition is a combination and order of the following columns x, Y, Z

Recommendations for building an index

1, the smaller the data type is usually better, because the smaller the data type usually in the disk, memory and CPU cache need less space, processing faster

2, the simple data type is better, the integer data than the character, the processing cost is smaller, because the string comparison is more complex. In MySQL, the built-in date should be used

and time data types, instead of storing time with strings. and storing IP addresses with integer data types

3. Avoid null should specify column NOT NULL. In MySQL, columns that contain null values are difficult to query for optimization. Should use 0, special values, empty strings instead of null values

Some tips for setting up indexes and writing SQL

1. Use limit 1 When the result set has only one row of data

2. Avoid SELECT *

3. Use join join instead of subquery

4, use Enum\char instead of varchar, use reasonable field property length

5. Use NOT NULL whenever possible

6, fixed-length table will be faster

7. Splitting large DELETE or INSERT statements

8, the smaller the query column, the faster

where is used

Try to put in front the conditions that will extract as few data as possible, and reduce the query time of the latter where condition.

Index Invalid condition

1. Where clause query condition has! =

2. Where clause uses function SELECT * from TB where left (XXX, 4) = ' yyyy '

3, use like query%xxx%

MySQL Internal configuration optimization

1, the connection request variable max_connections. If the server has a large number of concurrent connection requests, it is recommended that you change it a bit. But do not blindly improve, MySQL will be for

Each connection provides a connection buffer, which will cost more memory.

Show variables like ' max_connections ' show status like ' Max_used_connections '

Max_used_connections/max_connections * 100% 85 for ideal value

2, Back_log: When the number of MySQL connections reached max_connections, the new request will be stored in the stack, waiting for a link to be freed of resources, the number of the stack

That is Back_log, if the number of waiting connections exceeds back_log, the connection resource will not be granted. The default value is 50 and can be set to 128.

3, Interactive_timeout: An interactive connection in the number of seconds that the server waits for action in the closing money. Default value 28800, can be set to 7200

4, Key_buffer_size: Global buffer size, determines the processing speed of the index.  You can check the status values Key_read_requests and Key_reads. Key_reads/

Key_read_requests should be as low as possible, at least 1:100.

5, query_cache_size query buffer, the query results are stored in the buffer. By checking the status value qcache_*, you know whether the Query_cache_size setting is reasonable.

If the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering, and if the value of Qcache_hits is also very large, the query buffer is used

Very frequently, this thing needs to increase the buffer size

6, Innodb_buffer_pool_size function equivalent to key_buffer_size for MyISAM table.

7, Innodb_flush_log_at_trx_commit: Control InnoDB writes the data in log buffer to the logfile and flush the disk point in time, the value is 0 1 2 respectively

0 means that when a transaction commits, it does not log write operations, but writes data from log buffer to the log file once per second sick flush disk

1 represents a per-second or per-transaction commit that causes log file writes, flush disk operations, and the acid of the transaction

2 represents the action to write to the log file each time the transaction commits, but the flush disk operation is completed once per second

According to the MySQL manual, you can set to 0 or 2 if you are allowed to lose the most recent part of the transaction

8, Innodb_log_buffer_size:log cache size, generally 1-8m, default is 1M, for larger transactions can increase the cache size, set to 4 or 8

Buffering for each connection

1) record_buffer_size

Each thread that makes a sequential scan allocates a buffer of that size for each table it scans. If you do a lot of sequential scans, you may want to increase the value.

The default value is 131072 (128K) and can be changed to 16773120 (16M)

2) Read_rnd_buffer_size

The random read buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.

Can generally be set to 16M

3) Sort_buffer_size

Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by operation.

The default value is 2097144 (2M) and can be changed to 16777208 (16M).

4) Join_buffer_size

The size of the buffer that can be used by the Federated query operation

Record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size is exclusive for each thread, that is, if there are 100 threads connected, it is occupied as 16m*100

5) Table_cache

The size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents. By checking the status values of peak time open_tables and Opened_tables, you can determine whether you need to increase the value of Table_cache. If you find that open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_cache (the above status values can use Show status like ' open% Tables ' obtained). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.

1G memory machine, the recommended value is 128-256. Server with memory around 4GB This parameter can be set to 256M or 384M.

6) Max_heap_table_size

The size of the memory table that the user can create. This value is used to calculate the maximum row value for the memory table. This variable supports dynamic change, that is, set @max_heap_table_size =#

This variable, together with Tmp_table_size, limits the size of the internal memory table. If an internal heap (stacked) table is larger than Tmp_table_size,mysql, you can automatically change the in-memory heap table to the hard disk-based MyISAM table as needed.

7) Tmp_table_size

Increase the size of a temporary table by setting the Tmp_table_size option, such as a temporary table generated by the advanced group by operation. If this value is raised, MySQL will also increase the size of the heap table to improve the speed of the join query, it is recommended to optimize the query as far as possible, to ensure that the temporary table generated during the query in memory, to avoid the temporary table is too large to generate a hard disk-based MyISAM table.

8) Thread_cache_size

The number of threads that can be reused for saving in. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the customer's line is placed in the cache. If there are many new threads, the value of this variable can be increased in order to improve performance.

By comparing the variables of the connections and threads_created states, you can see the effect of this variable.

The default value is 110, which is adjustable to 80.

9) Thread_concurrency

The recommended setting is twice times the number of server CPU cores, such as a dual-core CPU, then the thread_concurrency should be 4, and 2 dual-core CPUs, thread_concurrency should be a value of 8. Default is 8

) wait_timeout

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

One of the MySQL optimization series