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

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

Transcript

1. 1)1Ͱສ݅
   όονճͨ͠ΒࢮͶΔ
   

   View Slide

2. ͨͳ͔ͻͯ͞Δ
   @tanakahisateru
   Pinoco developer
   
   PHPTAL contributor
   
   Firebug translation contributor
   
   Yii framework user
   
   PhpStorm user
   ϑϧελοΫΤϯδχΞ(স)
   

   View Slide

3. 1)1ΠϯλϓϦλɺ
   ىಈ΋࣮ߦ΋଎͍͠ɺ͜Εόο
   νॲཧʹ޲͍ͯΔΜ͡ΌͶ
   

   View Slide

4. ͜͏͍͏ͷΛ΍Ζ͏ͱ͢Δ
   $query = Comment::find()
   ->where(['not', ['deleted' => 1]])
   ->with('user');
   foreach ($query->all() as $model) {
   ...;
   }
   ˞ίʔυ͸:JJͰ͢
   

   View Slide

5. ଈࢮ
   SELECT * FROM `comment` WHERE NOT (`deleted`=1)
   PHP Fatal error: Allowed memory size of 134217728
   bytes exhausted (tried to allocate 4096 bytes)
   in ...
   

   View Slide

6. શ݅ϝϞϦʹ৐ͤΑ͏ͱ
   ͨ͠ͷ͕͍͚ͳ͔ͬͨ
   

   View Slide

7. 8FCͷ1BHJOBUJPOͷཁྖͰ
   ΍ͬͯΈΔ
   ϖʔδ݅͋Δͱͯ͠ʜ
   

   View Slide

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) {
   ...
   }
   }
   

   View Slide

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
   

   View Slide

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
    

    View Slide

11. ສ݅໨΄Ͳͷͱ͜ΖͰ
    ݅͋ͨΓ
    NTˠNT
    

    View Slide

12. ʜ
    εΩϟϯˠແࢹ͞ΕΔ
    ར༻͞ΕΔσʔλ
    ݅ແࢹޙʹ݅࢖͏
    42-จͷධՁ
    

    View Slide

13. ৐Ͱޮ͍ͯ͘Δ΍ͭɺ
    ݅਺͕૿͑Δͱ·͍ͣͳ
    

    View Slide

14. ͞Βʹɺ
    ͍͍ͪͪ42-Λൃߦ͍ͯ͠Δ
    ˠఆظతʹ໰͍߹Θͤෛՙ͕ൃੜ͢Δ
    

    View Slide

15. ͜͏͍͏;͏ʹ͍ͨ͠
    ʜ
    GPSFBDI SPXTBTSPX
    \^
    

    View Slide

16. ΠςϨʔλʔͰߟ͑Δ
    w :JJͰ͸BMM
    ͷ୅ΘΓʹ2VFSZCBUDI
    ͔
    2VFSZFBDI
    Λ࢖͏ɻ
    w Կ͔݅·ͱΊͯऔಘͯ͠ॲཧɺ࣍ͷϧʔϓͰ͸લͷ
    ΛϝϞϦ͔ΒࣺͯΔɻ
    w ˞$BLF1)1͸࠷ॳ͔ΒBMM
    ͕:JJͷ
    2VFSZFBDI
    ʹ͋ͨΓ·͢ɻ
    

    View Slide

17. CBUDI
    ͷ࢖͍ํ
    $query = Comment::find()
    ->where(['not', ['deleted' => 1]])
    ->with('user');
    foreach ($query->batch(1000) as $i => $models) {
    foreach ($models as $model) {
    ...;
    }
    }
    

    View Slide

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
    
    

    View Slide

19. $query = Comment::find()
    ->where(['not', ['deleted' => 1]])
    ->with('user');
    foreach ($query->batch(1000) as $i => $models) {
    foreach ($models as $model) {
    ...;
    }
    } ολʔϯ
    

    View Slide

20. PHP Fatal error: Allowed memory size of 134217728
    bytes exhausted (tried to allocate 4096 bytes)
    in ...
    

    View Slide

21. ͸
    

    View Slide

22. View Slide

23. 1%0.:42-@"553@64&@#6''&3&%@26&3:
    1%04UBUFNFOUͰ͜ͷଐੑΛ536&ʹઃఆ͢Δͱɺ
    .Z42-υϥΠό͸όοϑΝ൛ͷ.Z42-"1*Λ࢖༻
    ͠·͢ɻ
    ॳճͷGFUDI
    ͰGFUDI"MM
    ͱಉ͜͡ͱͯ͠ϝϞϦ
    ʹ৐ͤ·͢Αɻ
    CBUDI
    ͚ͩͰ͸ɺ"3ΛΠϯελϯεԽ͢ΔͨΊͷϝ
    ϞϦ͸અ໿Ͱ͖ͯ΋ɺ1%0಺෦όοϑΝ༻ͷϝϞϦ͸
    અ໿Ͱ͖ͯͳ͔ͬͨ
    

    View Slide

