From: Tom Lane <tgl@sss.pgh.pa.us>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: is it possible to make this faster?
Date: Fri, 26 May 2006 10:22:38 -0400
Message-ID: <25668.1148653358@sss.pgh.pa.us> (raw)
In-Reply-To: <b42b73150605252147h218ec25ci183283334731adbd@mail.gmail.com>
References: <b42b73150605251307id0e7998td4a3693f0d456f88@mail.gmail.com>
<15919.1148590347@sss.pgh.pa.us>
<16119.1148591497@sss.pgh.pa.us>
<b42b73150605252147h218ec25ci183283334731adbd@mail.gmail.com>
"Merlin Moncure" <mmoncure@gmail.com> writes:
> On 5/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Merlin Moncure" <mmoncure@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mmoncure@gmail.com
Subject: Re: is it possible to make this faster?
In-Reply-To: <25668.1148653358@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox