|
|
|
|
|
|
|
|
| Re: MYSQL database slave support [message #165199 is a reply to message #165082] |
Mon, 09 May 2011 12:26   |
eclipsewebmaster
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
|
Member |
|
|
I finally managed to get our forum upgraded to 3.0, then 3.0.2
The slave patch works, but I was getting many messages sent to the moderation queue since a few occurrences of INSERT followed by a SELECT would occur too fast for the slave to have the data.
Attached is another patch which adds /* USE MASTER */ in many of those cases where the most up-to-data data is needed.
At this time, it works reasonably well, and we're using the master+slave setup. I will need to do a bit more work to make it more robust for sessions, since if the slave gets lagged too far behind, we get the message "FATAL ERROR: No session, cannot update status!". I suppose a session is created (on master) then re-read (from the lagged slave) and since it's not there yet, an error is thrown. If you have hints as to where to look, it would be appreciated.
In the future, we (you?) will need to remember that any read-after-write operations need to be explicitly sent to the master... although, at this point in the Forum's life, I'm guessing there won't be many new instances of this behaviour...
-
Attachment: patch.txt
(Size: 9.25KB, Downloaded 1557 times)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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: 0
|
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.
|
|
|
|
| Re: MYSQL database slave support [message #165246 is a reply to message #165232] |
Mon, 16 May 2011 10:05 |
eclipsewebmaster
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
|
Member |
|
|
If the slave was lagged even slightly, some search queries were inserted as duplicates, which causes the slave to break. The patch below uses REPLACE INTO instead of INSERT INTO, which will never cause an error. Furthermore, it eliminates one database query.
### Eclipse Workspace Patch 1.0
#P fud
Index: FUDforum/src/search.php.t
===================================================================
RCS file: /cvsroot/foundation/fud/FUDforum/src/search.php.t,v
retrieving revision 1.2
diff -u -r1.2 search.php.t
--- FUDforum/src/search.php.t 9 May 2011 13:39:39 -0000 1.2
+++ FUDforum/src/search.php.t 16 May 2011 13:18:47 -0000
@@ -63,11 +63,9 @@
/* Remove expired cache entries. */
q('DELETE FROM {SQL_TABLE_PREFIX}search_cache WHERE expiry<'. (__request_timestamp__ - $GLOBALS['SEARCH_CACHE_EXPIRY']));
- if (!($total = q_singleval('SELECT count(*) FROM {SQL_TABLE_PREFIX}search_cache WHERE srch_query=\''. $qry_lck .'\' AND query_type='. $qt))) {
- q('INSERT INTO {SQL_TABLE_PREFIX}search_cache (srch_query, query_type, expiry, msg_id, n_match) '.
- q_limit('SELECT \''. $qry_lck .'\', '. $qt .', '. __request_timestamp__ .', msg_id, count(*) as word_count FROM {SQL_TABLE_PREFIX}search s INNER JOIN {SQL_TABLE_PREFIX}'. $tbl .' i ON i.word_id=s.id WHERE word IN('. $qr .') GROUP BY msg_id ORDER BY word_count DESC',
- 500, 0));
- }
+ q('REPLACE INTO {SQL_TABLE_PREFIX}search_cache (srch_query, query_type, expiry, msg_id, n_match) '.
+ q_limit('SELECT \''. $qry_lck .'\', '. $qt .', '. __request_timestamp__ .', msg_id, count(*) as word_count FROM {SQL_TABLE_PREFIX}search s INNER JOIN {SQL_TABLE_PREFIX}'. $tbl .' i ON i.word_id=s.id WHERE word IN('. $qr .') GROUP BY msg_id ORDER BY word_count DESC',
+ 500, 0));
if ($forum_limiter) {
if ($forum_limiter{0} != 'c') {
|
|
|
|
|
|
|
|
|
|
|
|
| Re: MYSQL database slave support [message #168516 is a reply to message #165594] |
Mon, 13 May 2013 17:44 |
 |
ew
Messages: 2
Registered: May 2013
Location: Ottawa, Canada
Karma: 0
|
Junior Member |
|
|
Here's a patch against 3.0.2 that will force a few more queries to the master. If the slave was lagged/disconnected, we'd get a ton of duplicated posts.
If there are any admins there, I'd appreciate knowing why my account 'eclipsewebmaster' is permanently banned.
--- fudforum/include/scripts_common.inc 2011-10-04 11:47:19.000000000 -0400
+++ fudforumtest/include/scripts_common.inc 2013-05-10 14:22:01.000000000 -0400
@@ -12,14 +12,14 @@
function match_user_to_post($from_email, $from_name, $create_users, &$user_id, $reg_date=0)
{
/* Try to identify user by email. */
- $user_id = q_singleval('SELECT id FROM '. sql_p .'users WHERE email='. _esc($from_email));
+ $user_id = q_singleval('SELECT /* USE MASTER */ id FROM '. sql_p .'users WHERE email='. _esc($from_email));
/* If user was not found via email, try to look the user up by login. */
if (empty($user_id) && !empty($from_name)) {
if ($GLOBALS['FUD_OPT_2'] & 128) {
- $user_id = q_singleval('SELECT id FROM '. sql_p .'users WHERE alias='. _esc(make_alias($from_name)));
+ $user_id = q_singleval('SELECT /* USE MASTER */ id FROM '. sql_p .'users WHERE alias='. _esc(make_alias($from_name)));
} else {
- $user_id = q_singleval('SELECT id FROM '. sql_p .'users WHERE login='. _esc($from_name));
+ $user_id = q_singleval('SELECT /* USE MASTER */ id FROM '. sql_p .'users WHERE login='. _esc($from_name));
}
}
--- fudforum/include/theme/default/th_adm.inc 2013-04-29 15:43:30.000000000 -0400
+++ fudforum/include/theme/default/th_adm.inc 2013-05-10 14:01:50.000000000 -0400
@@ -69,7 +69,7 @@
$exp = db_all('SELECT /* USE MASTER */ fud_thread.id FROM fud_tv_'. $forum_id .'
INNER JOIN fud_thread ON fud_thread.id=fud_tv_'. $forum_id .'.thread_id
INNER JOIN fud_msg ON fud_thread.root_msg_id=fud_msg.id
- WHERE fud_tv_'. $forum_id .'.id>'. (q_singleval('SELECT seq FROM fud_tv_'. $forum_id .' ORDER BY seq DESC LIMIT 1') - 50).'
+ WHERE fud_tv_'. $forum_id .'.id>'. (q_singleval('SELECT /* USE MASTER */ seq FROM fud_tv_'. $forum_id .' ORDER BY seq DESC LIMIT 1') - 50).'
AND fud_tv_'. $forum_id .'.iss>0
AND fud_thread.thread_opt>=2
AND (fud_msg.post_stamp+fud_thread.orderexpiry)<='. __request_timestamp__);
@@ -158,9 +158,9 @@
if ((!$sticky && $iss) || $iss >=8) { /* Sub-optimal case, non-sticky topic and thre are stickies in the forum. */
/* Find oldest sticky message. */
if ($sticky && $iss >=  {
- $iss = q_singleval('SELECT seq FROM fud_tv_'. $forum_id .' WHERE seq>'. ($max - 50) .' AND iss>=8 ORDER BY seq ASC LIMIT 1');
+ $iss = q_singleval('SELECT /* USE MASTER */ seq FROM fud_tv_'. $forum_id .' WHERE seq>'. ($max - 50) .' AND iss>=8 ORDER BY seq ASC LIMIT 1');
} else {
- $iss = q_singleval('SELECT seq FROM fud_tv_'. $forum_id .' WHERE seq>'. ($max - 50) .' AND iss>0 ORDER BY seq ASC LIMIT 1');
+ $iss = q_singleval('SELECT /* USE MASTER */ seq FROM fud_tv_'. $forum_id .' WHERE seq>'. ($max - 50) .' AND iss>0 ORDER BY seq ASC LIMIT 1');
}
/* Move all stickies up one. */
q('UPDATE fud_tv_'. $forum_id .' SET seq=seq+1 WHERE seq>='. $iss);
--- fudforum/scripts/nntp.php 2012-03-16 15:11:58.000000000 -0400
+++ fudforum/scripts/nntp.php 2013-05-10 13:58:42.000000000 -0400
@@ -102,9 +102,9 @@
define('sql_p', $GLOBALS['DBHOST_TBL_PREFIX']);
if (is_numeric($_SERVER['argv'][1])) {
- $config = db_sab('SELECT * FROM '. sql_p .'nntp WHERE id='. $_SERVER['argv'][1]);
+ $config = db_sab('SELECT /* USE MASTER */ * FROM '. sql_p .'nntp WHERE id='. $_SERVER['argv'][1]);
} else {
- $config = db_sab('SELECT * FROM '. sql_p .'nntp WHERE newsgroup='. _esc($_SERVER['argv'][1]));
+ $config = db_sab('SELECT /* USE MASTER */ * FROM '. sql_p .'nntp WHERE newsgroup='. _esc($_SERVER['argv'][1]));
}
if (!$config) {
exit('Invalid NNTP identifier.');
@@ -126,7 +126,7 @@
}
/* Fetch forum options. */
- $frm = db_sab('SELECT id, forum_opt, message_threshold, (max_attach_size * 1024) AS max_attach_size, max_file_attachments FROM '. sql_p .'forum WHERE id='. $config->forum_id);
+ $frm = db_sab('SELECT /* USE MASTER */ id, forum_opt, message_threshold, (max_attach_size * 1024) AS max_attach_size, max_file_attachments FROM '. sql_p .'forum WHERE id='. $config->forum_id);
$FUD_OPT_2 |= 128; // Disable USE_ALIASES.
|
|
|
|
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
) 1 Vote
]