Search disabled?

Kinetic^ · 20-10-2009 10:19am

Jumpy wrote: »

Personally I would rather have a slow site that sometimes errors than have no search.
I can understand a week after a major failure, but to be fair this is getting silly.

Making the site slow just so we can search is not going help anyone. It will just slow things down even further with people constantly pressing F5 due to the impatience of not being able to wait for pages to load.

DublinWriter · 20-10-2009 10:29am

I don't think that I'm the only 'New Posts' junkie on here and since search was disabled I don't find myself using boards half as much.

I think the Directors of Boards Ltd should have an extraordinary crises meeting over their losing .00003c per day due to my decreased site activity.

Jumpy · 20-10-2009 10:39am

Conor wrote: »

We're open to helpful, knowledgeable suggestions as to how we might get it working. If you're better at this stuff than we are, I'm all ears.

You havent really posted up enough to work on to be fair. You said the search function unbalances the site, yet other sites running the same software have been posted up with more activity and no publicly known issues, the difference for those sites is more hardware.

*this is not a sarcastic post this is entirely serious* - If you need help with the problem then create a post asking for ideas. There is a massive range of skills on this board.

What had the highest load when search was active?

IO?
CPU?
Memory?

Do you have the funds to add servers to the cluster? Is there a cluster?
Would solid state log drives be possible if its an IO issue?
Can you upgrade the servers if it is CPU or Memory or would you need to replace them?

Can you turn off the lesser used search functions to lower the load?

EDIT: Just saw Thaeds post saying its not hardware. NM

Conor · 20-10-2009 11:35am

Jumpy wrote: »

Can you turn off the lesser used search functions to lower the load?

Before:

Master => {All reads and writes EXCEPT search and modutils}
Slave => {search and modutils}

Now:

Master => {All writes and time-sensitive reads}
Slave => {The rest of the reads}

There's spare capacity on the slave, but no amount of spare capacity is enough for search. It's the character of the queries that matters, not the amount. Any query that takes more than 5-10 seconds is unacceptable on the database that handles most of our reads. Some searches take 90+ seconds.

Plan A above is to use an existing machine as a second slave to handle search. This has already been tried and failed due to insufficient RAM. More RAM is on the way and we'll try again.

Plan B above is to add another new, dedicated slave machine to handle search. This is on order but it's considered plan B because I want to try and get a temporary situation in place so that we don't need to wait until that's up and running.

Plan C above is to replace vBulletin's search with another solution altogether. Like Plan B, this is going to happen anyway and a prototype of this is in progress at the moment. I wouldn't hold your breath though, it's a tricky enough project.

Plan A and B are essentially a case of throwing hardware at a software problem. Kinda dumb, but hopefully it will tide us over until a full solution for search is arrived at.

The other two problems we're having is that the master is under-performing for no diagnosable reason (hence the reason we are sending so many reads to the slave) and the slave is deadlocking on some queries several times per day. Fixing search is only one part of three interlocking complex problems. Most of our time is sucked up trying to fix/work around those problems rather than fixing search. If all we had to do was fix search, we would have been done weeks ago.

The only person that would be useful to me right now would be someone who knows MySQL performance tuning inside out. If you haven't successfully run a large scale MySQL installation then it's not worth our while briefing you on the specifics, that time would be better spent getting actual work done. We've already got some of this help, but more would be welcome iff the benefit is worth the cost.

Jumpy · 20-10-2009 11:48am

Phasing out the initial master is not an option no?
I dont work with MySQL but one of the things I generally try in an underperforming environment is to move the system onto another piece of hardware to essentially start fresh. This can only be done in systems that support such a move obviously but can you add a third machine as a slave and move the masters functions to the current slave and promote?
If the underperforming issues you are referring to follow the hardware then at least you have crossed one possible reason off the list.

I understand where this may work for AD dc's or a VMWare environment it may be absolutely no use for MySQL. Its just an idea and if it helps then great.

seamus · 20-10-2009 11:49am

Just coming up with annoying queries now, but is there any way of categorising the searches to split out those which are easily indexable from those which aren't?

The "New posts" search for example, is* basically just a retrieval based on an indexed timestamp. The same for "My posts" and so forth. Not very taxing or time-consuming on properly indexed tables.

Text searching within posts and thread titles is an entirely different kind of search better handled by a good text indexer, like google.

I make this comment entirely out of selfishness. I could happily live on Google's text search for the next year if only I got "New posts" back

*I use "is" to mean "should be"

Quazzie · 20-10-2009 11:59am

seamus wrote: »

I make this comment entirely out of selfishness. I could happily live on Google's text search for the next year if only I got "New posts" back

Just click on the Boards.ie sign at the top left and it brings you to http://www.boards.ie/? page which has all the new posts on it.

seamus · 20-10-2009 12:09pm

Quazzie wrote: »

Just click on the Boards.ie sign at the top left and it brings you to http://www.boards.ie/? page which has all the new posts on it.

Yes, but it doesn't have the last 500 new posts in it.

Conor · 20-10-2009 12:21pm

