2022/08/06 JavaDo n+1問題に気を付けよう

2022/08/06 JavaDo
[Javaでも/それ以外でも] データアクセスについて聞きたい・話したい！

発表資料

n+1問題に気を付けよう
～Python Djangoで起きた悲しいこと～

Transcript

1. 2022/08/06 JavaDo
   データアクセスについて聞きたい・話したい！
   n+1問題に気を付けよう
   ～Python Djangoで起きた悲しいこと～
   村上 將志
   

   View Slide

2. もくじ
   はじめに
   発生した事象
   原因・対策
   DEMO
   この発表の概要、自己紹介などをお話します。
   どんな問題が発生したかをお話します。
   問題が起きた原因、解決の流れを解説します。
   問題の事象の確認、修正の確認について、デモでご覧いただきます。
   2022/08/06 1
   まとめ
   フレームワークのORM機能を使う上での 注意をまとめます。
   

   View Slide

3. はじめに
   2022/08/06 2
   

   View Slide

4. はじめに
   本日の発表は、私が実際に仕事で失敗した経
   験を皆様に共有いたします。
   本資料は、後ほど共有する予定ですので、スラ
   イドの内容をメモする必要はございません。
   2022/08/06 3
   

   View Slide

5. 自己紹介
   2022/08/06 4
   村上 將志（むらかみ まさし）と申します。
   ビットスター株式会社に所属し、Webシステムの主にバックエンド側を開発しています。
   https://bitstar.jp/
   これまで、Python3、Djangoでの開発してきましたが、会社ではPHP、Laravelを使う案件が多く、Laravelを勉
   強しなきゃなぁ、、、という感じです。
   前職では主にJavaを使っていましたが、もう10年以上Javaのコードを書いてません。
   ちゃんとしたシステムやサイトを作らなきゃならないんだから、本当ならJavaで作りたい・・・。（ひとりごと）
   2018年度まで札幌に住んでいて、Java Doのスタッフもしていましたが、2019年の春に大阪に転勤となり、今も
   大阪市に住んでいます。
   

   View Slide

6. おことわり
   2022/08/06 5
   • Java Doの勉強会ですが、仕事で使っているPython Djangoで説明いたします。
   • DjangoでRDB(リレーショナルデータベース)のMariaDB(MySQL互換)のテーブルを扱います。
   • しかし、他の言語・フレームワークでも同じような問題が発生する可能性もありますので、実装の際に留意するキッカ
   ケにして頂ければ幸いです。
   

   View Slide

7. 発生した事象
   2022/08/06 6
   

   View Slide

8. • Python Djangoで開発しており、DjangoのORMの機能で、RDBのテーブルからの検索を実装しました。
   • 関連するテーブルが多く、JOINして検索する必要がありました。
   ※主となるデータの検索は、マスタも含めて、16個のテーブルをjoinする必要があります。
   ※すべてのテーブルの、すべての項目を取得すると、項目数の合計は3桁となります。
   • ページングを実装しており、1ページあたり最大100行表示します。
   ※よくあるサイトだと、10～20行、多くても50行くらい
   • CSVダウンロード機能があります。
   ※条件にヒットしたデータの全件をCSV形式のファイルに出力します。
   ※ページングなどはせず、最大1万件まで出力可能とし、1万件を超えるような条件であれば条件を絞って頂くよう
   メッセージで案内。
   まず、問題のあったWebサイトの要件・仕様をざっくりと
   2022/08/06 7
   

   View Slide

9. 問題発生
   2022/08/06 8
   本番リリースは完了しましたが、本番運用でデータが増えていくと、下記のような問題が発生しました。
   • Webの表示
   • 100行分を表示しようとすると、30秒以上待たされたり、アクセスが多いタイミングなどでは、「504
   Gateway Timeout」になることがありました。
   • 検索ヒット数が20～30行の場合でも、表示に数秒かかり、体感として重いと感じるようになりました。
   • CSVダウンロード
   • 200～300件ヒットする条件を指定すると、1分以上待たされたり、「504 Gateway Timeout」も頻発
   しました。
   

   View Slide

10. 原因・対策
    2022/08/06 9
    

    View Slide

11. サンプルのデータベース定義
    2022/08/06 10
    DEMOのため、簡単なサンプルを作りました。
    （GitHubにアップしてあるので、最後にURLをお知らせします。）
    • 個人情報をDB管理
    • 都道府県、地方（関東地方、近畿地方、など）、性別（男、女）をマスタ管理
    個人情報本体
    性別マスタ
    都道府県マスタ
    地方マスタ
    Model :
    Person
    Model :
    Sex
    Model :
    Prefecture
    Model :
    District
    

    View Slide

