對於流量高的 Wordpress 網站來說，資料庫往往是第一個出現的瓶頸。針對資料庫擴充性的問題，本文介紹 HyperDB 這個解決方案。
HyperDB 設計來取代內建的$wpdb 物件，因此，對於現有程式來說，無須修改就能夠享受它帶來的好處。 HyperDB 支援多個資料庫服務器，可以將唯讀的請求導到 MySQL 從屬(slave)伺服器以提高查詢效能； 當某一台資料庫伺服器沒有回應，它也能夠偵測出來，並將請求導到其他還有作用的伺服器，以達到錯誤轉移的效果。
前提假設
- 假設有兩台 MySQL 伺服器，已經按照 設定 MySQL Master-Master 複製 方式設定。
- 這兩台 MySQL 伺服器各自內部的連線網址分別為: node1.local 跟 node2.local。
安裝方式
複製 db.php
將 HyperDB 的 zip 檔解開到某個目錄, 將其中的 db.php 複製到 wp-content 目錄中。
建立 db-config
在跟 wp-config.php 同一層的目錄，建立名為 db-config.php 的檔案，底下為其內容：
<?php
/** Variable settings **/
/**
* save_queries (bool)
* This is useful for debugging. Queries are saved in $wpdb->;queries. It is not
* a constant because you might want to use it momentarily.
* Default: false
*/
$wpdb->;save_queries = false;
/**
* persistent (bool)
* This determines whether to use mysql_connect or mysql_pconnect. The effects
* of this setting may vary and should be carefully tested.
* Default: false
*/
$wpdb->persistent = false;
/**
* max_connections (int)
* This is the number of mysql connections to keep open. Increase if you expect
* to reuse a lot of connections to different servers. This is ignored if you
* enable persistent connections.
* Default: 10
*/
$wpdb->max_connections = 10;
/**
* check_tcp_responsiveness
* Enables checking TCP responsiveness by fsockopen prior to mysql_connect or
* mysql_pconnect. This was added because PHP's mysql functions do not provide
* a variable timeout setting. Disabling it may improve average performance by
* a very tiny margin but lose protection against connections failing slowly.
* Default: true
*/
$wpdb->check_tcp_responsiveness = true;
/** Configuration Functions **/
/**
* $wpdb->add_database( $database );
*
* $database is an associative array with these parameters:
* host (required) Hostname with optional :port. Default port is 3306.
* user (required) MySQL user name.
* password (required) MySQL user password.
* name (required) MySQL database name.
* read (optional) Whether server is readable. Default is 1 (readable).
* Also used to assign preference. See "Network topology".
* write (optional) Whether server is writable. Default is 1 (writable).
* Also used to assign preference in multi-master mode.
* dataset (optional) Name of dataset. Default is 'global'.
* timeout (optional) Seconds to wait for TCP responsiveness. Default is 0.2
* lag_threshold (optional) The minimum lag on a slave in seconds before we consider it lagged.
* Set null to disable. When not set, the value of $wpdb->default_lag_threshold is used.
*/
/** Masters and slaves
*
* A database definition can include 'read' and 'write' parameters. These
* operate as boolean switches but they are typically specified as integers.
* They allow or disallow use of the database for reading or writing.
*
* A master database might be configured to allow reading and writing:
* 'write' => 1,
* 'read' => 1,
* while a slave would be allowed only to read:
* 'write' => 0,
* 'read' => 1,
*
* It might be advantageous to disallow reading from the master, such as when
* there are many slaves available and the master is very busy with writes.
* 'write' => 1,
* 'read' => 0,
* HyperDB tracks the tables that it has written since instantiation and sending
* subsequent read queries to the same server that received the write query.
* Thus a master set up this way will still receive read queries, but only
* subsequent to writes.
*/
/**
* Slaves lag awareness
*
* HyperDB accommodates slave lag by making decisions, based on the defined lag
* threshold. If the lag threshold is not set, it will ignore the slave lag.
* Otherwise, it will try to find a non-lagged slave, before connecting to a lagged one.
*
* A slave is considered lagged, if it's replication lag is bigger than the lag threshold
* you have defined in $wpdb->$default_lag_threshold or in the per-database settings, using
* add_database(). You can also rewrite the lag threshold, by returning
* $server['lag_threshold'] variable with the 'dataset' group callbacks.
*
* HyperDB does not check the lag on the slaves. You have to define two callbacks
* callbacks to do that:
*
* $wpdb->add_callback( $callback, 'get_lag_cache' );
*
* and
*
* $wpdb->add_callback( $callback, 'get_lag' );
*
* The first one is called, before connecting to a slave and should return
* the replication lag in seconds or false, if unknown, based on $wpdb->lag_cache_key.
*
* The second callback is called after a connection to a slave is established.
* It should return it's replication lag or false, if unknown,
* based on the connection in $wpdb->dbhs[ $wpdb->dbhname ].
*/
/** Sample Configuration 1: Using the Default Server **/
/** NOTE: THIS IS ACTIVE BY DEFAULT. COMMENT IT OUT. **/
/**
* This is the most basic way to add a server to HyperDB using only the
* required parameters: host, user, password, name.
* This adds the DB defined in wp-config.php as a read/write server for
* the 'global' dataset. (Every table is in 'global' by default.)
*/
$wpdb->add_database(array(
'host' => DB_HOST, // If port is other than 3306, use host:port.
'user' => DB_USER,
'password' => DB_PASSWORD,
'name' => DB_NAME,
));
/**
* This adds the same server again, only this time it is configured as a slave.
* The last three parameters are set to the defaults but are shown for clarity.
*/
$wpdb->add_database(array(
'host' => 'node1.local', // If port is other than 3306, use host:port.
'user' => DB_USER,
'password' => DB_PASSWORD,
'name' => DB_NAME,
'write' => 0,
'read' => 1,
'dataset' => 'global',
'timeout' => 0.2,
));
$wpdb->add_database(array(
'host' => 'node2.local', // If port is other than 3306, use host:port.
'user' => DB_USER,
'password' => DB_PASSWORD,
'name' => DB_NAME,
'write' => 0,
'read' => 1,
'dataset' => 'global',
'timeout' => 0.2,
));
/**
* Sample replication lag detection configuration.
*
* We use mk-heartbeat (http://www.maatkit.org/doc/mk-heartbeat.html)
* to detect replication lag.
*
* This implementation requires the database user
* to have read access to the heartbeat table.
*
* The cache uses shared memory for portability.
* Can be modified to work with Memcached, APC and etc.
*/
/*
$wpdb->lag_cache_ttl = 30;
$wpdb->shmem_key = ftok( __FILE__, "Y" );
$wpdb->shmem_size = 128 * 1024;
$wpdb->add_callback( 'get_lag_cache', 'get_lag_cache' );
$wpdb->add_callback( 'get_lag', 'get_lag' );
function get_lag_cache( $wpdb ) {
$segment = shm_attach( $wpdb->shmem_key, $wpdb->shmem_size, 0600 );
$lag_data = @shm_get_var( $segment, 0 );
shm_detach( $segment );
if ( !is_array( $lag_data ) || !is_array( $lag_data[ $wpdb->lag_cache_key ] ) )
return false;
if ( $wpdb->lag_cache_ttl < time() - $lag_data[ $wpdb->lag_cache_key ][ 'timestamp' ] )
return false;
return $lag_data[ $wpdb->lag_cache_key ][ 'lag' ];
}
function get_lag( $wpdb ) {
$dbh = $wpdb->dbhs[ $wpdb->dbhname ];
if ( !mysql_select_db( 'heartbeat', $dbh ) )
return false;
$result = mysql_query( "SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) AS lag FROM heartbeat LIMIT 1", $dbh );
if ( !$result || false === $row = mysql_fetch_assoc( $result ) )
return false;
// Cache the result in shared memory with timestamp
$sem_id = sem_get( $wpdb->shmem_key, 1, 0600, 1 ) ;
sem_acquire( $sem_id );
$segment = shm_attach( $wpdb->shmem_key, $wpdb->shmem_size, 0600 );
$lag_data = @shm_get_var( $segment, 0 );
if ( !is_array( $lag_data ) )
$lag_data = array();
$lag_data[ $wpdb->lag_cache_key ] = array( 'timestamp' => time(), 'lag' => $row[ 'lag' ] );
shm_put_var( $segment, 0, $lag_data );
shm_detach( $segment );
sem_release( $sem_id );
return $row[ 'lag' ];
}
*/
// The ending PHP tag is omitted. This is actually safer than including it.重點在其中的$wpdb->add_database 敘述，第一個敘述將 Wordpress 現有的資料庫連線作為預設， 只有這個的話，行為跟沒有安裝 HyperDB 應該一樣。後兩個敘述，就是把 node1.local 跟 node2.local 這兩個資料庫加入成為唯讀的連線設定。
修改 wp-config.php
修改 wp-config.php 檔案，在最後加上下列敘述:
require_once(ABSPATH . 'db-config.php');