5年以上にわたり、ホスティング会社でシステム管理者として働いており、freebsdとcentosを備えた100台以上のサーバーにサービスを提供しています。 この間、多くの自己記述スクリプトが蓄積され、私の生活が楽になりました。 これらのスクリプトをコミュニティと共有したいので、健全な批判に耳を傾けることは決して害になりません。
バックグラウンド。
何年も前、私の友人が財布に余剰金を持ち、単一ユニットのIntelサーバーの形でサーバーハードウェアを購入し、それを最も安いデータセンターに配置し（現在コロケーションで呼び出すのがファッショナブルであるため）、最初に自分のサイトを投稿し始めましたホスティングを友人に配布するために、友人は自分のページを投稿し始め、友人や雇用主のサイトを引き上げました。 私の友人はLAMPのインストールと設定にあまり詳しくないので、苦労せずにCpanel WHMをインストールしましたが、自由な労働力として「食料」と呼ばれるこの経済をすべて管理することに惹かれました。
数年の間、多くのサイトがサーバーに定着し、負荷が増大しました。それにより、私はさまざまな成功を収めて戦いました。 定期的に、mysqlで問題が始まりました。 一部のユーザーは後続の要求をブロックする遅い要求を生成し、他のユーザーは多数のJOINを含む複数層の要求を生成しました。 最終的に、mysqlはプロセスを飲み込み、応答しなくなりました。 mysqlプロセスのリストを確認し、緊急時にアラームを鳴らす監視スクリプトが必要でした。
まず、bashでスクリプトを書きました。 それから、主な仕事の性質から真珠を知る必要があったとき、それをPerlにコピーしました。
実践が示すように、通常の動作中、mysqlサーバーは、負荷の高いサーバーであっても5つ未満の「遅い」クエリを同時に処理します。もしあれば、これらのクエリを調査する機会です。 もちろん、スロークエリのログも定期的に分析する必要がありますが、これは次の記事のトピックであり、単純なインデックスを分析し、さらには自動的に構築するスクリプトについてです。
監視スクリプトのロジックは単純です。 検証時に、10個を超える（たとえば）「遅い」クエリが同時に実行されている場合（期間が1秒を超える場合）、mysqlサーバーが危険にさらされていると想定します。 この状態を「クリティカル」と呼びます。 状態が重大な場合、アラームを鳴らす必要があります。
さらなる実践により、mysqlがしばらく前にクリティカル状態になった場合にアラームを鳴らすと良いことが示されています。 つまり、同時に10個のプロセスはありませんが、1分ごとに長時間実行されるリクエストの数が増えます。 臨界前状態については、5という数値を取ります。
スクリプトを1分間に1回実行します。 プロセスのリストを見て、スリープ状態ではなく、1秒以上かかるすべてのものを検討します。 数が10を超える場合は、プロセスのリストとともに管理者に手紙を送信します。 結果の数値をファイルに保存します。 最後の5つの値について、このファイルから値を読み取ります（最後の5分間）。この期間中に5つの事前クリティカル状態があった場合、管理者にメールを送信します。
その後、統計状態で中断された多階建てのクエリを固定するブロックがスクリプトに挿入されました。
実際、スクリプト。
#!/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);
ニジニ・ノヴゴロド英語のコメントをおforびします。
その後、スクリプトは、私が勤務するホスティング会社のmysql戦闘サーバーに実装され、Mysqlサーバーが何度もサービスを拒否されるのを防ぐのに役立ち、ユーザーによって無駄なリソースが無駄になっていることを報告しました。
スクリプトは動作します：
-スパムボットが神を忘れたフォーラムを攻撃するとき。 負荷がかかると、パフォーマンスが低下し、フォーラムテーブルがクラッシュし始め、リクエストが「ロック済み」ステータスでキューに蓄積されます。 スクリプトから、プロセスの非常に特徴的で直感的なリストが得られます。
-ブラインドSQLインジェクションでユーザーサイトでベンチマーク攻撃が実行された場合。
-mysqlが愚かにハングし、これが負荷の下で時々発生する場合（1つのプロセスが無期限に動作し、他のすべてのプロセスはステータスなしでハングし、接続制限が選択されるまで蓄積します）-スクリプトはmysqlポートをポーリングする監視システムよりも速く動作します;
-データ量の点で印象的なテーブルがあり、クエリが最適化されていないため、1つのクエリが数秒または数分間実行される場合。 テーブルへの残りのクエリは蓄積され、ロック状態のキューを待ちます。 最適化されていないクエリはすぐにレターに表示されます。Explainクエリをすばやく表示し、必要に応じてインデックスを作成できます。 innodbが使用されている場合、監視スクリプトは、バンドルが「Sending data」または「Copying to tmp table」のステータスでハングしているため、集中的な低速クエリでも機能します。 このような大量のリクエストは、サーバー全体のパフォーマンスを大幅に低下させるため、非常に危険です。
-複数階のJOINリクエストがハングしたとき。 スクリプトは自動的にそれらを釘付けしますが、時々それらは殺されません-mysqlを再起動する理由;
-何度かスクリプトが特定のクエリでmysqlのハングをキャッチし、バグトラッカーで同様のクエリを使用してバグを検出した後、さらに調査して更新に至りました。
もちろん、誰かが大きなテーブルを修復、最適化、またはダンプすると、誤検知が発生することがあります。
このスクリプトが誰にでも役立つとうれしいです。