20.1 Administration Advisors

This section describes the expression-based Administration
Advisors.

32-Bit Binary Running on 64-Bit AMD Or Intel System

Raises an event if a 32-bit binary is detected running on a
64-bit platform. Most 32-bit binaries can run on a 64-bit
platform. However, for performance reasons, it is recommended to
run 64-bit binaries on 64-bit platforms, and 32-bit binaries on
32-bit platforms.

Default frequency 06:00:00

Default auto-close enabled no

Binary Logging Not Synchronized To Disk At Each Write

By default, the binary log is not synchronized to disk at each
write. If the server host, operating system, or MySQL server
crash, there is a chance that the latest statements in the
binary log are not written to disk. To prevent this, you can
cause the binary log to be synchronized to disk after every Nth
binary log entry using the syn_binlog global
variable. 1 is the safest value, but also the slowest.

Default frequency 06:00:00

Default auto-close enabled no

Binary Log Debug Information Disabled

The binary log captures DML, DDL, and security changes that
occur and stores these changes in a binary format. The binary
log enables point-in-time recovery, preventing data loss during
a disaster recovery situation. It also enables you to review all
alterations made to your database.

Binary log informational events are used for debugging and
related purposes. Informational events are enabled by setting
the system variable
binlog_rows_query_log_events=TRUE (or ON). By
default, this advisor generates an event if ROW or MIXED logging
is enabled and
binlog_rows_query_log_events=FALSE (or OFF).

Note

Binary log informational events were introduced in MySQL 5.6.2
and are not supported by earlier versions of MySQL.

Default frequency 06:00:00

Default auto-close enabled no

Binary Logging Is Limited

The binary log captures DML, DDL, and security changes that
occur and stores these changes in a binary format. The binary
log enables point-in-time recovery, preventing data loss during
a disaster recovery situation. It also enables you to review all
alterations made to your database.

Binary logging can be limited to specific databases with the
--binlog-do-db and the
--binlog-ignore-db options. However, if these
options are used, your point-in-time recovery options are
limited accordingly, along with your ability to review
alterations made to your system.

Default frequency 06:00:00

Default auto-close enabled no

Binary Logging Not Enabled

The binary log captures DML, DDL, and security changes and
stores these changes in a binary format. The binary log enables
point-in-time recovery, preventing data loss during a disaster
recovery situation. It also enables you to review all
alterations made to your database.

Default frequency 06:00:00

Default auto-close enabled no

Binary Logs Automatically Removed Too Quickly

The binary log captures DML, DDL, and security changes that
occur and stores these changes in a binary format. The binary
log enables point-in-time recovery, preventing data loss during
a disaster recovery situation. It is used on master replication
servers as a record of the statements to be sent to slave
servers. It also enables you to review all alterations made to
your database.

However, the number of log files and the space they use can grow
rapidly, especially on a busy server, so it is important to
remove these files on a regular basis when they are no longer
needed, as long as appropriate backups have been made. The
expire_logs_days parameter enables automatic
binary log removal.

Default frequency 12:00:00

Default auto-close enabled no

Database May Not Be Portable Due To Identifier Case Sensitivity

The case sensitivity of the underlying operating system
determines the case sensitivity of database and table names. If
you are using MySQL on only one platform, you don't normally
have to worry about this. However, depending on how you have
configured your server you may encounter difficulties if you
want to transfer tables between platforms that differ in
filesystem case sensitivity.

Default frequency 06:00:00

Default auto-close enabled no

Default Value Being Used For max_prepared_stmt_count

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 consume memory in the MySQL server
until they are closed, so it is important to use them properly
and to limit the number of statements that can be open at any
one time. The default value for max_prepared_stmt_count may not
be appropriate for your application and environment.

Default frequency 06:00:00

Default auto-close enabled no

Event Scheduler Disabled

The Event Scheduler is a very useful feature when enabled. It is
a framework for executing SQL commands at specific times or at
regular intervals. Conceptually, it is similar to the idea of
the Unix crontab (also known as a "cron job") or the Windows
Task Scheduler.