12. Python Djangoでの一般的なRDB検索方法
    2022/08/06 11
    person_list: List[Person] = Person.objects.all()[:10]
    person_list: List[Person] = ¥
    Person.objects.filter(prefecture__name__contains = '京都')[:10]
    「Django 検索」とかで探す（ググる）と、一般的な方法として、下記のような方法が見つかります。
    全レコードから、先頭の10件を取得する例：
    都道府県の名前に「京都」を含む（「東京都」「京都府」がヒット）レコードを検索して、先頭10件を取得する例：
    context[‘person_list’] = person_list
    検索した結果をテンプレートに渡す
    {% for person in person_list %}
    
    {{ person.name }}
    {{ person.prefecture.district.name }}
    {{ person.prefecture.name }}
    
    {% endfor %}
    テンプレートでのデータの取得（例：ループを回して各行の都道府県を取得）
    

    View Slide

13. Python DjangoでのRDB検索の期待
    2022/08/06 12
    person_list: List[Person] = Person.objects.filter(prefecture__name__contains = '京都')[:10]
    都道府県の名前に「京都」を含む（「東京都」「京都府」がヒット）レコードを検索して、先頭10件を取得する例：
    関連するテーブルをJOINして、こんな感じで検索してくれると期待していました。
    SELECT
    `p`.*, `ms`.*, `md`.*, `mp`.*
    FROM `person` AS `p`
    INNER JOIN `master_sex` AS `ms` ON `p`.`sex_id` = `ms`.`id`
    INNER JOIN `master_prefecture` AS `mp` ON `p`.`prefecture_id` = `mp`.`id`
    INNER JOIN `master_district` AS `md` ON `mp`.`district_id` = `md`.`id`
    WHERE
    `mp`.`name` LIKE '%京都%'
    ORDER BY
    `p`.`id`
    LIMIT 100
    ;
    ところが、そんなに甘くはありませんでした。
    

    View Slide

14. Djangoで普通に検索したときの実際の動き(n+1問題)
    2022/08/06 13
    id name sex_id prefecture_id
    5 大竹 稟 ・・・ 2 ・・・ 13 ・・・
    17 松島 晃一 ・・・ 1 ・・・ 13 ・・・
    58 池本 梨緒 ・・・ 2 ・・・ 13 ・・・
    71 相田 絵理 ・・・ 2 ・・・ 13 ・・・
    135 塚原 唯菜 ・・・ 2 ・・・ 13 ・・・
    149 神山 千春 ・・・ 2 ・・・ 13 ・・・
    156 池本 日菜子 ・・・ 2 ・・・ 13 ・・・
    246 楠 好男 ・・・ 1 ・・・ 13 ・・・
    402 松原 祐奈 ・・・ 2 ・・・ 13 ・・・
    504 磯崎 実結 ・・・ 2 ・・・ 13 ・・・
    id name district_id
    13 東京都 ・・・ 3
    個人情報を検索→10行取得
    次に、各マスタの名称を、テンプレートで表示等するタイ
    ミングで、各マスタの検索が走る
    ※②～④を個人情報10行について毎回実行
    SELECT `p`.* FROM `person` AS `p`
    INNER JOIN `master_prefecture` AS `mp`
    ON `p`.`prefecture_id` = `mp`.`id`
    WHERE `mp`.`name` LIKE '%京都%'
    ORDER BY `p`.`id`LIMIT 100;
    ② 性別マスタ検索
    SELECT * FROM `master_sex` WHERE `id` = 2;
    ③ 都道府県マスタ検索
    SELECT * FROM `master_prefecture` WHERE `id` = 13;
    ④ 地方マスタ検索
    SELECT * FROM `master_district` WHERE `id` = 3;
    まず、主となる個人情報のテーブルから検索
    結果の取得で、表示で必要のない列まで取得してしまう。
    ※列の多いテーブルでは無駄が多い。
    →メモリ消費の増大
    ①
    person_list: List[Person] = Person.objects.filter(prefecture__name__contains = '京都')[:10]
    都道府県の名前に「京都」を含む（「東京都」「京都府」がヒット）レコードを検索して、先頭10件を取得する例：
    

    View Slide

