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. 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
  2. 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
  3. 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
  4. 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
  5. 2021/10/8 CakeFest 12 Switch to Read Replica App Aurora Reader

    Aurora Reader Aurora Writer Batch Admin Write Read
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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