Documentation forDatabase Performance Analyzer

MySQL metrics collected by DPA

The following sections list the metrics that DPA collects for MySQL database instances.

  • Learn how to view these metrics and change the thresholds.
  • For detailed information about resolving issues, click the Information link next to the metric on the Resources tab. The Information link is not available for all metrics.

Memory

Metric Description
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 my.cnf and my.ini by updating the innodb_buffer_pool_size system variable and then restarting MySQL.

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 innodb_buffer_pool_size up to the total size of the database but not to exceed about 70% of total RAM. A general good practice is to size the buffer pool such that it is mostly full. By doing this, it indicates that you are not wasting memory and that queries are finding the majority of their data in the buffer pool.

If this metric is either too high or too low, consider the following:

  • If the Consumed Space is consistently low, this indicates that your buffer pool is too big and memory is unnecessarily allocated to the buffer pool. Investigate lowering the innodb_buffer_pool_size variable.
  • If the Consumed Space is consistently very high (99% or higher), this may indicate that the size of the buffer pool is too low. Check the resource metric InnoDB Buffer Pool Hit Ratio. If this metric is periodically or consistently low, investigate increasing the innodb_buffer_pool_size variable.
  • If the Consumed Space is low, but it is on the rise, this indicates that the buffer is being initially populated with data. No action is needed at this point.
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:

  • If the InnoDB log files are too small, this forces a checkpoint operation that flushes buffer pool pages to disk. Check the InnoDB Log Write Rate metric. If you see a lot of log writes that correspond to high InnoDB Buffer Pool Flushed Page Rate values, increase the innodb_log_file_size variable.

  • A buffer pool size that is too small can cause frequent flushes. 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, balance the key_buffer_size and innodb_buffer_pool_size values to best utilize memory for your MySQL instance.

  • Check the load, mostly writes, on the system and investigate ways to decrease the load. Although SELECTs can also cause pages to be flushed from the buffer pool to disk, writes usually cause higher flush rates.

  • Optimize SQL to reduce the number of rows being written:

    1. Go to the Trends page for the timeframe and look at the UPDATE, DELETE, and INSERT statements with the highest wait time.
    2. Determine which statements have the highest Rows Affected or Sent value on the SQL Data tab.
    3. For these statements, consider evaluating WHERE clauses to ensure you process only rows that are required.
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.

Disk

Metric Description
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_log_file_size in my.cnf and my.ini and then restarting MySQL.

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.

Network

Metric Description
Bytes Sent

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:

  1. Examine the Bytes Received (KB/s) metric together with Bytes Sent (KB/s) to gain a more complete story of network traffic.
  2. Optimize SQL to reduce network traffic. Go to the Trends page to identify which SQL statements have the highest wait time. Determine which of these statements have the highest Rows Affected or Sent statistic on the SQL Data tab. For these statements:

    • Evaluate WHERE clauses to ensure you are processing only rows that are required.

    • Eliminate columns from your result set that you don't need.

    • Use summary tables where possible to limit the number of rows processed/returned.

    • Rewrite complicated queries to assist in processing fewer rows.

  3. Enlist the assistance of your network admin to evaluate network traffic, with a focus on the traffic between the Application server and the MySQL server.

Bytes Received

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:

  1. Examine the Bytes Received (KB/s) metric together with Bytes Sent (KB/s) to gain a more complete story of network traffic.
  2. Check the LOAD DATA infile statements which can contribute to the network traffic.
  3. Enlist the assistance of your network admin to evaluate network traffic, with a focus on the traffic between the Application server and the MySQL server.
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.

Sessions

Metric Description
Blocked Threads

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.

Active Threads

The number of active threads in the database instance to support client connections. This metric is based on the MySQL Global Status variable threads_running. MySQL associates each client connection with a dedicated thread that handles all requests for that connection. This means that there are as many threads as there are clients currently connected.

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 thread_cache_size system variable. When a connection is established, MySQL creates 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 being created because no cached thread is available, look at the Created Threads (sessions) metric.

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:

  • Use connection pooling in your applications to reduce the number of simultaneous queries.
  • Use the MySQL master/slave architecture and move some or all SELECT queries to a slave.
  • MySQL may be incurring excess overhead such as memory. If you feel that this is a problem, you can decrease the thread stack size, but you need to realize that this will limit memory-consuming activities conducted by the thread. In other words, it limits complexity of SQL statements and stored program recursion depth. To set the stack size, start the server with --thread_stack=N where N is in bytes.
  • If the Active Threads value is higher than the thread cache size, MySQL may be incurring excess expense due to the creation and destruction of threads. If you feel that this overhead is a problem, you can try increasing the size of the thread cache by increasing the value of the thread_cache_size system variable.
