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