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, see The Binary Log.
Default frequency 00:05:00
Default auto-close enabled no
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.
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
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
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
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
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
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
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
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
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
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
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 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_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 frequency 06:00:00
Default auto-close enabled no
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 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
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
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
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
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
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