Automatically Distributing Reference Queries to Read Replica in CakePHP4

Transcript

1. 2019/11/9 CakeFest Automatically Distributing Reference Queries to Read Replica in CakePHP4 https://www.lancers.jp/ Yuki Kanazawa
2. 2021/10/8 CakeFest Speaker Biography
3. 2021/10/8 CakeFest Speaker Biography 3 Yuki Kanzawa Lancers, Inc. / Site Reliability Engineer (2013/11 -) Live in Sapporo, Japan Github：yKanazawa Twitter: @yakitori009 Language: C++, Java, PHP, Go Hobby: Shogi(Japanese Chess) Cakefest 2019 Tokyo, Japan
4. 2021/10/8 CakeFest Speaker Biography 4 Live in Sapporo, Japan (Moved in July) Tokyo Sapporo Hokkaido
5. 2021/10/8 CakeFest Hokkaido 5 Biei Niseko Hakodate Sapporo Shiretoko ©Kent Shiraishi / embed: 500px
6. 2021/10/8 CakeFest About Lancers
7. 2021/10/8 CakeFest About Lancers https://www.lancers.jp/ Genre: Crowdsourcing Start: 2008/4 PHP 5.2 → 5.3 → 5.6 → 7.3 CakePHP 1.2 → 1.3 → 2.8 → 2.10 → 4.x In Progress 2017 2019
8. 2019/11/9 CakeFest Consider a smooth upgrade to CakePHP 3 https://www.lancers.jp/ Yuki Kanazawa ２ years ago
9. 2021/10/8 CakeFest PHP7.3 CakePHP2.10 lancers PHP7.3 CakePHP2.10 lancers PHP7.3 CakePHP2.10 lancers 9 Server Configuration Diagram EC2 instance Route 53 ALB Auto Scaling App Service Batch ALB Admin Admin Console Same Repository Aurora Reader Aurora Reader Aurora Writer MySQL5.7
10. 2021/10/8 CakeFest 10 Lancers Servers EC2 instance Route 53 ALB Auto Scaling App Batch ALB Admin PHP7.3 CakePHP4 lancers_admin PHP7.3 CakePHP2.10 lancers PHP7.3 CakePHP4 lancers_batch Aurora Reader Aurora Reader Aurora Writer MySQL5.7 New development with CakePHP4 New development with CakePHP4 Service Admin Console
11. 2021/10/8 CakeFest Switch to Read Replica
12. 2021/10/8 CakeFest 12 Switch to Read Replica App Aurora Reader Aurora Reader Aurora Writer Batch Admin Write Read
13. 2021/10/8 CakeFest 13 Switch to Read Replica App Aurora Reader Aurora Reader Aurora Writer Batch Admin CakePHP4 Using plugin CakePHP4 Using plugin CakePHP2 Original implementation Write Read
14. 2021/10/8 CakeFest CakePHP Master Replica Plugin
15. 2021/10/8 CakeFest CakePHP Master Replica Plugin https://packagist.org/packages/connehito/cakephp-master-replica
16. 2021/10/8 CakeFest Setup CakePHP Master Replica Plugin return [ 'Datasources' => [ 'default' => [ 'className' => Connection::class, 'driver' => Mysql::class, 'host' => '192.168.0.1', 'username' => 'lancers', 'database' => 'lancers', ], ], ]; use Connehito¥CakephpMasterReplica¥Database¥Connection¥MasterR eplicaConnection; return [ 'Datasources' => [ 'default' => [ 'className' => MasterReplicaConnection::class, 'driver' => Mysql::class, 'database' => 'lancers', 'roles' => [ 'master' => [ 'host' => '192.168.0.1', 'username' => 'lancers', ], 'replica' => [ 'host' => '192.168.0.2', 'username' => 'lancers_readonly', ], ], ], ], ]; app.php
17. 2021/10/8 CakeFest Usage cakephp-master-replica plugin class UsersController extends AppController { … public function index( { $this->loadModel('Users'); $this->Users>-getConnection()->switchRole('replica'); $user = $this->Users->find(…; $this->Users>-getConnection()->switchRole('master'); $this->Users->updateAll(… … class UsersTable extends Table { … UsersTable.php UsersController.php Table UsersTable Switch to Replica before find Switch to Master before updateAll
18. 2021/10/8 CakeFest Implementation that automatically switches to read replica
19. 2021/10/8 CakeFest abstract class InheritedTable extends Table { … public function find(string $type = 'all', array $options = []): Query { $this->getConnection()->switchRole('replica'); $result = parent::find($type, $options); return $result; } … public function updateAll($fields, $conditions): int { $this->getConnection()->switchRole('master'); return parent::updateAll($fields, $conditions); } Implementation •Create a class that inherits from the Table class, and override ◦Update method : set to Master ◦Read method : set to Replica Table InheritedTable InheritedTable.php UsersTable
20. 2021/10/8 CakeFest Implementation class UsersTable extends InheritedTable { … class UsersController extends AppController { … public function index( { $this->loadModel('Users'); $user = $this->Users->find(…; $this->Users->updateAll(… … UsersTable.php UsersController.php Table InheritedTable UsersTable Switch to Replica in find Switch to Master in updateAll
21. 2021/10/8 CakeFest Functions to be overridden (CakePHP4.2) Switch to Replica Switch to Master __call (kind of find method) __call (other) exists delete find deleteMany findAll deleteManyOrFail findList deleteOrFail findThreaded query findOrCreate save get saveOrFail saveMany saveManyOrFail
22. 2021/10/8 CakeFest Implementation for magic method abstract class InheritedTable extends Table { … public function __call($method, $args) { if (preg_match('/^find(?:¥w+)?By/', $method) > 0) { $this->getConnection()->switchRole('replica'); return parent::__call($method, $args); } $this->getConnection()->switchRole('master'); return parent::__call($method, $args); } … •set Replica ◦findById, findByUserId, etc •set Master ◦Other Table InheritedTable InheritedTable.php Switch to Replica for findXxx Switch to Master for other
23. 2021/10/8 CakeFest Implementation with transactions
24. 2021/10/8 CakeFest Do not switchRole when executing a transaction abstract class InheritedTable extends Table { private function _switchMaster(): void { $conn = $this->getConnection(); if ($conn->inTransaction()) { return; } $conn->switchRole('master'); } private function _switchReplica(): void { $conn = $this->getConnection(); if ($conn->inTransaction()) { return; } $conn->switchRole('replica'); } … public function updateAll($fields, $conditions): int { $this->_switchMaster(); return parent::updateAll($fields, $conditions); } InheritedTable.php Table InheritedTable No switching during transaction No switching during transaction
25. 2021/10/8 CakeFest Do not switchRole when executing a transaction class UsersController extends AppController { … public function edit( { … $saveProcess = function () use ($faq, $data) { … $this->loadModel('Users'); $user = $this->Users->find(…; … $this->Users->updateAll(… return true; }; $conn = $this->Users->getConnection() $conn->switchRole('master'); $conn->transactional($saveProcess); … UsersController.php Switch to master before transaction Maintain master in transaction Transaction process
26. 2021/10/8 CakeFest Notes on PHPUnit
27. 2021/10/8 CakeFest Notes on PHPUnit … return [ 'Datasources' => [ 'default' => [ 'roles' => [ … 'master' => [ 'host' => '192.168.0.1', 'username' => 'lancers', ], 'replica' => [ 'host' => '192.168.0.2', 'username' => 'lancers_readonly', ], ], ], 'test' => [ 'roles' => [ … 'master' => [ 'host' => '192.168.0.1', 'username' => 'lancers', ], 'replica' => [ 'host' => '192.168.0.1', 'username' => 'lancers', ], ], ], ], ]; app.php Don’t set a read only user and replica host (DDL will not be possible) Recommend read only user
28. 2021/10/8 CakeFest Results
29. 2021/10/8 CakeFest RDS Aurora CPU Usage (Staging) •Run high-load batches Master Replica
30. 2021/10/8 CakeFest RDS Aurora CPU Usage (Production) •Usual load ◦Insufficient distributed processing in CakePHP2 ▪Will be resolved by migrating to CakePHP4 Master Replica
31. 2021/10/8 CakeFest Sample Source
32. 2021/10/8 CakeFest Sample and Explanation Sample Source https://github.com/LancersDevTeam/PHP_versionup/blob/ master/CakePHP4/src/Model/Table/BaseTable.php Explanation (Japanese) https://qiita.com/yKanazawa/items/64af3874448de8840ef4
33. 2021/10/8 CakeFest Thank You!
34. 2021/10/8 CakeFest RDS Aurora Connection Master Replica