[GRLUG] A data organization problem related to tags. Any database experts around?

[Ben DeMott]

Mediawiki uses memcached and a Mysql Proxy to cache queries.
They use about 60 servers in the US.

On Wed, Apr 14, 2010 at 1:31 PM, Adam Tauno Williams
<awilliam at whitemice.org> wrote:
> On Wed, 2010-04-14 at 13:11 -0400, Michael Mol wrote:
>> On Wed, Apr 14, 2010 at 12:46 PM, Adam Tauno Williams
>> <awilliam at whitemice.org> wrote:
>> > On Wed, 2010-04-14 at 09:55 -0400, mikemol at gmail.com wrote:
>> >> I don't *modify* their quieries, or really even know what they look like.
>> > Understood, but changing the queries isn't necessarily the point of
>> > EXPLAINing a query.
>> Ah, gotcha.
>> >> >> 15-minute load averages of 0.11, and more IO waiting than I'm
>> >> >> comfortable; I need to get in and tweek InnoDB's settings again.
>> >> > Which as a DBA makes me think: (a) something is creating temporary sort
>> >> > tables [and MySQL ***SUCKS*** at sorting], (b) something is creating
>> >> > temporary join tables [a buffer space issue or really dumb join], or (c}
>> >> > [worst case] the engine is falling back to a sequential scan due to bad
>> >> > query design or incorrect indexing [often the issue of cardinality comes
>> >> > into play here]
>> >> > To determine any of those you have to look at the problem on the query
>> >> > level.
>> >> > Maybe current MySQL can autolog EXPLAIN of queries for you?
>> >> I can enable profiling, sure, but it's not going to tell me anything I can
>> >> or will change. The queries reside in a place not safe for me to touch.
>> > They will tell you what queries are slow; and then possibly if the
>> > indexing is wrong or ineffective - you can certainly change the indexing
>> > - or what inserts/updates are slow, or what joins are slow.  Things can
>> > be addressed involving all those things without changing the table
>> > schema.  You can change indexing (modern RDBMS have many indexing
>> > options), fragment data, and alter page and extent sizes.  All which can
>> > have a very significant impact on performance.  Dealing with performance
>> > issues in this manner, with existing applications [and thus *their*
>> > schema] is what a "DBA" does;  it is an entire profession.
>>
>> Mm. I've had occasion and need to tune these things related to work,
>> but it seemed absurd to me that MediaWiki, which already powers a site
>> like Wikipedia, might need me make modifications to the tables that it
>> created itself.
>
> Possibly [likely?] they've optimized how their database is structured
> beyond out-of-the-box MediaWiki.  Having to re-index products from their
> default installation is extremely common.  Most products I've seen
> package a database with a whole slew of randomly chosen indexes: "Hey,
> it might be a good idea to index that!"
>
>
>
> _______________________________________________
> grlug mailing list
> grlug at grlug.org
> http://shinobu.grlug.org/cgi-bin/mailman/listinfo/grlug
>