The basics of its architecture are simple. An event is a stored
routine with a starting date and time, and a recurring tag. Once
defined and activated, it will run when requested. Unlike
triggers, events are not linked to specific table operations,
but to dates and times. Using the event scheduler, the database
administrator can perform recurring events with minimal hassle.
Common uses are the cleanup of obsolete data, the creation of
summary tables for statistics, and monitoring of server
performance and usage.

Default frequency 00:05:00

Default auto-close enabled no

General Query Log Enabled

The general query log is a general record of what mysqld is
doing. The server writes information to this log when clients
connect or disconnect, and it logs each SQL statement received
from clients. The general query log can be very useful when you
suspect an error in a client and want to know exactly what the
client sent to mysqld.

However, the general query log should not be enabled in
production environments because:

It adds overhead to the server;

It logs statements in the order they were received, not the
order they were executed, so it is not reliable for
backup/recovery;

It grows fast and can use a lot of disk space;

You cannot stop logging to the general query log without
stopping the server (for versions previous to 5.1).

You should use the binary log instead.

Default frequency 06:00:00

Default auto-close enabled no

Host Cache Size Not Sufficient

The MySQL server maintains a host cache in memory that contains
IP address, host name, and error information about clients. It
uses the host cache for several purposes:

By caching the results of IP-to-host name lookups, the
server avoids doing a DNS lookup for each client connection,
thereby improving performance.

The cache contains information about errors that occur
during the connection process. Some errors are considered
"blocking." If too many of these occur successively from a
given host without a successful connection, the server
blocks further connections from that host.

If the host cache is not large enough to handle all the
hosts from which clients may connect, performance may suffer
and you may lose information about client connection errors.

Default frequency 00:05:00

Default auto-close enabled no

In-Memory Temporary Table Size Limited By Maximum Heap Table Size

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. For
performance reasons it is recommended to have most temporary
tables created in memory, and only create exceedingly large
temporary tables on disk.

Default frequency 06:00:00

Default auto-close enabled no

InnoDB Fast Shutdown Enabled

Once you use the InnoDB Plugin on a set of database files, care
must be taken to avoid crashes and corruptions when using those
files with an earlier version of InnoDB, as might happen by
opening the database with MySQL when the plugin is not
installed. It is strongly recommended that
you use a "slow shutdown" (SET GLOBAL
innodb_fast_shutdown=0) when stopping the MySQL server
when the InnoDB Plugin is enabled. This will ensure log files
and other system information written by the plugin will not
cause problems when using a prior version of InnoDB.

The reason "slow" shutdown (innodb_fast_shutdown=0) is
recommended is because the InnoDB Plugin may write special
records to the transaction undo log that will cause problems if
the built-in InnoDB in MySQL attempts to read the log.
Specifically, these special records will be written when a
record in a COMPRESSED or DYNAMIC table is updated or deleted
and the record contains columns stored off-page. The built-in
InnoDB in MySQL cannot read these undo log records. Furthermore,
the built-in InnoDB in MySQL will fail to roll back incomplete
transactions that affect tables that it is unable to read
(tables in COMPRESSED or DYNAMIC format).

Also note that a "normal" shutdown will not necessarily empty
the undo log. A normal shutdown occurs when
innodb_fast_shutdown=1, the default. When InnoDB is shut down,
some active transactions may have uncommitted modifications, or
they may be holding a read view that prevents the purging of
some version information from the undo log. The next time InnoDB
is started after a normal shutdown (innodb_fast_shutdown=1), it
will roll back any incomplete transactions and purge old version
information. Therefore, it is important to perform a "slow"
shutdown (innodb_fast_shutdown=0) as part of the downgrade
process.

Default frequency 12:00:00

Default auto-close enabled no

InnoDB File Format Check Disabled Or Incorrect

