MySQL metrics collected by DPA
The following sections list the metrics that DPA collects for MySQL database instances.
|InnoDB Buffer Pool Hit Ratio||
The rate at which the InnoDB engine finds the data blocks it needs in memory rather than having to read from disk. Innodb_buffer_pool_size is a very important parameter for InnoDB performance. A rule of thumb is to set the innodb_buffer_pool_size up to the total size of the database but not to exceed about 70% of total RAM.
If you have MyISAM tables, you want to balance the key_buffer_size and the innodb_buffer_pool_size to best utilize memory for your MySQL instance.
If the hit ratio is lower than 90%, investigate increasing the buffer pool in
|InnoDB Buffer Pool Consumed Space||
The percentage of the InnoDB buffer pool that contains data. Innodb_buffer_pool_size is a very important parameter for InnoDB performance. A rule of thumb is to set the
If this metric is either too high or too low, consider the following:
|InnoDB Buffer Pool Flushed Page Rate||
The number of requests per second to flush pages from the InnoDB buffer pool to the data file. Flushing pages to disk is a normal InnoDB operation. InnoDB tries to do this activity in the background when the total load is low.
If the flush rate is too high, consider the following:
|InnoDB Buffer Pool Data Pages||The number of pages that contain data in the InnoDB buffer pool. This includes both dirty and clean pages.|
|InnoDB % of Dirty Buffer Pool Pages||The percentage of InnoDB buffer pool data pages that have been changed in memory but have not yet been written (flushed) to disk.|
|InnoDB fsync Call Rate||The number of InnoDB fsync() system calls per second made to flush both the data and log files to disk.|
|InnoDB Log Write Rate||
The number of requests per second to write to the InnoDB redo log. The general recommendation is to set the combined size of log files to about 25%-100% of the buffer pool size to avoid unnecessary buffer pool flush activity on log file overwrite.
A larger log file size will increase the time needed for a recovery process.
If this is one of your top metrics, consider increasing the
|InnoDB Data Read Ops Rate||The number of InnoDB data read operations per second.|
|InnoDB Data Write Ops Rate||The number of InnoDB data write operations per second.|
Throughput of bytes sent from MySQL to clients. If Bytes Sent has an abnormal spike or if it is higher than normal, consider the following:
Throughput of bytes received by MySQL from clients. If Bytes Received has an abnormal spike or if it is higher than normal in general, consider:
|Round-trip Time||The round-trip time when running "select 1" against this instance (includes network time but not connect time). If this is high, contact your network administrator to understand network latency.|
The number of threads that are blocked because another thread is holding a lock on an object, typically a table or an index. Drill down in the Trend page to locate additional details about what the blocking sessions are doing. Tune the queries you find by adding indexes or rewriting queries to minimize the time the locks are held.
The number of active threads in the database instance to support client connections. This metric is based on the MySQL Global Status variable
MySQL employs a thread cache to reduce the performance penalties associated with creating and destroying threads. The size of the thread cache is governed by the
Each thread has some overhead in the form of server and kernel resources, including stack space, that affects the ability to scale to handle large numbers of connections. If you need to handle a large number of simultaneous connections, a common solution is to decrease the thread stack size. Doing so will limit memory-consuming activities conducted by the thread.
If Active Threads is too high, consider the following:
The number of created threads in the database instance to support client connections in the given interval. This metric is based on the MySQL Global Status variable
Because thread creation and disposal can be expensive, MySQL employs a thread cache. When a connection is established, MySQL will create a new thread if an available thread cannot be found in the thread cache. When a connection ends, its thread is returned to the thread cache unless the cache is full. To monitor how many threads are currently running (cached or not), look at the Active Threads (sessions) metric.
The number of connection attempts in the given time interval (successful or not). This metric is based on the MySQL Global Status variable connections.
If the Connection Attempts value is high, investigate the connection attempts in the logs. Enable logging of the connection attempts in the following ways:
|Total Instance Wait Time||The total wait time for the instance.|
InnoDB Logical I/O
|InnoDB Row Read Rate||
The number of rows that are read from InnoDB tables per second. An occasional spike in this rate can indicate that a mysqldump backup task is running.
If you see a high InnoDB Row Read Rate that you believe is contributing to slow performance, consider optimizing the SQL to reduce the number of rows being read:
|InnoDB Buffer Pool Read Rate||The number of logical read requests per second from the InnoDB buffer pool. High values usually indicate high load on the system. Reads from the buffer pool are efficient reads, so high rates only rarely indicate a performance problem.|
|InnoDB Buffer Pool Write Rate||The number of requests per second to write to the InnoDB buffer pool.|
|Table Cache Hit Ratio||
The percentage of time that MySQL used an available cached "file descriptor" (that is, an
Whenever MySQL needs to access a table, it needs the table structure. The structures of previously opened tables are stored in the table cache. If a table's structure has not been cached, MySQL needs to load the structure from disk into cache, negatively affecting database performance. The lower this ratio is, the more the database has to load table structures from disk. Table structures are stored in .frm files on disk (tableName.frm).
If the Table Cache Hit Ratio is low, increase the
|Table Cache Filled||The percentage of the cache that is filled with "file descriptors" (that is, an
|Row Sort Rate||
The number of rows sorted per second while executing statements. If MySQL cannot use an index to retrieve presorted rows, it performs a sort that increments the
If this metric is high, consider these solutions:
|Sort Merge Passes||
The number of merge passes per second performed by the sort algorithm. A Sort Merge Pass occurs if sorting large amounts of data using a limited amount of space. Performance suffers when these sorts can not be performed in memory. When the sort buffer overflows, MySQL creates temporary files on disk to use in the file sorting and merging algorithm. The data is sorted in multiple passes to first sort small chunks of data before merging the results together.
|Joins By Table Scan||The number of joins that performed table scans (that is, joins that did not use indexes).|
|Temp Table Creation Rate||
The number of internal temporary tables created per second while executing statements. MySQL creates internal temporary tables to process operations such as SELECT ... GROUP BY / ORDER BY and SELECT DISTINCT. Unfortunately, temporary tables larger than the sizes specified in
If this metric is high, you run the risk of temporary tables being created on disk. Consider the following:
Consider also increasing the
|On-Disk Temp Table Creation Rate||The number of internal on-disk temporary tables created per second while executing statements.|
|Statements Execution Rate||
The number of statements executed per second, not including those executed from stored programs. This is only a problem if your users complain about poor performance. Consider the following:
|Statements Execution Rate from Stored Programs||
The number of statements executed per second from programs. This is only a problem if your users complain about poor performance. Consider these measures:
|Select Statement Rate||The number of times a SELECT statement has been executed per second.|
|Insert Statement Rate||The number of times an INSERT statement has been executed per second.|
|Update Statement Rate||The number of times an UPDATE statement has been executed per second.|
|Delete Statement Rate||The number of times a DELETE statement has been executed per second.|