Percona Toolkit を読む

このエントリは はてなエンジニア Advent Calendar 2019 の 18 日目のエントリです。

qiita.com

前日は id:ikesyo さんによる 2019年のSwiftモック事情 でした。

ikesyo.hatenablog.com

SQL を分析したい

今日の話。DB 負荷を継続的に計測していきたいのです。

そんなときに Percona Toolkit って良いライブラリがあるので、これを使っていきましょう。

SQL を分析するときは以下のようなことを考えます。

  1. 本番環境でログを採取
  2. normalize
  3. グルーピング
  4. EXPLAIN
  5. マズいクエリを見つけたらアラート発報

それぞれ見ていきましょう。

ログの採取

色々眺めたいので一旦 tcpdump を使います。

流れるクエリだけじゃなくて response も全部採取しているので、秒で GB 単位のデータが溜まっていくことに注意してください。

# pcap データをファイルに保存する
$ sudo /usr/sbin/tcpdump -i any port 3306 -G 60 -w /tmp/tcpdump_%Y%m%d_%H%M%S.pcap
# 再生する
$ /usr/sbin/tcpdump -r /tmp/tcpdump_20191218_000000.pcap -n -nn -tttt -x -q

この取得した pcap から、流れている SQL を取り出したいんですが、Percona Toolkit に TcpdumpParserMySQLProtocolParser っていうイイやつがあります。

名前を見るとイメージ沸くかな。これらを繋ぐといい感じに流れたクエリを取り出すことができます。

use strict;
use warnings;
use utf8;
use English qw(OS_ERROR);

use lib '/path/to/percona-toolkit/lib';

use Data::Dumper;
$Data::Dumper::Sortkeys  = 1;

use TcpdumpParser;
use ProtocolParser;
use MySQLProtocolParser;

my $parser   = new TcpdumpParser();
my $protocol = new MySQLProtocolParser();
my $queries = [];

my $file = 'tcpdump_20191218_000000.txt';

sub _read {
    my ( $fh ) = @_;
    return <$fh>;
}

eval {
    open my $fh, "<", $file or die "Cannot open $file: $OS_ERROR";
    my $parser_args = {
        next_event => sub { return _read($fh); },
        tell       => sub { return tell($fh);  },
        fh         => $fh,
    };
    while ( my $p = $parser->parse_event(%$parser_args) ) {
        my $e = $protocol->parse_event(%$parser_args, event => $p);
        push @$queries, $e if $e;
    }
    close $fh;
};

print Dumper $queries;

こんな感じ。

