Protocol Integration

MySQL protocol metrics (throughput, latency, errors) are captured out-of-the-box by collectors running with EPOCH_ANALYSIS_DEPTH=layer7 set in their configuration.

The user can either look at pre-canned dashboards for MySQL or use the Analytics Sandbox to plot charts without any additional configuration.

Note: In order to see additional infrastructure metrics the user can follow the steps listed under the Configuration section below (Optional).

Attributes

Below attributes can be used in filters and group by operations in analytics within alerts, dashboards, etc.

API Usage Name	UI Display Name	Description	Value / Example
mysql.db	Schema Name	Name of the database
mysql.user	Username	User name for the database connection
mysql.query	Query String	Full query statements captured as strings	SELECT itemid FROM storagedb
mysql.query.type	Query Type	Query statement type	INSERT, SELECT
mysql.server_version	Server Version	MySQL server version number	5.7.17
mysql.error.code	Server error code	Numeric error code	In this error message: ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist, the error code is 1146. see full list of error codes
mysql.error.string	Server error string	Textual description of error	In this error message: ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist, error string is Table 'test.no_such_table' doesn't exist

There are some attributes that are common across the integrations. The description to the relevant attributes for this integration can be found here: shared attributes

Protocol Datasources

Datasource	Available Aggregations	Unit	Description
mysql.request_response.latency	sum avg min max std_dev	millisecond	The aggregated latency across all MySQL request/response.
mysql.request_response.throughput	throughput	/s	The total number of MySQL request/response per second.
mysql.request_response.count	count		The total number of MySQL request/response.
mysql.request.bytes	sum avg min max	byte	The aggregated number of bytes across all MySQL requests.
mysql.request.bytes.rate	rate	byte/s	The rate of bytes transferred across all MySQL requests.
mysql.request.packets	sum avg min max	packet	The aggregated number of packets across all MySQL requests.
mysql.request.packets.rate	rate	packet/s	The rate of packets transferred across all MySQL requests.
mysql.request.count	count		The total number of MySQL requests.
mysql.request.throughput	throughput	/s	The rate of total number of MySQL requests.
mysql.response.bytes	sum avg min max	byte	The aggregated number of bytes across all MySQL responses.
mysql.response.bytes.rate	rate	byte/s	The rate of bytes transferred across all MySQL responses.
mysql.response.packets	sum avg min max	packet	The aggregated number of packets across all MySQL responses.
mysql.response.packets.rate	rate	packet/s	The rate of packets transferred across all MySQL responses.
mysql.response.count	count		The total number of MySQL responses.
mysql.response.throughput	throughput	/s	The rate of total number of MySQL responses.

---

Infrastructure Integration

Configuration:

Note: Epoch can provide stats for MySQL queries via protocol parsing without any configuration. This protocol parsing is supported only when MySQL clients connects to MySQL server with SSL mode disabled. The following steps are required to poll for stats reported by the MySQL server itself.

1. Create a epoch user with replication rights in your MySQL server

   sudo mysql -e "CREATE USER 'epoch'@'%%' IDENTIFIED BY 'INSERT PASSWORD';"
   sudo mysql -e "GRANT REPLICATION CLIENT ON *.* TO 'epoch'@'%%' 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 'epoch'@'%%';"
   sudo mysql -e "GRANT SELECT ON performance_schema.* TO 'epoch'@'%%';"
   
   mysql -u epoch --password='INSERT PASSWORD' -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 epoch --password='INSERT PASSWORD' -e "show slave status" && \
   echo -e "\033[0;32mMySQL grant - OK\033[0m" || \
   echo -e "\033[0;31mMissing REPLICATION CLIENT grant\033[0m"
   

   If you have also granted additional privileges, verify them with:

   mysql -u epoch --password='INSERT PASSWORD' -e "SELECT * FROM performance_schema.threads" && \
   echo -e "\033[0;32mMySQL SELECT grant - OK\033[0m" || \
   echo -e "\033[0;31mMissing SELECT grant\033[0m"
   mysql -u epoch --password='INSERT PASSWORD' -e "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST" && \
   echo -e "\033[0;32mMySQL PROCESS grant - OK\033[0m" || \
   echo -e "\033[0;31mMissing PROCESS grant\033[0m"
   

2. Configure the Agent to connect to MySQL by editing /etc/nutanix/epoch-dd-agent/conf.d/mysql.yaml

   init_config:
   instances:
     - server: localhost
       user: epoch
       pass: 'INSERT PASSWORD'
       tags:
           - optional_tag1
           - optional_tag2
       options:
           replication: 0
           galera_cluster: 1
   

