Stackstate-MySQL Integration

Overview

Connect MySQL to Stackstate in order to:

  • Visualize your database performance
  • Correlate the performance of MySQL with the rest of your applications

Installation

  1. Create a stackstate user with replication rights on your MySQL server with the following command, replacing <UNIQUEPASSWORD> with a unique password:

    sudo mysql -e "CREATE USER 'stackstate'@'localhost' IDENTIFIED BY '<UNIQUEPASSWORD>';"
    sudo mysql -e "GRANT REPLICATION CLIENT ON *.* TO 'stackstate'@'localhost' WITH MAX_USER_CONNECTIONS 5;"
    

    If you’d like to get the full metrics catalog please also grant the following privileges:

    sudo mysql -e "GRANT PROCESS ON *.* TO 'stackstate'@'localhost';"
    sudo mysql -e "GRANT SELECT ON performance_schema.* TO 'stackstate'@'localhost';"
    
  2. Verify that the user was created successfully using the following command, replacing <UNIQUEPASSWORD> with the password above:

    mysql -u stackstate --password=<UNIQUEPASSWORD> -e "show status" | \
    grep Uptime && echo -e "\033[0;32mMySQL user - OK\033[0m" || \
    echo -e "\033[0;31mCannot connect to MySQL\033[0m"
    mysql -u stackstate --password=<UNIQUEPASSWORD> -e "show slave status" && \
    echo -e "\033[0;32mMySQL grant - OK\033[0m" || \
    echo -e "\033[0;31mMissing REPLICATION CLIENT grant\033[0m"
    

Configuration

  1. Edit the mysql.yaml file in your agent’s conf.d directory, replacing <UNIQUEPASSWORD> with the password used above.

    init_config:
    
    instances:
      - server: localhost
        user: stackstate
        pass: <UNIQUEPASSWORD>
    
        tags:
            - optional_tag1
            - optional_tag2
        options:
            replication: 0
            galera_cluster: 1
            extra_status_metrics: true
            extra_innodb_metrics: true
            extra_performance_metrics: true
            schema_size_metrics: false
            disable_innodb_metrics: false
    

    Agent 5.7 added a new option: disable_innodb_metrics. This should only be used with older versions of MySQL without innodb engine support.

    See the metrics section below to see a list of the new metrics provided by each of the metric options.

For more details about configuring this integration refer to the following file(s) on GitHub:

Validation

To validate your installation and configuration, restart the agent and execute the info command. The output should contain a section similar to the following:

Checks
======
  [...]
  mysql
  -----
      - instance #0 [OK]
      - Collected 8 metrics & 0 events

Metrics