$VAR1 = [
   {
     No_good_index_used => 'No',
     No_index_used => 'No',
     Query_time => '0.000126',
     Rows_affected => 0,
     Thread_id => 4294967299,
     Warning_count => 0,
     arg => 'SELECT  `users`.* FROM `users` WHERE `users`.`id` = 1',
     bytes => 261,
     cmd => 'Query',
     db => undef,
     host => '127.0.0.1',
     ip => '127.0.0.1',
     port => '58430',
     pos_in_log => 130658,
     ts => '191218 23:46:08.915939',
     user => undef
   },
  ...

ところでレスポンスに NO_GOOD_INDEX_USED とか NO_INDEX_USED とかってフラグあったんですね。10 年以上ずっと MySQL 使ってきたけど知らなかった……。

normalize

SQL を取り出すと、

SELECT  `users`.* FROM `users` WHERE `users`.`id` = 1

のような SQL 文字列が取得できたけど、id = 1 の部分は同一視して集計したいですよね。

これまた Percona Toolkit に QueryRewriter っていうイイやつがあります。

この fingerprint メソッドが、よくできた normalize 処理なので、いただきます。

use QueryParser;
use QueryRewriter;

my $queries = ...;

my $qp = new QueryParser();
my $qr = new QueryRewriter(QueryParser => $qp);
print Dumper [ map { $qr->fingerprint($_->{arg}) } @$queries ];

これを通すと

$VAR1 = [
  'select `users`.* from `users` where `users`.`id` = ?',
  'select `users`.* from `users` where `users`.`id` in(?+)',
  ...

のように normalize されたクエリが取得できます。

自分でこの正規表現書けるかというとまったく自信無いので、枯れてるライブラリは最高ですね。

グルーピング

normalize して、クエリごとに集計可能になったので、グルーピングして集計処理をします。

これまた Percona Toolkit に EventAggregator っていうイイやつがあります。

あ、event ってのは、最初に Parser から取り出していた、1 クエリを表現する HashRef のことですね。

使い方はこんな感じ?

use EventAggregator;

my $ea = new EventAggregator(
   groupby    => 'fingerprint',
   worst      => 'Query_time',
   attributes => {
      Query_time => [qw(Query_time)],
   },
);

for my $event (@$queries) {
    # fingerprint を埋めて
    $event->{fingerprint} = $qr->fingerprint($event->{arg});
    # 集計処理に渡す
    $ea->aggregate($event);
}

# 集計結果を取得
print Dumper $ea->results;

ログ貼ると長いので省略するけど

{
  classes => {
    fingerprint => { 集計結果 },
    fingerprint => { 集計結果 },
    ...
  },
  globals => { ... },
  samples => {
    fingerprint => { event },
    fingerprint => { event },
    ...
  },
}

のような hash が返ってきます。

classes は

{
  'select `users`.* from `users` where `users`.`id` = ?' => {
    Query_time => {
      all => {
        100 => 2,
        102 => 2,
        98 => 1
      },
      cnt => 5,
      max => '0.000143',
      min => '0.000116',
      sum => '0.000657'
    }
  },
  'select `users`.* from `users` where `users`.`id` in(?+)' => {
    Query_time => {
      all => {
        111 => 1,
        94 => 1,
        96 => 1
      },
      cnt => 3,
      max => '0.000221',
      min => '0.000096',
      sum => '0.000424'
    }
  },
  ...

のように、Query_time を集計したもの。cnt, sum だけじゃなくて分布も眺めることができます。

samples は worst で渡した集計軸で、一番遅かったクエリの event です。

集計軸を自分で設定できるので、Query_time 以外でも色んな方向から眺めて分析できそうですね。

EXPLAIN

normalize した結果はおそらく 数百〜2000 ぐらいに収まると思う。これを片っ端から EXPLAIN して、悪い SQL じゃないかを確かめたい。

これまた Percona Toolkit に QueryReportFormatterJSONReportFormatter っていうイイやつがあります。

JSONReportFormatterQueryReportFormatter の子クラスで、出力が JSON になるよう override したものですね。

先ほど作った EventAggregator のオブジェクトと、EXPLAIN を投げる先の DB Handler を渡すと、片っ端から投げまくってくれます。

my $config = {
    dsn => 'dbi:mysql:dbname=DATABASE_NAME;host=127.0.0.1',
    user => 'root',
    pass => 'pass',
};
my $dbh = DBI->connect(map { $config->{$_} } qw(dsn user pass));

my $qrf = QueryReportFormatter->new(
    dbh => $dbh,
    ...
);
$qrf->print_reports(
    ea => $ea,
    ...
);

コードを見ると分かるんですが、要は文字列結合で EXPLAIN $query としているだけです。サブクエリがある場合は安易に EXPLAIN すると死ぬ可能性があるのでスキップされます。なるほど賢い!

マズいクエリを見つけたらアラート発報

JSON が取れるので、もちろん Mackerel に投げて、アラートやグラフを作っていきたい、ですよねぇ。

ここまでで、クエリごとの実行回数や実行時間、一番遅いクエリ等をそれぞれ取得し、更に全クエリについて EXPLAIN 結果も取得することができました。実はこれが pt-query-digest でやってることです。

なんで自前でやってきたかと言うと、一番欲しかった全自動 EXPLAIN 結果が --output json だとなーんか動かなくてですね、この記事はデバッグしている途中ってヤツです。

ドキュメント には

json output was introduced in 2.2.1 and is still in development, so the data structure may change in future versions.

とあります。なので JSON 出力に含める実装がまだ無い気がしています。(たぶん)

著名な便利ツールも中身は普通の Perl なので、こうして読み解いて&コントリビュートしていきたいね、というお話でした。

明日は id:Pasta-K さんです。

書類選考時に見ているポイント

2019-04-01 に「チーフエンジニア」という肩書きを手に入れてしまった。

はてなのエンジニア組織にはチーフエンジニアという役割のエンジニアがいて、評価や採用、その他大小諸々の施策を通じて、技術部門全体の生産性と幸福度を向上させるのがその仕事です。

はてなのエンジニアのバリューズ - Hatena Developer Blog

前職でも新卒採用、中途採用のお手伝いはしていたのだけれど、今は主業務の一つとして担当しているので、僕がどこを見ているのか、というのを書きとめておこう。

履歴書

チラ見しています。

「通勤片道1時間ぐらいかかりそうだけど大丈夫かなぁ?」とか「趣味がルービックキューブじゃん! はてなの speedcubing 部と戦わせたろ」とかを見ています。

職務経歴書

まぁまぁ見ています。

プロジェクトで使った技術、特にアーキテクチャについてを一番見ていると思います。次にプロジェクトの人数や、その中でどんな役割を担ったのか。

具体的に何故この技術を選択したのかとか、プロジェクトで困って解決したこと、解決できなかったことが書いてあるとすごくポイント高くなります。

技術スタックの近さも見ますが、そもそも社内で polyglot な選択をしているので (必要に応じて言語を使い分けてきたら自然とこうなった) やってきた言語そのものよりも、時代の変化とともに生きてきたっぽさを見ています。

GitHub アカウント

はてなに応募される方は持ってる確率高いですね。めちゃくちゃ見ています。

リポジトリ

普段リポジトリを見るときと同じ感じだと思う。

どんなプロダクトを作っているのかを知っておくと会話が弾むので、直近でコミットしてそうな数リポジトリ見てます。

  • star 数とか commit 数とか
    • 力の入れ具合をチラ見
  • README を見る
    • そもそも何をやるものなのかが書いてあるか
    • デモがあるとメチャクチャ体感良い
  • clone してきて tig 起動する
    • ブランチや PR を使ってるかとかを見たい
    • コミットをざっと 30-50 個ぐらい眺めて、コミットの粒度とかコミットメッセージとか
  • CI 用の設定ファイル見る
    • テストあるか、lint あるか、使ってる言語バージョンが最新か
    • 今だと GitHub Actions に移行しているとメチャクチャポイント高いですね
  • 使用ライブラリ一覧眺める
    • 面白いものあるかとか、一般的かとか、プロダクトの規模感掴むとか
  • ディレクトリ構成見る
    • フレームワークのデフォルトに沿っているか
    • 逆に離れていると、敢えてそうしているはずなので面接での会話のネタになる
  • DB 見る
    • 論理的にテーブルが分かれているかとか、正規化できているかとか
  • routes 見る
    • 一般的な API 設計方針を認識してるかとか
  • 静的解析かける
    • 神クラス作ってないか、定番のダメコードを避けられているか

サーバサイドっぽい話題が多いね。僕のバックグラウンドによるものです。

競技プログラミング

  • 提出してくる人すごく増えてきたね
  • 例えば AtCoder 緑あったら「おー、しっかりやってる!会話のネタになりそう」って思って見ています

SNS アカウント

  • お伝えいただけた場合、それなりに見ています
  • tweet 数、following 数、follower 数をチラ見
    • この辺は「インターネット慣れしてるか」という感覚
    • 1000 tweet ぐらいしていて欲しい
  • 発言内容が表垢っぽくないか
    • ペルソナ 1 つに集約されている人だと会話しやすい
      • 使い分けることを否定する意図ではないです
    • インターネットミームが頻繁に登場すると身内っぽい
      • 身内で思い出したけど、「Followers you know」見て「へー」って思ったりもする
  • コード (技術) の話が出てくるか
    • 頻繁に出てくると、生活の中にコーディングが入り込んでいるのでメチャクチャ良いですね
  • 最近興味を持っているものを知るのに使ってる感じ。アカウントがあれば会話が盛り上がりやすいし、無ければ他からひねり出します

その他アウトプット

はてなアカウント

  • エンジニア採用ではあまり見ていません
  • コンテンツプラットフォーム (はてなアカウントで使える toC 向けサービス群) 以外も 事業の柱としている ため
  • はてなが好き」は 社のバリューズの一つ ですが、「インターネットが大好き」も同じくバリューズの一つなので、別にどこでも良いと思っています
  • もちろんガッツリ使っていてくれると嬉しい
  • はてなじゃないとダメな理由、があるとカルチャーマッチしやすそうだなって思います

どれぐらいの時間を掛けているのか

一人につき 30 分〜1 時間半ぐらいですねー。だいたい 30 分だと準備不足で会話しんどい。

感想

いやー、書いてみて感じたけど、自分じゃまったく出来てないことを要求してますね。でも転職考えているならポートフォリオ整備するのを頑張りそうとも思う。

数ヶ月おきに一念発起して "Write Code Every Day" とか毎週ブログを書くとかをしばらく続けると、こういう筋力が戻ってくると思います。やっていこー

どういう時に自分の状態が悪いかと良くないときの対処方法

blog.hokkai7go.jp

dekolife.hatenablog.com

なるほどね。ヨサソウ

状態の悪さがわかる指標

  • 起きてるにも関わらず出社する気が起きなくて午後までボーッとしてる
  • なんかおなか空いてる気がする
  • 分かってるのに分かりたくない駄々っ子状態になる
  • 他人への要求水準が高くなる

改善案

  • 楽しいタスクをやる
    • テスト前に掃除する的なヤツ
  • 寝る
  • マンガ読む
  • ゲームする
  • 食べる

しんどいときに何やっても良くはならないので、今この瞬間の負荷を減らさないといけないと思う。なので本来は「タスクを減らすよう関係者と握る」「後で良いものを本当に後で良いとラベリングして視界/脳内から追い出す」が良い。(とは思っているものの、実践できないので、迷惑を掛けることでアラートを上げてマネージャに何とかして貰おう)

寝るのは強制的にタスクを追い出せるのでオススメです。マンガ・ゲームは、それに集中できるぐらい楽しいので追い出せるんだと思う。

楽しいタスクをやる、は「優先度の順位がおかしくなっている」「おかしくなる程度に今危ない」と分かって貰える (変な成果物がチームの手元に残る) のでアラート効果が高いんじゃないだろうか。しかもみんなの日々のツラみは何か解消されている。最高じゃん(ぉ

これらの改善案を、会話する前にやると迷惑がかかるけど、何もせずにズルズルしてると 1 週間ぐらい戻ってこれないので、一日二日で迷惑が収まるなら遊び呆ける日を作っても良いのでは!?(自己中心的な発想)

食べるのはなんか体を痛めつけてる感じがして一瞬だけ満足感ある。そして太る。


書いた後読み返してみての感想追記。

去年買ってよかったアイテム ベスト3

BIBILAB はだけない着る毛布

僕が買ったんだけど、2 週間後には妻も買ってました。

それ以前はロング半纏 (膝まであるヤツ) を着て満足してたけど、完全に世界が違う。暖房要らず。

iRing

2 万円弱の 7〜8inch タブレットを買って、ガラスが割れたら買い換える運用をしている。なんと去年は iRing を導入した結果割ってない。いつも 8 ヶ月ぐらいで買い換えてたのに。

カードケータイ KY-01L

前の記事がコレじゃん。

onk.hatenablog.jp

電話が受けられるモバイルルータとして大活躍。会社や家に置きっぱなしだったケータイを持ち歩けるようになりました。(今まで持っていたケータイとは何だったのか……)

こちらからは以上です。

カードケータイ KY-01L を買った

いやー、ようやく! ようやく僕の使い方に完全にジャストフィットしたケータイが出てくれたんじゃないか。

ここ数年、ずっとガラケー+ 7〜8インチタブレットを組み合わせる生活をしていて、今は

を使っていた。

ガラホなのは、1 年前に、ちょうど帰省の直前にガラケーを紛失したので docomo ショップに駆け込んだのが理由。重さが倍になってしまって、持ち歩くのが億劫で苦しんでいた。

7〜8インチタブレットが好きなのは感覚の問題で

  • 電子書籍をヘビーユーズしているので小説/技術書/マンガが読みやすいディスプレイサイズが欲しい
  • コードが読めるサイズだとノート PC が無くてもコードレビューができる
  • あまり周りに利用者が居ないのでちょうど良いデバッグ端末になる
  • ゲームをタブレットで遊ぶと迫力があって楽しい
  • ギリギリ尻ポケットに入る

が魅力なのです。

というわけで、ケータイに求めるのは Wi-Fi モデルのタブレットでできないことで、つまり通話とテザリングと (タブレットが無い緊急時に) Web が見られることの 3 点。まさにその用途だけに特化したものが出てくれるとは思ってもみなかった。最高では!?

一つだけ諦めたのはキャリアメールで、これはタブレットから IMAP で行うよう切り替えた。最近はサーバ落ちたときにもメール通知という手段を使わなくなってきたし、だいたい家と会社の往復しかしてないし、数十分放置して問題になるような SPoF もほとんど無いし、もう諦めても良いだろう。。

マルチテナントで ActiveSupport::CurrentAttributes を使おうと考えた

マルチテナントのアプリケーションを作っていて、サブドメインでアクセスしたらそのテナントのデータを表示したい。

apartment gem を使うのが一般的だと思うが、 builderscon tokyo 2018つらくないマルチテナンシーを求めて: 全て見せます! SmartHR データベース移行プロジェクトの裏側 という発表を聞いたので、せっかくなら organization_id を全テーブルに持たせる感じのデータ構造にしようと考えた。

以下のような作戦です。

(1) サブドメインを rack middleware で見て、 ActiveSupport::CurrentAttributes に保存する

class OrganizationInjector
  def initialize(app)
    @app = app
  end

  def call(env)
    req = ActionDispatch::Request.new(env)
    Current.organization = Organization.find_by!(subdomain: req.subdomain)
    @app.call(env)
  end
end

(2) モデルでは default_scope で制御する

class Article < AR::Base
  default_scope { Current.organization && where(organization: Current.organization) }
end

というわけで

となりました。

検索して全部見る

使っていくぞ!!! と叫んでコードを書き出して、ふと

Current.organization 作るんだったら current_user じゃなくて Current.user でも良いなぁ」

と思ったので、GitHub でひたすら ActiveSupport::CurrentAttributes を使っているコードを検索して、検索結果を 100 ページまで全部見た。

このときに、ちょっとでも参考になる可能性があるな、と思ったらとりあえず ghq get して 中を読む ようにしているんだけど、その過程で 3 つの良い出会いがあった。

(今回は検索結果 100 ページで中を見たのは 17 レポジトリなので、異常に S/N 比が悪い。検索力が低い……。

operator_recordable に出会った

https://github.com/yujideveloper/operator_recordable

と以前から妄想していた、record_with_operator の AS::CurrentAttributes 版があるのを知った。

動く実装がなくなって久しいので完全に最高。

rectify に出会った

https://github.com/andypike/rectify

導入しやすい trailblazer みたいなヤツです。

個人的には Rectify::Command の記法と、テストの stub 方法が充実しているのが気に入った。 まだ日本語の記事無いっぽいので狙い目。コードリーディング会で読むのにちょうど良いサイズだと思う。

動画も見た。 http://andypike.com/blog/conferences/rubyc-2016/

このレールは良いレールなので選んでも良いなぁ。

rails_multitenant に出会った

https://github.com/salsify/rails-multitenant

グローバルな変数に入れて default_scope でやるって発想は完全に同じで、 実装方法が AS::CurrentAttributes ではなく自前なのが違う。

グローバルなコンテキストに GlobalContextRegistry って名前を付けてるのが最高。触ると危ないヤツは危ないことが分かる名前であるべきだ。(なので AS::CurrentAttributes のクラス名は Current ではない方がヨサソウ)

中身は Thread.current ですね。

https://github.com/salsify/rails-multitenant/blob/v0.10.0/lib/rails_multitenant/global_context_registry.rb

自分で Thread.current 扱ってるよりもみんなの目を通ったコードの方が安全だろう、というのが AS::CurrentAttributes を使おうと思ったキッカケなので、gemify されてるならコレにしようかな。サクッと読める (すぐ自前に乗り換えられる) サイズなのも良い。

salsify は salsify_rubocop がとても似た音楽性を持っていて好きな会社なので、gem を使えて嬉しい。

全部見た結果

という印象です。他の使い道がある場合は教えて欲しい。