# Monitoring parallel query for Aurora MySQL If your Aurora MySQL cluster uses parallel query, you might see an increase in `VolumeReadIOPS` values. Parallel queries don't use the buffer pool. Thus, although the queries are fast, this optimized processing can result in an increase in read operations and associated charges. In addition to the Amazon CloudWatch metrics described in [Viewing metrics in the Amazon RDS console](USER_Monitoring.md), Aurora provides other global status variables. You can use these global status variables to help monitor parallel query execution. They can give you insights into why the optimizer might use or not use parallel query in a given situation. To access these variables, you can use the `[SHOW GLOBAL STATUS](https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html)` command. You can also find these variables listed following. A parallel query session isn't necessarily a one-to-one mapping with the queries performed by the database. For example, suppose that your query plan has two steps that use parallel query. In that case, the query involves two parallel sessions and the counters for requests attempted and requests successful are incremented by two. When you experiment with parallel query by issuing `EXPLAIN` statements, expect to see increases in the counters designated as "not chosen" even though the queries aren't actually running. When you work with parallel query in production, you can check if the "not chosen" counters are increasing faster than you expect. At this point, you can adjust so that parallel query runs for the queries that you expect. To do so, you can change your cluster settings, query mix, DB instances where parallel query is turned on, and so on. These counters are tracked at the DB instance level. When you connect to a different endpoint, you might see different metrics because each DB instance runs its own set of parallel queries. You might also see different metrics when the reader endpoint connects to a different DB instance for each session. | Name | Description | | --- | --- | | `Aurora_pq_bytes_returned` | The number of bytes for the tuple data structures transmitted to the head node during parallel queries. Divide by 16,384 to compare against `Aurora_pq_pages_pushed_down`. | | `Aurora_pq_max_concurrent_requests` | The maximum number of parallel query sessions that can run concurrently on this Aurora DB instance. This is a fixed number that depends on the AWS DB instance class. | | `Aurora_pq_pages_pushed_down` | The number of data pages (each with a fixed size of 16 KiB) where parallel query avoided a network transmission to the head node. | | `Aurora_pq_request_attempted` | The number of parallel query sessions requested. This value might represent more than one session per query, depending on SQL constructs such as subqueries and joins. | | `Aurora_pq_request_executed` | The number of parallel query sessions run successfully. | | `Aurora_pq_request_failed` | The number of parallel query sessions that returned an error to the client. In some cases, a request for a parallel query might fail, for example due to a problem in the storage layer. In these cases, the query part that failed is retried using the nonparallel query mechanism. If the retried query also fails, an error is returned to the client and this counter is incremented. | | `Aurora_pq_request_in_progress` | The number of parallel query sessions currently in progress. This number applies to the particular Aurora DB instance that you are connected to, not the entire Aurora DB cluster. To see if a DB instance is close to its concurrency limit, compare this value to `Aurora_pq_max_concurrent_requests`. | | `Aurora_pq_request_not_chosen` | The number of times parallel query wasn't chosen to satisfy a query. This value is the sum of several other more granular counters. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_below_min_rows` | The number of times parallel query wasn't chosen due to the number of rows in the table. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_column_bit` | The number of parallel query requests that use the nonparallel query processing path because of an unsupported data type in the list of projected columns. | | `Aurora_pq_request_not_chosen_column_geometry` | The number of parallel query requests that use the nonparallel query processing path because the table has columns with the `GEOMETRY` data type. For information about Aurora MySQL versions that remove this limitation, see [Upgrading parallel query clusters to Aurora MySQL version 3](aurora-mysql-parallel-query-optimizing.md#aurora-mysql-parallel-query-upgrade-pqv2). | | `Aurora_pq_request_not_chosen_column_lob` | The number of parallel query requests that use the nonparallel query processing path because the table has columns with a `LOB` data type, or `VARCHAR` columns that are stored externally due to the declared length. For information about Aurora MySQL versions that remove this limitation, see [Upgrading parallel query clusters to Aurora MySQL version 3](aurora-mysql-parallel-query-optimizing.md#aurora-mysql-parallel-query-upgrade-pqv2). | | `Aurora_pq_request_not_chosen_column_virtual` | The number of parallel query requests that use the nonparallel query processing path because the table contains a virtual column. | | `Aurora_pq_request_not_chosen_custom_charset` | The number of parallel query requests that use the nonparallel query processing path because the table has columns with a custom character set. | | `Aurora_pq_request_not_chosen_fast_ddl` | The number of parallel query requests that use the nonparallel query processing path because the table is currently being altered by a fast DDL `ALTER` statement. | | `Aurora_pq_request_not_chosen_few_pages_outside_buffer_pool` | The number of times parallel query wasn't chosen, even though less than 95 percent of the table data was in the buffer pool, because there wasn't enough unbuffered table data to make parallel query worthwhile. | | `Aurora_pq_request_not_chosen_full_text_index` | The number of parallel query requests that use the nonparallel query processing path because the table has full-text indexes. | | `Aurora_pq_request_not_chosen_high_buffer_pool_pct` | The number of times parallel query wasn't chosen because a high percentage of the table data (currently, greater than 95 percent) was already in the buffer pool. In these cases, the optimizer determines that reading the data from the buffer pool is more efficient. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_index_hint` | The number of parallel query requests that use the nonparallel query processing path because the query includes an index hint. | | `Aurora_pq_request_not_chosen_innodb_table_format` | The number of parallel query requests that use the nonparallel query processing path because the table uses an unsupported InnoDB row format. Aurora parallel query only applies to the `COMPACT`, `REDUNDANT`, and `DYNAMIC` row formats. | | `Aurora_pq_request_not_chosen_long_trx` | The number of parallel query requests that used the nonparallel query processing path, due to the query being started inside a long-running transaction. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_no_where_clause` | The number of parallel query requests that use the nonparallel query processing path because the query doesn't include any `WHERE` clause. | | `Aurora_pq_request_not_chosen_range_scan` | The number of parallel query requests that use the nonparallel query processing path because the query uses a range scan on an index. | | `Aurora_pq_request_not_chosen_row_length_too_long` | The number of parallel query requests that use the nonparallel query processing path because the total combined length of all the columns is too long. | | `Aurora_pq_request_not_chosen_small_table` | The number of times parallel query wasn't chosen due to the overall size of the table, as determined by number of rows and average row length. An `EXPLAIN` statement can increment this counter even though the query isn't actually performed. | | `Aurora_pq_request_not_chosen_temporary_table` | The number of parallel query requests that use the nonparallel query processing path because the query refers to temporary tables that use the unsupported `MyISAM` or `memory` table types. | | `Aurora_pq_request_not_chosen_tx_isolation` | The number of parallel query requests that use the nonparallel query processing path because query uses an unsupported transaction isolation level. On reader DB instances, parallel query only applies to the `REPEATABLE READ` and `READ COMMITTED` isolation levels. | | `Aurora_pq_request_not_chosen_update_delete_stmts` | The number of parallel query requests that use the nonparallel query processing path because the query is part of an `UPDATE` or `DELETE` statement. | | `Aurora_pq_request_not_chosen_unsupported_access` | The number of parallel query requests that use the nonparallel query processing path because the `WHERE` clause doesn't meet the criteria for parallel query. This result can occur if the query doesn't require a data-intensive scan, or if the query is a `DELETE` or `UPDATE` statement. | | `Aurora_pq_request_not_chosen_unsupported_storage_type` | The number of parallel query requests that use the nonparallel query processing path because the Aurora MySQL DB cluster isn't using a supported Aurora cluster storage configuration. This parameter is available in Aurora MySQL version 3.04 and higher. For more information, see [Limitations](aurora-mysql-parallel-query.md#aurora-mysql-parallel-query-limitations). | | `Aurora_pq_request_throttled` | The number of times parallel query wasn't chosen due to the maximum number of concurrent parallel queries already running on a particular Aurora DB instance. |