The problem is well-documented on VBulletin support forums.
Since you’re a database guy, you know all about the inherent deficiencies of read/write locks on a MySQL database when a search is performed, causing all writes to the database to get queued until the search has been completed and the database thusly unlocked, and how the server will crap it pants when the maximum number of incoming connections has been reached because the database won’t allow any writes until it’s been write-unlocked…Right?
That’s a ridiculously expensive desktop to host a database of that size. An off the shelf quad core nehalem dell will run you ~600 bucks. Add 8 GB of ram and you are more than capable.
I am willing to defer to your expertise on this, and to concede that you know what you’re talking about regarding searches on MySQL databases.
But i would like to know why it is that there are other messageboard sites out there, bigger in total posts than the SDMB and with a greater number of active members, that simply do not exhibit the sort of behavior that we see on the Dope.
Perhaps they simply possess capabilities, monetary, or skill-wise, that the SDMB brain trust does not possess, and is not able/interested in changing. They’re bankrupt; they ain’t gonna do shit, period. They should just turn loose of the message board, as it has little or nothing to do with Ed Zotti’s greatly diminished enterprise anymore, anyway.
I’m pretty curious about that, also. IAmNotSpartacus seems to have the most knowledge of MySQL that I’ve seen here.
I wonder if those other boards with more total posts get their databases searched as frequently as we get ours searched?
They also might have their archives off-loaded to a server dedicated to only that. Therefore, if you’re a poster at one of those sites, you never notice the board locking up.
Wow. There’s so much wrong with your post, it’s hard to know where to begin. So let’s begin at the beginning. You are assuming first of all that the 5 minute delay is a workaround, when it isn’t. The delay was put in place because it was perceived that frequent searches slow down the board. That’s a fix, not a workaround. You can’t begin with the presumption that searching doesn’t lock cursors on MySQL. It probably does. These claims about “I’ve been to some unnamed board and searched to my heart’s content with no problem” are anecdotal, even if true.
Second, this is MySQL, not Microsoft SQL or Oracle. It’s a low-budget database that almost certainly doesn’t index memo type fields (long text). So when people search for a word that appears in the text of a post, it has no choice but to search every post (within the other parameters) to find it. And it’s not an asynchronous process. In other words, you can’t just offload it to a separate thread (programming thread) and let it dump out when it’s done. You have to wait until it’s finished before the code following the query can proceed.
Third, Jerry really IS busy. This isn’t even his first duty. His first duty is to the owner (or trustee) of Creative Loafing. They have payroll, accounts payable, accounts receivable, management reporting, sales history, and all sorts of other demands. He likely has a daily routine that keeps him busy full time, and he likely schedules time for message board stuff when a manager or salesman isn’t gnawing at his ass for some kind of specialized custom report. And that’s not even to mention the monthly closeouts he has to do, which for most computer operators/administrators is a weekend long ordeal.
Fourth, the comparison between Jerry putting time into the message board and mods explaining things is just… completely inane. They are not comparable. The mods are constantly scanning the board; Jerry is not. Therefore, it is only natural that the mods would have plenty of time to explain things (i.e., post stuff), while Jerry would not likely have plenty of time to work on the message board server.
Finally, telling us to use Google says absolutely nothing about Jerry. Nothing. It might tell us about the mods and admins — meaning that they don’t know what they’re really talking about. Or that they are trying to give you alternate means to search as best they know how. But it doesn’t even remotely have to do with whether Jerry is up to the task of “fixing” something that may not even be broken.
Now.
With all that said, the five minute delay does not necessarily help at all. It helps as far as one user is concerned. But if a hundred users search at once, and then all hundred wait five minutes and search again, then the delay is no help at all. The board will *still *slow down when (and if) the cursors lock. So, no amount of delay is a solution to the problem if it is endemic to the budget database. Two minutes. Ten minutes. It doesn’t matter, because the same number of people will be searching at around the same time, all things being equal. But lesser delays might actually speed things up (!) if a lot of people are trying to search at the same time. PosterA’s search is likely to be finished before PosterB begins searching if the limit is less.
I don’t think TubaDiva or any other admin (except maybe Gaudere) can even answer the question. Dogging them about it won’t get you any information — assuming information is what you’re after.
First, are these other boards using Vbulletin? Second, how much income do these boards generate? The SDMB is somewhat unique among larger online communities in that it doesn’t really make much money, in fact I don’t even know if it’s at a break even point between subscriptions and advertisements.
The most popular method is to offload searching to Google. The other basic options are to offload searching to a separate server, however this requires replication of the main database, or you are only searching the database as it was last replicated.
Another option is to archive the majority of the old posts, and dump that into a separate database. However this requires a manual search of the two databases, or some pretty extensive vbulletin codehacking in order to accomplish (and it still doesn’t solve the read/write lock problems on the main database).
A third option is to install a third party add-on called Phoenix, but again this requires codehacking in the Vbulletin scripts, and from my understanding also breaks some popular features (like newest posts, posts since last visit etc)
I am not sure what methods Jerry has used to index the database, but I suspect that perhaps some noticeable improvement in search speeds could be produced by reviewing how the indexing is accomplished. However this can be somewhat time-consuming and requires some expertise. I won’t presume to speak for Jerry’s capabilities, but suffice it to say that if the work had to be farmed out it would not be inexpensive and if done in house it couldn’t just be done in between meetings.
As has been mentioned a multitude of times in this thread and every other one I’ve followed regarding this subject, the bottom line is resources. For the investment required the gain is hard to justify.
This is why you set up a slave server and send all your search queries to it. vBulletin has a feature specifically for offloading search to a slave for this reason. Many readers and one writer, and the slave writer is allowed to fall behind the master without any performance impact on the master – you just end up with search results that are a few seconds out of date. It ain’t rocket science.
The fact that they haven’t set this up after having the “temporary” 5 minute search delay in place for so long indicates to me that they just don’t give a shit about making it any better. A slave server doesn’t need to be some monster piece of hardware like the master does…it’s got one single writer with all writes serialized. That’s the point.
The section I underlined is wrong. Just because you can have a five minute delay between searches doesn’t mean that’s how it was designed to be used.
I’ve not been the one that’s been comparing other boards to this one, saying that their performance totally pwns the SDMB, but it’s not a hard claim to substantiate. For instance, let’s do a Google search on “largest vbulletin”:
Interesting, a threat on the vBulletin forums about the largest vBulletin forums on the net. I did a search for “SDMB” on their board since it wasn’t mentioned in the aforementioned thread (which may only mean that the people who posted in that thread don’t consider this to be one of the biggest vBulletin boards out there, but make of that what you will). I accidentally typed “SMDB” the first time, so I had to do another search real quick:
This forum requires that you wait 20 seconds between searches. Please try again in 12 seconds.
I think that’s a reasonable amount of time to add as a wait between searches, light years better than five minutes. When I got my search entered correctly, I got this:
Showing results 1 to 30 of 30
Search took 0.12 seconds
This was a guest, pulling up posts from 2000. Sounds like they’ve got it indexed correctly.
So clearly vBulletin knows how to run the system they build. It could be argued that they aren’t a very big forum with a lot of traffic, so let’s pick one of the sites in the first response in the original thread, just because it’s first on the list (http://board.flashkit.com/). I’ll do a search for today’s posts:
Showing results 1 to 25 of 118
Search took 0.09 seconds.
More results, faster, from a bigger pool of data. Not bad for one of the “largest vBulletin” boards on the net.
I don’t know much about MySQL, but let’s assume that everything you said there is true. How does that speak to the fact that other, larger boards are using MySQL with much better search response time than the SDMB with no search delays?
How can you speak to the details of his job so authoritatively? Not that I’m calling him a lazy bum, I’m just curious. I’m also curious why you’re listing a bunch of accounting functions as assigned to him as if that’s what a DBA does all day. I deal with that sort of thing all day, but I support financial software. Jerry does payroll? Really? I’ve heard of “running lean” before, but that’s ridiculous.
Look, I understand the concept of prioritizing the fires that a person needs to put out in a job like this. I also recognize that fixing the search on the SDMB is probably not on the top of his To Do list. However, the search has been broken for a long time. Besides the (relatively) recent upgrade which, by the way, was not even to the latest version at the time, we don’t see that much of anything is done to improve the performance of this board. You mean to tell me that Jerry can’t find some time over the last year to fix something that, while it may be on the lower end of his priority list, is still in his job description? You can’t use the “I’m busy” excuse forever.
That was hyperbole to express my disappointment in how I’ve seen this stuff go down over the last eight years I’ve been at this board.
It does say something about Jerry. I just can’t understand a sysadmin not wanting the system he administers to run at peak performance. That could be just me, though.
I’m just calling it like I see it. I don’t really expect a response from anyone directly, although it was kind of you to respond on their behalf. But when you’re going to try and throw out the Star Trek talk*, don’t be surprised if someone knows that you’re just trying to obfuscate things.
I use this term to describe when someone tries to throw out a bunch of technobabble in the hopes that nobody will notice that they didn’t really say anything. It’s like at the end of Wargames when someone suggests attacking the computer’s “deep logic”. It doesn’t mean anything, but if it’s 1982 and you’ve never heard of a computer, it sounds legit.
That’s all well and good, but it has nothing to do with vBulletin. That’s what I tried to explain before. Okay, let’s take an easy-to-read language like VBScript in ASP. A very simple code snippet might go something like this:
Dim CN
Dim SQL
Dim RS
Set CN = New Connection
CN.Connection = ConnectionString()
SQL = "SELECT LongText FROM Posts WHERE Username = ‘Liberal’ AND PostField LIKE ‘@Tranxene’
(Now, so far, we’re in the equivalent of vBulletin. Their language is probably something else, but that makes no difference. Here’s where vBulletin passes off to MySQL:)
Set RS = CN.Execute(SQL)
(Nothing can happen now — *nothing *— until MySQL returns a set of data into the variable “RS” — for RecordSet. CN was for Connection. The next line of code will not execute until RS has been populated.)
Do Until RS.EOF
…the above says basically “dump everything you have until End Of File”…
…now fill up the page with post snippets or thread titles…
RS.MoveNext
Loop
The point I was making is that, whatever language is being used, vBulletin must wait until MySQL returns control back to it before it can continue. There are only so many cursors available. (A cursor is basically a pointer to a database record.) When they’re all occupied, if it’s a budget database, then you just have to wait. In other words, MySQL will ignore the CN.Execute line because it has no cursor available to search the records. Depending on how it is coded, different things can happen, from hanging up altogether, to taking a long time to return your search results, to reporting an error — which is usually just a timeout. Almost all good coding puts in a timeout, just in case a loop gets “hung”. (Like, someone forgot the RS.MoveNext line.)
Now, Microsoft SQL is more sophisticated than that. It can queue requests until cursors are available. It can even store common function calls internally. Plus it works much much faster at pulling the records to start with. Someone mentioned indexes before, but there is simply no way to index long text fields. You would need multiple supercomputers working in parallel just to index this post. Without indexes, searching is slower because it has to go through every record of long text, just to see whether something in there matches what you’re searching for, and then go to the next one. Other fields CAN be indexed, like UserName, for example. And they probably are.
There can be any number of reasons. You mentioned Flashkit, for example, a board at which I used to post in my early days of working with SWiSH. Searches were slower then, even though the board was smaller, because over time, they have built a server farm. Multiple servers work together on tasks like searching. When the above CN.Execute is called, a switch is made internally to a server that is dedicated to MySQL and nothing else. For searches of long text, that dedicated server might push the task on forward to a server dedicated to nothing but long text searches.
My company has a similar server farm, not quite as elaborate as Flashkit’s, but the principle is the same. If too many users log in, some are switched off to a separate computer. Plus, we have another computer dedicated to handling Microsoft SQL (IIS). AND, we have another computer for staging our work, so that when we add new things, they won’t bother existing code. When we’re ready to implement our new things, we “replicate” them. In other words, we run a batch file that copies our staging server over to our two active servers. We have this setup for each website that we own.
But Creative Loafing — aside from being bankrupt presently — has never been primarily in the business of websites. Their plans have been limited by their business plan and by their resources. They don’t have the hardware (or frankly the software) necessary to handle our load. That’s not Jerry’s fault. He’s not the CEO. And if he WERE the CEO, he’d be fired by the board if he used precious profit dollars to farm up a non-profitable website.
Who said Jerry’s a DBA? Surely, you didn’t imagine that a company like Creative Loafing has an IT department. I’ve run IT departments before — both large and small. I’m running one right now. I have Senior Vice-president status with my company because IT is critical to our business model. But I’ll bet ten dollars against a dollar that Jerry does not have that status with Creative Loafing. Not to diminish his importance. Just pointing out that he probably doesn’t get to sit in an office all day (or at home, in my case) and review people’s work as he hands out assignments. There’s probably a bunch of PCs loosely connected on some sort of peer-to-peer network. And he has to deal with the lady in payables who can’t get her invoice to clear, the guy in receivables whose payment won’t post, the sales person who needs a report on the status of Charlotte (and he needs it yesterday), and the Trustee who needs reports for his due diligence. All these people use computers, and he probably runs around like crazy trying to keep everything going. I’ve been there, so I know what it’s like. I actually had a lady who (back in the days of 5-1/4 floppy disks) complained that she couldn’t get her disks to load. When I went to her desk, I found that she had hung them on her filing cabinet with refrigerator magnets. That’s the kind of bullshit Jerry likely has to deal with all day long. And then there’s this fucking message board.
It hasn’t been “forever”. But aside from that, it isn’t a matter of Jerry looking at his calendar and going, “Okay, November 12 looks like it would be a good day to work on the message board.” His days aren’t laid out like that. God only knows what demands will be made on him that day. When he DOES get a chance to work on the message board, it is probably because he has gotten special permission (with Ed’s help, I suspect), and his other work is still piling up. That’s just the nature of the thing.
I know. Look, I realize it can be frustrating. I’m as frustrated as anyone else. I’m a user too. But the fault doesn’t lie with Jerry. That’s probably why Ed bristles when Jerry is criticized and insulted. Now, it’s true that they could bring in some DBA guru to check everything out, but they would have to contract that out through ordinary business channels. They can’t just go, “Yeah, thanks, Seven. Do what you can. We’d really appreciate it.” There are laws and regulations concerning businesses and people who do work for them. If they allowed Seven to work on the thing, then their exposure would be manifold: (1) Seven could possibly fuck the whole thing up; (2) his labor, if it’s free, constitutes an asset for them, for which they must account; (3) his labor, if he contracts, the company must process a 1099 which, if they aren’t set up for it, they must do manually; (4) everything from OSHA to DOT is on your ass whenever you do anything involving the labor of someone else; and (5) if for some reason (among infinitely many reasons) Seven can’t complete his work, then Creative Loafing is stuck with multiple conundrums.
Premise rejected. There is nothing to indicate that Jerry is, first of all nothing more than a sysadmin, and second of all that he doesn’t want the system he administers to run at peak performance. Don’t you have job? One in the public workplace? If so, are you able to just decide what you want to do and what you don’t? Can you give attention to something when your boss is demanding your attention to something else? Or are you just a college student who doesn’t understand how any of this stuff works — not just the coding and system administration, but the business world as a whole? If we were to suppose that Jerry loves this message board more than anything in the world, and that he would love to spend days on end tinkering with it, he still would face the obstacles I’ve enumerated here — everything from demanding bosses, to the needs of the day to day business, to the hardware and software limitations.
Well, you can discard everything I’ve said if you want to. But there has been no “technobabble”. It’s just the way the business world works, and the world of IT in particular. I’m telling you straight. I have nothing against you.
since I’m a database guy, I know about things like replication and reporting copies of databases that can be used to satisfy ust such requests without interfering with board functionality. MySQL isn’t my specialty – as I said above it has far too many shrotcomings for me to ever do something so stupid as to run a business based on it – but the basics of what can be done to resolve these sorts of issues are common knowledge, and are not indicative of performance problems with the server or with the underlying software.
You can blame MySQL for being problematic, but I’d blame the folks (vBulletin) who wrote software for this platform without realizing its scale-out problems. Writing software on a platofrm you don’t understand is like trying to drive a car without knowing what the brakes are for.
Really? Because you expect that in addition to paying my membership fee which is supposed to pay for the board’s upkeep, I should also just dig deep and throw in a server? Maybe if this was some volunteer-run project with no budget, as opposed to a business that many many people have been paying for service for years, sure. Or maybe if I had some assurance that something would actually be done with my donation, I could see getting together a collection. But people have suggested exactly that in the past and been told by the administration that it’s not possible for them to accept donations.
Your suggestion basically amounts to “put up or shut up,” which is bullshit. And I’ll remind you that I have put up a server to help out another message board that was having performance problems.
Liberal, I am not going to go quoting your post because it was LONG. But I will say this: If you’ve been programming against databases – especially Microsoft SQL Server databases – and using cursors to do it, you need to be slapped HARD with something akin to the density of Brazilian Cherry.
If vBulletin was written the way you think, it’s a surprise it functions at all. So I very much doubt it works that way.
As for indexing your post taking several supercomputers? Microsoft has this handy feature called Full Text Search, and last time I heard it didn’t require a supercomputer to operate. So, Mr Senior VP, I suggest you kindly stop dispensing knowledge that you simply don’t have, and let those of us who do have the knowledge try to discuss the problem.
I’m sorry, but you have just completely disqualified yourself from talking about the technical aspects of this issue. You have no fucking idea what you’re talking about.
All the extra technical details are unnecessary. This is the bottom line. This site is nobody’s money maker and thus gets no love. We are like an old lady asking for more ice for her drink on the Titanic.
For the vast majority of message boards, the founder is usually an administrator, and takes an active role in the maintenance of the site. That includes things like software and server upgrades, backups, preventing security intrusions and spam, and so on. The administrators are usually very hands-on.
On the SDMB, board management is quite different. Ed Zotti is more of an absentee landlord, and he doesn’t seem get his hands dirty with the technical aspects of the site. Except for rule-making, his approach is hands-off. Instead of actively maintaining this Web site, the role is passed off to Jerry, who as others have said is either stretched to the limit and lacks the time to keep the SDMB well-oiled, or lacks the technical skill to properly maintain a message board. I’ll assume it’s the former.
Some have said that vBulletin doesn’t scale up, or that large message boards struggle with their search functions as they grow. Bullshit. According to big-boards.com, there are 65 vBulletin-based message boards that are larger than the SDMB. (Here’s your cite.) The ones I’ve visited on the list seem to function just fine; search works, and pages don’t time out. (SA has a perpetually broken search feature, but the software there is so hacked it can barely be called vBulletin anymore.)
There are several possible easy solutions to fixing the search issue. Making recommendations, though, violates an unwritten “junior administrator” rule, so I’ll keep my mouth shut.
Exactly. Nobody is going to buy any new hardware to run the site and nobody is going to pay for a pro to come in and fix/change stuff to get searches working.