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                 period => 'minute',
 706         },
 707         data_source_attrs => {
 708             draw  => 'LINE2',
 709         },
 710     },
 711     data_sources => [
 712         {name => 'Slow_queries', label => 'Slow queries'},
 713     ],
 714 };
 715
 716 #---------------------------------------------------------------------
 717
 718 $graphs{sorts} = {
 719     config => {
 720         global_attrs => {
 721             title  => 'Sorts',
 722             vlabel => 'Sorts / ${graph_period}',
 723         },
 724         data_source_attrs => {
 725             draw  => 'LINE2',
 726         },
 727     },
 728     data_sources => [
 729         {name => 'Sort_rows',         label => 'Rows sorted'},
 730         {name => 'Sort_range',        label => 'Range'},
 731         {name => 'Sort_merge_passes', label => 'Merge passes'},
 732         {name => 'Sort_scan',         label => 'Scan'},
 733     ],
 734 };
 735
 736 #---------------------------------------------------------------------
 737
 738 $graphs{table_locks} = {
 739     config => {
 740         global_attrs => {
 741             title  => 'Table locks',
 742             vlabel => 'locks per ${graph_period}',
 743         },
 744         data_source_attrs => {
 745             draw  => 'LINE2',
 746         },
 747     },
 748     data_sources => [
 749         {name => 'Table_locks_immediate', label => 'Table locks immed'},
 750         {name => 'Table_locks_waited',    label => 'Table locks waited'},
 751     ],
 752 };
 753
 754 #---------------------------------------------------------------------
 755
 756 $graphs{tmp_tables} = {
 757     config => {
 758         global_attrs => {
 759             title  => 'Temporary objects',
 760             vlabel => 'Objects per ${graph_period}',
 761         },
 762         data_source_attrs => {
 763             draw  => 'LINE2',
 764         },
 765     },
 766     data_sources => [
 767         {name => 'Created_tmp_disk_tables', label => 'Temp disk tables'},
 768         {name => 'Created_tmp_tables',      label => 'Temp tables'},
 769         {name => 'Created_tmp_files',       label => 'Temp files'},
 770     ],
 771 };
 772
 773 #---------------------------------------------------------------------
 774 #  Plugin Graphs
 775 #  These are mysql plugins of type INFORMATION SCHEMA
 776 #
 777 #  These will be added to $graphs if available
 778 #---------------------------------------------------------------------
 779
 780 my %graph_plugins = ();
 781
 782 $graph_plugins{query_response_time} = {
 783     count => {
 784         config => {
 785             global_attrs => {
 786                 title  => 'Query Response Time Count',
 787                 vlabel  => 'queries per ${graph_period}',
 788             },
 789             data_source_attrs => {
 790                 draw  => 'LINE2',
 791                 type => 'DERIVE',
 792             },
 793         },
 794         # data_sources are populated by sub plugin_query_response_time
 795         data_sources => [
 796         ],
 797    },
 798    total => {
 799         config => {
 800             global_attrs => {
 801                 title  => 'Query Response Time Total',
 802                 vlabel  => 'query time (microseconds) per ${graph_period}',
 803             },
 804             data_source_attrs => {
 805                 draw  => 'LINE2',
 806                 type => 'DERIVE',
 807             },
 808         },
 809         # data_sources are populated by sub plugin_query_response_time
 810         data_sources => [
 811         ],
 812    }
 813 };
 814
 815 $graph_plugins{user_statistics} = {
 816     connections => {
 817         config => {
 818             global_attrs => {
 819                 title  => 'User Connections',
 820                 vlabel  => 'connections per ${graph_period}',
 821             },
 822             data_source_attrs => {
 823                 draw  => 'LINE2',
 824                 type => 'DERIVE',
 825             },
 826         },
 827         cols => { 'total_connections' => {}, 'concurrent_connections' => {}, 'denied_connections' => {}, 'lost_connections'  => {}},
 828         data_sources => [
 829         ],
 830    },
 831    usertime => {
 832         config => {
 833             global_attrs => {
 834                 title  => 'User Time',
 835                 vlabel  => 'seconds',
 836             },
 837             data_source_attrs => {
 838                 draw  => 'LINE2',
 839                 type => 'DERIVE',
 840             },
 841         },
 842         cols => { 'connected_time' => {}, 'busy_time' => {}, 'cpu_time' => {} },
 843         data_sources => [
 844         ],
 845    },
 846    bytes => {
 847         config => {
 848             global_attrs => {
 849                 title  => 'User Bytes',
 850                 vlabel  => 'bytes',
 851             },
 852             data_source_attrs => {
 853                 draw  => 'LINE2',
 854                 type => 'DERIVE',
 855             },
 856         },
 857         cols => { 'bytes_received' => {}, 'bytes_sent' => {}, 'binlog_bytes_written' => {} },
 858         data_sources => [
 859         ],
 860    },
 861    rows => {
 862         config => {
 863             global_attrs => {
 864                 title  => 'User Rows',
 865                 vlabel  => 'rows',
 866             },
 867             data_source_attrs => {
 868                 draw  => 'LINE2',
 869                 type => 'DERIVE',
 870             },
 871         },
 872         cols => { 'rows_read' => {}, 'rows_sent' => {}, 'rows_deleted' => {}, 'rows_inserted' => {}, 'rows_updated' => {} },
 873         data_sources => [
 874         ],
 875    },
 876    commands => {
 877         config => {
 878             global_attrs => {
 879                 title  => 'Command breakdown by user',
 880                 vlabel  => 'commands',
 881             },
 882             data_source_attrs => {
 883                 draw  => 'LINE2',
 884                 type => 'DERIVE',
 885             },
 886         },
 887         cols => { 'select_commands' => {}, 'update_commands' => {}, 'other_commands' => {}, 'commit_transactions' => {}, 'rollback_transactions' => {} },
 888         data_sources => [
 889         ],
 890    }
 891
 892 };
 893 #---------------------------------------------------------------------
 894 #  M A I N
 895 #---------------------------------------------------------------------
 896
 897
 898 #
 899 # Global hash holding the data collected from mysql.
 900 #
 901 our $data; # Was 'my'. Changed to 'our' to facilitate testing.
 902
 903
 904 sub main {
 905     my $graph = basename($0);
 906     $graph =~ s/^mysql[0-9]*_//g; # allow multiple instances
 907     my $command = $ARGV[0] || 'show';
 908
 909     my %command_map = (
 910         'autoconf' => \&autoconf,
 911         'config'   => \&config,
 912         'show'     => \&show,
 913         'suggest'  => \&suggest,
 914     );
 915
 916     die "Unknown command: $command"
 917         unless exists $command_map{$command};
 918
 919     die "Missing dependency Cache::Cache"
 920         unless $has_cache || $command eq 'autoconf';
 921
 922     return $command_map{$command}->($graph);
 923 }
 924
 925
 926 #---------------------------------------------------------------------
 927 #  C O M M A N D   H A N D L E R S
 928 #---------------------------------------------------------------------
 929
 930 # Each command handler should return an appropriate exit code
 931
 932
 933 # http://munin-monitoring.org/wiki/ConcisePlugins#autoconf
 934 sub autoconf {
 935     unless ($has_cache) {
 936         print "no (Missing dependency Cache::Cache)\n";
 937         return 0;
 938     }
 939
 940     eval {
 941         db_connect();
 942     };
 943     if ($@) {
 944         my $err = $@;
 945         $err =~ s{\s at \s \S+ \s line .*}{}xms;
 946         print "no ($err)\n";
 947         return 0;
 948     }
 949     print "yes\n";
 950     return 0;
 951 }
 952
 953
 954 # http://munin-monitoring.org/wiki/ConcisePlugins#suggest
 955 sub suggest {
 956
 957     # What is the best way to decide which graphs is applicable to a
 958     # given system?
 959     #
 960     # Does the database use InnoDB? A zero count from:
 961     #
 962     #   SELECT COUNT(*)
 963     #     FROM tables
 964     #    WHERE table_type = 'base table'
 965     #      AND engine     = 'innodb'
 966     #
 967     # Does the database use binary logs? 'OFF' as the result from:
 968     #
 969     #   SHOW GLOBAL variables LIKE 'log_bin'
 970     #
 971     # Is the database setup as a slave? Empty result from:
 972     #
 973     #   SHOW SLAVE STATUS
 974
 975     foreach my $graph (sort keys(%graphs)) {
 976         next if $graph =~ /innodb_/ && $data->{_innodb_disabled};
 977         next if $graph =~ /wsrep_/ && $data->{_galera_disabled};
 978         print "$graph\n";
 979     }
 980
 981     return 0;
 982 }
 983
 984
 985 sub config {
 986     my $graph_name = shift;
 987
 988     # In MySQL 5.1 (and probably erlier versions as well) status
 989     # variables are unique when looking at the last 19 characters.
 990     #
 991     #   SELECT RIGHT(variable_name, 19), COUNT(*)
 992     #     FROM information_schema.global_status
 993     #    GROUP BY RIGHT(variable_name, 19)
 994     #   HAVING COUNT(*) > 1;
 995     #
 996     #   Empty set (0.06 sec)
 997     #
 998     # There is one duplicate when looking at server variables
 999     #
1000     #   SELECT RIGHT(variable_name, 19), COUNT(*)
1001     #     FROM information_schema.global_variables
1002     #    GROUP BY RIGHT(variable_name, 19)
1003     #   HAVING COUNT(*) > 1;
1004     #
1005     #   +--------------------------+----------+
1006     #   | RIGHT(variable_name, 19) | COUNT(*) |
1007     #   +--------------------------+----------+
1008     #   | OW_PRIORITY_UPDATES      |        2 |
1009     #   +--------------------------+----------+
1010     #   1 row in set (0.05 sec)
1011     #
1012     #   show global variables like '%OW_PRIORITY_UPDATES';
1013     #
1014     #   +--------------------------+-------+
1015     #   | Variable_name            | Value |
1016     #   +--------------------------+-------+
1017     #   | low_priority_updates     | OFF   |
1018     #   | sql_low_priority_updates | OFF   |
1019     #   +--------------------------+-------+
1020     #   2 rows in set (0.00 sec)
1021     #
1022     # Not a problem since we don't graph these
1023
1024     update_data();
1025
1026     die 'Unknown graph ' . ($graph_name ? $graph_name : '')
1027         unless $graphs{$graph_name};
1028
1029     my $graph = $graphs{$graph_name};
1030
1031     my %conf = (%{$defaults{global_attrs}}, %{$graph->{config}{global_attrs}});
1032     while (my ($k, $v) = each %conf) {
1033         print "graph_$k $v\n";
1034     }
1035     print "graph_category mysql2\n";
1036
1037     for my $ds (@{$graph->{data_sources}}) {
1038         my %ds_spec = (
1039             %{$defaults{data_source_attrs}},
1040             %{$graph->{config}{data_source_attrs}},
1041             %$ds,
1042         );
1043         while (my ($k, $v) = each %ds_spec) {
1044             # 'name' is only used internally in this script, not understood by munin.
1045             printf("%s.%s %s\n", clean_fieldname($ds->{name}), $k, $v) unless ($k eq 'name');
1046         }
1047         print_thresholds(clean_fieldname($ds->{name}));
1048     }
1049
1050     return 0;
1051 }
1052
1053 sub show {
1054     my $graph_name = shift;
1055
1056     update_data();
1057
1058     die 'Unknown graph ' . ($graph_name ? $graph_name : '')
1059         unless $graphs{$graph_name};
1060
1061     my $graph = $graphs{$graph_name};
1062
1063     die "Can't show data for '$graph_name' because InnoDB is disabled."
1064         if $graph_name =~ /innodb_/ && $data->{_innodb_disabled};
1065
1066     for my $ds (@{$graph->{data_sources}}) {
1067         my $value = exists $ds->{value}
1068             ? $ds->{value}($data)
1069                 : $data->{$ds->{name}};
1070
1071         printf "%s.value %s\n", clean_fieldname($ds->{name}), defined($value) ? $value : 'U';
1072     }
1073
1074     return 0;
1075 }
1076
1077
1078
1079 #---------------------------------------------------------------------
1080 #  U T I L I T Y   S U B S
1081 #---------------------------------------------------------------------
1082
1083
1084 sub db_connect {
1085     my $dsn = "$config{dsn};mysql_connect_timeout=5";
1086
1087     return DBI->connect($dsn, $config{user}, $config{password}, {
1088         RaiseError       => 1,
1089         PrintError       => 0,
1090         FetchHashKeyName => 'NAME_lc',
1091     });
1092 }
1093
1094
1095 sub update_data {
1096     $data = $shared_memory_cache->get('data');
1097     my $graphs_stored = $shared_memory_cache->get('graphs');
1098     %graphs = %{thaw($graphs_stored)} if $graphs_stored;
1099     return if $data;
1100
1101     #warn "Need to update cache";
1102
1103     $data = {};
1104
1105     my $dbh = db_connect();
1106
1107     # Set up defaults in case the server is not a slave
1108     $data->{relay_log_space} = 0;
1109     $data->{slave_running}   = 0;
1110     $data->{slave_stopped}   = 0;
1111
1112     # Set up defaults in case binlog is not enabled
1113     $data->{ma_binlog_size} = 0;
1114
1115     update_variables($dbh);
1116     update_plugins($dbh);
1117     update_innodb($dbh);
1118     update_master($dbh);
1119     update_slave($dbh);
1120
1121     $shared_memory_cache->set('data', $data);
1122     $shared_memory_cache->set('graphs', nfreeze(\%graphs));
1123 }
1124
1125
1126 sub update_plugins {
1127     my ($dbh) = @_;
1128
1129     my %plugin_map = (
1130         'query_response_time'         => \&plugin_query_response_time,
1131     );
1132
1133     sub add_graphs {
1134       my ($f, $sec, $dbh, %g) = @_;
1135       if ($f->($dbh) == 0) {
1136         while (my ($k, $v) = each %g) {
1137           $graphs{$sec . '_' . $k} = $v;
1138         }
1139       }
1140     }
1141
1142     my $sth = $dbh->prepare("SHOW PLUGINS");
1143     $sth->execute();
1144     while (my $row = $sth->fetchrow_hashref()) {
1145         next if $row->{'type'} ne 'INFORMATION SCHEMA';
1146         my $sec = lc $row->{'name'};
1147         next if not exists $plugin_map{$sec};
1148         add_graphs($plugin_map{$sec}, $sec, $dbh, %{$graph_plugins{$sec}});
1149     }
1150     $sth->finish();
1151
1152     my %is_map = (
1153         'user_statistics'         => \&is_user_statistics,
1154     );
1155
1156     $sth = $dbh->prepare("SHOW TABLES IN INFORMATION_SCHEMA");
1157     $sth->execute();
1158     while (my $row = $sth->fetchrow_hashref()) {
1159         my $sec = lc $row->{'tables_in_information_schema'};
1160         next if not exists $is_map{$sec};
1161         add_graphs($is_map{$sec}, $sec, $dbh, %{$graph_plugins{$sec}});
1162     }
1163     $sth->finish();
1164 }
1165
1166 sub update_variables {
1167     my ($dbh) = @_;
1168     my @queries = (
1169         'SHOW GLOBAL STATUS',
1170         'SHOW GLOBAL VARIABLES',
1171     );
1172
1173     my %variable_name_map = (
1174         table_cache => 'table_open_cache', # table_open_cache was
1175                                            # previously known as
1176                                            # table_cache in MySQL
1177                                            # 5.1.2 and earlier.
1178     );
1179
1180     for my $query (@queries) {
1181         $data->{$query} = {};
1182
1183         my $sth = $dbh->prepare($query);
1184         $sth->execute();
1185         while (my $row = $sth->fetch) {
1186             my $var = $variable_name_map{$row->[0]} || $row->[0];
1187             $data->{$var} = $row->[1];
1188         }
1189         $sth->finish();
1190     }
1191 }
1192
1193
1194 sub update_innodb {
1195     my ($dbh) = @_;
1196
1197     my $sth = $dbh->prepare('SHOW /*!50000 ENGINE*/ INNODB STATUS');
1198     eval {
1199         $sth->execute();
1200     };
1201     if ($@) {
1202         if ($@ =~ /Unknown (storage|table) engine 'INNODB'|Cannot call SHOW INNODB STATUS because skip-innodb is defined/i) {
1203             $data->{_innodb_disabled} = 1;
1204             return;
1205         }
1206         die $@;
1207     }
1208     my $row = $sth->fetchrow_hashref();
1209     my $status = $row->{'status'};
1210     $sth->finish();
1211
1212     parse_innodb_status($status);
1213 }
1214
1215
1216 sub update_master {
1217     my ($dbh) = @_;
1218
1219     my $sth = $dbh->prepare('SHOW MASTER LOGS');
1220     eval {
1221         $sth->execute();
1222     };
1223     if ($@) {
1224         # SHOW MASTER LOGS failed becuase binlog is not enabled
1225         return if $@ =~ /You are not using binary logging/;
1226         die $@;
1227     }
1228
1229     while (my $row = $sth->fetch) {
1230         $data->{ma_binlog_size} += $row->[1];
1231     }
1232
1233     $sth->finish();
1234 }
1235
1236
1237 sub update_slave {
1238     my ($dbh) = @_;
1239
1240     my $sth = $dbh->prepare('SHOW SLAVE STATUS');
1241     $sth->execute();
1242     my $row = $sth->fetchrow_hashref();
1243     return unless $row;
1244     while (my ($k, $v) = each %$row) {
1245         $data->{$k} = $v;
1246     }
1247     $sth->finish();
1248
1249     # undef when slave is stopped, or when MySQL fails to calculate
1250     # the lag (which happens depresingly often). (mk-heartbeat fixes
1251     # this problem.)
1252     $data->{seconds_behind_master} ||= 0;
1253
1254     # Track these two fields so we can trigger warnings if the slave stops
1255     # running
1256     $data->{slave_sql_running} = ($data->{slave_sql_running} eq 'Yes')
1257             ? 0 : 1;
1258     $data->{slave_io_running} = ($data->{slave_io_running} eq 'Yes')
1259             ? 0 : 1;
1260
1261 }
1262
1263
1264 #---------------------------------------------------------------------
1265 #  Information SCHEMA tables represent data to be processed
1266 #---------------------------------------------------------------------
1267
1268
1269 sub plugin_query_response_time {
1270     my ($dbh) = @_;
1271
1272     return 1 if not defined $data->{query_response_time_stats};
1273     return 1 if $data->{query_response_time_stats} eq 'OFF';
1274
1275     my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME");
1276     $sth->execute();
1277     while (my $row = $sth->fetchrow_hashref()) {
1278         my $time = $row->{'time'};
1279         $data->{'query_response_time_count_' . $time} = $row->{'count'};
1280         push @{$graph_plugins{query_response_time}->{count}->{data_sources}}, {name => 'query_response_time_count_' . $time, label => $time };
1281         next if $row->{'total'} eq 'TOO LONG';
1282         $data->{'query_response_time_total_' . $time} = $row->{'total'} * 1e6;
1283         push @{$graph_plugins{query_response_time}->{total}->{data_sources}}, {name => 'query_response_time_total_' . $time, label => $time };
1284     }
1285     $sth->finish();
1286
1287     return 0;
1288 }
1289
1290 sub is_user_statistics {
1291     my ($dbh) = @_;
1292
1293     return 1 if not defined $data->{userstat};
1294     return 1 if $data->{userstat} eq 'OFF';
1295
1296     my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS");
1297     $sth->execute();
1298     while (my $row = $sth->fetchrow_hashref()) {
1299         my $user = $row->{'user'};
1300         my $var;
1301         while (my ($g, $v) = each %{$graph_plugins{user_statistics}}) {
1302           while (my ($userstat,$conf) = each %{$v->{cols}}) {
1303             $var = 'user_stats_' . $user . '_' . $userstat;
1304             $data->{$var} = int $row->{$userstat};
1305             my $ds = { %$conf };
1306             $ds->{name} = $var;
1307             $ds->{label} = $user . ' ' . $userstat;
1308             push @{$graph_plugins{user_statistics}->{$g}->{data_sources}}, $ds;
1309           }
1310         }
1311     }
1312     $sth->finish();
1313     return 0;
1314 }
1315
1316 #
1317 # In 'SHOW ENGINE INNODB STATUS' 64 bit integers are not formated as
1318 # plain integers. They are either:
1319 #
1320 #   - split in two and needs to be shifted together,
1321 #   - or hexadecimal
1322 #
1323 sub innodb_bigint {
1324     my ($x, $y) = @_;
1325
1326     return defined $y
1327         ? Math::BigInt->new($x)->blsft(32) + $y
1328         : Math::BigInt->new("0x$x");
1329 }
1330
1331 #---------------------------------------------------------------------
1332 #  P A R S E   'SHOW ENGINE INNODB STATUS'   O U T P U T
1333 #---------------------------------------------------------------------
1334
1335
1336 # A nice walk through
1337 # http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/
1338
1339 # The parsing is split in one subrutine per section. Each subroutine
1340 # should parse a block with the following structure
1341 #
1342 # block body ...
1343 # more lines ....
1344 # ----------
1345
1346 sub parse_innodb_status {
1347     local $_ = shift;
1348
1349     # Add a dummy section to the end in case the innodb status output
1350     # has been truncated (Happens for status > 64K characters)
1351     $_ .= "\n----------\nDUMMY\n";
1352
1353     my %section_map = (
1354
1355         'BUFFER POOL AND MEMORY'      => \&parse_buffer_pool_and_memory,
1356         'INDIVIDUAL BUFFER POOL INFO' => \&skip,
1357         'FILE I/O'                    => \&parse_file_io,
1358         'INSERT BUFFER AND ADAPTIVE HASH INDEX'
1359             => \&parse_insert_buffer_and_adaptive_hash_index,
1360         'LATEST DETECTED DEADLOCK'    => \&skip,
1361         'LATEST FOREIGN KEY ERROR'    => \&skip,
1362         'LOG'                         => \&parse_log,
1363         'ROW OPERATIONS'              => \&skip,
1364         'SEMAPHORES'                  => \&parse_semaphores,
1365         'TRANSACTIONS'                => \&parse_transactions,
1366         'BACKGROUND THREAD'           => \&skip,
1367     );
1368
1369     skip_heading();
1370     for (;;) {
1371         m/\G(.*)\n/gc;
1372         my $sec = $1;
1373
1374         last if $sec eq 'END OF INNODB MONITOR OUTPUT';
1375         if ($sec eq 'DUMMY') {
1376             handle_incomplete_innodb_status();
1377             last;
1378         }
1379
1380         die "Unknown section: $1" unless exists $section_map{$sec};
1381         die "Parse error. Expected a section separator" unless m/\G-+\n/gc;
1382
1383         $section_map{$sec}->();
1384     }
1385 }
1386
1387
1388 # This regular expression handles the different formating of 64-bit
1389 # integers in different versions of the innodb engine. Either two
1390 # decimal 32-bit integers seperated by a space, or a single
1391 # hexadecimal 64-bit integer.
1392 my $innodb_bigint_rx = qr{([[a-fA-F\d]+)(?: (\d+))?};
1393
1394
1395 sub match_dashes { return m/\G-+\n(?!-)/gc; }
1396
1397
1398 sub skip_line    { return m/\G.*\n/gc; }
1399
1400
1401 sub skip_heading {
1402     # Heading is 6 lines
1403     for my $foo (1...6) {
1404         skip_line or die('Parse error');
1405     }
1406 }
1407
1408
1409 sub parse_section {
1410     my ($parser) = @_;
1411
1412     #warn substr($_, pos(), 10);
1413     for (;;) {
1414         return if match_dashes();
1415         next if $parser->();
1416         skip_line();
1417     }
1418 }
1419
1420
1421 sub skip { parse_section(sub {}); }
1422
1423
1424 sub parse_semaphores {
1425     parse_section(
1426         sub {
1427             m/\GMutex spin waits (\d+), rounds (\d+), OS waits (\d+)\n/gc && do {
1428                 $data->{ib_spin_waits}  = $1;
1429                 $data->{ib_spin_rounds} = $2;
1430                 $data->{ib_os_waits}    = $3;
1431                 return 1;
1432             };
1433         }
1434     );
1435 }
1436
1437
1438 sub parse_transactions {
1439     parse_section(
1440         sub {
1441             m/\GTrx id counter $innodb_bigint_rx\n/gc && do {
1442                 $data->{ib_tnx} = innodb_bigint($1, $2);
1443                 return 1;
1444             };
1445             m/\GPurge done for trx's n:o < $innodb_bigint_rx undo n:o < $innodb_bigint_rx\n/gc && do {
1446                 if (defined $3) {
1447                     # old format
1448                     $data->{ib_tnx_prg} = innodb_bigint($1, $2);
1449                     # FIX add to data? innodb_bigint($3, $4);
1450                 }
1451                 else {
1452                     # new format
1453                     $data->{ib_tnx_prg} = innodb_bigint($1);
1454                     # FIX add to data? innodb_bigint($2);
1455                 }
1456                 return 1;
1457             };
1458             m/\GHistory list length (\d+)\n/gc && do {
1459                 $data->{ib_tnx_hist} = $1;
1460                 return 1;
1461             };
1462         }
1463     );
1464
1465 }
1466
1467
1468 sub parse_file_io {
1469     parse_section(
1470         sub {
1471             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 {
1472                 $data->{ib_iop_aioread}  = $1;
1473                 $data->{ib_iop_aiowrite} = $2;
1474                 $data->{ib_iop_ibuf_aio} = $3;
1475                 $data->{ib_iop_log}      = $4;
1476                 $data->{ib_iop_sync}     = $5;
1477                 return 1;
1478             };
1479             m/\GPending flushes \(fsync\) log: (\d+); buffer pool: (\d+)\n/gc && do {
1480                 $data->{ib_iop_flush_log}   = $1;
1481                 $data->{ib_iop_flush_bpool} = $2;
1482                 return 1;
1483             };
1484             m/\G(\d+) OS file reads, (\d+) OS file writes, (\d+) OS fsyncs\n/gc && do {
1485                 $data->{ib_io_read}  = $1;
1486                 $data->{ib_io_write} = $2;
1487                 $data->{ib_io_fsync} = $3;
1488                 return 1;
1489             };
1490         }
1491     );
1492 }
1493
1494
1495 sub parse_insert_buffer_and_adaptive_hash_index {
1496     parse_section(
1497         sub {
1498       # MySQL < 5.5
1499       m/\G(\d+) inserts, (\d+) merged recs, (\d+) merges\n/gc && do {
1500         $data->{ib_ibuf_inserts}    = $1;
1501         $data->{ib_ibuf_merged_rec} = $2;
1502         $data->{ib_ibuf_merges}     = $3;
1503         return 1;
1504       };
1505       # MySQL >= 5.5
1506       m/\Gmerged operations:\n insert (\d+), delete mark \d+, delete \d+\ndiscarded operations:\n insert (\d+), delete mark \d+, delete \d+\n/gc && do {
1507         $data->{ib_ibuf_inserts} = $1;
1508         $data->{ib_ibuf_merged_rec} = $1 + $2;
1509         return 1;
1510       };
1511       m/\GIbuf: size (\d+), free list len (\d+), seg size (\d+),(?: (\d+) merges)?\n/gc && do {
1512         $data->{ib_ibuf_size}     = $1;
1513         $data->{ib_ibuf_free_len} = $2;
1514         $data->{ib_ibuf_seg_size} = $3;
1515         $data->{ib_ibuf_merges}   = $4 if defined $4; # MySQL >= 5.5
1516         return 1;
1517       };
1518         }
1519     );
1520 }
1521
1522
1523 sub parse_log {
1524     parse_section(
1525         sub {
1526             m/\GLog sequence number $innodb_bigint_rx\n/gc && do {
1527                 $data->{ib_log_written} = innodb_bigint($1, $2);
1528                 return 1;
1529             };
1530             m/\GLog flushed up to\s+$innodb_bigint_rx\n/gc && do {
1531                 $data->{ib_log_flush} = innodb_bigint($1, $2);
1532                 return 1;
1533             };
1534             m/\G(\d+) log i\/o's done.*\n/gc && do {
1535                 $data->{ib_io_log} = $1;
1536                 return 1;
1537             };
1538         }
1539     );
1540 }
1541
1542
1543 sub parse_buffer_pool_and_memory {
1544     parse_section(
1545         sub {
1546             m/\GBuffer pool size\s+(\d+)\n/gc && do {
1547                 $data->{ib_bpool_size} = $1;
1548                 return 1;
1549             };
1550             m/\GFree buffers\s+(\d+)\n/gc && do {
1551                 $data->{ib_bpool_free} = $1;
1552                 return 1;
1553             };
1554             m/\GDatabase pages\s+(\d+)\n/gc && do {
1555                 $data->{ib_bpool_dbpages} = $1;
1556                 return 1;
1557             };
1558             m/\GModified db pages\s+(\d+)\n/gc && do {
1559                 $data->{ib_bpool_modpages} = $1;
1560                 return 1;
1561             };
1562             m/\GPages read (\d+), created (\d+), written (\d+)\n/gc && do {
1563                 $data->{ib_bpool_read}    = $1;
1564                 $data->{ib_bpool_created} = $2;
1565                 $data->{ib_bpool_written} = $3;
1566                 return 1;
1567             };
1568         }
1569     );
1570 }
1571
1572
1573 sub handle_incomplete_innodb_status {
1574
1575     warn "Output from SHOW ENGINE INNDOB STATUS was truncated. "
1576         . "This happens if the output of SEIS exceeds 64KB. "
1577         . "Several of the InnoDB graphs might be affected by this.";
1578
1579     # FIX Is it possible to find some of the missing values from SHOW
1580     # STATUS?
1581 }
1582
1583
1584 exit main() unless caller;
1585
1586
1587 1;