JumpCloudの鍵認証で使用されるauthorized_keys.jcorigを理解する

業務上AWSのEC2のユーザ管理をJumpCloudで統一して管理している.JumpCloud上で管理対象のEC2に鍵認証を許可することができるのだが,公開鍵を設定する方法として「JumpCloudのコンソール上で設定する方法」と「実際にサーバに手動で設定する方法」がある.後者で設定しようとした際に~/.ssh/authorized_keysの他に~/.ssh/authorized_keys.jcorigというファイルがあることに気づいた(以降authorized_keysauthorized_keys.jcorigと表記する).今回はこれがなんのためにあるものなのか調べてみた.

authorized_keys.jcorigとは

公式には詳細については書いていなかった. support.jumpcloud.com

実際にauthorized_keys.jcorigの中身を確認したところ

# Keys added to this file will be automatically
# copied to your authorized_keys file by the JumpCloud agent.

と書いてあり,authorized_keys.jcorigに書くとJumpCloudエージェントがauthorized_keysにコピーしてくれるようだ.

さらにauthorized_keysを確認すると

# Keys added to this file will be automatically
# copied to your authorized_keys file by the JumpCloud agent.

# Constructed by the JumpCloud agent, you can add keys manually in /home/hoge/.ssh/authorized_keys.jcorig

と書いてあり,サーバ上で手動で設定するならauthorized_keys.jcorigに書き込めという旨が書いてある.

検証

公式にそこまで情報はないがauthorized_keys.jcorigに書き込めばauthorized_keysにJumpCloudエージェントが内容を反映してくれるのだろうという予想はできたので,実際に色々と試して検証してみることにする.JumpCloudエージェントがインストールされたEC2を用意し以下を確認していく.

  • サーバ上に手動で設定するパターン
    • authorized_keys.jcorigに公開鍵を追加
    • authorized_keysに公開鍵を追加
  • JumpCloud上で設定するパターン
    • 公開鍵を追加
    • 公開鍵を削除

サーバ上でauthorized_keys.jcorigに手動で公開鍵を追加

まずは正当な方法であろうauthorized_keys.jcorigに手動で公開鍵を追加する.しかし,しばらくしてもauthorized_keys.jcorigの内容がauthorized_keysにコピーされずログインはできなかった.そこで以下3パターンでログインの可否を確認する.

  • EC2を再起動するとauthorized_keys.jcorigの内容がauthorized_keysにコピーされログインできた
  • authorized_keysにも公開鍵を記述するとログインできた(その後EC2を再起動しても特に問題なし)
  • JumpCloudエージェントを再起動する方法ではauthorized_keysに内容がコピーされることはなくログインできなかった

サーバ上でauthorized_keysに手動で追加

次にauthorized_keysに公開鍵を追加してみた.するとログインできるようになったが,EC2を再起動をするとauthorized_keysから公開鍵が消えてしまいログインできなくなった.

ここまでの検証で以下のことがわかる.

  • 基本的にはauthorized_keysに公開鍵が登録されているかどうかで認証の機能が動作する一般的なLinuxの仕組みと同じ動きをする
  • authorized_keys.jcorigに公開鍵を記述してもauthorized_keysに記述されないと鍵認証されないので,即時でログインしたい場合はEC2を再起動するauthorized_keysにも同じ内容を書くことが必須
  • 手動で公開鍵を追加するにはauthorized_keys.jcorigに書かないと永続化されない
  • 公開鍵を削除する際は少なくともauthorized_keys.jcorigの公開鍵を削除しないと,EC2を再起動した際に再びauthorized_keysに書き込まれてしまう
  • 公開鍵を削除する際,即時にログインできないようにするにはauthorized_keys.jcorigの公開鍵を削除してEC2を再起動するかauthorized_keys.jcorigauthorized_keys両ファイルから公開鍵を削除する必要がある
  • authorized_keys.jcorigの内容をauthorized_keysにコピーする方法として,JumpCloudエージェントの再起動は使えない
  • 公開鍵の追加・削除共にauthorized_keys.jcorigが正として振る舞っている

JumpCloudコンソールから公開鍵を追加

JumpCloudコンソールを使って公開鍵を追加すると,EC2を再起動しなくともしばらくするとauthorized_keysに公開鍵が追加される.一方authorized_keys.jcorigには何も追加されない.また公開鍵を手動で追加した場合とJumpCloudコンソールから追加した場合とでauthorized_keysに記述される公開鍵の位置が以下のように異なる.

$ cat authorized_keys
# Keys added to this file will be automatically
# copied to your authorized_keys file by the JumpCloud agent.
[authorized_keys.jcorigからコピーされた手動で設定した公開鍵]

# Constructed by the JumpCloud agent, you can add keys manually in /home/yuichiro.takarada/.ssh/authorized_keys.jcorig
[JumpCloudコンソールで設定した公開鍵]

JumpCloudコンソールから公開鍵を削除

JumpCloudコンソールを使って公開鍵を削除すると,EC2を再起動しなくともしばらくするとauthorized_keysから公開鍵が削除される.ちなみに,JumpCloudコンソールから設定されたauthorized_keysに記述してある公開鍵を手動で削除するともちろんログインはできなくなるがJumpCloudのコンソールからは削除されない.そこからEC2を再起動するとauthorized_keysにJumpCloudコンソールで設定した公開鍵が再び設定される.

ここまでの検証で以下のことが分かる.

  • JumpCloudコンソールを使って追加された公開鍵はauthorized_keys.jcorigに書き込まれず,authorized_keysにのみ書き込まれる
  • JumpCloudコンソールを使って追加・削除された公開鍵は,EC2を再起動せずとも自動でauthorized_keysに追加・削除が行われる
  • JumpCloudコンソールを使って追加された公開鍵がauthorized_keysからサーバ上で手動により削除された場合でも,JumpCloudコンソールから削除されていなければ,EC2の再起動後再びauthorized_keysに書き込まれる
  • JumpCloudコンソールの状態を正として振舞う

検証結果からの学び

検証結果から,JumpCloudエージェントが入ったインスタンスでは「サーバ上で手動による公開鍵を設定する」・「JumpCloudで公開鍵を設定する」という2つの公開鍵設定方法がある中で,前者の方法ではauthorized_keys.jcorigを正としてauthorized_keysに公開鍵をコピーし,後者の方法ではJumpCloudコンソールを正としてauthorized_keysに公開鍵をコピーしていることが分かった.運用においては基本JumpCloudコンソール上から設定する方法でなんら問題ないと思うのでそうするつもりだが,どうしても手動で設定する必要がある場合だけ今回の調査を生かしてauthorized_keys.jcorigに公開鍵を記述する運用にしたい.とにかく,authorized_keys.jcorigが「サーバ上で手動による公開鍵を設定する」ためのファイルだということは理解した.

