项目中用mysql做最为最终的存储,主要还是觉得mysql稳定,
且一直用mysql做存储,运维经验比较丰富;且主从备份基本还算是比较安全的;
但 mysql 有时看起来效率比较低(这也是n多项目选择nosql做存储的原因,
之所以没选择nosql是因为项目中没人熟悉,没人用过,吃螃蟹是需要勇气和责任的);
有时竟然对这种慢无能无力,比如经常查问题,最后查到是mysql,哦,然后就没了,最多解释一句
“mysql 操作超时了”;不知道为什么慢,不知道慢在哪里,更没有一些监控之类的;
于是想怎样监控 mysql 的慢查询,发现mysql有自己的 slow log;哦,那就ok了,搞个 perl 脚本
定时去分析日志,加上 mysqlslowdump,可以比较完美的监控 mysql 的slow log;
现在附上这个 perl;
- #!/usr/bin/perl
- use List::Util qw(first);
- use Time::Local;
- $minutes = @ARGV ? shift @ARGV : 2;
- open MYSQL_CONF, " or die "cant find my.conf";
- $_ = first { /^\s*log-slow-queries\s*=\s*([\w.\/]+)/ } <MYSQL_CONF>
- or die "cant find log-slow-qureis";
- /=\s*([\w.\/]+)/;
- my $slow_log = $1;
- my $from_time = time() - $minutes * 60;
- my $mtime = (stat("$slow_log"))[9];
- print "$mtime vs from_time=$from_time\n";
- if ($mtime < $from_time)
- {
- print "no slow log in recent $minutes minutes\n";
- exit;
- }
- `tail -5000 $slow_log > /tmp/slow_log.txt`;
- open SELECT_LOG, ">/tmp/.last.txt" or die "cant open last log txt";
- open TMP_LOG, " or die "open tmp log failed";
- select SELECT_LOG;
- my $new_log = 0;
- while (<TMP_LOG>)
- {
- if ($new_log) {
- print;
- next;
- }
- if (/^# Time: (\d\d)(\d\d)(\d\d) (\d\d):(\d\d):(\d\d)$/) {
- $time_s = timelocal($6, $5, $4, $3, $2-1, 2000+$1);
- if ($time_s >= $from_time) {
- $new_log = 1;
- print;
- }
- }
- }
- exit if $new_log == 0;
- select STDOUT;
- `/usr/local/mysql/bin/mysqldumpslow -s t /tmp/.last.txt > /tmp/.sloww`;
- open SELECT_LOG, " or die "cant open slow log";
- undef$/;
- $content = <SELECT_LOG>;
- print $content;
- &send_mail("mysql slow log monitor", $content);
- sub send_mail {
- my ($subject, $message) = @_;
- }
使用:
加入crontab 即可:
*/2 * * * * /usr/local/bin/mysql_slow_monitor.perl 2 > /tmp/mysql_slow_monitor.log 2>&1 &
阅读(2455) | 评论(0) | 转发(0) |