Feature Overview
Performance test is a comprehensive analysis service for database instance performance and health. It can analyze SQL statement performance, CPU utilization, IOPS utilization, memory utilization, disk utilization, connections, locks, hotspot tables, and transactions, helping you identify and address existing and potential health issues in your database through smart diagnosis and optimization.
Note
For certain test items, the performance test report provides a series of optimization suggestions. Please carefully test the suggested measures before applying them so as to prevent the instance performance problems from getting worse.
Feature Overview
Health Rating: You can view the current comprehensive database performance score, with a maximum of 100 points. If the score remains below 60 for an extended period, consider optimizing your business or database configuration.
Report Generation, Viewing, and Saving: You can create reports at will and view the most recent one generated. Reports can be saved as webpages and downloaded for local review.
Performance test mainly includes the following features:
Resource Analysis
Analyzes the usage of database instance resources (CPU, disk, and connections) in a certain period of time and displays an overall score.
Note
As most instances have the idle overuse policy enabled by default, you may observe that the maximum CPU utilization can exceed 100%. If your CPU usage consistently goes beyond 100% and the average value is higher than the recommended level, it is advisable to expand your resources as soon as possible.
System Status
Sorts out key instance metrics, lists their status and time of occurrence, and suggests corresponding modifications.
Tablespace distribution
Lists the current top 10 tables in reverse order in terms of data space to help you identify oversized tables.
Redundant index detection
Lists the current possible redundant indexes (whose selectivity is below 1%) and suggests optimizations.
Note
Because a query statement must first query the indices before querying tables through indices, if there are too many identical data entries in the index column, the performance to reduce the amount of data to be filtered may be compromised and is not as fast as full table scan.
Deadlock Diagnosis
Deadlock diagnosis retrieves the last deadlock information from the database by diagnosing
show engine innodb status. If the deadlock occurs within the user-selected diagnostic time period, it will be displayed.Note
If deadlocks occur too frequently, it indicates that the SQL statements within transactions are prone to creating cycles when executed concurrently. The fundamental solution is to modify the SQL execution logic order, optimize the locking mechanism, and reduce the probability of deadlocks. A temporary solution is to kill the leading blocking session.
Lock wait diagnosis
Reports lock waits lasting over 60 seconds in the current time period.
Note
Lock waits are normal occurrences, but sometimes businesses may encounter errors like
Lock wait timeout exceeded; try restarting transaction. In MySQL, InnoDB lock information is stored in the information_schema system library, under the three tables innodb_trx, innodb_lock_waits, and innodb_locks. Lock wait diagnosis involves analyzing the lock dependency relationships among these three tables to identify the leading transaction and session information of sessions that hold locks for longer than a certain threshold and block other sessions, as well as the session information of the blocked transactions. The leading session is then terminated using the KILL command.Note
Currently, lock waits are supported only by InnoDB.
Long running session diagnosis
Diagnose sessions with a Command other than Sleep and an execution time (Time) exceeding 10 seconds in the
information_schema.processlist of the instance.Note
The best way to resolve long sessions is to optimize SQL and proactively implement session expiration configurations in your business code. Alternatively, you can adjust the
interactive_timeout and wait_timeout parameters to actively expire outdated sessions.Slow query analysis
Lists the current top 20 slow query statements based on the number of executions in reverse order.
Note
The slow query threshold can be adjusted through the
long_query_time configuration. There are various reasons for slow queries. Generally, if your instance has reasonable resource consumption and numerous slow queries, it is recommended to focus on the rationality of business SQL and indexes. If your instance has high performance consumption and numerous slow queries, it is suggested to pay attention to the appropriateness of instance configuration and optimize business SQL, indexes, etc. More detailed slow query data can be found under the slow query analysis feature.DB status check
Checks the health status of the DB layer in the current database.
Others
Lists other values that require DBA's attention.