PHPで100万件バッチ回したら死ねる

第31回関西PHP勉強会の発表

Transcript

1. 1)1Ͱສ݅
   όονճͨ͠ΒࢮͶΔ
2. ͨͳ͔ͻͯ͞Δ
   @tanakahisateru Pinoco developer PHPTAL contributor Firebug translation contributor Yii framework user PhpStorm user ϑϧελοΫΤϯδχΞ(স)
3. 1)1ΠϯλϓϦλɺ
   ىಈ΋࣮ߦ΋଎͍͠ɺ͜Εόο νॲཧʹ޲͍ͯΔΜ͡ΌͶ
4. ͜͏͍͏ͷΛ΍Ζ͏ͱ͢Δ $query = Comment::find() ->where(['not', ['deleted' => 1]]) ->with('user'); foreach ($query->all() as $model) { ...; } ˞ίʔυ͸:JJͰ͢
5. ଈࢮ SELECT * FROM `comment` WHERE NOT (`deleted`=1) PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in ...
6. શ݅ϝϞϦʹ৐ͤΑ͏ͱ ͨ͠ͷ͕͍͚ͳ͔ͬͨ
7. 8FCͷ1BHJOBUJPOͷཁྖͰ ΍ͬͯΈΔ
   ϖʔδ
   ݅͋Δͱͯ͠ʜ
8. $query = Comment::find() ->where(['not', ['deleted' => 1]]) ->with('user'); $count = $query->count(); for ($i = 0; $i <= $count / 1000; $i++) { $models = $query ->offset($i * 1000) ->limit(1000) ->all(); foreach ($models as $model) { ... } }
9. SELECT COUNT(*) FROM `comment` WHERE NOT (`deleted`=1) SELECT * FROM `comment` WHERE NOT (`deleted`=1) LIMIT 1000 SELECT * FROM `user` WHERE `id` IN (6, 5, 13, 14, 2, 16, 4, 28, 26, 33, 34, 40, 43, 1, 7, 50, 54, 55, 15, 67, 68, 66, 79) #0000 12.3809MB 302.3601ms SELECT * FROM `comment` WHERE NOT (`deleted`=1) LIMIT 1000 OFFSET 1000 SELECT * FROM `user` WHERE `id` IN (66, 26, 82, 40, 83, 87, 13, 33, 14, 50, 1, 54, 95, 92, 7, 25, 37, 112, 5, 28, 115, 32) #0001 19.7923MB 299.3069ms SELECT * FROM `comment` WHERE NOT (`deleted`=1) LIMIT 1000 OFFSET 2000 SELECT * FROM `user` WHERE `id` IN (92, 113, 5, 34, 122, 54, 125, 83, 13, 1, 66) #0002 19.7417MB 308.3329ms SELECT * FROM `comment` WHERE NOT (`deleted`=1) LIMIT 1000 OFFSET 3000 SELECT * FROM `user` WHERE `id`=13 #0003 19.5498MB 317.9131ms
10. SELECT * FROM `comment` WHERE NOT (`deleted`=1) LIMIT 1000 OFFSET 310000 SELECT * FROM `user` WHERE `id` IN (707, 310, 177, 588, 721, 315, 249, 536, 705, 311, 282, 378, 626, 220, 196, 716, 720, 725, 732, 208, 753, 511, 504, 329, 518, 723, 404, 729, 335, 751, 155, 168, 402, 519, 722, 691, 726, 171) #0310 19.8985MB 950.8541ms SELECT * FROM `comment` WHERE NOT (`deleted`=1) LIMIT 1000 OFFSET 311000 SELECT * FROM `user` WHERE `id` IN (282, 723, 378, 249, 311, 707, 310, 220, 519, 253, 722, 504, 177, 196, 335, 721, 748, 705, 24, 404, 315, 516, 751, 729, 753, 725, 730, 171, 732, 759, 168, 194, 235, 588, 155, 213, 283, 329, 511, 222, 502, 402, 208, 742, 510, 195) #0311 19.9424MB 957.8071ms SELECT * FROM `comment` WHERE NOT (`deleted`=1) LIMIT 1000 OFFSET 312000 SELECT * FROM `user` WHERE `id` IN (751, 220, 282, 378, 742, 283, 707, 168, 519, 335, 310, 725, 311, 722, 504, 721, 196, 626, 253, 732, 705, 184, 511, 222, 194, 155, 518, 753, 759, 329, 720, 177, 536, 404, 315, 526, 480) #0312 19.9184MB 969.8141ms SELECT * FROM `comment` WHERE NOT (`deleted`=1) LIMIT 1000 OFFSET 313000 SELECT * FROM `user` WHERE `id` IN (721, 480, 504, 311, 626, 378, 518, 310, 253, 402, 720, 588, 753, 725, 283, 177, 723, 329, 730, 235, 196, 722, 335, 404, 249, 222, 171, 155, 707, 194, 282, 751, 519, 691, 220, 735, 748, 732, 705, 168, 729) #0313 19.8984MB 978.6940ms
11. ສ݅໨΄Ͳͷͱ͜ΖͰ
    ݅͋ͨΓ
    NT
    ˠ
    NT
