Skip to content

使用 HyperDB 加強 WordPress 網站的資料庫擴充性

Published: 17 分鐘

對於流量高的 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 &quot;Network topology&quot;.
* 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__, &quot;Y&quot; );
$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 &lt; 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( &quot;SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) AS lag FROM heartbeat LIMIT 1&quot;, $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');
郭信義 (Sam Kuo)

奔騰網路科技技術長,專長分散式系統、Web 應用與雲端服務架構、設計、開發、部署與維運。工作之餘,喜歡關注自由軟體的發展與應用,偶爾寫一下部落格文章。

你可能會有興趣的文章