Seriously, WTF?

[Paul Makepeace]

On Thu, May 8, 2008 at 11:56 AM, Steve Sims <s.sims at fairfx.com> wrote:
>
>
>  On 8/5/08 11:09, "Jacqui Caren" <Jacqui.caren at ntlworld.com> wrote:
>
>  > One real life migration from MySQL to Pg for a UK based music business
>  > was covered on this list and had a dramatic performance
>  > inprovement. Initally the person making the change had no real
>  > Pg skills but as he picked up hints etc the difference soon became
>  > for more evident - especially in the time to web response and
>  > the processor load - two very business critical factors.
>
>  That would have been me, and the business was karmadownload.com, which will
>  be familiar to at least one fairly prominent list member. :)
>
>  As the catalogue at Karmadownload grew, and the complexity of the systems
>  increased, the performance with MySQL really started to suffer badly.  Given
>  the nature of licensing in the music business we engineered the site to
>  filter based on the country the viewer was located in.  This made for a
>  non-trivial database schema, and some incredibly complex queries.
>
>  The trigger to switching from MySQL to Pg was a couple of fairly complex
>  queries (joining about 8 tables) that had become *incredibly* slow.  One
>  took about 12s to execute, and no amount of futzing with the SQL or tuning
>  of MySQL would speed it up.  To make things worse, if you ran two copies of
>  this query at once they'd take 50s to complete...  Run three and you could
>  be waiting 5 minutes, and running 4 would usually never return a result...
>  MySQL simply would not scale for this query.  This was very bad news, since
>  it meant that the site could go offline.
>
>  The same query on Pg against the same data took about 3s to execute.  Two at
>  once would be 6s, three 9s, etc.  Performance across the board was much
>  improved, and only got better as I learnt more about how to tweak things.
>
>  The transition to Pg was fairly painful.  Lots of our queries had to be
>  tweaked to run properly, but much of that was helped with a handy library I
>  found for Pg which added in a load of MySQL compatible SQL functions.  Our
>  data also had to be cleaned before it could be transitioned, especially
>  dates that were stored.  In many ways we had relied on MySQL's poor date
>  handling, which complicated things.  Text encoding was an issue too IIRC.
>  The preparation work for the transition probably took four months - not bad
>  since I was the sole programmer/sysadmin/dbamin/etc.  Once all the
>  groundwork was done tho we only needed to take the site offline for 6 hours
>  to migrate the database over and transition.
>
>  I think we had been running MySQL 3.1.  I did seriously consider and test

Not that a good yarn isn't appreciated, but... Saying "MySQL 3.1
couldn't handle my incredibly complex queries" is rather like saying
"I switched from MS-DOS to Unix as the command line tool weren't up to
what I needed" -- a historic anecdote, and a somewhat self-evident one
at that. It doesn't really add anything to a modern "Which RDBMS?"
discussion. (Maybe you didn't intend it to; just sayin').

MySQL 5.1 is great product and I'm quite certain these days you
wouldn't run into the problems you've talked about.

An interesting story would be hearing how your company handles
redundancy, backups, and disaster recovery drills, with Pg.

P

>  out MySQL 4, which IIRC in tests gave about a 2x speed improvement, but that
>  was still slower than Pg, and it still suffered from the same scaling
>  issues, so it wasn't really an option.
>
>  Before transition I frequently had to nursemaid the server owing to database
>  issues.  I'd increasingly get calls at home in the evenings from colleagues
>  telling me the site was down, and this was invariably caused by MySQL
>  getting it's knickers in a twist.  After transition I could basically leave
>  the server to it and get on with other things.  All the pain I went through
>  was most definitely worth it.
>
>  Steve
>
>
>