15. Djangoで普通に検索したときの実際の動き(n+1問題)
    2022/08/06 14
    id name sex_id prefecture_id
    5 大竹 稟 ・・・ 2 ・・・ 13 ・・・
    17 松島 晃一 ・・・ 1 ・・・ 13 ・・・
    58 池本 梨緒 ・・・ 2 ・・・ 13 ・・・
    71 相田 絵理 ・・・ 2 ・・・ 13 ・・・
    135 塚原 唯菜 ・・・ 2 ・・・ 13 ・・・
    149 神山 千春 ・・・ 2 ・・・ 13 ・・・
    156 池本 日菜子 ・・・ 2 ・・・ 13 ・・・
    246 楠 好男 ・・・ 1 ・・・ 13 ・・・
    402 松原 祐奈 ・・・ 2 ・・・ 13 ・・・
    504 磯崎 実結 ・・・ 2 ・・・ 13 ・・・
    id name district_id
    13 東京都 ・・・ 3
    個人情報を検索→10行取得
    次に、各マスタの名称を、テンプレートで表示等するタイ
    ミングで、各マスタの検索が走る
    ※②～④を個人情報10行について毎回実行
    SELECT `p`.* FROM `person` AS `p`
    INNER JOIN `master_prefecture` AS `mp`
    ON `p`.`prefecture_id` = `mp`.`id`
    WHERE `mp`.`name` LIKE '%京都%'
    ORDER BY `p`.`id`LIMIT 100;
    ② 性別マスタ検索
    SELECT * FROM `master_sex` WHERE `id` = 2;
    ③ 都道府県マスタ検索
    SELECT * FROM `master_prefecture` WHERE `id` = 13;
    ④ 地方マスタ検索
    SELECT * FROM `master_district` WHERE `id` = 3;
    まず、主となる個人情報のテーブルから検索
    結果の取得で、表示で必要のない列まで取得してしまう。
    ※列の多いテーブルでは無駄が多い。
    →メモリ消費の増大
    ①
    person_list: List[Person] = Person.objects.filter(prefecture__name__contains = '京都')[:10]
    都道府県の名前に「京都」を含む（「東京都」「京都府」がヒット）レコードを検索して、先頭10件を取得する例：
    100行表示とかで、これだと、
    重くて使いものにならない
    

    View Slide

16. Djangoでの検索をJOINで一気に取ってくる方法はないか？
    2022/08/06 15
    関連するテーブルをJOINして、こんな感じで検索する方法はないか？
    SELECT
    `p`.*, `ms`.*, `md`.*, `mp`.*
    FROM `person` AS `p`
    INNER JOIN `master_sex` AS `ms` ON `p`.`sex_id` = `ms`.`id`
    INNER JOIN `master_prefecture` AS `mp` ON `p`.`prefecture_id` = `mp`.`id`
    INNER JOIN `master_district` AS `md` ON `mp`.`district_id` = `md`.`id`
    WHERE
    `mp`.`name` LIKE '%京都%'
    ORDER BY
    `p`.`id`
    LIMIT 100
    ;
    • JOINするテーブルの参照項目を定義（テーブル名ではなく、参照フィールド）
    • SELECTで取得する項目を定義
    • モデルでfilterする前にselect_relatedメソッドを呼び出す（引数：JOINするテーブルの参照項目）
    • filterの後でvaluesメソッドを呼び出す（引数：取得する項目）
    探してみたら、あった！！
    

    View Slide

17. サンプルのデータベース定義（おさらい）
    2022/08/06 16
    10を超えるテーブルでサンプルを作るのは大変なので、簡単なサンプルを作りました。
    （gitlabにアップしてあるので、最後にURLをお知らせします。）
    • 個人情報をDB管理
    • 都道府県、地方（関東地方、近畿地方、など）、性別（男、女）をマスタ管理
    個人情報本体
    性別マスタ
    都道府県マスタ
    地方マスタ
    Model :
    Person
    Model :
    Sex
    Model :
    Prefecture
    Model :
    District
    

    View Slide

18. JOINするテーブルの参照を定義（テーブル名ではなく、Personから見た参照フィールド）
    select_related_tables = [
    'prefecture',
    'prefecture__district',
    'sex',
    ]
    DjangoでのJOINで検索する方法 （１）
    2022/08/06 17
    

    View Slide

19. SELECTで取得する項目を定義
    select_fields = [
    'id',
    'name',
    'name_kana',
    'sex__name',
    'telephone',
    'telephone_mobile',
    'mail_address',
    'zip_code',
    'prefecture__district__name',
    'prefecture__name',
    'address1',
    'address2',
    'birthday',
    ・・・
    'created_at',
    'updated_at',
    ]
    DjangoでのJOINで検索する方法 （２）
    2022/08/06 18
    

    View Slide

20. person_list: List[Person] = ¥
    Person.objects ¥
    .select_related(*select_related_tables) ¥
    .filter(prefecture__name__contains = '京都') ¥
    .values(*select_fields)[:100]
    select_relatedメソッドの引数で、JOINして検索するテーブルの参照項目を、
    valuesメソッドの引数で、SELECTで取得する項目を、
    それぞれ*argsの形で指定
    • 関連して取得するテーブルをselect_relatedで指定することで、一気にJOINして検索してくれる。
    • 取得する列をvaluesで指定することで、無駄な列を取得しなくて済む。
    ※指定した列名をキーとする辞書型で取得する形となる。
    DjangoでのJOINで検索する方法 （３）
    2022/08/06 19
    

    View Slide

