Skip to content

Latest commit

 

History

History
333 lines (298 loc) · 27 KB

collectd-mysql.md

File metadata and controls

333 lines (298 loc) · 27 KB
Raw

collectd/mysql

Monitor Type: collectd/mysql (Source)

Accepts Endpoints: Yes

Multiple Instances Allowed: Yes

Overview

Monitors a MySQL database server using collectd's MySQL plugin. It supports MySQL versions 5.x or later.

This monitor connects to a MySQL instance and reports on the values returned by a SHOW STATUS command. This includes the following:

  • Number of commands processed
  • Table and row operations (handlers)
  • State of the query cache
  • Status of MySQL threads
  • Network traffic

Note on localhost

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what is expected compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. To ensure that the client makes a TCP/IP connection to the local server specify a host name value of 127.0.0.1, or the IP address or name of the local server.

Databases

You have to specify each database you want to monitor individually under the databases config option. If you have a common authentication to all databases being monitored, you can specify that in the top-level username/password options, otherwise they can be specified at the database level.

InnoDB metrics

If you want to enable InnoDB metrics (innodbStats to true), be sure that you granted to your user the PROCESS privilege.

Example Config

Sample YAML configuration:

monitors:
 - type: collectd/mysql
   host: 127.0.0.1
   port: 3306
   databases:
     - name: dbname
     - name: securedb
       username: admin
       password: s3cr3t
   username: dbuser
   password: passwd

Configuration

To activate this monitor in the Smart Agent, add the following to your agent config:

monitors:  # All monitor config goes under this key
 - type: collectd/mysql
   ...  # Additional config

For a list of monitor options that are common to all monitors, see Common Configuration.

Config option Required Type Description
host yes string
port yes integer
name no string
databases yes list of objects (see below) A list of databases along with optional authentication credentials.
username no string These credentials serve as defaults for all databases if not overridden
password no string
reportHost no bool A SignalFx extension to the plugin that allows us to disable the normal behavior of the MySQL collectd plugin where the host dimension is set to the hostname of the MySQL database server. When false (the recommended and default setting), the globally configured hostname config is used instead. (default: false)
innodbStats no bool (default: false)

The nested databases config object has the following fields:

Config option Required Type Description
name yes string
username no string
password no string

Metrics

