Table of Contents
This appendix contains reference information for MySQL Enterprise Monitor.
The following list provides information on known limitations within MySQL Enterprise Monitor
The maximum size for data stored within the
mysql.inventory
table is 64 bytes.
The MySQL Enterprise Monitor Agent when operating for Query Analyzer as a proxy cannot handle queries greater than 16MB.
The MySQL Enterprise Monitor Agent when operating for Query Analyzer as a proxy does not support clients using the MySQL protocol older than MySQL 5.0.
The MySQL Enterprise Monitor Agent when operating for Query Analyzer as a proxy does not support clients affects the authentication of clients. As the proxy is the real client connecting to the backend MySQL server, authentication should use the hostname of the proxy, not the original client.
When viewing the graphs generated by MySQL Enterprise Service Manager, the fonts used for the graphs may not be displayed properly if the required font is missing.
You can specify a custom font by setting a custom property
within the embedded MySQL Server to a known working font. To
do this, connect to your embedded MySQL sever using the
information provided by the
configuration_report.txt
file. Once
connected, execute the following statment:
mysql> INSERT INTO map_entries VALUES (1,'Helvetica','graph.font');
Replacing Helvetica
with the font you want
to use.
The font used for graph rendering will be selected as follows, in this order:
The user override value.
The MySQL Enterprise Service Manager default of Arial.
The graph engine default of SansSerif.
It is not possible to monitor more than one MySQL Enterprise Dashboard instance hosted on the same machine simultaneously within the same browser, even if each MySQL Enterprise Dashboard uses a different port for access. The effect of this limitation may be more significant on certain browsers.
When monitoring multiple MySQL instances with one MySQL Enterprise Monitor Agent, if any of the MySQL instances are down at the point when the agent is started, the agent will not attempt to reconnect to the servers. This could lead to indications that MySQL instances were down when in fact they were running and available. To avoid this problem, ensure all of the monitored instances are available before starting the agent.
The MySQL Enterprise Dashboard is known to work within the following browser environments:
Microsoft Internet Explorer 7.x, 8.x
MySQL Enterprise Dashboard is not compatible with the Compatibility View option within Internet Explorer 8.x. Please make sure this option is disabled before using MySQL Enterprise Dashboard
Safari 3.2, 4.x
Firefox 3.0, 3.5
The MySQL Enterprise Dashboard is known not to work within the following browser environments:
Microsoft Internet Explorer 6.x
Opera
Google Chrome
The Service Manager is available for Windows, Mac OS X, and a variety of Unix and Linux operating systems. In most cases, the standard operating system and packages are supported without further libraries and modules. All the required components are installed automatically.
Mac OS X Notes
The Mac OS X Service Manager is only supported on Intel architecture. However, the Mac OS X agent is supported for both Intel and the PowerPC.
Installation requires approximately 450MB of disk space for MySQL Enterprise Service Manager
Windows Notes
Installation requires approximately 260MB of disk space for MySQL Enterprise Service Manager
Unix/Linux Notes
Installation requires approximately 450MB of disk space for MySQL Enterprise Service Manager
On FreeBSD, you must have bind8
installed.
Disk-space usage requirements are approximate for the base application. During usage, the service manager records detailed information about your MySQL servers and environment, and disk space usage will increase with time to record this historical data. You can control how long information is stored once the service manager has been installed.
The minimum recommended requirements for the service manager are at least a 2GHz CPU, with two or more CPU cores, and at least 2GB of RAM. If you are monitoring a large number of services, then there will be an increased load on the server manager. Running the service manager on a machine that is already running other tasks is only recommended if you are monitoring a small number of agents. For monitoring five or more agents simultaneously, you should dedicate a machine to the process. For more information, see Section F.5, “Choosing Suitable MySQL Enterprise Service Manager Hardware Configurations”.
The Monitor Agent is available for a wide range of operating systems. For an up-to-date list please see the http://www.mysql.com/products/enterprise/. The agent can be used to monitor any MySQL server from version 4.0.x through 6.0.x.
Production support is not currently available for MySQL Enterprise Monitor (Service Manager component) in virtualized environments.
The SSL certificate that is provided with the server during installation is a sample certificate only. If you want to use SSL for communicating with your MySQL Enterprise Service Manager and MySQL Enterprise Dashboard you should replace the supplied certificate with your own certificate.
The certificate keystore is located in the
apache-tomcat/conf/myKeyStore
file within
your MySQL Enterprise Service Manager installation.
To create a new, self-signed, certificate, use the keytool command (provided with Java SE Security) to create a new certificate file:
shell> keytool -genkey -alias tomcat -keyalg RSA -validity 1825 -keystore newKeystore
You will be prompted to fill in the certificate information and to provide a unique password for the certificate.
The -validity
specifies the length of the
certificate created, specified in days. The
-alias
is required to ensure that the
certificate is associated with tomcat
.
Once you have created the new keystore, you should shutdown
MySQL Enterprise Service Manager, copy the new keystore file to
apache-tomcat/conf/myKeyStore
and restart
MySQL Enterprise Service Manager.
If you have an existing certificate that you would like to import into your keystore, you need to import the certificate using the following command:
shell> keytool -import -alias tomcat -keystore newKeystore -file public.pem
For more information on using SSL certificates in Tomcat see Apache Tomcat 6.0: SSL Configuration HOW-TO.
Running MySQL Enterprise Service Manager places a reasonable load on your system, and this load increases linearly as you add more agents monitoring more servers. Ideally, you should use a dedicated machine for MySQL Enterprise Service Manager, rather than running it alongside other applications and services.
Minimum System Requirements
2 or more CPU cores
2 or more GB of RAM
Disk I/O subsystem applicable for a write-intensive database
Recommended System Requirements (if monitoring 100 or more MySQL servers)
4 or more CPU cores
8 or more GB of RAM
Disk I/O subsystem applicable for a write-intensive database (RAID10, RAID 0+1)
The MySQL Enterprise Monitor Agent supports the following configurable options:
Table F.1. mysql-monitor-agent Help Options
Format | Config File | Description | Introduction | Deprecated | Removed |
---|---|---|---|---|---|
--help | help | Show help options | 2.0.0 | ||
--help-admin | help-admin | Show options for the admin-module | 2.0.0 | ||
--help-agent | help-agent | Show agent options | 2.0.0 | ||
--help-all | help-all | Show all help options | 2.0.0 | ||
--help-monitor | help-monitor | Show monitor options | 2.0.0 | ||
--help-proxy | help-proxy | Show options for the proxy-module | 2.0.0 |
Table F.2. mysql-monitor-agent Admin Options
Format | Config File | Description | Introduction | Deprecated | Removed |
---|---|---|---|---|---|
--admin-address | admin-address | Defines the listening address and poirt for the admin module | 2.0.0 | ||
--admin-lua-script | admin-lua-script | Script to execute by the admin module | 2.0.0 | ||
--admin-password | admin-password | Password for authentication for admin module | 2.0.0 | ||
--admin-username | admin-username | Username for authentication for admin module | 2.0.0 | ||
--proxy-address | proxy-address | Listening address:port of the proxy server | 2.0.0 |
Table F.3. mysql-monitor-agent Agent Options
Format | Config File | Description | Introduction | Deprecated | Removed |
---|---|---|---|---|---|
--agent-collector-plugins | agent-collector-plugins | Load the specified collector plugins | 2.0.0 | ||
--agent-generate-uuid | agent-generate-uuid | Generate a UUID for use with the agent-uuid | 2.0.0 | ||
--agent-heartbeat-interval | agent-heartbeat-interval | The interval for generating heartbeat operations | 2.0.0 | ||
--agent-host-id | agent-host-id | The host ID for the agent | 2.0.0 | ||
--agent-host-id-commandline | agent-host-id-commandline | The command to use to generate the agent host id | 2.0.0 | ||
--agent-hostname | agent-hostname | Hostname of the agent host | 2.0.0 | ||
--agent-item-files | agent-item-files | List of data items for additional collections | 2.0.0 | ||
--agent-mgmt-hostname | agent-mgmt-hostname | URL of the management server | 2.0.0 | ||
--agent-run-os-tests | agent-run-os-tests | Run the operating system tests and shutdown | 2.0.0 | ||
--agent-sync-attempts | agent-sync-attempts | Synchronize the attempts to connect at resync | 2.0.0 | ||
--agent-track-alloc | agent-track-alloc | Enable the tracking of the allocation sizes | 2.0.0 | ||
--agent-uuid | agent-uuid | UUID of this agent | 2.0.0 |
Table F.4. mysql-monitor-agent Monitor Options
Format | Config File | Description | Introduction | Deprecated | Removed |
---|---|---|---|---|---|
--monitor-interval | monitor-interval | Interval for executing queries against the backend | 2.0.0 | ||
--monitor-lua-script | monitor-lua-script | Script filename for the monitor | 2.0.0 | ||
--monitor-password | monitor-password | Set the password for the monitored MySQL Server | 2.0.0 | ||
--monitor-username | monitor-username | Set the username for the monitored MySQL Server | 2.0.0 |
Table F.5. mysql-monitor-agent Proxy Options
Format | Config File | Description | Introduction | Deprecated | Removed |
---|---|---|---|---|---|
--no-proxy | no-proxy | Don't start the proxy module | 2.0.0 | ||
--proxy-backend-addresses | proxy-backend-addresses | Address:port of the remote MySQL server | 2.0.0 | ||
--proxy-fix-bug-25371 | proxy-fix-bug-25371 | Enable the fix for Bug#25371 (for mysqld > 2.0.0.12) for older libmysql versions | 2.0.0 | ||
--proxy-lua-script | proxy-lua-script | Filename for Lua script for proxy operations | 2.0.0 | ||
--proxy-pool-no-change-user | proxy-pool-no-change-user | Don't use the protocol CHANGE_USER to reset the connection when coming from the connection pool | 2.0.0 | ||
--proxy-read-only-backend-addresses | proxy-read-only-backend-addresses | Address:port of the remote MySQL server (read only) | 2.0.0 | ||
--proxy-skip-profiling | proxy-skip-profiling | Disabled profiling of queries | 2.0.0 |
Table F.6. mysql-monitor-agent Application Options
Format | Config File | Description | Introduction | Deprecated | Removed |
---|---|---|---|---|---|
--basedir | basedir | Specify the base directory to prepend to paths in the config | 2.0.0 | ||
--daemon | daemon | Start in daemon-mode | 2.0.0 | ||
--defaults-file | defaults-file | Specify the configuration file | 2.0.0 | ||
--keepalive | keepalive | Try to restart the proxy if a crash occurs | 2.0.0 | ||
--log-backtrace-on-crash | log-backtrace-on-crash | Try to invoke the debug and generate a backtrace on crash | 2.0.0 | ||
--log-file | log-file | Specify the file for logging error messages | 2.0.0 | ||
--log-level | log-level | Logging level | 2.0.0 | ||
--log-use-syslog | log-use-syslog | Log errors to syslog | 2.0.0 | ||
--lua-cpath | lua-cpath | Set the LUA_CPATH | 2.0.0 | ||
--lua-path | lua-path | Set the LUA_PATH | 2.0.0 | ||
--max-open-files | max-open-files | Specify the maximum number of open files to support | 2.0.0 | ||
--pid-file | pid-file | PID file to store the process ID (when in daemon mode) | 2.0.0 | ||
--plugin-dir | plugin-dir | Path to the plugin files | 2.0.0 | ||
--plugins | plugins | List of plugins to load | 2.0.0 | ||
--user | user | Specify the user to use when running mysql-monitor-agent | 2.0.0 | ||
--version | version | Show the version information | 2.0.0 |
Version Introduced | 2.0.0 | ||
Command-Line Format | --help | ||
-? |
Show available help options.
Version Introduced | 2.0.0 | ||
Command-Line Format | --help-all |
Show all help options.
Version Introduced | 2.0.0 | ||
Command-Line Format | --help-admin |
Show options for the admin-module.
Version Introduced | 2.0.0 | ||
Command-Line Format | --help-proxy |
Show options for the proxy-module.
Version Introduced | 2.0.0 | ||
Command-Line Format | --help-monitor |
Show options for the monitor module.
Version Introduced | 2.0.0 | ||
Command-Line Format | --help-agent |
Show options for the agent module.
Version Introduced | 2.0.0 | ||
Command-Line Format | --admin-address | ||
Config-File Format | admin-address | ||
Permitted Values | |||
Type | string | ||
Default | :4041 |
Specify the host name (or IP address) and port for the
administration port. The default is
localhost:4041
.
Version Introduced | 2.0.0 | ||
Command-Line Format | --admin-lua-script | ||
Config-File Format | admin-lua-script | ||
Permitted Values | |||
Type | filename | ||
Default |
|
Specify the script to use for the administration module for the proxy.
Version Introduced | 2.0.0 | ||
Command-Line Format | --admin-password | ||
Config-File Format | admin-password | ||
Permitted Values | |||
Type | string | ||
Default |
|
Define the password to use to authenticate users wanting to connect to the administration module of mysql-monitor-agent. The administration module uses the MySQL protocol to request a username and password for connections.
Version Introduced | 2.0.0 | ||
Command-Line Format | --admin-username | ||
Config-File Format | admin-username | ||
Permitted Values | |||
Type | string | ||
Default | root |
Define the username to use to authenticate users wanting to
connect to the administration module of
mysql-monitor-agent. The default username
is root
. The administration module uses the
MySQL protocol to request a username and password for
connections.
--agent-collector-plugins=user
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-collector-plugins | ||
Config-File Format | agent-collector-plugins | ||
Permitted Values | |||
Type | numeric | ||
Default | collect_lua |
A comma-separated list of the plugins to be used and loaded by the agent and used to provide collector items.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-generate-uuid | ||
Config-File Format | agent-generate-uuid |
Regenerates a UUID for the agent. You can use this to generate a new UUID if you have copied configuration information for an agent from one machine to another. To use:
shell> /opt/mysql/enterprise/agent/bin/mysql-monitor-agent --agent-generate-uuid ee9296d7-f7cd-4fee-8b26-ead884ebf398 2009-03-05 11:49:37: (critical) shutting down normally
For more information, see Section 2.3.6.3, “Monitoring Multiple MySQL Servers”.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-heartbeat-interval | ||
Config-File Format | agent-heartbeat-interval | ||
Permitted Values | |||
Type | numeric | ||
Default | 3 |
Specify the heartbeat interval. The agent sends heartbeat messages to MySQL Enterprise Service Manager to indicate that it is available and still running and monitoring servers. The MySQL Enterprise Service Manager uses this information to help identify when an agent is available or not.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-host-id | ||
Config-File Format | agent-host-id |
Define the agent host id. The host id should be a unique identifier for the host on which the agent is running, and is different to the agent UUID. You can override the host ID for use within HA environments where you have two identical agent configurations, one on a live server, and one on a backup server to be brought into use if the primary server fails. The host ID is used to identify the host by the server, and during failover you want the host ID to remain constant as you monitor either the original or failover (replacement) server.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-host-id-commandline | ||
Config-File Format | agent-host-id-commandline |
Specify the command line to generate the host ID. By default, the host ID is generated by using a variety of methods configured for a specific platform, including using SSH keys and embedded host ID information. You can replace this with an alternative command-line
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-hostname | ||
Config-File Format | agent-hostname |
Override the automatically determined hostname. You can use this to specify an alternate hostname for the agent. This can be useful if your host has multiple hostnames and you want the machine identified by a specific hostname.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-item-files | ||
Config-File Format | agent-item-files |
A comma separated list of the item files used to load data items that are then collected and sent to MySQL Enterprise Service Manager. For more information, see Section 5.4.8, “Creating a Custom Data Collection Item”.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-mgmt-hostname | ||
Config-File Format | agent-mgmt-hostname |
Set the URL of the MySQL Enterprise Service Manager where collected data is
sent. The URL should include the username and password for the
agents, for example:
http://agent:password@memserver:18080/heartbeat
.
If you have the http_proxy
environment
variable configured within your environment, you should add
the value of agent-mgmt-hostname
to the
no_proxy
variable to ensure that data is
correctly transmitted to MySQL Enterprise Service Manager and not redirected
through the configured proxy address.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-run-os-tests | ||
Config-File Format | agent-run-os-tests |
Runs the internal operating system tests, and then exits. Using this option will generate a large body of information about the various operating system information that can be collected about the host. You can use this to determine whether the agent is collecting the right amount, quantity and quality of information to the server.
A truncated sample of the output is provided below:
2010-01-22 16:15:45: (critical) MySQL Monitor Agent 2.1.1.1138 started. sigar-test-all.c.128 (): pid = 1353 sigar-test-all.c.110 (): mem.ram = 6080, mem.total = 6374154240, mem.used = 3356090368, mem.free = 3018063872, mem.actual_free = 3018063872, mem.actual_used = 3356090368 sigar-test-all.c.143 (): swap.total = 28523896832, swap.used = 710623232, swap.free = 27813273600 sigar-test-all.c.169 (): cpu.user = 24544920, cpu.sys = 136764840, cpu.nice = 0, cpu.idle = 1234759920, cpu.wait = 0, cpu.total = 349015500 sigar-test-all.c.194 (): [0] cpu.user = 8516770, cpu.sys = 56838970, cpu.nice = 0, cpu.idle = 283667220, cpu.wait = 0, cpu.total = 349022960 [1] cpu.user = 6130420, cpu.sys = 12671090, cpu.nice = 0, ...
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-sync-attempts | ||
Config-File Format | agent-sync-attempts |
Attempt to synchronise with the server during the resynchronization.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-track-alloc | ||
Config-File Format | agent-track-alloc |
Track the memory allocation in the various modules of the agent to help monitor the memory usage.
Version Introduced | 2.0.0 | ||
Command-Line Format | --agent-uuid | ||
Config-File Format | agent-uuid | ||
Permitted Values | |||
Type | string |
Specify the agent UUID. A UUID is automatically generated for
each agent during installation, with the UUID automatically
added to the configuration. You can generate a new UUID using
the agent-generate-uuid
command line option.
Version Introduced | 2.0.0 | ||
Command-Line Format | --basedir | ||
Config-File Format | basedir | ||
Permitted Values | |||
Type | filename |
Specify a base directory that will be prepended to all other filename configuration options. The base name should be an absolute (not relative) directory. If you specify a relative directory, mysql-monitor-agent will generate an error during startup.
Version Introduced | 2.0.0 | ||
Command-Line Format | --defaults-file | ||
Config-File Format | defaults-file |
Specify a file to use as the file with configuration information. If not specified, configuration options are only taken from the command line.
Command-Line Format | --event-threads | ||
Config-File Format | event-threads | ||
Permitted Values | |||
Type | numeric | ||
Default | 1 |
Specify the number of event threads reserved to handle incoming requests.
Version Introduced | 2.0.0 | ||
Command-Line Format | --keepalive | ||
Config-File Format | keepalive |
Creates a process surrounding the main mysql-monitor-agent which will attempt to restart the true mysql-monitor-agent process in thr event a crash or other failure.
Version Introduced | 2.0.0 | ||
Command-Line Format | --log-backtrace-on-crash | ||
Config-File Format | log-backtrace-on-crash |
Logs the backtrace to the error log and tries to initialize the debugger in the event of a failure.
Version Introduced | 2.0.0 | ||
Command-Line Format | --log-file | ||
Config-File Format | log-file | ||
Permitted Values | |||
Type | filename |
Specify the name of a file to be used to record log information.
Version Introduced | 2.0.0 | ||
Command-Line Format | --log-level | ||
Config-File Format | log-level | ||
Permitted Values | |||
Type | enumeration | ||
Valid Values | error , warning , info , message , debug |
The log level to be used when outputting error messages. The
specification will output that level (or lower) of a given
error message. For example, message
will
also output info
,
warning
, and error
messages.
Version Introduced | 2.0.0 | ||
Command-Line Format | --log-use-syslog | ||
Config-File Format | log-use-syslog |
Causes errors to be sent to the syslog (Unix/Linux only).
Version Introduced | 2.0.0 | ||
Command-Line Format | --lua-cpath | ||
Config-File Format | lua-cpath | ||
Permitted Values | |||
Type | filename |
Specify the LUA_CPATH
to be used when
loading compiled modules or libraries for Lua scripts.
Version Introduced | 2.0.0 | ||
Command-Line Format | --lua-path | ||
Config-File Format | lua-path | ||
Permitted Values | |||
Type | filename |
Specify the LUA_CPATH
to be used when
loading modules for Lua.
Version Introduced | 2.0.0 | ||
Command-Line Format | --max-open-files | ||
Config-File Format | max-open-files | ||
Permitted Values | |||
Type | numeric |
The maximum number of open files and sockets supported by the mysql-monitor-agent process. You may need to increase this with certain scripts.
Version Introduced | 2.0.0 | ||
Command-Line Format | --monitor-interval | ||
Config-File Format | monitor-interval | ||
Permitted Values | |||
Type | numeric | ||
Default | 1 | ||
Min Value | 1 |
Execute queries against the backends at this interval. The default is 1.
Version Introduced | 2.0.0 | ||
Command-Line Format | --monitor-lua-script | ||
Config-File Format | monitor-lua-script | ||
Permitted Values | |||
Type | string |
Filename of the Lua script to use for the monitor module.
Version Introduced | 2.0.0 | ||
Command-Line Format | --monitor-password | ||
Config-File Format | monitor-password | ||
Permitted Values | |||
Type | string |
The password for the monitoring user account.
Version Introduced | 2.0.0 | ||
Command-Line Format | --monitor-username | ||
Config-File Format | monitor-username | ||
Permitted Values | |||
Type | string | ||
Default | monitor |
The username for the monitoring user account.
Version Introduced | 2.0.0 | ||
Command-Line Format | --no-proxy | ||
Config-File Format | no-proxy |
Disable the proxy module.
Version Introduced | 2.0.0 | ||
Command-Line Format | --plugin-dir | ||
Config-File Format | plugin-dir | ||
Permitted Values | |||
Type | filename |
The directory to use when loading plugins for mysql-monitor-agent.
Version Introduced | 2.0.0 | ||
Command-Line Format | --plugins | ||
Config-File Format | plugins | ||
Permitted Values | |||
Type | string |
A comma-separated list of the plugins to be loaded.
Version Introduced | 2.0.0 | ||
Command-Line Format | --proxy-address | ||
-P | |||
Config-File Format | proxy-address | ||
Permitted Values (>= 2.0.0) | |||
Type | string | ||
Default | :4040 |
The listening host name (or IP address) and port of the proxy
server. The default is :4040
(all IPs on
port 4040).
--proxy-read-only-backend-addresses=host:port
Version Introduced | 2.0.0 | ||
Command-Line Format | --proxy-read-only-backend-addresses | ||
-r | |||
Config-File Format | proxy-read-only-backend-addresses | ||
Permitted Values | |||
Type | string |
The listening host name (or IP address) and port of the proxy server for read-only connections. The default is for this information not to be set.
--proxy-backend-addresses=host:port
The host name (or IP address) and port of the MySQL server to connect to. You can specify multiple backend servers by supplying multiple options. Clients are connected to each backend server in round-robin fashion. For example, if you specify two servers A and B, the first client connection will go to server A; the second client connection to server B and the third client connection to server A.
Version Introduced | 2.0.0 | ||
Command-Line Format | --proxy-pool-no-change-user | ||
Config-File Format | proxy-pool-no-change-user |
Disables the use of the MySQL protocol
CHANGE_USER
when reusing a connection from
the pool of connections specified by the
backend-addresses
list.
disables profiling of queries (tracking time statistics). The default is for tracking to be enabled.
Version Introduced | 2.0.0 | ||
Command-Line Format | --proxy-fix-bug-25371 | ||
Config-File Format | proxy-fix-bug-25371 |
Gets round an issue when connecting to a MySQL server later than 5.1.12 when using a MySQL client library of any earlier version.
Version Introduced | 2.0.0 | ||
Command-Line Format | --proxy-lua-script | ||
-s | |||
Config-File Format | proxy-lua-script | ||
Permitted Values | |||
Type | filename |
specify the Lua script file to be loaded. Note that the script file is not physically loaded and parsed until a connection is made. Also note that the specified Lua script is reloaded for each connection; if the content of the Lua script changes while mysql-monitor-agent is running then the updated content will automatically be used when a new connection is made.
Version Introduced | 2.0.0 | ||
Command-Line Format | --daemon | ||
Config-File Format | daemon |
Starts the proxy in daemon mode.
Version Introduced | 2.0.0 | ||
Command-Line Format | --pid-file | ||
Config-File Format | pid-file | ||
Permitted Values | |||
Type | filename |
Sets the name of the file to be used to store the process ID.
Version Introduced | 2.0.0 | ||
Command-Line Format | --user | ||
Config-File Format | user | ||
Permitted Values | |||
Type | string |
Run mysql-monitor-agent as the specified
user
.
Version Introduced | 2.0.0 | ||
Command-Line Format | --version | ||
-V | |||
Config-File Format | version |
Show the version number.
The parameters for the Tomcat hosting system used to support MySQL Enterprise Service Manager and MySQL Enterprise Dashboard can have an affect on the performance of the system.
The default settings for Java for Tomcat are as follows:
Initial heap size | -Xms | 768MB |
Maximum heap size | -Xmx | 768MB |
Java stack size | -Xss | 128MB |
You can change these parameters to higher values,by editing the
JAVA_OPTS
setting within the corresponding
platform script.
On Unix/Linux and Mac OS X
Edit the values within
apache-tomcat/bin/setenv.sh
file within
your MySQL Enterprise Service Manager directory. You should avoid setting
maximum figures higher than the physical memory of your system
as this may reduce, rather than increase the performace.
If you change these parameters, you will need to shutdown and restart MySQL Enterprise Service Manager for the changes to take effect.
Windows
Edit the JAVA_OPTS
settings within the
apache-tomcat/bin/setenv.bat
file.
To enable the changes, you must re-install your service. To do this, shutdown your MySQL Enterprise Service Manager service and then reinstall the service by running:
shell> mysqlmonitorctl.bat uninstall shell> mysqlmonitorctl.bat install
On all platforms, changes to the JAVA_OPTS
using the above methods should be retained over an upgrade of the
MySQL Enterprise Service Manager service.
If you change these parameters, you must restart MySQL Enterprise Service Manager for the changes to take effect.
If you want to backup the data stored within your MySQL Enterprise Service Manager,
you can use any of the typical backup solutions, such as
mysqldump
, to save your data. All you need to
backup the information is host name, user name and password
details that were set during the installation of the
MySQL Enterprise Service Manager
You can locate this information by examining the contents of the
configuration_report.txt
file that was
generated when MySQL Enterprise Service Manager was installed. A scample of the
file is provided below:
MySQL Enterprise Monitor (Version 2.0.0.7088 : 20081031_152749_r7088) Here are the settings you specified: Application hostname and port: http://127.0.0.1:18080 Tomcat Ports: 18080 - 18443 (SSL) MySQL Port : 13306 Repository Credentials (bundled MySQL): --------------------------------------- service_manager/Password Use the following command to login to the MySQL Enterprise Monitor database: mysql -uservice_manager -pPassword -P13306 -h127.0.0.1
The last line provides the information about how to connect to the server using the standard mysql command line client.
All the MySQL Enterprise Monitor repository information, including your
configuration, rule and historical data is stored within the
mem
database.
To backup this information using mysqldump
you
might use the following command:
shell> mysqldump --single-transaction » -uservice_manager -pPassword -P13306 -h127.0.0.1 mem >mem.dump
The above command would create a file,
mem.dump
, containing all of the MySQL Enterprise Monitor
data.
To ensure consistency in a recovery situation, you may also want to backup the agent configuration and metadata stored on each monitored MySQL server. To do this:
Backup the configuration files of each agent. You should keep
a copy of the etc
directory for each
agent. This directory contains the main configuration file,
mysql-monitor-agent.ini
, and the
configuration information for each server being monitored,
which is stored within the etc/instances
directory.
On each server being monitored, retain a copy of the
mysql.inventory
table, which contains the
unique ID of the MySQL server.
You can migrate the data generated during a MySQL Enterprise Monitor 1.3.x installation using the Data Migration functionality of the Server Configuration panel.
To use the data migration feature, you must have installed MySQL Enterprise Service Manager using an update installer. The update installer performs the initial migration of your configuration, rules, schedule, and events data. The historical data is not migrated until you explicitly request the migration of information within the Manage Servers section of the Settings panel.
Data migration works on a single server, allowing you to select on which servers you want to migrate information. The migration is subject to the following:
You must elect to migrate the data from each server individually.
Migration takes approximately 5-6 hours, for each month, for each server. Therefore, if you have six months of data on 10 servers it could take between 300 and 360 hours (15 days) to migrate all of your historical data one server at a time.
To limit the data migration, set the Data Purge Behavior within the Settings page. Only data more recent than the specified purge period will be migrated. Data older than the purge period will be ignored.
To prevent performance issues, migrate only one or a small number of servers concurrently.
You can start and stop the migration of the data at any time. As a general guide, you should avoid stopping the data migration process and allow it to complete unless:
Run out of disk space.
MySQL Enterprise Service Manager becomes too slow and unresponsive.
Migration never completes.
With the last item, where the migration never completes, occasionally there are some aspects of the data that cannot be migrated successfully. This will prevent the migration process completing, but does not affect the conversion of any data that could be migrated.
Starting Historical Data Migration
To start data migration:
Switch to the Manage Servers display of the Settings panel within MySQL Enterprise Dashboard.
Ensure that the data migration functionality has been enabled. The Historical Data Migration will be visible.
and buttons next to
Select the servers you want to migrate by using the checkbox
next to each server name. You can select one or more servers
to migrate. Servers that are suitable for migration will show
their migration status within the Migration
Status columnn. If the server is not able to be
migrated, N/A
will be shown.
Click Historical Data Migration.
next toYou will be presented with a confirmation dialog box. To start the migration, click
. To cancel migration, click .
The servers that have been selected for migration will show
Queued for Migration
in the
Migration Status column.
Monitoring Historical Data Migration
You can check the migration status of any individual server by examining the Migration Status column for each server. You can see an example of the migration status below.
Note that the migration status is shown according to the state of migration at the time the page was loaded. The actual migration continues in the background, and the current state may not match the state of the migration at the time it is viewed.
Servers showing Done
in the Migration
Status column have already completed their migration.
You can check the overall migration status by examining the Upgrade Status display.
Stopping Historical Data Migration
You can stop the migration process for any server that is still migrating data. The migration can be restarted at any time without causing any problems.
To stop the historical data migration:
Select the servers you want to stop migrating by using the checkbox next to each server name. You can select one or more servers to stop migrating.
Click Historical Data Migration.
next toConfirmation that the migration has been stopped will be provided. If migration has already completed, you will be notified.
Removing Old Data
Once data migration has been completed for all the servers you
want to migrate, you may want to delete or remove access to the
old data within your MySQL Enterprise Monitor repository. Data for MySQL Enterprise Monitor 1.3
was stored in a database called merlin
within
the MySQL repository. Data for MySQL Enterprise Monitor 2.0 is stored within a
database called mem
.
To create a backup of the old information, use mysqldump:
shell> mysqldump -uservice_manager -pPassword -P13306 -h127.0.0.1 merlin >data-1.3.sql
The above will create a file, data-1.3.sql
containg all of the MySQL Enterprise Monitor 1.3 information.
If you remove access to the old data, then the data migration
options for old servers will be removed from the Manage
Servers panel within MySQL Enterprise Service Manager. To remove access,
you need to REVOKE
access to the
merlin
database:
mysql& REVOKE ALL on merlin.* FROM 'service_manager';
Note that revoking access to the old data will not reclaim any of the disk space used by the old data.
To delete the data from the database and free up the space being
used by the historical information, DROP
the
merlin
database:
mysql& DROP DATABASE merlin;
Once all the data has been migrated you can hide the migration user interface by clicking the
button.MySQL Enterprise Monitor is generally self managing and does not need excessive maintenance. You should, however, be aware of certain maintenance tasks that you can automate or will need to manually perform to keep your MySQL Enterprise Monitor running efficiently.
Make sure you have set the purge interval for your data to an appropriate value according to duration and history of data that you want to keep. For more information, see Data Purge Behavior.
Check, and delete, the contents of the temporary directory with your MySQL Enterprise Service Manager installation directory.
Basic
Advisors
Graphs
Silver
Advisors
CREATE TABLE LIKE Does Not Require Any Privileges On Source Table
Database May Not Be Portable Due To Identifier Case Sensitivity
Excessive Percentage Of Attempted Connections To The Server Have Failed
Improper key_cache_block_size Setting Can Corrupt MyISAM Tables
In-Memory Temporary Table Size Limited By Maximum Heap Table Size
Malformed Password Packet In Connection Protocol Can Crash Server
Next-Key Locking Disabled For InnoDB But Binary Logging Enabled
Non-Authorized User Has DB, Table, Or Index Privileges On All Databases
Stored Routine Runs In Definer''s Rather Than Caller''s Security Context
Use Of View Overrides Column Update Privileges On Underlying Table
User Can Gain Privileges By Running Stored Routine Declared Using SQL SECURITY INVOKER
User With Only ALTER Privilege On Partitioned Table Can Obtain SELECT Privilege Information
Graphs
Gold
Advisors
Graphs
Platinum
Advisors
Graphs
Individual Advisor information is show below.
32-Bit Binary Running on 64-Bit AMD Or Intel System
The chip architecture and operating system installed on a machine both impact the performance of software running on the system. While it is possible to run 32-bit software on many 64-bit systems, in general, software built to run on a 64-bit system will run better on such a system than software built to run on a 32-bit system.
Default frequency 06:00:00
Account Has An Overly Broad Host Specifier
The MySQL server has user accounts with overly broad host specifiers. A MySQL account is identified by both a username and a hostname, which are found in the User and Host columns of the mysql.user table. The User value is the name that a client must supply when connecting to the server. The Host value indicates the host or hosts from which the user is allowed to connect. If this is a literal hostname, the account is limited to connections only from that host. If the hostname contains the '%' wildcard character, the user can connect from any host that matches the wildcard character and potentially from any host at all.
From a security standpoint, literal host values are best and
%
is worst. Accounts that have Host
values containing wildcards are more susceptible to attack
than accounts with literal host values, because attackers can
attempt to connect from a broader range of machines.
For example, if an account has user and host values of
root
and %
, it means
that you can connect as the root user from
any
machine if you know the password. By
contrast, if the host name is localhost
or
127.0.0.1
, the attacker can only attempt to
connect as the root user from the server host.
Default frequency 00:05:00
A MySQL server may have user accounts with privileges on all
databases and tables (*.*). In most cases global privileges
should be allowed only for the MySQL root user, and possibly
for users that you trust or use for backup purposes. Global
privileges such as DROP, ALTER, DELETE, UPDATE,
INSERT,
and LOCK TABLES
may be
dangerous as they may cause other users to be affected
adversely.
Default frequency 00:05:00
Account Has Old Insecure Password Hash
Prior to MySQL 4.1, password hashes computed by the PASSWORD() function were 16 bytes long. As of MySQL 4.1 (and later), PASSWORD() was modified to produce a longer 41-byte hash value to provide enhanced security.
Default frequency 06:00:00
Account Has Strong MySQL Privileges
Certain account privileges can be dangerous and should only be granted to trusted users when necessary. For example, the FILE privilege allows a user to read and write files on the database server (which includes sensitive operating system files), the PROCESS privilege allows currently executing statements to be monitored, and the SHUTDOWN privilege allows a user to shut down the server. In addition, the GRANT privilege allows a user to grant privileges to others.
Default frequency 00:05:00
Attempted Connections To The Server Have Failed
Aborted connection attempts to MySQL may indicate an issue with respect to the server or network, or could be indicative of DoS or password-cracking attempts against the MySQL Server. The aborted-connects count is incremented when:
A client does not have privileges to access a database
A client uses the wrong password
A malformed packet is received
The connect_timeout variable is exceeded
Default frequency 00:05:00
Binary Log File Count Exceeds Specified Limit
The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables replication as well as point-in-time recovery, preventing data loss during a disaster recovery situation. It also enables you to review all alterations made to your database. However, binary logs consume disk space and file system resources, and can be removed from a production server after they are no longer needed by the slaves connecting to this master server, and after they have been backed up.
Default frequency 06:00:00
Binary Log Space Exceeds Specified Limit
The binary log captures DML, DDL, and security changes that occur and stores these changes in a binary format. The binary log enables replication as well as point-in-time recovery, preventing data loss during a disaster recovery situation. It also enables you to review all alterations made to your database. However, binary logs consume disk space and can be removed from a production server after they are no longer needed by the slaves connecting to this master server, and after they have been backed up.
Default frequency 06:00:00
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.
Default frequency 00:05:00
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
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.
Default frequency 06:00:00
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 sync_binlog
global variable. 1 is the safest value, but also the slowest.
Default frequency 06:00:00
Once the maximum connection limit for the MySQL server has been reached, no other user connections can be established and errors occur on the client side of the application.
Default frequency 00:01:00
CPU I/O usage should be low on a properly configured and well-tuned system. Excessive CPU I/O usage is often indicative of poor disk or network performance.
Default frequency 00:01:00
CPU usage should be low-to-moderate on a properly configured and well-tuned system. Excessive CPU usage can be indicative of many problems: insufficient RAM, fragmented disks, poorly-tuned queries, etc.
Default frequency 00:01:00
CREATE TABLE LIKE Does Not Require Any Privileges On Source Table
Due to Bug#25578, a user who does not have any access to a database can still clone the structure of tables in that database. Knowing the structure of tables in a database may give a determined hacker insight that allows him or her to proceed with other exploits.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
Data Flushed To Disk After Each SQL Statement
MySQL updates its data files on disk with the write() system
call after every SQL statement and lets the operating system
handle the synchronizing to disk. You can force MySQL to flush
everything to disk after every SQL statement with the
--flush
option, however, this will have an
adverse effect on performance.
Default frequency 06:00:00
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
Date-Handling Bugs Can Crash The Server
Two bugs related to date-handling operations can crash the server leading to potential Denial of Service (DoS) attacks:
Default frequency 06:00:00
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
Disabling Next-Key Locking In InnoDB Can Crash The Server
Due to several bugs, the server could crash if next-key locking in InnoDB was disabled.
These bugs have been fixed in later versions of the MySQL server.
Default frequency 06:00:00
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
Excessive Percentage Of Attempted Connections To The Server Have Failed
Excess aborted connection attempts to MySQL may indicate an issue with respect to the server or network, or could be indicative of DoS or password-cracking attempts against the MySQL Server. The aborted-connects count is incremented when:
A client does not have privileges to access a database
A client uses the wrong password
A malformed packet is received
The connect_timeout variable is exceeded
Default frequency 00:05:00
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
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
Improper key_cache_block_size Setting Can Corrupt MyISAM Tables
The server deducts some bytes from the key_cache_block_size option value and reduces it to the next lower 512 byte boundary. The resulting block size is not a power of two. Setting the key_cache_block_size system variable to a value that is not a power of two results in MyISAM table corruption.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
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 ideal to have most temporary tables
created in memory, leaving exceedingly large temporary tables
to be created on disk. Many DBAs configure
tmp_table_size
appropriately, but forget
that max_heap_table_size
also plays a role.
Default frequency 06:00:00
Incorrect InnoDB Flush Method On Windows
If innodb_file_per_table
is enabled and
innodb_flush_method
is not set to
unbuffered
on Windows, MySQL may not start
and you may see operating system error code 87.
Default frequency 06:00:00
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 etc. You should examine your tables and queries for proper use of indexes.
Default frequency 00:05:00
InnoDB Buffer Cache Has Sub-Optimal Hit Rate
Logical I/O is many times faster than physical I/O, and
therefore a DBA should strive to keep physical I/O to a
minimum. It is true that logical I/O is not free, and that the
DBA should work to keep all
I/O to a
minimum, but it is best if most data access is performed in
memory. When using InnoDB, most data access should occur in
RAM, and therefore the InnoDB buffer cache hit rate should be
high.
Default frequency 00:05:00
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
InnoDB Doublewrite Buffer Enabled
InnoDB uses a novel file flush technique called doublewrite. It adds safety to recovery following an operating system crash or a power outage, and improves performance on most varieties of Unix by reducing the need for fsync() operations.
Doublewrite means that before writing pages to a data file, InnoDB first writes them to a contiguous tablespace area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer has completed does InnoDB write the pages to their proper positions in the data file. If the operating system crashes in the middle of a page write, during recovery InnoDB can find a good copy of the page from the doublewrite buffer.
Default frequency 06:00:00
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
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
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
InnoDB Redo Logs Not Sized Correctly
To avoid frequent checkpoint activity and reduce overall physical I/O, which can slow down write-heavy systems, the InnoDB redo 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
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
Insecure Password Authentication Option Is Enabled
Prior to MySQL 4.1, password hashes computed by the PASSWORD() function were 16 bytes long. As of MySQL 4.1 (and later), PASSWORD() was modified to produce a longer 41-byte hash value to provide enhanced security. However, in order to allow backward-compatibility with user tables that have been migrated from pre-4.1 systems, you can configure MySQL to accept logins for accounts that have password hashes created using the old, less-secure PASSWORD() function, but this is not recommended.
Default frequency 06:00:00
Insecure Password Generation Option Is Enabled
Prior to MySQL 4.1, password hashes computed by the PASSWORD() function were 16 bytes long. As of MySQL 4.1 (and later), PASSWORD() was modified to produce a longer 41-byte hash value to provide enhanced security. In order to allow backward-compatibility with older client programs, you can configure MySQL to generate short (pre-4.1) password hashes for new passwords, however, this is not recommended.
Default frequency 06:00:00
INSERT ON DUPLICATE KEY UPDATE Bug May Break Replication
For INSERT ... ON DUPLICATE KEY UPDATE statements where some AUTO_INCREMENT values were generated automatically for inserts and some rows were updated, one auto-generated value was lost per updated row, leading to faster exhaustion of the range of the AUTO_INCREMENT column. Affected versions of MySQL include 5.0.24 to 5.0.34, and 5.1.12 to 5.1.17 (inclusive).
Because the original problem can affect replication (different values on master and slave), it is recommended that the master and its slaves be upgraded to the current version.
Default frequency 06:00:00
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 is limited to 4GB on
both 32-bit and 64-bit Windows systems, even in MySQL version
5.0.52 and later.
Default frequency 06:00:00
Key Buffer Size May Not Be Optimal For Key Cache
The key cache hit ratio represents the proportion of keys that are being read from the key cache in memory instead of from disk. This should normally be greater than 99% for optimum efficiency.
Default frequency 00:05:00
Key Buffer Size May Not Be Optimal For System RAM
The target server does not appear to have sufficient memory devoted to the key cache. On a dedicated server, this cache is commonly about 25%-50% of total RAM.
Default frequency 06:00:00
LOCAL Option Of LOAD DATA Statement Is Enabled
The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified.
There are two potential security issues with supporting the LOCAL version of LOAD DATA statements:
The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access.
In a Web environment where the clients are connecting from a separate web server, a user could use LOAD DATA LOCAL to read any files that the web server process has read access to (assuming that a user could run any statement against the SQL server). In this environment, the client with respect to the MySQL server actually is the web server, not the remote program being run by the user who connects to the web server.
Default frequency 00:05:00
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
Malformed Password Packet In Connection Protocol Can Crash Server
Due to Bug#28984, a malformed password packet in the connection protocol could cause the server to crash. This can lead to denial of service (DoS) attacks.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
Maximum Connection Limit Nearing Or Reached
Once the maximum connection limit for the MySQL server has been reached, no other user connections can be established and errors occur on the client side of the application.
Default frequency 00:05:00
Missing Security Improvements In GRANT Options
The GRANT statement is used to create MySQL user accounts and to grant rights to accounts. Due to bugs 15756 and 14385, rights may be granted erroneously in certain circumstances:
In grant table comparisons, improper use of a latin1 collation caused some hostname matches to be true that should have been false (Bug#15756).
GRANTs to users with wildcards in their host information could be erroneously applied to similar users with the same username and similar wildcards. For example, a privilege granted to foo@% is also applied to user foo@192.% (Bug#14385).
These bugs have been fixed in later versions of the MySQL server.
Default frequency 06:00:00
Multi-Byte Encoding Processing Can Lead To SQL Injection
Due to bug 8378, the server incorrectly parsed strings escaped with the mysql_real_escape_string() C API function. As a result, even when the character set-aware mysql_real_escape_string() function was used, SQL injection was possible.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
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 as reported by several bugs (#11527, #11684, #18874). Even though these bugs have been fixed, this feature is still considered beta-quality, as noted in the manual.
Default frequency 06:00:00
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 will 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), 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), 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.
Note that 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
MyISAM Key Cache Has Sub-Optimal Hit Rate
The key cache hit ratio represents the proportion of index values that are being read from the key cache in memory instead of from disk. This should normally be greater than 99% for optimum efficiency.
Default frequency 00:05:00
MySQL Agent Memory Usage Excessive
The memory needed by the MySQL Agent for basic monitoring is fairly small and consistent, and depends on the number of rules you have enabled. However, when the Query Analyzer is enabled, the Agent can use significantly more memory to monitor and analyze whatever queries you direct through it. In this case, the amount of memory used depends on the number of unique normalized queries, example queries and example explains being processed, plus the network bandwidth required to send query data to the Service Manager. In general, the amount of memory used for the Query Analyzer is small and well-bounded, but under some circumstances it can become excessive, especially on older versions of Linux.
Default frequency 00:01:00
MySQL Agent Not Communicating With Database Server
The MySQL Enterprise Service Agent must be able to communicate with the local MySQL database server in order to monitor the server and provide advice on enforcement of best practices.
Default frequency 00:01:00
In order to monitor a MySQL server, a Service Agent must be running and communicating with the Service Manager. If the Agent cannot communicate with the Service Manager, the Service Manager has no way of knowing if the MySQL database server being monitored is running, and it cannot collect current statistics to properly evaluate the rules scheduled against that server.
Default frequency 00:00:01
To perform useful work, it must be possible to connect to the local MySQL database server. If the MySQL Enterprise Service Agent cannot communicate with the server, it is likely the server is not running.
Default frequency 00:01:00
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
No Limit On Total Number Of Prepared Statements
Due to Bug#16365, there is no limit to the number of prepared statements that can be open per connection. This can lead to a Denial Of Service (DoS) attack, as the server will crash with out-of-memory (OOM) errors when the amount of statements becomes very large.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
No Value Set For myisam-recover
The myisam-recover
option 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
Non-Authorized User Has DB, Table, Or Index Privileges On All Databases
Privileges such as SELECT, INSERT, ALTER, and so forth allow a user to view and change data, as well as impact system performance. Such operations should be limited to only those databases to which a user truly needs such access so the user cannot inadvertently affect other people's applications and data stores.
Default frequency 01:00:00
Non-Authorized User Has GRANT Privileges On All Databases
The GRANT
privilege, when given on all
databases as opposed to being limited to a few specific
databases, enables a user to give to other users those
privileges that the grantor possesses on all databases. It can
be used for databases, tables, and stored routines. Such a
privilege should be limited to as few users as possible. Users
who do indeed need the GRANT privilege should have that
privilege limited to only those databases they are responsible
for, and not for all databases.
Default frequency 01:00:00
Non-Authorized User Has Server Admin Privileges
Certain privileges, such as SHUTDOWN and SUPER, are primarily used for server administration. Some of these privileges can have a dramatic effect on a system because they allow someone to shutdown the server or kill running processes. Such operations should be limited to a small set of users.
Default frequency 01:00:00
Object Changed: Database Has Been Altered
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to any database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Database Has Been Created
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to any database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Database Has Been Dropped
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to any database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Function Has Been Created
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to any database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Function Has Been Dropped
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to any database structures or functions and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Index Has Been Created
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to any database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Index Has Been Dropped
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to any database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Table Has Been Altered
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Table Has Been Created
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: Table Has Been Dropped
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when changes occur in a production environment with respect to database structures and investigate the reasons for the changes.
Default frequency 00:10:00
Object Changed: User Has Been Dropped
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when changes occur in a production environment with respect to database structures and investigate the reasons for the changes.
Default frequency 00:10:00
For development environments, changes to databases and objects may be a normal occurrence, but not for production environments. It is wise to know when any changes occur in a production environment with respect to any database structures and investigate the reasons for the changes.
Default frequency 00:10:00
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, so it is important to use them properly. If you are not closing prepared statements when you are done with them, you are needlessly tying up memory that could be put to use in other ways.
Default frequency 00:05:00
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
Query Cache Has Sub-Optimal Hit Rate
When enabled, the query cache should experience a high degree of "hits", meaning that queries in the cache are being reused by other user connections. A low hit rate may mean that not enough memory is allocated to the cache, identical queries are not being issued repeatedly to the server, or that the statements in the query cache are invalidated too frequently by INSERT, UPDATE or DELETE statements.
Default frequency 00:05:00
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
Enabling the query cache can increase performance by 200% for queries that are executed often and have large result sets.
Default frequency 00:05:00
Query Cache Potentially Undersized
When the Query Cache is full, and needs to add more queries to the cache, it will make more room in the cache by freeing the least recently used queries from the cache, and then inserting the new queries. If this is happening often then you should increase the size of the cache to avoid this constant "swapping".
Default frequency 00:05:00
A reasonable amount of free memory is required for a system to perform well. Without free memory, new processes and threads cannot start, and the operating system may do excessive paging (swapping blocks of memory to and from disk).
Default frequency 00:01:00
Root Account Can Login Remotely
By default, MySQL includes a root account with unlimited privileges that is typically used to administer the MySQL server. If possible, accounts with this much power should not allow remote logins in order to limit access to only those users able to login to the machine on which MySQL is running. This helps prevent unauthorized users from accessing and changing the system.
Default frequency 00:05:00
The root user account has unlimited privileges and is intended for administrative tasks. Privileged accounts should have strong passwords to prevent unauthorized users from accessing and changing the system.
Default frequency 00:05:00
Security Alterations Detected: User Privileges Granted
For development environments, changes to database security privileges may be a normal occurrence, but for production environments it is wise to know when any security changes occur with respect to database privileges, and to ensure that those changes are authorized and required.
Default frequency 00:05:00
Security Alterations Detected: User Privileges Revoked
For development environments, changes to database security privileges may be a normal occurrence, but for production environments it is wise to know when any security changes occur with respect to database privileges, and to ensure that those changes are authorized and required.
Default frequency 00:05:00
Security Alterations Have Been Detected
For development environments, changes to database security privileges may be a normal occurrence, but for production environments it is wise to know when any security changes occur with respect to database privileges, and to ensure that those changes are authorized and required.
Default frequency 00:05:00
Server Contains Default "test" Database
By default, MySQL comes with a database named
test
that anyone can access. This database
is intended only for testing and should be removed before
moving into a production environment. Because the default
test
database can be accessed by any user
and has permissive privileges, it should be dropped
immediately as part of the installation process.
Default frequency 00:05:00
Server Has Accounts Without A Password
Accounts without passwords are particularly dangerous because an attacker needs to guess only a username. Assigning passwords to all accounts helps prevent unauthorized users from accessing the system.
Default frequency 00:05:00
Anonymous MySQL accounts allow clients to connect to the server without specifying a username. Since anonymous accounts are well known in MySQL, removing them helps prevent unauthorized users from accessing the system.
Default frequency 00:05:00
Server Includes A Root User Account
By default, MySQL includes a root account with unlimited privileges that is typically used to administer the MySQL server. There is no reason this account must be named root. Accounts with this much power should not be easily discovered. Since the root account is well known in MySQL, changing its name helps prevent unauthorized users from accessing and changing the system.
Default frequency 00:05:00
Server-Enforced Data Integrity Checking Disabled
SQL Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. If no SQL modes are enabled this means there is no form of server-enforced data integrity, which means incoming data that is invalid will not be rejected by the server, but instead will be changed to conform to the target column's default datatype. Note, however, that beginning with MySQL 4.1, any client can change its own session SQL mode value at any time.
Default frequency 06:00:00
Server-Enforced Data Integrity Checking Not Strict
SQL Modes define what SQL syntax MySQL should support and what
kind of data validation checks it should perform. There are
many possible options that can be used in conjunction with
each other to specify varying degrees of syntax and data
validation checks the MySQL server will perform. However, to
ensure the highest level of confidence for data integrity, at
least one of the following should be included in the list:
TRADITIONAL, STRICT_TRANS_TABLES,
or
STRICT_ALL_TABLES
.
Note, however, that beginning with MySQL 4.1, any client can change its own session SQL mode value at any time.
Default frequency 06:00:00
Slave Detection Of Network Outages Too High
Slaves must deal with network connectivity outages that affect
the ability of the slave to get the latest data from the
master, and hence cause replication to fall behind. However,
the slave notices the network outage only after receiving no
data from the master for slave_net_timeout
seconds
. You may want to decrease
slave_net_timeout
so the outages -- and
associated connection retries -- are detected and resolved
faster. The default for this parameter is 3600 seconds (1
hour), which is too high for many environments.
Default frequency 06:00:00
Slave Execution Position Too Far Behind Read Position
When a slave receives updates from its master, the I/O thread stores the data in local files known as relay logs. The slave's SQL thread reads the relay logs and executes the updates they contain. If the position from which the SQL thread is reading is way behind the position to which the I/O thread is currently writing, it is a sign that replication is getting behind and results of queries directed to the slave may not reflect the latest changes made on the master.
Default frequency 00:05:00
If replication on a slave has been stopped, it means the slave is not retrieving the latest statements from the master and it is not executing those statements on the slave.
Default frequency 00:01:00
Slave Has Experienced A Replication Error
When a slave receives updates from its master it must apply those updates locally so the data on the slave matches that on the server. If an error occurs while applying an update on a slave, the data on the slave may not match that on the master and it is an indication that replication may be broken.
Default frequency 00:05:00
Slave Has Login Accounts With Inappropriate Privileges
Altering and dropping tables on a slave can break replication. Unless the slave also hosts non-replicated tables, there is no need for accounts with these privileges.
Default frequency 06:00:00
Slave Has Problem Communicating With Master
Slaves must connect to a master to get the latest data from the master. If they cannot connect, or periodically have trouble connecting, replication may fall behind (i.e. the slave may not have the lastest data that was written to the master).
Default frequency 00:05:00
If neither the slave I/O thread nor the slave SQL threads are running, it means the slave is not getting the latest statements from the master and it is not executing those statements on the slave, and thus replication has stopped entirely.
Default frequency 00:01:00
The slave I/O thread is the thread that retrieves statements from the master's binary log and records them into the slave's relay log. If this thread isn't running, it means the slave is not able to retrieve the latest data from the master.
Default frequency 00:01:00
Slave Not Configured As Read Only
Arbitrary or unintended updates to a slave may break
replication or cause a slave to be inconsistent with respect
to its master. Making a slave read_only
can
be useful to ensure that a slave accepts updates only from its
master server and not from clients; it minimizes the
possibility of unintended updates.
Default frequency 06:00:00
Slave Relay Log Space Is Very Large
When a slave receives updates from its master, the I/O thread stores the data in local files known as relay logs. The slave's SQL thread reads the relay logs and executes the updates they contain. After the SQL thread has executed all the updates in a relay log, the file is no longer needed and can be deleted to conserve disk space.
Default frequency 06:00:00
Slave Relay Logs Not Automatically Purged
When a slave receives updates from its master, the I/O thread stores the data in local files known as relay logs. The slave's SQL thread reads the relay logs and executes the updates they contain. After the SQL thread has executed all the updates in a relay log, the file is no longer needed and can be deleted to conserve disk space.
Default frequency 06:00:00
The slave SQL thread is the thread that reads statements from the slave's relay log and executes them to bring the slave in sync with the master. If this thread isn't running, it means the slave is not able to apply the latest changes it has read from the master, and results of queries directed to the slave may not reflect the latest changes made on the master.
Default frequency 00:01:00
Slave SQL Thread Reading From Older Relay Log Than I/O Thread
When a slave receives updates from its master, the I/O thread stores the data in local files known as relay logs. The slave's SQL thread reads the relay logs and executes the updates they contain. If the SQL thread is reading from an older relay log than the one to which the I/O thread is currently writing, it is a sign that replication is getting behind and results of queries directed to the slave may not reflect the latest changes made on the master.
Default frequency 00:05:00
If a slave is too far behind the master, results of queries directed to the slave may not reflect the latest changes made on the master.
Default frequency 00:01:00
Slave Waiting To Free Relay Log Space
For slaves with limited disk space you can place a limit on how large the replication relay log can grow. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unprocessed relay logs. While this protects MySQL from filling up the disk, it means replication is delayed and the slave will fall behind the master.
Default frequency 00:05:00
Slave Without REPLICATION SLAVE Accounts
If the master ever fails, you may want to use one of the slaves as the new master. An account with the REPLICATION SLAVE privilege must exist for a server to act as a replication master (so a slave can connect to it), so it's a good idea to create this account on your slaves to prepare it to take over for a master if needed.
Default frequency 06:00:00
The slow query log can be used to identify queries that take a long time to complete.
Default frequency 00:05:00
Stored Procedures Found With SELECT * Syntax
Best practices for SQL coding state that no query should be issued with SELECT *. Reasons include:
To ensure that only the necessary columns are returned from a SQL statement, the actual column names should be specifically entered. This cuts down on unwanted network traffic as only columns necessary for query satisfaction are present.
If the underlying table has columns added or removed, the query itself may malfunction if cursors or other such application objects are used.
Default frequency 06:00:00
Stored Routine Runs In Definer''s Rather Than Caller''s Security Context
Due to bug 18630, a stored routine created by one user and then made accessible to a different user using GRANT EXECUTE could be executed by that user with the privileges of the routine's definer.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space or to increase the speed of your system by spreading your tables to different disks.
However, symlinks can compromise security. This is especially important if you run mysqld as root, because anyone who has write access to the server's data directory could then delete any file in the system!
Default frequency 06:00:00
MySQL is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client threads having different states on the same table, the table is opened independently by each concurrent thread.
The table cache is used to cache file descriptors for open
tables and there is a single cache shared by all clients.
Increasing the size of the table cache allows mysqld to keep
more tables open simultanously by reducing the number of file
open and close operations that must be done. If the value of
Open_tables
is approaching the value of
table_cache
, this may indicate performance
problems.
Default frequency 00:05:00
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
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
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 sum of Handler variables which denote all row accesses - such as Handler_read_key, Handler_read_next etc. You should examine your tables and queries for proper use of indexes.
Default frequency 00:05:00
Temporary Tables To Disk Ratio Excessive
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
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
Thread Cache Size May Not Be Optimal
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
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
Use Of View Overrides Column Update Privileges On Underlying Table
Due to Bug#27878, by using a view, a user who only has privileges to update a given column of a table is able to update any column of that table, even though the view is defined with SQL SECURITY INVOKER. Also, use of a view could allow a user to gain update privileges for tables in other databases.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
User Can Gain Privileges By Running Stored Routine Declared Using SQL SECURITY INVOKER
Due to Bug#27337, if a stored routine was declared using SQL SECURITY INVOKER, a user who invoked the routine could gain privileges. For example, a user without the CREATE privilege on a certain database could gain that privilege after invoking a stored routine.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
User Has Rights To Database That Does Not Exist
When a database is dropped, user privileges on the database are not automatically dropped. This has security implications as that user will regain privileges if a database with the same name is created in the future, which may not be the intended result.
Default frequency 00:05:00
User With Only ALTER Privilege On Partitioned Table Can Obtain SELECT Privilege Information
Due to Bug#23675, a user with only the ALTER privilege on a partitioned table could obtain information about the table that should require the SELECT privilege.
This bug has been fixed in later versions of the MySQL server.
Default frequency 06:00:00
Users Can View All Databases On MySQL Server
The SHOW DATABASES privilege should be granted only to users
who need to see all the databases on a MySQL Server. It is
recommended that the MySQL Server be started with the
--skip-show-database
option enabled to
prevent anyone from using the SHOW DATABASES statement unless
they have been specifically granted the SHOW DATABASES
privilege.
Note: If a user is granted any global privilege, such as CREATE TEMPORARY TABLES or LOCK TABLES, they are automatically given the ability to show databases unless the server is started with the --skip-show-database option enabled. DBAs should be aware of this fact, in the event that any applications make use of temporary tables.
Default frequency 00:05:00
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
Individual graphs are defined below.
Name | Connections |
Range Label | total/min |
Series | Expression |
Aborted | aborted |
Active | running |
Current | connected |
Variables | Data Item |
connected | mysql :status :Threads_connected |
running | mysql :status :Threads_running |
aborted | mysql :status :Aborted_connects |
Name | CPU Utilization |
Range Label | % |
Series | Expression |
Kernel | sys/(sys+user+wait+idle)*100 |
Total | (sys+user+wait)/(sys+user+wait+idle)*100 |
User | user/(sys+user+wait+idle)*100 |
Wait I/O | wait/(sys+user+wait+idle)*100 |
Variables | Data Item |
sys | os :cpu :cpu_sys |
user | os :cpu :cpu_user |
wait | os :cpu :cpu_wait |
idle | os :cpu :cpu_idle |
Name | Database Activity |
Range Label | avg statements/sec |
Series | Expression |
Call | ((call_proc)/60) |
Delete | ((deletes+delete_multi)/60) |
Insert | ((inserts+insert_selects)/60) |
Replace | ((replaces+replace_selects)/60) |
Select | (selects/60) |
Update | ((updates+update_multi)/60) |
Variables | Data Item |
selects | mysql :status :Com_select |
inserts | mysql :status :Com_insert |
insert_selects | mysql :status :Com_insert_select |
updates | mysql :status :Com_update |
update_multi | mysql :status :Com_update_multi |
replaces | mysql :status :Com_replace |
replace_selects | mysql :status :Com_replace_select |
deletes | mysql :status :Com_delete |
delete_multi | mysql :status :Com_delete_multi |
call_proc | mysql :status :Com_call_procedure |
Name | Hit Ratios |
Range Label | % |
Series | Expression |
InnoDB Buffer | 100-((iReads / iReadRequests)*100) |
Key Cache | (100-((keyReads / keyReadRequests)*100)) * (keyReadRequests
>= keyReads) |
Query Cache | (qHits/(qHits+qInserts))*100 |
Thread Cache | 100-((tcreated / connections)*100) |
Variables | Data Item |
qHits | mysql :status :Qcache_hits |
qInserts | mysql :status :Qcache_inserts |
keyReads | mysql :status :Key_reads |
keyReadRequests | mysql :status :Key_read_requests |
iReads | mysql :status :Innodb_buffer_pool_reads |
iReadRequests | mysql :status :Innodb_buffer_pool_read_requests |
tcreated | mysql :status :Threads_created |
connections | mysql :status :Connections |
Name | InnoDB Buffer Pool |
Range Label | MB |
Series | Expression |
Modified | (modified*16384)/(1024*1024) |
Total Size | (size*16384)/(1024*1024) |
Used | (used*16384)/(1024*1024) |
Variables | Data Item |
modified | mysql :innodbstatus :innodb_bp_modified_pages |
size | mysql :innodbstatus :innodb_bp_size |
used | mysql :innodbstatus :innodb_bp_db_pages |
Name | InnoDB OS File Access |
Range Label | avg operations/sec |
Series | Expression |
File fsync() | (fsync/60) |
File Reads | (read/60) |
File Writes | (write/60) |
Variables | Data Item |
read | mysql :innodbstatus :innodb_io_os_file_reads |
write | mysql :innodbstatus :innodb_io_os_file_writes |
fsync | mysql :innodbstatus :innodb_io_os_file_fsyncs |
Name | InnoDB Row Details |
Range Label | avg rows/sec |
Series | Expression |
Rows Deleted | (deleted/60) |
Rows Inserted | (inserted/60) |
Rows Read | (read/60) |
Rows Updated | (udpated/60) |
Variables | Data Item |
read | mysql :innodbstatus :innodb_rows_read |
inserted | mysql :innodbstatus :innodb_rows_inserted |
updated | mysql :innodbstatus :innodb_rows_updated |
deleted | mysql :innodbstatus :innodb_rows_deleted |
Name | InnoDB Semaphores |
Range Label | avg waits/sec |
Series | Expression |
OS Waits | (oswaits/60) |
Spin Rounds | (srounds/60) |
Spin Waits | (swaits/60) |
Variables | Data Item |
swaits | mysql :innodbstatus :innodb_sem_mutex_spin_waits |
srounds | mysql :innodbstatus :innodb_sem_mutex_rounds |
oswaits | mysql :innodbstatus :innodb_sem_mutex_os_waits |
Name | KBytes In/Out |
Range Label | avg kbytes/sec |
Series | Expression |
Received | ((bytesIn/1024)/60) |
Sent | ((bytesOut/1024)/60) |
Total | (((bytesIn+bytesOut)/1024)/60) |
Variables | Data Item |
bytesIn | mysql :status :Bytes_received |
bytesOut | mysql :status :Bytes_sent |
Name | Load Average |
Range Label | Load Average |
Series | Expression |
1 | zero |
15 | two |
5 | one |
Variables | Data Item |
zero | os :loadavg :0 |
one | os :loadavg :1 |
two | os :loadavg :2 |
Name | Memory Usage - Agent |
Range Label | MB |
Series | Expression |
Agent | agent_mem_size / 1024 / 1024 |
Lua | lua_mem_size / 1024 / 1024 |
Variables | Data Item |
lua_mem_size | agent :lua :mem_size |
agent_mem_size | agent :proc :mem_resident |
Name | Memory Usage - OS Resident |
Range Label | MB |
Series | Expression |
Total | ram_total/(1024*1024) |
Used | (ram_total-ram_unused)/(1024*1024) |
Variables | Data Item |
ram_total | os :mem :ram_total |
ram_unused | os :mem :ram_unused |
Name | Memory Usage - OS Virtual |
Range Label | MB |
Series | Expression |
Total | swap_total/(1024*1024) |
Used | (swap_total-swap_unused)/(1024*1024) |
Variables | Data Item |
swap_total | os :mem :swap_total |
swap_unused | os :mem :swap_unused |
Name | Opened Tables |
Range Label | total/min |
Series | Expression |
Opened Tables | openedTables |
Variables | Data Item |
openedTables | mysql :status :Opened_tables |
Name | Query Cache Blocks |
Range Label | num blocks |
Series | Expression |
Free | free_blocks |
Size | size_blocks |
Variables | Data Item |
size_blocks | mysql :status :Qcache_total_blocks |
free_blocks | mysql :status :Qcache_free_blocks |
Name | Query Cache Efficiency |
Range Label | avg cache ops/sec |
Series | Expression |
Hits | (hits/60) |
Inserts | (inserts/60) |
Not Cached | (not_cached/60) |
Variables | Data Item |
hits | mysql :status :Qcache_hits |
inserts | mysql :status :Qcache_inserts |
not_cached | mysql :status :Qcache_not_cached |
Name | Query Cache Lowmem Prunes |
Range Label | avg cache ops/sec |
Series | Expression |
Lowmem Prunes | (deletes/60) |
Variables | Data Item |
deletes | mysql :status :Qcache_lowmem_prunes |
Name | Query Cache Memory |
Range Label | MB |
Series | Expression |
Free MB | free/(1024*1024) |
Size MB | size/(1024*1024) |
Variables | Data Item |
size | mysql :variables :query_cache_size |
free | mysql :status :Qcache_free_memory |
Name | Query Cache Queries |
Range Label | num queries |
Series | Expression |
Queries in Cache | queries |
Variables | Data Item |
queries | mysql :status :Qcache_queries_in_cache |
Name | Replication Delay |
Range Label | total seconds |
Series | Expression |
Seconds Behind Master | sbehind |
Variables | Data Item |
sbehind | mysql :slavestatus :Seconds_Behind_Master |
Name | Row Accesses |
Range Label | avg rows/sec |
Series | Expression |
Average Rows Per Query | ((first+key+next+prev+hread_rnd+hread_rnd_next+sort_rows) /
questions) |
Rows Read via Full Scan | ((hread_rnd+hread_rnd_next)/60) |
Rows Read via Indexes | ((first+key+next+prev)/60) |
Variables | Data Item |
first | mysql :status :Handler_read_first |
key | mysql :status :Handler_read_key |
next | mysql :status :Handler_read_next |
prev | mysql :status :Handler_read_prev |
hread_rnd | mysql :status :Handler_read_rnd |
hread_rnd_next | mysql :status :Handler_read_rnd_next |
sort_rows | mysql :status :Sort_rows |
questions | mysql :status :Questions |
Name | Row Writes |
Range Label | avg rows/sec |
Series | Expression |
Rows Deleted | (delete/60) |
Rows Inserted | (write/60) |
Rows Updated | (update/60) |
Variables | Data Item |
delete | mysql :status :Handler_delete |
update | mysql :status :Handler_update |
write | mysql :status :Handler_write |
Name | Sort Activity |
Range Label | total/min |
Series | Expression |
Merge Passes | Sort_merge_passes |
Range | Sort_range |
Scan | Sort_scan |
Variables | Data Item |
Sort_merge_passes | mysql :status :Sort_merge_passes |
Sort_range | mysql :status :Sort_range |
Sort_scan | mysql :status :Sort_scan |
Name | Table Locks |
Range Label | avg locks/sec |
Series | Expression |
Immediate | (locks_immediate/60) |
Waited | (locks_waited/60) |
Variables | Data Item |
locks_waited | mysql :status :Table_locks_waited |
locks_immediate | mysql :status :Table_locks_immediate |