2019/11/9 CakeFest Automatically Distributing Reference Queries to Read Replica in CakePHP4 https://www.lancers.jp/ Yuki Kanazawa

2021/10/8 CakeFest Speaker Biography

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

2021/10/8 CakeFest Speaker Biography 4 Live in Sapporo, Japan (Moved in July) Tokyo Sapporo Hokkaido

2021/10/8 CakeFest Hokkaido 5 Biei Niseko Hakodate Sapporo Shiretoko ©Kent Shiraishi / embed: 500px

2021/10/8 CakeFest About Lancers

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

2019/11/9 CakeFest Consider a smooth upgrade to CakePHP 3 https://www.lancers.jp/ Yuki Kanazawa ２ years ago

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

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

2021/10/8 CakeFest Switch to Read Replica

2021/10/8 CakeFest 12 Switch to Read Replica App Aurora Reader Aurora Reader Aurora Writer Batch Admin Write Read

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

2021/10/8 CakeFest CakePHP Master Replica Plugin

2021/10/8 CakeFest CakePHP Master Replica Plugin https://packagist.org/packages/connehito/cakephp-master-replica

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

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

2021/10/8 CakeFest Implementation that automatically switches to read replica

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

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

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

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

2021/10/8 CakeFest Implementation with transactions

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

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

2021/10/8 CakeFest Notes on PHPUnit

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

2021/10/8 CakeFest Results

2021/10/8 CakeFest RDS Aurora CPU Usage (Staging) ●Run high-load batches Master Replica

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

2021/10/8 CakeFest Sample Source

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

2021/10/8 CakeFest Thank You!

2021/10/8 CakeFest RDS Aurora Connection Master Replica