These are the metrics available for this monitor. Metrics that are categorized as container/host (default) are in bold and italics in the list below.

  • cache_result.cache_size (gauge)
    MySQL Qcache Size
  • cache_result.qcache-hits (cumulative)
    The number of hits on MySQL query cache.
  • cache_result.qcache-inserts (cumulative)
    The number of inserts into MySQL query cache.
  • cache_result.qcache-not_cached (cumulative)
    The number of MySQL queries that were not cacheable or not cached.
  • cache_result.qcache-prunes (cumulative)
    The number of queries that were pruned from query cache because of low-memory condition.
  • cache_size.qcache (gauge)
    The number of queries in MySQL query cache.
  • mysql_bpool_bytes.data (gauge)
    The total number of bytes in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
  • mysql_bpool_bytes.dirty (gauge)
    The total current number of bytes held in dirty pages in the InnoDB buffer pool.
  • mysql_bpool_counters.pages_flushed (cumulative)
    The number of requests to flush pages from the InnoDB buffer pool.
  • mysql_bpool_counters.read_ahead (cumulative)
    The number of pages read into the InnoDB buffer pool by the read-ahead background thread.
  • mysql_bpool_counters.read_ahead_evicted (cumulative)
    The number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.
  • mysql_bpool_counters.read_ahead_rnd (cumulative)
    The number of “random” read-aheads initiated by InnoDB. This happens when a query scans a large portion of a table but in random order.
  • mysql_bpool_counters.read_requests (cumulative)
    The number of logical read requests.
  • mysql_bpool_counters.reads (cumulative)
    The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.
  • mysql_bpool_counters.wait_free (cumulative)
    Normally, writes to the InnoDB buffer pool happen in the background. When InnoDB needs to read or create a page and no clean pages are available, InnoDB flushes some dirty pages first and waits for that operation to finish. This counter counts instances of these waits.
  • mysql_bpool_counters.write_requests (cumulative)
    The number of writes done to the InnoDB buffer pool.
  • mysql_bpool_pages.data (gauge)
    The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages.
  • mysql_bpool_pages.dirty (gauge)
    The current number of dirty pages in the InnoDB buffer pool.
  • mysql_bpool_pages.free (gauge)
    The number of free pages in the InnoDB buffer pool.
  • mysql_bpool_pages.misc (gauge)
    The number of pages in the InnoDB buffer pool that are busy because they have been allocated for administrative overhead, such as row locks or the adaptive hash index.
  • mysql_bpool_pages.total (gauge)
    The total size of the InnoDB buffer pool, in pages.
  • mysql_commands.admin_commands (cumulative)
    The number of MySQL ADMIN commands executed
  • mysql_commands.alter_db (cumulative)
    The number of MySQL ALTER DB commands executed
  • mysql_commands.alter_db_upgrade (cumulative)
    The number of MySQL ALTER DB UPGRADE commands executed
  • mysql_commands.alter_event (cumulative)
    The number of MySQL ALTER EVENT commands executed
  • mysql_commands.alter_function (cumulative)
    The number of MySQL ALTER FUNCTION commands executed
  • mysql_commands.alter_procedure (cumulative)
    The number of MySQL ALTER PROCEDURE commands executed
  • mysql_commands.alter_server (cumulative)
    The number of MySQL ALTER SERVER commands executed
  • mysql_commands.alter_table (cumulative)
    The number of MySQL ALTER TABLE commands executed
  • mysql_commands.alter_tablespace (cumulative)
    The number of MySQL ALTER TABLESPACE commands executed
  • mysql_commands.alter_user (cumulative)
    The number of MySQL ALTER USER commands executed
  • mysql_commands.analyze (cumulative)
    The number of MySQL ANALYZE commands executed
  • mysql_commands.assign_to_keycache (cumulative)
    The number of MySQL ASSIGN TO KEYCACHE commands executed
  • mysql_commands.begin (cumulative)
    The number of MySQL BEGIN commands executed
  • mysql_commands.binlog (cumulative)
    The number of MySQL BINLOG commands executed
  • mysql_commands.call_procedure (cumulative)
    The number of MySQL CALL PROCEDURE commands executed
  • mysql_commands.change_db (cumulative)
    The number of MySQL CHANGE DB commands executed
  • mysql_commands.change_master (cumulative)
    The number of MySQL CHANGE MASTER commands executed
  • mysql_commands.check (cumulative)
    The number of MySQL CHECK commands executed
  • mysql_commands.checksum (cumulative)
    The number of MySQL CHECKSUM commands executed
  • mysql_commands.commit (cumulative)
    The number of MySQL COMMIT commands executed
  • mysql_commands.create_db (cumulative)
    The number of MySQL CREATE DB commands executed
  • mysql_commands.create_event (cumulative)
    The number of MySQL CREATE EVENT commands executed
  • mysql_commands.create_function (cumulative)
    The number of MySQL CREATE FUNCTION commands executed
  • mysql_commands.create_index (cumulative)
    The number of MySQL CREATE INDEX commands executed
  • mysql_commands.create_procedure (cumulative)
    The number of MySQL CREATE PROCEDURE commands executed
  • mysql_commands.create_server (cumulative)
    The number of MySQL CREATE SERVER commands executed
  • mysql_commands.create_table (cumulative)
    The number of MySQL CREATE TABLE commands executed
  • mysql_commands.create_trigger (cumulative)
    The number of MySQL CREATE TRIGGER commands executed
  • mysql_commands.create_udf (cumulative)
    The number of MySQL CREATE UDF commands executed
  • mysql_commands.create_user (cumulative)
    The number of MySQL CREATE USER commands executed
  • mysql_commands.create_view (cumulative)
    The number of MySQL CREATE VIEW commands executed
  • mysql_commands.dealloc_sql (cumulative)
    The number of MySQL DEALLOC SQL commands executed
  • mysql_commands.delete (cumulative)
    The number of MySQL DELETE commands executed
  • mysql_commands.delete_multi (cumulative)
    The number of MySQL DELETE MULTI commands executed
  • mysql_commands.do (cumulative)
    The number of MySQL DO commands executed
  • mysql_commands.drop_db (cumulative)
    The number of MySQL DROP DB commands executed
  • mysql_commands.drop_event (cumulative)
    The number of MySQL DROP EVENT commands executed
  • mysql_commands.drop_function (cumulative)
    The number of MySQL DROP FUNCTION commands executed
  • mysql_commands.drop_index (cumulative)
    The number of MySQL DROP INDEX commands executed
  • mysql_commands.drop_procedure (cumulative)
    The number of MySQL DROP PROCEDURE commands executed
  • mysql_commands.drop_server (cumulative)
    The number of MySQL DROP SERVER commands executed
  • mysql_commands.drop_table (cumulative)
    The number of MySQL DROP TABLE commands executed
  • mysql_commands.drop_trigger (cumulative)
    The number of MySQL DROP TRIGGER commands executed
  • mysql_commands.drop_user (cumulative)
    The number of MySQL DROP USER commands executed
  • mysql_commands.drop_view (cumulative)
    The number of MySQL DROP VIEW commands executed
  • mysql_commands.empty_query (cumulative)
    The number of MySQL EMPTY QUERY commands executed
  • mysql_commands.execute_sql (cumulative)
    The number of MySQL EXECUTE SQL commands executed
  • mysql_commands.flush (cumulative)
    The number of MySQL FLUSH commands executed
  • mysql_commands.get_diagnostics (cumulative)
    The number of MySQL GET DIAGNOSTICS commands executed
  • mysql_commands.grant (cumulative)
    The number of MySQL GRANT commands executed
  • mysql_commands.ha_close (cumulative)
    The number of MySQL HA CLOSE commands executed
  • mysql_commands.ha_open (cumulative)
    The number of MySQL HA OPEN commands executed
  • mysql_commands.ha_read (cumulative)
    The number of MySQL HA READ commands executed
  • mysql_commands.help (cumulative)
    The number of MySQL HELP commands executed
  • mysql_commands.insert (cumulative)
    The number of MySQL INSERT commands executed
  • mysql_commands.insert_select (cumulative)
    The number of MySQL INSERT SELECT commands executed
  • mysql_commands.install_plugin (cumulative)
    The number of MySQL INSTALL PLUGIN commands executed
  • mysql_commands.kill (cumulative)
    The number of MySQL KILL commands executed
  • mysql_commands.load (cumulative)
    The number of MySQL LOAD commands executed
  • mysql_commands.lock_tables (cumulative)
    The number of MySQL LOCK TABLES commands executed
  • mysql_commands.optimize (cumulative)
    The number of MySQL OPTIMIZE commands executed
  • mysql_commands.preload_keys (cumulative)
    The number of MySQL PRELOAD KEYS commands executed
  • mysql_commands.prepare_sql (cumulative)
    The number of MySQL PREPARE SQL commands executed
  • mysql_commands.purge (cumulative)
    The number of MySQL PURGE commands executed
  • mysql_commands.purge_before_date (cumulative)
    The number of MySQL PURGE BEFORE DATE commands executed
  • mysql_commands.release_savepoint (cumulative)
    The number of MySQL RELEASE SAVEPOINT commands executed
  • mysql_commands.rename_table (cumulative)
    The number of MySQL RENAME TABLE commands executed
  • mysql_commands.rename_user (cumulative)
    The number of MySQL RENAME USER commands executed
  • mysql_commands.repair (cumulative)
    The number of MySQL REPAIR commands executed
  • mysql_commands.replace (cumulative)
    The number of MySQL REPLACE commands executed
  • mysql_commands.replace_select (cumulative)
    The number of MySQL REPLACE SELECT commands executed
  • mysql_commands.reset (cumulative)
    The number of MySQL RESET commands executed
  • mysql_commands.resignal (cumulative)
    The number of MySQL RESIGNAL commands executed
  • mysql_commands.revoke (cumulative)
    The number of MySQL REVOKE commands executed
  • mysql_commands.revoke_all (cumulative)
    The number of MySQL REVOKE ALL commands executed
  • mysql_commands.rollback (cumulative)
    The number of MySQL ROLLBACK commands executed
  • mysql_commands.rollback_to_savepoint (cumulative)
    The number of MySQL ROLLBACK TO SAVEPOINT commands executed
  • mysql_commands.savepoint (cumulative)
    The number of MySQL SAVEPOINT commands executed
  • mysql_commands.select (cumulative)
    The number of MySQL SELECT commands executed
  • mysql_commands.set_option (cumulative)
    The number of MySQL SET OPTION commands executed
  • mysql_commands.show_binlog_events (cumulative)
    The number of MySQL SHOW BINLOG EVENTS commands executed
  • mysql_commands.show_binlogs (cumulative)
    The number of MySQL SHOW BINLOGS commands executed
  • mysql_commands.show_charsets (cumulative)
    The number of MySQL SHOW CHARSETS commands executed
  • mysql_commands.show_collations (cumulative)
    The number of MySQL SHOW COLLATIONS commands executed
  • mysql_commands.show_create_db (cumulative)
    The number of MySQL SHOW CREATE DB commands executed
  • mysql_commands.show_create_event (cumulative)
    The number of MySQL SHOW CREATE EVENT commands executed
  • mysql_commands.show_create_func (cumulative)
    The number of MySQL SHOW CREATE FUNC commands executed
  • mysql_commands.show_create_proc (cumulative)
    The number of MySQL SHOW CREATE PROC commands executed
  • mysql_commands.show_create_table (cumulative)
    The number of MySQL SHOW CREATE TABLE commands executed
  • mysql_commands.show_create_trigger (cumulative)
    The number of MySQL SHOW CREATE TRIGGER commands executed
  • mysql_commands.show_databases (cumulative)
    The number of MySQL SHOW DATABASES commands executed
  • mysql_commands.show_engine_logs (cumulative)
    The number of MySQL SHOW ENGINE LOGS commands executed
  • mysql_commands.show_engine_mutex (cumulative)
    The number of MySQL SHOW ENGINE MUTEX commands executed
  • mysql_commands.show_engine_status (cumulative)
    The number of MySQL SHOW ENGINE STATUS commands executed
  • mysql_commands.show_errors (cumulative)
    The number of MySQL SHOW ERRORS commands executed
  • mysql_commands.show_events (cumulative)
    The number of MySQL SHOW EVENTS commands executed
  • mysql_commands.show_fields (cumulative)
    The number of MySQL SHOW FIELDS commands executed
  • mysql_commands.show_function_code (cumulative)
    The number of MySQL SHOW FUNCTION CODE commands executed
  • mysql_commands.show_function_status (cumulative)
    The number of MySQL SHOW FUNCTION STATUS commands executed
  • mysql_commands.show_grants (cumulative)
    The number of MySQL SHOW GRANTS commands executed
  • mysql_commands.show_keys (cumulative)
    The number of MySQL SHOW KEYS commands executed
  • mysql_commands.show_master_status (cumulative)
    The number of MySQL SHOW MASTER STATUS commands executed
  • mysql_commands.show_open_tables (cumulative)
    The number of MySQL SHOW OPEN TABLES commands executed
  • mysql_commands.show_plugins (cumulative)
    The number of MySQL SHOW PLUGINS commands executed
  • mysql_commands.show_privileges (cumulative)
    The number of MySQL SHOW PRIVILEGES commands executed
  • mysql_commands.show_procedure_code (cumulative)
    The number of MySQL SHOW PROCEDURE CODE commands executed
  • mysql_commands.show_procedure_status (cumulative)
    The number of MySQL SHOW PROCEDURE STATUS commands executed
  • mysql_commands.show_processlist (cumulative)
    The number of MySQL SHOW PROCESSLIST commands executed
  • mysql_commands.show_profile (cumulative)
    The number of MySQL SHOW PROFILE commands executed
  • mysql_commands.show_profiles (cumulative)
    The number of MySQL SHOW PROFILES commands executed
  • mysql_commands.show_relaylog_events (cumulative)
    The number of MySQL SHOW RELAYLOG EVENTS commands executed
  • mysql_commands.show_slave_hosts (cumulative)
    The number of MySQL SHOW SLAVE HOSTS commands executed
  • mysql_commands.show_slave_status (cumulative)
    The number of MySQL SHOW SLAVE STATUS commands executed
  • mysql_commands.show_status (cumulative)
    The number of MySQL SHOW STATUS commands executed
  • mysql_commands.show_storage_engines (cumulative)
    The number of MySQL SHOW STORAGE ENGINES commands executed
  • mysql_commands.show_table_status (cumulative)
    The number of MySQL SHOW TABLE STATUS commands executed
  • mysql_commands.show_tables (cumulative)
    The number of MySQL SHOW TABLES commands executed
  • mysql_commands.show_triggers (cumulative)
    The number of MySQL SHOW TRIGGERS commands executed
  • mysql_commands.show_variables (cumulative)
    The number of MySQL SHOW VARIABLES commands executed
  • mysql_commands.show_warnings (cumulative)
    The number of MySQL SHOW WARNINGS commands executed
  • mysql_commands.signal (cumulative)
    The number of MySQL SIGNAL commands executed
  • mysql_commands.slave_start (cumulative)
    The number of MySQL SLAVE START commands executed
  • mysql_commands.slave_stop (cumulative)
    The number of MySQL SLAVE STOP commands executed
  • mysql_commands.truncate (cumulative)
    The number of MySQL TRUNCATE commands executed
  • mysql_commands.uninstall_plugin (cumulative)
    The number of MySQL UNINSTALL PLUGIN commands executed
  • mysql_commands.unlock_tables (cumulative)
    The number of MySQL UNLOCK TABLES commands executed
  • mysql_commands.update (cumulative)
    The number of MySQL UPDATE commands executed
  • mysql_commands.update_multi (cumulative)
    The number of MySQL UPDATE MULTI commands executed
  • mysql_commands.xa_commit (cumulative)
    The number of MySQL XA COMMIT commands executed
  • mysql_commands.xa_end (cumulative)
    The number of MySQL XA END commands executed
  • mysql_commands.xa_prepare (cumulative)
    The number of MySQL XA PREPARE commands executed
  • mysql_commands.xa_recover (cumulative)
    The number of MySQL XA RECOVER commands executed
  • mysql_commands.xa_rollback (cumulative)
    The number of MySQL XA ROLLBACK commands executed
  • mysql_commands.xa_start (cumulative)
    The number of MySQL XA START commands executed
  • mysql_handler.commit (cumulative)
    The number of internal COMMIT statements.
  • mysql_handler.delete (cumulative)
    The number of times rows have been deleted from tables.
  • mysql_handler.external_lock (cumulative)
    The number of external_lock occurences.
  • mysql_handler.prepare (cumulative)
    The number of times "Prepare" phase was executed in the two-phase commit operations.
  • mysql_handler.read_first (cumulative)
    The number of times the first entry in an index was read.
  • mysql_handler.read_key (cumulative)
    The number of times a row was read based on a key.
  • mysql_handler.read_next (cumulative)
    The number of requests to read the next row in key order.
  • mysql_handler.read_prev (cumulative)
    The number of requests to read the previous row in key order.
  • mysql_handler.read_rnd (cumulative)
    The number of requests that read a random fixed position in the data file.
  • mysql_handler.read_rnd_next (cumulative)
    The number of requests for the next row in the data file.
  • mysql_handler.rollback (cumulative)
    The number of requests for a rollback operation on the storage engine.
  • mysql_handler.savepoint (cumulative)
    The number of requests to place a savepoint on the storage engine. This can be used to roll back later.
  • mysql_handler.savepoint_rollback (cumulative)
    The number of requests to roll back to a savepoint.
  • mysql_handler.update (cumulative)
    The number of requests to update a row in a table.
  • mysql_handler.write (cumulative)
    The number of requests to insert a row in a table.
  • mysql_innodb_data.fsyncs (cumulative)
    The number of fsync() operations so far.
  • mysql_innodb_data.read (cumulative)
    The amount of data read since the server was started (in bytes).
  • mysql_innodb_data.reads (cumulative)
    The total number of data reads (OS file reads).
  • mysql_innodb_data.writes (cumulative)
    The total number of data writes.
  • mysql_innodb_data.written (cumulative)
    The amount of data written so far, in bytes.
  • mysql_innodb_dblwr.writes (cumulative)
    The number of doublewrite operations that have been performed.
  • mysql_innodb_dblwr.written (cumulative)
    The number of pages that have been written to the doublewrite buffer.
  • mysql_innodb_log.fsyncs (cumulative)
    The number of fsync() writes done to the InnoDB redo log files.
  • mysql_innodb_log.waits (cumulative)
    The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.
  • mysql_innodb_log.write_requests (cumulative)
    The number of write requests for the InnoDB redo log.
  • mysql_innodb_log.writes (cumulative)
    The number of physical writes to the InnoDB redo log file.
  • mysql_innodb_log.written (cumulative)
    The number of bytes written to the InnoDB redo log files.
  • mysql_innodb_pages.created (cumulative)
    The number of pages created by operations on InnoDB tables.
  • mysql_innodb_pages.read (cumulative)
    The number of pages read from the InnoDB buffer pool by operations on InnoDB tables.
  • mysql_innodb_pages.written (cumulative)
    The number of pages written by operations on InnoDB tables.
  • mysql_innodb_row_lock.time (cumulative)
    The total time spent in acquiring row locks for InnoDB tables, in milliseconds.
  • mysql_innodb_row_lock.waits (cumulative)
    The number of times operations on InnoDB tables had to wait for a row lock.
  • mysql_innodb_rows.deleted (cumulative)
    The number of rows deleted from InnoDB tables.
  • mysql_innodb_rows.inserted (cumulative)
    The number of rows inserted into InnoDB tables.
  • mysql_innodb_rows.read (cumulative)
    The number of rows read from InnoDB tables.
  • mysql_innodb_rows.updated (cumulative)
    The number of rows updated in InnoDB tables.
  • mysql_locks.immediate (cumulative)
    The number of MySQL table locks which were granted immediately.
  • mysql_locks.waited (cumulative)
    The number of MySQL table locks which had to wait before being granted.
  • mysql_octets.rx (cumulative)
    The number of bytes received by MySQL server from all clients.
  • mysql_octets.tx (cumulative)
    The number of bytes sent by MySQL server to all clients.
  • mysql_select.full_join (cumulative)
    The number of joins that perform full table scans.
  • mysql_select.full_range_join (cumulative)
    The number of joins that used a range search on a reference table.
  • mysql_select.range (cumulative)
    The number of joins that used a range on the first table.
  • mysql_select.range_check (cumulative)
    The number of joins without keys that check for key usage after each row.
  • mysql_select.scan (cumulative)
    The number of joins that did a full scan of the first table.
  • mysql_slow_queries (cumulative)
    The number of queries that have taken more than long_query_time seconds.
  • mysql_sort.range (cumulative)
    The number of sorts that were done using ranges.
  • mysql_sort.scan (cumulative)
    The number of sorts that were done by scanning the table.
  • mysql_sort_merge_passes (cumulative)
    The number of merge passes done by the sorting algorithm.
  • mysql_sort_rows (cumulative)
    The number of rows that were sorted.
  • threads.cached (gauge)
    The number of threads cached by MySQL for re-use on a new client connection. A MySQL thread corresponds to a single MySQL connection.
  • threads.connected (gauge)
    The number of currently open MySQL connections. A MySQL thread corresponds to a single MySQL connection.
  • threads.running (gauge)
    The number of MySQL threads that are processing a query. A MySQL thread corresponds to a single MySQL connection.
  • total_threads.created (cumulative)
    The total number of threads created by MySQL for client connections. A MySQL thread corresponds to a single MySQL connection.

Non-default metrics (version 4.7.0+)

To emit metrics that are not default, you can add those metrics in the generic monitor-level extraMetrics config option. Metrics that are derived from specific configuration options that do not appear in the above list of metrics do not need to be added to extraMetrics.

To see a list of metrics that will be emitted you can run agent-status monitors after configuring this monitor in a running agent instance.