zero1/magento2-splitdb

1.0.5 2023-08-22 11:22 UTC

Requires

• magento/framework: ^100.1|^101.0|^102.0|^103.0
• zero1/base: ^1.0.0

Suggests

• mdoq/module-connector: Improves DevOps for Magento2

GPL-3.0-or-later

• database
• aws
• writer
• magento
• reader
• magento 2
• Database Writer

README

Ability to split database traffic to 1 or more readers. Allowing you to take advantage of an AWS reader endpoint for example.

Installation

These steps are intended to be carried out in a staging/development environment. If you don't have, look at MDOQ which can provide multiple development environments quickly and cheaply.

1. composer require zero1/magento2-splitdb
2. php bin/magento module:enable Zero1_SplitDb
3. php bin/magento setup:upgrade
4. php bin/magento deploy:mode:set production

Configuration

To use separate endpoints for reading and writing to the database. You need to configure the endpoints in app/etc/env.php

Before

'db' => [
    'table_prefix' => '',
    'connection' => [
        'default' => [
            'host' => '[DB_HOST]',
            'dbname' => '[DB_NAME]',
            'username' => '[DB_USERNAME]',
            'password' => '[DB_PASSWORD]',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
            'active' => '1',
        ]
    ]
],

After

'db' => [
    'table_prefix' => '',
    'connection' => [
        'default' => [
            'host' => '[DB_HOST]',
            'dbname' => '[DB_NAME]',
            'username' => '[DB_USERNAME]',
            'password' => '[DB_PASSWORD]',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
            'active' => '1',
            'slaves' => [
                [
                    'host' => '[DB_READER_1_HOST]',
                    'username' => '[DB_READER_1_USERNAME]',
                    'password' => '[DB_READER_1_PASSWORD]',
                ],
                [
                    'host' => '[DB_READER_2_HOST]',
                    'username' => '[DB_READER_2_USERNAME]',
                    'password' => '[DB_READER_2_PASSWORD]',
                ]
            ]
        ]
    ]
],

• slaves: you can configure as many as you want. (Though with AWS you would only need to specify the single reader endpoint). The configuration for each slave is merged over the base config. So each slave will inherit all config values not defined. Each request will be locked to a single reader. (This is to stop multiple connections being opened)
• excluded_areas: you can also enforce the use of the writer endpoint for specific urls. By default this will be '/checkout' and '/customer'. If you don't want enforce for any areas supply an empty area

  'db' => [
      'table_prefix' => '',
      'connection' => [
          'default' => [
              'host' => '[DB_HOST]',
              'dbname' => '[DB_NAME]',
              'username' => '[DB_USERNAME]',
              'password' => '[DB_PASSWORD]',
              'model' => 'mysql4',
              'engine' => 'innodb',
              'initStatements' => 'SET NAMES utf8;',
              'active' => '1',
              'excluded_areas' => [],
              'slaves' => [
                  [
                      'host' => '[DB_READER_1_HOST]',
                      'username' => '[DB_READER_1_USERNAME]',
                      'password' => '[DB_READER_1_PASSWORD]',
                  ],
                  [
                      'host' => '[DB_READER_2_HOST]',
                      'username' => '[DB_READER_2_USERNAME]',
                      'password' => '[DB_READER_2_PASSWORD]',
                  ]
              ]
          ]
      ]
  ],

  If you want to enforce different or more areas, supply them in an array

  'db' => [
      'table_prefix' => '',
      'connection' => [
          'default' => [
              'host' => '[DB_HOST]',
              'dbname' => '[DB_NAME]',
              'username' => '[DB_USERNAME]',
              'password' => '[DB_PASSWORD]',
              'model' => 'mysql4',
              'engine' => 'innodb',
              'initStatements' => 'SET NAMES utf8;',
              'active' => '1',
              'excluded_areas' => [
                  '/checkout',
                  '/customer',
                  '/another-location',
              ],
              'slaves' => [
                  [
                      'host' => '[DB_READER_1_HOST]',
                      'username' => '[DB_READER_1_USERNAME]',
                      'password' => '[DB_READER_1_PASSWORD]',
                  ],
                  [
                      'host' => '[DB_READER_2_HOST]',
                      'username' => '[DB_READER_2_USERNAME]',
                      'password' => '[DB_READER_2_PASSWORD]',
                  ]
              ]
          ]
      ]
  ],

N.B: Don't forget to flush cache after updating the app/etc/env.php file and clear opache.

TODO

• Reader exclusions DB/Adapter/Pdo/MysqlProxy.php:114 this is required because Magento saves the model, then instantly tries to load it. Need to locate the offending code or find another way around.

Debug

To debug which queries are going to which endpoint while the platform is in production mode add

'log_level' => \Monolog\Logger::INFO,

to the default node
then run tail -f var/log/system.log | grep Zero1_SplitDb an request some pages.