まとめ

  • JumpCloudエージェントが入ったインスタンスauthorized_keys.jcorigというファイルがなんのためにあるのかということを調べた
  • authorized_keys.jcorigは「サーバ上で公開鍵を設定する」場合の公開鍵を管理するファイルであることが分かった

LambdaとSSMでEC2からS3のオブジェクト取得させる

AWS環境で,Lambdaを用いてS3からEC2にオブジェクトを取得させる設定をしたのでメモがてら残しておく.

概要

Lambdaを使い,AWS Systems Manager(SSM)経由でEC2にAWSコマンドを実行させ,S3からオブジェクトを取得する.また,S3にあるオブジェクトは他AWSアカウントからPUTされたと想定する.以下に図を示す.

f:id:a-mochan:20200312213744j:plain

S3のバケットポリシーの設定

AWSアカウント間をまたぐS3バケットへのアップロードでは,オブジェクトの権限に気をつける.
例えば,AというAWSアカウントがS3バケットを作成したとして,Bという他のAWSアカウントからS3バケットへオブジェクトをPUTする.PUTする際にオプションを何も指定しなければ,S3バケットへPUTされたオブジェクトの所有者はBとなってしまい,AにあるEC2からはGETできないしマネージメントコンソールからもダウンロードできない.これを避けるため,PUTする際(aws-cliの場合)に--acl bucket-owner-full-controlオプションをつけて実行する.これにより,PUTしたオブジェクトの所有者がAアカウントになりAアカウントからGETすることが可能となる.詳しくは以下のブログが参考になった.

qiita.com

test-bucketというS3バケットを用意し,他のアカウントからPUTしてもらう.PUTしてもらう際に--acl bucket-owner-full-controlオプションをつけておかないとPUTできないようにConditionを設定したバケットポリシーを以下に示す.もっと制限をかけたければPrincipalCondtionを適宜追加する.

{
    "Version": "2012-10-17",
    "Id": "PolicyId2",
    "Statement": [
        {
            "Sid": "Test",
            "Effect": "Allow",
            "Principal": "*",
            "Action": [
                "s3:PutObject",
                "s3:PutObjectAcl"
            ],
            "Resource": "arn:aws:s3:::test-bucket/*",
            "Condition": {
                "StringEquals": "s3:x-amz-acl": "bucket-owner-full-control"
            }
        }
    ]
}

EC2の設定

EC2では,S3からオブジェクトを取得するために,今回はAWSコマンドを使う.AWSコマンドがない場合はインストールを行う.また,LambdaでSSMを使うのでEC2インスタンスにSSMエージェントが入っていない場合もインストールしておく.さらに,SSMがEC2を扱うことができるようにAmazonEC2RoleforSSMポリシーが付与されたロールをEC2にアタッチしておく.

Lambdaの設定

Lambdaでは,SSMを使ってEC2上でAWSコマンドを実行してS3からオブジェクトをEC2上に配置する.まずは,LambdaがSSMを扱うことができるようにAmazonSSMFullAccessポリシーが付与されたロールをLambdaにアタッチする.また,EC2上で実行するAWSコマンドで必要なシークレットIDとシークレットキーは環境変数としてLambdaで渡すようにする.Pythonで記述したLambdaの例を以下に示す.

import boto3
import os

ACCESS_KEY_ID = os.environ['ACCESS_KEY_ID']
SECRET_ACCESS_KEY = os.environ['SECRET_ACCESS_KEY']

ssm = boto3.client('ssm')

def lambda_handler(event, context):
    bucket = 'test-bukect/'
    ec2_dir = '/var/tmp'
    s3_dir = 'test/'
    
    commands = [
        "export AWS_ACCESS_KEY_ID=" + ACCESS_KEY_ID,
        "export AWS_SECRET_ACCESS_KEY=" + SECRET_ACCESS_KEY,
        "export AWS_DEFAULT_REGION=ap-northeast-1",
        'aws s3 sync s3://' + bucket + s3_dir + " " + ec2_dir
    ]
    ssm.send_command(
            InstanceIds = ('i-123456789',),
            DocumentName = "AWS-RunShellScript",
            Parameters = {
                "commands": commands,
                "executionTimeout": ["3600"]
            }
    )

これで,Lambdaを実行すればtest-bucketの/testディレクトリ以下にある内容がEC2上の/var/tmp以下にコピーされる.

まとめ

  • LambdaとSSMを用いてEC2からS3のオブジェクト取得させた
  • 他アカウントからのS3へのPUTではbucket-owner-full-controlをつける

Route53 Resolverを使う際気をつけたいこと

会社で,あるサービスをオンプレからAWSへ移行する際,Direct Connect(DX)を張って移行した.その際AWS環境からオンプレにある外部に公開していないDNSサーバへ名前解決する必要があったのでRoute53 Resolverを使用した.Route53 Resolverを使ってみて2つほど注意したいことがあったのでメモとして残しておこうと思う.

Route53 Resolver

Route53 Resolverとは,オンプレとVPCがDXやVPNで繋がっている環境で,オンプレからRoute53のプライベートホストゾーンへアクセスさせたり,逆にVPC内からオンプレの外部に公開していないDNSサーバへアクセスさせたい時に利用するサービスである.両アクセスはRoute53 Resolverによって作成されるエンドポイントを経由してそれぞれの権威サーバへアクセスが行われる.詳しい設定は以下のブログが参考になる.

dev.classmethod.jp

サブドメインの柔軟な名前解決が難しい

1つめの注意すべきことだが,タイトルだけでは何を言っているか分かりにくいと思う.
例として,Route53 Resolverを使ってAWS環境からオンプレのDNSexample.com)に名前解決する場合を考えてみる.example.comドメインRoute53 Resolverを作成すると,example.comおよびそのサブドメインはすべてオンプレのDNSに名前解決するようになる.
ここで,aaa.example.comの名前解決だけオンプレのDNSではなくRoute53に向けたい場合を考えると,example.comサブドメインもすべてオンプレのDNSへ名前解決しにいくので一筋縄ではいかない.この場合,オンプレDNS側でaaa.example.comはRoute53へ向けるNSレコードなどを追加して対応する必要があると思う.
では逆に,aaa.example.comの名前解決だけオンプレのDNSへ向けたい場合を考えると,これは単純にaaa.example.comドメインRoute53 Resolverを作成すればよいことになる.
要するにここで注意したいのは,Route53 Resolver作成時に設定するドメインとそのサブドメインはもれなく指定したDNSへ名前解決されてしまう点である.

さらに補足すると,example.comドメインaaa.example.comドメインのそれぞれでRoute53 Resolverを作成することができる.その場合はaaa.example.comドメインおよびそのサブドメインの名前解決はaaa.example.comドメインで作成したRoute53 Resolverを経由する.それ以外のexample.comドメインおよびそのサブドメインexample.comドメインで作成したRoute53 Resolverを経由する.つまり,最長一致のような動きをする.ただし,Route53 Resolverで設定するDNSにRoute53は指定できない.

f:id:a-mochan:20200229160945p:plain
ゾルバルール作成画面の説明

