mysql_

   1 #!/usr/bin/perl
   2 # -*- perl -*-
   3
   4 =encoding utf8
   5
   6 =head1 NAME
   7
   8 mysql_ - Munin plugin to display misc MySQL server status
   9
  10 =head1 APPLICABLE SYSTEMS
  11
  12 Any MySQL platform, tested by the authors on:
  13 * MySQL 5.0.51
  14 * MariaDB 5.5.39
  15 * MariaDB-5.5.39(galera).
  16 * MySQL 5.6.12
  17 * MariaDB 10.0.18
  18
  19 Plugins:
  20 * MariaDB-10 Query Response Time: https://mariadb.com/kb/en/mariadb/query_response_time-plugin/
  21
  22 Information Schema tables:
  23 * User statistics - MariaDB-5.2+, OurDelta, Percona Server - https://mariadb.com/kb/en/mariadb/user-statistics
  24
  25 =head1 CONFIGURATION
  26
  27 This script is used to generate data for several graphs. To generate
  28 data for one specific graph, you need to create a symbolic link with a
  29 name like mysql_<GRAPH> to this script.
  30
  31 If you need to run against multiple MySQL instances on the same host,
  32 create your symlinks with names like mysql<N>_<GRAPH> where N is any
  33 non-negative integer. You must also set the env.cachenamespace variable
  34 to a unique value for each group of symlinks.
  35
  36 To get a list of symlinks that can be created, run:
  37
  38   ./mysql_ suggest
  39
  40 In addition you might need to specify connection parameters in the
  41 plugin configuration to override the defaults. These are the defaults:
  42
  43   [mysql_*]
  44     env.mysqlconnection DBI:mysql:information_schema
  45     env.mysqluser root
  46
  47 Non-default example:
  48
  49   [mysql_*]
  50     env.mysqlconnection DBI:mysql:information_schema;host=127.0.0.1;port=3306
  51     env.mysqluser munin
  52     env.mysqlpassword geheim
  53     env.cachenamespace munin_mysql_pri
  54   [mysql2_*]
  55     env.mysqlconnection DBI:mysql:information_schema;host=127.0.0.1;port=13306
  56     env.mysqluser munin
  57     env.mysqlpassword ryuWyawEv
  58     env.cachenamespace munin_mysql_alt
  59   [mysql10_*]
  60     user munin
  61     env.mysqluser munin
  62     env.mysqlconnection DBI:mysql:information_schema;mysql_read_default_file=/etc/munin/.my-10.cnf
  63     env.cachenamespace munin_mysql_10
  64     # here the [client] section of /etc/munin/.my-10.cnf is read. socket= can
  65     # be specified here.
  66
  67 Creating a munin user:
  68
  69   CREATE USER 'munin'@'localhost' IDENTIFIED BY 'ryuWyawEv';
  70
  71 or with a unix_socket plugin (INSTALL PLUGIN unix_socket SONAME 'auth_socket')
  72
  73   CREATE USER 'munin'@'localhost' IDENTIFIED WITH unix_socket;
  74
  75 Note: requires 'user munin' in the configuration.
  76
  77 The minimum required priviledges of the munin database user is:
  78
  79   GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'munin'@'localhost';
  80
  81
  82 Warning and critical values can be set via the environment in the usual way.
  83 For example:
  84
  85   [mysql_replication]
  86     env.slave_io_running_warning 0.5
  87     env.slave_sql_running_warning 0.5
  88     env.seconds_behind_master_warning 300
  89     env.seconds_behind_master_critical 600
  90
  91 =head1 DEPENDENCIES
  92
  93 =over
  94
  95 =item Cache::Cache
  96
  97 The plugin uses shared memory to cache the statistics gathered from
  98 MySQL. This ensures minimal inpact on the MySQL server.
  99
 100 =item DBD::mysql
 101
 102 =back
 103
 104 =head1 INTERPRETATION
 105
 106 =head2 InnoDB
 107
 108 The statistics from innodb are mainly collected from the command
 109
 110   SHOW ENGINE INNODB STATUS
 111
 112 A nice walk through is found at
 113 L<http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/>
 114
 115 =head2 The graphs
 116
 117 FIX point to relevant sections in the MySQL manual and other www
 118 resources for each graph
 119
 120 =over
 121
 122 =item mysql_replication
 123
 124 slave_io_running and slave_sql_running both translate the "Yes" values to 0 and
 125 anything else to 1 for their respective fields in the "SHOW SLAVE STATUS" output.
 126 This can be used to warn on slave failure if the warning and critical values
 127 are set as seen in a previous section.
 128
 129 =back
 130
 131 =head1 LICENSE
 132
 133 Copyright (C) 2008,2009 Kjell-Magne Øierud
 134
 135 This program is free software; you can redistribute it and/or modify
 136 it under the terms of the GNU General Public License as published by
 137 the Free Software Foundation; version 2 dated June, 1991.
 138
 139 This program is distributed in the hope that it will be useful, but
 140 WITHOUT ANY WARRANTY; without even the implied warranty of
 141 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 142 General Public License for more details.
 143
 144 You should have received a copy of the GNU General Public License along
 145 with this program; if not, write to the Free Software Foundation, Inc.,
 146 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 147
 148 =head1 VERSION
 149
 150 git-master + a few munin modifications
 151
 152 This plugin was downloaded from L<http://github.com/kjellm/munin-mysql/>
 153
 154 =head1 MAGICK MARKERS
 155
 156   #%# family=auto
 157   #%# capabilities=suggest autoconf
 158
 159 =cut
 160
 161 use warnings;
 162 use strict;
 163 use utf8;
 164
 165 use DBI;
 166 use File::Basename;
 167 use Math::BigInt; # Used to append "=> lib 'GMP'" here, but GMP caused
 168                   # segfault on some occasions. Removed as I don't
 169                   # think the tiny performance boost is worth the
 170                   # debugging effort.
 171 use Storable qw(nfreeze thaw);
 172
 173 use Munin::Plugin;
 174
 175 my $has_cache;
 176
 177 BEGIN {
 178     eval 'require Cache::SharedMemoryCache';
 179     $has_cache = $@ ? 0 : 1;
 180 }
 181
 182
 183 #---------------------------------------------------------------------
 184 #  C O N F I G
 185 #---------------------------------------------------------------------
 186
 187 my %config = (
 188     'dsn'        => $ENV{'mysqlconnection'} || 'DBI:mysql:information_schema',
 189     'user'       => $ENV{'mysqluser'}       || 'root',
 190     'password'   => $ENV{'mysqlpassword'}   || '',
 191     'cache_namespace' => $ENV{'cachenamespace'} || 'munin_mysql',
 192 );
 193
 194
 195 #---------------------------------------------------------------------
 196 #  C A C H E
 197 #---------------------------------------------------------------------
 198
 199 my %cache_options = (
 200     'namespace'          => $config{cache_namespace},
 201     'default_expires_in' => 60,
 202 );
 203
 204 my $shared_memory_cache ;
 205 if ($has_cache)
 206 {
 207   $shared_memory_cache = Cache::SharedMemoryCache->new(\%cache_options)
 208     or die("Couldn't instantiate SharedMemoryCache");
 209 }
 210
 211 #---------------------------------------------------------------------
 212 #  G R A P H   D E F I N I T I O N S
 213 #---------------------------------------------------------------------
 214
 215 # These are defaults to save typing in the graph definitions
 216 my %defaults = (
 217     global_attrs => {
 218         args   => '--base 1000',
 219     },
 220     data_source_attrs => {
 221         min   => '0',
 222         type  => 'DERIVE',
 223         draw  => 'AREASTACK',
 224     },
 225 );
 226
 227 # %graphs contains the graph definitions, it is indexed on the graph
 228 # name. The information stored for each graph is used for both showing
 229 # data source values and for printing the graph configuration. Each
 230 # graph follows the followingformat:
 231 #
 232 # $graph{NAME} => {
 233 #     config => {
 234 #         # The global attributes for this graph
 235 #         global_attrs => {}
 236 #         # Attributes common to all data sources in this graph
 237 #         data_source_attrs => {}
 238 #     },
 239 #     data_sources => [
 240 #         # NAME - The name of the data source (e.g. variable names
 241 #         #        from SHOW STATUS)
 242 #         # DATA_SOURCE_ATTRS - key-value pairs with data source
 243 #         #                     attributes
 244 #         {name => 'NAME', (DATA_SOURCE_ATTRS)},
 245 #         {...},
 246 #     ],
 247 my %graphs = ();
 248
 249 #---------------------------------------------------------------------
 250
 251 $graphs{bin_relay_log} = {
 252     config => {
 253         global_attrs => {
 254             title  => 'Binary/Relay Logs',
 255             vlabel => 'Log activity (txn/s)',
 256         },
 257         data_source_attrs => {
 258             draw  => 'LINE1',
 259         },
 260     },
 261     data_sources => [
 262         {name => 'Binlog_cache_disk_use', label => 'Binlog Cache Disk Use', info => 'Number of transactions which used a temporary disk cache because they could not fit in the regular binary log cache, being larger than binlog_cache_size'},
 263         {name => 'Binlog_cache_use',      label => 'Binlog Cache Use', info => 'Number of transaction which used the regular binary log cache, being smaller than binlog_cache_size'},
 264     ],
 265 };
 266
 267 #---------------------------------------------------------------------
 268 $graphs{binlog_space} = {
 269     config => {
 270         global_attrs => {
 271             title  => 'Binary log space',
 272             vlabel => 'Log space',
 273             args   => '--base 1024',
 274         },
 275         data_source_attrs => {
 276             draw  => 'LINE1',
 277             type  => 'GAUGE',
 278         },
 279     },
 280     data_sources => [
 281         {name => 'ma_binlog_size',        label => 'Binary Log Space'},
 282     ],
 283 };
 284
 285 #-------------------------
 286 $graphs{binlog_groupcommit} = {
 287     config => {
 288        global_attrs => {
 289            title  => 'Binary Log Group Commits',
 290            vlabel => 'Commits/Groups',
 291        },
 292        data_source_attrs => {
 293            draw  => 'LINE1',
 294        },
 295     },
 296     data_sources => [
 297        {name => 'Binlog_commits', label => 'Binlog commits'},
 298        {name => 'Binlog_group_commits',      label => 'Binlog Group Commits'},
 299        {name => 'Binlog_group_commit_trigger_count', label => 'Binlog Groups because of binlog_commit_wait_count'},
 300        {name => 'Binlog_group_commit_trigger_timeout', label => 'Binlog Groups because of binlog_commit_wait_usec'},
 301        {name => 'Binlog_group_commit_trigger_lock_wait', label => 'Binlog Groups because of transactions'},
 302     ],
 303 };
 304
 305 #---------------------------------------------------------------------
 306
 307 $graphs{commands} = {
 308     config => {
 309         global_attrs => {
 310             title  => 'Command Counters',
 311             vlabel => 'Commands per ${graph_period}',
 312             total  => 'Questions',
 313         },
 314         data_source_attrs => {},
 315     },
 316     data_sources => [
 317         {name => 'Com_delete',         label => 'Delete'},
 318         {name => 'Com_insert',         label => 'Insert'},
 319         {name => 'Com_insert_select',  label => 'Insert select'},
 320         {name => 'Com_load',           label => 'Load Data'},
 321         {name => 'Com_replace',        label => 'Replace'},
 322         {name => 'Com_replace_select', label => 'Replace select'},
 323         {name => 'Com_select',         label => 'Select'},
 324         {name => 'Com_update',         label => 'Update'},
 325         {name => 'Com_update_multi',   label => 'Update multi'},
 326     ],
 327 };
 328
 329 #---------------------------------------------------------------------
 330
 331 $graphs{connections} = {
 332     config => {
 333         global_attrs => {
 334             title  => 'Connections',
 335             vlabel => 'Connections per ${graph_period}',
 336         },
 337         data_source_attrs => {
 338             draw  => 'LINE1',
 339         },
 340     },
 341     data_sources => [
 342         {name => 'max_connections',      label  => 'Max connections',
 343                                          type   => 'GAUGE',
 344                                          draw   => 'AREA',
 345                                          colour => 'cdcfc4'},
 346         {name => 'Max_used_connections', label  => 'Max used',
 347                                          type   => 'GAUGE',
 348                                          draw   => 'AREA',
 349                                          colour => 'ffd660'},
 350         {name => 'Aborted_clients',      label => 'Aborted clients'},
 351         {name => 'Aborted_connects',     label => 'Aborted connects'},
 352         {name => 'Threads_connected',    label => 'Threads connected',
 353                                          type  => 'GAUGE'},
 354         {name => 'Threads_running',      label => 'Threads running',
 355                                          type  => 'GAUGE'},
 356         {name => 'Connections',          label => 'New connections'},
 357     ],
 358 };
 359
 360 #---------------------------------------------------------------------
 361
 362 $graphs{files_tables} = {
 363     config => {
 364         global_attrs => {
 365             title  => 'Files and tables',
 366             vlabel => 'Tables',
 367         },
 368         data_source_attrs => {
 369             type  => 'GAUGE',
 370             draw  => 'LINE1',
 371         },
 372     },
 373     data_sources => [
 374         {name => 'table_open_cache', label  => 'Table cache',
 375                                      draw   => 'AREA',
 376                                      colour => 'cdcfc4'},
 377         {name => 'Open_files',       label => 'Open files'},
 378         {name => 'Open_tables',      label => 'Open tables'},
 379         {name => 'Opened_tables',    label => 'Opened tables',
 380                                      type  => 'DERIVE',
 381                                      min   => 0},
 382     ],
 383 };
 384
 385 #---------------------------------------------------------------------
 386
 387 $graphs{innodb_bpool} = {
 388     config => {
 389         global_attrs => {
 390             title  => 'InnoDB Buffer Pool',
 391             vlabel => 'Pages',
 392             args   => '--base 1024',
 393         },
 394         data_source_attrs => {
 395             draw => 'LINE2',
 396             type => 'GAUGE',
 397         },
 398     },
 399     data_sources => [
 400         {name => 'ib_bpool_size',     label  => 'Buffer pool size',
 401                                       draw   => 'AREA',
 402                                       colour => 'ffd660'},
 403         {name => 'ib_bpool_dbpages',  label  => 'Database pages',
 404                                       draw   => 'AREA',
 405                                       colour => 'cdcfc4'},
 406         {name => 'ib_bpool_free',     label => 'Free pages'},
 407         {name => 'ib_bpool_modpages', label => 'Modified pages'},
 408     ],
 409 };
 410
 411 #---------------------------------------------------------------------
 412
 413 $graphs{innodb_bpool_act} = {
 414     config => {
 415         global_attrs => {
 416             title  => 'InnoDB Buffer Pool Activity',
 417             vlabel => 'Activity per ${graph_period}',
 418             total  => 'Total',
 419         },
 420         data_source_attrs => {
 421             draw => 'LINE2',
 422         },
 423     },
 424     data_sources => [
 425         {name => 'ib_bpool_read',    label => 'Pages read'},
 426         {name => 'ib_bpool_created', label => 'Pages created'},
 427         {name => 'ib_bpool_written', label => 'Pages written'},
 428     ],
 429 };
 430
 431 #---------------------------------------------------------------------
 432
 433 $graphs{innodb_insert_buf} = {
 434     config => {
 435         global_attrs => {
 436             title  => 'InnoDB Insert Buffer',
 437             vlabel => 'Activity per ${graph_period}',
 438         },
 439         data_source_attrs => {
 440             draw => 'LINE1',
 441         },
 442     },
 443     data_sources => [
 444         {name => 'ib_ibuf_inserts',    label => 'Inserts'},
 445         {name => 'ib_ibuf_merged_rec', label => 'Merged Records'},
 446         {name => 'ib_ibuf_merges',     label => 'Merges'},
 447     ],
 448 };
 449
 450 #---------------------------------------------------------------------
 451
 452 $graphs{innodb_io} = {
 453     config => {
 454         global_attrs => {
 455             title  => 'InnoDB IO',
 456             vlabel => 'IO operations per ${graph_period}',
 457         },
 458         data_source_attrs => {
 459             draw => 'LINE1',
 460         },
 461     },
 462     data_sources => [
 463         {name => 'ib_io_read',  label => 'File reads'},
 464         {name => 'ib_io_write', label => 'File writes'},
 465         {name => 'ib_io_log',   label => 'Log writes'},
 466         {name => 'ib_io_fsync', label => 'File syncs'},
 467     ],
 468 };
 469
 470 #---------------------------------------------------------------------
 471
 472 $graphs{innodb_io_pend} = {
 473     config => {
 474         global_attrs => {
 475             title  => 'InnoDB IO Pending',
 476             vlabel => 'Pending operations',
 477         },
 478         data_source_attrs => {
 479             draw => 'LINE1',
 480         },
 481     },
 482     data_sources => [
 483         {name => 'ib_iop_log',         label => 'AIO Log'},
 484         {name => 'ib_iop_sync',        label => 'AIO Sync'},
 485         {name => 'ib_iop_flush_bpool', label => 'Buf Pool Flush'},
 486         {name => 'ib_iop_flush_log',   label => 'Log Flushes'},
 487         {name => 'ib_iop_ibuf_aio',    label => 'Insert Buf AIO Read'},
 488         {name => 'ib_iop_aioread',     label => 'Normal AIO Reads'},
 489         {name => 'ib_iop_aiowrite',    label => 'Normal AIO Writes'},
 490     ],
 491 };
 492
 493 #---------------------------------------------------------------------
 494
 495 $graphs{innodb_log} = {
 496     config => {
 497         global_attrs => {
 498             title  => 'InnoDB Log',
 499             vlabel => 'Log activity per ${graph_period}',
 500         },
 501         data_source_attrs => {
 502             draw => 'LINE1',
 503         },
 504     },
 505     data_sources => [
 506         {name => 'innodb_log_buffer_size', label  => 'Buffer Size',
 507                                            type   => 'GAUGE',
 508                                            draw   => 'AREA',
 509                                            colour => 'fafd9e'},
 510         {name => 'ib_log_flush',           label => 'KB Flushed'},
 511         {name => 'ib_log_written',         label => 'KB Written'},
 512     ],
 513 };
 514
 515 #---------------------------------------------------------------------
 516
 517 $graphs{innodb_rows} = {
 518     config => {
 519         global_attrs => {
 520             title  => 'InnoDB Row Operations',
 521             vlabel => 'Operations per ${graph_period}',
 522             total  => 'Total',
 523         },
 524         data_source_attrs => {},
 525     },
 526     data_sources => [
 527         {name => 'Innodb_rows_deleted',  label => 'Deletes'},
 528         {name => 'Innodb_rows_inserted', label => 'Inserts'},
 529         {name => 'Innodb_rows_read',     label => 'Reads'},
 530         {name => 'Innodb_rows_updated',  label => 'Updates'},
 531     ],
 532 };
 533
 534 #---------------------------------------------------------------------
 535
 536 $graphs{innodb_semaphores} = {
 537     config => {
 538         global_attrs => {
 539             title  => 'InnoDB Semaphores',
 540             vlabel => 'Semaphores per ${graph_period}',
 541         },
 542         data_source_attrs => {
 543             draw  => 'LINE1',
 544         },
 545     },
 546     data_sources => [
 547         {name => 'ib_spin_rounds', label => 'Spin Rounds'},
 548         {name => 'ib_spin_waits',  label => 'Spin Waits'},
 549         {name => 'ib_os_waits',    label => 'OS Waits'},
 550     ],
 551 };
 552
 553 #---------------------------------------------------------------------
 554
 555 $graphs{innodb_tnx} = {
 556     config => {
 557         global_attrs => {
 558             title  => 'InnoDB Transactions',
 559             vlabel => 'Transactions per ${graph_period}',
 560         },
 561         data_source_attrs => {
 562             draw  => 'LINE1',
 563         },
 564     },
 565     data_sources => [
 566         {name => 'ib_tnx', label => 'Transactions created'},
 567     ],
 568 };
 569
 570 #---------------------------------------------------------------------
 571
 572 $graphs{myisam_indexes} = {
 573     config => {
 574         global_attrs => {
 575             title  => 'MyISAM Indexes',
 576             vlabel => 'Requests per ${graph_period}',
 577         },
 578         data_source_attrs => {
 579             draw  => 'LINE2',
 580         },
 581     },
 582     data_sources => [
 583         {name => 'Key_read_requests',  label => 'Key read requests'},
 584         {name => 'Key_reads',          label => 'Key reads'},
 585         {name => 'Key_write_requests', label => 'Key write requests'},
 586         {name => 'Key_writes',         label => 'Key writes'},
 587    ],
 588 };
 589
 590 #---------------------------------------------------------------------
 591
 592 $graphs{network_traffic} = {
 593     config => {
 594         global_attrs => {
 595             title  => 'Network Traffic',
 596             args   => '--base 1024',
 597             vlabel => 'Bytes received (-) / sent (+) per ${graph_period}',
 598         },
 599         data_source_attrs => {
 600             draw  => 'LINE2',
 601         },
 602     },
 603     data_sources => [
 604         {name => 'Bytes_received', label => 'Bytes transfered',
 605                                    graph => 'no'},
 606         {name => 'Bytes_sent',     label    => 'Bytes transfered',
 607                                    negative => 'Bytes_received'},
 608     ],
 609 };
 610
 611 #---------------------------------------------------------------------
 612
 613 $graphs{qcache} = {
 614     config => {
 615         global_attrs => {
 616             title  => 'Query Cache',
 617             vlabel => 'Commands per ${graph_period}',
 618         },
 619         data_source_attrs => {
 620              draw => 'LINE1',
 621         },
 622     },
 623     data_sources => [
 624         {name => 'Qcache_queries_in_cache', label => 'Queries in cache'},
 625         {name => 'Qcache_hits',             label => 'Cache hits'},
 626         {name => 'Qcache_inserts',          label => 'Inserts'},
 627         {name => 'Qcache_not_cached',       label => 'Not cached'},
 628         {name => 'Qcache_lowmem_prunes',    label => 'Low-memory prunes'},
 629     ],
 630 };
 631
 632 #---------------------------------------------------------------------
 633
 634 $graphs{qcache_mem} = {
 635     config => {
 636         global_attrs => {
 637             title  => 'Query Cache Memory',
 638             vlabel => 'Bytes',
 639             args   => '--base 1024 --lower-limit 0',
 640         },
 641         data_source_attrs => {
 642             draw => 'AREA',
 643             type => 'GAUGE',
 644         },
 645     },
 646     data_sources => [
 647         {name => 'query_cache_size',    label => 'Cache size'},
 648         {name => 'Qcache_free_memory',  label => 'Free mem'},
 649     ],
 650 };
 651
 652 #---------------------------------------------------------------------
 653
 654 $graphs{replication} = {
 655     config => {
 656         global_attrs => {
 657             title  => 'Replication',
 658             vlabel => 'Activity',
 659         },
 660         data_source_attrs => {
 661             draw => 'LINE1',
 662         },
 663     },
 664     data_sources => [
 665         {name => 'slave_io_running',           label => 'Slave IO Running',
 666                                                type  => 'GAUGE',
 667                                                draw  => 'AREA'},
 668         {name => 'slave_sql_running',          label => 'Slave SQL Running',
 669                                                type  => 'GAUGE',
 670                                                draw  => 'AREA'},
 671         {name => 'Slave_retried_transactions', label => 'Retried Transactions'},
 672         {name => 'Slave_open_temp_tables',     label => 'Open Temp Tables'},
 673         {name => 'seconds_behind_master',      label => 'Secs Behind Master',
 674                                                type  => 'GAUGE'},
 675     ],
 676 };
 677
 678 #---------------------------------------------------------------------
 679
 680 $graphs{select_types} = {
 681     config => {
 682         global_attrs => {
 683             title  => 'Select types',
 684             vlabel => 'Commands per ${graph_period}',
 685             total  => 'Total',
 686         },
 687         data_source_attrs => {},
 688     },
 689     data_sources => [
 690         {name => 'Select_full_join',       label => 'Full join'},
 691         {name => 'Select_full_range_join', label => 'Full range'},
 692         {name => 'Select_range',           label => 'Range'},
 693         {name => 'Select_range_check',     label => 'Range check'},
 694         {name => 'Select_scan',            label => 'Scan'},
 695     ],
 696 };
 697
 698 #---------------------------------------------------------------------
 699
 700 $graphs{slow} = {
 701     config => {
 702         global_attrs => {
 703             title  => 'Slow Queries',
 704             vlabel => 'Slow queries per ${graph_period}',
 705         },
 706         data_source_attrs => {
 707             draw  => 'LINE2',
 708         },
 709     },
 710     data_sources => [
 711         {name => 'Slow_queries', label => 'Slow queries'},
 712     ],
 713 };
 714
 715 #---------------------------------------------------------------------
 716
 717 $graphs{sorts} = {
 718     config => {
 719         global_attrs => {
 720             title  => 'Sorts',
 721             vlabel => 'Sorts / ${graph_period}',
 722         },
 723         data_source_attrs => {
 724             draw  => 'LINE2',
 725         },
 726     },
 727     data_sources => [
 728         {name => 'Sort_rows',         label => 'Rows sorted'},
 729         {name => 'Sort_range',        label => 'Range'},
 730         {name => 'Sort_merge_passes', label => 'Merge passes'},
 731         {name => 'Sort_scan',         label => 'Scan'},
 732     ],
 733 };
 734
 735 #---------------------------------------------------------------------
 736
 737 $graphs{table_locks} = {
 738     config => {
 739         global_attrs => {
 740             title  => 'Table locks',
 741             vlabel => 'locks per ${graph_period}',
 742         },
 743         data_source_attrs => {
 744             draw  => 'LINE2',
 745         },
 746     },
 747     data_sources => [
 748         {name => 'Table_locks_immediate', label => 'Table locks immed'},
 749         {name => 'Table_locks_waited',    label => 'Table locks waited'},
 750     ],
 751 };
 752
 753 #---------------------------------------------------------------------
 754
 755 $graphs{tmp_tables} = {
 756     config => {
 757         global_attrs => {
 758             title  => 'Temporary objects',
 759             vlabel => 'Objects per ${graph_period}',
 760         },
 761         data_source_attrs => {
 762             draw  => 'LINE2',
 763         },
 764     },
 765     data_sources => [
 766         {name => 'Created_tmp_disk_tables', label => 'Temp disk tables'},
 767         {name => 'Created_tmp_tables',      label => 'Temp tables'},
 768         {name => 'Created_tmp_files',       label => 'Temp files'},
 769     ],
 770 };
 771
 772 #---------------------------------------------------------------------
 773 #  Plugin Graphs
 774 #  These are mysql plugins of type INFORMATION SCHEMA
 775 #
 776 #  These will be added to $graphs if available
 777 #---------------------------------------------------------------------
 778
 779 my %graph_plugins = ();
 780
 781 $graph_plugins{query_response_time} = {
 782     count => {
 783         config => {
 784             global_attrs => {
 785                 title  => 'Query Response Time Count',
 786                 vlabel  => 'queries per ${graph_period}',
 787             },
 788             data_source_attrs => {
 789                 draw  => 'LINE2',
 790                 type => 'DERIVE',
 791             },
 792         },
 793         # data_sources are populated by sub plugin_query_response_time
 794         data_sources => [
 795         ],
 796    },
 797    total => {
 798         config => {
 799             global_attrs => {
 800                 title  => 'Query Response Time Total',
 801                 vlabel  => 'query time (microseconds) per ${graph_period}',
 802             },
 803             data_source_attrs => {
 804                 draw  => 'LINE2',
 805                 type => 'DERIVE',
 806             },
 807         },
 808         # data_sources are populated by sub plugin_query_response_time
 809         data_sources => [
 810         ],
 811    }
 812 };
 813
 814 $graph_plugins{user_statistics} = {
 815     connections => {
 816         config => {
 817             global_attrs => {
 818                 title  => 'User Connections',
 819                 vlabel  => 'connections per ${graph_period}',
 820             },
 821             data_source_attrs => {
 822                 draw  => 'LINE2',
 823                 type => 'DERIVE',
 824             },
 825         },
 826         cols => { 'total_connections' => {}, 'concurrent_connections' => {}, 'denied_connections' => {}, 'lost_connections'  => {}},
 827         data_sources => [
 828         ],
 829    },
 830    usertime => {
 831         config => {
 832             global_attrs => {
 833                 title  => 'User Time',
 834                 vlabel  => 'seconds',
 835             },
 836             data_source_attrs => {
 837                 draw  => 'LINE2',
 838                 type => 'DERIVE',
 839             },
 840         },
 841         cols => { 'connected_time' => {}, 'busy_time' => {}, 'cpu_time' => {} },
 842         data_sources => [
 843         ],
 844    },
 845    bytes => {
 846         config => {
 847             global_attrs => {
 848                 title  => 'User Bytes',
 849                 vlabel  => 'bytes',
 850             },
 851             data_source_attrs => {
 852                 draw  => 'LINE2',
 853                 type => 'DERIVE',
 854             },
 855         },
 856         cols => { 'bytes_received' => {}, 'bytes_sent' => {}, 'binlog_bytes_written' => {} },
 857         data_sources => [
 858         ],
 859    },
 860    rows => {
 861         config => {
 862             global_attrs => {
 863                 title  => 'User Rows',
 864                 vlabel  => 'rows',
 865             },
 866             data_source_attrs => {
 867                 draw  => 'LINE2',
 868                 type => 'DERIVE',
 869             },
 870         },
 871         cols => { 'rows_read' => {}, 'rows_sent' => {}, 'rows_deleted' => {}, 'rows_inserted' => {}, 'rows_updated' => {} },
 872         data_sources => [
 873         ],
 874    },
 875    commands => {
 876         config => {
 877             global_attrs => {
 878                 title  => 'Command breakdown by user',
 879                 vlabel  => 'commands',
 880             },
 881             data_source_attrs => {
 882                 draw  => 'LINE2',
 883                 type => 'DERIVE',
 884             },
 885         },
 886         cols => { 'select_commands' => {}, 'update_commands' => {}, 'other_commands' => {}, 'commit_transactions' => {}, 'rollback_transactions' => {} },
 887         data_sources => [
 888         ],
 889    }
 890
 891 };
 892 #---------------------------------------------------------------------
 893 #  M A I N
 894 #---------------------------------------------------------------------
 895
 896
 897 #
 898 # Global hash holding the data collected from mysql.
 899 #
 900 our $data; # Was 'my'. Changed to 'our' to facilitate testing.
 901
 902
 903 sub main {
 904     my $graph = basename($0);
 905     $graph =~ s/^mysql[0-9]*_//g; # allow multiple instances
 906     my $command = $ARGV[0] || 'show';
 907
 908     my %command_map = (
 909         'autoconf' => \&autoconf,
 910         'config'   => \&config,
 911         'show'     => \&show,
 912         'suggest'  => \&suggest,
 913     );
 914
 915     die "Unknown command: $command"
 916         unless exists $command_map{$command};
 917
 918     die "Missing dependency Cache::Cache"
 919         unless $has_cache || $command eq 'autoconf';
 920
 921     return $command_map{$command}->($graph);
 922 }
 923
 924
 925 #---------------------------------------------------------------------
 926 #  C O M M A N D   H A N D L E R S
 927 #---------------------------------------------------------------------
 928
 929 # Each command handler should return an appropriate exit code
 930
 931
 932 # http://munin-monitoring.org/wiki/ConcisePlugins#autoconf
 933 sub autoconf {
 934     unless ($has_cache) {
 935         print "no (Missing dependency Cache::Cache)\n";
 936         return 0;
 937     }
 938
 939     eval {
 940         db_connect();
 941     };
 942     if ($@) {
 943         my $err = $@;
 944         $err =~ s{\s at \s \S+ \s line .*}{}xms;
 945         print "no ($err)\n";
 946         return 0;
 947     }
 948     print "yes\n";
 949     return 0;
 950 }
 951
 952
 953 # http://munin-monitoring.org/wiki/ConcisePlugins#suggest
 954 sub suggest {
 955
 956     # What is the best way to decide which graphs is applicable to a
 957     # given system?
 958     #
 959     # Does the database use InnoDB? A zero count from:
 960     #
 961     #   SELECT COUNT(*)
 962     #     FROM tables
 963     #    WHERE table_type = 'base table'
 964     #      AND engine     = 'innodb'
 965     #
 966     # Does the database use binary logs? 'OFF' as the result from:
 967     #
 968     #   SHOW GLOBAL variables LIKE 'log_bin'
 969     #
 970     # Is the database setup as a slave? Empty result from:
 971     #
 972     #   SHOW SLAVE STATUS
 973
 974     foreach my $graph (sort keys(%graphs)) {
 975         next if $graph =~ /innodb_/ && $data->{_innodb_disabled};
 976         next if $graph =~ /wsrep_/ && $data->{_galera_disabled};
 977         print "$graph\n";
 978     }
 979
 980     return 0;
 981 }
 982
 983
 984 sub config {
 985     my $graph_name = shift;
 986
 987     # In MySQL 5.1 (and probably erlier versions as well) status
 988     # variables are unique when looking at the last 19 characters.
 989     #
 990     #   SELECT RIGHT(variable_name, 19), COUNT(*)
 991     #     FROM information_schema.global_status
 992     #    GROUP BY RIGHT(variable_name, 19)
 993     #   HAVING COUNT(*) > 1;
 994     #
 995     #   Empty set (0.06 sec)
 996     #
 997     # There is one duplicate when looking at server variables
 998     #
 999     #   SELECT RIGHT(variable_name, 19), COUNT(*)
1000     #     FROM information_schema.global_variables
1001     #    GROUP BY RIGHT(variable_name, 19)
1002     #   HAVING COUNT(*) > 1;
1003     #
1004     #   +--------------------------+----------+
1005     #   | RIGHT(variable_name, 19) | COUNT(*) |
1006     #   +--------------------------+----------+
1007     #   | OW_PRIORITY_UPDATES      |        2 |
1008     #   +--------------------------+----------+
1009     #   1 row in set (0.05 sec)
1010     #
1011     #   show global variables like '%OW_PRIORITY_UPDATES';
1012     #
1013     #   +--------------------------+-------+
1014     #   | Variable_name            | Value |
1015     #   +--------------------------+-------+
1016     #   | low_priority_updates     | OFF   |
1017     #   | sql_low_priority_updates | OFF   |
1018     #   +--------------------------+-------+
1019     #   2 rows in set (0.00 sec)
1020     #
1021     # Not a problem since we don't graph these
1022
1023     update_data();
1024
1025     die 'Unknown graph ' . ($graph_name ? $graph_name : '')
1026         unless $graphs{$graph_name};
1027
1028     my $graph = $graphs{$graph_name};
1029
1030     my %conf = (%{$defaults{global_attrs}}, %{$graph->{config}{global_attrs}});
1031     while (my ($k, $v) = each %conf) {
1032         print "graph_$k $v\n";
1033     }
1034     print "graph_category mysql2\n";
1035
1036     for my $ds (@{$graph->{data_sources}}) {
1037         my %ds_spec = (
1038             %{$defaults{data_source_attrs}},
1039             %{$graph->{config}{data_source_attrs}},
1040             %$ds,
1041         );
1042         while (my ($k, $v) = each %ds_spec) {
1043             # 'name' is only used internally in this script, not understood by munin.
1044             printf("%s.%s %s\n", clean_fieldname($ds->{name}), $k, $v) unless ($k eq 'name');
1045         }
1046         print_thresholds(clean_fieldname($ds->{name}));
1047     }
1048
1049     return 0;
1050 }
1051
1052 sub show {
1053     my $graph_name = shift;
1054
1055     update_data();
1056
1057     die 'Unknown graph ' . ($graph_name ? $graph_name : '')
1058         unless $graphs{$graph_name};
1059
1060     my $graph = $graphs{$graph_name};
1061
1062     die "Can't show data for '$graph_name' because InnoDB is disabled."
1063         if $graph_name =~ /innodb_/ && $data->{_innodb_disabled};
1064
1065     for my $ds (@{$graph->{data_sources}}) {
1066         my $value = exists $ds->{value}
1067             ? $ds->{value}($data)
1068                 : $data->{$ds->{name}};
1069
1070         printf "%s.value %s\n", clean_fieldname($ds->{name}), defined($value) ? $value : 'U';
1071     }
1072
1073     return 0;
1074 }
1075
1076
1077
1078 #---------------------------------------------------------------------
1079 #  U T I L I T Y   S U B S
1080 #---------------------------------------------------------------------
1081
1082
1083 sub db_connect {
1084     my $dsn = "$config{dsn};mysql_connect_timeout=5";
1085
1086     return DBI->connect($dsn, $config{user}, $config{password}, {
1087         RaiseError       => 1,
1088         PrintError       => 0,
1089         FetchHashKeyName => 'NAME_lc',
1090     });
1091 }
1092
1093
1094 sub update_data {
1095     $data = $shared_memory_cache->get('data');
1096     my $graphs_stored = $shared_memory_cache->get('graphs');
1097     %graphs = %{thaw($graphs_stored)} if $graphs_stored;
1098     return if $data;
1099
1100     #warn "Need to update cache";
1101
1102     $data = {};
1103
1104     my $dbh = db_connect();
1105
1106     # Set up defaults in case the server is not a slave
1107     $data->{relay_log_space} = 0;
1108     $data->{slave_running}   = 0;
1109     $data->{slave_stopped}   = 0;
1110
1111     # Set up defaults in case binlog is not enabled
1112     $data->{ma_binlog_size} = 0;
1113
1114     update_variables($dbh);
1115     update_plugins($dbh);
1116     update_innodb($dbh);
1117     update_master($dbh);
1118     update_slave($dbh);
1119
1120     $shared_memory_cache->set('data', $data);
1121     $shared_memory_cache->set('graphs', nfreeze(\%graphs));
1122 }
1123
1124
1125 sub update_plugins {
1126     my ($dbh) = @_;
1127
1128     my %plugin_map = (
1129         'query_response_time'         => \&plugin_query_response_time,
1130     );
1131
1132     sub add_graphs {
1133       my ($f, $sec, $dbh, %g) = @_;
1134       if ($f->($dbh) == 0) {
1135         while (my ($k, $v) = each %g) {
1136           $graphs{$sec . '_' . $k} = $v;
1137         }
1138       }
1139     }
1140
1141     my $sth = $dbh->prepare("SHOW PLUGINS");
1142     $sth->execute();
1143     while (my $row = $sth->fetchrow_hashref()) {
1144         next if $row->{'type'} ne 'INFORMATION SCHEMA';
1145         my $sec = lc $row->{'name'};
1146         next if not exists $plugin_map{$sec};
1147         add_graphs($plugin_map{$sec}, $sec, $dbh, %{$graph_plugins{$sec}});
1148     }
1149     $sth->finish();
1150
1151     my %is_map = (
1152         'user_statistics'         => \&is_user_statistics,
1153     );
1154
1155     $sth = $dbh->prepare("SHOW TABLES IN INFORMATION_SCHEMA");
1156     $sth->execute();
1157     while (my $row = $sth->fetchrow_hashref()) {
1158         my $sec = lc $row->{'tables_in_information_schema'};
1159         next if not exists $is_map{$sec};
1160         add_graphs($is_map{$sec}, $sec, $dbh, %{$graph_plugins{$sec}});
1161     }
1162     $sth->finish();
1163 }
1164
1165 sub update_variables {
1166     my ($dbh) = @_;
1167     my @queries = (
1168         'SHOW GLOBAL STATUS',
1169         'SHOW GLOBAL VARIABLES',
1170     );
1171
1172     my %variable_name_map = (
1173         table_cache => 'table_open_cache', # table_open_cache was
1174                                            # previously known as
1175                                            # table_cache in MySQL
1176                                            # 5.1.2 and earlier.
1177     );
1178
1179     for my $query (@queries) {
1180         $data->{$query} = {};
1181
1182         my $sth = $dbh->prepare($query);
1183         $sth->execute();
1184         while (my $row = $sth->fetch) {
1185             my $var = $variable_name_map{$row->[0]} || $row->[0];
1186             $data->{$var} = $row->[1];
1187         }
1188         $sth->finish();
1189     }
1190 }
1191
1192
1193 sub update_innodb {
1194     my ($dbh) = @_;
1195
1196     my $sth = $dbh->prepare('SHOW /*!50000 ENGINE*/ INNODB STATUS');
1197     eval {
1198         $sth->execute();
1199     };
1200     if ($@) {
1201         if ($@ =~ /Unknown (storage|table) engine 'INNODB'|Cannot call SHOW INNODB STATUS because skip-innodb is defined/i) {
1202             $data->{_innodb_disabled} = 1;
1203             return;
1204         }
1205         die $@;
1206     }
1207     my $row = $sth->fetchrow_hashref();
1208     my $status = $row->{'status'};
1209     $sth->finish();
1210
1211     parse_innodb_status($status);
1212 }
1213
1214
1215 sub update_master {
1216     my ($dbh) = @_;
1217
1218     my $sth = $dbh->prepare('SHOW MASTER LOGS');
1219     eval {
1220         $sth->execute();
1221     };
1222     if ($@) {
1223         # SHOW MASTER LOGS failed becuase binlog is not enabled
1224         return if $@ =~ /You are not using binary logging/;
1225         die $@;
1226     }
1227
1228     while (my $row = $sth->fetch) {
1229         $data->{ma_binlog_size} += $row->[1];
1230     }
1231
1232     $sth->finish();
1233 }
1234
1235
1236 sub update_slave {
1237     my ($dbh) = @_;
1238
1239     my $sth = $dbh->prepare('SHOW SLAVE STATUS');
1240     $sth->execute();
1241     my $row = $sth->fetchrow_hashref();
1242     return unless $row;
1243     while (my ($k, $v) = each %$row) {
1244         $data->{$k} = $v;
1245     }
1246     $sth->finish();
1247
1248     # undef when slave is stopped, or when MySQL fails to calculate
1249     # the lag (which happens depresingly often). (mk-heartbeat fixes
1250     # this problem.)
1251     $data->{seconds_behind_master} ||= 0;
1252
1253     # Track these two fields so we can trigger warnings if the slave stops
1254     # running
1255     $data->{slave_sql_running} = ($data->{slave_sql_running} eq 'Yes')
1256             ? 0 : 1;
1257     $data->{slave_io_running} = ($data->{slave_io_running} eq 'Yes')
1258             ? 0 : 1;
1259
1260 }
1261
1262
1263 #---------------------------------------------------------------------
1264 #  Information SCHEMA tables represent data to be processed
1265 #---------------------------------------------------------------------
1266
1267
1268 sub plugin_query_response_time {
1269     my ($dbh) = @_;
1270
1271     return 1 if not defined $data->{query_response_time_stats};
1272     return 1 if $data->{query_response_time_stats} eq 'OFF';
1273
1274     my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME");
1275     $sth->execute();
1276     while (my $row = $sth->fetchrow_hashref()) {
1277         my $time = $row->{'time'};
1278         $data->{'query_response_time_count_' . $time} = $row->{'count'};
1279         push @{$graph_plugins{query_response_time}->{count}->{data_sources}}, {name => 'query_response_time_count_' . $time, label => $time };
1280         next if $row->{'total'} eq 'TOO LONG';
1281         $data->{'query_response_time_total_' . $time} = $row->{'total'} * 1e6;
1282         push @{$graph_plugins{query_response_time}->{total}->{data_sources}}, {name => 'query_response_time_total_' . $time, label => $time };
1283     }
1284     $sth->finish();
1285
1286     return 0;
1287 }
1288
1289 sub is_user_statistics {
1290     my ($dbh) = @_;
1291
1292     return 1 if not defined $data->{userstat};
1293     return 1 if $data->{userstat} eq 'OFF';
1294
1295     my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS");
1296     $sth->execute();
1297     while (my $row = $sth->fetchrow_hashref()) {
1298         my $user = $row->{'user'};
1299         my $var;
1300         while (my ($g, $v) = each %{$graph_plugins{user_statistics}}) {
1301           while (my ($userstat,$conf) = each %{$v->{cols}}) {
1302             $var = 'user_stats_' . $user . '_' . $userstat;
1303             $data->{$var} = int $row->{$userstat};
1304             my $ds = { %$conf };
1305             $ds->{name} = $var;
1306             $ds->{label} = $user . ' ' . $userstat;
1307             push @{$graph_plugins{user_statistics}->{$g}->{data_sources}}, $ds;
1308           }
1309         }
1310     }
1311     $sth->finish();
1312     return 0;
1313 }
1314
1315 #
1316 # In 'SHOW ENGINE INNODB STATUS' 64 bit integers are not formated as
1317 # plain integers. They are either:
1318 #
1319 #   - split in two and needs to be shifted together,
1320 #   - or hexadecimal
1321 #
1322 sub innodb_bigint {
1323     my ($x, $y) = @_;
1324
1325     return defined $y
1326         ? Math::BigInt->new($x)->blsft(32) + $y
1327         : Math::BigInt->new("0x$x");
1328 }
1329
1330 #---------------------------------------------------------------------
1331 #  P A R S E   'SHOW ENGINE INNODB STATUS'   O U T P U T
1332 #---------------------------------------------------------------------
1333
1334
1335 # A nice walk through
1336 # http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/
1337
1338 # The parsing is split in one subrutine per section. Each subroutine
1339 # should parse a block with the following structure
1340 #
1341 # block body ...
1342 # more lines ....
1343 # ----------
1344
1345 sub parse_innodb_status {
1346     local $_ = shift;
1347
1348     # Add a dummy section to the end in case the innodb status output
1349     # has been truncated (Happens for status > 64K characters)
1350     $_ .= "\n----------\nDUMMY\n";
1351
1352     my %section_map = (
1353
1354         'BUFFER POOL AND MEMORY'      => \&parse_buffer_pool_and_memory,
1355         'INDIVIDUAL BUFFER POOL INFO' => \&skip,
1356         'FILE I/O'                    => \&parse_file_io,
1357         'INSERT BUFFER AND ADAPTIVE HASH INDEX'
1358             => \&parse_insert_buffer_and_adaptive_hash_index,
1359         'LATEST DETECTED DEADLOCK'    => \&skip,
1360         'LATEST FOREIGN KEY ERROR'    => \&skip,
1361         'LOG'                         => \&parse_log,
1362         'ROW OPERATIONS'              => \&skip,
1363         'SEMAPHORES'                  => \&parse_semaphores,
1364         'TRANSACTIONS'                => \&parse_transactions,
1365         'BACKGROUND THREAD'           => \&skip,
1366     );
1367
1368     skip_heading();
1369     for (;;) {
1370         m/\G(.*)\n/gc;
1371         my $sec = $1;
1372
1373         last if $sec eq 'END OF INNODB MONITOR OUTPUT';
1374         if ($sec eq 'DUMMY') {
1375             handle_incomplete_innodb_status();
1376             last;
1377         }
1378
1379         die "Unknown section: $1" unless exists $section_map{$sec};
1380         die "Parse error. Expected a section separator" unless m/\G-+\n/gc;
1381
1382         $section_map{$sec}->();
1383     }
1384 }
1385
1386
1387 # This regular expression handles the different formating of 64-bit
1388 # integers in different versions of the innodb engine. Either two
1389 # decimal 32-bit integers seperated by a space, or a single
1390 # hexadecimal 64-bit integer.
1391 my $innodb_bigint_rx = qr{([[a-fA-F\d]+)(?: (\d+))?};
1392
1393
1394 sub match_dashes { return m/\G-+\n(?!-)/gc; }
1395
1396
1397 sub skip_line    { return m/\G.*\n/gc; }
1398
1399
1400 sub skip_heading {
1401     # Heading is 6 lines
1402     for my $foo (1...6) {
1403         skip_line or die('Parse error');
1404     }
1405 }
1406
1407
1408 sub parse_section {
1409     my ($parser) = @_;
1410
1411     #warn substr($_, pos(), 10);
1412     for (;;) {
1413         return if match_dashes();
1414         next if $parser->();
1415         skip_line();
1416     }
1417 }
1418
1419
1420 sub skip { parse_section(sub {}); }
1421
1422
1423 sub parse_semaphores {
1424     parse_section(
1425         sub {
1426             m/\GMutex spin waits (\d+), rounds (\d+), OS waits (\d+)\n/gc && do {
1427                 $data->{ib_spin_waits}  = $1;
1428                 $data->{ib_spin_rounds} = $2;
1429                 $data->{ib_os_waits}    = $3;
1430                 return 1;
1431             };
1432         }
1433     );
1434 }
1435
1436
1437 sub parse_transactions {
1438     parse_section(
1439         sub {
1440             m/\GTrx id counter $innodb_bigint_rx\n/gc && do {
1441                 $data->{ib_tnx} = innodb_bigint($1, $2);
1442                 return 1;
1443             };
1444             m/\GPurge done for trx's n:o < $innodb_bigint_rx undo n:o < $innodb_bigint_rx\n/gc && do {
1445                 if (defined $3) {
1446                     # old format
1447                     $data->{ib_tnx_prg} = innodb_bigint($1, $2);
1448                     # FIX add to data? innodb_bigint($3, $4);
1449                 }
1450                 else {
1451                     # new format
1452                     $data->{ib_tnx_prg} = innodb_bigint($1);
1453                     # FIX add to data? innodb_bigint($2);
1454                 }
1455                 return 1;
1456             };
1457             m/\GHistory list length (\d+)\n/gc && do {
1458                 $data->{ib_tnx_hist} = $1;
1459                 return 1;
1460             };
1461         }
1462     );
1463
1464 }
1465
1466
1467 sub parse_file_io {
1468     parse_section(
1469         sub {
1470             m/\GPending normal aio reads: (\d+), aio writes: (\d+),\n\s*ibuf aio reads: (\d+), log i\/o's: (\d+), sync i\/o's: (\d+)\n/gc && do {
1471                 $data->{ib_iop_aioread}  = $1;
1472                 $data->{ib_iop_aiowrite} = $2;
1473                 $data->{ib_iop_ibuf_aio} = $3;
1474                 $data->{ib_iop_log}      = $4;
1475                 $data->{ib_iop_sync}     = $5;
1476                 return 1;
1477             };
1478             m/\GPending flushes \(fsync\) log: (\d+); buffer pool: (\d+)\n/gc && do {
1479                 $data->{ib_iop_flush_log}   = $1;
1480                 $data->{ib_iop_flush_bpool} = $2;
1481                 return 1;
1482             };
1483             m/\G(\d+) OS file reads, (\d+) OS file writes, (\d+) OS fsyncs\n/gc && do {
1484                 $data->{ib_io_read}  = $1;
1485                 $data->{ib_io_write} = $2;
1486                 $data->{ib_io_fsync} = $3;
1487                 return 1;
1488             };
1489         }
1490     );
1491 }
1492
1493
1494 sub parse_insert_buffer_and_adaptive_hash_index {
1495     parse_section(
1496         sub {
1497       # MySQL < 5.5
1498       m/\G(\d+) inserts, (\d+) merged recs, (\d+) merges\n/gc && do {
1499         $data->{ib_ibuf_inserts}    = $1;
1500         $data->{ib_ibuf_merged_rec} = $2;
1501         $data->{ib_ibuf_merges}     = $3;
1502         return 1;
1503       };
1504       # MySQL >= 5.5
1505       m/\Gmerged operations:\n insert (\d+), delete mark \d+, delete \d+\ndiscarded operations:\n insert (\d+), delete mark \d+, delete \d+\n/gc && do {
1506         $data->{ib_ibuf_inserts} = $1;
1507         $data->{ib_ibuf_merged_rec} = $1 + $2;
1508         return 1;
1509       };
1510       m/\GIbuf: size (\d+), free list len (\d+), seg size (\d+),(?: (\d+) merges)?\n/gc && do {
1511         $data->{ib_ibuf_size}     = $1;
1512         $data->{ib_ibuf_free_len} = $2;
1513         $data->{ib_ibuf_seg_size} = $3;
1514         $data->{ib_ibuf_merges}   = $4 if defined $4; # MySQL >= 5.5
1515         return 1;
1516       };
1517         }
1518     );
1519 }
1520
1521
1522 sub parse_log {
1523     parse_section(
1524         sub {
1525             m/\GLog sequence number $innodb_bigint_rx\n/gc && do {
1526                 $data->{ib_log_written} = innodb_bigint($1, $2);
1527                 return 1;
1528             };
1529             m/\GLog flushed up to\s+$innodb_bigint_rx\n/gc && do {
1530                 $data->{ib_log_flush} = innodb_bigint($1, $2);
1531                 return 1;
1532             };
1533             m/\G(\d+) log i\/o's done.*\n/gc && do {
1534                 $data->{ib_io_log} = $1;
1535                 return 1;
1536             };
1537         }
1538     );
1539 }
1540
1541
1542 sub parse_buffer_pool_and_memory {
1543     parse_section(
1544         sub {
1545             m/\GBuffer pool size\s+(\d+)\n/gc && do {
1546                 $data->{ib_bpool_size} = $1;
1547                 return 1;
1548             };
1549             m/\GFree buffers\s+(\d+)\n/gc && do {
1550                 $data->{ib_bpool_free} = $1;
1551                 return 1;
1552             };
1553             m/\GDatabase pages\s+(\d+)\n/gc && do {
1554                 $data->{ib_bpool_dbpages} = $1;
1555                 return 1;
1556             };
1557             m/\GModified db pages\s+(\d+)\n/gc && do {
1558                 $data->{ib_bpool_modpages} = $1;
1559                 return 1;
1560             };
1561             m/\GPages read (\d+), created (\d+), written (\d+)\n/gc && do {
1562                 $data->{ib_bpool_read}    = $1;
1563                 $data->{ib_bpool_created} = $2;
1564                 $data->{ib_bpool_written} = $3;
1565                 return 1;
1566             };
1567         }
1568     );
1569 }
1570
1571
1572 sub handle_incomplete_innodb_status {
1573
1574     warn "Output from SHOW ENGINE INNDOB STATUS was truncated. "
1575         . "This happens if the output of SEIS exceeds 64KB. "
1576         . "Several of the InnoDB graphs might be affected by this.";
1577
1578     # FIX Is it possible to find some of the missing values from SHOW
1579     # STATUS?
1580 }
1581
1582
1583 exit main() unless caller;
1584
1585
1586 1;