To prevent possible crashes or data corruptions when the InnoDB
Plugin opens an ib-file set, it will check that it can fully
support the file formats in use within the ib-file set. If the
system is restarted following a crash, or a "fast shutdown"
(i.e., innodb_fast_shutdown is greater than zero), there may be
on-disk data structures (such as redo or undo entries, or
doublewrite pages) that are in a "too-new" format for the
current software. During the recovery process, serious damage
can be done to your data files if these data structures are
accessed. The startup check of the file format occurs before any
recovery process begins, thereby preventing the problems
described in the
"Possible
Problems" section of the InnoDB Plugin documentation.

Setting innodb_file_format_check to OFF, or to a different
format than the one in use, is very dangerous, as it permits the
recovery process to run, possibly corrupting your database if
the previous shutdown was a crash or "fast shutdown". You should
only set innodb_file_format_check in this manner if you are sure
that the previous shutdown was done with innodb_fast_shutdown=0,
so that essentially no recovery process will occur.

Default frequency 12:00:00

Default auto-close enabled no

InnoDB INFORMATION_SCHEMA Plugins Missing

Several INFORMATION_SCHEMA tables -- INNODB_CMP, INNODB_CMPMEM,
INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS -- contain live
information about compressed InnoDB tables, the compressed
InnoDB buffer pool, all transactions currently executing inside
InnoDB, the locks that transactions hold and those that are
blocking transactions waiting for access to a resource (a table
or row). These tables are very useful for monitoring the
activity and performance of the InnoDB Plugin storage engine.

However, these INFORMATION_SCHEMA tables are themselves plugins
to the MySQL server. As such, they need to be installed as
described in the InnoDB Plugin User's Guide. If they are not
installed, you will not be able to use them to monitor the
InnoDB storage engine.

Default frequency 12:00:00

Default auto-close enabled no

InnoDB Transaction Logs Not Sized Correctly

To avoid frequent checkpoint activity and reduce overall
physical I/O, which can slow down write-heavy systems, the
InnoDB transaction logs should be approximately 50-100% of the
size of the InnoDB buffer pool, depending on the size of the
buffer pool.

Default frequency 06:00:00

Default auto-close enabled no

InnoDB Status Truncation Detected

InnoDB primarily uses the SHOW ENGINE INNODB STATUS command to
dump diagnostics information. As this SHOW statement can output
a lot of data when running in a system with very many concurrent
sessions, the output is limited to 64 kilobytes in versions <
5.5.7, and 1 megabyte on versions greater than 5.5.7. You are
running a version where the truncation limit should be 1
megabyte, however truncation is still occurring in your system,
and the MEM Agent relies on this output to pass back a number of
key InnoDB statistics.

However, InnoDB provides a startup option called
innodb_status_file, which dumps the same output as SHOW ENGINE
INNODB STATUS to a file called innodb_status.<mysql pid>
in the datadir. The MEM Agent (in versions > 2.3.0) will read
this file automatically if it exists before executing the SHOW
statement.

Default frequency 00:05:00

Default auto-close enabled no

InnoDB Strict Mode Is Off

To guard against ignored typos and syntax errors in SQL, or
other unintended consequences of various combinations of
operational modes and SQL commands, the InnoDB Plugin provides a
"strict mode" of operations. In this mode, InnoDB will raise
error conditions in certain cases, rather than issue a warning
and process the specified command (perhaps with some unintended
defaults). This is analogous to MySQL's sql_mode, which controls
what SQL syntax MySQL will accept, and determines whether it
will silently ignore errors, or validate input syntax and data
values.

Using the new clauses and settings for ROW_FORMAT and
KEY_BLOCK_SIZE on CREATE TABLE and ALTER TABLE commands and the
CREATE INDEX command can be confusing when not running in strict
mode. Unless you run in strict mode, InnoDB will ignore certain
syntax errors and will create the table or index, with only a
warning in the message log. However if InnoDB strict mode is on,
such errors will generate an immediate error and the table or
index will not be created, thus saving time by catching the
error at the time the command is issued.

Default frequency 12:00:00

Default auto-close enabled no

InnoDB Tablespace Cannot Automatically Expand

