DB instance monitoring - AWS Prescriptive Guidance

DB instance monitoring

A DB instance is the basic building block of Amazon RDS. It is an isolated database environment that runs in the cloud. For MySQL and MariaDB databases, the DB instance is the mysqld program, also known as the MySQL server, which includes multiple threads and components such as the SQL parser, the query optimizer, thread/connection handler, system and status variables, and one or more pluggable storage engines. Each storage engine is designed to support a specialized use case. The default and recommended storage engine is InnoDB, which is a transactional, general-purpose, relational database engine that complies with the atomicity, consistency, isolation, durability (ACID) model. InnoDB features in-memory structures (buffer pool, change buffer, adaptive hash index, log buffer) as well as on-disk structures (tablespaces, tables, indexes, undo log, redo log, doublewrite buffer files). To ensure that your database adheres closely to the ACID model, the InnoDB storage engine implements numerous capabilities to protect your data, including transactions, commit, rollback, crash-recovery, row-level locking, and multiversion concurrency control (MVCC).

All these internal components of a DB instance work jointly to help maintain the availability, integrity, and security of your data at the expected and satisfactory performance level. Depending on your workload, each component and feature might impose resource demands on CPU, memory, network, and storage subsystems. When a surge in demand for a specific resource exceeds the provisioned capacity or the software limits for that resource (imposed either by configuration parameters or by software design), the DB instance can experience performance degradation or complete unavailability and corruption. Therefore, it is critical to measure and monitor these internal components, compare them against defined baseline values, and generate alerts if the monitored values deviate from the expected values.

As described previously, you can use different tools to monitor your MySQL and MariaDB instances. We recommend that you use the Amazon RDS Performance Insights and CloudWatch tools for monitoring and alerting, because these tools are integrated with Amazon RDS, gather high-resolution metrics, present the latest performance information in near real time, and generate alarms.

Regardless of your preferred monitoring tool, we recommend that you turn on the Performance Schema in your MySQL and MariaDB DB instances. The Performance Schema is an optional feature for monitoring the operation of the MySQL server (the DB instance) at a low level, and is designed to have minimal impact on overall database performance. You can manage this feature by using the performance_schema parameter. Although this parameter is optional, you must use it to collect high-resolution (one second) per-SQL metrics, active session metrics, wait events, and other detailed, low-level monitoring information, which is collected by Amazon RDS Performance Insights.

Sections