[Summary view]

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

   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). If specified, a table being written to is
  47   *   deemed fully synced and suitable for slave read.
  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 if the 'latency' option
  58   *   is set. For those queries master 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   *      * If the 'latency' option is set then the microtime() is compared to
  67   *        the write microrime, and if more then latency time has passed the slave
  68   *        handle is used.
  69   *      * Otherwise (not enough time passed or 'latency' option not set)
  70   *        we choose the master write handle
  71   *   If none of the above conditions are met the slave instance is used.
  72   *
  73   * A 'latency' example:
  74   *  - we have set $CFG->dboptions['readonly']['latency'] to 0.2.
  75   *  - a SQL_QUERY_UPDATE to table tbl_x happens, and it is recorded in
  76   *    the $written array
  77   *  - 0.15 seconds later SQL_QUERY_SELECT with tbl_x is requested - the master
  78   *    connection is used
  79   *  - 0.10 seconds later (0.25 seconds after SQL_QUERY_UPDATE) another
  80   *    SQL_QUERY_SELECT with tbl_x is requested - this time more than 0.2 secs
  81   *    has gone and master -> slave sync is assumed, so the slave connection is
  82   *    used again
  83   */
  84  
  85  trait moodle_read_slave_trait {
  86  
  87      /** @var resource master write database handle */
  88      protected $dbhwrite;
  89  
  90      /** @var resource slave read only database handle */
  91      protected $dbhreadonly;
  92  
  93      private $wantreadslave = false;
  94      private $readsslave = 0;
  95      private $slavelatency = 0;
  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                      // @codingStandardsIgnoreStart
 199                      try {
 200                          $this->raw_connect($rodb['dbhost'], $rodb['dbuser'], $rodb['dbpass'], $dbname, $prefix, $dboptions);
 201                          $this->dbhreadonly = $this->get_db_handle();
 202                          break;
 203                      } catch (dml_connection_exception $e) {
 204                          // If readonly slave is not connectable we'll have to do without it.
 205                      }
 206                      // @codingStandardsIgnoreEnd
 207                  }
 208                  // ... lock_db queries always go to master.
 209                  // Since it is a lock and as such marshalls concurrent connections,
 210                  // it is best to leave it out and avoid master/slave latency.
 211                  $this->readexclude[] = 'lock_db';
 212                  // ... and sessions.
 213                  $this->readexclude[] = 'sessions';
 214              }
 215          }
 216          if (!$this->dbhreadonly) {
 217              $this->set_dbhwrite();
 218          }
 219  
 220          return true;
 221      }
 222  
 223      /**
 224       * Set database handle to readwrite master
 225       * Will connect if required. Calls set_db_handle()
 226       * @return void
 227       */
 228      private function set_dbhwrite(): void {
 229          // Lazy connect to read/write master.
 230          if (!$this->dbhwrite) {
 231              $temptables = $this->temptables;
 232              $this->raw_connect($this->pdbhost, $this->pdbuser, $this->pdbpass, $this->pdbname, $this->pprefix, $this->pdboptions);
 233              if ($temptables) {
 234                  $this->temptables = $temptables; // Restore temptables, so we don't get separate sets for rw and ro.
 235              }
 236              $this->dbhwrite = $this->get_db_handle();
 237          }
 238          $this->set_db_handle($this->dbhwrite);
 239      }
 240  
 241      /**
 242       * Returns whether we want to connect to slave database for read queries.
 243       * @return bool Want read only connection
 244       */
 245      public function want_read_slave(): bool {
 246          return $this->wantreadslave;
 247      }
 248  
 249      /**
 250       * Returns the number of reads done by the read only database.
 251       * @return int Number of reads.
 252       */
 253      public function perf_get_reads_slave(): int {
 254          return $this->readsslave;
 255      }
 256  
 257      /**
 258       * On DBs that support it, switch to transaction mode and begin a transaction
 259       * @return moodle_transaction
 260       */
 261      public function start_delegated_transaction() {
 262          $this->set_dbhwrite();
 263          return parent::start_delegated_transaction();
 264      }
 265  
 266      /**
 267       * Called before each db query.
 268       * @param string $sql
 269       * @param array $params array of parameters
 270       * @param int $type type of query
 271       * @param mixed $extrainfo driver specific extra information
 272       * @return void
 273       */
 274      protected function query_start($sql, array $params = null, $type, $extrainfo = null) {
 275          parent::query_start($sql, $params, $type, $extrainfo);
 276          $this->select_db_handle($type, $sql);
 277      }
 278  
 279      /**
 280       * Select appropriate db handle - readwrite or readonly
 281       * @param int $type type of query
 282       * @param string $sql
 283       * @return void
 284       */
 285      protected function select_db_handle(int $type, string $sql): void {
 286          if ($this->dbhreadonly && $this->can_use_readonly($type, $sql)) {
 287              $this->readsslave++;
 288              $this->set_db_handle($this->dbhreadonly);
 289              return;
 290          }
 291          $this->set_dbhwrite();
 292      }
 293  
 294      /**
 295       * Check if The query qualifies for readonly connection execution
 296       * Logging queries are exempt, those are write operations that circumvent
 297       * standard query_start/query_end paths.
 298       * @param int $type type of query
 299       * @param string $sql
 300       * @return bool
 301       */
 302      protected function can_use_readonly(int $type, string $sql): bool {
 303          if ($this->loggingquery) {
 304              return false;
 305          }
 306  
 307          if (during_initial_install()) {
 308              return false;
 309          }
 310  
 311          // Transactions are done as AUX, we cannot play with that.
 312          switch ($type) {
 313              case SQL_QUERY_SELECT:
 314                  if ($this->transactions) {
 315                      return false;
 316                  }
 317  
 318                  $now = null;
 319                  foreach ($this->table_names($sql) as $tablename) {
 320                      if (in_array($tablename, $this->readexclude)) {
 321                          return false;
 322                      }
 323  
 324                      if ($this->temptables && $this->temptables->is_temptable($tablename)) {
 325                          return false;
 326                      }
 327  
 328                      if (isset($this->written[$tablename])) {
 329                          if ($this->slavelatency) {
 330                              $now = $now ?: microtime(true);
 331                              if ($now - $this->written[$tablename] < $this->slavelatency) {
 332                                  return false;
 333                              }
 334                              unset($this->written[$tablename]);
 335                          } else {
 336                              return false;
 337                          }
 338                      }
 339                  }
 340  
 341                  return true;
 342              case SQL_QUERY_INSERT:
 343              case SQL_QUERY_UPDATE:
 344                  // If we are in transaction we cannot set the written time yet.
 345                  $now = $this->slavelatency && !$this->transactions ? microtime(true) : true;
 346                  foreach ($this->table_names($sql) as $tablename) {
 347                      $this->written[$tablename] = $now;
 348                  }
 349                  return false;
 350              case SQL_QUERY_STRUCTURE:
 351                  foreach ($this->table_names($sql) as $tablename) {
 352                      if (!in_array($tablename, $this->readexclude)) {
 353                          $this->readexclude[] = $tablename;
 354                      }
 355                  }
 356                  return false;
 357          }
 358          return false;
 359      }
 360  
 361      /**
 362       * Indicates delegated transaction finished successfully.
 363       * Set written times after outermost transaction finished
 364       * @param moodle_transaction $transaction The transaction to commit
 365       * @return void
 366       * @throws dml_transaction_exception Creates and throws transaction related exceptions.
 367       */
 368      public function commit_delegated_transaction(moodle_transaction $transaction) {
 369          parent::commit_delegated_transaction($transaction);
 370  
 371          if ($this->transactions) {
 372              return;
 373          }
 374  
 375          if (!$this->slavelatency) {
 376              return;
 377          }
 378  
 379          $now = null;
 380          foreach ($this->written as $tablename => $when) {
 381              if ($when === true) {
 382                  $now = $now ?: microtime(true);
 383                  $this->written[$tablename] = $now;
 384              }
 385          }
 386      }
 387  
 388      /**
 389       * Parse table names from query
 390       * @param string $sql
 391       * @return array
 392       */
 393      protected function table_names(string $sql): array {
 394          preg_match_all('/\b'.$this->prefix.'([a-z][A-Za-z0-9_]*)/', $sql, $match);
 395          return $match[1];
 396      }
 397  }