Save 37% off PRO during our Black Friday Sale! »

Automatically Distributing Reference Queries to Read Replica in CakePHP4

Automatically Distributing Reference Queries to Read Replica in CakePHP4

652359244199b2b5108a5e09c027e0da?s=128

Kanazawa Yuki

October 08, 2021
Tweet

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 2 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