Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Takeru Saso
November 01, 2017

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

Takeru Saso

November 01, 2017
Tweet

Other Decks in Programming

Transcript

  1. 自己紹介 笹生 健(さそう たける) MR君ファミリーの保守・運用エンジニア(チームリーダー) スキルセット Java(それなり) Python(それなり) SQL(変態級) 趣味:

    料理, 太極拳 2児の父 主なTV出演歴 NHK Eテレ「すくすく子育て」 所属団体 JAPAN MENSA Global Genius Generation Group (4G)
  2. 変態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
  3. 変態SQLが生まれるワケ 会員 ID サービスA利用回数 サービスB利用回数 ・・・ 合計 過去 1ヶ月 過去

    3ヶ月 ・・・ 合計 過去 1ヶ月 過去 3ヶ月 ・・・ 原因:営業用提案・報告資料のデータ抽出業務 会員毎のサービス利用状況を知りたいらしい サービスたくさん 集計条件たくさん & コロコロ変わる
  4. 変態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
  5. 検証方法 測定諸元 アプリサーバー Python 3.5.2 pandas 0.21.0 DBサーバー Oracle 12c

    測定方法 3パターンを5回ずつ計測 最速値を比較 データ量がリッチな本番環境(=ノイズが多い環境)を使って検証しちゃったので Oracleのキャッシュが効くのはご愛嬌
  6. 検証結果 41.95 16.47 16.10 並列化なし マルチスレッド マルチプロセス 実 行 時

    間 [ 秒 ] マルチプロセス最速(予想通り) マルチスレッドも悪くない ほぼDBサーバー待ち(I/O待ち)のアプリ うまくマッチした