MySQL Enterprise Monitor 8.0 Release Notes
This section describes the Performance Advisors.
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, seeThe Binary Log.
Default frequency00:05:00
Default auto-close enabledno
Exposes the current summary of file I/O by wait type globally from thesys.x$io_global_by_wait_by_latencyview.
This advisor has no configurable thresholds and is used to populate the graphs and tables of theDatabase File I/Oreport. The report continues to display historical data if the Advisor is disabled, but does not display any new data.
If the space required to build a temporary table exceeds eithertmp_table_sizeormax_heap_table_size, MySQL creates a disk-based table in the server'stmpdirdirectory. 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 frequency00:05:00
Default auto-close enabledno
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 frequency00:01:00
Default auto-close enabledno
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 frequency00:01:00
Default auto-close enabledno
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 frequency00:01:00
Default auto-close enabledno
Ifflush_timeis 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 frequency06:00:00
Default auto-close enabledno
The target server does not appear to be using indexes efficiently. The values ofHandler_read_rnd_nextandHandler_read_rndtogether, which reflect the number of rows read via full table scans, are high compared to the Handler variables which denote index accesses, such asHandler_read_key,Handler_read_next, and so on. You should examine your tables and queries for proper use of indexes.
Default frequency00:05:00
Default auto-close enabledno
For optimal performance, InnoDB should not have to wait before writing pages into the InnoDB buffer pool.
Default frequency00:05:00
Default auto-close enabledyes
Different values forinnodb_flush_methodcan have a marked effect on InnoDB performance. In some versions of GNU/Linux and Unix, flushing files to disk by invokingfsync()(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 theinnodb_flush_methodparameter toO_DIRECTorO_DSYNC.
Default frequency06:00:00
Default auto-close enabledno
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 settinginnodb_flush_log_at_trx_committo 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 frequency06:00:00
Default auto-close enabledyes
Note
This advisor does not evaluate against MySQL 8, or higher.
InnoDB supports compressed tables (COMPRESSEDrow format) and more efficient BLOB handling (DYNAMICrow format), but both features require support for the latest file format (innodb_file_format=Barracuda). These features also require the use of theROW_FORMAT=[DYNAMIC|COMPRESSED]inCREATE TABLEandALTER TABLEstatements.
For more information, seeDefining the Row Format of a Table.
Default frequency12:00:00
Default auto-close enabledno
For optimal performance, InnoDB should not have to wait before writing DML activity to the InnoDB log buffer.
Default frequency00:05:00
Default auto-close enabledno
MyISAM uses table-level locking, which can adversely affect performance when there are many concurrentINSERTandSELECTstatements becauseINSERTs block allSELECTs until theINSERTis completed. However, MyISAM can be configured to allowINSERTandSELECTstatements to run concurrently in certain situations.
If
concurrent_insertis set to 1, the default, orAUTOas of MySQL 5.5.3 or later, MySQL allowsINSERTandSELECTstatements to run concurrently for MyISAM tables that have no free blocks in the middle of the data file.If
concurrent_insertis set to 2, available in MySQL 5.0.6 and later, orALWAYSas 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_insertto 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 manySELECTs, thus effectively preventingINSERTs from filling the holes.
Default frequency06:00:00
Default auto-close enabledno
准备好的语句可能会增加在美联社的表现plications 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 frequency00:05:00
Default auto-close enabledno
准备好的语句可能会增加在美联社的表现plications 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 frequency00:05:00
Default auto-close enabledno
Note
This advisor does not evaluate against MySQL 8, or higher.
Enabling the query cache can significantly increase performance forSELECTqueries 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 frequency00:05:00
Default auto-close enabledno
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 frequency00:05:00
Default auto-close enabledno
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 frequency00:05:00
Default auto-close enabledno
Thethread_pool_stall_limitvariable 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 ofthread_pool_stall_limit应该选择这样的语句执行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 thethread_pool_stall_limit参数太高意味着长期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 frequency00:05:00
Default auto-close enabledno
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 frequency00:05:00
Default auto-close enabledno