[Summary view]

Differences Between: [Versions 39 and 310] [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 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). 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          // Late connect to read/write master if needed.
 230          if (!$this->dbhwrite) {
 231              $this->raw_connect($this->pdbhost, $this->pdbuser, $this->pdbpass, $this->pdbname, $this->pprefix, $this->pdboptions);
 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 $params 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 = null, $type, $extrainfo = null) {
 271          parent::query_start($sql, $params, $type, $extrainfo);
 272          $this->select_db_handle($type, $sql);
 273      }
 274  
 275      /**
 276       * Select appropriate db handle - readwrite or readonly
 277       * @param int $type type of query
 278       * @param string $sql
 279       * @return void
 280       */
 281      protected function select_db_handle(int $type, string $sql): void {
 282          if ($this->dbhreadonly && $this->can_use_readonly($type, $sql)) {
 283              $this->readsslave++;
 284              $this->set_db_handle($this->dbhreadonly);
 285              return;
 286          }
 287          $this->set_dbhwrite();
 288      }
 289  
 290      /**
 291       * Check if The query qualifies for readonly connection execution
 292       * Logging queries are exempt, those are write operations that circumvent
 293       * standard query_start/query_end paths.
 294       * @param int $type type of query
 295       * @param string $sql
 296       * @return bool
 297       */
 298      protected function can_use_readonly(int $type, string $sql): bool {
 299          if ($this->loggingquery) {
 300              return false;
 301          }
 302  
 303          if (during_initial_install()) {
 304              return false;
 305          }
 306  
 307          // Transactions are done as AUX, we cannot play with that.
 308          switch ($type) {
 309              case SQL_QUERY_SELECT:
 310                  if ($this->transactions) {
 311                      return false;
 312                  }
 313  
 314                  $now = null;
 315                  foreach ($this->table_names($sql) as $tablename) {
 316                      if (in_array($tablename, $this->readexclude)) {
 317                          return false;
 318                      }
 319  
 320                      if ($this->temptables && $this->temptables->is_temptable($tablename)) {
 321                          return false;
 322                      }
 323  
 324                      if (isset($this->written[$tablename])) {
 325                          if ($this->slavelatency) {
 326                              $now = $now ?: microtime(true);
 327                              if ($now - $this->written[$tablename] < $this->slavelatency) {
 328                                  return false;
 329                              }
 330                              unset($this->written[$tablename]);
 331                          } else {
 332                              return false;
 333                          }
 334                      }
 335                  }
 336  
 337                  return true;
 338              case SQL_QUERY_INSERT:
 339              case SQL_QUERY_UPDATE:
 340                  // If we are in transaction we cannot set the written time yet.
 341                  $now = $this->slavelatency && !$this->transactions ? microtime(true) : true;
 342                  foreach ($this->table_names($sql) as $tablename) {
 343                      $this->written[$tablename] = $now;
 344                  }
 345                  return false;
 346              case SQL_QUERY_STRUCTURE:
 347                  foreach ($this->table_names($sql) as $tablename) {
 348                      if (!in_array($tablename, $this->readexclude)) {
 349                          $this->readexclude[] = $tablename;
 350                      }
 351                  }
 352                  return false;
 353          }
 354          return false;
 355      }
 356  
 357      /**
 358       * Indicates delegated transaction finished successfully.
 359       * Set written times after outermost transaction finished
 360       * @param moodle_transaction $transaction The transaction to commit
 361       * @return void
 362       * @throws dml_transaction_exception Creates and throws transaction related exceptions.
 363       */
 364      public function commit_delegated_transaction(moodle_transaction $transaction) {
 365          parent::commit_delegated_transaction($transaction);
 366  
 367          if ($this->transactions) {
 368              return;
 369          }
 370  
 371          if (!$this->slavelatency) {
 372              return;
 373          }
 374  
 375          $now = null;
 376          foreach ($this->written as $tablename => $when) {
 377              if ($when === true) {
 378                  $now = $now ?: microtime(true);
 379                  $this->written[$tablename] = $now;
 380              }
 381          }
 382      }
 383  
 384      /**
 385       * Parse table names from query
 386       * @param string $sql
 387       * @return array
 388       */
 389      protected function table_names(string $sql): array {
 390          preg_match_all('/\b'.$this->prefix.'([a-z][A-Za-z0-9_]*)/', $sql, $match);
 391          return $match[1];
 392      }
 393  }