Shulou(Shulou.com)05/31 Report--

MySQL server performance is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Server performance analysis 1. Brief introduction to performance Optimization:

For performance questions, there were 1000 DBA and 1000 answers. Such as "QPS", "CPU Load", "extensibility" and so on.

Principle 1: we define performance as the measure of time required to complete a task. That is, "performance is response time".

Performance is measured by tasks and time, not resources. The purpose of the database is to execute SQL statements, and the "task" in the previous sentence is a query or a SQL statement (SELECT UPDATE DELETE, etc.). In summary, the performance of the database server is measured by the response time of the query, and the unit is the time spent on each query. Here we assume that performance optimization is to reduce the response time as much as possible under a certain workload.

CPU usage is just a phenomenon, not a good measurable goal.

The improvement in throughput can be seen as a by-product of performance optimization, which allows the server to execute more queries per second. Because the query execution time per second is shorter. (throughput is the reciprocal of performance: number of queries per unit time, QPS,QPM, etc.)

Principle 2: if it cannot be measured, it cannot be effectively optimized. The first step should be to measure where time is spent.

The time required to complete a task can be divided into two parts: execution time and waiting time.

If you want to optimize the execution time of a task, the best way is to measure the time it takes to locate different subtasks, and then optimize to remove some subtasks, reduce the execution frequency of subtasks or improve the execution efficiency of subtasks.

If you want to optimize the wait time of a task, it is more complicated, and the wait may be caused by the indirect influence of other systems.

1.1. Optimize through performance analysis.

Performance profiling (Profiling) is divided into two steps: first measure the time spent on the task, then count and sort the results, and put the important tasks first.

The performance profile report lists all tasks. Record one task per line, including the task name, the execution time of the task, the elapsed time of the task, the average time of the task, and the percentage of the total time that the task was executed.

The analysis based on execution time studies which task has the longest execution time.

The wait-based analysis is to determine at which stage the task is blocked for the longest time.

In fact, when an analysis based on execution time finds that a task takes too much time, it should be analyzed in depth and it may be found that some "execution time" is actually waiting.

1.2. Understanding performance analysis

Although performance profiling outputs rankings, totals, and averages, there is still a lot of important information that is missing.

Queries worth optimizing: some queries that account for only a small proportion of the total response time are not worth optimizing. If you spend 1000 dollars to optimize a task and there is no increase in revenue from a single business, it can be said to be wasted. If the cost of optimization is greater than the benefit, optimization should be stopped.

Exception: some tasks need to be optimized even if they do not appear in front of the profiling output. For example, some tasks are performed very few times, and each time they are executed very slowly, seriously affecting the user experience.

Hidden details: performance profiling cannot show all response times, only believing that averages are very dangerous. It is as meaningless as the average body temperature of all patients in the hospital.

two。 Analyze the performance of the application system

In fact, profiling an application is generally easier and more profitable than profiling a database server. It is recommended to conduct a top-down performance analysis of the system, so that we can track the whole process from user initiation to server response. although performance problems are related to databases in most cases, there are also a lot of problems caused by applications.

# you should measure everything that can be measured as much as possible and accept the additional cost of these measurements. Tom Kyte, a performance optimization master of # Oracle, was asked about the measurement point overhead in Oracle. His answer is that measurement points are at least 10% shared for performance optimization # most applications do not need to run detailed performance measurements every day, so they actually contribute at least 10% 3. Parsing MySQL query 3.1 dissecting server load

Each new version of MySQL adds more measurable points. But if we just need to analyze and find expensive queries, the slow query log should be able to meet our needs. All queries can be captured by setting "long_query_time" to 0, and the response time of the query is already microsecond. In the current version, slow log is the least expensive and most accurate tool for measuring query time. If slow log is enabled for a long time, it should be used in conjunction with the logrotate tool (

Use the logrotate tool to cut MySQL logs and send slow log analysis to mailboxes

). The MySQL of the Percona branch records more and more valuable information than the official community version. Such as query plan, lock, Ithumb O activity, etc. In general, slow logging is a lightweight and comprehensive performance profiling tool.

You can use pt-query-digest to analyze the slow query log, as shown below:

