Hey man, you’re thinking too linearly. It’s a database, not Fort Knox.
I’m happy they’re taking it seriously and working on it. It’s prolly premature but this is the best performance I’ve had since I moved back from Los Angeles.
You’d give up your Charter Member status and cheap renewals for search?
The last time Jerry had to shut down the search he gave a dark little post that it was being “abused” and that soon they would track down the perps. It was then realized and revealed that it was normal Doper usage that caused the “attack”. That’s when they decided to leave it at 2 minute waits. No, it’s not Fort Knox, but it’s not much of a functioning database either.
Internet vBulletin messageboards generally have great performance. Since this is the first pay site I have suscribed to, it figures that this one would have the least amount of features and the most amount of downtime.
But hey, I wanted to read what real intellectuals have to say about life, so it’s been worth it!
For anyone who’s interested, I found the old post in which Ed Zotti detailed the precise problem our board is having, which is one that’s inherent to vBulletin (newer versions, too, so a simple upgrade is not going to cure things). More excruciating detail can be found at the vBulletin forums here.
Fairly obviously nothing has been done to fix this in the last year (short of trimming the database), although given the nature of the problem this isn’t entirely shocking. The solutions appear to involve either a third-party hack, or a slave server with a mirror of the database dedicated entirely to search. Neither of these are trivial undertakings (although I’d have thought that Sphinx search could’ve been implemented in this timeframe rather than the slightly finger-in-the-dyke style fixes that have been carried out instead).
Sphinx search sounds like it would indeed solve the problem.
Btw. setting up a replicated mysql slave takes about 5 minutes (given that the hardware and OS is setup of cause) + the intial copy of the database. Modifying vbulletin to direct searches to it will proberly take the longest, but should still not be a huge task.
Thank you for that, it made it easier to notice that Ed’s last activity here occurred about ten hours ago. .
BRB, I’m going to hunt down his update, I’ll link it when I find it.
Actually, Coldfire’s stepped down from his dyke fingering duties. 
Well, I don’t think it’s quite that trivial, but I realise it’s not technically tricky. It does involve buying a whole new server, however, which obviously doesn’t happen overnight around here. By contrast, the Sphinx mod would be a cheaper option but looks far more technically demanding, since the details of its implementation are far from set in stone - there’s a 30-something page thread at the vBulletin forums with users swapping various different techniques for its setup and use, including one guy charging thousands of dollars for his optimisation services.
Agreed about the server, but it actually is that simple. You copy the DB to the slave server, grant it the right privileges on the master, edit my.cnf and start them both (yes I have done it).
Ah, thank you Dead Badger. That clears things up immensely. I’ll summarize the problem for those of us who have no idea what a read-write lock is.
Think of a database as being like a room with a bunch of filing cabinets in it. The filing cabinets are called “tables”. Every form filed in a particular filing cabinet is called a row. One of the filing cabinets would be the labeled “threads” table. Every form(or row) in the threads table contains all of the information about a thread: the title of the thread, who the OP is, etc. There’s also a “posts” table. Each row in the posts table contains all of the information about a single post: the poster, the text, the thread it was posted in, the order(maybe it was the 14th post), etc. There are other tables, but these will suffice to describe the problem.
Now, when you want to read a thread, you make a request to the webserver. The webserver will go into the filing cabinets, find all of the posts associated with the thread you want to read, format them for you and send them back. If you want to post to a thread, the webserver receives your request, fills out a new form(row) describing the post that you just made, and puts it in the proper place in the filing cabinet.
But we have a problem. Lots of people are making requests to the webserver at any time. We need some rules to define how to share access. The first thing to note is that we can’t let somebody write to the database at the same time that somebody else is reading from it. If we let that happen, the person reading the database might see the half-completed “post” row that’s being inserted and get very confused. We also can’t let two people write to the database at the same time – both might decide that they’re posting the 16th post in the same thread, and then things would get very confused when everybody sees that there are two posts #16 in a thread. So we make a rule. If you want to read the database, you first have to look inside the room and see if anybody is already inside. If somebody’s there and they’re writing to the database, you have to wait outside until they’re done. If it’s empty or everybody inside is just reading, you can go right in. If you want to write, you can only go in if the room is empty.
But there’s still a problem. Suppose somebody is reading the database, and somebody else comes in and wants to do a write. He has to wait. Next, a second reader comes along and goes into the database(he can do this; there are only readers using the database right now). Then the first reader leaves. The writer still can’t go in because the second reader is still there. Then a third reader comes goes in. If things keep up like this, the writer could be delayed indefinitely. And if you think about how many people are reading the SDMB at the same time, you can probably see that this is pretty likely at peak times. So we make a new rule for readers: if there are any writers waiting in line, you have to go to the back of the line. So let’s summarize the rules:
Readers:
-if there are any writers waiting or using the database, you must go to the back of the line
-otherwise, you can go right in and use the database
Writers:
-if anybody is using the database, you must go to the back of the line
-otherwise, you can go right in and use the database
Hopefully, this is clear. This is how the database for the SDMB actually works. So now I can describe the problems that searches cause. The first thing to note is that searching takes a really, really long time in computing terms. I don’t know what algorithms are being used, but with 8 million posts, it’s going to take time. Of course, a search is a read operation. So here’s what happens:
Somebody starts a search.
Somebody else posts a new message.
Do you see the problem? A search is a read operation. Posting a message is a write operation. The search goes into the database right away, which means that the post must wait. And now, nobody else can get into the database for anything*. You want to view a thread? That’s a read operation, and a write operation is waiting. Get to the back of the line. Things really start to suck if somebody else issues a second search. The search has to wait at the back of the line. And suppose somebody else tries to post. The post gets to the back of the line. And then a bunch more view thread requests come in. They also get to the back of the line. Now, consider what happens when the first search finally completes. The first post message request goes through. And then the second search starts. Now the second post message request is at the front of the line. It has to wait for two searches to complete, and so do all of the “view thread” requests behind it.
If more and more searches come in, this will cause the queue to use the database to get longer and longer, and that means that the delay in posting and viewing threads gets longer and longer. If it gets too long, your web browser will timeout the request and give you an error.
So what’s the solution? Allegedly, having a second server dedicated to searches will solve the problem. I can certainly see how that would solve the problem for reading threads. But the search server would need to be kept up to date with all of the new posts coming in, which might still lead to timeouts when posting messages. But I don’t really know how MySQL replication works, so it’s entirely possible that a dedicated search would get rid of all of our issues.
- Technically speaking, nobody else can get into the database for anything that needs to use the posts table.
Ah, I missed the bit in your first post assuming the server and OS were set up, hence my quibble. Never budget less than a day (and several nearby breakable items) when setting up a new machine, that’s my motto. 
I’ve a speculative solution (i.e. out of my ass) to the search problem. If a dedicated search server + DB is not possible, then how about setting up the following system: each user can submit a certain number of search requests (search request quota). All of these requests go into a queue. The server is constrained by a global search query limit (say, 2 per minute, I don’t know). As each query is processed, the static HTML results get PMed to the requester. So, there’ll be a delay between query and results. But, at most, it should be on the order of a 5-6 minutes at the most, and typically, 2 minutes This is the SDMB, people can play in the Game Room till then.
There’s not much wrong with that solution, II Gyan II, but that would require extensive changes to the software. Jerry administers the servers; he doesn’t write the code – they bought an off-the-shelf product.
Repeat of prior disclaimer: while I am a database geek, I am not a SQL-based db geek.
This is not a possibility with or without record locking. This is a web-based db. Until the end user hits the “post” button the db hasn’t a single byte of what the member is posting. Those bits & bytes do not reside on the db server, only on the end user’s computer.
Even with a non-web-based db, like Filemaker in a multi-user environment over a WAN, until end user X commits the record they are creating & editing, those values do not get flushed back to server and written to the server’s HD. No other user has a ghost of a chance of seeing that data “in mid-edit”. Just doesn’t happen, can’t.
And record locking should totally not be necessary to perform searches. Edits, yeah. Searches, no.
I can see a (faulty) logic behind locking records for searches: “I don’t want to miss any info that someone might be creating right as I am conducting my search. And I don’t want to bring up something as if it were valid right as someone else is modifying or deleting it. Make all edits stop until my search is conducted”. Like, yeesh, lighten up, if your data is changing all THAT damn fast, your search result is only going to be valid for 0.3 seconds after you obtain it. Unless your registered users happen to be quantum subatomic particles and you need a frozen state anlysis that captures their position and you need it to be for the same time for the last recs in the set as for the first, c’mon, nothing in the world of data is that damn volatile!
There is no “needs to be” reason to lock records just to search them unless it’s a resource allocation decision: searches take up enough cycles, let’s make everyone else pause so the search can get the lion’s share of processor attention, or disk-access attention (more likely). And this, too, would seem to be a self-defeating strategy. Making everyone else wait means more of a logjam than if you just let searches transpire concurrently with all other read operations, including read-write operations.
And there’s no reason a record currently being written to should not be readable (it would be the data as it was just before editing of that record started, but that, the written-to-disk version, should be readable).
I’m not talking about record locking. What I mean is, once you hit the “Post Reply” button, the PHP frontend will issue an SQL INSERT command. While the INSERT command is being executed, no reads to the entire posts table are allowed.
Oh. Yeah, INSERT should definitely lock the rec between the time it starts and the time it completes execution. (in FmPro parlance we would say “during edit until the record is committed”). But SEARCHING should not lock records. And even a locked record should be searchable (for records in mid-edit, it would be the prior value, the value that is indexed; index should be refreshed as the last part of a write operation)
MySQL uses table-level locking. In order to read the table, it has to acquire a read lock, otherwise an INSERT command could come in and get half-way through modifying the table while the search is progressing, and then the search would see inconsistent data in the table(which is very bad).