コストに注意

Route53のトラフィックフローなど他のRoute53の機能もそうなのだが,Route53の料金体系として設置しているだけでコストがかかるタイプが多い.Route53 Resolverの場合,設置するだけで一時間0.125USDなので一ヶ月で90USDかかる.そして,この90USDはElastic Network Interface(ENI)ごとにかかり,Route53 ResolverではDNSクエリに対して最低でも2つのIPアドレス(ENI)を必要とするので,設置するだけで一ヶ月180USD(90USD*2)かかる.さらに,通信量が上乗せされていくのでRoute53 Resolverを使う際は設置コストも頭に入れておくとよいかもしれない.

aws.amazon.com

まとめ

  • Route53 Resolverで気をつけるを紹介した
    • Route53 Resolverを経由させるサブドメインの扱いに注意する
    • 通信量の他に設置コストもかかる

簡単!ワークフロービルダーで業務効率化してみた

去年の10月頃にSlackの機能としてワークフロービルダーがリリースされた.ワークフロービルダーとはある程度決められた業務フローをSlackで自動化できる機能である.最近業務で使ったので事例紹介と使ってみての感想を書こうと思う.

事例の説明

弊社はワークフローのSaaSを使って業務フローを効率化しているが,エンジニアの業務フローの中にはSaaSを使う必要のないフローがあったりする.その一例として個人が使うサンドボックスAWSアカウントの作成がある.弊社では,開発エンジニアはインフラへ申請すれば,検証のための個人用AWSアカウントを発行できるルールになっている.今回はその申請をワークフロービルダーを使って出せるようにした.
全体の流れを説明すると,まず申請者はAというチャンネルでワークフロービルダーを使って申請を行う.すると,インフラがワークフロー通知用に作ったBというチャンネルに内容が表示される.そして,インフラが申請内容についての作業を行った後,再びAチャンネルに作業完了の通知が表示される.

手順

ワークフローを作成するには,Slackの左上にある自分の名前やワークスペース名が表示されている部分をクリックし,「ワークフロービルダー」を選択する.すると以下のような画面が出てくる. f:id:a-mochan:20200202112008p:plain

「作成」を押すと,ワークフロー作成後に上記のワークフロー一覧画面で表示される名前の入力を求められるので,「個人用AWSアカウント申請」という名前で登録する. f:id:a-mochan:20200202112140p:plain

次に,ワークフローをどのトリガーで発火させるかを選ぶ.この事例では,申請者がアクションメニューから明示的に申請してほしいので「アクションメニュー」を選択する. f:id:a-mochan:20200202112226p:plain

次に,アクションメニュー一覧に表示されるワークフローの名前の設定と作成するワークフローをどのチャンネルに設定するかを聞かれるので以下のように設定する. f:id:a-mochan:20200202112302p:plain

ここまでで以下のようなベースができるので,ここからは1つずつステップを作成していく. f:id:a-mochan:20200202112323p:plain

まずはAチャンネルで申請内容を入力してもらうフォームを表示したいので,ステップの追加で「フォームを作成する」を選択し,以下のようなフォームを作成する.入力したフォームの内容をAチャンネルにも流しておきたいので,「提出された回答をチャンネルまたはDMで他のメンバーに送信する」にチェックをつけてAチャンネルを指定しておく. f:id:a-mochan:20200202112440p:plain

次に,Bチャンネルにフォームで提出された内容を流したいので,ステップの追加で「メッセージを送信」を選択し,以下のように設定する.Bチャンネルに流れるメッセージに「完了」ボタンをつけて,作業者が作業を終えたタイミングでそのボタンを押し,次のステップに進むようにする.
メッセージの送信では,当該ステップより前に作成されたステップに関連する要素を変数としてテキストに埋め込むことができる.また,下部には作成したテキストのレビューが表示されているので確認が容易だ. f:id:a-mochan:20200202112457p:plain

完了ボタンが押されたあと,再びAチャンネルに完了が通知されるように以下のようにステップを追加する. f:id:a-mochan:20200219075201p:plain

全体像は以下のようになる.右上に「公開する」ボタンがあると思うのでそれを押すとAチャンネルで使えるようになる. f:id:a-mochan:20200219075417p:plain

では,実際にAチャンネルの右上に表示されている⚡️マークから「個人用AWSアカウント申請」を押してみる.すると先ほど作成したフォームが表示されると思うので「申請者」「申請者のメールアドレス」を入力して「送信」をクリック.その内容がAチャンネルに流れる. f:id:a-mochan:20200219075440p:plain

それと同時にBチャンネルにも流れている.作業者は作業を行なったあと,「完了」ボタンを押す. f:id:a-mochan:20200219075451p:plain

するとAチャンネルの方に完了通知が届く. f:id:a-mochan:20200219075503p:plain

以上が個人用AWSアカウント申請を例にした紹介である.

感想

使ってみてワークフロービルダーは良いところといまいちなところが見えた.

  • 良いところ
    • ワークフローの作成自体がとても容易
    • 作成した人や使う人がワークフロービルダーが出力する内容を削除できないので,変に消されたり修正されたりしない
  • いまいちなところ
    • フローの分岐や前のステータスに戻ることができない
    • 良いところの裏返しであるが,間違って出してしまっても取り消すことができない
    • 複数のチャンネルでワークフローの共有ができないので,1つ作って他で使い回すことができない

まとめ

  • Slackのワークフロービルダーを使ってみた
  • とても簡単にワークフローを作れる反面,機能はまだ少ないので簡単なワークフローを作成する際に使う感じになりそう

シリコンバレーでのキャリアを考えている人必読「エンジニアとして世界の最前線で働く選択肢」を読んだ

私は大学院生の時、大学を通じてロサンゼルスへ行き、そこでインターンをしたり、シリコンバレーのスタートアップで話を聞いたりした経験があります。その時から、いつかまたシリコンバレーで働いてみたいなぁと思っていました。そんななかつい先日、「エンジニアとして世界の最前線で働く選択肢」という本を知って読んでみたらとても面白かったので、内容の紹介や感想を書いていきたいと思います。

  • 1章:あなたはアメリカに合っているのか
  • 2章:どうやったら渡米できるか
  • 3章:アメリカ企業に就職・転職する
  • 4章:ホワイトボードコーディング面接を突破する
  • 5章:アメリカで働くと何が違うのか
  • 6章:転職を通してキャリアアップする
  • 7章:解雇に備える

ビザの取得

今日本で働いている日本人がアメリカで働きたいと思った時、まず一番最初にぶち当たる大きい壁がビザの取得です。当たり前ですが、アメリカで日本人が働くには、入国して働くためのビザが必要となります。この本を読むと、アメリカで働くためのビザにはいくつか種類があり、それぞれのビザを取得するには労力や難易度が違うことが分かります。本では4つの方法が紹介されていたので簡単に説明します。ただし、ビザの取得方法や種類は変わることがあるのでその時の最新情報はみておいた方がよいです。