If the InnoDB tablespace is not allowed to automatically grow to
meet incoming data demands and your application generates more
data than there is room for, out-of-space errors will occur and
your application may experience problems.

Default frequency 06:00:00

Default auto-close enabled no

Key Buffer Size Greater Than 4 GB

To minimize disk I/O, the MyISAM storage engine employs a key
cache (or key buffer) to keep the most frequently accessed index
blocks in memory. However, prior to MySQL version 5.0.52 this
key buffer is limited in size to 4 GB, even on 64-bit
operating systems. If set to a larger value, mysqld
may crash when it tries to increase the actual buffer beyond 4
GB.

Note

key_buffer_size remains limited to 4GB on
64-bit Windows systems until MySQL version 5.1.31.

Default frequency 06:00:00

Default auto-close enabled no

Multiple Threads Used When Repairing MyISAM Tables

Using multiple threads when repairing MyISAM tables can improve
performance, but it can also lead to table and index corruption.

Default frequency 06:00:00

Default auto-close enabled no

MySQL Server No Longer Eligible For Oracle Premier Support

To ensure you are running versions of MySQL which are still
covered by their support contracts, this advisor checks for
MySQL versions which are no longer eligible for Premier support
cover. Specifically, for versions 5.1 and 5.5.

The default thresholds are defined in a numeric format, where
version 5.5 is represented as 50500 (Notice threshold), and 5.1
as 50100 (warning threshold).

Default frequency 06:00:00

Default auto-close enabled no

Next-Key Locking Disabled For InnoDB But Binary Logging Enabled

Next-key locking in InnoDB can be disabled, which may improve
performance in some situations. However, this may result in
inconsistent data when recovering from the binary logs in
replication or recovery situations. Starting from MySQL 5.0.2,
this option is even more unsafe than it was in version 4.1.x.

Default frequency 06:00:00

Default auto-close enabled no

No Value Set For MyISAM Recover Options

The myisam-recover-options option (named
myisam-recover before MySQL 5.5.3) enables
automatic MyISAM crash recovery should a MyISAM table become
corrupt for some reason. If this option is not set, then a table
will be "Marked as crashed" if it becomes corrupt, and no
sessions will be able to SELECT from it, or perform any sort of
DML against it.

Default frequency 06:00:00

Default auto-close enabled no

Query Cache Not Available

MySQL can cache the results of SELECT statements in memory so
that they do not have to constantly be parsed and executed. If
your application often runs the same queries over and over,
caching the results can increase performance significantly. It's
important to use a version or binary of MySQL that supports the
query cache.

Default frequency 06:00:00

Default auto-close enabled no

Table Cache Set Too Low For Startup

The table cache size controls the number of open tables that can
occur at any one time on the server. MySQL will work to open and
close tables as needed, however you should avoid having the
table cache set too low, causing MySQL to constantly open and
close tables to satisfy object access.

If the table cache limit has been exceeded by the number of
tables opened in the first three hours of service, then the
table cache size is likely set too low.

Default frequency 00:30:00

Default auto-close enabled no

Time Zone Data Not Loaded

The MySQL server supports multiple time zones and provides
various date and time functions, including a function that
converts a datetime value from one time zone to another
(CONVERT_TZ). However, while the MySQL installation procedure
creates the time zone tables in the mysql database, it does not
load them; you must do so manually after installation. If the
time zone tables are not loaded, certain time zone functions
such as CONVERT_TZ will not work.

Default frequency 12:00:00

Default auto-close enabled no

Warnings Not Being Logged

Error conditions encountered by a MySQL server are always logged
in the error log, but warning conditions are only logged if
log_warnings is set to a value greater than
0. If warnings are not logged you will not get valuable
information about aborted connections and various other
communication errors. This is especially important if you use
replication so you get more information about what is happening,
such as messages about network failures and reconnection.

Default frequency 12:00:00

Default auto-close enabled no

XA Distributed Transaction Support Enabled For InnoDB

XA Distributed Transaction support is turned on by default. If
you are not using this feature, note that it adds an extra fsync
for each transaction and may adversely affect performance.