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