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

Setup

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

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.

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

Data Collected

Metrics

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
mysql.innodb.data_writes
(gauge)
The rate of data writes.
shown as write
mysql.innodb.mutex_os_waits
(gauge)
The rate of mutex OS waits.
shown as event
mysql.innodb.mutex_spin_rounds
(gauge)
The rate of mutex spin rounds.
shown as event
mysql.innodb.mutex_spin_waits
(gauge)
The rate of mutex spin waits.
shown as event
mysql.innodb.os_log_fsyncs
(gauge)
The rate of fsync writes to the log file.
shown as write
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
mysql.net.connections
(gauge)
The rate of connections to the server.
shown as connection
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
mysql.performance.com_delete_multi
(gauge)
The rate of delete-multi statements.
shown as query
mysql.performance.com_insert
(gauge)
The rate of insert statements.
shown as query
mysql.performance.com_insert_select
(gauge)
The rate of insert-select statements.
shown as query
mysql.performance.com_replace_select
(gauge)
The rate of replace-select statements.
shown as query
mysql.performance.com_select
(gauge)
The rate of select statements.
shown as query
mysql.performance.com_update
(gauge)
The rate of update statements.
shown as query
mysql.performance.com_update_multi
(gauge)
The rate of update-multi.
shown as query
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
mysql.performance.created_tmp_files
(gauge)
The rate of temporary files created by second.
shown as file
mysql.performance.created_tmp_tables
(gauge)
The rate of internal temporary tables created by second by the server while executing statements.
shown as table
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
mysql.performance.questions
(gauge)
The rate of statements executed by the server.
shown as query
mysql.performance.slow_queries
(gauge)
The rate of slow queries.
shown as query
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.
shown as
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.
shown as
mysql.replication.slaves_connected
(gauge)
Number of slaves connected to a replication master.
shown as
mysql.performance.queries
(gauge)
The rate of queries.
shown as query
extra_status_metrics adds the following metrics:
mysql.binlog.cache_disk_useGAUGE
mysql.binlog.cache_useGAUGE
mysql.performance.handler_commitRATE
mysql.performance.handler_deleteRATE
mysql.performance.handler_prepareRATE
mysql.performance.handler_read_firstRATE
mysql.performance.handler_read_keyRATE
mysql.performance.handler_read_nextRATE
mysql.performance.handler_read_prevRATE
mysql.performance.handler_read_rndRATE
mysql.performance.handler_read_rnd_nextRATE
mysql.performance.handler_rollbackRATE
mysql.performance.handler_updateRATE
mysql.performance.handler_writeRATE
mysql.performance.opened_tablesRATE
mysql.performance.qcache_total_blocksGAUGE
mysql.performance.qcache_free_blocksGAUGE
mysql.performance.qcache_free_memoryGAUGE
mysql.performance.qcache_not_cachedRATE
mysql.performance.qcache_queries_in_cacheGAUGE
mysql.performance.select_full_joinRATE
mysql.performance.select_full_range_joinRATE
mysql.performance.select_rangeRATE
mysql.performance.select_range_checkRATE
mysql.performance.select_scanRATE
mysql.performance.sort_merge_passesRATE
mysql.performance.sort_rangeRATE
mysql.performance.sort_rowsRATE
mysql.performance.sort_scanRATE
mysql.performance.table_locks_immediateGAUGE
mysql.performance.table_locks_immediate.rateRATE
mysql.performance.threads_cachedGAUGE
mysql.performance.threads_createdMONOTONIC
extra_innodb_metrics adds the following metrics:
mysql.innodb.active_transactionsGAUGE
mysql.innodb.buffer_pool_dataGAUGE
mysql.innodb.buffer_pool_pages_dataGAUGE
mysql.innodb.buffer_pool_pages_dirtyGAUGE
mysql.innodb.buffer_pool_pages_flushedRATE
mysql.innodb.buffer_pool_pages_freeGAUGE
mysql.innodb.buffer_pool_pages_totalGAUGE
mysql.innodb.buffer_pool_read_aheadRATE
mysql.innodb.buffer_pool_read_ahead_evictedRATE
mysql.innodb.buffer_pool_read_ahead_rndGAUGE
mysql.innodb.buffer_pool_wait_freeMONOTONIC
mysql.innodb.buffer_pool_write_requestsRATE
mysql.innodb.checkpoint_ageGAUGE
mysql.innodb.current_transactionsGAUGE
mysql.innodb.data_fsyncsRATE
mysql.innodb.data_pending_fsyncsGAUGE
mysql.innodb.data_pending_readsGAUGE
mysql.innodb.data_pending_writesGAUGE
mysql.innodb.data_readRATE
mysql.innodb.data_writtenRATE
mysql.innodb.dblwr_pages_writtenRATE
mysql.innodb.dblwr_writesRATE
mysql.innodb.hash_index_cells_totalGAUGE
mysql.innodb.hash_index_cells_usedGAUGE
mysql.innodb.history_list_lengthGAUGE
mysql.innodb.ibuf_free_listGAUGE
mysql.innodb.ibuf_mergedRATE
mysql.innodb.ibuf_merged_delete_marksRATE
mysql.innodb.ibuf_merged_deletesRATE
mysql.innodb.ibuf_merged_insertsRATE
mysql.innodb.ibuf_mergesRATE
mysql.innodb.ibuf_segment_sizeGAUGE
mysql.innodb.ibuf_sizeGAUGE
mysql.innodb.lock_structsRATE
mysql.innodb.locked_tablesGAUGE
mysql.innodb.locked_transactionsGAUGE
mysql.innodb.log_waitsRATE
mysql.innodb.log_write_requestsRATE
mysql.innodb.log_writesRATE
mysql.innodb.lsn_currentRATE
mysql.innodb.lsn_flushedRATE
mysql.innodb.lsn_last_checkpointRATE
mysql.innodb.mem_adaptive_hashGAUGE
mysql.innodb.mem_additional_poolGAUGE
mysql.innodb.mem_dictionaryGAUGE
mysql.innodb.mem_file_systemGAUGE
mysql.innodb.mem_lock_systemGAUGE
mysql.innodb.mem_page_hashGAUGE
mysql.innodb.mem_recovery_systemGAUGE
mysql.innodb.mem_thread_hashGAUGE
mysql.innodb.mem_totalGAUGE
mysql.innodb.os_file_fsyncsRATE
mysql.innodb.os_file_readsRATE
mysql.innodb.os_file_writesRATE
mysql.innodb.os_log_pending_fsyncsGAUGE
mysql.innodb.os_log_pending_writesGAUGE
mysql.innodb.os_log_writtenRATE
mysql.innodb.pages_createdRATE
mysql.innodb.pages_readRATE
mysql.innodb.pages_writtenRATE
mysql.innodb.pending_aio_log_iosGAUGE
mysql.innodb.pending_aio_sync_iosGAUGE
mysql.innodb.pending_buffer_pool_flushesGAUGE
mysql.innodb.pending_checkpoint_writesGAUGE
mysql.innodb.pending_ibuf_aio_readsGAUGE
mysql.innodb.pending_log_flushesGAUGE
mysql.innodb.pending_log_writesGAUGE
mysql.innodb.pending_normal_aio_readsGAUGE
mysql.innodb.pending_normal_aio_writesGAUGE
mysql.innodb.queries_insideGAUGE
mysql.innodb.queries_queuedGAUGE
mysql.innodb.read_viewsGAUGE
mysql.innodb.rows_deletedRATE
mysql.innodb.rows_insertedRATE
mysql.innodb.rows_readRATE
mysql.innodb.rows_updatedRATE
mysql.innodb.s_lock_os_waitsRATE
mysql.innodb.s_lock_spin_roundsRATE
mysql.innodb.s_lock_spin_waitsRATE
mysql.innodb.semaphore_wait_timeGAUGE
mysql.innodb.semaphore_waitsGAUGE
mysql.innodb.tables_in_useGAUGE
mysql.innodb.x_lock_os_waitsRATE
mysql.innodb.x_lock_spin_roundsRATE
mysql.innodb.x_lock_spin_waitsRATE
extra_performance_metrics adds the following metrics:
mysql.performance.query_run_time.avgGAUGE
mysql.performance.digest_95th_percentile.avg_usGAUGE
schema_size_metrics adds the following metric:
mysql.info.schema.sizeGAUGE