3. Restart the Agent

   /etc/init.d/epoch-collectors restart
   

4. Execute the info command and verify that the integration check has passed.

   /etc/init.d/epoch-collectors info
   

   The output of the command should contain a section similar to the following:

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

Infrastructure Datasources

Datasource	Available Aggregations	Unit	Description
mysql.galera.wsrep_cluster_size	avg max min sum	node	The current number of nodes in the Galera cluster.
mysql.innodb.buffer_pool_free	avg max min sum	page	The number of free pages in the InnoDB Buffer Pool.
mysql.innodb.buffer_pool_total	avg max min sum	page	The total number of pages in the InnoDB Buffer Pool.
mysql.innodb.buffer_pool_used	avg max min sum	page	The number of used pages in the InnoDB Buffer Pool.
mysql.innodb.buffer_pool_utilization	avg max min sum	fraction	The utilization of the InnoDB Buffer Pool.
mysql.innodb.current_row_locks	avg max min sum	lock	The number of current row locks.
mysql.innodb.data_reads	avg max min sum	read/second	The rate of data reads.
mysql.innodb.data_writes	avg max min sum	write/second	The rate of data writes.
mysql.innodb.mutex_os_waits	avg max min sum	event/second	The rate of mutex OS waits.
mysql.innodb.mutex_spin_rounds	avg max min sum	event/second	The rate of mutex spin rounds.
mysql.innodb.mutex_spin_waits	avg max min sum	event/second	The rate of mutex spin waits.
mysql.innodb.os_log_fsyncs	avg max min sum	write/second	The rate of fsync writes to the log file.
mysql.innodb.row_lock_time	avg max min sum	fraction	Fraction of time spent (ms/s) acquring row locks.
mysql.innodb.row_lock_waits	avg max min sum	event/second	The number of times per second a row lock had to be waited for.
mysql.net.connections	avg max min sum	connection/second	The rate of connections to the server.
mysql.net.max_connections	avg max min sum	connection	The maximum number of connections that have been in use simultaneously since the server started.
mysql.performance.com_delete	avg max min sum	query/second	The rate of delete statements.
mysql.performance.com_delete_multi	avg max min sum	query/second	The rate of delete-multi statements.
mysql.performance.com_insert	avg max min sum	query/second	The rate of insert statements.
mysql.performance.com_insert_select	avg max min sum	query/second	The rate of insert-select statements.
mysql.performance.com_replace_select	avg max min sum	query/second	The rate of replace-select statements.
mysql.performance.com_select	avg max min sum	query/second	The rate of select statements.
mysql.performance.com_update	avg max min sum	query/second	The rate of update statements.
mysql.performance.com_update_multi	avg max min sum	query/second	The rate of update-multi.
mysql.performance.created_tmp_disk_tables	avg max min sum	table/second	The rate of internal on-disk temporary tables created by second by the server while executing statements.
mysql.performance.created_tmp_files	avg max min sum	file/second	The rate of temporary files created by second.
mysql.performance.created_tmp_tables	avg max min sum	table/second	The rate of internal temporary tables created by second by the server while executing statements.
mysql.performance.kernel_time	avg max min sum	percent	Percentage of CPU time spent in kernel space by MySQL.
mysql.performance.key_cache_utilization	avg max min sum	fraction	The key cache utilization ratio.
mysql.performance.open_files	avg max min sum	file	The number of open files.
mysql.performance.open_tables	avg max min sum	table	The number of of tables that are open.
mysql.performance.qcache_hits	avg max min sum	hit/second	The rate of query cache hits.
mysql.performance.questions	avg max min sum	query/second	The rate of statements executed by the server.
mysql.performance.slow_queries	avg max min sum	query/second	The rate of slow queries.
mysql.performance.table_locks_waited	avg max min sum		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	avg max min sum	connection	The number of currently open connections.
mysql.performance.threads_running	avg max min sum	thread	The number of threads that are not sleeping.
mysql.performance.user_time	avg max min sum	percent	Percentage of CPU time spent in user space by MySQL.
mysql.replication.seconds_behind_master	avg max min sum	second	The lag in seconds between the master and the slave.
mysql.replication.slave_running	avg max min sum		A boolean showing if this server is a replication slave that is connected to a replication master.
mysql.replication.slaves_connected	avg max min sum		Number of slaves connected to a replication master.
mysql.performance.queries	avg max min sum	query/second	The rate of queries.