# Tuning Aurora MySQL with Amazon DevOps Guru proactive insights DevOps Guru proactive insights detect known problematic conditions on your Aurora MySQL DB clusters before they occur. DevOps Guru can do the following: + Prevent many common database issues by cross-checking your database configuration against common recommended settings. + Alert you to critical issues in your fleet that, if left unchecked, can lead to larger problems later. + Alert you to newly discovered problems. Every proactive insight contains an analysis of the cause of the problem and recommendations for corrective actions. **Topics** + [The InnoDB history list length increased significantly](proactive-insights.history-list.md) + [Database is creating temporary tables on disk](proactive-insights.temp-tables.md) # The InnoDB history list length increased significantly Starting on *date*, your history list for row changes increased significantly, up to *length* on *db-instance*. This increase affects query and database shutdown performance. **Topics** + [Supported engine versions](#proactive-insights.history-list.context.supported) + [Context](#proactive-insights.history-list.context) + [Likely causes for this issue](#proactive-insights.history-list.causes) + [Actions](#proactive-insights.history-list.actions) + [Relevant metrics](#proactive-insights.history-list.metrics) ## Supported engine versions This insight information is supported for all versions of Aurora MySQL. ## Context The InnoDB transaction system maintains multiversion concurrency control (MVCC). When a row is modified, the pre-modification version of the data being modified is stored as an undo record in an undo log. Every undo record has a reference to its previous redo record, forming a linked list. The InnoDB history list is a global list of the undo logs for committed transactions. MySQL uses the history list to purge records and log pages when transactions no longer require the history. The history list length is the total number of undo logs that contain modifications in the history list. Each log contains one or more modifications. If the InnoDB history list length grows too large, indicating a large number of old row versions, queries and database shutdowns become slower. ## Likely causes for this issue Typical causes of a long history list include the following: + Long-running transactions, either read or write + A heavy write load ## Actions We recommend different actions depending on the causes of your insight. **Topics** + [Don't begin any operation involving a database shutdown until the InnoDB history list decreases](#proactive-insights.history-list.actions.no-shutdown) + [Identify and end long-running transactions](#proactive-insights.history-list.actions.long-txn) + [Identify the top hosts and top users by using Performance Insights.](#proactive-insights.history-list.actions.top-PI) ### Don't begin any operation involving a database shutdown until the InnoDB history list decreases Because a long InnoDB history list slows database shutdowns, reduce the list size before initiating operations involving a database shutdown. These operations include major version database upgrades. ### Identify and end long-running transactions You can find long-running transactions by querying `information_schema.innodb_trx`. **Note** Make sure also to look for long-running transactions on read replicas. **To identify and end long-running transactions** 1. In your SQL client, run the following query: ``` SELECT a.trx_id, a.trx_state, a.trx_started, TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open", a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state FROM information_schema.innodb_trx a, information_schema.processlist b WHERE a.trx_mysql_thread_id=b.id AND TIMESTAMPDIFF(SECOND,a.trx_started, now()) > 10 ORDER BY trx_started ``` 1. End each long-running transaction with the stored procedure [mysql.rds\$1kill](mysql-stored-proc-ending.md#mysql_rds_kill). ### Identify the top hosts and top users by using Performance Insights. Optimize transactions so that large numbers of modified rows are immediately committed. ## Relevant metrics The following metrics are related to this insight: + `trx_rseg_history_len` – This counter metric can be viewed in Performance Insights, as well as the `INFORMATION_SCHEMA.INNODB_METRICS` table. For more information, see [InnoDB INFORMATION\$1SCHEMA metrics table](https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-metrics-table.html) in the MySQL documentation. + `RollbackSegmentHistoryListLength` – This Amazon CloudWatch metric measures the undo logs that record committed transactions with delete-marked records. These records are scheduled to be processed by the InnoDB purge operation. The metric `trx_rseg_history_len` has the same value as `RollbackSegmentHistoryListLength`. + `PurgeBoundary` – The transaction number up to which InnoDB purging is allowed. If this CloudWatch metric doesn't advance for extended periods of time, it's a good indication that InnoDB purging is blocked by long-running transactions. To investigate, check the active transactions on your Aurora MySQL DB cluster. This metric is available only for Aurora MySQL version 2.11 and higher, and version 3.08 and higher. + `PurgeFinishedPoint` – The transaction number up to which InnoDB purging is performed. This CloudWatch metric can help you examine how fast InnoDB purging is progressing. This metric is available only for Aurora MySQL version 2.11 and higher, and version 3.08 and higher. + `TransactionAgeMaximum` – The age of the oldest active running transaction. This CloudWatch metric is available only for Aurora MySQL version 3.08 and higher. + `TruncateFinishedPoint` – The transaction number up to which undo truncation is performed. This CloudWatch metric is available only for Aurora MySQL version 2.11 and higher, and version 3.08 and higher. For more information on the CloudWatch metrics, see [Instance-level metrics for Amazon Aurora](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances). # Database is creating temporary tables on disk Your recent on-disk temporary table usage increased significantly, up to *percentage*. The database is creating around *number* temporary tables per second. This might impact performance and increase disk operations on *db-instance*. **Topics** + [Supported engine versions](#proactive-insights.temp-tables.context.supported) + [Context](#proactive-insights.temp-tables.context) + [Likely causes for this issue](#proactive-insights.temp-tables.causes) + [Actions](#proactive-insights.temp-tables.actions) + [Relevant metrics](#proactive-insights.temp-tables.metrics) ## Supported engine versions This insight information is supported for all versions of Aurora MySQL. ## Context Sometimes it's necessary for the MySQL server to create an internal temporary table while processing a query. Aurora MySQL can hold an internal temporary table in memory, where it can be processed by the TempTable or MEMORY storage engine, or stored on disk by InnoDB. For more information, see [Internal Temporary Table Use in MySQL](https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html) in the *MySQL Reference Manual*. ## Likely causes for this issue An increase in on-disk temporary tables indicates the use of complex queries. If the configured memory is insufficient to store temporary tables in memory, Aurora MySQL creates the tables on disk. This can impact performance and increase disk operations. ## Actions We recommend different actions depending on the causes of your insight. + For Aurora MySQL version 3, we recommend that you use the TempTable storage engine. + Optimize your queries to return less data by selecting only necessary columns. If you turn on the Performance Schema with all `statement` instruments enabled and timed, you can query `SYS.statements_with_temp_tables` to retrieve the list of queries that use temporary tables. For more information, see [Prerequisites for Using the sys Schema](https://dev.mysql.com/doc/refman/8.0/en/sys-schema-prerequisites.html) in the MySQL documentation. + Consider indexing columns that are involved in sorting and grouping operations. + Rewrite your queries to avoid `BLOB` and `TEXT` columns. These columns always use disk. + Tune the following database parameters: `tmp_table_size` and `max_heap_table_size`. The default values for these parameters is 16 MiB. When using the MEMORY storage engine for in-memory temporary tables, their maximum size is defined by the `tmp_table_size` or `max_heap_table_size` value, whichever is smaller. When this maximum size is reached, MySQL automatically converts the in-memory internal temporary table to an InnoDB on-disk internal temporary table. For more information, see [Use the TempTable storage engine on Amazon RDS for MySQL and Amazon Aurora MySQL](https://aws.amazon.com/blogs/database/use-the-temptable-storage-engine-on-amazon-rds-for-mysql-and-amazon-aurora-mysql/). **Note** When explicitly creating MEMORY tables with CREATE TABLE, only the `max_heap_table_size` variable determines how large a table can grow. There is also no conversion to an on-disk format. ## Relevant metrics The following Performance Insights metrics are related to this insight: + Created\$1tmp\$1disk\$1tables + Created\$1tmp\$1tables For more information, see [Created\$1tmp\$1disk\$1tables](https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Created_tmp_disk_tables) in the MySQL documentation.