Pt-query-digest slow.log > slow_log_analyze.log / data/mysql/3109/slow.log: 53% 00:25 remain / data/mysql/3109/slow.log: 98% 00:00 remaincat slow_log_analyze.log# 75.3s user time, 2s system time, 41.28M rss, 235.76M vsz# Current date: Sun Feb 25 15:43:11 2018 Hostname: MySQL-Cent7-IP001109# Files: / data/mysql/3109/slow.log# Overall: 445.27k total, 59 unique 0.03 QPS 0.04x concurrency _ # Time range: 2017-09-28T16:00:25 to 2018-02-25T07:27:18# Attribute total min max avg 95% stddev median# = # Exec time 461284s 100ms 150s 1s 3s 1s 740ms# Lock time 1154s 010s 3ms 57us 83ms 21us# Rows sent 426.70M 0 9.54M 1004.84 97.36 76.26k 0.9 "Rows examine 465.04M 0 9.54M 1.07k 299.03 76.26k 0.9" Query size 4.55G 6 1022.79k 10.71k 76.28 73.23k 36.6 "Profile# Rank Query ID Response time Calls R/Call V Item# # 1 0x558CAEF5F387E929 238431.3966 51.7% 294383 0.8099 0.62 SELECT sbtest?# 2 0x84D1DEE77FA8D4C3 53638.8398 11.6% 33446 1.6037 1.14 SELECT sbtest?# 3 0x3821AE1F716D5205 53362.1845 11.6% 33504 1.5927 1.11 SELECT sbtest?# 4 0x737F39F04B198EF6 53244.4816 11.5% 33378 1.5952 1.14 SELECT sbtest?# 5 0x6EEB1BFDCCF4EBCD 53036.2877 11.5% 33539 1.5813 1.10 SELECT sbtest?# 6 0x67A347A2812914DF 2619.2344 0.6% 200 13.0962 67.98 SELECT tpcc?.order_line# 7 0x28FC5B5D583E2DA6 2377.9580 0.5% 215 11.0603 11.53 SHOW GLOBAL STATUS# 10 0xE730A9F41A4AB139 259.9002 0.1% 355 0.7321 0.42 SHOW INNODB STATUS# 11 0x88901A51719CB50B 131.1035 0.0% 39 3.3616 21.74 SELECT information_schema.tables# 12 0x16F46891A99F2C89 127.1865 0.0% 88 1.4453 1.15 SELECT performance_schema.events_statements_history# 14 0x153F1CE7D660AE82 79.2867 0.0% 46 1.7236 1.47 SELECT information_schema.processlist# MISC 0xMISC 3976.0946 0.9% 16077 0.2473 0.0 # Query 1: 0.17 QPS 0.14x concurrency, ID 0x558CAEF5F387E929 at byte 487747785mm This item is included in the report because it matches-- limit.# Scores: v Time range M = 0.6 Time range: 2018-02-03T11:26:24 to 2018-02-23T13:03:23# Attribute pct total min max avg 95% stddev median:

In addition to slow logging, the pt-query-digest tool in the Percona Toolkit toolkit can also be parsed, using the-processlist parameter to constantly analyze the output of "show processlist". But the output of "show processlist" is rapidly changing. Even collecting once a second still leaves out a lot of useful information, so this approach is not highly recommended. Another way is to use the-- type=tcpdump option to analyze the network packet capture data.

3.2 parsing a single query using SHOW PROFILE

Disabled by default, but it is a session-level parameter. Set profiling=1, which then sends all statements directly to the server, measures the time it takes and other data related to the change in the execution status of the query.

When a query is submitted to the server, the tool records the profiling information to a temporary table and assigns an integer identifier starting at 1 to the query.

Such as:

Set profiling=1select * from t_Order Select * from t_Productshow profiles+-+ | Query_ID | Duration | Query | +- -+ | 1 | 9.75e-05 | SHOW WARNINGS | | 2 | 0.00052075 | select * from t_order | | 3 | 0.000511 | select * from t_product | | 4 | 5.3e-05 | SHOW WARNINGS | +- -+ show profile for query 3-Status | Duration | +-- +-- + | starting | 0.000065 | checking permissions | 0.000009 | Opening tables | 0.000142 | | init | 0.000022 | System lock | 0.000010 | | optimizing | 0.000008 | | statistics | 0.000013 | preparing | 0.000012 | executing | 0.000007 | Sending data | 0.000154 | end | 0.000010 | query end | 0.000011 | closing tables | 0.000010 | | freeing items | | | 0.000016 | | cleaning up | 0.000012 | +-+-+ |

The analysis report gives each step of the query execution and the time it takes, and it is difficult to quickly determine which step takes the most time by looking at the results. The output is sorted in the order in which it is executed, not by the time spent. Here is how to query the profiling report using INFORMATION_SHCEMA:

