Documentation Home
MySQL Enterprise Monitor 8.0 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 6.0Mb
PDF (A4) - 6.0Mb


20.8 Performance Advisors

This section describes the Performance Advisors.

Binary Log Usage Exceeding Disk Cache Memory Limits

When binary log usage exceeds the binary log cache memory limits, it is performing excessive disk operations. For optimal performance, transactions that move through the binary log should be contained within the binary log cache.

For more information on the binary log and binary log cache, see The Binary Log.

Default frequency 00:05:00

Default auto-close enabled no

Database File I/O Global Summary

Exposes the current summary of file I/O by wait type globally from the sys.x$io_global_by_wait_by_latency view.

This advisor has no configurable thresholds and is used to populate the graphs and tables of the Database File I/O report. The report continues to display historical data if the Advisor is disabled, but does not display any new data.

Excessive Disk Temporary Table Usage Detected

If the space required to build a temporary table exceeds either tmp_table_size or max_heap_table_size, MySQL creates a disk-based table in the server's tmpdir directory. Also, tables that have TEXT or BLOB columns are automatically placed on disk.

For performance reasons it is ideal to have most temporary tables created in memory, leaving exceedingly large temporary tables to be created on disk.

Default frequency 00:05:00

Default auto-close enabled no

Excessive Number of Locked Processes

Depending on the circumstances, storage engines, and other factors, one process may be using or accessing a resource (for example a table or row) required by another process in such a way that the second process cannot proceed until the first process releases the resource. In this case the second process is in a locked state until the resource is released. If many processes are in a locked state it may be a sign of serious trouble related to resource contention, or a long running session that is not releasing currently held locks when it should have.

Default frequency 00:01:00

Default auto-close enabled no

Excessive Number of Long Running Processes

Most applications and databases are designed to execute queries very quickly. If many queries are taking a long time to execute (more than a few seconds) it can be a sign of trouble. In such cases queries may need to be tuned or rewritten, or indexes added to improve performance. In other cases the database schema may have to be redesigned.

Default frequency 00:01:00

Default auto-close enabled no

Excessive Number of Long Running Processes Locked

Most applications and databases are designed to execute queries very quickly, and to avoid resource contention where one query is waiting for another to release a lock on some shared resource. If many queries are locked and taking a long time to execute (more than a few seconds), it can be a sign of performance trouble and resource contention. In such cases queries may need to be tuned or rewritten, or indexes added to improve performance. In other cases the database schema may have to be redesigned.

Default frequency 00:01:00

Default auto-close enabled no

Flush Time Set To Non-Zero Value

If flush_time is set to a non-zero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk. If your system is unreliable and tends to lock up or restart often, forcing out table changes this way degrades performance but can reduce the chance of table corruption or data loss. We recommend that this option be used only on Windows, or on systems with minimal resources.

Default frequency 06:00:00

Default auto-close enabled no

Indexes Not Being Used Efficiently

The target server does not appear to be using indexes efficiently. The values of Handler_read_rnd_next and Handler_read_rnd together, which reflect the number of rows read via full table scans, are high compared to the Handler variables which denote index accesses, such as Handler_read_key, Handler_read_next, and so on. You should examine your tables and queries for proper use of indexes.

Default frequency 00:05:00

Default auto-close enabled no

InnoDB Buffer Pool Writes May Be Performance Bottleneck

For optimal performance, InnoDB should not have to wait before writing pages into the InnoDB buffer pool.

Default frequency 00:05:00

Default auto-close enabled yes

InnoDB Flush Method May Not Be Optimal

Different values for innodb_flush_method can have a marked effect on InnoDB performance. In some versions of GNU/Linux and Unix, flushing files to disk by invoking fsync() (which InnoDB uses by default) or other similar methods, can be surprisingly slow. If you are dissatisfied with database write performance, you might try setting the innodb_flush_method parameter to O_DIRECT or O_DSYNC.

Default frequency 06:00:00

Default auto-close enabled no

InnoDB Log Buffer Flushed To Disk After Each Transaction

By default, InnoDB's log buffer is written out to the log file at each transaction commit and a flush-to-disk operation is performed on the log file, which enforces ACID compliance. In the event of a crash, if you can afford to lose a second's worth of transactions, you can achieve better performance by setting innodb_flush_log_at_trx_commit to either 0 or 2. If you set the value to 2, then only an operating system crash or a power outage can erase the last second of transactions. This can be very useful on slave servers, where the loss of a second's worth of data can be recovered from the master server if needed.

Default frequency 06:00:00

Default auto-close enabled yes

InnoDB Not Using Newest File Format

Note

This advisor does not evaluate against MySQL 8, or higher.

