對於流量高的 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}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
<?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 檔案,在最後加上下列敘述:

.php}
1
require_once(ABSPATH . 'db-config.php');