Skip site navigation (1) Skip section navigation (2)

Re: is it possible to make this faster?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
Cc: "Postgresql Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: is it possible to make this faster?
Date: 2006-05-26 14:22:38
Message-ID: 25668.1148653358@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On 5/25/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
>>> recent versions of mysql do much better, returning same set in < 20ms.

>> Are you sure you measured that right?  I tried to duplicate this using
>> mysql 5.0.21, and I see runtimes of 0.45 sec without an index and
>> 0.15 sec with.  This compares to psql times around 0.175 sec.  Doesn't
>> look to me like we're hurting all that badly, even without using the
>> index.

> Well, my numbers were approximate, but I tested on a few different
> machines.  the times got closer as the cpu speed got faster.  pg
> really loves a quick cpu.  on 600 mhz p3 I got 70ms on mysql and
> 1050ms on pg.  Mysql query cache is always off for my performance
> testing.

Well, this bears looking into, because I couldn't get anywhere near 20ms
with mysql.  I was using a dual Xeon 2.8GHz machine which ought to be
quick enough, and the stock Fedora Core 5 RPM of mysql.  (Well, actually
that SRPM built on FC4, because this machine is still on FC4.)  I made a
MyISAM table with three integer columns as mentioned, and filled it with
about 300000 rows with 2000 distinct values of (a,b) and random values
of c.  I checked the timing both in the mysql CLI, and with a trivial
test program that timed mysql_real_query() plus mysql_store_result(),
getting pretty near the same timings each way.

BTW, in pgsql it helps a whole lot to raise work_mem a bit for this
example --- at default work_mem it wants to do sort + group_aggregate,
while with work_mem 2000 or more it'll use a hash_aggregate plan which
is quite a bit faster.

It seems possible that there is some equivalently simple tuning on the
mysql side that you did and I didn't.  This is an utterly stock mysql
install, just "rpm -i" and "service mysqld start".

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: RagnarDate: 2006-05-26 16:23:37
Subject: Re: column totals
Previous:From: James NeethlingDate: 2006-05-26 13:19:51
Subject: Re: column totals

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group