Shulou(Shulou.com)06/01 Report--

The following brings you about how to optimize the performance of mysql, if you are interested, let's take a look at this article. I believe it will be of some help to you after reading how to optimize the performance of mysql.

Slow query Analysis of MYSQL performance Optimization

1) location of performance bottleneck

Show command slow query log explain analysis query profiling analysis query

2) Index and query optimization 3) configuration optimization

MySQL database is the two common bottlenecks of CPU and Imax O. When CPU is saturated, it usually occurs when the data is loaded into memory or read from disk.

You can use mpstat, iostat, sar, and vmstat to view the performance status of the system.

Iostat

Optimize the nature of the database

Yes, there are usually three: using indexes, analyzing queries using EXPLAIN, and adjusting the internal configuration of MySQL.

1. Analysis of query and index optimization

When optimizing MySQL, it is usually necessary to analyze the database EXPLAIN analysis query, profiling analysis and show command query system status and system variables, by locating the bottleneck of analysis performance in order to better optimize the performance of the database system.

View MySQL CVM configuration information mysql > show variables

Check the various status values of MySQL CVM running mysql > show global status

# mysqladmin variables-u username-ppassword-- displays system variables

# mysqladmin extended-status-u username-ppassword-- display status information

For more complete show commands, please see: mysql > help show

Or http://dev.mysql.com/doc/refman/5.7/en/show.html

Slow log is enabled:

Add three configuration parameters under the [mysqld] line in the configuration file my.cnf, and restart the mysql service

Slow_query_log = 1 / / 0 off and 1 on

Slow_query_log_file = / usr/local/mysql/data/slow-query.log / / slow query log storage location

Long_query_time = 1 / / indicates that the query takes more than 1 second to record.

Add the log-queries-not-using-indexes parameter to my.cnf to indicate that queries that do not use indexes are recorded to the slow query log.

Second, view the setting information of slow query

Mysql > show variables like'% slow_query_log%'

Mysql > show variables like'% long_query_time%'

Transform the sql query statement

Mysql > select * from test1.tb1 where entertime

'2016-9-3'

Explain Analysis query

Use the EXPLAIN keyword to simulate the optimizer's execution of SQL queries to know how MySQL handles you

Of the SQL statement.

From the point of view that the explain simulation optimizer executes sql statements, it does not use index queries, but full table scans.

Optimization method: create an index on the stuname column

Mysql > create index index_stuname on test1.tb1 (stuname)

Execute explain again

Mysql > explain select * from test1.tb1 where stuname='admin'\ G

Profiling Analysis query

Through the slow log query, we can know which SQL statements are inefficient, through explain we can know the specific implementation of SQL statements, index use and so on, but also can be combined with the show command to check the execution status.

Select @ @ profiling; / / 0 means it is not open

Execute the sql statement to be tested

Mysql > select * from test1.tb1 where stuname='admin' and entertime='2016-9-1'

Mysql > show profiles\ G; / / you can get the time and ID of the executed SQL statement

Status: is the state in profile, and duration: is the time spent in the status state.

Mysql configuration optimization

Mysql parameter optimization is related to different websites, and their online traffic, visits, posts, network conditions, and machine hardware configuration. Optimization can not be completed at one time, and it takes constant observation and debugging to get the best results.

1) variable of connection request:

1. Max_connections

ERROR 1040: Too many connections errors often occur when the value is too small. You can pass mysql > show status like.

The 'connections'; wildcard looks at the number of connections in the current state (the number of connections trying to connect to the MySQL, regardless of whether the connection is successful or not) to determine the size of the value.

Maximum number of connections

Max_used_connections / max_connections * 100% (ideal value ≈ 85%)

If max_used_connections is the same as max_connections, then the setting of max_connections is too low or exceeds the limit of CVM load, and if it is less than 10%, the setting is too large.

2.back_log

The number of connections that MySQL can hold temporarily. It works when the main MySQL thread gets a lot of connection requests in a very short period of time.

How to set up back_log?

Modify the / etc/my.cnf file and add the following under [mysqld], such as setting the maximum number of connections to 1024back_log = numeric restart

3.wait_timeout and interactive_timeout

Wait_timeout-- the number of seconds MySQL waits before closing a non-interactive connection-- refers to the number of seconds that mysql waits before closing an interactive connection.

Wait_timeout:

(1) if the size is set, the connection closes quickly so that some persistent connections do not work.

(2) if the setting is too large, it is easy to cause the connection to be opened for too long. In show processlist, you can see too many sleep.

State of the connection, resulting in a too many connections error

3) wait_timeout is generally expected to be as low as possible

The setting of interactive_timeout will not have much impact on your web application

View wait_timeout and interactive_timeout

Mysql > show variables like'% wait_tmeout%'

Mysql > show variables like'% interactive_timeout%'

How do I set up wait_timeout and interactive_timeout?

Modify the / etc/my.cnf file and add the following under [mysqld]

Wait_timeout=100

Interactive_timeout=100

After restarting MySQL Server, check that the settings have taken effect.

Global buffering:

4.key_buffer_size

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values Key_read_requests and Key_reads, you can see whether the key_buffer_size setting is reasonable.

There are a total of 6 index read requests, and 3 requests cannot find the index read directly from the hard disk in memory. Calculate the probability that the index misses the cache:

Key_buffer_size only works on MyISAM tables. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is the MyISAM table. You can use the check status value created_tmp_disk_tables

Adjust key_buffer_size

The default configuration value is 8388608 (8m), and the host has 4GB memory. You can tune the value to 268435456 (256MB) to modify the / etc/my.cnf file. Add the following under [mysqld]

5. Query_cache_size (query cache referred to as QC)

Using query buffering, MySQL stores the query results in a buffer, and in the future, for the same SELECT statement (case sensitive), the results will be read directly from the buffer.

If the value is large, there is more memory fragmentation in Query Cache, and FLUSH QUERY CACHE will defragment the cache.

Qcache_queries_in_cache: number of Query of cache in current Query Cache; Qcache_total_blocks: number of block in current Query Cache; Query the configuration of query_cache on CVM:

Query_cache_limit: queries larger than this size will not be cached

Query_cache_min_res_unit: the minimum size of the cache block. The configuration of query_cache_min_res_unit is a "double-edged sword". The default is 4KB. Setting a large value is good for big data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.

Execute the mysqlslap tool for testing before optimization

Execute the mysqlslap tool for testing after optimization

Read the above details about how to optimize the performance of mysql, and whether you have gained anything. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.