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

[mikemol at gmail.com]

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. Modifying their database schema sets me up for a world of hurt during a future software update.

>
>> 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. I've found I can vastly improve performance by tuning the table engine, which is something that *is* safe for me to touch.

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

And, again, diagnosing this at at the query level isn't going to help a whole lot, for two reasons:

1) I'm not going to modify the MediaWiki software outside extensions and the like, as doing so sets me up for a world of hurt during a software update. I've screwed myself that way in the past in a relatively innocuous area (I modified the Monobook skin, and they changed the skin API on me!), so now I know better.
2) My chief problem isn't that MW is written poorly from a database perspective; it's the same software that runs Wikipedia, and, given the right quantity of serverside hardware thrown at MySQL, will run Wikipedia. (Not that I can afford that much hardware!)  Rather, myis that MW isn't capable of representing the data in the way I want to express it, which is why I'm looking at building a fresh implementation with pivot display behavior in mind.

Currently, MySQL is probably having more trouble because of my high update count combined with slow I/O.  The VPS disk sits on RAID 5, and, being VPS, I'm not going to be the only one on that host's physical volume. Sitting on the same VM as Apache+PHP+memcached isn't going to help matters, there. Having only one logical CPU in the VM isn't going to help matters there.

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

Why have I hit performance issues in the past, and expect to hit it int the future? Don't laugh; it's disgusting.

At first, the site ran on a 256M Slicehost account. That gave it enough oomph for a while under the load it was facing, but once it started getting straffic surges from moderate proggit exposure, it ran out of RAM fast. (256M wasn't enough to keep that configuration of MySQL from going to swap. I tuned Apache and MySQL as much as I could, which made things better for a while.  Then we got hit with moderate StumbleUpon traffic, demanding 8 dynamically-generated pages per minute, which spurred me to migrate from that 256M Slicehost account to a Linode 540. (A whopping 100% increase in RAM, for only $6/mo more!)

The Linode was enough to sustain us for a long while; I was able to keep MySQL out of swap territory, and even had enough RAM left over between MySQL and mod_prefork to get 64M of memcached in there. (Not sure if you've ever had to count MBs when managing apache+mysql+other services on the same production box. Not fun. I suspect you've typically had more money to throw at the server environment, though.)  Then we get to January or therabouts, and RC starts seeing a different kind of traffic flood. While it's able to handle the read flow just fine, thanks to caching, I start seeing a massive increase in use where people are adding content, which means I'm seeing a lot of updates, not just reads.

Just to throw some analytics data in there...In the last 28 days, I had a 28% increase in visits over the previous 28 days, which was itself a 24% increase over the 28 days before that. I actually expect MW to be able to handle it for a while; I've still got 800M of RAM on this new 2G VPS that isn't getting much use outside the file cache, so I've still got room to throw in more caching. I haven't seen a level of traffic below my November StumbleUpon surge in almost four weeks, but the site's responsive for now, so I can plan for the long term, this time, rather than work to survive the short term.

-- 
:wq
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 271 bytes
Desc: OpenPGP digital signature
Url : http://shinobu.grlug.org/pipermail/grlug/attachments/20100414/ce872f95/attachment-0001.pgp