SQL query metricset

edit

This functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.

The sql query metricset collects rows returned by a query.

Field names (columns) are returned as lowercase strings. Values are returned as numeric or string.

Fields

edit

For a description of each field in the metricset, see the exported fields section.

Here is an example document generated by this metricset:

{
    "@timestamp": "2017-10-12T08:05:34.853Z",
    "event": {
        "dataset": "sql.query",
        "duration": 115000,
        "module": "sql"
    },
    "metricset": {
        "name": "query",
        "period": 10000
    },
    "service": {
        "address": "172.21.0.2:3306",
        "type": "sql"
    },
    "sql": {
        "driver": "mysql",
        "metrics": {
            "numeric": {
                "aborted_clients": 0,
                "aborted_connects": 0,
                "binlog_cache_disk_use": 0,
                "binlog_cache_use": 0,
                "binlog_stmt_cache_disk_use": 0,
                "binlog_stmt_cache_use": 0,
                "bytes_received": 106,
                "bytes_sent": 100,
                "com_admin_commands": 1,
                "com_alter_db": 0,
                "com_alter_db_upgrade": 0,
                "com_alter_event": 0,
                "com_alter_function": 0,
                "com_alter_instance": 0,
                "com_alter_procedure": 0,
                "com_alter_server": 0,
                "com_alter_table": 0,
                "com_alter_tablespace": 0,
                "com_alter_user": 0,
                "com_analyze": 0,
                "com_assign_to_keycache": 0,
                "com_begin": 0,
                "com_binlog": 0,
                "com_call_procedure": 0,
                "com_change_db": 0,
                "com_change_master": 0,
                "com_change_repl_filter": 0,
                "com_check": 0,
                "com_checksum": 0,
                "com_commit": 0,
                "com_create_db": 0,
                "com_create_event": 0,
                "com_create_function": 0,
                "com_create_index": 0,
                "com_create_procedure": 0,
                "com_create_server": 0,
                "com_create_table": 0,
                "com_create_trigger": 0,
                "com_create_udf": 0,
                "com_create_user": 0,
                "com_create_view": 0,
                "com_dealloc_sql": 0,
                "com_delete": 0,
                "com_delete_multi": 0,
                "com_do": 0,
                "com_drop_db": 0,
                "com_drop_event": 0,
                "com_drop_function": 0,
                "com_drop_index": 0,
                "com_drop_procedure": 0,
                "com_drop_server": 0,
                "com_drop_table": 0,
                "com_drop_trigger": 0,
                "com_drop_user": 0,
                "com_drop_view": 0,
                "com_empty_query": 0,
                "com_execute_sql": 0,
                "com_explain_other": 0,
                "com_flush": 0,
                "com_get_diagnostics": 0,
                "com_grant": 0,
                "com_group_replication_start": 0,
                "com_group_replication_stop": 0,
                "com_ha_close": 0,
                "com_ha_open": 0,
                "com_ha_read": 0,
                "com_help": 0,
                "com_insert": 0,
                "com_insert_select": 0,
                "com_install_plugin": 0,
                "com_kill": 0,
                "com_load": 0,
                "com_lock_tables": 0,
                "com_optimize": 0,
                "com_preload_keys": 0,
                "com_prepare_sql": 0,
                "com_purge": 0,
                "com_purge_before_date": 0,
                "com_release_savepoint": 0,
                "com_rename_table": 0,
                "com_rename_user": 0,
                "com_repair": 0,
                "com_replace": 0,
                "com_replace_select": 0,
                "com_reset": 0,
                "com_resignal": 0,
                "com_revoke": 0,
                "com_revoke_all": 0,
                "com_rollback": 0,
                "com_rollback_to_savepoint": 0,
                "com_savepoint": 0,
                "com_select": 0,
                "com_set_option": 0,
                "com_show_binlog_events": 0,
                "com_show_binlogs": 0,
                "com_show_charsets": 0,
                "com_show_collations": 0,
                "com_show_create_db": 0,
                "com_show_create_event": 0,
                "com_show_create_func": 0,
                "com_show_create_proc": 0,
                "com_show_create_table": 0,
                "com_show_create_trigger": 0,
                "com_show_create_user": 0,
                "com_show_databases": 0,
                "com_show_engine_logs": 0,
                "com_show_engine_mutex": 0,
                "com_show_engine_status": 0,
                "com_show_errors": 0,
                "com_show_events": 0,
                "com_show_fields": 0,
                "com_show_function_code": 0,
                "com_show_function_status": 0,
                "com_show_grants": 0,
                "com_show_keys": 0,
                "com_show_master_status": 0,
                "com_show_open_tables": 0,
                "com_show_plugins": 0,
                "com_show_privileges": 0,
                "com_show_procedure_code": 0,
                "com_show_procedure_status": 0,
                "com_show_processlist": 0,
                "com_show_profile": 0,
                "com_show_profiles": 0,
                "com_show_relaylog_events": 0,
                "com_show_slave_hosts": 0,
                "com_show_slave_status": 0,
                "com_show_status": 1,
                "com_show_storage_engines": 0,
                "com_show_table_status": 0,
                "com_show_tables": 0,
                "com_show_triggers": 0,
                "com_show_variables": 0,
                "com_show_warnings": 0,
                "com_shutdown": 0,
                "com_signal": 0,
                "com_slave_start": 0,
                "com_slave_stop": 0,
                "com_stmt_close": 0,
                "com_stmt_execute": 0,
                "com_stmt_fetch": 0,
                "com_stmt_prepare": 0,
                "com_stmt_reprepare": 0,
                "com_stmt_reset": 0,
                "com_stmt_send_long_data": 0,
                "com_truncate": 0,
                "com_uninstall_plugin": 0,
                "com_unlock_tables": 0,
                "com_update": 0,
                "com_update_multi": 0,
                "com_xa_commit": 0,
                "com_xa_end": 0,
                "com_xa_prepare": 0,
                "com_xa_recover": 0,
                "com_xa_rollback": 0,
                "com_xa_start": 0,
                "connection_errors_accept": 0,
                "connection_errors_internal": 0,
                "connection_errors_max_connections": 0,
                "connection_errors_peer_address": 0,
                "connection_errors_select": 0,
                "connection_errors_tcpwrap": 0,
                "connections": 8,
                "created_tmp_disk_tables": 0,
                "created_tmp_files": 6,
                "created_tmp_tables": 0,
                "delayed_errors": 0,
                "delayed_insert_threads": 0,
                "delayed_writes": 0,
                "flush_commands": 1,
                "handler_commit": 0,
                "handler_delete": 0,
                "handler_discover": 0,
                "handler_external_lock": 0,
                "handler_mrr_init": 0,
                "handler_prepare": 0,
                "handler_read_first": 0,
                "handler_read_key": 0,
                "handler_read_last": 0,
                "handler_read_next": 0,
                "handler_read_prev": 0,
                "handler_read_rnd": 0,
                "handler_read_rnd_next": 0,
                "handler_rollback": 0,
                "handler_savepoint": 0,
                "handler_savepoint_rollback": 0,
                "handler_update": 0,
                "handler_write": 0,
                "innodb_available_undo_logs": 128,
                "innodb_buffer_pool_bytes_data": 8011776,
                "innodb_buffer_pool_bytes_dirty": 81920,
                "innodb_buffer_pool_pages_data": 489,
                "innodb_buffer_pool_pages_dirty": 5,
                "innodb_buffer_pool_pages_flushed": 41,
                "innodb_buffer_pool_pages_free": 7702,
                "innodb_buffer_pool_pages_misc": 0,
                "innodb_buffer_pool_pages_total": 8191,
                "innodb_buffer_pool_read_ahead": 0,
                "innodb_buffer_pool_read_ahead_evicted": 0,
                "innodb_buffer_pool_read_ahead_rnd": 0,
                "innodb_buffer_pool_read_requests": 2476,
                "innodb_buffer_pool_reads": 453,
                "innodb_buffer_pool_wait_free": 0,
                "innodb_buffer_pool_write_requests": 1009,
                "innodb_data_fsyncs": 7,
                "innodb_data_pending_fsyncs": 0,
                "innodb_data_pending_reads": 0,
                "innodb_data_pending_writes": 0,
                "innodb_data_read": 7492096,
                "innodb_data_reads": 484,
                "innodb_data_writes": 58,
                "innodb_data_written": 706560,
                "innodb_dblwr_pages_written": 2,
                "innodb_dblwr_writes": 1,
                "innodb_log_waits": 0,
                "innodb_log_write_requests": 0,
                "innodb_log_writes": 2,
                "innodb_num_open_files": 23,
                "innodb_os_log_fsyncs": 4,
                "innodb_os_log_pending_fsyncs": 0,
                "innodb_os_log_pending_writes": 0,
                "innodb_os_log_written": 1024,
                "innodb_page_size": 16384,
                "innodb_pages_created": 37,
                "innodb_pages_read": 452,
                "innodb_pages_written": 41,
                "innodb_row_lock_current_waits": 0,
                "innodb_row_lock_time": 0,
                "innodb_row_lock_time_avg": 0,
                "innodb_row_lock_time_max": 0,
                "innodb_row_lock_waits": 0,
                "innodb_rows_deleted": 0,
                "innodb_rows_inserted": 0,
                "innodb_rows_read": 8,
                "innodb_rows_updated": 0,
                "innodb_truncated_status_writes": 0,
                "key_blocks_not_flushed": 0,
                "key_blocks_unused": 6693,
                "key_blocks_used": 5,
                "key_read_requests": 38,
                "key_reads": 5,
                "key_write_requests": 0,
                "key_writes": 0,
                "last_query_cost": 0,
                "last_query_partial_plans": 0,
                "locked_connects": 0,
                "max_execution_time_exceeded": 0,
                "max_execution_time_set": 0,
                "max_execution_time_set_failed": 0,
                "max_used_connections": 1,
                "not_flushed_delayed_rows": 0,
                "ongoing_anonymous_transaction_count": 0,
                "open_files": 31,
                "open_streams": 0,
                "open_table_definitions": 219,
                "open_tables": 352,
                "opened_files": 384,
                "opened_table_definitions": 0,
                "opened_tables": 0,
                "performance_schema_accounts_lost": 0,
                "performance_schema_cond_classes_lost": 0,
                "performance_schema_cond_instances_lost": 0,
                "performance_schema_digest_lost": 0,
                "performance_schema_file_classes_lost": 0,
                "performance_schema_file_handles_lost": 0,
                "performance_schema_file_instances_lost": 0,
                "performance_schema_hosts_lost": 0,
                "performance_schema_index_stat_lost": 0,
                "performance_schema_locker_lost": 0,
                "performance_schema_memory_classes_lost": 0,
                "performance_schema_metadata_lock_lost": 0,
                "performance_schema_mutex_classes_lost": 0,
                "performance_schema_mutex_instances_lost": 0,
                "performance_schema_nested_statement_lost": 0,
                "performance_schema_prepared_statements_lost": 0,
                "performance_schema_program_lost": 0,
                "performance_schema_rwlock_classes_lost": 0,
                "performance_schema_rwlock_instances_lost": 0,
                "performance_schema_session_connect_attrs_lost": 0,
                "performance_schema_socket_classes_lost": 0,
                "performance_schema_socket_instances_lost": 0,
                "performance_schema_stage_classes_lost": 0,
                "performance_schema_statement_classes_lost": 0,
                "performance_schema_table_handles_lost": 0,
                "performance_schema_table_instances_lost": 0,
                "performance_schema_table_lock_stat_lost": 0,
                "performance_schema_thread_classes_lost": 0,
                "performance_schema_thread_instances_lost": 0,
                "performance_schema_users_lost": 0,
                "prepared_stmt_count": 0,
                "qcache_free_blocks": 1,
                "qcache_free_memory": 1031832,
                "qcache_hits": 0,
                "qcache_inserts": 0,
                "qcache_lowmem_prunes": 0,
                "qcache_not_cached": 4,
                "qcache_queries_in_cache": 0,
                "qcache_total_blocks": 1,
                "queries": 18,
                "questions": 1,
                "select_full_join": 0,
                "select_full_range_join": 0,
                "select_range": 0,
                "select_range_check": 0,
                "select_scan": 0,
                "slave_open_temp_tables": 0,
                "slow_launch_threads": 0,
                "slow_queries": 0,
                "sort_merge_passes": 0,
                "sort_range": 0,
                "sort_rows": 0,
                "sort_scan": 0,
                "ssl_accept_renegotiates": 0,
                "ssl_accepts": 0,
                "ssl_callback_cache_hits": 0,
                "ssl_client_connects": 0,
                "ssl_connect_renegotiates": 0,
                "ssl_ctx_verify_depth": 0,
                "ssl_ctx_verify_mode": 0,
                "ssl_default_timeout": 0,
                "ssl_finished_accepts": 0,
                "ssl_finished_connects": 0,
                "ssl_session_cache_hits": 0,
                "ssl_session_cache_misses": 0,
                "ssl_session_cache_overflows": 0,
                "ssl_session_cache_size": 0,
                "ssl_session_cache_timeouts": 0,
                "ssl_sessions_reused": 0,
                "ssl_used_session_cache_entries": 0,
                "ssl_verify_depth": 0,
                "ssl_verify_mode": 0,
                "table_locks_immediate": 118,
                "table_locks_waited": 0,
                "table_open_cache_hits": 0,
                "table_open_cache_misses": 0,
                "table_open_cache_overflows": 0,
                "tc_log_max_pages_used": 0,
                "tc_log_page_size": 0,
                "tc_log_page_waits": 0,
                "threads_cached": 0,
                "threads_connected": 1,
                "threads_created": 1,
                "threads_running": 1,
                "uptime": 4,
                "uptime_since_flush_status": 4
            },
            "string": {
                "compression": "OFF",
                "innodb_buffer_pool_dump_status": "Dumping of buffer pool not started",
                "innodb_buffer_pool_load_status": "Buffer pool(s) load completed at 200126 13:05:12",
                "innodb_buffer_pool_resize_status": "",
                "max_used_connections_time": "2020-01-26 13:05:13",
                "ssl_cipher": "",
                "ssl_cipher_list": "",
                "ssl_server_not_after": "",
                "ssl_server_not_before": "",
                "ssl_session_cache_mode": "NONE",
                "ssl_version": ""
            }
        },
        "query": "show status;"
    }
}