Rob Church and Timwi wrote (regarding frequency of edits that change categories):
Probably higher than that, to be honest, especially with all those Wikipedians running around, dealing with Special:Uncategorisedpages, and correcting stub templates and so forth.
Also don't forget about vandals just blanking a (categorised) page, plus the immediately following revert.
Okay, well, so that we have some quantified data and can think make a firm decision one way or the other about the suitability of a MyISAM table for category intersections, I'll take a random sample of edits (of some statistically significant size - doesn't take much) and see how many involve a category change and report back.
And, as always, anybody else got any ideas? If this can't work, I can't think of anything else beside a separate Lucene index (which will then not be real time and have to be updated every so often) as I don't think excluding large categories from an intersection search is an acceptable solution.
Thanks, Aerik
On 12/11/06, Aerik Sylvan aerik@thesylvans.com wrote: [snip]
And, as always, anybody else got any ideas? If this can't work, I can't think of anything else beside a separate Lucene index (which will then not be real time and have to be updated every so often) as I don't think excluding large categories from an intersection search is an acceptable solution.
If we're willing to go external, there are lots of possible solutions. (Including lucene). Being external doesn't preclude real-time or near real time. Something could scan recent changes, and go and check to see if the categories have changed. Load could be limited by dampening the polling of a single article to N times in a M hour window.
And of course, I agree that excluding large categories is not a solution. In an ideal world where we had working support for category logic, many categories would be rather long. Categories as a tool for direct navigation is an antiquated idea, ... more flexible and suitable tools like portals and navboxes mostly replaced categories as a direct navigational tool ages ago.
I wrote earlier:
Okay, well, so that we have some quantified data and can think make a firm decision one way or the other about the suitability of a MyISAM table for category intersections, I'll take a random sample of edits (of some statistically significant size - doesn't take much) and see how many involve a category change and report back.
So I took a sample of 50 randomly selected edits (looked at every nth edit on recentchanges) and looked at the diff for that edit to see how many had category changes. In 50 edits, 3 of them had category changes. This is 6% of edits changing categories. The number of categories changed per edit doesn't matter, as the row in the proposed table is per page, so there is only one update operation per edit (to the proposed table) regardless of the number of categories changed in that edit.
In a much less scientific sample, I looked at recentchanges to see how many edits we're having per hour. From http://stats.wikimedia.org/EN/TablesDatabaseEdits.htm I come up with an average of 83 per minute, from June, but I'm sure it's gone up - taking a quick look at recentchanges, I see about 300 in the past minute. Even at 300/minute, that's 5/second, and it's unlikely that more than one of those would write to the fulltext indexed MyISAM categoryintersections table in that one second.
So, I think this is a pretty good solution. I think, if we're concerned about a MyISAM table crashing, perhaps it makes send to have the data in it be redundant (store the categories in the text table as well), so rebuilding it becomes a more straightforward operation of copying selected rows to the innoDB table.
Thoughts?
Aerik
wikitech-l@lists.wikimedia.org