留学後に現地就職

これは一番の王道パターンです。経済的・時間的問題がないのであればこれが確実にアメリカでの就職に繋がります。ただし、会社を辞めて留学するパターンでは、アメリカでの就職に失敗した場合、日本に戻っても年齢の問題で就職が難しくなるリスクはあります。これに関しては、@noralifeさんが以下のポッドキャストで実例をお話されていたので聞いてみるといいかと思います。

fukabori.fm

日本から直接雇用

これは一番ハードルが高く、運も必要な方法です。難しい理由としては2つあります。1つは面接の労力が大きい点です。アメリカ国内にたくさんの優秀なエンジニアがいるのに、わざわざ海外のエンジニアを面接する理由はあまりありません。オンサイト面接で飛行機代やその他のお金がかかったり、採用した人が文化に馴染めなかった時のリスクなどもあります。
もう1つは、採用した後のビザの問題です。H1-Bビザの枠に余裕がなければ4月にビザを申請して10月に発給されるまで半年待たなければなりません。「このチームの人員が足りないから雇いたい」という場合はまず採用されません。

ただし、それでも海外のエンジニアを雇いたい理由はあります。まず思いつくのが、応募してきたのがとても優秀なエンジニアである場合です。オープンソースに多大に貢献していて、そのネットワークを使って紹介されるケースが考えられます。ビザの問題は別途あるのですが、例えば著名な論文誌や学会の掲載実績があるなどの理由で発行されるOビザを取得できるのであれば、ビザの問題はなくなり採用される可能性があがります。
次に考えられるのが、日本人を雇いたい場合です。アメリカにありながら日本を相手にしたビジネスをしている会社や、もともと日本人が作った会社で日本人を増やしたいときなど、日本人を直接雇う場合があります。

日本で就職後に移籍

これはアメリカに本社がある外資系かアメリカに支社がある日本の会社に入社して、海外へ社内移籍するという方法です。この方法のメリットはなんといっても簡単な点に尽きます。前述した留学の方法は社会人にとってはハードルが高いですし、直接雇用の方法もアメリカの会社の面接を突破し、ビザの問題もクリアしなければならないのでハードルが高いです。それよりは、日本で働いて仕事の実績を武器に移籍する方がずっと簡単ですし、社内移籍ではLビザ・Eビザ等を使用できます。
一方デメリットは、実現が運まかせなことです。どんなに仕事の実力があっても、その時の会社の方針や状況により移れないことがあります。著者は運良く入社後数年で駐在のチャンスをもらえたそうです。「アメリカで働けるといいなと思っているけど、日本でずっと働くのもそんなに悪くない」と思っている人は向いている方法だと書いていました。

その他の方法

他にも、抽選による永住権の取得アメリカ人との結婚による永住権の取得アメリカで生まれたことによる市民権の取得などのパターンもあるのですが、上記で紹介した方法が海外で働く現実的な路線だと思います。どの方法もトレードオフで本人の人生の選択によることが分かりました。ただ、この本を読んで色々と海外就職について調べていたらEビザを使った方法で、かつ私が知らなかった方法で海外就職した方がいたので紹介しておきます。

taiyakijp.com

エンジニアの面接

この本では、面接に関することについてかなり詳細に記載されています。著者が面接官としての経験から、面接前・面接中・面接後のさまざまなフェーズで気をつけるべきことを記しています。例としては、面接前のリクルータとのチャットにおいて、「希望給与額を言ってはいけないらしいです。これは将来の給料交渉が非常に難しくなるからです。「仕事の内容を詳しく理解したうえで考えたい」などと言っておけば大丈夫です。何も知らないとつい言ってしまいそうで、損をする可能性もありそうですね。
また、アメリカの面接も日本と同様で、応募する側も面接官を見極めるスタンスは一緒です。面接の最後では、たいていこちら側に質問があるか聞かれます。ここでする質問は自分の就職の軸や会社に求めることによって変わると思うのですが、本で紹介されていた1つの質問が日本では考えられないなと思ったので紹介しておきます。それは、「ほかにもいい会社はたくさんありますが、なぜ私はこの会社を選ぶべきなのでしょうか」です。日本では通常会社側がする質問ですが、アメリカの面接では応募する側がするのですね。この質問の背景には別のコンテキストがあるのかもしれませんが、日本の面接ではまずしない質問で驚きました。
さらに著者はソフトウェアエンジニアなので、面接で実施されるホワイトボードコーディングについても詳細に記されていました。ソフトウェアエンジニアの方は読んでみるとよいかもしれません。

人の流動性や解雇について

シリコンバレーでの1つの会社の平均勤続年数は2〜3年で、転職を繰り返しキャリアアップをするのが普通です。人の流動性が高く、みな「良い環境」「良い給料」「良い仕事内容」などさまざまな理由で転職します。そしてたいていの会社は、一度面接が失敗しても、半年から1年経てば再チャレンジを認めており、会社側も人を受け入れる体制があります。この本の第6章では、シリコンバレーでエンジニアキャリアを歩むために必要な知識や気をつけるべきことがまとめられていました。それらの中には日本でエンジニアキャリアを歩む人にもためになる内容もあります。例えば以下のような内容です。

  • 「できること」よりも「やりたいこと」
  • 転職のタイミングは自分よりも周りの状況がカギ
  • 迷ったら難しいポジションを選ぶ
  • 仕事の内容も環境も大事
  • 大企業とスタートアップでできること・できないこと
  • 日本人として働くことのメリット・デメリット
  • マネジメント系と技術系のキャリアトラックがある

日本のWeb系企業に勤めている私からみると、シリコンバレーにはエンジニアとして働く環境が整っていると感じざるを得ませんでした。ただ実際に働いていないので、働く環境なら日本のWeb系企業もあまりひけを取らないなという印象もいだきました。

人の流動性が高いというのは、自分の望み通りの転職ができるというわけではありません。会社に解雇されることも十分にあります。
解雇には大きく2種類あって、悪い解雇の「ファイア」と、必ずしも悪いとは言えない「レイオフ」があります。基本的にファイアは、違法行為や社内規則違反など「従業員側に非があった場合の解雇」です。ただし、パフォーマンス不足もファイア扱いになってしまうそうです。ファイア扱いになってしまうと次の転職活動が大変になるので、パフォーマンス不足の場合は自主退職という形で辞めさせてくれる会社もあるそうです。
一方、レイオフ「会社に原因がある場合の解雇」です。これは経済状況悪化や事業方針の転換などさまざまな理由で起こり得ます。またファイアとは違ってある日突然レイオフされることも特徴です。著者もレイオフにあったことがあるので、レイオフへの対策はたくさん書かれていました。ここはとても生々しいというかリアルな話なのでぜひ読んでみて欲しいです。