21. person_list: List[Person] = ¥
    Person.objects ¥
    .select_related(*select_related_tables) ¥
    .filter(prefecture__name__contains = '京都') ¥
    .values(*select_fields)[:100]
    select_relatedメソッドの引数で、JOINして検索するテーブルの参照項目を、
    valuesメソッドの引数で、SELECTで取得する項目を、
    それぞれ*argsの形で指定
    • 関連して取得するテーブルをselect_relatedで指定することで、一気にJOINして検索してくれる。
    • 取得する列をvaluesで指定することで、無駄な列を取得しなくて済む。
    ※指定した列名をキーとする辞書型で取得する形となる。
    DjangoでのJOINで検索する方法 （３）
    2022/08/06 20
    

    View Slide

22. DEMO
    2022/08/06 21
    

    View Slide

23. サンプルDEMO
    2022/08/06 22
    • 今回は、Python3、Djangoで、簡単なサンプルを作成しました。
    • サンプルは、私のGitHubに公開します。
    https://github.com/murakami0923/django-nplusone-sample
    • サンプルを動かす際には、LinuxにインストールしたDocker、Docker Compose (v2)が必要です。
    ※私はOracle VM VirtualBoxにUbuntu 22.04のゲストOSを作成し、Dockerをインストールして使用します。
    ※Windowsの場合、WSL2でUbuntuをインストールしても使えるはずです。
    • アプリケーションのコンテナで、アプリケーション実行ユーザーを、ホスト側のグループとユーザーと同じもので作成し、ボ
    リュームを参照するようにしています。
    

    View Slide

24. Docker環境
    2022/08/06 23
    

    View Slide

25. サンプルの準備
    2022/08/06 24
    • GitHubからサンプルを取得します。
    （git clone https://github.com/murakami0923/django-nplusone-sample.git）
    • サンプルのディレクトリに移動します。(cd django-nplusone-sample/)
    • Dockerfileをビルドします。(./docker-build.sh)
    

    View Slide

26. 実行・確認の流れ
    2022/08/06 25
    • まず、Docker Composeで定義したコンテナ起動します。（./start-docker-compose.sh)
    ※必要なボリュームのディレクトリ作成を含みます。
    • しばらく（実行環境にもよりますが、およそ10秒ほど）待ちます。
    • ターミナル等でボリュームの中のクエリログをless等で開いておきます。
    （sudo less -S volumes/mysql/log/mysql.log） ※-S : 折り返さずに表示
    • ブラウザで、n+1問題対応前のページへアクセスします。
    （http://{Dockerホストのホスト名orIPアドレス}:8001/person/001/）
    • クエリログで、各マスタの取得が個別に行われていることを確認します。
    • ブラウザで、n+1問題対応後のページへアクセスします。
    （http://{Dockerホストのホスト名orIPアドレス}:8001/person/002/）
    • クエリログで、マスタも含めてjoinで一括で取得していることを確認します。
    • 確認が終わったら、Docker Composeで定義したコンテナを停止します。（ ./stop-docker-compose.sh ）
    

    View Slide

27. まとめ
    2022/08/06 26
    

    View Slide

28. まとめ
    2022/08/06 27
    • フレームワークの機能を使う場合は、その仕様や制約などにより、今回のような問題が発生する可
    能性があります。
    • 今回はPythonのDjangoを使いましたが、ほかの言語・フレームワークでも、こうした仕様・制約
    で、使う側での工夫・対応が求められるかもしれません。
    • フレームワークのORM機能を使う場合、開発環境で、DBのクエリログの出力を有効化しておき、
    開発の段階で実行されたクエリをこまめに確認し、n+1のような問題が起きていないか確認するこ
    とをおすすめします。
    

    View Slide

29. 補足
    2022/08/06 28
    • システムの開発に携わって20年ほど経ちますが、その中の経験から、RDBからの検索機能でのパ
    フォーマンス問題は、下記のような問題で解決できることが多かったです。
    • クエリ自体の問題（特にグループ化）
    • インデックスが適切に設定されていない
    • n+1問題（今回紹介した内容）
    • その中で、クエリの問題と、インデックス問題では、クエリの実行自体に時間がかかるケースもあ
    ります。（スロークエリ）
    • 本番環境においても、スロークエリを検知するため、スロークエリのログを有効化することもおす
    すめです。
    • クラウドのマネージドのRDBでも、スロークエリのログの設定が可能なケースもあります。
    （例：AWS RDS MySQLの設定）
    

    View Slide

30. ご清聴ありがとうございました
    村上 將志
    Twitter : @masashi0923
    Facebook : masashi0923
    2022/08/06 29
    

    View Slide