MySQL probe checkpoint queries

Products

• Unified Infrastructure Management for Mainframe
• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Need to know what queries the mysql probe uses to generate metrics for the available set of checkpoints.

What are the mysql Checkpoint Queries used by the mysql probe?

Environment

• Release:  UIM Server any release
• Component: UIMMSQ any version

Resolution

The attached mysql_checkpoint.txt file contains the list of checkpoints that are made by the mysql probe for the default set of checkpoints and identifies the mysql query used by the probe to extract the metrics from the monitored mySQL database.

For each checkpoint listed in the "Checkpoint Name" column the probe issues one of a fixed set of MYSQL queries as listed in the corresponding "QueryFlags" column.

For example, for the "number_of_databases" checkpoint, the probe issues the following SQL query:

SHOW DATABASES;  

The probe parses the output of the query to report the number of databases metric returned by this query.

Another example:

For the "myisam_total_disk_write_ratio" checkpoint, the probe issues the following SQL query:

SHOW GLOBAL STATUS;

From the mysql Metrics docops page, the myisam_total_disk_write_ratio checkpoint is described as follows:

"Monitors the ratio of disk writes to all write requests."

which is calculated as: Key_writes/Key_write_requests.

 

Review the following link for reference: 

http://dba.stackexchange.com/questions/20083/whats-the-formula-for-calculating-key-efficiency-key-buffer-used-and-query-ca 

 

The only checkpoint which executes an actual query is table_size_ratio which executes the following:

"SELECT table_name, avg_row_length, data_length, max_data_length, index_length, data_free, table_schema FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND max_data_length > 0 ORDER BY table_schema ASC"

All other QoS is populated using SHOW GLOBAL STATUS or SHOW VARIABLES, and the output is parsed to populate QoS metrics.

For other checkpoints Review the following link for reference for starters:

https://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html 

There is no more information to provide more detailed information regarding the mysql probe checkpoint calculations.

