Last 300 Posts

MSE_Martin · 2 January 2007 at 1:00PM

The Last 300 posts search is still reeking havoc on the servers I'm afraid. Even after the server upgrade, it seems the database struggles whenever it needs to search more than 3,000,000. We think this is one of the reasons we're still getting database errors.

Therefore, hopefully this week, to help we're going to take

The MS Arms, DT, Funny Money, Forum Policy out of the Last 300 posts search (and the unregistered users search) and see if it works.

If that does make a difference, but people are missing the search, we'll see if we can build a 'Last 300 MS Arms' specific search which searches all those boards and will be placed in the resource bar in those boards.

In the meantime can I ask regulars please to use this function sparingly... it'd really help.

Martin

steve_r123 · 2 January 2007 at 1:27PM

The currently active users list at the bottom of the main forum page is usually a resource hog on these vBulletin forums. Just a suggestion of making that visable to administrators only and see if that helps?

sra · 2 January 2007 at 4:25PM

I didn't realize people still used the 300 posts thingy.

It was great in the old Yabb days to have something like that where you could check that kind of list a few times a day and effectively read every post on the whole site, but a forum of the this size who'd want to do that?

I wonder if it's a very small number of people using it a lot these days

Former_MSE_Webmaster · 2 January 2007 at 5:31PM

Hi folks,

We're continuing to work on the new server and the search function but part of our work will require us to disable the site search for about an hour. We're expecting to be able to restore it between 5:30 and 6pm this evening.

Apologies for any inconvenience.

Webby

tiff · 2 January 2007 at 5:51PM

So if you're looking for the "new posts" link you wont find it because the search function has been taken offline.

jamesd · 2 January 2007 at 7:05PM

Martin. Webby, cutting those out should help. You might also put in:

GET_LOCK('search',10); whatever search does; RELEASE_LOCK('search');

to limit the server to one search at a time, with an orderly queue building up. You could use different strings if you think you can handle more than one concurrent search, perhaps one per web server.

You may also find that ALTER TABLE ... ORDER BY thread, post helps, if the posts have those fields and are normally stored in a MyISAM table in the order in which they are added. Doing the sort will be very slow but it should reduce disk seeks from crawlers and search by putting all posts in a retrieved thread in about the same physical position on disk. Don't even think of doing this on a live server - it'll take more time than OPTIMIZE TABLE.

Remember that the official advice from MySQL in this situation is "stop using MYSQL's built-in search, because you're chasing a performance target you will never be able to reliably reach if you continue to grow". If necessary, sponsor the development of a search using Sphinx for the message board software, it'll probably be cheaper than chasing performance with MySQL search.

For the current state of play, see the EuroOSCON 2006 presentation High Performance Full Text Search for Database Content by Peter Zaitsev and Vadim Tkachenko.

Hiring Peter and/or Vadim wouldn't be a bad idea if you want performance advice. Peter is fairly local to you and very capable.

Wikipedia abandoned MySQL search when it was using more than half the power of four database servers and still slow, at about 1000 Alexa worldwide rank. The index size was around 2.5GB at that point. Switched to Lucene, but Sphinx would be better for most places these days, unless you need support for lots of languages, as Wikipedia does. Lucene has stemming support for more languages.

Copying the search index to /dev/nul before starting up the database server can help, so can preloading the search index. Really, to do decently well, you need enough RAM to hold the whole text database and the whole search index in RAM. But you'll still fail to get decent performance long term, it's just a reasonable way to do it until you finally give up. Beats throwing human time at the problem, since RAM is cheaper than humans. Alternatively you can do what you're doing now and cut the searched text size, possibly splitting search across multiple servers each handling part of the text.

You will fail, and spend more and more of your time and resources babysitting search, if you continue to use MySQL's fulltext search. Sorry, that's just how it is. It's a very convenient and fine solution for low load and modest text volume, but you're no longer low load with modest text volume. Start doing something else now instead of a continuing with the MySQL fulltext solution which will never work for you again for more than short periods.

Former_MSE_Webmaster · 2 January 2007 at 7:48PM

Thanks for the input jamesd. As it happens, we're working towards a better search solution, most likely using Sphinx and we're hoping to get it going ASAP. It's just a matter of having the resources available to do it and I've only got a couple of humans to throw at things as it is.

You sound as though you've got some expertise in this area so please feel free to comment further. Technical suggestions are always welcome.

Cheers,

Webby

jamesd · 2 January 2007 at 7:58PM

You're on the right track, so good luck with a quick Sphinx implementation. If you can it would be helpful to say more about the comparative performance of the two solutions once you have the Sphinx one in place. It's an interesting area for quite a few people and knowing that it's effective will help to persuade others to do the right thing as well.

alchemista · 2 January 2007 at 10:43PM

I do production website operations support for a career (performance issues, software, etc). The #1 rule of diagnosing performance issues and optimizing performance is to profile the app first before just chasing down problems that "you know have to be it".

Have you profiled the app to find out where the problem is coming from using preferrably a software level profiler, otherwise having to resort to database level stats? Often people get too stuck on focusing on the database, when it's really a higher level app design issue.

I sent an email previously asking if you were looking for short term help on this, but got the automated reply. I hope it's fixed soon, I love the site!

jamesd · 2 January 2007 at 11:06PM

alchemista, in the past the search has been turned off and returned performance to reasonable levels, so it's been fairly well established that it is a problem, besides it being known as a generic issue for larger search corpus sizes with the fulltext search built in to MySQL.

On the general application side, if I recall correctly, they have already added multiple page builders to handle the traffic level.

alchemista · 2 January 2007 at 11:12PM

jamesd wrote:

alchemista, in the past the search has been turned off and returned performance to reasonable levels, so it's been fairly well established that it is a problem, besides it being known as a generic issue for larger search corpus sizes with the fulltext search built in to MySQL.

On the general application side, if I recall correctly, they have already added multiple page builders to handle the traffic level.

That's good evidence, when you say reasonable levels is it acceptable/fast? The search has always been dysfunctional on this site, probably my only complaint since it will often not even show results that you know match the search.

Do you have any other actual profiling output (be it database/app/etc) to pinpoint where the CPU and I/O time is being spent? Often the problem is much simpler once you have that, you save yourself jumping to conclusions, and you have evidence of what's really wrong. You also gain a better understanding of how the system operates under load so that you can make higher level design decisions.

Last 300 Posts

Comments

Confirm your email address to Create Threads and Reply

🚀 Getting Started

Categories

Is this how you want to be seen?

Get our FREE Weekly email full of deals & guides - and it’s spam-free