IMPORTANT: No additional bug fixes or documentation updates
will be released for this version. For the latest information, see the
current release documentation.
SQL query metricset
edit
IMPORTANT: This documentation is no longer updated. Refer to Elastic's version policy and the latest documentation.
SQL query metricset
editThis 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 collect rows returned by a query.
Fields names (columns) will be returned as lowercase. Values will be returned as numeric or string.
Fields
editFor 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;"
}
}