MySQL default checks and policies

  • Release version: Yokohama
  • Updated January 30, 2025
  • 4 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of MySQL Default Checks and Policies

    The Agent Client Collector for ServiceNow provides predefined checks and policies to monitor MySQL database metrics effectively. These default checks enable customers to validate the health and performance of their MySQL instances by running specific queries and collecting metrics through command-line scripts. The checks cover query result validation, thread monitoring, and overall MySQL process and metric reporting.

    Show full answer Show less

    Key Features

    • MySQL Query Result Count Check: Validates the number of rows returned by a custom MySQL query and triggers alerts based on defined warning and critical thresholds. This helps ensure queries return expected data volumes.
    • MySQL Threads Check: Monitors the number of running MySQL threads and generates OK, WARNING, or CRITICAL statuses depending on configured thresholds. Useful to detect thread overload or underuse.
    • MySQL Database Health Check: Confirms if the MySQL database service is running by analyzing thread counts with configurable upper and lower alert thresholds.
    • MySQL Metrics Collection: Gathers detailed performance metrics from MySQL, including general database statistics, commands executed, aborted connections, and byte transfer counts. Metrics are output with customizable naming schemes for integration with monitoring tools.
    • MySQL Processes Metrics: Provides insights into active MySQL processes, the commands they are running, and the databases involved, aiding in process-level troubleshooting.

    Usage and Practical Considerations

    • All checks require connection parameters such as host, port, database name, and optionally a MySQL configuration file (.ini) with credentials.
    • For UNIX-like systems, socket parameters are required when connecting to localhost.
    • Checks produce clear, actionable output indicating current status (OK/WARNING/CRITICAL) and relevant metric values to assist in monitoring decisions.
    • Command-line options allow fine-tuning thresholds for alerts, enabling customization to specific operational environments.
    • Verbose modes are available for metrics commands to output error details for deeper diagnostics.

    Key Outcomes

    ServiceNow customers leveraging these default MySQL checks and policies can expect streamlined monitoring setup for MySQL databases, allowing prompt detection of performance issues such as excessive threads, unexpected query results, or database downtime. The built-in metrics collection supports detailed performance analysis and integration with broader monitoring dashboards, enhancing overall database reliability and operational visibility.

    The Agent Client Collector provides the following default checks and policies for MySQL Metrics monitoring.

    Table 1. MySQL Events
    Type Check Description Usage and Usage example Output
    Event app.mysql.check-mysql-alive Verifies the length of a result set from a MySQL query. check-mysql-query-result-count.rb (options)

    -c, --critical COUNT COUNT critical threshold for number of items returned by the query (required)

    -d, --database DATABASE MySQL database (required)

    -h, --host HOST MySQL Host to connect to (required)

    -i, --ini VALUE My.cnf ini file

    --ini-section VALUE Section in my.cnf ini file. To enable connection to MySQL thru the .ini file, provide the values against the properties 'user' and 'password' in the client section in the .ini file.

    -P, --port PORT MySQL Port to connect to

    -q, --query QUERY Query to execute (required)

    -w, --warning COUNT Count warning threshold for number of items returned by the query (required)

    -S, --socket UNIX socket to connect to (required if host specified is 'localhost' on UNIX - like systems)

    Usage Example

    check-mysql-query-result-count.rb -h 127.0.0.1 -P 3306 -d mysql -q "select * from user" -w 5 -c 8

    		 MysqlQueryCountCheck OK/CRITICAL/WARNING: message regarding ratio between query length and threshold values
    Event app.mysql.check-mysql-threads Verifies the MySQL DB number of running threads and assigns a status of OK/WARNING/CRITICAL depending on input values. check-mysql-threads.rb (options)

    -h, --hostname HOST Hostname to login to

    -i, --ini VALUE My.cnf ini file

    --ini-section VALUE Section in my.cnf ini file (Needed if .ini path provided). To enable connection to MySQL thru the .ini file, provide the values against the properties 'user' and 'password' in the client section in the .ini file.

    -c, --critnum NUMBER Number of running threads upon which an alert is issued

    -w, --warnnum NUMBER Number of running threads upon which a warning is issued

    -P, --port PORT MySQL Port to connect to

    -S, --socket UNIX socket to connect to (required if host specified is 'localhost' on UNIX - like systems)

    Usage Example

    check-mysql-threads.rb -h 127.0.0.1 -P 3306 -l 0 -m 1 -c 25 -w 20

    		 CheckMySQLHealth OK/Critical/Warning and number of running threads
    Event util.check-mysql-query Verifies whether MySQL DB is running. check-mysql-threads.rb (options)

    -h, --hostname HOST Hostname to login to

    -i, --ini VALUE My.cnf ini file

    --ini-section VALUE Section in my.cnf ini file (Needed if .ini path is provided). To enable connection to MySQL thru the .ini file, provide the values against the properties 'user' and 'password' in the client section in the .ini file.

    -c, --critnum NUMBER Number of running threads upon which an alert is issued

    -w, --warnnum NUMBER Number of running threads upon which a warning is issued

    -l, --critlow NUMBER Number of running threads under which an alert is issued

    -m, --warnlow NUMBER Number of running threads under which a warning is issued

    -P, --port PORT MySQL Port to connect to

    -s, --socket UNIX socket to connect to (required if host specified is 'localhost' on UNIX - like systems)

    Usage Example

    check-mysql-threads.rb -h 127.0.0.1 -P 3306 -l 0 -m 1 -c 25 -w 20

    CheckMySQLHealth OK/Critical/Warning and number of running threads
    Table 2. MySQL Metrics
    Type Check Description Usage and Usage Example Output
    Metric app.mysql.metrics-mysql Returns metrics on the MySQL DB. /usr/local/bin/metrics-mysql-graphite.rb (options)

    -h, --host HOST MySQL host to connect to (required)

    -i, --ini VALUE My.cnf ini file

    --ini-section VALUE Section in my.cnf ini file (Needed if .ini path is provided). To enable connection to MySQL thru the .ini file, provide the values against the properties 'user' and 'password' in the client section in the .ini file.

    -P, --port PORT MySQL port to connect to.

    -s, --scheme SCHEME Metric naming scheme, text to append to metric

    -S, --socket UNIX socket to connect to (required if host specified is 'localhost' on UNIX - like systems)

    -v, --verbose Show errors (if generated) regarding secondary status copies. Add -v to the Command to activate.

    Usage Example

    check-mysql-threads.rb -h 127.0.0.1 -P 3306 -l 0 -m 1 -c 25 -w 20

    		 MysqlQueryCountCheck OK/CRITICAL/WARNING: message regarding ratio between query length and threshold values
    Metric app.mysql.check-mysql-threads Verifies the MySQL DB number of running threads and assigns a status of OK/WARNING/CRITICAL depending on input values. /usr/local/bin/metrics-mysql-graphite.rb (options)

    -h, --hostname HOST Hostname to connect to (required)

    -i, --ini VALUE My.cnf ini file

    --ini-section VALUE Section in my.cnf ini file (Needed if .ini path is provided). To enable connection to MySQL thru the .ini file, provide the values against the properties 'user' and 'password' in the client section in the .ini file.

    -c, --critnum NUMBER Number of running threads upon which an alert is issued

    -w, --warnnum NUMBER Number of running threads upon which a warning is issued

    -l, --critlow NUMBER Number of running threads under which an alert is issued

    -m, --warnlow NUMBER Number of running threads under which a warning is issued

    -P, --port PORT MySQL Port to connect to

    -s, --socket UNIX socket to connect to (required if host specified is 'localhost' on UNIX - like systems)

    Usage Example

    metrics-mysql-graphite.rb -h 127.0.0.1 -P 3306

    hostname.mysql.general.abortedClients 7600 1649630975

    hostname.mysql.general.abortedConnects 247 1649630975

    hostname.mysql.general.txBytes 752733902 1649630975

    hostname.mysql.commands.admin_commands 1631 1649630975

    hostname.mysql.commands.alter_table 0 1649630975
    Metric app.mysql.metrics-mysql-processes Returns various metrics regarding MySQL DB processes

    /usr/local/bin/metrics-mysql-processes.rb (options)

    -h, --host MySQL host to connect to

    -i, --ini VALUE My.cnf ini file

    --ini-section VALUE Section in my.cnf ini file (Needed if .ini path is provided). To enable connection to MySQL thru the .ini file, provide the values against the properties 'user' and 'password' in the client section in the .ini file.

    -P, --port PORT MySQL Port to connect to

    -s --scheme SCHEME Metric naming scheme, text to append to metric

    -s, --socket UNIX socket to connect to (required if host specified is 'localhost' on UNIX - like systems)

    Usage Example

    metrics-mysql-processes.rb -h 127.0.0.1 -P 3306

    processes, commands they're running and the databases they're running the commands on

    Example:

    • hostname.mysql.database.mysql 1 1649631113
    • hostname.mysql.command.Daemon 1 1649631113
    • hostname.mysql.command.Sleep 4 1649631113
    • hostname.mysql.command.Query 1 1649631113