From: Joe Conway <mail@joeconway.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>
Cc: Hackers (PostgreSQL) <pgsql-hackers@postgresql.org>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Bernd Helmle <mailings@oopsware.de>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: Susanne Ebrecht <susanne.ebrecht@credativ.de>
Subject: Re: [PATCHES] 8.2 features?
Date: Wed, 19 Jul 2006 21:16:02 -0700
Message-ID: <44BF0382.2010501@joeconway.com> (raw)
In-Reply-To: <23198.1153367887@sss.pgh.pa.us>
References: <44B69778.5050104@dunslane.net>
<50461.84.63.113.55.1152832991.squirrel@pezone.net>
<ECCAAC0A3182895D3D12B0E8@[172.26.14.247]>
<44B7ADFC.90205@dunslane.net>
<44BC0432.3040509@joeconway.com>
<44BC661F.80609@joeconway.com>
<44BCD06C.3060409@calorieking.com>
<44BCF1D4.3010807@dunslane.net>
<5842.1153246741@sss.pgh.pa.us>
<44BD8854.9060506@calorieking.com>
<15212.1153279954@sss.pgh.pa.us>
<44BDB2F5.7080003@joeconway.com>
<44BEA710.6060700@joeconway.com>
<23198.1153367887@sss.pgh.pa.us>
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>
>>I did some testing today against mysql and found that it will easily
>>absorb insert statements with 1 million targetlists provided you set
>>max_allowed_packet high enough for the server. It peaked out at about
>>600MB, compared to my test similar last night where it was using about
>>3.8 GB when I killed it.
>
>>So the question is, do we care?
>
> What's the performance like relative to mysql? It seems hard to believe
> that we can afford the overhead of a separate INSERT statement per row
> (duplicating all the work of parse analysis, rewrite, planning, executor
> start/stop) ... at least not without looking mighty bad.
I don't have the exact numbers handy, but not too great.
As I recall, with last night's patch we did 100K inserts in about 4
seconds, and today mysql did 100K in about 1 second. We never finished
the 1 million insert test due to swapping (I killed it after quite a
while), and mysql did 1 million in about 18 seconds (we did 300K in 13
seconds). The hardware was not identical between last night's test and
today's on mysql, but very similar (similar CPUs and memory, although
the machine I did the mysql tests on had scsi drives, while the pg test
was done on sata).
The difficulty is finding a way to avoid all that extra work without a
very ugly special case kludge just for inserts. I've been banging my
head on that on-and-off for a few days now, and every idea looks uglier
than the last. One suggestion I got off list was to figure out a way to
build a tuplestore and use it to feed the executor. That's starting to
sound better and better to me.
Any ideas or guidance would be greatly appreciated.
Joe
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-docs@postgresql.org
Cc: mail@joeconway.com, tgl@sss.pgh.pa.us, chris.kings-lynne@calorieking.com, pgsql-hackers@postgresql.org, andrew@dunslane.net, mailings@oopsware.de, peter_e@gmx.net, susanne.ebrecht@credativ.de
Subject: Re: [PATCHES] 8.2 features?
In-Reply-To: <44BF0382.2010501@joeconway.com>
* 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