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, 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 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_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_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_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.