Seriously, WTF?

[Steve Sims]

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