On Fri, Feb 22, 2008 at 3:14 PM, Brion Vibber brion@wikimedia.org wrote:
"Too slow" depends on actual usage. Unless you've benchmarked it in that usage, I wouldn't toss that word around yet.
Well, maybe I'm biased with its total failure to work reasonably on my own web server. :)
- Reade and writes can hold conflicting locks, and you may have to wait
on things sometimes.
(Probably not that bad in this kind of case.)
Isn't it? On my own server with MySQL fulltext (using vBulletin; I just switched to Sphinx to kill this problem) I would commonly see searches taking 20 or 30 seconds, or more, although most took much less. Possibly the searches or data sets were much worse cases than what we're discussing here, I don't know. What would happen to my forum, anyway, is that a routine update would get queued shortly after the select started, and that would in turn block all selects as well as all updates, for 20 or 30 seconds or more. Any pending update blocks selects, even if it it itself is blocked by a read lock, to avoid starvation of updates. This was on a table with only around 2.2 million rows, albeit much larger ones than category intersection would need (forum posts) -- so actually it could be comparable to the amount of data in a Wikipedia categories table, at one row per article, although it would have somewhat fewer rows.
My recollection from Aerik's testing was that performance for MySQL fulltext was kind of marginal, didn't look like it would necessarily stand up under load. Looking back through the archives a bit, Jens seemed to agree a priori with my (more recent) assessment of table-level locking:
https://lists.wikimedia.org/mailman/htdig/wikitech-l/2006-December/028002.ht...
Here's some of Aerik's testing, with a benchmark:
https://lists.wikimedia.org/mailman/htdig/wikitech-l/2006-December/028081.ht...
Which gives a third of a second. Given one very rough estimate of five category changes per second (and that was some time ago), that would all but eliminate any concurrency in the selects: you'd have a couple of selects, then an update would get queued, everything would wait, a couple of updates would run, a few more selects, another update and everything waits again . . . And if the table crashed, you'd have to rebuild it, which could take an hour or a day.
But hey, we have a working proof-of-concept. If anyone wants to try getting it running, I'm not objecting. My suspicion is that something reliable and non-blocking like Lucene or maybe PostgreSQL would be better, but I don't claim any great knowledge about databases.