[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Trait that adds read-only slave connection capability
  19   *
  20   * @package    core
  21   * @category   dml
  22   * @copyright  2018 Srdjan Janković, Catalyst IT
  23   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  defined('MOODLE_INTERNAL') || die();
  27  
  28  /**
  29   * Trait to wrap connect() method of database driver classes that gives
  30   * ability to use read only slave instances for SELECT queries. For the
  31   * databases that support replication and read only connections to the slave.
  32   * If the slave connection is configured there will be two database handles
  33   * created, one for the master and another one for the slave. If there's no
  34   * slave specified everything uses master handle.
  35   *
  36   * Classes that use this trait need to rename existing connect() method to
  37   * raw_connect(). In addition, they need to provide get_db_handle() and
  38   * set_db_handle() methods, due to dbhandle attributes not being named
  39   * consistently across the database driver classes.
  40   *
  41   * Read only slave connection is configured in the $CFG->dboptions['readonly']
  42   * array.
  43   * - It supports multiple 'instance' entries, in case one is not accessible,
  44   *   but only one (first connectable) instance is used.
  45   * - 'latency' option: master -> slave sync latency in seconds (will probably
  46   *   be a fraction of a second). A table being written to is deemed fully synced
  47   *   after that period and suitable for slave read. Defaults to 1 sec.
  48   * - 'exclude_tables' option: a list of tables that never go to the slave for
  49   *   querying. The feature is meant to be used in emergency only, so the
  50   *   readonly feature can still be used in case there is a rogue query that
  51   *   does not go through the standard dml interface or some other unaccounted
  52   *   situation. It should not be used under normal circumstances, and its use
  53   *   indicates a problem in the system that needs addressig.
  54   *
  55   * Choice of the database handle is based on following:
  56   * - SQL_QUERY_INSERT, UPDATE and STRUCTURE record table from the query
  57   *   in the $written array and microtime() the event. For those queries master
  58   *   write handle is used.
  59   * - SQL_QUERY_AUX queries will always use the master write handle because they
  60   *   are used for transaction start/end, locking etc. In that respect, query_start() and
  61   *   query_end() *must not* be used during the connection phase.
  62   * - SQL_QUERY_AUX_READONLY queries will use the master write handle if in a transaction.
  63   * - SELECT queries will use the master write handle if:
  64   *   -- any of the tables involved is a temp table
  65   *   -- any of the tables involved is listed in the 'exclude_tables' option
  66   *   -- any of the tables involved is in the $written array:
  67   *      * current microtime() is compared to the write microrime, and if more than
  68   *        latency time has passed the slave handle is used
  69   *      * otherwise (not enough time passed) we choose the master write handle
  70   *   If none of the above conditions are met the slave instance is used.
  71   *
  72   * A 'latency' example:
  73   *  - we have set $CFG->dboptions['readonly']['latency'] to 0.2.
  74   *  - a SQL_QUERY_UPDATE to table tbl_x happens, and it is recorded in
  75   *    the $written array
  76   *  - 0.15 seconds later SQL_QUERY_SELECT with tbl_x is requested - the master
  77   *    connection is used
  78   *  - 0.10 seconds later (0.25 seconds after SQL_QUERY_UPDATE) another
  79   *    SQL_QUERY_SELECT with tbl_x is requested - this time more than 0.2 secs
  80   *    has gone and master -> slave sync is assumed, so the slave connection is
  81   *    used again
  82   */
  83  
  84  trait moodle_read_slave_trait {
  85  
  86      /** @var resource master write database handle */
  87      protected $dbhwrite;
  88  
  89      /** @var resource slave read only database handle */
  90      protected $dbhreadonly;
  91  
  92      private $wantreadslave = false;
  93      private $readsslave = 0;
  94      private $slavelatency = 1;
  95      private $structurechange = false;
  96  
  97      private $written = []; // Track tables being written to.
  98      private $readexclude = []; // Tables to exclude from using dbhreadonly.
  99  
 100      // Store original params.
 101      private $pdbhost;
 102      private $pdbuser;
 103      private $pdbpass;
 104      private $pdbname;
 105      private $pprefix;
 106      private $pdboptions;
 107  
 108      /**
 109       * Gets db handle currently used with queries
 110       * @return resource
 111       */
 112      abstract protected function get_db_handle();
 113  
 114      /**
 115       * Sets db handle to be used with subsequent queries
 116       * @param resource $dbh
 117       * @return void
 118       */
 119      abstract protected function set_db_handle($dbh): void;
 120  
 121      /**
 122       * Connect to db
 123       * The real connection establisment, called from connect() and set_dbhwrite()
 124       * @param string $dbhost The database host.
 125       * @param string $dbuser The database username.
 126       * @param string $dbpass The database username's password.
 127       * @param string $dbname The name of the database being connected to.
 128       * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
 129       * @param array $dboptions driver specific options
 130       * @return bool true
 131       * @throws dml_connection_exception if error
 132       */
 133      abstract protected function raw_connect(string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions = null): bool;
 134  
 135      /**
 136       * Connect to db
 137       * The connection parameters processor that sets up stage for master write and slave readonly handles.
 138       * Must be called before other methods.
 139       * @param string $dbhost The database host.
 140       * @param string $dbuser The database username.
 141       * @param string $dbpass The database username's password.
 142       * @param string $dbname The name of the database being connected to.
 143       * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
 144       * @param array $dboptions driver specific options
 145       * @return bool true
 146       * @throws dml_connection_exception if error
 147       */
 148      public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions = null) {
 149          $this->pdbhost = $dbhost;
 150          $this->pdbuser = $dbuser;
 151          $this->pdbpass = $dbpass;
 152          $this->pdbname = $dbname;
 153          $this->pprefix = $prefix;
 154          $this->pdboptions = $dboptions;
 155  
 156          if ($dboptions) {
 157              if (isset($dboptions['readonly'])) {
 158                  $this->wantreadslave = true;
 159                  $dboptionsro = $dboptions['readonly'];
 160  
 161                  if (isset($dboptionsro['connecttimeout'])) {
 162                      $dboptions['connecttimeout'] = $dboptionsro['connecttimeout'];
 163                  } else if (!isset($dboptions['connecttimeout'])) {
 164                      $dboptions['connecttimeout'] = 2; // Default readonly connection timeout.
 165                  }
 166                  if (isset($dboptionsro['latency'])) {
 167                      $this->slavelatency = $dboptionsro['latency'];
 168                  }
 169                  if (isset($dboptionsro['exclude_tables'])) {
 170                      $this->readexclude = $dboptionsro['exclude_tables'];
 171                      if (!is_array($this->readexclude)) {
 172                          throw new configuration_exception('exclude_tables must be an array');
 173                      }
 174                  }
 175                  $dbport = isset($dboptions['dbport']) ? $dboptions['dbport'] : null;
 176  
 177                  $slaves = $dboptionsro['instance'];
 178                  if (!is_array($slaves) || !isset($slaves[0])) {
 179                      $slaves = [$slaves];
 180                  }
 181  
 182                  if (count($slaves) > 1) {
 183                      // Randomise things a bit.
 184                      shuffle($slaves);
 185                  }
 186  
 187                  // Find first connectable readonly slave.
 188                  $rodb = [];
 189                  foreach ($slaves as $slave) {
 190                      if (!is_array($slave)) {
 191                          $slave = ['dbhost' => $slave];
 192                      }
 193                      foreach (['dbhost', 'dbuser', 'dbpass'] as $dbparam) {
 194                          $rodb[$dbparam] = isset($slave[$dbparam]) ? $slave[$dbparam] : $$dbparam;
 195                      }
 196                      $dboptions['dbport'] = isset($slave['dbport']) ? $slave['dbport'] : $dbport;
 197  
 198                      try {
 199                          $this->raw_connect($rodb['dbhost'], $rodb['dbuser'], $rodb['dbpass'], $dbname, $prefix, $dboptions);
 200                          $this->dbhreadonly = $this->get_db_handle();
 201                          break;
 202                      } catch (dml_connection_exception $e) { // phpcs:ignore
 203                          // If readonly slave is not connectable we'll have to do without it.
 204                      }
 205                  }
 206                  // ... lock_db queries always go to master.
 207                  // Since it is a lock and as such marshalls concurrent connections,
 208                  // it is best to leave it out and avoid master/slave latency.
 209                  $this->readexclude[] = 'lock_db';
 210                  // ... and sessions.
 211                  $this->readexclude[] = 'sessions';
 212              }
 213          }
 214          if (!$this->dbhreadonly) {
 215              $this->set_dbhwrite();
 216          }
 217  
 218          return true;
 219      }
 220  
 221      /**
 222       * Set database handle to readwrite master
 223       * Will connect if required. Calls set_db_handle()
 224       * @return void
 225       */
 226      private function set_dbhwrite(): void {
 227          // Lazy connect to read/write master.
 228          if (!$this->dbhwrite) {
 229              $temptables = $this->temptables;
 230              $this->raw_connect($this->pdbhost, $this->pdbuser, $this->pdbpass, $this->pdbname, $this->pprefix, $this->pdboptions);
 231              if ($temptables) {
 232                  $this->temptables = $temptables; // Restore temptables, so we don't get separate sets for rw and ro.
 233              }
 234              $this->dbhwrite = $this->get_db_handle();
 235          }
 236          $this->set_db_handle($this->dbhwrite);
 237      }
 238  
 239      /**
 240       * Returns whether we want to connect to slave database for read queries.
 241       * @return bool Want read only connection
 242       */
 243      public function want_read_slave(): bool {
 244          return $this->wantreadslave;
 245      }
 246  
 247      /**
 248       * Returns the number of reads done by the read only database.
 249       * @return int Number of reads.
 250       */
 251      public function perf_get_reads_slave(): int {
 252          return $this->readsslave;
 253      }
 254  
 255      /**
 256       * On DBs that support it, switch to transaction mode and begin a transaction
 257       * @return moodle_transaction
 258       */
 259      public function start_delegated_transaction() {
 260          $this->set_dbhwrite();
 261          return parent::start_delegated_transaction();
 262      }
 263  
 264      /**
 265       * Called before each db query.
 266       * @param string $sql
 267       * @param array|null $params An array of parameters.
 268       * @param int $type type of query
 269       * @param mixed $extrainfo driver specific extra information
 270       * @return void
 271       */
 272      protected function query_start($sql, ?array $params, $type, $extrainfo = null) {
 273          parent::query_start($sql, $params, $type, $extrainfo);
 274          $this->select_db_handle($type, $sql);
 275      }
 276  
 277      /**
 278       * This should be called immediately after each db query. It does a clean up of resources.
 279       *
 280       * @param mixed $result The db specific result obtained from running a query.
 281       * @return void
 282       */
 283      protected function query_end($result) {
 284          if ($this->written) {
 285              // Adjust the written time.
 286              array_walk($this->written, function (&$val) {
 287                  if ($val === true) {
 288                      $val = microtime(true);
 289                  }
 290              });
 291          }
 292  
 293          parent::query_end($result);
 294      }
 295  
 296      /**
 297       * Select appropriate db handle - readwrite or readonly
 298       * @param int $type type of query
 299       * @param string $sql
 300       * @return void
 301       */
 302      protected function select_db_handle(int $type, string $sql): void {
 303          if ($this->dbhreadonly && $this->can_use_readonly($type, $sql)) {
 304              $this->readsslave++;
 305              $this->set_db_handle($this->dbhreadonly);
 306              return;
 307          }
 308          $this->set_dbhwrite();
 309      }
 310  
 311      /**
 312       * Check if The query qualifies for readonly connection execution
 313       * Logging queries are exempt, those are write operations that circumvent
 314       * standard query_start/query_end paths.
 315       * @param int $type type of query
 316       * @param string $sql
 317       * @return bool
 318       */
 319      protected function can_use_readonly(int $type, string $sql): bool {
 320          if ($this->loggingquery) {
 321              return false;
 322          }
 323  
 324          if (during_initial_install()) {
 325              return false;
 326          }
 327  
 328          // Transactions are done as AUX, we cannot play with that.
 329          switch ($type) {
 330              case SQL_QUERY_AUX_READONLY:
 331                  // SQL_QUERY_AUX_READONLY may read the structure data.
 332                  // We don't have a way to reliably determine whether it is safe to go to readonly if the structure has changed.
 333                  return !$this->structurechange;
 334              case SQL_QUERY_SELECT:
 335                  if ($this->transactions) {
 336                      return false;
 337                  }
 338  
 339                  $now = null;
 340                  foreach ($this->table_names($sql) as $tablename) {
 341                      if (in_array($tablename, $this->readexclude)) {
 342                          return false;
 343                      }
 344  
 345                      if ($this->temptables && $this->temptables->is_temptable($tablename)) {
 346                          return false;
 347                      }
 348  
 349                      if (isset($this->written[$tablename])) {
 350                          $now = $now ?: microtime(true);
 351  
 352                          if ($now - $this->written[$tablename] < $this->slavelatency) {
 353                              return false;
 354                          }
 355                          unset($this->written[$tablename]);
 356                      }
 357                  }
 358  
 359                  return true;
 360              case SQL_QUERY_INSERT:
 361              case SQL_QUERY_UPDATE:
 362                  foreach ($this->table_names($sql) as $tablename) {
 363                      $this->written[$tablename] = true;
 364                  }
 365                  return false;
 366              case SQL_QUERY_STRUCTURE:
 367                  $this->structurechange = true;
 368                  foreach ($this->table_names($sql) as $tablename) {
 369                      if (!in_array($tablename, $this->readexclude)) {
 370                          $this->readexclude[] = $tablename;
 371                      }
 372                  }
 373                  return false;
 374          }
 375          return false;
 376      }
 377  
 378      /**
 379       * Indicates delegated transaction finished successfully.
 380       * Set written times after outermost transaction finished
 381       * @param moodle_transaction $transaction The transaction to commit
 382       * @return void
 383       * @throws dml_transaction_exception Creates and throws transaction related exceptions.
 384       */
 385      public function commit_delegated_transaction(moodle_transaction $transaction) {
 386          if ($this->written) {
 387              // Adjust the written time.
 388              $now = microtime(true);
 389              foreach ($this->written as $tablename => $when) {
 390                  $this->written[$tablename] = $now;
 391              }
 392          }
 393  
 394          parent::commit_delegated_transaction($transaction);
 395      }
 396  
 397      /**
 398       * Parse table names from query
 399       * @param string $sql
 400       * @return array
 401       */
 402      protected function table_names(string $sql): array {
 403          preg_match_all('/\b'.$this->prefix.'([a-z][A-Za-z0-9_]*)/', $sql, $match);
 404          return $match[1];
 405      }
 406  }