| Re: MYSQL database slave support [message #165232 is a reply to message #165217] |
Fri, 13 May 2011 11:48   |
eclipsewebmaster
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma:
|
Member |
|
|
naudefj wrote on Thu, 12 May 2011 06:31Would it be possible to validate the "is_post" patch on your system?
I did. I think the is_post is good, since any SELECT query used to determine correct INSERT/UPDATE data will be sent to the MASTER. Since the conditional was put on the actual connection, I've altered the patch to verify that fud_sql_slave_lnk is defined before issuing a query. It makes for a complex if() statement, so if you have any suggestions...
On the down side, since the result of a post is a redirect to a get, the "is_post" method cannot entirely replace the current /* USE MASTER */ for loading up-to-date data.
Actually, running a search has led me to conclude that each message displayed in the search results does a call to fileio.inc.t:read_msg_body(), in which I had added a /* USE MASTER */. Below is a patch to override that in case read_msg_body() comes from a search page. At Eclipse we have over 670,000 forum messages and a bizillion users, so it's important for me to send as much as I can to the slave db.
### Eclipse Workspace Patch 1.0
#P fud
Index: FUDforum/sql/mysql/db.inc
===================================================================
RCS file: /cvsroot/foundation/fud/FUDforum/sql/mysql/db.inc,v
retrieving revision 1.4
diff -u -r1.4 db.inc
--- FUDforum/sql/mysql/db.inc 9 May 2011 19:13:59 -0000 1.4
+++ FUDforum/sql/mysql/db.inc 13 May 2011 13:53:04 -0000
@@ -23,7 +23,7 @@
}
/* Connect to slave, if specified. */
- if (!empty($GLOBALS['DBHOST_SLAVE_HOST'])) {
+ if (!empty($GLOBALS['DBHOST_SLAVE_HOST']) && !$GLOBALS['is_post']) {
$conn = @$connect_func($GLOBALS['DBHOST_SLAVE_HOST'], $GLOBALS['DBHOST_USER'], $GLOBALS['DBHOST_PASSWORD']);
if (!$conn) {
fud_logerror('Unable to init SlaveDB, fallback to MasterDB: '. mysql_error(), 'sql_errors');
@@ -93,7 +93,7 @@
# Force master if SELECT /* USE MASTER */
# If databases are locked, assume a transaction and use MASTER exclusively.
$db = fud_sql_lnk;
- if(strtoupper(substr(trim($query), 0, 6)) == 'SELECT' && strtoupper(substr(trim($query), 0, 23)) != "SELECT /* USE MASTER */" && !db_locked()) {
+ if(strtoupper(substr(trim($query), 0, 6)) == 'SELECT' && strtoupper(substr(trim($query), 0, 23)) != "SELECT /* USE MASTER */" && !db_locked() && defined('fud_sql_slave_lnk')) {
$db = fud_sql_slave_lnk;
}
$r = mysql_query($query, $db) or fud_sql_error_handler($query, mysql_error($db), mysql_errno($db), db_version());
@@ -112,7 +112,7 @@
# If databases are locked, assume a transaction and use MASTER exclusively.
$db = fud_sql_lnk;
$db_used = "MASTER";
- if(strtoupper(substr(trim($query), 0, 6)) == 'SELECT' && strtoupper(substr(trim($query), 0, 23)) != "SELECT /* USE MASTER */" && !db_locked()) {
+ if(strtoupper(substr(trim($query), 0, 6)) == 'SELECT' && strtoupper(substr(trim($query), 0, 23)) != "SELECT /* USE MASTER */" && !db_locked() && defined('fud_sql_slave_lnk')) {
$db = fud_sql_slave_lnk;
$db_used = "SLAVE";
}
### Eclipse Workspace Patch 1.0
#P fud
Index: FUDforum/src/fileio.inc.t
===================================================================
RCS file: /cvsroot/foundation/fud/FUDforum/src/fileio.inc.t,v
retrieving revision 1.3
diff -u -r1.3 fileio.inc.t
--- FUDforum/src/fileio.inc.t 9 May 2011 19:13:59 -0000 1.3
+++ FUDforum/src/fileio.inc.t 13 May 2011 13:59:23 -0000
@@ -12,8 +12,13 @@
function read_msg_body($off, $len, $id)
{
if ($off == -1) { // Fetch from DB and return.
- # fetch from master, since data is read after a new post
- return q_singleval('SELECT /* USE MASTER */ data FROM {SQL_TABLE_PREFIX}msg_store WHERE id='. $id);
+ # fetch from master if not a search, since data is read after a new post
+ if($GLOBALS['t'] == 'search') {
+ return q_singleval('SELECT data FROM {SQL_TABLE_PREFIX}msg_store WHERE id='. $id);
+ }
+ else {
+ return q_singleval('SELECT /* USE MASTER */ data FROM {SQL_TABLE_PREFIX}msg_store WHERE id='. $id);
+ }
}
if (!$len) { // Empty message.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
) 1 Vote
]