If you have to be the CEO to get anything done at Creative Loafing, then it’s no wonder they’ve gone bankrupt. Some people lower on the totem pole have to be granted some decision making power, or else nothing gets done. Ever.
I may not be the Senior VP of a technology company (argument from authority, by the way), but I’ve been running and supporting financial software and databases for the past five or six years, which I daresay are a bit more intricate than a message board. Seeing as how the software I support is used by some very large companies to keep track of all their financials, as well as timekeeping, employee expense reimbursement, and HR data, I like to think that I have a fairly decent idea of how the business world works. So I’ll thank you to not talk down to me like I’m a freshman in college who just took Philosophy 101 and has entered one of your GD threads to announce, “But, God is, like, DEAD, man.”
After I submitted this, I realized that “technobabble” was the wrong word to use here. Your eagerness to explain to me exactly what the problem was in excruciating detail sounds an awful lot to me like someone who knows a little bit about a little bit but thinks he knows more than he does. I get this all the time from my users who start talking about how the data in the tables in corrupted because the app isn’t burdening the costs correctly when I later find out that they don’t have the right accounts in the base of their G&A pool.
I hate it when the database runs out of cursors, I worked at a company once that just would not buy any more, man that sucked.
11 million posts in one table is not massive and it’s not even large.
However, a quick googlin’ shows you are right about MySQL (that’s why people like Oracle, readers and writers don’t block each other). Looks like most sites just setup the slave for searches.
Sorry, but I didn’t know about that the first time. And I didn’t mean for you to put up or shut up, but merely that, for a company making no profit — and in fact, is in bankruptcy — an extra server for the message board is (or should be) the last thing on their minds. You keep making it sound like it’s a simple thing. Put in a little time and money, and problem solved. That is simply not the case for Creative Loafing even if it IS the case for My Favorite Message Board.
Cursors are used internally whether you use cursor syntax or not. The example I gave does not explicitly use cursors, and so your whole premise is wack. But internally, there has to be a mechanism to keep up with which record is being fetched. And it has nothing to do with how vBulletin is programmed. My example clearly showed (over simplified for clarity) where the vBulletin code ends and the handover to MySQL takes place. It is not my problem that you did not understand that.
Regarding full text search, that’s nothing new. We used it in the 80s, and so it must predate even that. But there are trade-offs with full text search, not the least of which are precision and false positives. Other tools are required to make FTS effective and efficient, but even then there are other tradeoffs, such as relevance versus proximity. In other words, which is more important — a post that contains the phrase twice or one that is more current? That sort of thing. And I wasn’t talking about the need for supercomputers to do a text search, but to INDEX the whole post. There is no database that indexes long text (memo) fields. It’s because they can’t.
They’re just not that big, Lord Ashtar. If Jerry has a staff at all, it’s probably just one person. And it may well be that that person assists other people as well. That’s just the nature of a small business.
It was not my intention to condescend. I apologize for giving you that impression. But quite frankly, if you know the business world, then there should be no reason for you to expect so much from a small bankrupt business.
I don’t think we all need to fight over who knows more than whom. It’s mostly semantic argument anyway. (Like the cursors, for instance.) I’m only trying to get across the fact that the resources are not available to the company that owns this board to make any improvements to it any time in the near future. That’s all I’m saying.
You’re claiming that MySQL blocks all connections, no matter what they’re doing, because of a SELECT on a single table. This is wrong. Dead wrong. Stupidly wrong. If this was right, MySQL would not be capable of running the huge number of very large sites that it runs. If you think this is true, you do not know what you are talking about.
You’re also claiming that while a single page is waiting for a MySQL query, the entire site grinds to a halt. This is equally wrong. Each page request is being handled by a seperate PHP process. One can be waiting for a particularly slow query and all the other ones hum along just fine, even making their own queries.
I don’t know who you think your VB code is impressing, but it ain’t people who know what they’re talking about.
Lest you think I can’t back up my claim with facts, here’s an experiment I just did on an actual production mysql database. The table in question only contains about 5.5 million rows, but it’s enough that a full text search takes about 11 seconds:
mysql> select count(*) from stat_track_plays;
+----------+
| count(*) |
+----------+
| 5491603 |
+----------+
1 row in set (3.48 sec)
mysql> select * from stat_track_plays where from_url like '%libsagenius%';
Empty set (10.76 sec)
While I was waiting for that SELECT to run, I did this query dozens of times from another connection, each one taking 0.06 sec:
mysql> select * from stat_track_plays limit 10;
ETA: there were no results containing the string ‘libsagenius’
This has been an ongoing question for a few years now. I’m guessing if you looked at a valuation based on profit it would be zero. If you based it on member numbers it would be worth somewhere between $50-100k.
The problem is this place is poorly run. It’s beyond me why the bosses of CL still allow this to be run so badly, since it’s clear that with proper management this place could be a goldmine. There are so many loyal members (loyal is an understatement) with expendable income that they could make serious profit out of this.
Sadly, IMHO it will only be a change in ownership that will force the issue. All that being said, there is nothing to prevent you from making a serious offer to CL to buy this place. Since they are looking for ways to raise money, they might actually take it seriously. (Do it through a good lawyer though.)
It was never intended to impress anyone. You’re fretting furiously over who swings the bigger bat, and I’m just saying that the company — the corporation — cannot accept your help or anyone else’s help, whom they have not hired, without exposure to liability.
Regarding the technical matters mentioned above, you misunderstood what I said. I did not say, for example, that the whole site grinds to a halt. What I said was that the code ON THAT PAGE cannot proceed until MySQL returns a value (actually, a reference in this case) to that variable. That’s simply the nature of synchronous code. If you were to write the same code locally (which we often do before changing the connection string and uploading), you can put break points at the appropriate places, and witness the amount of time for a complex query in real time — and that’s with Microsoft SQL. SDMB search queries are not all that complex, granted, but I was simply making the point that ASP pages do not run threaded code. Perhaps PHP pages do, but if so, there still has to be at some point a return to the code that lists the results. I don’t see how asynchrony would help at all in that cirucumstance because once a query is sent, there really isn’t anything for the code to do until a recordset is returned FOR THAT PAGE ONLY. It just lists the post snippets or thread titles, depending on what you’ve specified. It isn’t like a payroll program which can calculate the gross to net in a separate threaded process while the program moves on to calculate work efficiency tables. What does the PHP code do while waiting for the result set? I can’t imagine any useful background task. You supply your criteria. You click search. You wait. And while you’re waiting, the processing is done within MySQL. You start seeing a list when the results are returned. Implying over and over that I’m an idiot does not change that fact.
And look, if you just wanted to make a blindingly obvious observation about the query being synchronous, then you’re off on a completely different discussion. Full-text searches being synchronous has fuck-all to do with overall board performance – it’s only going to appear slow to the person who’s actually waiting for it. You brought up “cursor locking”, which seems to imply that you think one PHP process can’t do a big slow query for one user without blocking other queries on the system. This idea is what I’m arguing against. Your statement that nothing – nothing – can happen while a search query is in progress is misleading at best and might lead people to believe that it somehow identifies the big performance problem with search. It doesn’t.
I don’t care who here “swings a bigger bat” with respect to databases and web site performance – what I care about is people coming in here spouting bullshit and pasting little snippets of code to try to look like experts in order to “counter” some very legitimate complaints.
And if you think I’m being rude, maybe you should consider what contribution your initial response to me in this thread made, [insert appropriate honorific here].
I haven’t used MySQL (experience is with DB2, Oracle and MS SQL), but a little googlin’ shows MySQL has supported FULLTEXT indexing and searching of TEXT types (all of 'em) since around 2003.
It is not your place to tell me what I really meant. What I obviously meant was that no further code on that page can execute until MySQL returns a recordset to that variable. Look at the context. Lines of code, a SQL call, and then a dump of the recordset.
Well, duh. Those are the people who are complaining, sir — the people who have to wait until a timeout and then get error messages. That’s what the whole OP is about. The guy didn’t want to wait five minutes to do his next search.
That misrepresents what I brought up. I did not bring up locked cursors in the context of the post that sent you off on this tangent. That was much earlier, as I will show below.
Then you are arguing against yourself and something you made up. The only thing I said about cursors was that there is a finite number of them available at any one time. I said that I suspected that they were fewer in MySQL and other budget databases than there are in MS SQL and Oracle.
But it CAN. Repeating what I ACTUALLY said before:
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.
The above is merely saying that when enough users search at the same time, it is conceivable that they will exhaust the available internal cursors of MySQL. (Oracle’s default maximum is, I believe, 1000. Or at least, it used to be.) Internally, when no cursor is available, all cursors lock until the first available one releases. It is not a stack; it is a register. And it isn’t a single search that slows the board down. It is hundreds of simultaneous searches.
Your implications are insulting and rude. And your complaints are not legitimate. The OP’s complaint is legitimate, but you seem to be insisting that a corporation in bankruptcy should either accept volunteer help or buy more equipment — neither of which it can do.
So, is all this sabre rattling about nothing more than you being pissed off and venting your anger? That’s rather amusing, actually.