12. ʜ εΩϟϯˠແࢹ͞ΕΔ ར༻͞ΕΔσʔλ  ݅ແࢹޙʹ݅࢖͏ 42-จͷධՁ
13. ৐Ͱޮ͍ͯ͘Δ΍ͭɺ ݅਺͕૿͑Δͱ·͍ͣͳ
14. ͞Βʹɺ
    ͍͍ͪͪ42-Λൃߦ͍ͯ͠Δ
    ˠ
    ఆظతʹ໰͍߹Θͤෛՙ͕ൃੜ͢Δ
15. ͜͏͍͏;͏ʹ͍ͨ͠ ʜ GPSFBDI SPXT
    BT
    SPX \
    ^
16. ΠςϨʔλʔͰߟ͑Δ w :JJͰ͸
    BMM
    ͷ୅ΘΓʹ
    2VFSZCBUDI
    ͔
    2VFSZFBDI
    Λ࢖͏ɻ
    w Կ͔݅·ͱΊͯऔಘͯ͠ॲཧɺ࣍ͷϧʔϓͰ͸લͷ ΛϝϞϦ͔ΒࣺͯΔɻ
    w ˞
    $BLF1)1͸࠷ॳ͔Β
    BMM
    ͕
    :JJ
    ͷ
    2VFSZFBDI
    ʹ͋ͨΓ·͢ɻ
17. CBUDI ͷ࢖͍ํ $query = Comment::find() ->where(['not', ['deleted' => 1]]) ->with('user'); foreach ($query->batch(1000) as $i => $models) { foreach ($models as $model) { ...; } }
18. $rows = []; $count = 0; while ($count++ < $this->batchSize && ($row = $this->_dataReader->read())) { $rows[] = $row; } public function read() { return $this->_statement->fetch(); } 1%04UBUFNFOUGFUDI
    Ͱ݅͝ͱʹ 1%04UBUFNFOUGFUDI"MM
19. $query = Comment::find() ->where(['not', ['deleted' => 1]]) ->with('user'); foreach ($query->batch(1000) as $i => $models) { foreach ($models as $model) { ...; } } ολʔϯ
20. PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in ...
21. ͸
23. 1%0.:42-@"553@64&@#6''&3&%@26&3: 1%04UBUFNFOU
    Ͱ͜ͷଐੑΛ
    536&
    ʹઃఆ͢Δͱɺ
    .Z42-
    υϥΠό͸όοϑΝ൛ͷ
    .Z42-
    "1*
    Λ࢖༻ ͠·͢ɻ 
    ॳճͷ
    GFUDI
    Ͱ
    GFUDI"MM
    ͱಉ͜͡ͱͯ͠ϝϞϦ ʹ৐ͤ·͢Αɻ CBUDI
    ͚ͩͰ͸ɺ"3ΛΠϯελϯεԽ͢ΔͨΊͷϝ ϞϦ͸અ໿Ͱ͖ͯ΋ɺ1%0಺෦όοϑΝ༻ͷϝϞϦ͸ અ໿Ͱ͖ͯͳ͔ͬͨ
24. ͡Όɺ͜ΕͰ͍͍ͷ Yii::$app->db->pdo->setAttribute( \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false );
25. SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. The SQL being executed was: SELECT * FROM `user` WHERE
26. ඇόοϑΝΫΤϦͱ03. w %#઀ଓຊ͋ͨΓ͔ͭ͠Ԡ౴ετϦʔϜΛ࣋ͯͳ ͍ɻεςʔτϝϯτ͝ͱͰ͸ͳ͍ɻ ͋ͨΓ·͑Ͱ ͢Ͷ
    w ඇόοϑΝͳϨίʔυͷॲཧதʹ΋ɺ03.͕ؔ࿈ ςʔϒϧΛϨΠδʔϑΣον͢ΔՄೳੑ͕͋Δ
    w શମϧʔϓ༻ͷ%#઀ଓͱ௨ৗͷ%#઀ଓΛ෼͚Δ
