Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Automatically Distributing Reference Queries to Read Replica in CakePHP4

Automatically Distributing Reference Queries to Read Replica in CakePHP4

Kanazawa Yuki

October 08, 2021
Tweet

More Decks by Kanazawa Yuki

Other Decks in Programming

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