まとめ

  • 「エンジニアとして世界の最前線で働く選択肢」を読んだ
  • 実際にシリコンバレーで日本人エンジニアとして10年以上も働いている人が書いた本なので、内容がとても細かくて状況をイメージしやすかった
  • シリコンバレーでエンジニアとして働いてみたいとうっすらとでも思っている人には一読してほしい本でした

監視のいろはを学ぶ !入門 監視を読んだ

会社では,オンプレにあるサービスの監視はZabbixを使っているが,クラウドで動いているサービスの監視はこれといった監視システムは決まっていない.何か新しいものを導入するのか,そのままZabbixを使っていくのかにかかわらず,Webサービスを提供する上での監視ってそもそもなんだっけ?という漠然としたことを知りたかったので,今回は「入門 監視」を読んだ.各章で語られる内容を今運用しているZabbixに当てはめて考えながら読んでみると,なるほどと納得することが多かった.この本では,監視する対象が網羅的に書かれていて,それぞれの詳しい監視の方法については別途他の書籍や記事が紹介されている.この記事では,監視の基本的なことが書いてある1章と2章,そして3章のアラートについてまとめようと思う.

入門 監視 ―モダンなモニタリングのためのデザインパターン

入門 監視 ―モダンなモニタリングのためのデザインパターン

目次

  • 1章 : 監視のアンチパターン
  • 2章 : 監視のデザインパターン
  • 3章 : アラート、オンコール、インシデント管理
  • 4章 : 統計入門
  • 5章 : ビジネスを監視する
  • 6章 : フロントエンド監視
  • 7章 : アプリケーション監視
  • 8章 : サーバ監視
  • 9章 : ネットワーク監視
  • 10章 : セキュリティ監視
  • 11章 : 監視アセスメントの実行
  • 付録A : 手順書の例:Demo App
  • 付録B : 可用性表
  • 付録C : 実践 監視 SaaS

監視のアンチパターン1:ツール依存

ここでは「何を実現したいか」よりも「どのツールを使うか」に焦点があたってしまう問題を指摘している.

監視とは,単一の問題ではなく,いくつかの問題が複雑にからみあっているので,このツールを使えばいいという銀の弾丸はない.また,成功した企業が使っているツールを採用すればいいかと考える人もいるかもしれないが,それらの企業は成功するために必要だったからそのツールを採用したのであってその逆ではない.

そして,銀の弾丸がないからこそ複数のツールを使って問題を解決することもあるが,ツールを増やすことを無意味に怖がってはいけない.「仕事ができる最低限の数」であればツールは増やしても良いというのが執筆者の考えだ.

監視で可視化は重要であるが,「一目でわかるようにしてほしい」と言われたときは注意が必要だ.監視における「一目でわかる」アプローチとは,状態を見るための1つの場所が欲しいのであって,1つのツールや1つのダッシュボードが欲しいわけではない.ツールとダッシュボードは1対1に対応している必要はないので,複数のツールから1つのダッシュボードに出力することだってある.それらのダッシュボードシステムが「一目でわかる場所」にあればいいだけだ.

監視のアンチパターン2:役割としての監視

ここでは,監視は現場の人間が仕組み化することが重要だと述べられている.

会社に監視専門部隊があるとして,すべての監視をその部隊にやらせるのはアンチパターンである,監視対象に詳しくない人間が監視をするのは無理なので,アプリケーション・データベース・ネットワークエンジニアなどの現場のメンバーが監視の仕組みをデザインするべき.監視とは役割ではなくスキルである.監視専門部隊の仕事は現場のチームに監視ツールをサービスとして提供することになる.

監視のアンチパターン3:チェックボックス監視

チェックボックス監視とは「これを監視しています」と言うための,とりあえず設定したような監視システムのこと.以下のような兆候があると,このアンチパターンに陥ってしまっている可能性がある.

  • システム負荷,CPU使用率,メモリ使用率などのメトリクスは記録しているが,システムがダウンしたことの理由は分からない
  • 誤検知が多すぎるのでアラートを常に無視する
  • 各メトリクスを5分あるいはそれより長い間隔で監視している
  • メトリクスの履歴を保存していない

このアンチパターンは,監視のアンチパターン2と一緒に見つかることがある.監視を設定する人がシステムの動作を完全に理解していないと,シンプルで簡単なものの監視設定をしただけになるからである.このアンチパターンを直すにはいくつかの方法がある.

「動いている」かどうかを監視する

「動いている」ことを定義すると,何を監視すればよいかが決まり,それらは「何か」がおかしいことを示す優れた指標となる. 例えば,Webアプリケーションの場合,HTTPでGET /したレスポンスコードを記録し,HTTP 200 OKが返ってきているか,ページに特定の文字列があるかどうか,さらにリクエストのレイテンシが小さいかどうかを確認する.この監視1つで,アプリケーションが本当に動いているかどうかに関するたくさんの情報を得ることができる.

アラートに関してはOSのメトリクスはあまり意味がない

例えば,MySQLが継続的にCPUを全部使っていたとしても,レスポンスタイムが許容範囲に収まっていれば何も問題はない. CPU使用率やメモリ使用率のような低レベルなメトリクスで判断してアラートを送るよりは,「動いている」かどうかでアラートを送る方が有益である.

メトリクスをもっと高頻度で取得する

複雑なシステムでは,数分あるいは数秒の間にたくさんのことが起きるので,5分に1回しかメトリクスを取得しない設定などは実質的に何も見ていないのと同じ.最低でも60秒に1回メトリクスを取得する.

監視のアンチパターン4:監視を支えにする

アプリケーションのバグが出た時に根本解決をせずにその場所に監視を仕込んで気づけるようにしておく暫定対応はよくない.監視を増やしてもシステムが直るわけではないので,状況の改善にならない.

監視のアンチパターン5:手動設定

監視は100%自動化する.新しい監視設定を自動化していないと監視設定がストレスになり,やがて考えるのをやめてしまう.

監視のデザインパターン1:組み合わせ可能な監視

組み合わせ可能な監視とは,専門化されたツールを複数使い,それらを疎に結合させて作る「監視プラットフォーム」である.利点として,1つのツールややり方に長期間にわたってコミットする必要がないことが挙げられる.あるツールがやり方に合わなくなった時,監視プラットフォーム全体を置き換えるのではなく,そのツールだけを削除して他のもので置き換えることが可能である.監視サービスには以下の5つの要素がある.

  1. データ収集
  2. データストレージ
  3. 可視化
  4. 分析とレポート
  5. アラート

1.データ収集

データ収集コンポーネントは,その名の通りデータを収集する.データ収集を行うには主な方法としてプル型プッシュ型がある.プル型は,サービスがリモートノードに対して,ノードの情報を送りつけてくるよう要求を出す.一方,プッシュ型は,クライアント(サーバやアプリケーションなど)はデータを他の場所に,一定間隔あるいはイベントが発生したタイミングでプッシュする.どちらの方法もそれぞれメリットとユースケースがある.

どんなデータを集めるかによって,メトリクスとログという2種類のデータがある.

メトリクス