27. $original = Yii::$app->db; $unbufferedDb = Yii::createObject([ 'class' => Connection::class, 'dsn' => $original->dsn, 'username' => $original->username, 'password' => $original->password, 'charset' => $original->charset, 'enableSchemaCache' => $original->enableSchemaCache, 'attributes' => [ \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false, ], ]); foreach ( $query->batch(1000, $unbufferedDb) as $i => $models ) {
28. SELECT * FROM `comment` WHERE NOT (`deleted`=1) SELECT * FROM `user` WHERE `id` IN (6, 5, 13, 14, 2, 16, 4, 28, 26, 33, 34, 40, 43, 1, 7, 50, 54, 55, 15, 67, 68, 66, 79) #0000 11.7377MB 298.9209ms SELECT * FROM `user` WHERE `id` IN (66, 26, 82, 40, 83, 87, 13, 33, 14, 50, 1, 54, 95, 92, 7, 25, 37, 112, 5, 28, 115, 32) #0001 19.1115MB 287.5609ms SELECT * FROM `user` WHERE `id` IN (92, 113, 5, 34, 122, 54, 125, 83, 13, 1, 66) #0002 19.0610MB 299.1409ms SELECT * FROM `user` WHERE `id`=13 #0003 18.8693MB 293.2000ms
29. SELECT * FROM `user` WHERE `id` IN (705, 626, 721, 282, 378, 329, 162, 588, 536, 516, 720, 707, 519, 311, 310, 177, 315, 249, 220, 196, 716, 725, 732, 208, 753, 511, 504, 518, 723, 404, 729, 335, 751, 155, 168, 402, 722, 691, 726) #0310 26.6485MB 306.1929ms SELECT * FROM `user` WHERE `id` IN (171, 249, 707, 282, 310, 311, 378, 220, 208, 315, 723, 519, 253, 722, 504, 177, 196, 335, 721, 748, 705, 24, 404, 516, 751, 729, 753, 725, 730, 732, 759, 168, 194, 235, 588, 155, 213, 283, 329, 511, 222, 502, 402, 742, 510, 195) #0311 19.2913MB 338.2702ms SELECT * FROM `user` WHERE `id` IN (283, 168, 222, 171, 751, 335, 282, 220, 707, 378, 742, 311, 249, 753, 519, 310, 725, 722, 504, 721, 196, 626, 253, 732, 705, 184, 511, 194, 155, 518, 759, 329, 720, 177, 536, 404, 315, 526) #0312 19.2691MB 307.0250ms SELECT * FROM `user` WHERE `id` IN (732, 220, 310, 707, 721, 480, 536, 311, 626, 196, 378, 504, 335, 518, 253, 402, 720, 588, 753, 725, 283, 177, 723, 329, 730, 235, 722, 404, 249, 222, 171, 155, 194, 282, 751, 519, 691, 735, 748, 705, 168) #0313 19.2462MB 312.9332ms
30. ·ͱΊ w 0''4&5-*.*5
    ͸εΩϟϯίετ͕ߴ͍ͷͰ຤ඌ ·ͰḷΔʹ͸ෆ޲͖
    w .Z42-ͰେྔσʔλͷόονॲཧΛ͢Δͱ͖͸
    1%0.:42-@"553@64&@#6''&3&%@26&3:
    Λ
    GBMTF
    ʹ͢Δ
    w ඇόοϑΝΫΤϦͻͱͭʹ͖ͭɺઐ༻ͷ%#઀ଓΛ ͻͱͭ։͘͜ͱ
31. ิ଍આ໌ w :JJͷ"DUJWF3FDPSEΛ࢖͍·͕ͨ͠ɺ&MPRVFOUͳͲଞͷ03.Ͱ΋ಉ ͡ߟ͑ํͰΞϓϩʔνͰ͖Δ͸ͣͰ͢ʜͨͿΜ
    w %PDUSJOFʹ͸ΤϯςΟςΟϚωʔδϟʔ͕͋ΔͷͰɺΦϯϝϞϦͷؔ ࿈ΤϯςΟςΟ͕ɺঃʑʹϝϞϦΛѹഭ͠ͳ͍͔΋഑ྀ͍ͯͩ͘͠͞ɻ ࢼͯ͠ͳ͍
    w $BLF1)1͸
    BMM
    ͱ
    FBDI
    Λ۠ผͤͣɺͭͶʹ݅ͮͭ
    GFUDI
    ͠ ·͢ɻ$BLFͰ͸ٯʹɺؔ࿈ϨίʔυͷϨΠδʔϩʔυͰ
    $BOOPU
    FYFDVUF
    RVFSJFT
    XIJMF
    PUIFS
    VOCV⒎FSFE
    RVFSJFT
    BSF
    BDUJWF
    ͕ग़ͳ͍Α͏஫ҙ͍ͯͩ͘͠͞ɻ
    w :JJͷ%#͸͍͟ͱ͍͏ͱ͖ॊೈͰ͍͍͘͢͝Αɻ
