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
   

   View Slide

2. 2021/10/8 CakeFest
   Speaker Biography
   

   View Slide

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
   

   View Slide

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

   View Slide

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

   View Slide

6. 2021/10/8 CakeFest
   About Lancers
   

   View Slide

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
   

   View Slide

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

   View Slide

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
   

   View Slide

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
    

    View Slide

11. 2021/10/8 CakeFest
    Switch to Read Replica
    

    View Slide

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

    View Slide

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
    

    View Slide

14. 2021/10/8 CakeFest
    CakePHP Master Replica
    Plugin
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

23. 2021/10/8 CakeFest
    Implementation with transactions
    

    View Slide

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
    

    View Slide

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
    

    View Slide

26. 2021/10/8 CakeFest
    Notes on PHPUnit
    

    View Slide

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
    

    View Slide

28. 2021/10/8 CakeFest
    Results
    

    View Slide

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

    View Slide

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
    

    View Slide

31. 2021/10/8 CakeFest
    Sample Source
    

    View Slide

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
    

    View Slide

33. 2021/10/8 CakeFest
    Thank You!
    

    View Slide

34. 2021/10/8 CakeFest
    RDS Aurora Connection
    Master
    Replica
    

    View Slide