Всем привет.
Более пяти лет я работаю системным администратором в хостинговой компании, обслуживаю более сотни серверов с freebsd и centos. За это время накопилось много самописных скриптов, облегчающих мне жизнь. Этими скриптами хочу поделиться с сообществом, да и выслушать здоровую критику никогда не помешает.
Предыстория.
Много лет назад один мой товарищ, имея в кошельке излишек денежных средств, прикупил серверное железо в виде одноюнитового интеловского сервера, разместил его в самом дешевом датацентре (как это модно сейчас называть — на колокейшене), и начал там размещать сначала свои сайтики, потом начал раздавать хостинг друзьям, друзья стали размещать свои странички, подтянули потом сайты своих друзей и работодателей. Так как товарищ слабо разбирался в установке и настройке LAMP, не мудрствуя лукаво, он поставил Cpanel WHM, меня же привлек в качестве бесплатной рабочей силы админить все это хозяйство, что называется, «за еду», благо хостинг мне тоже был нужен.
За несколько лет на сервере прижилось множество сайтов, росла нагрузка, с которой я с переменным успехом боролся. Периодически стали возникать проблемы с mysql. Одни юзеры плодили медленные запросы, блокировавшие последующие, другие плодили многоэтажные запросы с кучей JOIN, которые в силу до сих пор не починенного бага зависали в состоянии statistics, выжирая при этом процессорные ресурсы. В конечном итоге mysql обжирался процессов и переставал отвечать. Возникла необходимость некоего скрипта мониторинга, который смотрел бы список процессов mysql, и бил тревогу в случае возникновения нештатной ситуации.
Сначала я написал скрипт на bash. Потом, когда по роду основной работы пришлось познакомиться с перлом — переписал на Perl.
Как показывает практика, при штатной работе mysql сервера одновременно отрабатывает менее пяти «медленных» запросов, даже для высоконагруженных серверов, а если таковые имеются, то это повод изучить эти запросы. Анализировать лог медленных запросов, конечно, тоже нужно периодически, но это тема для следующей статьи, про скрипт, который анализирует и даже строит автоматически несложные индексы.
Логика работы скрипта мониторинга простая. Будем считать, что mysql сервер под угрозой, если на момент проверки одновременно выполняется более десяти (например) «медленных» запросов — длительностью более, чем одна секунда. Назовем это состояние «критическим». Если состояние критическое, то надо бить тревогу.
Дальнейшая практика показала, что было бы неплохо бить тревогу, если в течение некоторого времени mysql находится в предкритическом состоянии. То есть 10 процессов одновременно еще нет, но с каждой минутой количество долго отрабатывающих запросов растет. За предкритическое состояние возьмем цифру, скажем, 5.
Запускаем скрипт раз в минуту. Смотрим список процессов, считаем все, что не в статусе Sleep и выполняется дольше одной секунды. Если число больше 10, то отправляем письмо админу вместе со списком процессов. Сохраняем полученное число в файл. Считываем значения из этого файла 5 последних значений — за последние 5 минут, и если в этот промежуток времени было 5 предкритических состояний, то отправляем письмо админу.
В дальнейшем в скрипт был вставлен блок, прибивавший подвисшие в состоянии statistics многоэтажные запросы.
Собственно, скрипт.
Прошу прощения за комментарии на нижегородском английском.
В дальнейшем скрипт был внедрен на боевые mysql серверы в хостинговой компании, где я работаю, и помогал предотвратить отказ Mysql сервера в обслуживании много раз, да и просто сообщал о бесполезной трате ресурсов каким-нибудь пользователем.
скрипт срабатывает:
— когда на забытые богом форумы нападают спам-боты. Под нагрузкой, производительность падает, таблицы форума начинают лочиться, запросы копятся в очереди в статусе «Locked». От скрипта приходит весьма характерный и наглядный список процессов;
— когда на сайты пользователей совершается атака типа benchmark в слепых SQL инъекциях;
— когда mysql тупо повисает, а с ним под нагрузкой такое случается эпизодически (один процесс работает до бесконечности, все остальные просто висят без всякого статуса, и копятся, пока лимит подключений не выберут) — скрипт срабатывает быстрее, чем система мониторинга, опрашивающая mysql порт;
— когда у юзеров внушительные по объему данных таблицы и настолько неоптимизированные запросы, что один запрос выполняется по несколько секунд, а то и минут. Остальные запросы к таблице копятся и ждут очереди в статусе Locked. В письме сразу виден неоптимизированный запрос, можно быстренько посмотреть explain запроса и построить при необходимости индекс. Если используется innodb, то на интенсивных медленных запросах скрипт мониторинга тоже срабатывает, ибо висит их пачка в статусе «Sending data» или «Copying to tmp table». Такие запросы в большом количестве весьма опасны, так как сильно понижают производительность сервера в целом;
— когда виснут многоэтажные JOIN запросы. Скрипт прибивает их автоматически, но иногда они не убиваются — повод перезапустить mysql;
— несколько раз скрипт отлавливал зависания mysql на конкретных запросах, дальнейшее изучение которых приводило в итоге к обновлению, после обнаружения бага с подобными запросами на багтрекере.
Изредка приходят, конечно, ложные срабатывания, если кто-то чинит, оптимизирует, или дампит большую таблицу.
Буду рад, если этот скрипт кому-нибудь пригодится.
Более пяти лет я работаю системным администратором в хостинговой компании, обслуживаю более сотни серверов с 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 $pidfile\n"; print PIDFILE "$$\n"; close PIDFILE; open (PROCFILE,">$procfile") || die "cant create $procfile\n"; 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::errstr\n"); } $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 | $info\n\n"; $sth2 = $dbh->prepare("kill $mid"); $sth2->execute; $sth2->finish; open (MAIL,"|/usr/sbin/sendmail -F$hostname $email"); print MAIL "To:$email\nSubject:".$subj."Hanged query in the statistics state: $hostname, user $user \n\n"; print MAIL $statinfo; close (MAIL); }; ### if ($time>$slowtime) { $slowcounter++; } $info =~ s/[\r\n\t]+/ /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 $datfile\n"; 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:$email\nSubject:".$subj."Critical state of $hostname\n\n"; 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 на конкретных запросах, дальнейшее изучение которых приводило в итоге к обновлению, после обнаружения бага с подобными запросами на багтрекере.
Изредка приходят, конечно, ложные срабатывания, если кто-то чинит, оптимизирует, или дампит большую таблицу.
Буду рад, если этот скрипт кому-нибудь пригодится.
