MySQL default checks and policies

  • Release version: Xanadu
  • Updated August 1, 2024
  • 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 offers predefined checks and policies to monitor MySQL metrics effectively. These enable ServiceNow customers to verify MySQL database health, query results, thread counts, and process activities, ensuring timely detection of issues and optimized database performance.

    Show full answer Show less

    MySQL Checks

    • Check MySQL Alive (app.mysql.check-mysql-alive): Validates the size of a query result set against specified warning and critical thresholds. Useful to confirm expected data retrieval from MySQL.
    • Check MySQL Threads (app.mysql.check-mysql-threads): Monitors the number of active MySQL threads and triggers alerts based on defined warning and critical thread count thresholds. This helps detect potential performance bottlenecks or overloads.
    • Check MySQL Query (util.check-mysql-query): Confirms whether MySQL is running by evaluating thread counts against both upper and lower warning/critical limits, providing a comprehensive health check.

    Each check supports connection options including host, port, UNIX socket, database name, and reading credentials from a MySQL configuration (.ini) file. This flexibility helps integrate with various MySQL deployment scenarios.

    MySQL Metrics

    • MySQL DB Metrics (app.mysql.metrics-mysql): Collects and reports various MySQL database metrics via a Graphite-compatible script, enabling detailed performance monitoring and trending.
    • MySQL Thread Metrics (app.mysql.check-mysql-threads metric): Provides metrics on thread usage with alert thresholds for both high and low thread counts, reinforcing proactive monitoring.
    • MySQL Process Metrics (app.mysql.metrics-mysql-processes): Gathers data on MySQL processes, including commands running and associated databases, delivering granular insight into database activities.

    Metrics collection scripts support connection options similar to checks and allow a customizable metric naming scheme to fit into existing monitoring architectures.

    Practical Use for ServiceNow Customers

    • Enable these checks and metrics to receive automated, threshold-based alerts on MySQL health and performance.
    • Use configuration options to securely connect to MySQL instances, including through .ini files that store credentials.
    • Analyze metric outputs to understand query loads, thread usage, and active processes, facilitating capacity planning and troubleshooting.
    • Integrate collected data into ServiceNow dashboards or external monitoring systems for consolidated visibility and faster incident response.

    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