From: Worky Workerson <worky.workerson@gmail.com>
To: Luke Lonergan <llonergan@greenplum.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best COPY Performance
Date: Tue, 31 Oct 2006 16:45:16 -0400
Message-ID: <ce4072df0610311245v5978877el835fc479f4433878@mail.gmail.com> (raw)
In-Reply-To: <C168C853.55FD%llonergan@greenplum.com>
References: <e9c163070610280503t4788ab5ah857ea249b5027e2@mail.gmail.com>
<C168C853.55FD%llonergan@greenplum.com>
> Maybe it is just the PK *build* that slows it down, but I just tried some
> small scale experiments on my MacBook Pro laptop (which has the same disk
> performance as your server) and I get only a 10-15% slowdown from having a
> PK on an integer column. The 10-15% slowdown was on 8.1.5 MPP, so it used
> both CPUs to build the index and load at about 15 MB/s.
...snip...
> What is your schema for the table?
A single IP4 PK and 21 VARCHARs. It takes about 340 seconds to load a
1.9GB file with the PK index, and about 230 seconds without it (ALTER
TABLE mytable DROP CONSTRAINT mytable_pkey), which is a pretty
significant (~30%) savings. If I read the vmstat output correctly
(i.e. the cpu us column), I'm still at 12% and thus still cpu-bound,
except for when the checkpoint occurs, i.e (everything is chugging
along similar to the first line, then stuff gets wonky):
r b swpd free buff cache si so bi bo in cs us sy id wa
2 0 279028 4620040 717940 9697664 0 0 0 19735 1242 7534 13 4 82 1
1 2 279028 4476120 718120 9776840 0 0 0 2225483 1354 5269 13 6 71 11
0 3 279028 4412928 718320 9866672 0 0 2 19746 1324 3978 10 2 69 18
1 1 279028 4334112 718528 9971456 0 0 0 20615 1311 5912 10 3 69 18
0 1 279028 4279904 718608 9995244 0 0 0 134946 1205 674 1 3 85 11
0 2 279028 4307344 718616 9995304 0 0 0 54 1132 247 0 1 77 22
1 0 279028 7411104 718768 6933860 0 0 0 9942 1148 3618 11 6 80 3
1 0 279028 7329312 718964 7015536 0 0 1 19766 1232 5108 13 2 84 1
Also, as a semi-side note, I only have a single checkpoint without the
index, while I have 2 with the index.
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: worky.workerson@gmail.com, llonergan@greenplum.com
Subject: Re: Best COPY Performance
In-Reply-To: <ce4072df0610311245v5978877el835fc479f4433878@mail.gmail.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