32. ΊͰͨ͠ΊͰͨ͠
33. Ͱ͔͢
34. ʮ΋ͬͱ଎͍ͨ͘͠ʯ ཉ͕ग़͖ͯ·͢ΑͶ
35. ྫ
    "DUJWF3FDPSEΛ࢖Θͳ͍ $query = Comment::find() ->where(['not', ['deleted' => 1]]) ->joinWith('user', false) ->select([ Comment::tableName() . '.created_at', Comment::tableName() . '.track_info_cookie', User::tableName() . '.email', ]) ->asArray(); foreach ($query->batch(1000, $db) as $i => $data) { foreach ($data as $row) { ... $row['email'], } ഑ྻͰΑͤ͜
36. ͋͘·Ͱಈ͍ͨίʔυ͕جຊ $query = Comment::find() ->where(['not', ['deleted' => 1]]) ->with('user'); $query = Comment::find() ->where(['not', ['deleted' => 1]]) ->joinWith('user', false) ->select([ Comment::tableName() . '.created_at', Comment::tableName() . '.track_info_cookie', User::tableName() . '.email', ]) ->asArray(); :JJ͸"3༻ͷΫΤϦϏϧμͱੜ42-༻ͷ
    ΫΤϦϏϧμͱͰಉ࣮͡૷Λڞ༗͢Δ
37. SELECT `comment`.created_at, `comment`.track_info_cookie, `user`.email FROM `comment` LEFT JOIN `user` ON `comment`.`user_id` = `user`.`id` WHERE NOT (`deleted`=1) SELECT * FROM `comment` WHERE NOT (`deleted`=1) SELECT * FROM `user` WHERE `id` IN (6, 5, 13, 14, 2, 16, 4, 28, 26, 33, 34, 40, 43, 1, 7, 50, 54, 55, 15, 67, 68, 66, 79)
38. ྫ
    ΦϒδΣΫτࢦ޲Λ࢖Θ ͳ͍ ೔࣌ͷϑΥʔϚοτ date_default_timezone_set('Asia/Tokyo'); foreach ($query->batch(1000, $db) as $i => $data) { foreach ($data as $row) { ... date('Y-m-d H:i:s', $row['created_at']), } Yii::$app->formatter->asDatetime($model->created_at), %BUF5JNFͱ*OUM%BUF'PSNBUUFSΛ࢖͍ͬͯΔ
39. SELECT `comment`.created_at, `comment`.track_info_cookie, `user`.email FROM `comment` LEFT JOIN `user` ON `comment`.`user_id` = `user`.`id` WHERE NOT (`deleted`=1) #0000 4.0045MB 125.2842ms #0001 3.9951MB 65.9080ms #0002 3.9945MB 85.3901ms #0003 3.9912MB 70.5140ms #0004 3.9912MB 64.1000ms #0005 3.9914MB 65.2461ms #0006 3.9913MB 63.3559ms #0007 3.9915MB 68.8491ms #0008 3.9971MB 65.9039ms #0009 3.9954MB 64.3489ms #0010 3.9939MB 62.7449ms
40. #0310 4.0183MB 63.7751ms #0311 4.0257MB 66.2868ms #0312 4.0243MB 65.1290ms #0313 4.0189MB 61.5571ms #0314 4.0192MB 65.6259ms #0315 4.0259MB 63.7259ms #0316 4.0192MB 66.6759ms #0317 4.0264MB 66.0169ms #0318 4.0257MB 62.4452ms #0319 4.0223MB 62.3848ms #0320 4.0236MB 66.5529ms #0321 4.0274MB 62.3240ms #0322 4.0225MB 61.0139ms #0323 4.0230MB 65.1271ms #0324 4.0219MB 61.9309ms #0325 4.0188MB 63.7441ms
41. ϝϞϦ
    .#ˠ.#
    ଎౓
    NTˠNT
    
42. ͨͩ͠ʜ w ͍͖ͳΓੜͷ42-Λॻ͍ͯύϑΥʔϚϯεΛಘΑ͏ ͱ͠ͳ͍
    w ͍͖ͳΓΦϒδΣΫτࢦ޲Λࣺͯͨ΄͏͕଎͍ͱ͔ ݴΘͳ͍
    w 03.ͱ001Ͱਖ਼͘͠ಈ͘Α͏ʹ࡞͔ͬͯΒɺͦͷ ಈ࡞Λյ͞ͳ͍Α͏ςετͭͭ͠νϡʔχϯάͯ͠ ͍͘