Всем привет.
Более пяти лет я работаю системным администратором в хостинговой компании, обслуживаю более сотни серверов с freebsd и centos. За это время накопилось много самописных скриптов, облегчающих мне жизнь. Этими скриптами хочу поделиться с сообществом, да и выслушать здоровую критику никогда не помешает.
Предыстория.
Много лет назад один мой товарищ, имея в кошельке излишек денежных средств, прикупил серверное железо в виде одноюнитового интеловского сервера, разместил его в самом дешевом датацентре (как это модно сейчас называть — на колокейшене), и начал там размещать сначала свои сайтики, потом начал раздавать друзьям, друзья стали размещать свои странички, подтянули потом сайты своих друзей и работодателей. Так как товарищ слабо разбирался в установке и настройке LAMP, не мудрствуя лукаво, он поставил Cpanel WHM, меня же привлек в качестве бесплатной рабочей силы админить все это хозяйство, что называется, «за еду», благо мне тоже был нужен.
За несколько лет на сервере прижилось множество сайтов, росла нагрузка, с которой я с переменным успехом боролся. Периодически стали возникать проблемы с mysql. Одни юзеры плодили медленные запросы, блокировавшие последующие, другие плодили многоэтажные запросы с кучей JOIN, которые в силу до сих пор не починенного бага зависали в состоянии statistics, выжирая при этом процессорные ресурсы. В конечном итоге mysql обжирался процессов и переставал отвечать. Возникла необходимость некоего скрипта мониторинга, который смотрел бы список процессов mysql, и бил тревогу в случае возникновения нештатной ситуации.
Сначала я написал скрипт на bash. Потом, когда по роду основной работы пришлось познакомиться с перлом — переписал на Perl.
Как показывает практика, при штатной работе mysql сервера одновременно отрабатывает менее пяти «медленных» запросов, даже для высоконагруженных серверов, а если таковые имеются, то это повод изучить эти запросы. Анализировать лог медленных запросов, конечно, тоже нужно периодически, но это тема для следующей статьи, про скрипт, который анализирует и даже строит автоматически несложные индексы.
Логика работы скрипта мониторинга простая. Будем считать, что mysql сервер под угрозой, если на момент проверки одновременно выполняется более десяти (например) «медленных» запросов — длительностью более, чем одна секунда. Назовем это состояние «критическим». Если состояние критическое, то надо бить тревогу.
Дальнейшая практика показала, что было бы неплохо бить тревогу, если в течение некоторого времени mysql находится в предкритическом состоянии. То есть 10 процессов одновременно еще нет, но с каждой минутой количество долго отрабатывающих запросов растет. За предкритическое состояние возьмем цифру, скажем, 5.
Запускаем скрипт раз в минуту. Смотрим список процессов, считаем все, что не в статусе Sleep и выполняется дольше одной секунды. Если число больше 10, то отправляем письмо админу вместе со списком процессов. Сохраняем полученное число в файл. Считываем значения из этого файла 5 последних значений — за последние 5 минут, и если в этот промежуток времени было 5 предкритических состояний, то отправляем письмо админу.
В дальнейшем в скрипт был вставлен блок, прибивавший подвисшие в состоянии statistics многоэтажные запросы.
Собственно, скрипт.
#!/usr/bin/perl
#use strict;
use DBI;
use DBD::mysql;
use POSIX;
($sysname, $hostname, $release, $version, $machine) = POSIX::uname();
my $slowtime=1; # сколько секунд считать за медленный запрос
my $warnlevel=5; # сколько одновременных медленных запросов считать подозрительным
my $warncounter=5; # если $warncounter подряд то высылаем письмо
my $alarmcounter=10; # если одновременных медленных запросов >= $alarmcounter то высылаем письмо сразу
my $socket='/tmp/mysql56.sock'; # коннектимся по сокету
my $email="admin@myemail.net"; # получатель письма
my $wrkdir='/tmp/';
my $procfile=$wrkdir.'alarm.proclist'; # фременный файл для списка процессов
my $datfile=$wrkdir.'alarm.dat'; # файл куда пишем количество одновременных медленных запросов
my $pidfile=$wrkdir.'alarm.pid'; # pid файл. если mysql повиснет то скрипты мониторинга хотябы плодиться не будут
if (-e "$pidfile") {
printf("pid file found. Exit.n");
exit(255);
}
open (PIDFILE,">$pidfile") || die "cant create $pidfilen";
print PIDFILE "$$n";
close PIDFILE;
open (PROCFILE,">$procfile") || die "cant create $procfilen";
my ($proc, $dbh, $sth, $totalcounter, $slowcounter, $sleepcounter, $user, $time, $state, $command, $info, $i);
until ($dbh = DBI->connect("DBI:mysql:mysql_socket=$socket", "user", "password")){
unlink($pidfile);
die("Can't connect: $DBI::errstrn");
}
$sth = $dbh->prepare("SHOW FULL PROCESSLIST");
$sth->execute;
my @proclist=();
$totalcounter=$slowcounter=$sleepcounter=0;
while (my $row = $sth->fetchrow_hashref()) {
$user=$row->{'User'}; $time=$row->{'Time'}; $state=$row->{'State'};
$command=$row->{'Command'}; $info=$row->{'Info'};
$totalcounter++;
next if ($user =~ m/root/);
if ($command =~ m/(Sleep|Delayed|Binlog)/){ $sleepcounter++; next; };
### убиваем зависшие запросы в состоянии statistics
if ($state =~ m/statistics/ && $time > 5){
$statinfo="$user: killed $mid: $dbuser | $db | $time | $state | $command | $infonn";
$sth2 = $dbh->prepare("kill $mid");
$sth2->execute;
$sth2->finish;
open (MAIL,"|/usr/sbin/sendmail -F$hostname $email");
print MAIL "To:$emailnSubject:".$subj."Hanged query in the statistics state: $hostname, user $user nn";
print MAIL $statinfo;
close (MAIL);
};
###
if ($time>$slowtime) { $slowcounter++; }
$info =~ s/[rnt]+/ /g;
push (@proclist,sprintf("%-24s | %4d | %s | %s | %s n", $user, $time, $state, $command, $info));
printf PROCFILE ("%-24s | %4d | %s | %s | %s n", $user, $time, $state, $command, $info);
}
$sth->finish;
close PROCFILE;
#print "--- $slowcounter slow queries from total $totalcounter ($sleepcounter are sleep) ---- n";
my @data=();
### read slowcounter timings from dat file
open (DATFILE,"<$datfile");
while(<DATFILE>){
my($line) = $_;
chomp($line);
push (@data,$line);
}
close(DATFILE);
### if dat file is smaller than warnlevel then fill timings by zeros
if (scalar(@data)<$warnlevel) {
for $i ( 0 .. $warnlevel-scalar(@data) ) {
push (@data,0);
}
}
### shift timings with last slowcounter
push (@data,$slowcounter);
shift(@data);
### dumping slowcounter timings to dat file
open (DATFILE,"+>$datfile") || die "cant create $datfilen";
foreach (@data) {
print DATFILE "$_n";
}
close(DATFILE);
### get number of bad states for last minutes
my $cnt=0;
foreach (@data) {
if($_ >= $warnlevel) { $cnt++; }
}
my $subj=" ";
if ($slowcounter>=$alarmcounter) { # very critical state
$subj=" VERY ";
}
my $warnmessage="Critical state of $hostname! There was a $warncounter checks with at least $warnlevel long queries!n";
if ($slowcounter>=$alarmcounter) { # very critical state
$warnmessage=$warnmessage."--- !!! Last check shows $slowcounter long queries!n";
}
if (($cnt >= $warncounter) || $slowcounter>=$alarmcounter){
open (MAIL,"|/usr/sbin/sendmail -F$hostname $email");
print MAIL "To:$emailnSubject:".$subj."Critical state of $hostnamenn";
print MAIL $warnmessage;
print MAIL "---------------------------------------------------------------------------------------------------n";
print MAIL "--- $slowcounter slow queries from total $totalcounter ($sleepcounter are sleep) n";
print MAIL "---------------------------------------------------------------------------------------------------n";
foreach (@proclist) {
print MAIL "$_";
}
close (MAIL);
}
unlink($pidfile);
Прошу прощения за комментарии на нижегородском английском.
В дальнейшем скрипт был внедрен на боевые mysql серверы в хостинговой компании, где я работаю, и помогал предотвратить отказ Mysql сервера в обслуживании много раз, да и просто сообщал о бесполезной трате ресурсов каким-нибудь пользователем.
скрипт срабатывает:
— когда на забытые богом форумы нападают спам-боты. Под нагрузкой, производительность падает, таблицы форума начинают лочиться, запросы копятся в очереди в статусе «Locked». От скрипта приходит весьма характерный и наглядный список процессов;
— когда на сайты пользователей совершается атака типа benchmark в слепых SQL инъекциях;
— когда mysql тупо повисает, а с ним под нагрузкой такое случается эпизодически (один процесс работает до бесконечности, все остальные просто висят без всякого статуса, и копятся, пока лимит подключений не выберут) — скрипт срабатывает быстрее, чем система мониторинга, опрашивающая mysql порт;
— когда у юзеров внушительные по объему данных таблицы и настолько неоптимизированные запросы, что один запрос выполняется по несколько секунд, а то и минут. Остальные запросы к таблице копятся и ждут очереди в статусе Locked. В письме сразу виден неоптимизированный запрос, можно быстренько посмотреть explain запроса и построить при необходимости индекс. Если используется innodb, то на интенсивных медленных запросах скрипт мониторинга тоже срабатывает, ибо висит их пачка в статусе «Sending data» или «Copying to tmp table». Такие запросы в большом количестве весьма опасны, так как сильно понижают производительность сервера в целом;
— когда виснут многоэтажные JOIN запросы. Скрипт прибивает их автоматически, но иногда они не убиваются — повод перезапустить mysql;
— несколько раз скрипт отлавливал зависания mysql на конкретных запросах, дальнейшее изучение которых приводило в итоге к обновлению, после обнаружения бага с подобными запросами на багтрекере.
Изредка приходят, конечно, ложные срабатывания, если кто-то чинит, оптимизирует, или дампит большую таблицу.
Буду рад, если этот скрипт кому-нибудь пригодится.
Автор: charliez
