Re: Seriously, WTF?

George Barnett wrote:
> Funny - I dont see Postgres in there.  Surely PG could
at least beat MS-SQL?

Only *commercial* products are submitted and even the
commercial 
versions of Pg do not seem to have been submitted.
Also that comparison is quite small scale and does not match
the types
of activities I tend to undertake in projects.

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.

I use Firebird, sybase, MSSQL, Oracle, Ingres, Pg, MySQL and
others - 
they all have pro's and cons and you have to understand them
to pick 
which is the best fit for a specific job.
There is no magic bullet and if like me you tend to push an
RDBMS to
its limits you soon find out some of the less obvious cons.

IMHO MySQL are apples and oranges. There is no way to
compare them
as they have radically different "envelopes".

On 8/5/08 11:09, "Jacqui Caren"
<Jacqui.carenntlworld.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

On Thu, May 8, 2008 at 11:56 AM, Steve Sims <s.simsfairfx.com> wrote:
>
>
>  On 8/5/08 11:09, "Jacqui Caren"
<Jacqui.carenntlworld.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
>
>
>

Paul Makepeace wrote:

> An interesting story would be hearing how your company
handles
> ... disaster recovery drills ...

Probably the same way they handle the rocking horse manure.

(Nothing against the company in question, but who can, hand
on heart, 
say they've ever taken part in one of these?)

On 8 May 2008, at 12:30, Paul Makepeace wrote:

> On Thu, May 8, 2008 at 11:56 AM, Steve Sims
<s.simsfairfx.com> wrote:
>>
>>
>>  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

Reviewing http://
bond.pm.org/pipermail/bond.pm/Week-of- 
Mon-20051219/000637.html

I think Steve is misremembering version numbers with the
passage of time
and he probably meant running 4.0 and tested against 5.0
beta.

As great as MySQL 5.1 undoubtedly is, it was only officially
released as
"general availability" a little under 2 months ago
I believe.

- Mark

On 8 May 2008, at 12:45, Jonathan Bennett wrote:
[Disaster recovery drills]
> (Nothing against the company in question, but who can,
hand on  
> heart, say they've ever taken part in one of these?)


I have, except that it wasn't a drill. The overtime put 50%
onto my  
take-home that month, which was nice.

It's like testing: it's much better to just ignore it until
it bites  
you, at least if you have the same attitude as a lot of
places I've  
worked at. Since they usually don't have a disaster, this
does  
actually seem to be a reasonable business decision despite
how it  
annoys my sense of Doing Things Right.

Paul Makepeace wrote:
> 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.
>   
>

I would rate your "I'm quite certain these days you
wouldn't run into 
the problems you've talked about" to be of even less
value that a 
"historic anecdote". At least he was telling us
what actually happened, 
your contribution is pure fantasy and the DOS to Unix
analogy doesn't 
hold well either. An SQL query is an SQL query wherever it
is run and 
thus we can compare database engines.

Unless you have proof that 5.1 no longer exhibits the
problems that he 
described then I will just assume you are a proMySQL / anti
PG fanboy.

Opinions / Assholes. You know the drill.

-- 
Peter Hickman.

Semantico, Lees House, 21-23 Dyke Road, Brighton BN1 3FE
t: 01273 358223
f: 01273 723232
e: peter.hickmansemantico.com
w: www.semantico.com

On Thu, May 8, 2008 at 12:45 PM, Jonathan Bennett
<jonathan.bennettheise-online.co.uk> wrote:
> Paul Makepeace wrote:
>
>
> >
> > An interesting story would be hearing how your
company handles
> > ... disaster recovery drills ...
> >
>
>  Probably the same way they handle the rocking horse
manure.
>
>  (Nothing against the company in question, but who can,
hand on heart, say
> they've ever taken part in one of these?)

Got the t-shirt too  I always
kind of assumed any company above a
reasonable size would do that, no? Certainly, backup testing
you can
do from $company->size == 1

Mark, true; I meant MySQL 5.x.

Paul

>

On Thu, May 8, 2008 at 1:20 PM, Peter Hickman
<peter.hickmansemantico.com> wrote:
> Paul Makepeace wrote:
>
> > 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.
> >
> >
>
>  I would rate your "I'm quite certain these days
you wouldn't run into the
> problems you've talked about" to be of even less
value that a "historic
> anecdote". At least he was telling us what
actually happened, your
> contribution is pure fantasy and the DOS to Unix
analogy doesn't hold well
> either. An SQL query is an SQL query wherever it is run
and thus we can
> compare database engines.
>
>  Unless you have proof that 5.1 no longer exhibits the
problems that he
> described then I will just assume you are a proMySQL /
anti PG fanboy.
>
>  Opinions / Assholes. You know the drill.

That's a bit harsh.

All the migrations I've done between RDBMSs have been from
MySQL &
Oracle to Pg so I'm more in the Pg camp if push came to
shove. That's
offset somewhat however by hearing the endless desperately
out-dated
MySQL FUD here.

The Google Ads system runs on MySQL and is probably the
largest
deployment of MySQL running anywhere. So when I say I'm
certain you
wouldn't run into those problems, I have a reasonable level
of
confidence.

Is that more what you were looking for?

Paul

>
>  --
>  Peter Hickman.
>
>  Semantico, Lees House, 21-23 Dyke Road, Brighton BN1
3FE
>  t: 01273 358223
>  f: 01273 723232
>  e: peter.hickmansemantico.com
>  w: www.semantico.com
>
>

On Thu, May 08, 2008 at 02:05:32PM +0100, Christopher Jones
wrote:

> I have a relatively simple table with approximately 100
million rows  
> of data, and query times suck. I'd be interested to
know whether  
> anyone thinks this is good, acceptable/normal, or bad;
> 
> mysql> select * from histones where chr='1' and
location > 10000 and  
> location < 20000;
> ...
> 7 rows in set (3 min 39.94 sec)

Do you have indices on the chr and location fields?

-- 
David Cantrell | Hero of the Information Age

  I remember when computers were frustrating because they
did
  exactly what you told them to.  That seems kinda quaint
now.
      -- JD Baldwin, in the Monastery