24. ͡Όɺ͜ΕͰ͍͍ͷ
    Yii::$app->db->pdo->setAttribute(
    \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,
    false
    );
    

    View Slide

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
    

    View Slide

26. ඇόοϑΝΫΤϦͱ03.
    w %#઀ଓຊ͋ͨΓ͔ͭ͠Ԡ౴ετϦʔϜΛ࣋ͯͳ
    ͍ɻεςʔτϝϯτ͝ͱͰ͸ͳ͍ɻ ͋ͨΓ·͑Ͱ
    ͢Ͷ
    
    w ඇόοϑΝͳϨίʔυͷॲཧதʹ΋ɺ03.͕ؔ࿈
    ςʔϒϧΛϨΠδʔϑΣον͢ΔՄೳੑ͕͋Δ
    w શମϧʔϓ༻ͷ%#઀ଓͱ௨ৗͷ%#઀ଓΛ෼͚Δ
    

    View Slide

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
    ) {
    

    View Slide

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
    

    View Slide

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
    

    View Slide

30. ·ͱΊ
    w 0''4&5-*.*5͸εΩϟϯίετ͕ߴ͍ͷͰ຤ඌ
    ·ͰḷΔʹ͸ෆ޲͖
    w .Z42-ͰେྔσʔλͷόονॲཧΛ͢Δͱ͖͸
    1%0.:42-@"553@64&@#6''&3&%@26&3:
    ΛGBMTFʹ͢Δ
    w ඇόοϑΝΫΤϦͻͱͭʹ͖ͭɺઐ༻ͷ%#઀ଓΛ
    ͻͱͭ։͘͜ͱ
    

    View Slide

31. ิ଍આ໌
    w :JJͷ"DUJWF3FDPSEΛ࢖͍·͕ͨ͠ɺ&MPRVFOUͳͲଞͷ03.Ͱ΋ಉ
    ͡ߟ͑ํͰΞϓϩʔνͰ͖Δ͸ͣͰ͢ʜͨͿΜ
    w %PDUSJOFʹ͸ΤϯςΟςΟϚωʔδϟʔ͕͋ΔͷͰɺΦϯϝϞϦͷؔ
    ࿈ΤϯςΟςΟ͕ɺঃʑʹϝϞϦΛѹഭ͠ͳ͍͔΋഑ྀ͍ͯͩ͘͠͞ɻ
    ࢼͯ͠ͳ͍
    
    w $BLF1)1͸BMM
    ͱFBDI
    Λ۠ผͤͣɺͭͶʹ݅ͮͭGFUDI
    ͠
    ·͢ɻ$BLFͰ͸ٯʹɺؔ࿈ϨίʔυͷϨΠδʔϩʔυͰ$BOOPU
    FYFDVUFRVFSJFTXIJMFPUIFSVOCV⒎FSFERVFSJFTBSFBDUJWF
    ͕ग़ͳ͍Α͏஫ҙ͍ͯͩ͘͠͞ɻ
    w :JJͷ%#͸͍͟ͱ͍͏ͱ͖ॊೈͰ͍͍͘͢͝Αɻ
    

    View Slide

32. ΊͰͨ͠ΊͰͨ͠
    

    View Slide

33. Ͱ͔͢
    

    View Slide

34. ʮ΋ͬͱ଎͍ͨ͘͠ʯ
    ཉ͕ग़͖ͯ·͢ΑͶ
    

    View Slide

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'],
    }
    ഑ྻͰΑͤ͜
    

    View Slide

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-༻ͷ
    ΫΤϦϏϧμͱͰಉ࣮͡૷Λڞ༗͢Δ
    

    View Slide

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)
    

    View Slide

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Λ࢖͍ͬͯΔ
    

    View Slide

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
    

    View Slide

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
    

    View Slide

41. ϝϞϦ.#ˠ.#
    ଎౓NTˠNT
    

    View Slide

42. ͨͩ͠ʜ
    w ͍͖ͳΓੜͷ42-Λॻ͍ͯύϑΥʔϚϯεΛಘΑ͏
    ͱ͠ͳ͍
    w ͍͖ͳΓΦϒδΣΫτࢦ޲Λࣺͯͨ΄͏͕଎͍ͱ͔
    ݴΘͳ͍
    w 03.ͱ001Ͱਖ਼͘͠ಈ͘Α͏ʹ࡞͔ͬͯΒɺͦͷ
    ಈ࡞Λյ͞ͳ͍Α͏ςετͭͭ͠νϡʔχϯάͯ͠
    ͍͘
    

    View Slide