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

Editor to share with you the performance analysis of the MySQL database server, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

3.1 introduction

Performance: the measure of time required to complete a task, in other words performance is response time

Throughput: query data per unit time (reciprocal of performance definition)

Step one: find out where time is going and where time is spent

If the pass measurement does not find the answer, the measurement method is wrong or not perfect, only measure the activities that need to be optimized.

Do not start or stop the test at the wrong time, measuring the aggregated information rather than the target activity itself; subtasks need to be located and optimized

Principle: cannot be effectively optimized if it cannot be measured

3.1.1 optimize through performance analysis

Performance analysis: the main method of measuring and analyzing where time is spent

1. Time spent on measuring tasks; 2. Statistics and ranking of results (important front row)

Similar tasks can be grouped together and the desired results can be obtained through a performance profile report, which lists all tasks with one task per line:

Task name, execution time, elapsed time, average execution time, percentage of total execution time; sorted by task elapsed time in descending order

Performance profiling type:

Analysis based on execution time: which task has the longest execution time

Analysis based on waiting: determine where the task is. The blocking time is the longest.

3.1.2 understanding performance analysis

Missing but important information in performance profiling:

1. Query worthy of optimization

The query which accounts for a small proportion of the total response time is not worth optimizing; the cost is greater than the benefit and stops optimization.

2. Abnormal situation

Optimize tasks that are not explicitly optimized, such as tasks that are performed less often but are very slow each time

3. Unknown unknown

Lost time: the difference between the total time of the task and the actual measured time, even if it is not found, pay attention to the possibility of such problems.

4. Hidden details

Unable to display the distribution of all response time, more information, histogram, percentage, standard deviation, deviation index

5. Interactive analysis cannot be carried out in a higher-level stack.

3.2 performance profiling of applications: top-down

Factors affecting performance bottlenecks:

1. External resources, calling external web services or search engines

2. The application needs to process a large amount of data and analyze a very large xml file.

3. Perform expensive operations in the loop: abuse of rules

4. Use inefficient algorithm: brute force search algorithm

Suggestion: code that includes performance profiling should be considered in new projects

3.2.1 measure PHP application: null 3.3 parse MySQL query 3.3.1 parse server load

Log files are queried by MySQL:

1. Slow log: low overhead, high precision and large disk space. Pay attention to the deployment of log rotation tool for a long time, which can only be enabled during the collection of load samples, with a microsecond level of 5.1.

2. General log, which is recorded when the query is requested to the server, excluding response time and execution plan

Analyze query log

From top to bottom, Mr. Cheng analyzed the report (pt-query-digest) to see the sections of special concern.

3.3.2 analyze a single query

Think about why it takes so long and how to optimize

After using SHOW PROFILE:MySQL5.1

View: show variables like "% pro%"; [source]

Disable by default, turn on: set profiling=1; and then execute the statement on the server (turn off set profiling=off;)

Syntax:

SHOW PROFILE [type [, type]...] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL-displays all the cost information | BLOCK IO-displays the IO-related cost of the block | CONTEXT SWITCHES-- context switching-related cost | CPU-displays the CPU-related cost information | IPC-shows Show send and receive related cost information | MEMORY-display memory related cost information | PAGE FAULTS-display page error related cost information | SOURCE-display and Source_function Source_file,Source_line-related cost information | SWAPS-- the information showing the cost related to the number of exchanges is essentially that the cost information is recorded in the information_schema.profiling table show profiles View show profile for query 2; get the cost of the specified query (the second query cost breakdown) show profile cpu for query 2; view the cost of a specific part, such as the cost show profile block io,cpu for query 2 of the CPU section; and view different resource costs using SHOW STATUS: counter

Global show global status, based on a certain connection session level, scope should be noted

Counter shows the frequency of activity, commonly used: handle counter, temporary file, table counter

Temporary tables are created and manipulated through handles (references, pointers? ) access to this temporary table, affecting the corresponding numbers in the show status result

Use slow query log: [source] [source]

Record the statements in MySQL whose response time exceeds the threshold long_query_time to the slow query log (the log can be written to a file or database table, and it is recommended to write a file if high performance is required). The default is 10s, which needs to be opened manually.

View:

(1) the value of slow_query_log is ON to enable slow log, and OFF to disable slow log.

(2) the value of slow_query_log_file is the recorded slow log to the file (Note: the default name is hostname.log. If the slow log is written in the specified file, you need to specify that the output log format of slow query is file, and the related command is: show variables like'% log_output%'; to check the output format).

(3) long_query_time specifies a slow query threshold, that is, if the execution time of a statement exceeds this threshold, it is a slow query statement, and the default value is 10 seconds.

(4) if the value of log_queries_not_using_indexes is set to ON, all queries that do not make use of the index will be recorded (Note: if only log_queries_not_using_indexes is set to ON and slow_query_log is set to OFF, this setting will not take effect, that is, the setting takes effect if the value of slow_query_log is set to ON). Generally, it will be temporarily enabled when performance tuning. When enabled, sql that uses full index scan will also be recorded in the slow query log.

/ / the above command is only valid for the current time. When MySQL restart fails, if you want to take effect permanently, you need to configure my.cnf to view the output format: file? Table show variables like'% log_output%'; enables generic log query: set globalgeneral_log= on; closes generic log query: set globalgeneral_log=off; sets generic log output to table mode: set globallog_output='TABLE'; sets generic log output to file mode: set globallog_output='FILE'; sets general log output to table and file mode: set globallog_output='FILE, number of TABLE'; query slow query statements: show global status like'% slow%' Brief introduction to the log section:

Information such as which statement causes a slow query (sql_text), the query time of the slow query statement (query_time), the table locking time (Lock_time), and the number of rows scanned (rows_examined).

Use the built-in slow query log analysis tool: mysqldumpslow

Perl mysqldumpslow-s c-t 10 slow-query.log

-s indicates how to sort, c, t, l, r are sorted by the number of records, time, query time, and the number of records returned. Ac, at, al, and ar indicate the corresponding flashback;-t means top, followed by data indicating how many entries are returned; and-g can be followed by regular expression matching, which is case-insensitive.

Use Performance Schema: [source] [source]

Monitor MySQL server, collect performance parameters, and table storage engine PERFORMANCE_SCHEMA, low energy consumption

Local server, the table is a memory table, the contents of the table are repopulated when the server is started, discarded when closed, and changes are not copied or written to the binary log

Properties:

The performance scenario configuration can be dynamically modified by SQL, which immediately affects data collection

Monitoring service events: events are anything that the service does and is perceived, and time information can be collected

Database performance solution, which provides a way to internally check the runtime database service, focusing on performance data

Specific to a database service, database tables are associated with the data service, and modifications are not backed up or written to the binary log

The storage engine collects event data with "perception points" and stores it in the performance_schema database, which can be queried through select statements.

Supplement: the initial installation of the database has three basic libraries

Mysql

Includes permission configuration, event, storage engine status, master-slave information, log, time zone information, user rights configuration, etc.

Information_schema

Abstract analysis of database metadata, which provides a SQL statement to query the running state of the database. Each query to information_schema produces mutually exclusive access to metadata, which affects the access performance of other databases.

Performance_schema

Memory database, using performance_schema storage engine, collects and stores the runtime state of mysql service in performace_schema database through event mechanism. Note that when two words are connected by an underscore, performance_schema is a database; when separated by spaces, it represents a database performance scenario, as well as a storage engine.

These are all the contents of the article "performance Analysis of MySQL Database Server". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.