src/mysql.c

   1 /**
   2  * collectd - src/mysql.c
   3  * Copyright (C) 2006  Florian octo Forster
   4  *
   5  * This program is free software; you can redistribute it and/or modify it
   6  * under the terms of the GNU General Public License as published by the
   7  * Free Software Foundation; either version 2 of the License, or (at your
   8  * option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful, but
  11  * WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  13  * General Public License for more details.
  14  *
  15  * You should have received a copy of the GNU General Public License along
  16  * with this program; if not, write to the Free Software Foundation, Inc.,
  17  * 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
  18  *
  19  * Authors:
  20  *   Florian octo Forster <octo at verplant.org>
  21  **/
  22
  23 #include "collectd.h"
  24 #include "common.h"
  25 #include "plugin.h"
  26 #include "configfile.h"
  27
  28 #ifdef HAVE_MYSQL_MYSQL_H
  29 #include <mysql/mysql.h>
  30 #endif
  31
  32 #define MODULE_NAME "mysql"
  33
  34 #if COLLECT_LIBMYSQL
  35 # define MYSQL_HAVE_READ 1
  36 #else
  37 # define MYSQL_HAVE_READ 0
  38 #endif
  39
  40 #define BUFSIZE 512
  41
  42 static char *host = "localhost";
  43 static char *user;
  44 static char *pass;
  45 static char *db = NULL;
  46
  47 /* TODO
  48  * understand `Select_*' and possibly do that stuff as well..
  49  */
  50
  51 static char *commands_file = "mysql/mysql_commands-%s.rrd";
  52 static char *handler_file  = "mysql/mysql_handler-%s.rrd";
  53 static char *qcache_file   = "mysql/mysql_qcache.rrd";
  54 static char *threads_file  = "mysql/mysql_threads.rrd";
  55 static char *traffic_file  = "traffic-mysql.rrd";
  56
  57 static char *commands_ds_def[] =
  58 {
  59         "DS:value:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  60         NULL
  61 };
  62 static int commands_ds_num = 1;
  63
  64 static char *handler_ds_def[] =
  65 {
  66         "DS:value:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  67         NULL
  68 };
  69 static int handler_ds_num = 1;
  70
  71 static char *qcache_ds_def[] =
  72 {
  73         "DS:hits:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  74         "DS:inserts:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  75         "DS:not_cached:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  76         "DS:lowmem_prunes:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  77         "DS:queries_in_cache:GAUGE:"COLLECTD_HEARTBEAT":0:U",
  78         NULL
  79 };
  80 static int qcache_ds_num = 5;
  81
  82 static char *threads_ds_def[] =
  83 {
  84         "DS:running:GAUGE:"COLLECTD_HEARTBEAT":0:U",
  85         "DS:connected:GAUGE:"COLLECTD_HEARTBEAT":0:U",
  86         "DS:cached:GAUGE:"COLLECTD_HEARTBEAT":0:U",
  87         "DS:created:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  88         NULL
  89 };
  90 static int threads_ds_num = 4;
  91
  92 static char *traffic_ds_def[] =
  93 {
  94         "DS:incoming:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  95         "DS:outgoing:COUNTER:"COLLECTD_HEARTBEAT":0:U",
  96         NULL
  97 };
  98 static int traffic_ds_num = 2;
  99
 100 static char *config_keys[] =
 101 {
 102         "Host",
 103         "User",
 104         "Password",
 105         "Database",
 106         NULL
 107 };
 108 static int config_keys_num = 4;
 109
 110 #if MYSQL_HAVE_READ
 111 static MYSQL *getconnection (void)
 112 {
 113         static MYSQL *con;
 114         static int    state;
 115
 116         static int wait_for = 0;
 117         static int wait_increase = 60;
 118
 119         int step;
 120
 121         if (state != 0)
 122         {
 123                 int err;
 124                 if ((err = mysql_ping (con)) != 0)
 125                 {
 126                         syslog (LOG_WARNING, "mysql_ping failed: %s", mysql_error (con));
 127                         state = 0;
 128                 }
 129                 else
 130                 {
 131                         state = 1;
 132                         return (con);
 133                 }
 134         }
 135
 136         step = atoi (COLLECTD_STEP);
 137
 138         if (wait_for > 0)
 139         {
 140                 wait_for -= step;
 141                 return (NULL);
 142         }
 143
 144         wait_for = wait_increase;
 145         wait_increase *= 2;
 146         if (wait_increase > 86400)
 147                 wait_increase = 86400;
 148
 149         if ((con = mysql_init (con)) == NULL)
 150         {
 151                 syslog (LOG_ERR, "mysql_init failed: %s", mysql_error (con));
 152                 state = 0;
 153                 return (NULL);
 154         }
 155
 156         if (mysql_real_connect (con, host, user, pass, db, 0, NULL, 0) == NULL)
 157         {
 158                 syslog (LOG_ERR, "mysql_real_connect failed: %s", mysql_error (con));
 159                 state = 0;
 160                 return (NULL);
 161         }
 162         else
 163         {
 164                 state = 1;
 165                 wait_for = 0;
 166                 wait_increase = 60;
 167                 return (con);
 168         }
 169 } /* static MYSQL *getconnection (void) */
 170 #endif /* MYSQL_HAVE_READ */
 171
 172 static void init (void)
 173 {
 174         return;
 175 }
 176
 177 static int config (char *key, char *value)
 178 {
 179         if (strcasecmp (key, "host") == 0)
 180                 return ((host = strdup (value)) == NULL ? 1 : 0);
 181         else if (strcasecmp (key, "user") == 0)
 182                 return ((user = strdup (value)) == NULL ? 1 : 0);
 183         else if (strcasecmp (key, "password") == 0)
 184                 return ((pass = strdup (value)) == NULL ? 1 : 0);
 185         else if (strcasecmp (key, "database") == 0)
 186                 return ((db = strdup (value)) == NULL ? 1 : 0);
 187         else
 188                 return (-1);
 189 }
 190
 191 static void commands_write (char *host, char *inst, char *val)
 192 {
 193         char buf[BUFSIZE];
 194
 195         if (snprintf (buf, BUFSIZE, commands_file, inst) >= BUFSIZE)
 196                 return;
 197
 198         rrd_update_file (host, buf, val, commands_ds_def, commands_ds_num);
 199 }
 200
 201 static void handler_write (char *host, char *inst, char *val)
 202 {
 203         char buf[BUFSIZE];
 204
 205         if (snprintf (buf, BUFSIZE, handler_file, inst) >= BUFSIZE)
 206                 return;
 207
 208         rrd_update_file (host, buf, val, handler_ds_def, handler_ds_num);
 209 }
 210
 211 static void qcache_write (char *host, char *inst, char *val)
 212 {
 213         rrd_update_file (host, qcache_file, val,
 214                         qcache_ds_def, qcache_ds_num);
 215 }
 216
 217 static void threads_write (char *host, char *inst, char *val)
 218 {
 219         rrd_update_file (host, threads_file, val,
 220                         threads_ds_def, threads_ds_num);
 221 }
 222
 223 static void traffic_write (char *host, char *inst, char *val)
 224 {
 225         rrd_update_file (host, traffic_file, val,
 226                         traffic_ds_def, traffic_ds_num);
 227 }
 228
 229 #if MYSQL_HAVE_READ
 230 static void commands_submit (char *inst, unsigned long long value)
 231 {
 232         char buf[BUFSIZE];
 233         int  status;
 234
 235         status = snprintf (buf, BUFSIZE, "%u:%llu", (unsigned int) curtime, value);
 236
 237         if (status < 0)
 238         {
 239                 syslog (LOG_ERR, "snprintf failed");
 240                 return;
 241         }
 242         else if (status >= BUFSIZE)
 243         {
 244                 syslog (LOG_WARNING, "snprintf was truncated");
 245                 return;
 246         }
 247
 248         plugin_submit ("mysql_commands", inst, buf);
 249 }
 250
 251 static void handler_submit (char *inst, unsigned long long value)
 252 {
 253         char buf[BUFSIZE];
 254         int  status;
 255
 256         status = snprintf (buf, BUFSIZE, "%u:%llu", (unsigned int) curtime, value);
 257
 258         if (status < 0)
 259         {
 260                 syslog (LOG_ERR, "snprintf failed");
 261                 return;
 262         }
 263         else if (status >= BUFSIZE)
 264         {
 265                 syslog (LOG_WARNING, "snprintf was truncated");
 266                 return;
 267         }
 268
 269         plugin_submit ("mysql_handler", inst, buf);
 270 }
 271
 272 static void qcache_submit (unsigned long long hits, unsigned long long inserts,
 273                 unsigned long long not_cached, unsigned long long lowmem_prunes,
 274                 int queries_in_cache)
 275 {
 276         char buf[BUFSIZE];
 277         int  status;
 278
 279         status = snprintf (buf, BUFSIZE, "%u:%llu:%llu:%llu:%llu:%i",
 280                         (unsigned int) curtime, hits, inserts, not_cached,
 281                         lowmem_prunes, queries_in_cache);
 282
 283         if (status < 0)
 284         {
 285                 syslog (LOG_ERR, "snprintf failed");
 286                 return;
 287         }
 288         else if (status >= BUFSIZE)
 289         {
 290                 syslog (LOG_WARNING, "snprintf was truncated");
 291                 return;
 292         }
 293
 294         plugin_submit ("mysql_qcache", "-", buf);
 295 }
 296
 297 static void threads_submit (int running, int connected, int cached,
 298                 unsigned long long created)
 299 {
 300         char buf[BUFSIZE];
 301         int  status;
 302
 303         status = snprintf (buf, BUFSIZE, "%u:%i:%i:%i:%llu",
 304                         (unsigned int) curtime,
 305                         running, connected, cached, created);
 306
 307         if (status < 0)
 308         {
 309                 syslog (LOG_ERR, "snprintf failed");
 310                 return;
 311         }
 312         else if (status >= BUFSIZE)
 313         {
 314                 syslog (LOG_WARNING, "snprintf was truncated");
 315                 return;
 316         }
 317
 318         plugin_submit ("mysql_threads", "-", buf);
 319 }
 320
 321 static void traffic_submit (unsigned long long incoming,
 322                 unsigned long long outgoing)
 323 {
 324         char buf[BUFSIZE];
 325         int  status;
 326
 327         status = snprintf (buf, BUFSIZE, "%u:%llu:%llu", (unsigned int) curtime,
 328                         incoming, outgoing);
 329
 330         if (status < 0)
 331         {
 332                 syslog (LOG_ERR, "snprintf failed");
 333                 return;
 334         }
 335         else if (status >= BUFSIZE)
 336         {
 337                 syslog (LOG_WARNING, "snprintf was truncated");
 338                 return;
 339         }
 340
 341         plugin_submit ("mysql_traffic", "-", buf);
 342 }
 343
 344 static void mysql_read (void)
 345 {
 346         MYSQL     *con;
 347         MYSQL_RES *res;
 348         MYSQL_ROW  row;
 349         char      *query;
 350         int        query_len;
 351         int        field_num;
 352
 353         unsigned long long qcache_hits          = 0ULL;
 354         unsigned long long qcache_inserts       = 0ULL;
 355         unsigned long long qcache_not_cached    = 0ULL;
 356         unsigned long long qcache_lowmem_prunes = 0ULL;
 357         int qcache_queries_in_cache = -1;
 358
 359         int threads_running   = -1;
 360         int threads_connected = -1;
 361         int threads_cached    = -1;
 362         unsigned long long threads_created = 0ULL;
 363
 364         unsigned long long traffic_incoming = 0ULL;
 365         unsigned long long traffic_outgoing = 0ULL;
 366
 367         /* An error message will have been printed in this case */
 368         if ((con = getconnection ()) == NULL)
 369                 return;
 370
 371         query = "SHOW STATUS";
 372         if (mysql_get_server_version (con) >= 50002)
 373                 query = "SHOW GLOBAL STATUS";
 374
 375         query_len = strlen (query);
 376
 377         if (mysql_real_query (con, query, query_len))
 378         {
 379                 syslog (LOG_ERR, "mysql_real_query failed: %s\n",
 380                                 mysql_error (con));
 381                 return;
 382         }
 383
 384         if ((res = mysql_store_result (con)) == NULL)
 385         {
 386                 syslog (LOG_ERR, "mysql_store_result failed: %s\n",
 387                                 mysql_error (con));
 388                 return;
 389         }
 390
 391         field_num = mysql_num_fields (res);
 392         while ((row = mysql_fetch_row (res)))
 393         {
 394                 char *key;
 395                 unsigned long long val;
 396
 397                 key = row[0];
 398                 val = atoll (row[1]);
 399
 400                 if (strncmp (key, "Com_", 4) == 0)
 401                 {
 402                         if (val == 0ULL)
 403                                 continue;
 404
 405                         /* Ignore `prepared statements' */
 406                         if (strncmp (key, "Com_stmt_", 9) != 0)
 407                                 commands_submit (key + 4, val);
 408                 }
 409                 else if (strncmp (key, "Handler_", 8) == 0)
 410                 {
 411                         if (val == 0ULL)
 412                                 continue;
 413
 414                         handler_submit (key + 8, val);
 415                 }
 416                 else if (strncmp (key, "Qcache_", 7) == 0)
 417                 {
 418                         if (strcmp (key, "Qcache_hits") == 0)
 419                                 qcache_hits = val;
 420                         else if (strcmp (key, "Qcache_inserts") == 0)
 421                                 qcache_inserts = val;
 422                         else if (strcmp (key, "Qcache_not_cached") == 0)
 423                                 qcache_not_cached = val;
 424                         else if (strcmp (key, "Qcache_lowmem_prunes") == 0)
 425                                 qcache_lowmem_prunes = val;
 426                         else if (strcmp (key, "Qcache_queries_in_cache") == 0)
 427                                 qcache_queries_in_cache = (int) val;
 428                 }
 429                 else if (strncmp (key, "Bytes_", 6) == 0)
 430                 {
 431                         if (strcmp (key, "Bytes_received") == 0)
 432                                 traffic_incoming += val;
 433                         else if (strcmp (key, "Bytes_sent") == 0)
 434                                 traffic_outgoing += val;
 435                 }
 436                 else if (strncmp (key, "Threads_", 8) == 0)
 437                 {
 438                         if (strcmp (key, "Threads_running") == 0)
 439                                 threads_running = (int) val;
 440                         else if (strcmp (key, "Threads_connected") == 0)
 441                                 threads_connected = (int) val;
 442                         else if (strcmp (key, "Threads_cached") == 0)
 443                                 threads_cached = (int) val;
 444                         else if (strcmp (key, "Threads_created") == 0)
 445                                 threads_created = val;
 446                 }
 447         }
 448         mysql_free_result (res); res = NULL;
 449
 450         if ((qcache_hits != 0ULL)
 451                         || (qcache_inserts != 0ULL)
 452                         || (qcache_not_cached != 0ULL)
 453                         || (qcache_lowmem_prunes != 0ULL))
 454                 qcache_submit (qcache_hits, qcache_inserts, qcache_not_cached,
 455                                 qcache_lowmem_prunes, qcache_queries_in_cache);
 456
 457         if (threads_created != 0ULL)
 458                 threads_submit (threads_running, threads_connected,
 459                                 threads_cached, threads_created);
 460
 461         traffic_submit  (traffic_incoming, traffic_outgoing);
 462
 463         /* mysql_close (con); */
 464
 465         return;
 466 }
 467 #else
 468 # define mysql_read NULL
 469 #endif /* MYSQL_HAVE_READ */
 470
 471 void module_register (void)
 472 {
 473         plugin_register (MODULE_NAME, init, mysql_read, NULL);
 474         plugin_register ("mysql_commands", NULL, NULL, commands_write);
 475         plugin_register ("mysql_handler",  NULL, NULL, handler_write);
 476         plugin_register ("mysql_qcache",   NULL, NULL, qcache_write);
 477         plugin_register ("mysql_threads",  NULL, NULL, threads_write);
 478         plugin_register ("mysql_traffic",  NULL, NULL, traffic_write);
 479         cf_register (MODULE_NAME, config, config_keys, config_keys_num);
 480 }
 481
 482 #undef BUFSIZE
 483 #undef MODULE_NAME