SQLをアプリケーション層で並列化してみた～Python編~

Transcript

1. SQLを アプリケーション層で 並列化してみた ～Python編～ エムスリー株式会社 エンジニアリンググループ 笹生健
2. 自己紹介 笹生 健（さそう たける） MR君ファミリーの保守・運用エンジニア（チームリーダー） スキルセット Java（それなり） Python（それなり） SQL（変態級） 趣味： 料理, 太極拳 2児の父 主なTV出演歴 NHK Eテレ「すくすく子育て」 所属団体 JAPAN MENSA Global Genius Generation Group (4G)
3. 大学時代 専攻：コンピューターサイエンス スパコンのアルゴリズム研究 つまり並列処理の研究 実際はスパコン作って遊んでた→ 2002年6月に世界47位！ self-madeスパコンでは世界2位！
4. 社会人時代～エムスリーへ 2003年4月：社会人になる 紆余曲折を経る 2013年11月1日：エムスリー入社 なぜか毎日SQLを書くお仕事 SQLが変態的に上手くなる 時折、社内ツール（Python製）をメンテ Java, Rails, Scala, Kotlinの会社で、なぜかPythonistaに
5. 今回のテーマ SQLを Pythonアプリで 並列処理
6. 動機 くっそ重たい変態SQL 速くしたい
7. 変態SQLの例 with login_count_partner as ( select partner_id , count(distinct created_time) as count_total_login , count(distinct case when created_time >= current_date - interval'7 days' then created_time end) as count_login_in_7 from partner_login group by partner_id ), partner_movie_attach as ( select mh.partner_id , max(case when mmh.movie_id is not null then 1 else 0 end) as movie_attach_flg from message_header mh left join movie_message_header mmh on (mmh.message_header_id = mh.id) group by mh.partner_id ), partner_sug_reg_count as ( select a.partner_id , count(distinct d.personal_code) as count_suggest , count(distinct case when r.user_id is not null and r.deleted_time is null then d.personal_code end) as count_register from assignment a join users d on (d.id = a.users_id and d.client_id = 1000) left join m3_user m on (m.personal_code = d.personal_code) left join registration r on (r.user_id = m.user_id and r.client_id = 1000) where exists (select 1 from partner f where f.client_id = 1000 and f.id = a.partner_id) and priority = 1 group by a.partner_id ) select m.message_body_id ,to_char(m.send_time, 'YYYY-MM-DD') as send_date ,(select h.message_body_id from message_header h where h.id = m.parent_id) as parent_body_id ,g1.name as shiten ,g2.name as team ,to_char(m.send_time, 'YYYY-MM-DD') as person_answered_date ,'"'||d.workplace_name||'"' as workplace_name ,m.user_id as user_id ,d.personal_code as personal_person_cd ,d.name as person_name ,g3.name as tanto ,f.name as partner_name ,f.id as partner_id ,coalesce(a.movie_attach_flg, 0) as partner_movie_attach_flg ,coalesce(l.count_total_login, 0) as partner_login_count_total ,coalesce(l.count_login_in_7, 0) as partner_login_count_in_7 ,coalesce(src.count_suggest, 0) as partner_count_suggest_person ,coalesce(src.count_register, 0) as partner_count_register_person ,case when (case when not ( 1=0 OR m.title like '「ありがとう」が押されました%' OR m.title like '「良かったよ」が押さ れました%' OR m.title like '「もう少し詳しく」が押されました%' OR m.title like '「見たよ」が押されました%' OR m.title like '「ありがとう」が押されました%' OR m.title like '「良かったよ」が押されました%' OR m.title like '「もう少し詳しく」 が押されました%' OR m.title like '「見たよ」が押されました%' OR m.title like '「なるほど」が押されました%' OR m.title like '「頼りにしてます」が押されました%' OR m.title like '「ありがとう(K)」が押されました%' OR m.title like '「いいね (K)」が押されました%' OR m.title like '「論文(K)」が押されました%' OR m.title like '「見たよ(K)」が押されました%' OR m.title like '「次回に期待(K)」が押されました%' OR m.title like '「頼りにしてる(K)」が押されました%' ) then '"'|| regexp_replace( regexp_replace(regexp_replace(m.insertion_message, E'<[^>]*>', '', 'g'), E'[ ¥r¥n¥t]+', ' ', 'g'), '引 用本文.+', '') ||'"' end) is not null and not m.title like 'はい・いいえ回答%' then '1' else '0' end as person_text_message_flg ,case when m.title like '「イイね！」が押されました%' then 1 else 0 end as stamp_mat ,case when m.title like '「すごくイイね！」が押されました%' then 1 else 0 end as stamp_good ,case when m.title like '「ぜひ会いたい！」が押されました%' then 1 else 0 end as stamp_visit ,case when m.title like '「見たよ」が押されました%' then 1 else 0 end as stamp_watch ,case when m.title like '「よくわからない」が押されました%' then 1 else 0 end as stamp_pre ,case when m.title like '「残念！」が押されました%' then 1 else 0 end as stamp_for ,case when m.title like '「もう少し詳しく」が押されました%' then 1 else 0 end as stamp_detail ,case when m.title like '「参考になったよ」が押されました%' then 1 else 0 end as stamp_helpful ,case when m.title like '「試してみようかな」が押されました%' then 1 else 0 end as stamp_try ,case when m.title like '「試してみたよ」が押されました%' then 1 else 0 end as stamp_new ,case when m.title like 'はい・いいえ回答%' then '1' else '0' end as yes_no ,case when m.title like '%が押されました%' then 1 else 0 end as stamp_all ,to_char(res.send_time, 'YYYY-MM-DD') as reply_date from partner f left join ( select msg.user_id , msg.personal_code , msg.message_body_id , msg.send_time , msg.parent_id , msg.title from message msg where msg.client_id = 1000 and msg.message_type = 'USER_TO_PARTNER' and not exists (select 1 from test_users t where msg.user_id = t.user_id) and msg.send_time is not null and msg.send_time < current_date ) m on m.partner_id = f.id left join m3_user m3 on m.user_id = m3.user_id left join users d on m3.personal_code = d.personal_code and d.client_id = 1000 left join login_count_partner l on l.partner_id = f.id left join partner_movie_attach a on (a.partner_id = f.id) left join partner_sug_reg_count src on (src.partner_id = f.id) left join message res on m.message_header_id = res.parent_id left join partner_group g1 on f.group1 = g1.id left join partner_group g2 on f.group2 = g2.id left join partner_group g3 on f.group3 = g3.id where f.client_id = 1000
8. 変態SQLが生まれるワケ 会員 ID サービスA利用回数 サービスB利用回数 ・・・ 合計 過去 1ヶ月 過去 3ヶ月 ・・・ 合計 過去 1ヶ月 過去 3ヶ月 ・・・ 原因：営業用提案・報告資料のデータ抽出業務 会員毎のサービス利用状況を知りたいらしい サービスたくさん 集計条件たくさん ＆ コロコロ変わる
9. 変態SQLの並列化戦略 会員 ID サービスA利用回数 サービスB利用回数 ・・・ 合計 過去 1ヶ月 過去 3ヶ月 ・・・ 合計 過去 1ヶ月 過去 3ヶ月 ・・・ 複数のサブクエリに分割→サブクエリ実行を並列化 select distinct user_id from users where client_id = 1000; select user_id , count(distinct user_id) as visit_all , count(distinct case accessed_time >= now() – interval 1 month then user_id end) as visit_within_1month , count(distinct case accessed_time >= now() – interval 3 month then user_id end) as visit_within_3months ・・・ from service_A_activities where client_id = 1000 group by user_id; select user_id , count(distinct user_id) as visit_all , count(distinct case accessed_time >= now() – interval 1 month then user_id end) as visit_within_1month , count(distinct case accessed_time >= now() – interval 3 month then user_id end) as visit_within_3months ・・・ from service_B_activities where client_id = 1000 group by user_id; select user_id ・・・ from ・・・ group by user_id; 最後にjoin
10. Python + Pandasで並列処理 Pandas Python向けのデータ解析処理用ライブラリ http://pandas.pydata.org/ 表形式データをオンメモリで高速処理 join, filter, groupingも速い 多様なフォーマットとのデータ読み書きが可能 CSV, テキストファイル, Excel, SQLデータベース
11. Pandasでのサブクエリ実行・join SQLの実行 pandas.read_sql left outer join pandas.merge
12. Pythonでの並列処理 マルチスレッド、マルチプロセスの2方式 対応する標準モジュール threading multiprocessing どっちがイイの？ multiprocessingの方が良さそう threadingは1スレッドしか実行できない場面が多い • GIL（グローバルインタプリタロック）の取得待ちが原因 multiprocessingの方がシンプルに記述できる感じ
13. threadingの使い方 threading.Threadのサブクラスを作る ※joinで処理結果を返すようオーバーライド 並列処理したいfunctionと引数をサブクラスに渡す ※複数の引数はタプル渡し Thread.joinで結果取得してreduce 実行するfunctionの引数を タプル渡しするようrunをオーバーライド
14. multiprocessingの使い方 各プロセスで実行するfunctionの引数を 予めリスト化 multiprocessing.pool.Poolクラスで ワーカープロセスのプールを生成 ※最大プロセス数を指定 starmapでジョブを並列実行し、 結果をリストで取得
15. 試してみた 3パターンで変態SQLを実行 1. 並列化なし 2. マルチスレッドで並列化（threading使用） 3. マルチプロセスで並列化（multiprocessing使用） 変態SQLの構成 7個のサブクエリをleft join
16. 検証方法 測定諸元 アプリサーバー Python 3.5.2 pandas 0.21.0 DBサーバー Oracle 12c 測定方法 3パターンを5回ずつ計測 最速値を比較 データ量がリッチな本番環境（＝ノイズが多い環境）を使って検証しちゃったので Oracleのキャッシュが効くのはご愛嬌
17. 検証結果 41.95 16.47 16.10 並列化なし マルチスレッド マルチプロセス 実 行 時 間 [ 秒 ] マルチプロセス最速（予想通り） マルチスレッドも悪くない ほぼDBサーバー待ち（I/O待ち）のアプリ うまくマッチした
18. 今後の課題 Pythonのバージョン変えてみる v3.2からGILの仕組み・性能が変わっている 他の並列処理用ライブラリを試す Joblib https://pythonhosted.org/joblib/ マルチプロセス用ライブラリ multiprocessingより機能が豊富らしい 性能が若干劣るという噂
19. おしまい ご清聴ありがとうございました！！