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

[Adam Tauno Williams]

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

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

<aside>Also, MySQL does *not* scale well as connection counts climb.
This has been demonstrated time and again in benchmarks.  As a DBA [and
not just me] this schaffes as people constantly claim 'relational
database performance drops radically as concurrent users rise' when what
they mean is 'MySQL performance drops radically as concurrent users
rise;  I've never used a relational database other than MySQL, but I'm
just going to say "relational databases" (in general) anyway, that way I
sound smarter'.  I'm not putting you in that category - but for the
record.</aside>

> As far as database size, the InnoDB file is 1.6GB, and the MyISAM
> portion of the wiki is 28M. The raw SQL dump sits at 1.1GB and 620489
> rows.

I don't use MySQL but in any case I wouldn't consider those databases
particularly large.  If you have I/O loading without a huge write load
then I'd really suspect you have some query oriented issue [the engine
is executing one or more of the queries badly].