[collectd] [PATCH] Added some key InnoDB metrics that I need to track.
Gregory Haase
greg at taximagic.com
Tue Sep 18 09:53:51 CEST 2012
---
src/mysql.c | 195 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
src/types.db | 5 ++
2 files changed, 200 insertions(+)
diff --git a/src/mysql.c b/src/mysql.c
index 32b352b..3b7ffdf 100644
--- a/src/mysql.c
+++ b/src/mysql.c
@@ -31,6 +31,7 @@
#include "common.h"
#include "plugin.h"
#include "configfile.h"
+#include "string.h"
#ifdef HAVE_MYSQL_H
#include <mysql.h>
@@ -52,6 +53,7 @@ struct mysql_database_s /* {{{ */
_Bool master_stats;
_Bool slave_stats;
+ _Bool innodb_stats;
_Bool slave_notif;
_Bool slave_io_running;
@@ -169,6 +171,8 @@ static int mysql_config_database (oconfig_item_t *ci) /* {{{ */
status = cf_util_get_boolean (child, &db->slave_stats);
else if (strcasecmp ("SlaveNotifications", child->key) == 0)
status = cf_util_get_boolean (child, &db->slave_notif);
+ else if (strcasecmp ("InnoDBStats", child->key) == 0)
+ status = cf_util_get_boolean (child, &db->innodb_stats);
else
{
WARNING ("mysql plugin: Option `%s' not allowed here.", child->key);
@@ -383,6 +387,48 @@ static MYSQL_RES *exec_query (MYSQL *con, const char *query)
return (res);
} /* exec_query */
+static int split_row (char *string, char **lines, size_t size)
+{
+ int i;
+ char *ptr;
+ char *saveptr;
+
+ i = 0;
+ ptr = string;
+ saveptr = NULL;
+ while ((lines[i] = strtok_r (ptr, "\n", &saveptr)) != NULL)
+ {
+ ptr = NULL;
+ i++;
+ if (i >= size)
+ break;
+ }
+
+ return i;
+} /* split_row */
+
+static int split_line (char *string, char **fields, size_t size)
+{
+ size_t i;
+ char *ptr;
+ char *saveptr;
+
+ i = 0;
+ ptr = string;
+ saveptr = NULL;
+ while ((fields[i] = strtok_r (ptr, ", ", &saveptr)) != NULL)
+ {
+ ptr = NULL;
+ i++;
+
+ if (i >= size)
+ break;
+ }
+
+ return i;
+} /* split_line */
+
+
static int mysql_read_master_stats (mysql_database_t *db, MYSQL *con)
{
MYSQL_RES *res;
@@ -548,6 +594,152 @@ static int mysql_read_slave_stats (mysql_database_t *db, MYSQL *con)
return (0);
} /* mysql_read_slave_stats */
+static int mysql_innodb_stats (mysql_database_t *db, MYSQL *con)
+{
+ MYSQL_RES *res;
+ MYSQL_ROW row;
+
+ char *query, *lines[150], *fields[12];
+ int field_num, numlines, numfields, i, txn_cnt = 0, unpurge_cnt = 0;
+
+ query = "SHOW /*!50000 ENGINE*/ INNODB STATUS";
+
+ res = exec_query (con, query);
+ if (res == NULL)
+ return (-1);
+
+ row = mysql_fetch_row (res);
+ if (row == NULL)
+ {
+ ERROR ("mysql plugin: Failed to get innodb statistics: "
+ "`%s' did not return any rows.", query);
+ return (-1);
+ }
+
+ field_num = mysql_num_fields (res);
+ if (field_num < 3)
+ {
+ ERROR ("mysql plugin: Failed to get InnoDB statistics: "
+ "`%s' returned less than 3 columns.", query);
+ return (-1);
+ }
+
+ numlines = split_row(row[2], lines, STATIC_ARRAY_SIZE (lines));
+
+ /**
+ * Most of the InnoDB Status parsing was derived from
+ * ss_get_mysql_stats.php that some with the Percona
+ * monitoring plugin:
+ * http://www.percona.com/doc/percona-monitoring-plugins/
+ */
+ for (i = 0; i < numlines; ++i) {
+ if (strncmp ("Mutex spin waits", lines[i],
+ strlen ("Mutex spin waits")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ gauge_submit ("mysql_mutex", "spin_waits",
+ atof (fields[3]), db);
+ gauge_submit ("mysql_mutex", "spin_rounds",
+ atof (fields[5]), db);
+ gauge_submit ("mysql_mutex", "OS_waits",
+ atof (fields[8]), db);
+
+ }
+ else if (strncmp ("Trx id counter", lines[i],
+ strlen ("Trx id counter")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ txn_cnt = strtol (fields[3], NULL, 16);
+ derive_submit ("innodb_trx", "total_transactions",
+ txn_cnt, db);
+ }
+ else if (strncmp ("Purge done for trx", lines[i],
+ strlen ("Trx id counter")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ unpurge_cnt = txn_cnt - strtol (fields[6], NULL, 16);
+ derive_submit ("innodb_trx", "current_transactions",
+ unpurge_cnt, db);
+ }
+ else if (strncmp ("History list length", lines[i],
+ strlen ("History list length")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ derive_submit ("innodb_trx", "history_list",
+ atof (fields[3]), db);
+ }
+ else if (strncmp ("Buffer pool size ", lines[i],
+ strlen ("Buffer pool size ")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ gauge_submit ("innodb_buffer_pool", "pool_size",
+ atof (fields[3]), db);
+ }
+ else if (strncmp ("Free buffers", lines[i],
+ strlen ("Free buffers")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ gauge_submit ("innodb_buffer_pool", "free_pages",
+ atof (fields[2]), db);
+ }
+ else if (strncmp ("Database pages", lines[i],
+ strlen ("Database pages")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ gauge_submit ("innodb_buffer_pool", "database_pages",
+ atof (fields[2]), db);
+ }
+ else if (strncmp ("Modified db pages", lines[i],
+ strlen ("Modified db pages")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ gauge_submit ("innodb_buffer_pool", "modified_db_pages",
+ atof (fields[3]), db);
+ }
+ else if (strncmp ("Pages read ahead", lines[i],
+ strlen ("Pages read ahead")) == 0)
+ {
+ /* do nothing */
+ }
+ else if (strncmp ("Pages read", lines[i],
+ strlen ("Pages read")) == 0)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ derive_submit ("innodb_buffer_pool_activity", "pages_read",
+ atof (fields[2]), db);
+ derive_submit ("innodb_buffer_pool_activity", "pages_created",
+ atof (fields[4]), db);
+ derive_submit ("innodb_buffer_pool_activity", "pages_written",
+ atof (fields[6]), db);
+ }
+ else if (strstr (lines[i], " OS file reads") != NULL)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ derive_submit("innodb_io_activity", "file_reads",
+ atof (fields[0]), db);
+ derive_submit("innodb_io_activity", "file_writes",
+ atof (fields[4]), db);
+ derive_submit("innodb_io_activity", "file_syncs",
+ atof (fields[8]), db);
+ }
+ else if (strstr (lines[i], " log i/o's done, ") != NULL)
+ {
+ numfields = split_line (lines[i], fields, STATIC_ARRAY_SIZE (fields));
+ derive_submit("innodb_io_activity", "log_writes",
+ atof (fields[0]), db);
+ }
+ }
+
+ row = mysql_fetch_row (res);
+ if (row != NULL)
+ WARNING ("mysql plugin: `%s' returned more than one row - "
+ "ignoring further results.", query);
+
+ mysql_free_result (res);
+
+ return (0);
+} /* mysql_innodb_stats */
+
static int mysql_read (user_data_t *ud)
{
mysql_database_t *db;
@@ -703,6 +895,9 @@ static int mysql_read (user_data_t *ud)
if ((db->slave_stats) || (db->slave_notif))
mysql_read_slave_stats (db, con);
+ if (db->innodb_stats)
+ mysql_innodb_stats (db, con);
+
return (0);
} /* int mysql_read */
diff --git a/src/types.db b/src/types.db
index ed27b78..1e8c3c0 100644
--- a/src/types.db
+++ b/src/types.db
@@ -77,6 +77,10 @@ if_octets rx:DERIVE:0:U, tx:DERIVE:0:U
if_packets rx:DERIVE:0:U, tx:DERIVE:0:U
if_rx_errors value:DERIVE:0:U
if_tx_errors value:DERIVE:0:U
+innodb_trx value:DERIVE:0:U
+innodb_buffer_pool value:GAUGE:0:U
+innodb_buffer_pool_activity value:DERIVE:0:U
+innodb_io_activity value:DERIVE:0:U
invocations value:DERIVE:0:U
io_octets rx:DERIVE:0:U, tx:DERIVE:0:U
io_packets rx:DERIVE:0:U, tx:DERIVE:0:U
@@ -99,6 +103,7 @@ mysql_commands value:DERIVE:0:U
mysql_handler value:DERIVE:0:U
mysql_locks value:DERIVE:0:U
mysql_log_position value:DERIVE:0:U
+mysql_mutex value:GAUGE:0:U
mysql_octets rx:DERIVE:0:U, tx:DERIVE:0:U
nfs_procedure value:DERIVE:0:U
nginx_connections value:GAUGE:0:U
--
1.7.11.5
More information about the collectd
mailing list