Additional Information

 /* Checkpoint Name                                  Id                                      QueryFlags Type

    /*************************************************************************/

    /* General Class */

    /*************************************************************************/

    { "available",                                      CPID_GEN_AVAILABLE,                     QUERY_NONE, CPTYPE_BOOLEAN},

    { "number_of_databases",                            CPID_GEN_NODB,                          QUERY_SHOW_DATABASES, CPTYPE_LONG},

    { "bytes_sent",                                     CPID_GEN_BYSENT,                        QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "bytes_received",                                 CPID_GEN_BYREC,                         QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "max_per_thread_memory",                          CPID_GEN_MAXPERTHRMEM,                  QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    { "max_shared_buffers",                             CPID_GEN_MAXSHAMEM,                     QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    { "tot_memory_configured",                          CPID_GEN_CONFMAXTOTMEM,                 QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Connection History Class */

    /*************************************************************************/

    { "connections_attempted",                          CPID_HIST_CONNATTEMPTS,                 QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "connections_successful",                         CPID_HIST_CONNSUCCESS,                  QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "connections_refused",                            CPID_HIST_CONNREFUSED,                  QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "connection_refusal_rate",                        CPID_HIST_CONNREFUSEDRATE,              QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "connections_aborted",                            CPID_HIST_CONNABORTED,                  QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Connection Class */

    /*************************************************************************/

    { "connections_count",                              CPID_CONN_COUNT,                        QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "connection_usage_rate",                          CPID_CONN_USAGERATE,                    QUERY_SHOW_GLOBAL_STATUS | QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    { "running_threads",                                CPID_CONN_RUNNTHREADS,                  QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "peak_open_connections",                          CPID_CONN_PEAKOPENCONN,                 QUERY_SHOW_GLOBAL_STATUS, CPTYPE_LONG},

    { "connection_peak_usage_rate",                     CPID_CONN_PEAKUSAGERATE,                QUERY_SHOW_GLOBAL_STATUS | QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* MyISAm Key cache Class */

    /*************************************************************************/

    { "myisam_cache_usage_rate",                        CPID_MYISAMCACHE_USAGERATE,             QUERY_SHOW_GLOBAL_STATUS | QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    { "myisam_read_requests",                           CPID_MYISAMCACHE_READREQUEST,           QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "myisam_reads_from_disk",                         CPID_MYISAMCACHE_DISKREADS,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "myisam_cache_hit_rate",                          CPID_MYISAMCACHE_HITRATE,               QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "myisam_write_requests",                          CPID_MYISAMCACHE_WRITEREQ,              QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "myisam_writes_to_disk",                          CPID_MYISAMCACHE_DISKWRITE,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "myisam_total_disk_write_ratio",                  CPID_MYISAMCACHE_TOTDISKWRITERATIO,     QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* InnoDB Cache Class */

    /*************************************************************************/

    { "innodb_cache_usage_rate",                        CPID_INNODBCACHE_USAGERATE,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "innodb_cache_hit_rate",                          CPID_INNODBCACHE_HITRATE,               QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "innodb_cache_write_waits_rate",                  CPID_INNODBCACHE_WRITEWAITRATE,         QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},    

    { "innodb_latched_pages",                           CPID_INNODBCACHE_LATCHEDPAGES,          QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Table Locks Class */

    /*************************************************************************/

    { "table_locks_acquired_immediately",               CPID_TAB_LOCKSAQUIRED,                  QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "table_lock_waits",                               CPID_TAB_LOCKSWAIT,                     QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "table_lock_contention_rate",                     CPID_TAB_LOCKCONTENTIONRATE,            QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* InnoDB Row Locks Class */

    /*************************************************************************/

    { "innodb_lock_waits",                              CPID_INNODBROW_LOCKSWAITS,              QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "innodb_avg_wait_time",                           CPID_INNODBROW_AVGWAITTIME,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_LONG},

    /*************************************************************************/

    /* InnoDB Logs Class */

    /*************************************************************************/    

    { "innodb_log_waits_rate",                          CPID_INNODBLOG_WAITRATE,                QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "innodb_total_size_cache_size_rate",              CPID_INNODBLOG_CACHESIZERATE,           QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Thread Cache Class */

    /*************************************************************************/

    { "thread_cache_number_of_threads",                 CPID_THREADS_COUNT,                     QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "thread_cache_threads_created_con",               CPID_THREADS_CONN,                      QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "thread_cache_hit_rate",                          CPID_THREADS_CACHEHITRATE,              QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Table Cache */

    /*************************************************************************/

    { "cache_tables_open",                              CPID_TAB_OPEN,                          QUERY_SHOW_GLOBAL_STATUS, CPTYPE_LONG},

    { "table_cache_rate",                               CPID_TAB_INCACHERATE,                   QUERY_SHOW_GLOBAL_STATUS | QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    { "table_cache_misses",                             CPID_TAB_CACHEMISS,                     QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Query Class */

    /*************************************************************************/

    { "query_cache_usage_rate",                         CPID_QUERY_CACHEUSAGERATE,              QUERY_SHOW_GLOBAL_STATUS | QUERY_SHOW_VARIABLE, CPTYPE_DOUBLE},

    { "query_cache_fragmentation_rate",                 CPID_QUERY_FRAGRATE,                    QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "queries_in_cache",                               CPID_QUERY_INCACHE,                     QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "queries_not_cached",                             CPID_QUERY_NOTCACHED,                   QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "queries_inserted_cache",                         CPID_QUERY_INSERTTOCACHE,               QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "query_cache_hits",                               CPID_QUERY_CACHEHITS,                   QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "queries_pruned_from_cache",                      CPID_QUERY_PRUNEDFROMCACHE,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "query_cache_pruned_inserts_ratio",               CPID_QUERY_PUNEDINSERTRATIO,            QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "query_cache_hit_rate",                           CPID_QUERY_CACHEHITRATE,                QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "query_cache_hits_inserts_ratio",                 CPID_QUERY_CACHEHITINSERTRATIO,         QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Temp Tables Class */

    /*************************************************************************/

    { "temp_tables_created",                            CPID_TEMPTAB_CREATED,                   QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "temp_tables_created_on_disk",                    CPID_TEMPTAB_CREATEDONDISK,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "temp_tables_disk_total_ratio",                   CPID_TEMPTAB_DISKTTOTRATIO,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Slow Launch Threads Class */

    /*************************************************************************/ 

    { "slow_launch_threads",                            CPID_SLOWTHREADS_COUNT,                 QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Slow Queries Class */

    /*************************************************************************/

    { "slow_queries_enabled",                           CPID_SLOWQUERY_ENABLED,                 QUERY_SHOW_VARIABLE, CPTYPE_BOOLEAN},

    { "slow_queries_min_time",                          CPID_SLOWQUERY_MINTIME,                 QUERY_SHOW_VARIABLE, CPTYPE_LONG},

    { "slow_queries_no",                                CPID_SLOWQUERY_COUNT,                   QUERY_SHOW_GLOBAL_STATUS, CPTYPE_LONG},

    /*************************************************************************/

    /* Sort Buffers Class */

    /*************************************************************************/

    { "sort_buffers_range",                             CPID_SORTBUF_RANGE,                     QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "sort_buffers_scan",                              CPID_SORTBUF_SCAN,                      QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "sort_buffers_temp_files",                        CPID_SORTBUF_TEMPFILES,                 QUERY_SHOW_GLOBAL_STATUS, CPTYPE_LONG},

    /*************************************************************************/

    /* Full Table Scans Class */

    /*************************************************************************/

    { "full_table_scan_rate",                           CPID_FULLTABSCAN_RATE,                  QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "full_table_scan_selects_requiring",              CPID_FULLTABSCAN_SELECTS,               QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "full_table_scan_selects_requiring_rate",         CPID_FULLTABSCAN_SELECTRATE,            QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "full_table_scan_for_joins",                      CPID_FULLTABSCAN_SCANFORJOIN,           QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Statements Class */

    /*************************************************************************/

    { "stmts_all",                                      CPID_STATEMENTS_ALL,                    QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_select_percent",                           CPID_STATEMENTS_SELECTPER,              QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_insert_percent",                           CPID_STATEMENTS_INSERTPER,              QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_update_percent",                           CPID_STATEMENTS_UPDATEPER,              QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_delete_percent",                           CPID_STATEMENTS_DELETEPER,              QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_replace_percent",                          CPID_STATEMENTS_REPLACEPER,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_read_write_ratio",                         CPID_STATEMENTS_READWRITERATIO,         QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_commits",                                  CPID_STATEMENTS_COMMITS,                QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_rollback_commit_ratio",                    CPID_STATEMENTS_ROLLBACKCOMMITS,        QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "stmts_insert_commit_ratio",                      CPID_STATEMENTS_INSERTCOMMITRATE,       QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Replication Class */

    /*************************************************************************/

    { "rep_slave_running",                              CPID_REP_RUNNING,                       QUERY_SHOW_GLOBAL_STATUS, CPTYPE_BOOLEAN},

    { "rep_io_running",                                 CPID_REP_IORUNNING,                     QUERY_SHOW_SLAVE_STATUS, CPTYPE_BOOLEAN},

    { "rep_slave_sql_running",                          CPID_REP_SQLRUNNING,                    QUERY_SHOW_SLAVE_STATUS, CPTYPE_BOOLEAN},

    { "rep_seconds_behind_master",                      CPID_REP_SECBEHINDMASTER,               QUERY_SHOW_SLAVE_STATUS, CPTYPE_LONG},

    { "last_errno",                                     CPID_REP_LASTERR,                       QUERY_SHOW_SLAVE_STATUS, CPTYPE_LONG},

    { "rep_slave_retried_transactions",                 CPID_REP_SALVERETRIEDTRANS,             QUERY_SHOW_SLAVE_STATUS, CPTYPE_DOUBLE},

    { "rep_slave_open_temp_tables",                     CPID_REP_SALVEOPENTMPTAB,               QUERY_SHOW_SLAVE_STATUS, CPTYPE_DOUBLE},

    { "rep_master_log_read_position",                   CPID_REP_MASTERLOGREADPOS,              QUERY_SHOW_SLAVE_STATUS, CPTYPE_DOUBLE},

    { "rep_relay_master_log_file_position",             CPID_REP_RELMASTERLOGFILEPOS,           QUERY_SHOW_SLAVE_STATUS, CPTYPE_DOUBLE},

    { "rep_relay_log_file_position",                    CPID_REP_RELLOGFILEPOS,                 QUERY_SHOW_SLAVE_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Binary Log Class */

    /*************************************************************************/

    { "bin_log_transactions_used_cache",                CPID_BINLOG_TRANSUSEDCACHE,             QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "bin_log_transactions_saved_in_temp_file",        CPID_BINLOG_TRANSSAVEDTEMPTAB,          QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    { "bin_log_transactions_saved_in_temp_file_rate",   CPID_BINLOG_TRANSSAVEDTEMPTABRATE,      QUERY_SHOW_GLOBAL_STATUS, CPTYPE_DOUBLE},

    /*************************************************************************/

    /* Size Class */

    /*************************************************************************/

    { "database_size_ratio",                            CPID_FREE_DBSIZE_PER,                   QUERY_SHOW_DATABASES | QUERY_SHOW_TABLE_STATUS, CPTYPE_DOUBLE},

    { "table_size_ratio",                               CPID_FREE_TABSIZE_PER,                  QUERY_SHOW_DATABASES | QUERY_SHOW_TABLE_STATUS, CPTYPE_DOUBLE}