メトリクスには2つの表現方法がある.
1つはカウンタである.カウンタは,車の走行距離計のような増加していくメトリクスで,Webサイトに訪れる累計人数を数えるといった用途に最適である.
もう1つはゲージである.ゲージは,車の速度計のようなある時点の値を表す.

ログ

ログは,基本的には連続した文字列のことで,いつイベントが発生したのかを示すタイムスタンプが関連づけられたものである.ログには構造化ログ非構造化ログの2種類がある.
非構造化ログとは,各フィールドに対して明確な意味のマッピングはない.例えば,Webサーバがデフォルトで出力するようなログが非構造化ログである.
反対に,構造化ログは各フィールドの意味が分かるようJSONやその他さまざまな形で非構造化ログをパースしたものである.

2.データストレージ

時系列データであるメトリクスは,通常は時系列データベース(TSDB:Time Series Database)に保存される.TSDBは,基本的にはタイムスタンプと値というキーと値のペアから構成される時系列データを保存するためにデザインされた専用のデータベースである.このキーと値のペアをデータポイントという.
TSDBの多くは,一定期間後にデータの「間引き」や「有効期限切れ」が発生する.これは,データが古くなったら,複数のデータポイントが1つのデータポイントにまとめられることを意味する.よくある間引きの方法としては平均化がある.

ログのストレージにはシステムにファイルとして保存するか検索エンジン(Elasticsearchなど)に保存するかの2つの方法がある.

3.可視化

収集したログやメトリクスをダッシュボードにまとめたりして可視化する.可視化はそれだけで1冊の本が書ける.以下はこの本で紹介されていた可視化についての書籍である.

The Visual Display of Quantitative Information

The Visual Display of Quantitative Information

  • 作者:Edward R. Tufte
  • 出版社/メーカー: Graphics Pr
  • 発売日: 2001/05/01
  • メディア: ハードカバー

Information Dashboard Design: The Effective Visual Communication Of Data

Information Dashboard Design: The Effective Visual Communication Of Data

  • 作者:Stephen Few
  • 出版社/メーカー: Oreilly & Associates Inc
  • 発売日: 2005/03/27
  • メディア: ペーパーバック

4.分析とレポート

監視データの種類によっては,単なる可視化を超えて,分析とレポートの分野に踏み込むと有益な場合がある.よくあるユースケースの1つとして,アプリケーションとサービスのサービスレベルアグリーメントSLA:Service Level Agreement)を決定し,レポートする場合が挙げられる.SLAとは,サービス提供者と顧客との間で取り交わされる,アプリケーションやサービスに期待される可用性についての契約である.

5.アラート

監視した結果をアラートとして人間に知らせる必要があるが,監視の目的をしっかり理解することが重要である.後半で詳しく説明する.

監視のデザインパターン2:ユーザ視点での監視

監視対象は数多く存在するが,まず監視するべきなのは,ユーザがアプリケーションとやり取りをするところである.もっとも効果的な監視ができる方法の1つが,HTTPレスポンスコードを使うことである.ここから監視を始め,徐々にWebのノードやワーカノードといったコンポーネントに監視を広げていくことになるが,常に「このメトリクスはユーザへの影響をどう教えてくれるか」と自問自答するべきである.

f:id:a-mochan:20200119212605j:plain
ユーザに近いところから監視を始める

監視のデザインパターン3:作るのではなく買う

監視ツールのSaaSサービスを使う.これはツール依存アンチパターンの答えにもなるようなデザインパターンである.執筆者がSaaSの購入を勧める理由は以下の通り.

  • 安いから
  • 監視ツールの設計を行う人がその道の専門家ではないことが多いから
  • 簡単かつすぐに監視の仕組みを立ち上げられるのでプロダクトにフォーカスできるから
  • SaaSを使わない合理的な理由が少ないから

SaaSを使いたがらない人が挙げる理由のほとんどは,上記の認知コストに行き着く.オンプレの監視の仕組みを成長させるのに必要なお金や時間が,SaaSの監視の仕組みを成長させるお金や時間を超えることは往往にしてあるので検討する余地はある.

監視のデザインパターン4:継続的改善

監視の仕組みを常に改善したり再構築を重ねることで監視を育てていく.

どうしたらアラートをよくできるか

監視システムを構築すれば必ずアラートの設定は必要になるのに,アラートはなぜか無視されたり気づかれなかったりする.人間の注意力には限りがあるので,アラートをどうやったらよくできるかという課題はとても重要である.アラートをコンテキストによって2つの意味で使い分けている人が多いので,まずそれを定義する.

  • 誰かを叩き起こすアラート
    • 緊急の対応が求められ,でなければシステムがダウンしてしまう
    • 例:全Webサーバがダウンし,メインサイトへの疎通が取れない
  • 参考情報としてのアラート
    • すぐに対応する必要はないが,アラートが来たことは誰かが確認すべきもの
    • 例:夜間バックアップジョブの失敗

ここでは前者について取り上げる.よいアラートの仕組みを作るために気をつけるべき項目を以下に示す.

  1. アラートにメールを使うのをやめる
  2. 手順書を書く
  3. 固定の閾値を決めることだけが方法ではない
  4. アラートを削除してチューニングする
  5. メンテナンス期間を使う
  6. まずは自動復旧を試す

それぞれ説明する.

1.アラートにメールを使うのをやめる

メールでアラートを送るのは,受け取る人がうるさくて最もうんざりしてしまう方法で,アラート疲れの原因になる.そこで以下の3つのパターンでアラートの送り先を変える.

すぐに応答かアクションが必要なアラート

これらはSMS,PagerDutyなどに送る.

注意は必要だがすぐにアクションは必要ないアラート

これらは社内のチャットルームに送るのがおすすめ.メールだと受信箱をいっぱいにしてしまいがちなので,他の場所があるならそちらの方がよい.

履歴や診断のために保存しておくアラート

必要なアラートはログファイルにも送る.アラートを保存しておくと,あとでどのアプリケーションやサービスでトラブルが多いかというレポートを送ることができる.

2.手順書を書く

手順書は,アラートが来た時に自分のやるべきことが分かるすばらしい方法である.また,環境が複雑になってくると,チームの誰もが各システムのことを知っているわけではなくなるので,手順書が知識を広めるよい方法にもなる.よい手順書とは,以下のような質問に答えるように書かれたものである.

  • これは何のサービスで,何をするものか
  • 誰が責任者か
  • どんな依存性を持っているか
  • インフラの構成はどのようなものか
  • どんなメトリクスやログを送っていて,それらはどういう意味なのか
  • どんなアラートが設定されていて,その理由は何なのか

ただし,手順書はなんでもかんでも作ればいいというものではない.例えば,修復手順がコピーアンドペーストでできるような内容ならば,その修復を自動化してアラート自体を削除できる.手順書は,何らかの問題を解決するのに,人間の判断と診断が必要な時に使うものである.

3.固定の閾値を決めることだけが方法ではない

