pt-query-digest的一个问题

昨天有同学问,用pt-query-digest分析后,发现排在最前面都是ADMIN开头的语句,真正的语句却没有真实反映在报表中

$ sudo pt-query-digest --type genlog /tmp/general.log  --order-by Query_time:cnt --limit 3
 
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== =============
#    1 0x440061F2EB7A7639  0.0000  0.0% 53390 0.0000  0.00 ADMIN CLOSE
#    2 0x99AA0165670CE848  0.0000  0.0% 53389 0.0000  0.00 ADMIN PREPARE
#    3 0x1228F184663C8627  0.0000  0.0% 53386 0.0000  0.00 ADMIN EXECUTE
# MISC 0xMISC              0.0000  0.0%  5042 0.0000   0.0 <42 ITEMS>

首先要知道这些语句是因为JAVA使用preparestatment引起,打开general.log发现类似语句:

11637701 Prepare select * from t  
 11637701 Execute select * from t
 11637701 Close stmt

pt-query-digest没有很好处理prepare及execute后面语句,导致真正的sql没有展示出来。
想到把Execute改成Query,把其余的ADMIN过滤掉,那就是一份正确的报告了。

$sed -i 's/Execute/Query/g' /tmp/general.log 
$ sudo pt-query-digest --type genlog /tmp/general.log --filter '$event->{fingerprint} !~ m/^admin/i' --order-by Query_time:cnt --limit 3
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x2DAF846CC781D91A  0.0000  0.0% 14855 0.0000  0.00 SELECT t?
#    2 0x7164FF6E647C5863  0.0000  0.0% 11604 0.0000  0.00 SELECT t2?
#    3 0x6576B004C40147B5  0.0000  0.0%  4582 0.0000  0.00 SELECT t3

可以看到真正的sql出来

细心同学会发现ADMIN CLOSE、ADMIN PREPARE、ADMIN EXECUTE的值几乎都是一样,理论上应该是相同。

mysql分区表和普通表关联查询异常

发现线上有数据库load异常,查看show processlist,同一条sql都是在copy tmp,以前执行几十个ms的,现在基本都是几十s,怀疑执行计划有问题
1
可以看到a表(按月分表的分区表)进行全表扫描,a表记录在上千万,b表记录就几千。看一下a、b表的索引情况
1
当初为了快速解决问题,把join sql拆成两条sql,问题解决。今天想追查一下问题,发现一样的sql,执行计划居然正常了
1

目前没有找到根本原因,先记录一下,莫非和统计信息收集有关或者是bug?

类似bug:Bug #69013

puppet循环创建目录方法

业务需求:在特定目录下,判断某个目录是否存在,如果不存在就创建特定目录,存在啥事不做。不用DSL实现

site.pp文件定义,在/xx/7和/xx/8创建Recycle目录,如果存了Recycle,不覆盖

define mkdir_recycle() {
  file { "/xx/${title}/Recycle":
      ensure => directory,
      owner  => 'admin',
      group  => 'admin',
      mode   => 0775;
    }
}
mkdir_recycle{["7","8"]:}
 
# ls /xx/{7,8}/
/xx/7/:
 
/xx/8/:
 
puppet的日志:
notice: /Stage[main]//Mkdir_recycle[7]/File[/xx/7/Recycle]/ensure: created
notice: /Stage[main]//Mkdir_recycle[8]/File[/xx/8/Recycle]/ensure: created
notice: Finished catalog run in 0.05 seconds
 
# ls /xx/{7,8}/
/xx/7/:
Recycle
 
/xx/8/:
Recycle

perl环视(断言)

每次写断言相关匹配都查一下文档,还老实点记在自己blog吧

$ vi test.pl  
#!/usr/bin/perl
USE warnings;
USE strict;
my $line = "alovebcliked";
#(?=EXP)断言自身出现的位置的后面能匹配表达式EXP
IF ( $line =~ /(\w+)(?=love)/ ){
  print "matched one:$1\n";
}
#(?<=EXP)断言自身出现的位置的前面能匹配表达式EXP
IF ($line =~ /(?<=LIKE)\d/){
  print "matched two\n";
}
elsif($line =~ /(?<=LIKE)(\w+)/){
  print "matched two two:$1\n";
}
 
$line = "abc123";
#(?!EXP)断言此位置的后面不能匹配表达式EXP
IF ($line =~ /([a-z]{2})(?!\d+)/){
   print "matched three:$1\n";
}
IF ($line =~ /([a-z]{3})(?!\d+)/){
   print "matched three three:$1\n";
}
$line = "123abc";
#(?<!EXP)断言此位置的前面不能匹配表达式
IF ($line =~ /(?<![a-z])([a-z]{3})/){
   print "matched four:$1\n";
}
$ perl test.pl
matched one:a
matched two two:d
matched three:ab
matched four:abc

perl私有方法两种实现

perl不像其他语言有private声明私有方法,但有变通方法。

一、利用调用者是否是同一个类,来隐藏私有方法(参考http://stackoverflow.com/questions/451521/how-do-i-make-private-functions-in-a-perl-module)

$cat test_priv.pl
package My;
sub new {
        return bless { }, shift;
}
sub private_func {
        my ($s,%args) = @_;
        die "Error: Private method called"
                unless (caller)[0]->isa( ref($s) );
        warn "OK: Private method called by " . (caller)[0];
}
sub public_func {
        my ($s,%args) = @_;
        $s->private_func();
}
package main;
my $obj = My->new();
$obj->public_func();
$obj->private_func();
 
$perl test_priv.pl
OK: Private method called by My at test_priv.pl line 9.
Error: Private method called at test_priv.pl line 7.

生成对象只能通过公共方法才能访问私有方法。

二、利用文件作用域,perl只能继承类的方法的特性

$cat test_priv2.pl
package Test;
sub new {
        return bless { }, shift;
}
my $private_func = sub {
   my $self = shift;
   print "I am private func!\n";
};
sub public_func {
   my $self = shift;
   print "I am public func!\n";
   $self->$private_func();
}
my $obj = Test->new();
$obj->public_func;
 
$perl test_priv2.pl
I am public func!
I am private func!

类继承以后,也能通过public_func来访问匿名函数(private)