InnoDB supports compressed tables (COMPRESSED row format) and more efficient BLOB handling (DYNAMIC row format), but both features require support for the latest file format (innodb_file_format=Barracuda). These features also require the use of the ROW_FORMAT=[DYNAMIC|COMPRESSED] in CREATE TABLE and ALTER TABLE statements.

For more information, see Defining the Row Format of a Table.

Default frequency 12:00:00

Default auto-close enabled no

InnoDB Log Waits May Be Performance Bottleneck

For optimal performance, InnoDB should not have to wait before writing DML activity to the InnoDB log buffer.

Default frequency 00:05:00

Default auto-close enabled no

MyISAM Concurrent Insert Setting May Not Be Optimal

MyISAM uses table-level locking, which can adversely affect performance when there are many concurrent INSERT and SELECT statements because INSERTs block all SELECTs until the INSERT is completed. However, MyISAM can be configured to allow INSERT and SELECT statements to run concurrently in certain situations.

  • If concurrent_insert is set to 1, the default, or AUTO as of MySQL 5.5.3 or later, MySQL allows INSERT and SELECT statements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file.

  • If concurrent_insert is set to 2, available in MySQL 5.0.6 and later, or ALWAYS as of MySQL 5.5.3 or later, MySQL allows concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

    Setting concurrent_insert to 2 allows tables to grow even when there are holes in the middle. This can be bad for applications that delete large chunks of data but continue to issue many SELECTs, thus effectively preventing INSERTs from filling the holes.

Default frequency 06:00:00

Default auto-close enabled no

Prepared Statements Not Being Closed

Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement.

However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed.

Default frequency 00:05:00

Default auto-close enabled no

Prepared Statements Not Being Used Effectively

Prepared statements may increase performance in applications that execute similar statements more than once, primarily because the query is parsed only once. Prepared statements can also reduce network traffic because it is only necessary to send the data for the parameters for each execution rather than the whole statement.

However, prepared statements take time to prepare and consume memory in the MySQL server until they are closed, so it is important to use them properly. If you are only executing a statement a few times, the overhead of creating a prepared statement may not be worthwhile.

Default frequency 00:05:00

Default auto-close enabled no

Query Cache Is Excessively Fragmented

Note

This advisor does not evaluate against MySQL 8, or higher.

Enabling the query cache can significantly increase performance for SELECT queries that are identically executed across many connections, returning the same result set. However, performance can be adversely affected if the memory used for the query cache is excessively fragmented, causing the server to pause while it is removing entries from the cache or searching the free block list for a good block to use to insert a new query into the cache.

Default frequency 00:05:00

Default auto-close enabled no

Table Lock Contention Excessive

Performance can be degraded if the percentage of table operations that have to wait for a lock is high compared to the overall number of locks. This can happen when using a table-level locking storage engine, such as MyISAM, instead of a row-level locking storage engine.

Default frequency 00:05:00

Default auto-close enabled no

Thread Cache Not Enabled

Each connection to the MySQL database server runs in its own thread. Thread creation takes time, so rather than killing the thread when a connection is closed, the server can keep the thread in its thread cache and use it for a new connection later.

Default frequency 00:05:00

Default auto-close enabled no

Thread Pool Stall Limit Too Low

The thread_pool_stall_limit variable enables the thread pool to handle long-running statements. If a long-running statement was permitted to block a thread group, all other connections assigned to the group would be blocked and unable to start execution until the long-running statement completed. In the worst case, this could take hours or even days.

The value of thread_pool_stall_limit should be chosen such that statements that execute longer than its value are considered stalled. Stalled statements generate a lot of extra overhead since they involve extra context switches and in some cases even extra thread creations. On the other hand, setting the thread_pool_stall_limit parameter too high means that long-running statements block a number of short-running statements for longer than necessary. Short wait values permit threads to start more quickly. Short values are also better for avoiding deadlock situations. Long wait values are useful for workloads that include long-running statements, to avoid starting too many new statements while the current ones execute.

Default frequency 00:05:00

Default auto-close enabled no

Thread Pooling Not Enabled

As of MySQL 5.5.16, commercial distributions of MySQL include a thread pool plug-in that provides an alternative thread-handling model designed to reduce overhead and improve performance. It implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections.

With servers that have many concurrent active connections (generally, more than the number of CPUs within the machine) it can be beneficial for performance to enable the Thread Pool plug-in. This keeps the number of actively executing threads within the server lower, generally leaving less contention for locks and resources, whilst still maintaining very high connection counts from applications.

Default frequency 00:05:00

Default auto-close enabled no

Too Many Concurrent Queries Running

Too many active queries indicates there is a severe load on the server, and may be a sign of lock contention or unoptimized SQL queries.

Default frequency 00:05:00

Default auto-close enabled no