「この値がXを超えた」といったアラートに意味がない場合はたくさんある.典型的な例としては,ディスク使用量がある.「空き容量が10%以下」という固定された閾値を決めてしまうと,ディスク使用量が11%から80%まで急激に増えるというケースを見逃してしまう.これを解決する方法の1つに,変化量やグラフの傾きを使うことがあげられる.

4.アラートを削除してチューニングする

うるさすぎるアラートは監視システムの信頼をなくし,アラートを受け取る人はアラートを無視してしまうようになる.しかし,それが監視の仕組みを完全に信頼しない理由にはならないので監視を継続するが,時間が経つとアラート疲れを起こしてしまう.この対策はシンプルでアラートを減らすことに尽きる.以下に示すのは,アラートを減らす際に考えるべき項目である.

  • すべてのアラートは誰かがアクションする必要がある状態か
  • 1ヶ月間のアラートの履歴を見て,どんなアラートがあるか,どんなアクションを取ったか,各アラートの影響はどうか,削除できるか,閾値を変更できるか,監視の内容をより正確にできるかを考える
  • アラートを完全に削除するために,どんな自動化の仕組みが作れるか

5.メンテナンス期間を使う

何らかのサービスで作業をする必要があり,そのサービスにはアラートが設定されているなら,アラートをメンテナンス期間に入れる.これにより不要なアラートは飛ばなくなる.ただし,アラートの止めすぎには注意する.作業をしていたら,知らなかった依存性があり,他のサービスで問題が起きることもある.

6.まずは自動復旧を試す

自動復旧はアラート疲れを避けるすばらしい方法なので,アラートを送る前にまずは自動復旧ができるかを考える.

まとめ

  • 「入門 監視」を読んだ
  • 1,2章を読んで,目的にあったツールを選んでいこうと思えた
  • レイヤーに関係なく網羅的に監視の話が書かれているので,それぞれのレイヤーで何が監視対象になりうるか全体像が把握できた

MySQLのパフォーマンスチューニング勉強会に参加した

MySQL8.0のパフォーマンスチューニング勉強会に参加した.クエリのチューニングよりかはDBA目線のチューニングを知りたいという部分が目的だったが,クエリのチューニングについても勉強になった.資料すべてを解説したわけではないので,お話していただいた箇所のメモをまとめる.

eventreg.oracle.com

Oracleアカウントをお持ちの方は資料を以下のサイトからダウンロードできる.

www.mysql.com

この勉強会では,前半でチューニングの概論とMySQL ServerのチューニングのようなDBA目線のチューニングについて話され,後半で開発者目線のSQLチューニングについて話された.

チューニング概論

キューイング

複数のユーザ/リクエストがある場合基本的にはキューイングされていき,システムが飽和状態に近づくとキューイングによる遅延が急激に増大する現象がある.これを「ホッケースティック」と呼び,「ホッケースティック」に陥るとボトルネックがCPU,ネットワーク,ディスクのどこにあるのか見極める必要がある

ベンチマークテストのありがちな間違い

  • データサイズが想定されるサイズと全然違う
  • 同時接続数を意識せずツールでただ回すだけのテスト

ビジネス面からの考慮

  • チューニングするよりハードウェア交換の方が早いのではないか
  • そのパフォーマンス,スケーラビリティ,信頼性は本当に必要かどうか

MySQL ServerチューニングTips

ここではDBA目線でMySQL Serverのチューニングを学ぶ.パラメータ設定が主なチューニングとなる.

システム変数の変更

  • my.cnfを変更して再起動
  • SET GLOBALで再起動せずとも変更できるが,再起動すると元に戻る
  • mysql8.0ではSET PERSISTで再起動しなくても永続的に変更できるが,設定はmy.cnfではなくmysqld-auto.cnfに格納されるので注意

ステータス変数

  • mysql> show statusで今の状況を確認できる
  • mysql> variablesSET GLOBALで書き換えられたMySQLサーバーの設定を見ることができる

パフォーマンス・スキーマ/sysスキーマ

  • 性能統計情報分析のためのしくみ
  • パフォーマンス・スキーマはかなり詳細の統計まで表示されるが項目が多くややこしい
  • sysスキーマはパフォーマンススキーマを便利に使うためのビュー,プロシージャ,ファンクションのセット

チューニングされるパラメータ&チューニングの際よく使われる指標

  • max_connections(デフォルト151)
    • サーバが許容可能なコネクション数
    • 多すぎるとメモリを消費しきる可能性がある
  • Max_used_connections

    • mysql>show statusで確認できる
    • サーバーが開始されてから同時に使用された接続の最大数
    • Max_used_connectionsmax_connectionsを超えていると接続できなかったコネクションがある」のように,max_connectionsの設定の指標になる
  • thread_cache_size(デフォルトは「9=8+(max_connections/100)」による自動計算)

    • コネクションの受け口となるスレッドをキャッシュとしてプールしておく数
    • MySQL的にはFileIO(ファイル開いて,書き込んで,閉じてみたいな作業)が一番のコストなのでFileIOが発生しないようにスレッドを用意しておくのがこのオプション
  • threads_created

    • 接続を処理するために作成されたスレッドの数
    • 通常運用で増えていることがあればthread_cache_sizeを増やすことを検討する
  • sort_buffer_size(デフォルト256KB)

    • ソート用のメモリサイズ
  • sort_merge_passes

    • sort_buffer_sizeが足りず,一時ファイルを利用してソートした回数
    • これが増えているようならsort_buffer_sizeをあげるかインデックスを貼ることを検討
  • innodb_buffer_pool_size(デフォルト128MB)

    • テーブルおよびインデックスのデータをキャッシュするメモリ
    • デフォルトで小さく設定してある
    • サーバがMySQL専用なのであればメインメモリの80%程度を割り当てる
  • innodb_log_file_size(デフォルト48MB)

    • リカバリ用にUPDATEINSERTといった更新処理を貯めておくログファイルのサイズ
    • innodb_buffer_pool_sizeの25~100%
    • 値を大きくするとクラッシュ後のリカバリ時間が長くなる
  • innodb_file_per_table(デフォルトON)

    • テーブル単位でOS上のファイルを分ける設定
    • ONにしているとデータを削除すればディスクは空くが,OFFにしているとデータはibdataファイルに貯められ,データを削除してもibdataからは消えない
  • innodb_flush_log_at_trx_commit(デフォルト1)
    • 1(遅い)コミット時にログバッファがログファイルへ書き込まれ,ログファイルをディスクへフラッシュ(真のACID)
    • 2(速い)コミット時にはログファイルへ書き込み,ディスクへのフラッシュは毎秒一回
    • 0(最速)ログを毎秒一回(またはそれ以下)でディスクにフラッシュ
    • スレーブでマスターに昇格しないものが確定しているのであれば0や2でもよいが,マスターは必ず1にする
  • innodb_io_capacity(デフォルト200)
    • InnoDBのバックグラウンドタスクに使用するI/Oキャパシティ(IOPS)の上限を設定
    • SSDを使うことしか想定しない場合はもう少しあげてもよい