extra_status_metrics adds the following metrics:  
mysql.binlog.cache_disk_use GAUGE
mysql.binlog.cache_use GAUGE
mysql.performance.handler_commit RATE
mysql.performance.handler_delete RATE
mysql.performance.handler_prepare RATE
mysql.performance.handler_read_first RATE
mysql.performance.handler_read_key RATE
mysql.performance.handler_read_next RATE
mysql.performance.handler_read_prev RATE
mysql.performance.handler_read_rnd RATE
mysql.performance.handler_read_rnd_next RATE
mysql.performance.handler_rollback RATE
mysql.performance.handler_update RATE
mysql.performance.handler_write RATE
mysql.performance.opened_tables RATE
mysql.performance.qcache_total_blocks GAUGE
mysql.performance.qcache_free_blocks GAUGE
mysql.performance.qcache_free_memory GAUGE
mysql.performance.qcache_not_cached RATE
mysql.performance.qcache_queries_in_cache GAUGE
mysql.performance.select_full_join RATE
mysql.performance.select_full_range_join RATE
mysql.performance.select_range RATE
mysql.performance.select_range_check RATE
mysql.performance.select_scan RATE
mysql.performance.sort_merge_passes RATE
mysql.performance.sort_range RATE
mysql.performance.sort_rows RATE
mysql.performance.sort_scan RATE
mysql.performance.table_locks_immediate GAUGE
mysql.performance.table_locks_immediate.rate RATE
mysql.performance.threads_cached GAUGE
mysql.performance.threads_created MONOTONIC
extra_innodb_metrics adds the following metrics:  
mysql.innodb.active_transactions GAUGE
mysql.innodb.buffer_pool_data GAUGE
mysql.innodb.buffer_pool_pages_data GAUGE
mysql.innodb.buffer_pool_pages_dirty GAUGE
mysql.innodb.buffer_pool_pages_flushed RATE
mysql.innodb.buffer_pool_pages_free GAUGE
mysql.innodb.buffer_pool_pages_total GAUGE
mysql.innodb.buffer_pool_read_ahead RATE
mysql.innodb.buffer_pool_read_ahead_evicted RATE
mysql.innodb.buffer_pool_read_ahead_rnd GAUGE
mysql.innodb.buffer_pool_wait_free MONOTONIC
mysql.innodb.buffer_pool_write_requests RATE
mysql.innodb.checkpoint_age GAUGE
mysql.innodb.current_transactions GAUGE
mysql.innodb.data_fsyncs RATE
mysql.innodb.data_pending_fsyncs GAUGE
mysql.innodb.data_pending_reads GAUGE
mysql.innodb.data_pending_writes GAUGE
mysql.innodb.data_read RATE
mysql.innodb.data_written RATE
mysql.innodb.dblwr_pages_written RATE
mysql.innodb.dblwr_writes RATE
mysql.innodb.hash_index_cells_total GAUGE
mysql.innodb.hash_index_cells_used GAUGE
mysql.innodb.history_list_length GAUGE
mysql.innodb.ibuf_free_list GAUGE
mysql.innodb.ibuf_merged RATE
mysql.innodb.ibuf_merged_delete_marks RATE
mysql.innodb.ibuf_merged_deletes RATE
mysql.innodb.ibuf_merged_inserts RATE
mysql.innodb.ibuf_merges RATE
mysql.innodb.ibuf_segment_size GAUGE
mysql.innodb.ibuf_size GAUGE
mysql.innodb.lock_structs RATE
mysql.innodb.locked_tables GAUGE
mysql.innodb.locked_transactions GAUGE
mysql.innodb.log_waits RATE
mysql.innodb.log_write_requests RATE
mysql.innodb.log_writes RATE
mysql.innodb.lsn_current RATE
mysql.innodb.lsn_flushed RATE
mysql.innodb.lsn_last_checkpoint RATE
mysql.innodb.mem_adaptive_hash GAUGE
mysql.innodb.mem_additional_pool GAUGE
mysql.innodb.mem_dictionary GAUGE
mysql.innodb.mem_file_system GAUGE
mysql.innodb.mem_lock_system GAUGE
mysql.innodb.mem_page_hash GAUGE
mysql.innodb.mem_recovery_system GAUGE
mysql.innodb.mem_thread_hash GAUGE
mysql.innodb.mem_total GAUGE
mysql.innodb.os_file_fsyncs RATE
mysql.innodb.os_file_reads RATE
mysql.innodb.os_file_writes RATE
mysql.innodb.os_log_pending_fsyncs GAUGE
mysql.innodb.os_log_pending_writes GAUGE
mysql.innodb.os_log_written RATE
mysql.innodb.pages_created RATE
mysql.innodb.pages_read RATE
mysql.innodb.pages_written RATE
mysql.innodb.pending_aio_log_ios GAUGE
mysql.innodb.pending_aio_sync_ios GAUGE
mysql.innodb.pending_buffer_pool_flushes GAUGE
mysql.innodb.pending_checkpoint_writes GAUGE
mysql.innodb.pending_ibuf_aio_reads GAUGE
mysql.innodb.pending_log_flushes GAUGE
mysql.innodb.pending_log_writes GAUGE
mysql.innodb.pending_normal_aio_reads GAUGE
mysql.innodb.pending_normal_aio_writes GAUGE
mysql.innodb.queries_inside GAUGE
mysql.innodb.queries_queued GAUGE
mysql.innodb.read_views GAUGE
mysql.innodb.rows_deleted RATE
mysql.innodb.rows_inserted RATE
mysql.innodb.rows_read RATE
mysql.innodb.rows_updated RATE
mysql.innodb.s_lock_os_waits RATE
mysql.innodb.s_lock_spin_rounds RATE
mysql.innodb.s_lock_spin_waits RATE
mysql.innodb.semaphore_wait_time GAUGE
mysql.innodb.semaphore_waits GAUGE
mysql.innodb.tables_in_use GAUGE
mysql.innodb.x_lock_os_waits RATE
mysql.innodb.x_lock_spin_rounds RATE
mysql.innodb.x_lock_spin_waits RATE
extra_performance_metrics adds the following metrics:  
mysql.performance.query_run_time.avg GAUGE
mysql.performance.digest_95th_percentile.avg_us GAUGE
schema_size_metrics adds the following metric:  
mysql.info.schema.size GAUGE
mysql.galera.wsrep_cluster_size
(gauge)
The current number of nodes in the Galera cluster.
shown as node
mysql.innodb.buffer_pool_free
(gauge)
The number of free pages in the InnoDB Buffer Pool.
shown as page
mysql.innodb.buffer_pool_total
(gauge)
The total number of pages in the InnoDB Buffer Pool.
shown as page
mysql.innodb.buffer_pool_used
(gauge)
The number of used pages in the InnoDB Buffer Pool.
shown as page
mysql.innodb.buffer_pool_utilization
(gauge)
The utilization of the InnoDB Buffer Pool.
shown as fraction
mysql.innodb.current_row_locks
(gauge)
The number of current row locks.
shown as lock
mysql.innodb.data_reads
(gauge)
The rate of data reads.
shown as read/second
mysql.innodb.data_writes
(gauge)
The rate of data writes.
shown as write/second
mysql.innodb.mutex_os_waits
(gauge)
The rate of mutex OS waits.
shown as event/second
mysql.innodb.mutex_spin_rounds
(gauge)
The rate of mutex spin rounds.
shown as event/second
mysql.innodb.mutex_spin_waits
(gauge)
The rate of mutex spin waits.
shown as event/second
mysql.innodb.os_log_fsyncs
(gauge)
The rate of fsync writes to the log file.
shown as write/second
mysql.innodb.row_lock_time
(gauge)
Fraction of time spent (ms/s) acquring row locks.
shown as fraction
mysql.innodb.row_lock_waits
(gauge)
The number of times per second a row lock had to be waited for.
shown as event/second
mysql.net.connections
(gauge)
The rate of connections to the server.
shown as connection/second
mysql.net.max_connections
(gauge)
The maximum number of connections that have been in use simultaneously since the server started.
shown as connection
mysql.performance.com_delete
(gauge)
The rate of delete statements.
shown as query/second
mysql.performance.com_delete_multi
(gauge)
The rate of delete-multi statements.
shown as query/second
mysql.performance.com_insert
(gauge)
The rate of insert statements.
shown as query/second
mysql.performance.com_insert_select
(gauge)
The rate of insert-select statements.
shown as query/second
mysql.performance.com_replace_select
(gauge)
The rate of replace-select statements.
shown as query/second
mysql.performance.com_select
(gauge)
The rate of select statements.
shown as query/second
mysql.performance.com_update
(gauge)
The rate of update statements.
shown as query/second
mysql.performance.com_update_multi
(gauge)
The rate of update-multi.
shown as query/second
mysql.performance.created_tmp_disk_tables
(gauge)
The rate of internal on-disk temporary tables created by second by the server while executing statements.
shown as table/second
mysql.performance.created_tmp_files
(gauge)
The rate of temporary files created by second.
shown as file/second
mysql.performance.created_tmp_tables
(gauge)
The rate of internal temporary tables created by second by the server while executing statements.
shown as table/second
mysql.performance.kernel_time
(gauge)
Percentage of CPU time spent in kernel space by MySQL.
shown as percent
mysql.performance.key_cache_utilization
(gauge)
The key cache utilization ratio.
shown as fraction
mysql.performance.open_files
(gauge)
The number of open files.
shown as file
mysql.performance.open_tables
(gauge)
The number of of tables that are open.
shown as table
mysql.performance.qcache_hits
(gauge)
The rate of query cache hits.
shown as hit/second
mysql.performance.questions
(gauge)
The rate of statements executed by the server.
shown as query/second
mysql.performance.slow_queries
(gauge)
The rate of slow queries.
shown as query/second
mysql.performance.table_locks_waited
(gauge)
The total number of times that a request for a table lock could not be granted immediately and a wait was needed.
mysql.performance.threads_connected
(gauge)
The number of currently open connections.
shown as connection
mysql.performance.threads_running
(gauge)
The number of threads that are not sleeping.
shown as thread
mysql.performance.user_time
(gauge)
Percentage of CPU time spent in user space by MySQL.
shown as percent
mysql.replication.seconds_behind_master
(gauge)
The lag in seconds between the master and the slave.
shown as second
mysql.replication.slave_running
(gauge)
A boolean showing if this server is a replication slave that is connected to a replication master.
mysql.performance.queries
(gauge)
The rate of queries.
shown as query/second