Jumpy wrote: »

Phasing out the initial master is not an option no?
I dont work with MySQL but one of the things I generally try in an underperforming environment is to move the system onto another piece of hardware to essentially start fresh. This can only be done in systems that support such a move obviously but can you add a third machine as a slave and move the masters functions to the current slave and promote?
If the underperforming issues you are referring to follow the hardware then at least you have crossed one possible reason off the list.

I understand where this may work for AD dc's or a VMWare environment it may be absolutely no use for MySQL. Its just an idea and if it helps then great.

To replace the current master we need to wait until the plan B hardware arrives. We don't have that kind of capacity sitting spare ready for such an eventuality (although it's a direction we're moving in).

The last time we flipped the master and slave we had a 15 hour session in the datacentre and a 5 hour hard downtime. That, and the master has faster hardware than the slave anyway.

Trust me, we've thought of those options and they suck more than our current ones.

seamus wrote: »

Just coming up with annoying queries now, but is there any way of categorising the searches to split out those which are easily indexable from those which aren't?

The "New posts" search for example, is* basically just a retrieval based on an indexed timestamp. The same for "My posts" and so forth. Not very taxing or time-consuming on properly indexed tables.

Text searching within posts and thread titles is an entirely different kind of search better handled by a good text indexer, like google.

Separating out that stuff is 1-2 man days of rewriting search.php. We don't have that kind of time spare right now.

seamus wrote: »

I make this comment entirely out of selfishness. I could happily live on Google's text search for the next year if only I got "New posts" back

Use the front page.

Quazzie · 20-10-2009 12:31pm

Conor wrote: »

Use the front page.

Is there anyway to add an icon to the new posts on the front page so it brings you to the last post rather than the OP?

seamus · 20-10-2009 12:32pm

Use the front page.

Harsh

Quazzie wrote: »

Is there anyway to add an icon to the new posts on the front page so it brings you to the last post rather than the OP?

If you click on the number of posts beside the thread title it brings you to the first new post in the thread.

Conor · 20-10-2009 12:32pm

Quazzie wrote: »

Is there anyway to add an icon to the new posts on the front page so it brings you to the last post rather than the OP?

Click on the number after the thread title rather than the thread title itself.

Quazzie · 20-10-2009 12:37pm

seamus wrote: »

If you click on the number of posts beside the thread title it brings you to the first new post in the thread.

Conor wrote: »

Click on the number after the thread title rather than the thread title itself.

Thanks

TimTim · 20-10-2009 7:41pm

Conor wrote: »

The other two problems we're having is that the master is under-performing for no diagnosable reason (hence the reason we are sending so many reads to the slave) and the slave is deadlocking on some queries several times per day. Fixing search is only one part of three interlocking complex problems. Most of our time is sucked up trying to fix/work around those problems rather than fixing search. If all we had to do was fix search, we would have been done weeks ago.

While I'm no mySQL expert I've used this before to get a rough idea of where I'm going wrong

http://www.day32.com/MySQL/tuning-primer.sh

When dealing with issues like this I always try go back to basics. What changed in the 24 hours previously before the performance going to crap?

If you are using RAID did a drive fail and you haven't been notified and the array is degraded?

Has the box started swapping a lot? etc etc.

Dudess · 20-10-2009 7:47pm

Luddite here but anyway:

Presume clearing out unused accounts/ancient threads wouldn't make a difference?

Also, how do you use the Google search in Gordon's announcement? A couple of people have said it's really good, but I'm not seeing it. Yes, I have pressed the button

but is there anything else I should be doing?

sceptre · 20-10-2009 7:58pm

Dudess wrote: »

Also, how do you use the Google search in Gordon's announcement? A couple of people have said it's really good, but I'm not seeing it. Yes, I have pressed the button but is there anything else I should be doing?

Naw, that's pretty much it. Obviously you've first entered what you want to search for I assume:) It's the same search you get on the right hand side of the page when you open the main boards page.

Dudess · 20-10-2009 8:09pm

sceptre wrote: »

Obviously you've first entered what you want to search for I assume:)

Conor · 20-10-2009 8:11pm

TimTim wrote: »

While I'm no mySQL expert I've used this before to get a rough idea of where I'm going wrong

http://www.day32.com/MySQL/tuning-primer.sh

I've been using several similar scripts.

TimTim wrote: »

When dealing with issues like this I always try go back to basics. What changed in the 24 hours previously before the performance going to crap?

Lots of things changed.

The current master used to be the slave. When the current slave was the master it performed better despite having slower processors. We can't just flip them back, because the slave (old master) was reinstalled during that maintenance period and hence we can't guarantee it would perform like it used to. Also, the slave has its own problems.

TimTim wrote: »

If you are using RAID did a drive fail and you haven't been notified and the array is degraded?

Has the box started swapping a lot? etc etc.

There's no IO issue at all. It just appears to use more CPU% than it should.

Dudess wrote: »

Luddite here but anyway:

Presume clearing out unused accounts/ancient threads wouldn't make a difference?

