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