【MySQL】MySQL慢日志分析WEB页面Box Anemometer+pt-query-digest

参考

http://blog.itpub.net/26355921/viewspace-1162415/

http://www.cnblogs.com/xuanzhi201111/p/4128894.html

Percona Toolkit下载链接

http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/

Anemometer下载链接

https://github.com/box/Anemometer

WEB title修改

Anemometer-master/views/header.php

WEB 导航栏修改

Anemometer-master/views/navbar.php

如果插入的库只有localhost可以参考下面的修改，只保留Graph Search和Table Search

<div class="navbar navbar-fixed-top">
  <div class="navbar-inner">
    <div class="container">
      <a class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse">
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </a>
      <a class="brand" href="<?php echo site_url(); ?>"><img src="img/anemometer.png"> Box Anemometer</a>
      <div class="nav-collapse">
        <ul class="nav">
           <li class="divider-vertical"></li>
          
            <?php if (isset($datasource)) { ?> 
              <?php if ($source_type != 'performance_schema')  { ?>
                <li><a href="<?php echo site_url().'?action=graph_search&datasource='.$datasource; ?>"><i class="icon-picture icon-white" style="width: 32px"></i> Graph Search</a></li>
                <li class="divider-vertical"></li>
                <li><a href="<?php echo site_url().'?action=report&datasource='.$datasource; ?>"><i class="icon-list-alt icon-white" style="width: 16px"></i> Table Search</a></li>
              <?php } ?>
            <?php } ?>
        </ul>
      </div><!--/.nav-collapse -->  
        <?php if (isset($datasource) and $source_type != 'performance_schema') { ?> 
        <form class="form-search form-inline pull-right" id="quicksearch" action="<?php echo site_url()."?action=quicksearch&datasource={$datasource}"; ?>" method="post">
          <input type="text" class="input-medium" name="checksum" placeholder="checksum">
            <a class="btn" href="javascript:document.getElementById('quicksearch').submit()"><i class="icon-search"></i> Find Query</a>
        </form>
        <?php } ?>
        
    </div>
  </div>
</div>

<?php
/*
<?php foreach( $path as $p ) { ?> 
  <li><a href="<?php echo $url ?>"><?php echo $p ?></a></li>
<?php } ?>
*/
?>

 

conf/config.inc.php是Anemometer的配置文件，其中有WEB上面的一些默认值设置

可配置reviewer数量和review_type
$conf['reviewers'] = array( 'dba1','dba2');
$conf['review_types'] = array( 'good', 'bad', 'ticket-created', 'needs-fix', 'fixed', 'needs-analysis', 'review-again');

查询和展示WEB配置
$conf['history_defaults'] = array(
    'output'        => 'table',
    'fact-group'    => 'date',
    'fact-order'    => 'date DESC',
    'fact-limit' => '90',
    'dimension-ts_min_start' => date("Y-m-d H:i:s", strtotime( '-90 day')),
    'dimension-ts_min_end'    => date("Y-m-d H:i:s"),
    'table_fields' => array('date', 'index_ratio','query_time_avg','rows_sent_avg','ts_cnt','Query_time_sum','Lock_time_sum','Rows_sent_sum','Rows_examined_sum','Tmp_table_sum','Filesort_sum','Full_scan_sum')
);

$conf['report_defaults'] = array(
    'fact-group'    => 'checksum',
    'fact-order'    => 'Query_time_sum DESC',
    'fact-limit' => '20',
    'dimension-ts_min_start' => date("Y-m-d H:i:s", strtotime( '-1 day')),
    'dimension-ts_min_end'    => date("Y-m-d H:i:s"),
    'table_fields' => array('checksum','snippet', 'index_ratio','query_time_avg','rows_sent_avg','ts_cnt','Query_time_sum','Lock_time_sum','Rows_sent_sum','Rows_examined_sum','Tmp_table_sum','Filesort_sum','Full_scan_sum'),
    'dimension-pivot-hostname_max' => null
);

$conf['graph_defaults'] = array(
    'fact-group'    => 'minute_ts',
    'fact-order'    => 'minute_ts',
    'fact-limit' => '',
    'dimension-ts_min_start' => date("Y-m-d H:i:s", strtotime( '-7 day')),
    'dimension-ts_min_end'    => date("Y-m-d H:i:s"),
    'table_fields' => array('minute_ts'),
    // hack ... fix is to make query builder select the group and order fields,
    // then table fields only has to contain the plot_field
    'plot_field' => 'Query_time_sum',
);

执行计划配置部分，标红部分是连接业务DB的端口和账号密码
$conf['plugins'] = array(

    'visual_explain' => '/usr/bin/pt-visual-explain',
#    percona toolkit has removed query advisor
#    'query_advisor'    => '/usr/bin/pt-query-advisor',

    'show_create'    => true,
    'show_status'    => true,

    'explain'    =>    function ($sample) {
        $conn = array();

        if (!array_key_exists('hostname_max',$sample) or strlen($sample['hostname_max']) < 5)
        {
            return;
        }

        $pos = strpos($sample['hostname_max'], ':');
        if ($pos === false)
        {
            $conn['port'] = 3306;
            $conn['host'] = $sample['hostname_max'];
        }
        else
        {
            $parts = preg_split("/:/", $sample['hostname_max']);
            $conn['host'] = $parts[0];
            $conn['port'] = $parts[1];
        }

        $conn['db'] = 'mysql';
        if ($sample['db_max'] != '')
        {
            $conn['db'] = $sample['db_max'];
        }

        $conn['user'] = 'root';
        $conn['password'] = '';

        return $conn;
    },
);

修改后的页面如下：

 

执行计划：