Search replication failing - disheartened

[WotC_Mel]

Hi,

I'm an admin for a large forum running 3.06 (patched for 3.07 security) and we have had to disable search. The vbulletin search grinds us to a halt and the full text is not cutting it either.

We have attempted to replicate the post table on a second server and have search run there to avoid the performance hit to the rest of the boards. The issue is that after two days of reasonable results, the post table corrupts and replication fails. This has now happened four different times. We had hoped this last time might work since MySQLs last release addressed some replication issues. (which is also why I am posting this here.)

So, is there anyone out there who is able to get replication running smoothly? Or is there another answer for search? At this point I am dispairing that we should attempt to move to Lithium forums which seems to be the only enterprise level solution that has workable search.

I will try to answer any questions about the set up, but may need to drag in my tech who can get specific.

-Mel

[Marco van Herwaarden]

What connection is between these 2 servers?
Is there any reason found that can be causing this corruption? Extreme high loads in traffic and/or server load on any of these 2 servers?
What are the server specs?

I realize that this is not a real help now, but vB3.5 will have buildin support for multiple database servers.

[WotC_Mel]

Thank you for replying.

Here are the answers, straight from my tech's email:

> What connection is between these 2 servers?
100 Mbits/s link. I have the ability to upgrade to 1000 Mbits/s if it will help.

> Is there any reason found that can be causing this corruption? Extreme high loads in traffic and/or server load on any of these 2 servers?
While there is a lot of traffic on the master database, generally the load doesn't go over 4, with the average usually under 1.
Apache runs on a separate machine which frequently has extremely high load levels.

> What are the server specs?
Master and Slave run on dual processor 2.4 GHz Xeon with 2GB of memory on a hardware RAID 1 system partition and a hardware RAID 5 data partition.

I am hopeful in many ways for 3.5 release and am monitoring that discussion, however not a lot of it leads me to believe that "big boards" are going to experience significant performance help. I hope I am wrong, but I feel like I am trying to get a sqare peg to fit a round hole in terms of scalability.

-Mel

[Marco van Herwaarden]

Did you ever monitor the long running queries. I know for big boards, adding some extra indexes can just make the difference.

I would love to give you an easy solution, but there is not a single way a problem like this can be resolved, it would take some serious testing and investigating to find where you can still gain some performance. Maybe you just need a litle relieve of the server to avoid those corruptions, maybe it will just not work with the hardware. Very difficult to give a generic answer.

[Marco van Herwaarden]

PS Re-reading your first post. Did you only replicate the post table? And is this still while running full text search?

[WotC_Mel]

We have done a number of optimizing methods and do look at the long queries time. I'll ask the tech if anything is standing out at the moment.

As far as it not being one thing, I figured. However I was hoping others had had a replication issue and could give some stories about how they made it work. I have seen other admins talk about offloading search onto another server.

And I am very willing to try and have someone with experience look at the situation for a price. It would take some time to get my company to cough up the money, but it could be done.

-Mel

PS Re-reading your first post. Did you only replicate the post table? And is this still while running full text search?

Ah, I think yes. I will double check.

PS Re-reading your first post. Did you only replicate the post table? And is this still while running full text search?


Checked and he said:

No, we are replicated the entire DB. Yes, running full text search.