On Fri, Feb 22, 2008 at 3:14 PM, Brion Vibber <brion(a)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.h…
Here's some of Aerik's testing, with a benchmark:
https://lists.wikimedia.org/mailman/htdig/wikitech-l/2006-December/028081.h…
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.