Topic: Use of several SQL servers
We want to put the searches on a separate SQL server... It would be great if the code could be in the mainstream 
Is this a feature someone has already needed/used?
Re: Use of several SQL servers
search? It seems like that's a two liner: close the existing database connection and open a new one on your separate MySQL server.
- sirena
- Senior Member
- Offline
Re: Use of several SQL servers
I'm curious: what would the architecture of the site need to be to accomplish this?
Also couldn't you just 'load-balance' MySQL across 2 servers?
Re: Use of several SQL servers
Sirena > several things. "Load balancing" could be achieved with mysql proxy, but it's only alpha quality for now.
Then, punbb's search is quite poor (huge tables, lineary growing requests, no weighting of results) - it takes a long time to execute, and therefore locks several tables. If someone posts at the same time someone is searching, nobody will be able to even browse the forum before the search is ended. By putting the searches on another server, we hope to get rid of these locks, before we develop some search based on lucene / sphinxsearch / whatever.
Re: Use of several SQL servers
Yann: would my solution work?
Also, have you tried the fulltext search mod for 1.2?
- elbekko
- Senior Member
- Offline
Re: Use of several SQL servers
Opening a second connection would indeed be best for this. Don't forget to do it on every page that modifies the search tho.
Re: Use of several SQL servers
Bekko: That's not true 
I'm assuming this is a master/save setup, with one of the slaves being used exclusively for search. You would still write to the master.
- elbekko
- Senior Member
- Offline
Re: Use of several SQL servers
Erm, if your search tables are on a separate server, you have to make a new connection to that server to search/update the search.
Oh, and the fulltext mod can't really be trusted as it has proven to have quite a bit of issues with permissions.
Re: Use of several SQL servers
elbekko wrote:
Erm, if your search tables are on a separate server, you have to make a new connection to that server to search/update the search.
Ah, my understanding was that he wanted to put search *requests* on a different server, since that's where the evil, difficult queries are.
elbekko wrote:
Oh, and the fulltext mod can't really be trusted as it has proven to have quite a bit of issues with permissions.
Bah, yet another thing to put on my todo list to look at 
Re: Use of several SQL servers
Someone posted a fix to the permissions issue in that mod very similar to the one I just coded
Re: Use of several SQL servers
Hey Smartys, yeah, the mod has been around for quite a long time, but I was made aware of the security issues, which stopped my from deploying it. I would prefer wait for either a search coded with sphinxsearch/htdig/lucene (I am getting told we're getting closer) or a more tested search in 1.3