I, too, would like to get some technical feedback from those in the know, but I assume Jerry/Ed will get back to us just as soon as they have something to share.
Record locking is different from file locking, and the choice of which, if any, may be up to the vB/PHP programmer who calls SQL routines, or an internal setting in our particular SQL implementation. It’s possible file locking is being used when we could get by with less intrusive record locking.
Non-exclusive record locking should not prevent reading any record in the database but it will prevent modifing the one record. File locking, necessary to modify the length of the file to append a new record, is much more limiting to other users. Ideally, a “file locked” state is kept as brief as possible.
There is also the spectre of a “deadlock”, where one file is held locked waiting for some other operation to complete, and the other operation is waiting for the first one. This can bring everything to a screeching halt until some watchdog timer catches it, if we are lucky enough to have such a device.
Of course, we don’t yet know if this is the problem at all, but it could be. The symptoms are there.
The site IS the database. There’s almost nothing you can do here which doesn’t access the database in some way; actually I can’t immediately think of a single function which doesn’t. VBulletin is driven almost entirely by MySQL queries, and all the information you can access here is stored in the DB tables including post info, thread info, user info, profile info and scads of other stuff.
Well, the database and the site may be on the same server (though for security purposes, they shouldn’t be), but that still doesn’t make them the same thing. Like I said, slow data access will effectively slow down your access to the site. But the site is just a bunch of code pages. The database is an engine that controls a collection of data that is of interest to the site.
For examples of things that don’t access the database, you have the buttons, the page numbers, the style sheets, the tables and formatting, and really lots and lots of stuff. What happens is that the site server first puts the page together in terms of format, and then requests data from the database server. It fills certain container controls (like table cells, for example, or divs) on the page with the data it gets, and then dumps that whole thing to your PC.
If there is a lock on the database during a request for data, then the next request has to wait until the lock is released. In other words, if a table is locked while I search, you won’t get your page back from the PC until I’m finished. Now, there are various extents and ranges of locks. Sometimes, only a row is locked. And sometimes, as I illustrated earlier, nothing is locked. But not all DB engines do this the same way or with the same efficiency.
But if MySQL is able to dump data without locking, then the speed of the site server is a lot more critical to your surfing experience. Because the data are so text-rich, they’re probably stored in noncontiguous areas all over the disk while the actual table holds nothing more than pointers. And because MySQL is (as I understand it) not as sophisticated as SQL Server, for example, my bet is on the database slowing everything down.
Whether it can be tweaked depends on its features and on the technical expertise of its administrator(s).
Combination of read and write locks. If you’ve got the patience, this explains the issue:
Inherent problem with MySQL in a board our size. Jerry’s tweaked most of what can be tweaked. Next-level solutions are all pretty big projects, slave servers, hack in third-party search engines, etc. Got the lads looking into it. Any change needs to make business sense. Notwithstanding the occasional crash, the board works better since the server upgrade; remains to be seen whether further improvement worth the investment.
Two more questions if you would:
Is the upgrade for vBulletin Version 3.0.7 to 3.5 or better being considered?
It would seem that, that would provide a better platform for tweaking for improvements?
Is the old server still around and available for use? That could be a cheap way to set up a search only server.
I know this is only a small part of Jerry’s job, but it sounds like there might be some affordable fixes to the problems. A quick skim of the thread you linked seems to indicate Sphinx might be a solution if SDMB was on version 3.5 or better.
Like I say, got the lads looking into it. Out-of-pocket expense isn’t really the problem. It’s more you have to install box A, then upgrade to version B, then hack in product C, and hope it all works while fending off numerous PO’d parties wanting to know whether mission-critical weeks-overdue projects D, E, F, and G are going to get done before the whole company goes down the pipe. No doubt you’ve been there.
Ed – thanks for the info and the link; I’ll look at it later today during some coffee break (you posted at 11:20 PM my time so I’ve just seen it, and I’m now at work)
OK, I’ve trudged through that thread you linked, and part of a thread it links to - Sphinx Search - vBulletin.org Forum.
This is about a (free) product called “Sphinx” which they seem to like very much.
Would you consider adopting it on out board? According to what I read (and I did not go through all 25 pages of the VBulletin.org thread I linked to!) it seems to be pretty straightforward to implement, and very effective even on boards with over 20 million posts.
You know, when the word ‘abuse’ was used in there, along with ‘Charter Members/Members’, and an apology for the problems the bad apples were causing… well, I guess I expected something a bit more - oh, I don’t know, abusive, perhaps? Instead, what we have is a paying member - acting, presumably, with no ill intent - wishing to find something and taking eight attempts at it.
Not eighty searches, and not eight accounts spamming every few seconds…
One individual.
Eight searches.
In four minutes.
Excuse me if I think this is pretty frickin’ pathetic. Not just that it was enough to crash the server, but that EIGHT searches got a paying member branded as an Abuser of the System.
I don’t tend to weigh in on ‘this system sucks’ threads (feel free to Search on that and prove me wrong -* just don’t try it more than once*) but frankly: that sucks.
In WHAT portion of what Ed said is he branding the person who did the searches as “an Abuser of the System”???
He simply pointed out that what happened dropped the system. He also points out, if you take the time to read it, that the powers that be looked into the situation to see if it was an attempt at petty vandalism, and decided that it was the result of normal curiosity caused by a particular thread open at that time.
So, I’d suggest unruffling the feathers; nothing about what Ed said was in any way disparaging of the person involved. :smack:
On review, I gather you were talking about what [bjdavis** had posted. I’m not sure he was talking about the person who submitted the 8 searches in 4 min.; it might have been something earlier. And please note that Ed apologized for Jerry already in a later post.
