Understanding the time spent by queries Reviewing query optimizer settings

Troubleshooting query performance for Aurora MySQL databases

MySQL provides query optimizer control through system variables that affect how query plans are evaluated, switchable optimizations, optimizer and index hints, and the optimizer cost model. These data points can be helpful not only while comparing different MySQL environments, but also to compare previous query execution plans with current execution plans, and to understand the overall execution of a MySQL query at any point.

Query performance depends on many factors, including the execution plan, table schema and size, statistics, resources, indexes, and parameter configuration. Query tuning requires identifying bottlenecks and optimizing the execution path.

Find the execution plan for the query and check whether the query is using appropriate indexes. You can optimize your query by using EXPLAIN and reviewing the details of each plan.
Aurora MySQL version 3 (compatible with MySQL 8.0 Community Edition) uses an EXPLAIN ANALYZE statement. The EXPLAIN ANALYZE statement is a profiling tool that shows where MySQL spends time on your query and why. With EXPLAIN ANALYZE, Aurora MySQL plans, prepares, and runs the query while counting rows and measuring the time spent at various points of the execution plan. When the query completes, EXPLAIN ANALYZE prints the plan and its measurements instead of the query result.
Keep your schema statistics updated by using the ANALYZE statement. The query optimizer can sometimes choose poor execution plans because of outdated statistics. This can lead to poor performance of a query because of inaccurate cardinality estimates of both tables and indexes. The last_update column of the innodb_table_stats table shows the last time your schema statistics were updated, which is a good indicator of "staleness."
Other issues can occur, such as distribution skew of data, that aren't taken into account for table cardinality. For more information, see Estimating ANALYZE TABLE complexity for InnoDB tables and Histogram statistics in MySQL in the MySQL documentation.

Understanding the time spent by queries

The following are ways to determine the time spent by queries:

Profiling

By default, profiling is disabled. Enable profiling, then run the slow query and review its profile.


SET profiling = 1;
Run your query.
SHOW PROFILE;

Identify the stage where the most time is spent. According to General thread states in the MySQL documentation, reading and processing rows for a SELECT statement is often the longest-running state over the lifetime of a given query. You can use the EXPLAIN statement to understand how MySQL runs this query.
Review the slow query log to evaluate rows_examined and rows_sent to make sure that the workload is similar in each environment. For more information, see Logging for Aurora MySQL databases.
Run the following command for tables that are part of the identified query:
```
SHOW TABLE STATUS\G;
```
Capture the following outputs before and after running the query on each environment:
```
SHOW GLOBAL STATUS;
```
Run the following commands on each environment to see if there are any other query/session influencing the performance of this sample query.
```
SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;
```
Sometimes, when resources on the server are busy, it impacts every other operation on the server, including queries. You can also capture information periodically when queries are run or set up a cron job to capture information at useful intervals.

Performance Schema

The Performance Schema provides useful information about server runtime performance, while having minimal impact on that performance. This is different from the information_schema, which provides schema information about the DB instance. For more information, see Turning on the Performance Schema for Performance Insights on Aurora MySQL.

Query optimizer trace

To understand why a particular query plan was chosen for execution, you can set up optimizer_trace to access the MySQL query optimizer.

Run an optimizer trace to show extensive information on all the paths available to the optimizer and its choice.


SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

Reviewing query optimizer settings

Aurora MySQL version 3 (compatible with MySQL 8.0 Community Edition) has many optimizer-related changes compared with Aurora MySQL version 2 (compatible with MySQL 5.7 Community Edition). If you have some custom values for the optimizer_switch, we recommend that you review the differences in the defaults and set optimizer_switch values that work best for your workload. We also recommend that you test the options available for Aurora MySQL version 3 to examine how your queries perform.

Note

Aurora MySQL version 3 uses the community default value of 20 for the innodb_stats_persistent_sample_pages parameter.

You can use the following command to show the optimizer_switch values:


SELECT @@optimizer_switch\G;

The following table shows the default optimizer_switch values for Aurora MySQL versions 2 and 3.

Setting	Aurora MySQL version 2	Aurora MySQL version 3
`batched_key_access`	off	off
`block_nested_loop`	on	on
`condition_fanout_filter`	on	on
`derived_condition_pushdown`	–	on
`derived_merge`	on	on
`duplicateweedout`	on	on
`engine_condition_pushdown`	on	on
`firstmatch`	on	on
`hash_join`	off	on
`hash_join_cost_based`	on	–
`hypergraph_optimizer`	–	off
`index_condition_pushdown`	on	on
`index_merge`	on	on
`index_merge_intersection`	on	on
`index_merge_sort_union`	on	on
`index_merge_union`	on	on
`loosescan`	on	on
`materialization`	on	on
`mrr`	on	on
`mrr_cost_based`	on	on
`prefer_ordering_index`	on	on
`semijoin`	on	on
`skip_scan`	–	on
`subquery_materialization_cost_based`	on	on
`subquery_to_derived`	–	off
`use_index_extensions`	on	on
`use_invisible_indexes`	–	off

For more information, see Switchable optimizations (MySQL 5.7) and Switchable optimizations (MySQL 8.0) in the MySQL documentation.

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Logging for Aurora MySQL

Aurora MySQL reference