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

This article is about how to check the performance of MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

How do I view MySQL performance?

Now there are usually two ways to monitor MySQL: one is to connect to the mysql database and use show status,show variables,flush status to view various performance indicators of mysql; the other is to directly use mysqladmin to view its performance indicators.

The use of method 1:

View MySQL server configuration information: mysql > show variables

View the various status values that the MySQL server is running: mysql > show global status

Slow query: mysql > show variables like'% s%'

Mysql > show global status like'% slow%'

Number of connections: mysql > show variables like 'max_connections'

Key_buffer_size is the parameter mysql > show variables like 'key_buffer_size' that has the greatest impact on the performance of the MyISAM table.

Temporary table: mysql > show global status like 'created_tmp%'

Check out open table: mysql > show global status like 'open%tables%'

Process usage: mysql > show global status like 'Thread%'

Query cache (query cache): mysql > show global status like 'qcache%'

Number of file openings (open_files): mysql > show global status like 'open_files'

Table lock: mysql > show global status like 'table_locks%'

Table scan: mysql > show global status like 'handler_read%'

The use of method 2:

UserParameter=mysql.uptime,mysqladmin-uroot status | cut-f2-d ":" | cut-F1-d "T"

Mysqladmin two parameters, status,extended-status

Shell > mysqladmin-uroot-ppassword variables status

The information contained is as follows:

Uptime: number of seconds that 4557887 # mysql runs

Threads: 1 # connections

Questions: 1684130 # The number of questions (queries) from clients since the server was started.

Slow queries: 0 # The number of queries that have taken more than long_query_time seconds

Opens: 221872 # The number of tables the server has opened.

Flush tables: 1 # The number of flush-*, refresh, and reload commands the server has executed.

Open tables: 64 # The number of tables that currently are open.

Queries per second avg: 0.369 # average number of queries per second since the last run

Key MySQL statistical indicators

What should I do when the database is slow or unable to respond to the query? We can find out the cause of this problem by monitoring indicators related to performance and resource utilization, so as to avoid the impact of related components that rely on database performance.

There are hundreds of performance metrics available to MySQL users. This paper introduces four key commonly used metrics, query throughput, query execution performance, connection and buffer pool usage.

Query throughput: there is a counter called Questions inside MySQL. Every time the client sends a query statement, the value will be increased by one, which can be used to measure the query throughput. SHOW GLOBAL STATUS LIKE "Questions"

Query execution performance: there are many ways to monitor query latency, such as through MySQL built-in metrics, or through query performance patterns. MySQL 5.6.6 is enabled by default, and tables in MySQL's performance_schema database store low-level statistics on server events and query execution.

Connection status: MySQL's documentation indicates that a robust server should be able to handle hundreds of connections, which can be viewed in the form of SHOW VARIABLES LIKE 'max_connections';.

Buffer pool usage: MySQL's default storage engine, InnoDB, uses a memory area called a buffer pool to cache data for data tables and indexes. If database performance starts to decline and disk Icano continues to climb, expanding the buffer pool can often lead to a rebound in performance.

Thank you for reading! This is the end of the article on "how to check the performance of MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.