Use a different database server ONLY for search - where to change?

[nicor2k]

SMF Version: SMF 1.0.9
This may sound a bit weird

Out of performance issues i want to use a different database server for the search function then for the rest of the board.

So I thought it would be enough to just set new server- and login-data in the /sources/search.php.

It works when i just output the variable "$db_server" in the plushsearch() function.
But... where do I now set the new server variable, that it is ONLY used in the search function?

thanks!   

[Kindred]

Do you have root access to your server?

I suggest you try the Sphinx Search module...

[nicor2k]

you mean "http://www.sphinxsearch.com/"? Sounds like an interesting upgrade, but for a quick 'n dirty solution i thought my idea is easier and faster to implement... SMF Searches (1.0.9) are taking to long on MySQL at the moment...  

[Kindred]

well, I suggest that you upgrade to 1.1.2 and install the sphinx search...

http://www.simplemachines.org/community/index.php?topic=127672.msg882047#msg882047

[nicor2k]

as mentioned in my other topic (http://www.simplemachines.org/community/index.php?topic=161421.0;topicseen) i am afraid that the update from 1.0.9 to 1.1.2 with all my own modifications takes too long.

I first have to install it and test the update on a backup server  

[nicor2k]

I had an idea to solve my problem with the two databases and it sounds first like a cool idea: This way you could switch which parts are running on one server, and which on the other

unfortunately, i am stuck in a "header send" error and don't find a solution...


Here my idea, maybe someone has a clue *hope*

in the smf/index.php, we have this code: 

Code Select Expand

// Connect to the MySQL database.
if (empty($db_persist))
$db_connection = @mysql_connect($db_server, $db_user, $db_passwd);
else
$db_connection = @mysql_pconnect($db_server, $db_user, $db_passwd);


soooo, i tried to use the $_REQUEST['action'] to specify a new address for $db_server:

Code Select Expand


// Connect to the MySQL database.
if ($_GET['action'] == 'search') { $db_server = '123.456.7.8';}
if (empty($db_persist))
$db_connection = @mysql_connect($db_server, $db_user, $db_passwd);
else
$db_connection = @mysql_pconnect($db_server, $db_user, $db_passwd);


But... this is producing the following error, even if i don't make any printing etc:

Notice: Undefined index: action in index.php on line 79
session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at index.php:79)

[Sarge]

Hi, I'm browsing through older topics to see if they're resolved.

Do you still need assistance with this?

[nicor2k]

Hi Sarge, thanks for assistance!

Since it is already quite a long time, I happily solved the problem mainly with the shown code - I have no clue what it was, but I can post the working code if anyone is interested?

But I have another question lately
http://www.simplemachines.org/community/index.php?topic=181335.msg1153690#msg1153690

[Sarge]

Since it is already quite a long time, I happily solved the problem mainly with the shown code - I have no clue what it was, but I can post the working code if anyone is interested?

Sure, it would be great!

[nicor2k]

sorry it took me so long for replying - here's the code that works for me:


1) First of all, we need the file /SMFforum/index.php
2) There, we have to find this part (at line 60-80, i think):

Code Select Expand


// Connect to the MySQL database.
if (empty($db_persist))
$db_connection = @mysql_connect($db_server, $db_user, $db_passwd);
else
$db_connection = @mysql_pconnect($db_server, $db_user, $db_passwd);


Now my code, which lets you choose which action shall run on which server:

Code Select Expand


if(isset($_GET['action']) && ($_GET['action'] == "search" || $_GET['action'] == "search2")) { $db_server = '123.456.7.8'; }


This should come after the comment, and - of course - before the connect and the "if (empty" thing. So you can make a little load-balancing, letting longer sql-queries run on another server to keep the rest fast. As I said, it works fine for me, if there are better ways or suggestions - please tell :-)

I only change the value for the $db_server variable, which is set and loaded from Setting.php. Keep in mind that you need the same User and Username for your second SMF-DB on the 2. server as on the first, otherwise you need to change more variables. (And the 2. Server should have an actual copy of the first servers DB...)

[Sarge]

Thanks for sharing your solution.

[nicor2k]

I hope it helps someone! 

[pcigre]

well, I suggest that you upgrade to 1.1.2 and install the sphinx search...

http://www.simplemachines.org/community/index.php?topic=127672.msg882047#msg882047

Please move this topic so all can read it... Users will smaller boards like 200-500k posts can benefit from this too.