Set @ query_id=1SELECT STATE,SUM (DURATION) AS Total_R, ROUND (100*SUM (DURATION) / (SELECT SUM (DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @ query_id), 2) AS Pct_R,COUNT (*) AS Calls The output of SUM (DURATION) / COUNT (*) AS "R/Call" FROM INFORMATION_SCHEMA.PROFILINGWHERE QUERY_ID=@query_idGROUP BY STATEORDER BY Total_R DESC# is as follows: +-- + | STATE | Total_R | | Pct_R | Calls | R/Call | +-- + | starting | 0.000072 | 20.45 | 1 | 0.0000720000 | | Sending data | 0.000047 | 13.35 | 1 | | | 0.0000470000 | | init | 0.000030 | 0.000030 | 1 | 0.0000300000 | | Opening tables | 0.000026 | 7.39 | 1 | 0.0000260000 | | checking permissions | 0.000025 | 0.000025 | 1 | 0.0000250000 | | cleaning up | 0.000023 | 0.000023 | 1 | 0.0000230000 | | System lock | 0.000019 | 5.40 | 1 | 0.0000190000 | | statistics | | | 0.000018 | 0.000018 | 1 | 0.0000180000 | | preparing | 0.000016 | 4.55 | 1 | 0.0000160000 | optimizing | 0.000015 | 4.26 | 1 | 0.0000150000 | | freeing items | 0.000014 | 3.98 | 1 | 0.0000140000 | | query end | 0.000013 | 3.69 | 1 | 0.0000130000 | | closing tables | | | 0.000012 | 0.000012 | 1 | 0.0000120000 | | executing | 0.000011 | 3.13 | 1 | 0.0000110000 | | end | 0.000011 | 3.13 | 1 | 0.0000110000 | +-- -+ # from this result, you can easily see that the long query time is mainly due to spending a lot of time on sending data. # this status represents a lot of reasons. It could be a variety of different server activities, including searching for matching row records at association time, and it's hard to say how much time can be saved by optimizing. # if Sorting result takes a lot of time, you can consider increasing sort buffer size to use show status

MySQL's show status command returns counters, both at the server-level global level and at the session level based on a connection. The MySQL official manual explains in detail whether all variables are global or session-level.

Most of the results of show status are counters that show the frequency of certain activities, such as reading the index, but cannot tell how much time has been spent. In the results of show status, only one Innodb_row_lock_time refers to operation time, and this is global, or session-level work cannot be measured. The most useful counters include handle counters, temporary files, and table counters. Reset the session-level counter to 0, then query the previously mentioned view, and then check the results of the counter:

Flush status;select * from sakila.nicer_but_slower_film_list;#.show status where variable_name like "Handler%" or Variable_name like "Created%" +-- +-+ | Variable_name | Value | +-+-+ | Created_tmp_disk_tables | 2 | | Created_tmp_files | 2 | Created_tmp_tables | | 3 | | Handler_commit | 1 | Handler_delete | 0 | Handler_discover | 0 | Handler_external_lock | 10 | Handler_mrr_init | 0 | Handler_prepare | 0 | Handler_read_first | 3 | Handler_read_key | 12942 | | Handler_read_last | | | 0 | | Handler_read_next | 6462 | | Handler_read_prev | 0 | | Handler_read_rnd | 5462 | Handler_read_rnd_next | 6478 | Handler_rollback | 0 | Handler_savepoint | 0 | Handler_savepoint_rollback | 0 | Handler_update | 0 | Handler_write | 0 | | | +-+ + |

You can see from the results that the query uses three temporary tables, two of which are disk temporary tables, and there are many read operations (Handler_read_rnd_next) that do not use the index. Assuming that we do not know the specific definition of this view, we can only infer from the results that this query may have made a multi-table association query without a suitable index, or that one of the subqueries may have created a temporary table and then made a federated query with other tables, while the temporary table used to preserve the results of the subquery has no index.

Note, however, that using show status itself creates a temporary table, and it is accessed through handle operations, which also affects the corresponding numbers in the show status results, and different versions may behave differently. Comparing the execution plan results of the previous queries obtained through show profiles, the counters of the temporary tables are increased by at least 2.

The query execution plan seen through explain can also get most of the same information as show status, but the result is estimated through explain and the actual measurement through show status. For example, explain cannot tell you whether a temporary table is a disk table.

Use performance_schema and sys view libraries

In 5.6. a mature performance_schema view library is introduced, and in 5.7, in order to facilitate the use of performance_schema, a sys library based on performance_schema is introduced. Through the sys library, we can easily observe a lot of basic data, at the same time, we can use MySQL WorkBench to easily view. As shown in the figure:

4. Diagnosis of intermittent problems

If the overall operation of the server is fine, and only a query occasionally slows down, you need to focus on this particular query.

Server problems can often be solved by upgrading hardware or upgrading to a new version of MySQL.

Use show global status

At a certain frequency, such as executing the show global status command once a second to capture data, when a problem occurs, it can be found through the "spikes" or "depressions" of some counters (such as Threads_running, Threads_connected, Questions: excluding the number of queries in the stored procedure, and Queries: all queries).

Mysqladmin ext-i1 | awk'/ Queries/ {q=$4-qp Qp=$4} / Threads_connected/ {tc=$4} / Threads_running/ {printf "% 5d% 5d% 5d\ n", Q, tc, $4}'# output is as follows: 798 136 7767 134 9 828 134 7 683 134 7 784 135 7 134 7 108 134 24 187 179 31 1179 134 7 1151 1120 13513 this command captures show global status data once a second Output to awk to calculate and output the number of queries per second, connection threads, active threads. # the trends of these three data are highly sensitive to occasional pauses at the database level.

In this example, the application uses connection pooling, so Threads_connected does not have a spike, but the number of threads executing queries increases significantly, while the number of threads executing queries increases significantly, and the number of queries per second is significantly lower than normal data.

How to analyze this phenomenon, only by guessing there are certain risks, but in practice, there are two reasons that are more likely. One of them is that there is some kind of bottleneck inside the server, which causes the new query to pile up because it needs to acquire the lock that the old query is waiting for before it starts execution. Generally speaking, this kind of lock will also cause back-end pressure on the application server, resulting in queuing problems on the application server. Another common reason is that the server suddenly encounters the impact of a large number of query requests, such as the query storm caused by the breakdown of the front-end redis or redis.

Use show processlist

Constantly capture the output of show processlist to see if a large number of threads are in an abnormal state or have other abnormal characteristics.

Mysql-e 'show processlist\ G' | grep State: | sort | uniq-c | sort-rn 744 State: 67 State: Sending data 35 State:freeing items 8 State:NULL 6 State:end 4 State:Updating 4 State:cleaning up 2 State:update 1 State:sorting result 1 State:logging slow query

In most cases, the State column is very useful. As you can see from the output of this example, there are many threads in the end of query execution, including "freeing items", "end", "cleaning up", and "logging slow query".

Attached are the common states that need to be paid attention to summarized by teacher Ye Jinrong:

Copy to tmp table: it is recommended to run the alter table statement in the trough period.

Copying to tmp table: a temporary table copied to memory, which is common in group by operations. Indexing is recommended.

Copying to tmp table disk: the temporary result set is too large to fit in memory. You need to copy the temporary table with inner size to disk. It is recommended to increase sort_buffer_size and tmp_table_size.

Creating sort index: the current SELECT requires temporary tables for Order by. Recommendation: create an appropriate index

Creating tmp table: it is recommended that you create appropriate indexes and use less UNION,VIEW,SUBQUERY

Reading from net: it is recommended to reduce the size of packets sent by the client and improve the quality of network bandwidth

Sending data: read data from the hard disk and send it to the client. It is recommended to reduce the amount of data that needs to be scanned and sent to the client by indexing or adding Limit.

Sorting Result: the scan results are being sorted, similar to Creating sort index, but in normal tables, not in memory tables, it is recommended to add indexes.

Statistics: carries on the data statistics, in order to parse the execution plan, often occurs, indicating that there is something wrong with Icano.

Use lsof

The use of disk temporary tables is judged by lsof sampling mysqld's use of tmp folders.

Lsof > lsof.txtawk'/ mysqld.*tmp/ {total + = $7;} total {printf "% s% 7.2f MB\ n", $4, total/1024/1024; total=0} 'lsof.txt11uW 12.00 MBmysql 0.00 MB

When inspecting a system, you should first ask yourself whether you have encountered such unreasonable problems as they are now. If so, you need to conduct an in-depth investigation, and you should try your best to follow up every possible question until you find the result. Instead of being distracted by so many questions that you stray from the topic, you finally forget the question to be investigated at the beginning. You can write down all the questions on paper, check one and cross out one, and finally confirm that all the questions have been investigated.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.