FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » General » Database discussions » MYSQL database slave support (Patch included)  () 1 Vote
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
MYSQL database slave support [message #164998] Wed, 13 April 2011 12:35 Go to next message
eclipsewebmaster is currently offline  eclipsewebmaster   Canada
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
I'm preparing to move fudforum onto our database cluster, but it currently lacks slave support.

Attached is a rough patch (Against 3.0.2) that seems to cover the most of it. I tie into FUD_OPT_4 for Use Slave Yes/no. The slave algorithm essentially sends all SELECT queries to the slave, except a) if SELECT /* USE MASTER */ is specified in the query, or b) if db_locked() since that assumes a transaction is taking place.

It would be nice to query the slave to see if it's lagged, but for Bugzilla I typically do that with a shell script and it works quite well, so I'll likely do the same here.

Note: I have not tested this with mysqli.
  • Attachment: patch.txt
    (Size: 6.90KB, Downloaded 465 times)
Re: MYSQL database slave support [message #165002 is a reply to message #164998] Thu, 14 April 2011 12:35 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Looks like a really well prepared patch!

I would appreciate if you (or anyone else interested) can use if for a couple of weeks and sort out all the issues before we commit it (I can, unfortunately, not test it myself).

Re: MYSQL database slave support [message #165008 is a reply to message #165002] Fri, 15 April 2011 07:15 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   Canada
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
I'll be upgrading the Eclipse forums from 2.8.1 to 3.0.2 sometime next week, and I'll use the slave db patch. I'll let you know how it works.
Re: MYSQL database slave support [message #165082 is a reply to message #165008] Fri, 22 April 2011 01:57 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Variation of your patch was committed:
http://fudforum.svn.sourceforge.net/fudforum/?rev=5216&view=rev
Re: MYSQL database slave support [message #165199 is a reply to message #165082] Mon, 09 May 2011 08:26 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   Canada
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
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 447 times)
Re: MYSQL database slave support [message #165200 is a reply to message #165199] Mon, 09 May 2011 08:57 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Thanks! Patch was committed, with the exception of "SELECT id FROM {SQL_TABLE_PREFIX}mod..." (table not being updated).

Details @ http://fudforum.svn.sourceforge.net/fudforum/?rev=5243&view=rev
Re: MYSQL database slave support [message #165201 is a reply to message #165200] Mon, 09 May 2011 11:45 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   Canada
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
Here's what I think is a final patch. This ensures that session data, as well as all the data loaded after a posted message is fetched from the master DB.

With this, Slave DB support for MySQL should be pretty solid for user functions. I haven't looked at anything in the admin area.
  • Attachment: patch.txt
    (Size: 10.94KB, Downloaded 452 times)
Re: MYSQL database slave support [message #165203 is a reply to message #165201] Mon, 09 May 2011 23:02 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
How about sending all queres during POST requests to the master? That will allow us to remove most of these hints. For example, in db.inc:

-	/* Connect to slave, if specified. */
-	if (!empty($GLOBALS['DBHOST_SLAVE_HOST'])) {
+	/* Connect to slave, if specified and not a POST request. */
+	if (!empty($GLOBALS['DBHOST_SLAVE_HOST']) && !$GLOBALS['is_post']) {

Re: MYSQL database slave support [message #165204 is a reply to message #165203] Tue, 10 May 2011 06:59 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   Canada
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
That would send search queries to the master as well, and those are the biggest ones I'd want my slave to handle.

With the patch in my previous comment, I think we're in good shape. I've even intentionally lagged my slave server for minutes and the forum still operated as expected.
Re: MYSQL database slave support [message #165208 is a reply to message #165204] Tue, 10 May 2011 12:14 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
It makes sense. We'll continue with your patches then.
Re: MYSQL database slave support [message #165217 is a reply to message #165208] Thu, 12 May 2011 06:31 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
I've just checked: the search function uses GET and not POST requests.
Would it be possible to validate the "is_post" patch on your system?
Re: MYSQL database slave support [message #165232 is a reply to message #165217] Fri, 13 May 2011 07:48 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   Canada
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
naudefj wrote on Thu, 12 May 2011 06:31
Would 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 06:05 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   Canada
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
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 #165250 is a reply to message #165246] Mon, 16 May 2011 23:05 Go to previous messageGo to next message
Ernesto is currently offline  Ernesto   Sweden
Messages: 413
Registered: August 2005
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
I would just like to point out, that REPLACE INTO is my favorite MYSQL command!

Re: MYSQL database slave support [message #165251 is a reply to message #165250] Tue, 17 May 2011 00:45 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Ernesto wrote:
I would just like to point out, that REPLACE INTO is my favorite MYSQL command!


Yea, and until all other databases support it, it is my worst nightmare!
Re: MYSQL database slave support [message #165570 is a reply to message #165251] Tue, 19 July 2011 03:31 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Where are we with this? We need to wrap this up before we release a stable 3.0.3 release.
Re: MYSQL database slave support [message #165594 is a reply to message #165570] Sat, 23 July 2011 04:49 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
I've been running all the patches I've submitted here at eclipse.org and it seems to work well. While troubleshooting, I had to disable slave support, and load to my master increased noticeably, so for large installations slave support is definitely useful.
Re: MYSQL database slave support [message #168516 is a reply to message #165594] Mon, 13 May 2013 13:44 Go to previous messageGo to next message
ew is currently offline  ew
Messages: 2
Registered: May 2013
Location: Ottawa, Canada
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 >= Cool {
- $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.
Re: MYSQL database slave support [message #168517 is a reply to message #168516] Mon, 13 May 2013 13:47 Go to previous messageGo to next message
naudefj is currently offline  naudefj   
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Just checked, your account is definitely not blocked. Will send you and E-mail to discuss.
Re: MYSQL database slave support [message #168821 is a reply to message #168517] Sat, 27 July 2013 05:23 Go to previous message
naudefj is currently offline  naudefj   
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Changes committed @ http://sourceforge.net/p/fudforum/code/5628/
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Insert html content as a value into mysql database?
Next Topic: mssql 2012
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Oct 21 02:58:17 EDT 2017

Total time taken to generate the page: 0.00970 seconds