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

[Adam Tauno Williams]

On Wed, 2010-04-14 at 09:55 -0400, mikemol at gmail.com wrote:
> On Wed, Apr 14, 2010 at 7:54 AM, Adam Tauno Williams <awilliam at whitemice.org> wrote:
> > On Tue, 2010-04-13 at 00:02 -0400, Michael Mol wrote:
> >> I *frequently* hit performance issues with MediaWiki. Every six
> >> months, a new-heights surge of traffic spurs me to get my hands on a
> >> larger VPS, even after I've gone through and optimized parameters for
> >> the InnoDB and MyISAM tables.
> > But have you EXPLAIN'd queries?  If MediaWiki [or whatever ORM it uses]
> > can log queries that is what I would do.  Run each of the queries with
> > EXPLAIN and see if an expensive query pops up - then try to debug why it
> > is so expensive.  A *lot* can be done in that regard.   Perhaps
> > MediaWiki is doing something pathological [issuing the same query
> > hundreds of times - happens more often than you might think] or the
> > indexing they defined doesn't match your use-case [very common,
> > developers shouldn't be allowed to type "CREATE INDEX" without their
> > keyboard shocking them].
> 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.

> >> 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.

> My complaint isn't with MySQL as it's performing under my load, it's
> with MediaWiki for not being able to express the data to the end user
> in the way I want,

You originally framed the question as a data-model / performance
relationships.  So discussing performance issues related to this
data-model seemed relevant to me. 

>  and this whole issue of MySQL performance as it *currently* sits on
> my site appears to have stemmed from a comment I made while trying to
> show I do know *something* about how to improve the site's
> performance.

No, MySQL performance cannot be unlinked from the performance of an
application that uses MySQL.