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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

$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

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

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

͸

No content

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

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

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

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

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

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

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

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

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

ΊͰͨ͠ΊͰͨ͠

Ͱ͔͢

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

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

͋͘·Ͱಈ͍ͨίʔυ͕جຊ $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-༻ͷ ΫΤϦϏϧμͱͰಉ࣮͡૷Λڞ༗͢Δ

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)

ྫΦϒδΣΫτࢦ޲Λ࢖Θ ͳ͍ ೔࣌ͷϑΥʔϚοτ 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Λ࢖͍ͬͯΔ

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

#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

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

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