Nope, 'fraid not.

Dudess · 20-10-2009 8:17pm

:eek:

Look at that query of mine you quoted: "If you are using RAID did a drive fail and you haven't been notified and the array is degraded?

Has the box started swapping a lot? etc etc".

Have to say I'm pretty impressed with myself...

Conor · 20-10-2009 8:23pm

Dudess wrote: »

:eek:

Look at that query of mine you quoted: "If you are using RAID did a drive fail and you haven't been notified and the array is degraded?

Has the box started swapping a lot? etc etc".

Have to say I'm pretty impressed with myself...

You gotta give yourself more credit.

Gordon · 20-10-2009 8:23pm

Have you tried switching it off and on again?

Conor · 20-10-2009 8:23pm

Gordon wrote: »

Have you tried switching it off and on again?

We didn't have the choice several times.

Dudess · 20-10-2009 8:29pm

Seriously, there are so many thankable instances on Feedback - can we have thanks back here please?

I mean, you've already taken our search... :pac:

TimTim · 20-10-2009 8:49pm

Conor wrote: »

There's no IO issue at all. It just appears to use more CPU% than it should.

How much more CPU%? As in it gets to 99% and seems to get stuck at that until you kill it?

Have you tried strace the process when it starts eating CPU and see if its doing anything funky in the backround?

Conor · 20-10-2009 9:03pm

TimTim wrote: »

How much more CPU%? As in it gets to 99% and seems to get stuck at that until you kill it?

Nope, it still has plenty to spare, just it's 50-75% higher CPU utilisation than a similar machine with slower processors. (Xeon E5420 on the master v Xeon E5320 on the slave.)

Despite the faster processors and higher utilisation, it clears queries at a lower rate than the older machine. This would normally point to IO issues, but

TimTim wrote: »

Have you tried strace the process when it starts eating CPU and see if its doing anything funky in the backround?

If it was spiking and then dying I'd consider it, but it's an across-the-board slowdown. I'd also be very hesitant to do that on a production machine.

TimTim · 20-10-2009 9:47pm

Conor wrote: »

Nope, it still has plenty to spare, just it's 50-75% higher CPU utilisation than a similar machine with slower processors. (Xeon E5420 on the master v Xeon E5320 on the slave.)

Despite the faster processors and higher utilisation, it clears queries at a lower rate than the older machine. This would normally point to IO issues, but

I'm guessing there isn't any individual mysql threads getting stuck or some weird process running?

I know I had a box which when a certain db was queried a mysql thread would eat CPU time for breakfast and never finish the query. The only solution I found was to drop the entire database and import it again, brutal solution but it worked.

Conor wrote: »

If it was spiking and then dying I'd consider it, but it's an across-the-board slowdown. I'd also be very hesitant to do that on a production machine.

Anything unusal appearing in 'show processlist' or whatever the correct mysql command is?

If you are looking to try a whole new direction you may consider one of the mysql builds from here: http://ourdelta.org/ it has a good few patches from facebook/google etc. and tries fix some of the scaling issues mysql has.

Unless something tweaks in my brain later on, I'm afraid thats all I can offer!

Conor · 20-10-2009 10:20pm

TimTim wrote: »

I'm guessing there isn't any individual mysql threads getting stuck or some weird process running?

I know I had a box which when a certain db was queried a mysql thread would eat CPU time for breakfast and never finish the query. The only solution I found was to drop the entire database and import it again, brutal solution but it worked.

Anything unusal appearing in 'show processlist' or whatever the correct mysql command is?

If you are looking to try a whole new direction you may consider one of the mysql builds from here: http://ourdelta.org/ it has a good few patches from facebook/google etc. and tries fix some of the scaling issues mysql has.

Unless something tweaks in my brain later on, I'm afraid thats all I can offer!

It's not any one query getting stuck, it's all queries running slower than expected. There's nothing else running on the machine. We have detailed frequency data for each query running on the machine (thanks to the lads at daft - they had a handy script) and the slow query log is pretty close to empty.

The "one query getting stuck" problem is on the slave, but that's looking more and more like a concurrency bug in MySQL. Queries get wedged in the "Sending data" state (with a frequency of roughly 1/25000000) and cannot be killed except by kill -9'ing the mysqld process. I'm hunting the cause, but it's taking time to collect enough data to figure it out.

randylonghorn · 21-10-2009 12:24am

I now have a headache just from reading this ... sincere sympathies to Conor and Ross!

GLaDOS · 21-10-2009 12:39am

Gordon wrote: »

Have you tried switching it off and on again?

Is it definitely plugged in?

ynotdu · 21-10-2009 1:32am

"Good Morning,how can I help You today?what is Your service tag?"
"wait just one moment please,I will transfer You to the right department"

"Good Morning,how can i help You today?what is Your service tag?"
"wait just one moment please,I will transfer You to the right department"

"Good Morn...........

Get the idea Conor?
Just a suggestion but could You make this Your Default reply{a sticky may'be?:))

Yeah let up people and let the guy do his job ffs!

Search disabled?

Comments