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:

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:

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

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.

Default frequency 06:00:00

Default auto-close enabled no