[Summary view]

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

   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 transactionstart/end, locking etc. In that respect, query_start() and
  61   *   query_end() *must not* be used during the connection phase.
  62   * - SELECT queries will use the master write handle if:
  63   *   -- any of the tables involved is a temp table
  64   *   -- any of the tables involved is listed in the 'exclude_tables' option
  65   *   -- any of the tables involved is in the $written array:
  66   *      * current microtime() is compared to the write microrime, and if more than
  67   *        latency time has passed the slave handle is used
  68   *      * otherwise (not enough time passed) we choose the master write handle
  69   *   If none of the above conditions are met the slave instance is used.
  70   *
  71   * A 'latency' example:
  72   *  - we have set $CFG->dboptions['readonly']['latency'] to 0.2.
  73   *  - a SQL_QUERY_UPDATE to table tbl_x happens, and it is recorded in
  74   *    the $written array
  75   *  - 0.15 seconds later SQL_QUERY_SELECT with tbl_x is requested - the master
  76   *    connection is used
  77   *  - 0.10 seconds later (0.25 seconds after SQL_QUERY_UPDATE) another
  78   *    SQL_QUERY_SELECT with tbl_x is requested - this time more than 0.2 secs
  79   *    has gone and master -> slave sync is assumed, so the slave connection is
  80   *    used again
  81   */
  82  
  83  trait moodle_read_slave_trait {
  84  
  85      /** @var resource master write database handle */
  86      protected $dbhwrite;
  87  
  88      /** @var resource slave read only database handle */
  89      protected $dbhreadonly;
  90  
  91      private $wantreadslave = false;
  92      private $readsslave = 0;
  93      private $slavelatency = 1;
  94  
  95      private $written = []; // Track tables being written to.
  96      private $readexclude = []; // Tables to exclude from using dbhreadonly.
  97  
  98      // Store original params.
  99      private $pdbhost;
 100      private $pdbuser;
 101      private $pdbpass;
 102      private $pdbname;
 103      private $pprefix;
 104      private $pdboptions;
 105  
 106      /**
 107       * Gets db handle currently used with queries
 108       * @return resource
 109       */
 110      abstract protected function get_db_handle();
 111  
 112      /**
 113       * Sets db handle to be used with subsequent queries
 114       * @param resource $dbh
 115       * @return void
 116       */
 117      abstract protected function set_db_handle($dbh): void;
 118  
 119      /**
 120       * Connect to db
 121       * The real connection establisment, called from connect() and set_dbhwrite()
 122       * @param string $dbhost The database host.
 123       * @param string $dbuser The database username.
 124       * @param string $dbpass The database username's password.
 125       * @param string $dbname The name of the database being connected to.
 126       * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
 127       * @param array $dboptions driver specific options
 128       * @return bool true
 129       * @throws dml_connection_exception if error
 130       */
 131      abstract protected function raw_connect(string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions = null): bool;
 132  
 133      /**
 134       * Connect to db
 135       * The connection parameters processor that sets up stage for master write and slave readonly handles.
 136       * Must be called before other methods.
 137       * @param string $dbhost The database host.
 138       * @param string $dbuser The database username.
 139       * @param string $dbpass The database username's password.
 140       * @param string $dbname The name of the database being connected to.
 141       * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
 142       * @param array $dboptions driver specific options
 143       * @return bool true
 144       * @throws dml_connection_exception if error
 145       */
 146      public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions = null) {
 147          $this->pdbhost = $dbhost;
 148          $this->pdbuser = $dbuser;
 149          $this->pdbpass = $dbpass;
 150          $this->pdbname = $dbname;
 151          $this->pprefix = $prefix;
 152          $this->pdboptions = $dboptions;
 153  
 154          if ($dboptions) {
 155              if (isset($dboptions['readonly'])) {
 156                  $this->wantreadslave = true;
 157                  $dboptionsro = $dboptions['readonly'];
 158  
 159                  if (isset($dboptionsro['connecttimeout'])) {
 160                      $dboptions['connecttimeout'] = $dboptionsro['connecttimeout'];
 161                  } else if (!isset($dboptions['connecttimeout'])) {
 162                      $dboptions['connecttimeout'] = 2; // Default readonly connection timeout.
 163                  }
 164                  if (isset($dboptionsro['latency'])) {
 165                      $this->slavelatency = $dboptionsro['latency'];
 166                  }
 167                  if (isset($dboptionsro['exclude_tables'])) {
 168                      $this->readexclude = $dboptionsro['exclude_tables'];
 169                      if (!is_array($this->readexclude)) {
 170                          throw new configuration_exception('exclude_tables must be an array');
 171                      }
 172                  }
 173                  $dbport = isset($dboptions['dbport']) ? $dboptions['dbport'] : null;
 174  
 175                  $slaves = $dboptionsro['instance'];
 176                  if (!is_array($slaves) || !isset($slaves[0])) {
 177                      $slaves = [$slaves];
 178                  }
 179  
 180                  if (count($slaves) > 1) {
 181                      // Randomise things a bit.
 182                      shuffle($slaves);
 183                  }
 184  
 185                  // Find first connectable readonly slave.
 186                  $rodb = [];
 187                  foreach ($slaves as $slave) {
 188                      if (!is_array($slave)) {
 189                          $slave = ['dbhost' => $slave];
 190                      }
 191                      foreach (['dbhost', 'dbuser', 'dbpass'] as $dbparam) {
 192                          $rodb[$dbparam] = isset($slave[$dbparam]) ? $slave[$dbparam] : $$dbparam;
 193                      }
 194                      $dboptions['dbport'] = isset($slave['dbport']) ? $slave['dbport'] : $dbport;
 195  
 196                      try {
 197                          $this->raw_connect($rodb['dbhost'], $rodb['dbuser'], $rodb['dbpass'], $dbname, $prefix, $dboptions);
 198                          $this->dbhreadonly = $this->get_db_handle();
 199                          break;
 200                      } catch (dml_connection_exception $e) { // phpcs:ignore
 201                          // If readonly slave is not connectable we'll have to do without it.
 202                      }
 203                  }
 204                  // ... lock_db queries always go to master.
 205                  // Since it is a lock and as such marshalls concurrent connections,
 206                  // it is best to leave it out and avoid master/slave latency.
 207                  $this->readexclude[] = 'lock_db';
 208                  // ... and sessions.
 209                  $this->readexclude[] = 'sessions';
 210              }
 211          }
 212          if (!$this->dbhreadonly) {
 213              $this->set_dbhwrite();
 214          }
 215  
 216          return true;
 217      }
 218  
 219      /**
 220       * Set database handle to readwrite master
 221       * Will connect if required. Calls set_db_handle()
 222       * @return void
 223       */
 224      private function set_dbhwrite(): void {
 225          // Lazy connect to read/write master.
 226          if (!$this->dbhwrite) {
 227              $temptables = $this->temptables;
 228              $this->raw_connect($this->pdbhost, $this->pdbuser, $this->pdbpass, $this->pdbname, $this->pprefix, $this->pdboptions);
 229              if ($temptables) {
 230                  $this->temptables = $temptables; // Restore temptables, so we don't get separate sets for rw and ro.
 231              }
 232              $this->dbhwrite = $this->get_db_handle();
 233          }
 234          $this->set_db_handle($this->dbhwrite);
 235      }
 236  
 237      /**
 238       * Returns whether we want to connect to slave database for read queries.
 239       * @return bool Want read only connection
 240       */
 241      public function want_read_slave(): bool {
 242          return $this->wantreadslave;
 243      }
 244  
 245      /**
 246       * Returns the number of reads done by the read only database.
 247       * @return int Number of reads.
 248       */
 249      public function perf_get_reads_slave(): int {
 250          return $this->readsslave;
 251      }
 252  
 253      /**
 254       * On DBs that support it, switch to transaction mode and begin a transaction
 255       * @return moodle_transaction
 256       */
 257      public function start_delegated_transaction() {
 258          $this->set_dbhwrite();
 259          return parent::start_delegated_transaction();
 260      }
 261  
 262      /**
 263       * Called before each db query.
 264       * @param string $sql
 265       * @param array|null $params An array of parameters.
 266       * @param int $type type of query
 267       * @param mixed $extrainfo driver specific extra information
 268       * @return void
 269       */
 270      protected function query_start($sql, ?array $params, $type, $extrainfo = null) {
 271          parent::query_start($sql, $params, $type, $extrainfo);
 272          $this->select_db_handle($type, $sql);
 273      }
 274  
 275      /**
 276       * This should be called immediately after each db query. It does a clean up of resources.
 277       *
 278       * @param mixed $result The db specific result obtained from running a query.
 279       * @return void
 280       */
 281      protected function query_end($result) {
 282          if ($this->written) {
 283              // Adjust the written time.
 284              array_walk($this->written, function (&$val) {
 285                  if ($val === true) {
 286                      $val = microtime(true);
 287                  }
 288              });
 289          }
 290  
 291          parent::query_end($result);
 292      }
 293  
 294      /**
 295       * Select appropriate db handle - readwrite or readonly
 296       * @param int $type type of query
 297       * @param string $sql
 298       * @return void
 299       */
 300      protected function select_db_handle(int $type, string $sql): void {
 301          if ($this->dbhreadonly && $this->can_use_readonly($type, $sql)) {
 302              $this->readsslave++;
 303              $this->set_db_handle($this->dbhreadonly);
 304              return;
 305          }
 306          $this->set_dbhwrite();
 307      }
 308  
 309      /**
 310       * Check if The query qualifies for readonly connection execution
 311       * Logging queries are exempt, those are write operations that circumvent
 312       * standard query_start/query_end paths.
 313       * @param int $type type of query
 314       * @param string $sql
 315       * @return bool
 316       */
 317      protected function can_use_readonly(int $type, string $sql): bool {
 318          if ($this->loggingquery) {
 319              return false;
 320          }
 321  
 322          if (during_initial_install()) {
 323              return false;
 324          }
 325  
 326          // Transactions are done as AUX, we cannot play with that.
 327          switch ($type) {
 328              case SQL_QUERY_SELECT:
 329                  if ($this->transactions) {
 330                      return false;
 331                  }
 332  
 333                  $now = null;
 334                  foreach ($this->table_names($sql) as $tablename) {
 335                      if (in_array($tablename, $this->readexclude)) {
 336                          return false;
 337                      }
 338  
 339                      if ($this->temptables && $this->temptables->is_temptable($tablename)) {
 340                          return false;
 341                      }
 342  
 343                      if (isset($this->written[$tablename])) {
 344                          $now = $now ?: microtime(true);
 345  
 346                          if ($now - $this->written[$tablename] < $this->slavelatency) {
 347                              return false;
 348                          }
 349                          unset($this->written[$tablename]);
 350                      }
 351                  }
 352  
 353                  return true;
 354              case SQL_QUERY_INSERT:
 355              case SQL_QUERY_UPDATE:
 356                  foreach ($this->table_names($sql) as $tablename) {
 357                      $this->written[$tablename] = true;
 358                  }
 359                  return false;
 360              case SQL_QUERY_STRUCTURE:
 361                  foreach ($this->table_names($sql) as $tablename) {
 362                      if (!in_array($tablename, $this->readexclude)) {
 363                          $this->readexclude[] = $tablename;
 364                      }
 365                  }
 366                  return false;
 367          }
 368          return false;
 369      }
 370  
 371      /**
 372       * Indicates delegated transaction finished successfully.
 373       * Set written times after outermost transaction finished
 374       * @param moodle_transaction $transaction The transaction to commit
 375       * @return void
 376       * @throws dml_transaction_exception Creates and throws transaction related exceptions.
 377       */
 378      public function commit_delegated_transaction(moodle_transaction $transaction) {
 379          if ($this->written) {
 380              // Adjust the written time.
 381              $now = microtime(true);
 382              foreach ($this->written as $tablename => $when) {
 383                  $this->written[$tablename] = $now;
 384              }
 385          }
 386  
 387          parent::commit_delegated_transaction($transaction);
 388      }
 389  
 390      /**
 391       * Parse table names from query
 392       * @param string $sql
 393       * @return array
 394       */
 395      protected function table_names(string $sql): array {
 396          preg_match_all('/\b'.$this->prefix.'([a-z][A-Za-z0-9_]*)/', $sql, $match);
 397          return $match[1];
 398      }
 399  }