SQLチューニングの基本

ここからは開発者目線のSQLチューニングに関する内容である.システムが遅い時を想定してMySQLのチューニングを学んでいく.

問題の特定

システムが遅い時,クエリが問題な可能性もあればネットワークやディスクが問題の可能性もあるので多角的な調査が必要.以下はMySQLで問題があるかどうかを調べる際に有効な手段.

  • スロークエリログ
    • 実行時間が指定した時間以上のクエリをスロークエリとしてファイルに出力する
    • 有事の際は普通のクエリもスロークエリとして吐き出されてしまうので,何かが起きる瞬間を捉えるために利用すべき
  • mysql> SHOW FULL PROCESSLIST
    • 今動いているクエリやリクエストされているクエリなどのプロセスを表示
  • パフォーマンススキーマでの確認
    • SHOW FULL PROCESSLIST+αの情報が得られる
    • SQLで確認できる
  • MySQL Query Analyzer
    • 商用のみ
    • スロークエリが発生した時,CPUやネットワークなどの他の要因と相関を可視化できる

クエリの問題である可能性がある時

システムが遅い原因がクエリにあると仮定したら,まずは実行計画をとりtypeを確認する.以下の表は上から順にtypeの望ましい値となっている.

typeの値 意味
system 1行しかないテーブル(systemテーブル)※constの特殊な例
const PRIMARY/UNIQUEインデックスによる等価検索(一意検索)
eq_ref PRIMARY/UNIQUEインデックスによるJOIN
ref ユニークでないインデックスによる等価検索,JOIN
fulltext 全文検索インデックスを使用した全文検索
ref_or_null ユニークでないインデックスによる等価検索とIS NULLのOR
index_merge 複数のインデックスをマージ
unique_subquery サブクエリ内で,PRIMARY/UNIQUEインデックスで等価検索(一意検索)
range 範囲検索
index インデックスのフルスキャン
ALL フルテーブルスキャン

indexALLを対応すれば遅いクエリが解消されることが多い.indexはたいていrefeq_refにできる.ただしindexの場合でも対象行数が少ない場合は例外としてindexでもOK.基本的にはrowsをなるべく小さくするような方針で解消していく.rowsとはクエリを実行するためにフェッチされる行数の推測値である.

遅いクエリを解消する方法に関する追加情報としてExtraという項目がある.

  • Using filesort
    • 使えるインデックスがなかったので一時ファイルを使ってソートしてしまったが,インデックスがあれば解消できるかもしれない
  • Using temporary
    • 同上
  • Using index
    • クエリが1つのインデックスにアクセスするだけで完結するので非常に高速
    • これが出てくるようにチューニングすべき
  • Using where
    • テーブルから行をフェッチした後にさらにWHERE条件での絞り込みが必要なもの
    • インデックスが使われない場合に出てくるが,インデックスが使われてもインデックスでの検索後にさらにもう一段階のWHEREによる絞り込みが必要な場合にも出てくる

SELECTの種類を示すselect_typeという項目がある.

  • DEPENDENT UNIONDEPENDENT SUBQUERYは可能な限り避けた方がよい
  • DEPENDENT UNIONはUNION内の2つめ以降のSELECTステートメントで外側のクエリに依存していることを表し,DEPENDENT SUBQUERYはサブクエリ内の最初のSELECTで外側のクエリに依存していることを表している
  • どちらも,結果1行1行に対して外側の表のマッチング処理が行われるため非常に工数が大きい

遅いクエリの対応方法

クエリに原因があると分かればSQLチューニングを実施する.

SQLチューニングの基本
  • インデックスの活用
  • 複数テーブルのJOIN
    • 取り出す行数が少量の小さなテーブルから順番にJOINするのが基本
  • 従来はチューニングのためにサブクエリをJOINに書き換えることが多かったが,MySQL5.6以降ではJOINに書き換えなくてもパフォーマンスよく実行できるケースが多い
インデックスの考慮事項
  • 表データを全件取得する場合はインデックスを使わない方が高速になる
  • UPDATE文でインデックスが使えていない場合は,ロック待ちを過剰に発生させる可能性があるので要注意
    • InnoDBでは,処理した行ではなくアクセスした行に対してロックを取得するため,1件しか更新しないUPDATE文であってもインデックスが使えていない場合はテーブルロックになってしまう
    • トランザクション分離レベルをREAD COMMITTEDに変更している場合は,処理した行に対してのみロックを取得する(ギャップロックが無効化されるため)
  • インデックスをつけすぎない
    • 参照時の性能は向上するが,更新時はオーバヘッドになる
    • 重複するようなインデックスは利用しない
      • key(a,b)があるならkey(a)は削除
    • インデックス内で順序が先の列のみ利用可能
      • インデックスがkey(a,b)だとWHERE b = 5のような場合には使われない
      • ただしMySQL8.0からはスキップスキャンによりインデックスを使用する場合もある
    • 基本的にはカーディナリティが低い列にはインデックスを付けない
      • 例外として,化粧品メーカーで男性向けにメールを打ちたいといった場合は男性はかなり少ないと思われるので,性別のようなカーディナリティが低い列でも有用かも
  • converting indexesはインデックスにアクセスするだけで必要なデータを取り出せるため高速
    • SELECT c FROM t WHERE b = 5の場合key(b,c)がよい
  • インデックス列はそのまま参照しないと効力がない
    • WHERE a*100>=90ではインデックスが使えないので,WHERE a>=0.9と指定する

オプティマイザの制御

  • インデックスヒント
    • 使うインデックスを明示的に指示する
    • EXPLAINpossible_keysに表示されるオプティマイザが使用可能なインデックスとEXPLAINkeyに表示されるオプティマイザが実際に使用したインデックスを見ることでインデックスヒントを使うべきかどうか判断する
    • OUTER JOINの時は使えない
  • STRAIGHT_JOIN
    • JOINの順番を明示的に指示する
    • プログラムで明示的に順番指定できるなら使う必要はない

Q & A

Q1. table_open_cacheはどれくらいにしておけばいい?メモリが足りなくなるのではと怖い.
A. table_open_cachemax_connectionsによって動的に設定されるのでそれで十分なはず.メモリを気にするならmax_connectionsを設定する方がいい.

Q2.クエリキャッシュのヒット率ってどれくらいがいい?
A. mysql8.0からなくなる.キャッシュアウト・インが頻繁になるところがあるので,MySQL8.0ではキャッシュせずInnoDBをもっと速くする方がよいという考えかららしい.

まとめ

  • MySQL8.0のパフォーマンスチューニング勉強会に参加した
  • MySQL8.0に限らず以前のバージョンにも言えるチューニングの話をされた
  • 個人的にはDBA目線のチューニングはまだあまり知らなかったので参考になったし,SQLチューニングも新たな学びがあってよかった