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% through 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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.