Print

[heinrich]

One problem I see....
"The mini allows for 100,000 documents/URLs to be stored in a collection"
Even if each thread counted as 1 document, newbie chat and the case modding forum together have over 120,000 threads....
While a google search appliance would work fine for 'normal' searches, the invision search engine allows for the 'view new posts' tool, searches for posts by certain users, allows mods to search by posting IP, etc..

A solution is being worked on to have a dedicated box for searching the sql db.

[kcornwell]

QUOTE(heinrich @ Jan 21 2006, 08:12 PM)

A solution is being worked on to have a dedicated box for searching the sql db.

Sweet.  

I use Invision on a couple sites.  What technical issues do you get with a large active message board?  Obviously search times are long.  How does setting up another box solve this?  Replicate the db for searching?  Play with the db indexes?

Thanks heinrich,
kc

[heinrich]

I would assume the main problem is - mysql's fulltext search sucks on a large database.  It's 'live' but takes a load of CPU time and locks writing.
We peon's arent told a lot of specifics, but I can only assume that xantium will setup a mysql slave that would be used solely for searching (and would no longer be real-time)

[gjm]

QUOTE(kcornwell @ Jan 21 2006, 10:55 PM)

They should wait for invsion to support mySQL 5.0.  With 5.0 you can use triggers (and stored proceedures).  Set them up to fire with the appropriate update, drop, insert statements.  Invsion has said it's coming soon.  However invision says a lot of things.  So who knows.   Other than using triggers I can only see that they would have to run some fairly complex scripts to keep everything in sync.  I'm sure someone out there in the Invision MB world has done this allready.  I would like to know how xbs is going about this.  

MySQL supports replication, it's a standard feature of RDBMS systems. The data would be automatically (and almost instantly) replicated to another box, and this box would handle all the search queries. Replication is a common way to perform backups / database server redundancy too. I don't see how support for triggers will help in this situation.


At the database admins: If you're not doing so already, it may be worth considering using InnoDB as the storage mechanism instead of MyISAM for your big tables - posts, sessions etc.

If there is an update/insert/delete query on a MyISAM table, the whole table is locked until that operation finishes. If there's a lot of concurrent users (you clearly do have!) then that is an awful lot of locks and a lot of people waiting around - hence a lot of connections left open and a lot of wasted resources.

InnoDB supports row-level locking, so only the row being altered is locked, leaving the rest of the table accessible to others. However InnoDB is really slow with searches.. So i'm not sure what's going to give you better performance, the resources saved by InnoDB might allow you to turn search back on.

I think a separate replication server for the searches is the best solution though, although not the simplest!

I've got a lot of experience with linux server administration if i can ever be of any help just ask..

[kcornwell]

Good to know.  Thanks for the info.