Created Threads

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 threads_created. MySQL associates each client connection with a dedicated thread that handles all requests for that connection. This means that there are as many threads as there are clients currently connected.

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.

  • If the Thread Creation Rate value is high and the thread cache is not full, this generally means that the cache is being filled, which is a normal situation. To see how many threads are in the cache and the size of the thread cache, look at the threads_cached and thread_cache_size system variables.
  • If the Thread Creation Rate value is high and the thread cache is full, this means that available threads are not being found in the thread cache, causing new connections to create new threads, which can be an expensive operation. If you think this overhead is causing problems, you can try increasing the size of the thread cache by increasing the value of the thread_cache_size system variable.
  • Consider using connection pooling in your application(s).
Connection Attempts

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:

  • If you are only interested in aborted attempts, make sure that the value (level) of the log_warning system variable is 2, and then check the error log.
  • If you are interested in successful and aborted connections, make sure that the general query log is enabled by checking the general_log system variable. The location of the general query log file is in the general_log_file system variable. Enabling the general query log can decrease the performance of the MySQL server, as every connection attempt and SQL statement will be logged.

Waits

Metric Description
Total Instance Wait Time The total wait time for the instance.

InnoDB Logical I/O

Metric Description
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:

  1. Go to the Trends page for the time frame and look at the statements with the highest wait time.
  2. Determine which of the statements have the highest 'Rows Examined' value on the SQL Data tab.
  3. For these statements, consider the following:
    • Use summary tables where possible to limit the number of rows processed.
    • Rewrite complicated queries to assist in processing fewer rows.
    • Evaluate WHERE clauses to ensure you process only rows that are required
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.

Objects

Metric Description
Table Cache Hit Ratio

The percentage of time that MySQL used an available cached "file descriptor" (that is, an .frm file that contains a table's underlying format).

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_open_cache variable in my.cnf and my.ini. Recommendations:

  • Set table_open_cache to the total number of tables in the database.
  • A typical range for the table_open_cache is from 2000 (default) to 100,000.
Table Cache Filled The percentage of the cache that is filled with "file descriptors" (that is, an .frm file that contains a table's underlying format).

Sorts/Joins

Metric Description
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 sort_rows counter.

If this metric is high, consider these solutions:

  • Check the Sort Merge Passes resource metric and determine if there is a need to increase sort_buffer_size.

  • Optimize the SQL to reduce sorting.

    1. Go to the Trends page for the time frame and look at the statements with the highest wait time.
    2. Find the statements with the highest Rows Sorted value on the SQL Data tab.
    3. For these statements, consider using a combined or covered index with the same columns in the same order as the ORDER BY clause. In some cases, MySQL can use an index to satisfy an ORDER BY clause without performing any extra sorting.
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.

  • Look at the Row Sort Rate metric. If there is a lot of sorting happening in this time frame, follow the suggested solutions.
  • Increase the global sort_buffer_size system variable to improve the performance of queries that sort a lot of data.
  • Increase the sort_buffer_size at the session level with a SET statement. Add the statement to your application code before running these kinds of queries. For example: SET session sort_buffer_size = 8M
  • Use an index with columns in the ORDER BY clause. MySQL might use this index to satisfy an ORDER BY clause without extra sorting.
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 tmp_table_size and max_heap_table_size have to be converted to a slow, disk-based MyISAM temporary table. Likewise, if the query uses TEXT or BLOB fields, MySQL always has to use slow, disk-based temporary tables because in-memory temporary tables don't support those fields.

If this metric is high, you run the risk of temporary tables being created on disk. Consider the following:

  1. Go to the Trends page to identify which SQL statements have the highest wait time.
  2. Find the statements with the highest Temp Tables Created statistic on the SQL Data tab.
  3. For these statements:
    • Use a combined or covered index that has the same columns in the same order as the ORDER BY clause. In some cases, MySQL can use an index to satisfy an ORDER BY clause without doing any extra sorting.
    • Remove TEXT/BLOB fields if they are not needed for the query.

Consider also increasing the tmp_table_size or max_heap_table_size values to reduce the number of internal temporary tables that have to be written to disk.

On-Disk Temp Table Creation Rate The number of internal on-disk temporary tables created per second while executing statements.

Statements

Metric Description
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:

  • Identify and tune the queries with the highest wait time.
  • Look to see if there are a high number of executions of a SQL Statement. Look for possible ways to modify your application to decrease the number of executions, such as caching.
  • If you believe poor performance is due to the volume of statements being executed, consider implementing a MySQL master and slave architecture and move some or all SELECT queries to a slave.
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:

  • Identify and tune the queries with the highest wait time.
  • If you believe poor performance is due to the volume of statements being executed, consider implementing a MySQL master and slave architecture and move some or all SELECT queries to a slave.
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.