pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBest COPY Performance
52+ messages / 13 participants
[nested] [flat]
* Best COPY Performance
@ 2006-10-20 20:05 Worky Workerson <worky.workerson@gmail.com>
0 siblings, 2 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-20 20:05 UTC (permalink / raw)
To: pgsql-performance
What is the best COPY performance that you have gotten on a "normal" table?
I know that this is question is almost too general, but it might help
me out a bit, or at least give me the right things to tweak. Perhaps
the question can be rewritten as "Where are the major bottlenecks in a
COPY?" or "How can I compute the max theoretical COPY performance for
my hardware?". The two subquestions that I have from this are:
-Are my ETL scripts (perl) maximizing the database COPY speeds?
-Can I tweak my DB further to eek out a bit more performance?
I'm using perl to ETL a decent sized data set (10 million records) and
then loading it through perl::DBI's copy. I am currently getting
between 10K and 15K inserts/second. I've profiled the ETL scripts a
bit and have performance-improved a lot of the code, but I'd like to
determine whether it makes sense to try and further optimize my Perl
or count it as "done" and look for improvements elsewhere.
I ran trivial little insert into a table with a single integer row and
came close to 250K inserts/second using psql's \copy, so I'm thinking
that my code could be optimized a bit more, but wanted to check around
to see if that was the case.
I am most interested in loading two tables, one with about 21 (small)
VARCHARs where each record is about 200 bytes, and another with 7
INTEGERs, 3 TIMESTAMPs, and 1 BYTEA where each record is about 350
bytes.
I have implemented most of the various bits of PG config advice that I
have seen, both here and with a some googling, such as:
wal_buffers=128
checkpoint_segments=128
checkpoint_timeout=3000
Software: PG 8.1.3 on RHEL 4.3 x86_64
Hardware: Quad Dual-core Opteron, Fibre Channel SAN with 256M BBC
Thanks!
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-21 01:11 Merlin Moncure <mmoncure@gmail.com>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 1 reply; 52+ messages in thread
From: Merlin Moncure @ 2006-10-21 01:11 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-performance
On 10/21/06, Worky Workerson <worky.workerson@gmail.com> wrote:
> What is the best COPY performance that you have gotten on a "normal" table?
>
> I know that this is question is almost too general, but it might help
> me out a bit, or at least give me the right things to tweak. Perhaps
> the question can be rewritten as "Where are the major bottlenecks in a
> COPY?" or "How can I compute the max theoretical COPY performance for
> my hardware?". The two subquestions that I have from this are:
> -Are my ETL scripts (perl) maximizing the database COPY speeds?
> -Can I tweak my DB further to eek out a bit more performance?
>
> I'm using perl to ETL a decent sized data set (10 million records) and
> then loading it through perl::DBI's copy. I am currently getting
> between 10K and 15K inserts/second. I've profiled the ETL scripts a
> bit and have performance-improved a lot of the code, but I'd like to
> determine whether it makes sense to try and further optimize my Perl
> or count it as "done" and look for improvements elsewhere.
>
> I ran trivial little insert into a table with a single integer row and
> came close to 250K inserts/second using psql's \copy, so I'm thinking
> that my code could be optimized a bit more, but wanted to check around
> to see if that was the case.
>
> I am most interested in loading two tables, one with about 21 (small)
> VARCHARs where each record is about 200 bytes, and another with 7
> INTEGERs, 3 TIMESTAMPs, and 1 BYTEA where each record is about 350
> bytes.
indexes/keys? more memory for sorting during index creation can have
a dramatic affect on bulk insert performance. check for pg_tmp
folders popping up during copy run.
> I have implemented most of the various bits of PG config advice that I
> have seen, both here and with a some googling, such as:
>
> wal_buffers=128
> checkpoint_segments=128
> checkpoint_timeout=3000
>
> Software: PG 8.1.3 on RHEL 4.3 x86_64
> Hardware: Quad Dual-core Opteron, Fibre Channel SAN with 256M BBC
for table light on indexes, 10-15k for copy is pretty poor. you can
get pretty close to that with raw inserts on good hardware. I would
suggest configuirng your perl script to read from stdin and write to
stdout, and pipe it to psql using copy from stdin. then just
benchmark your perl script redirecting output to a file.
merlin
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-23 09:27 Markus Schaber <schabi@logix-tt.com>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 2 replies; 52+ messages in thread
From: Markus Schaber @ 2006-10-23 09:27 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-performance
Hi, Worky,
Worky Workerson wrote:
> I am currently getting
> between 10K and 15K inserts/second.
> I ran trivial little insert into a table with a single integer row and
> came close to 250K inserts/second using psql's \copy, so I'm thinking
> that my code could be optimized a bit more, but wanted to check around
> to see if that was the case.
Could you COPY one of your tables out to disk via psql, and then COPY it
back into the database, to reproduce this measurement with your real data?
Also, how much is the disk load, and CPU usage?
As long as psql is factor 20 better than your perl script, I think that
the perl interface is what should be optimized.
On a table with no indices, triggers and contstraints, we managed to
COPY about 7-8 megabytes/second with psql over our 100 MBit network, so
here the network was the bottleneck.
You should think about making your perl program writing the COPY
statement as text, and piping it into psql.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-23 15:10 Worky Workerson <worky.workerson@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 52+ messages in thread
From: Worky Workerson @ 2006-10-23 15:10 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
> > I am most interested in loading two tables, one with about 21 (small)
> > VARCHARs where each record is about 200 bytes, and another with 7
> > INTEGERs, 3 TIMESTAMPs, and 1 BYTEA where each record is about 350
> > bytes.
>
> indexes/keys? more memory for sorting during index creation can have
> a dramatic affect on bulk insert performance. check for pg_tmp
> folders popping up during copy run.
The only index on load is a single IP4 btree primary key, which I
figure should function about like an INTEGER.
> for table light on indexes, 10-15k for copy is pretty poor. you can
> get pretty close to that with raw inserts on good hardware. I would
> suggest configuirng your perl script to read from stdin and write to
> stdout, and pipe it to psql using copy from stdin. then just
> benchmark your perl script redirecting output to a file.
So simple and hadn't thought of that ... thanks. When I pre-create a
COPY file, I can load it at about 45K inserts/sec (file was 1.8GB or
14.5 million records in 331 seconds), which looks like its about 5.5
MB/s. I'm loading from a local 15K SCSI320 RAID10 (which also
contains the PG log files) to a 10K SCSI320 RAID10 on an FC SAN. Does
this look more consistent with "decent" performance, or should I go
looking into some hardware issues i.e. SAN configuration? I've
currently got several hats including hardware/systems/security admin,
as well as DBA and programmer, and my SAN setup skills could
definitely use some more work.
Hardware aside, my perl can definitely use some work, and it seems to
be mostly the CSV stuff that I am using, mostly for convenience. I'll
see if I can't redo some of that to eliminate some CSV processing, or,
barring that, multithread the process to utilize more of the CPUs.
Part of the reason that I hadn't used psql in the first place is that
I'm loading the data into partitioned tables, and the loader keeps
several COPY connections open at a time to load the data into the
right table. I guess I could just as easily keep several psql pipes
open, but it seemed cleaner to go through DBI.
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-23 15:11 Luke Lonergan <llonergan@greenplum.com>
parent: Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 52+ messages in thread
From: Luke Lonergan @ 2006-10-23 15:11 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-performance
Markus,
On 10/23/06 2:27 AM, "Markus Schaber" <schabi@logix-tt.com> wrote:
> On a table with no indices, triggers and contstraints, we managed to
> COPY about 7-8 megabytes/second with psql over our 100 MBit network, so
> here the network was the bottleneck.
We routinely get 10-12MB/s on I/O hardware that can sustain a sequential
write rate of 60+ MB/s with the WAL and data on the same disks.
It depends on a few things you might not consider, including the number and
type of columns in the table and the client and server encoding. The
fastest results are with more columns in a table and when the client and
server encoding are the same.
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-23 15:40 Worky Workerson <worky.workerson@gmail.com>
parent: Markus Schaber <schabi@logix-tt.com>
1 sibling, 2 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-23 15:40 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-performance
Markus,
> Could you COPY one of your tables out to disk via psql, and then COPY it
> back into the database, to reproduce this measurement with your real data?
$ psql -c "COPY my_table TO STDOUT" > my_data
$ ls my_data
2018792 edgescape_pg_load
$ time cat my_data | psql -c "COPY mytable FROM STDIN"
real 5m43.194s
user 0m35.412s
sys 0m9.567s
> Also, how much is the disk load, and CPU usage?
When I am loading via the perl (which I've established is a
bottleneck), the one CPU core is at 99% for the perl and another is at
30% for a postmaster, vs about 90% for the postmaster when going
through psql.
The disk load is where I start to get a little fuzzy, as I haven't
played with iostat to figure what is "normal". The local drives
contain PG_DATA as well as all the log files, but there is a
tablespace on the FibreChannel SAN that contains the destination
table. The disk usage pattern that I see is that there is a ton of
consistent activity on the local disk, with iostat reporting an
average of 30K Blk_wrtn/s, which I assume is the log files. Every
several seconds there is a massive burst of activity on the FC
partition, to the tune of 250K Blk_wrtn/s.
> On a table with no indices, triggers and contstraints, we managed to
> COPY about 7-8 megabytes/second with psql over our 100 MBit network, so
> here the network was the bottleneck.
hmm, this makes me think that either my PG config is really lacking,
or that the SAN is badly misconfigured, as I would expect it to
outperform a 100Mb network. As it is, with a straight pipe to psql
COPY, I'm only working with a little over 5.5 MB/s. Could this be due
to the primary key index updates?
Thanks!
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-23 21:59 Jim C. Nasby <jim@nasby.net>
parent: Worky Workerson <worky.workerson@gmail.com>
0 siblings, 4 replies; 52+ messages in thread
From: Jim C. Nasby @ 2006-10-23 21:59 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Mon, Oct 23, 2006 at 11:10:19AM -0400, Worky Workerson wrote:
> >> I am most interested in loading two tables, one with about 21 (small)
> >> VARCHARs where each record is about 200 bytes, and another with 7
> >> INTEGERs, 3 TIMESTAMPs, and 1 BYTEA where each record is about 350
> >> bytes.
> >
> >indexes/keys? more memory for sorting during index creation can have
> >a dramatic affect on bulk insert performance. check for pg_tmp
> >folders popping up during copy run.
>
> The only index on load is a single IP4 btree primary key, which I
> figure should function about like an INTEGER.
>
> >for table light on indexes, 10-15k for copy is pretty poor. you can
> >get pretty close to that with raw inserts on good hardware. I would
> >suggest configuirng your perl script to read from stdin and write to
> >stdout, and pipe it to psql using copy from stdin. then just
> >benchmark your perl script redirecting output to a file.
>
> So simple and hadn't thought of that ... thanks. When I pre-create a
> COPY file, I can load it at about 45K inserts/sec (file was 1.8GB or
> 14.5 million records in 331 seconds), which looks like its about 5.5
> MB/s. I'm loading from a local 15K SCSI320 RAID10 (which also
> contains the PG log files) to a 10K SCSI320 RAID10 on an FC SAN. Does
> this look more consistent with "decent" performance, or should I go
> looking into some hardware issues i.e. SAN configuration? I've
> currently got several hats including hardware/systems/security admin,
> as well as DBA and programmer, and my SAN setup skills could
> definitely use some more work.
>
> Hardware aside, my perl can definitely use some work, and it seems to
> be mostly the CSV stuff that I am using, mostly for convenience. I'll
> see if I can't redo some of that to eliminate some CSV processing, or,
> barring that, multithread the process to utilize more of the CPUs.
> Part of the reason that I hadn't used psql in the first place is that
> I'm loading the data into partitioned tables, and the loader keeps
> several COPY connections open at a time to load the data into the
> right table. I guess I could just as easily keep several psql pipes
> open, but it seemed cleaner to go through DBI.
http://stats.distributed.net used to use a perl script to do some
transformations before loading data into the database. IIRC, when we
switched to using C we saw 100x improvement in speed, so I suspect that
if you want performance perl isn't the way to go. I think you can
compile perl into C, so maybe that would help some.
Ultimately, you might be best of using triggers instead of rules for the
partitioning since then you could use copy. Or go to raw insert commands
that are wrapped in a transaction.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-23 22:10 Joshua D. Drake <jd@commandprompt.com>
parent: Jim C. Nasby <jim@nasby.net>
3 siblings, 0 replies; 52+ messages in thread
From: Joshua D. Drake @ 2006-10-23 22:10 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
> Ultimately, you might be best of using triggers instead of rules for the
> partitioning since then you could use copy. Or go to raw insert commands
> that are wrapped in a transaction.
My experience is that triggers are quite a bit faster than rules in any
kind of partitioning that involves more than say 7 tables.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-23 22:37 Craig A. James <cjames@modgraph-usa.com>
parent: Jim C. Nasby <jim@nasby.net>
3 siblings, 1 reply; 52+ messages in thread
From: Craig A. James @ 2006-10-23 22:37 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Jim C. Nasby wrote:
> http://stats.distributed.net used to use a perl script to do some
> transformations before loading data into the database. IIRC, when we
> switched to using C we saw 100x improvement in speed, so I suspect that
> if you want performance perl isn't the way to go. I think you can
> compile perl into C, so maybe that would help some.
I use Perl extensively, and have never seen a performance problem. I suspect the perl-to-C "100x improvement" was due to some other factor, like a slight change in the schema, indexes, or the fundamental way the client (C vs Perl) handled the data during the transformation, or just plain bad Perl code.
Modern scripting languages like Perl and Python make programmers far, far more productive than the bad old days of C/C++. Don't shoot yourself in the foot by reverting to low-level languages like C/C++ until you've exhausted all other possibilities. I only use C/C++ for intricate scientific algorithms.
In many cases, Perl is *faster* than C/C++ code that I write, because I can't take the time (for example) to write the high-performance string manipulation that have been fine-tuned and extensively optimized in Perl.
Craig
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-24 13:17 Worky Workerson <worky.workerson@gmail.com>
parent: Jim C. Nasby <jim@nasby.net>
3 siblings, 1 reply; 52+ messages in thread
From: Worky Workerson @ 2006-10-24 13:17 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
> http://stats.distributed.net used to use a perl script to do some
> transformations before loading data into the database. IIRC, when we
> switched to using C we saw 100x improvement in speed, so I suspect that
> if you want performance perl isn't the way to go. I think you can
> compile perl into C, so maybe that would help some.
Like Craig mentioned, I have never seen those sorts of improvements
going from perl->C, and developer efficiency is primo for me. I've
profiled most of the stuff, and have used XS modules and Inline::C on
the appropriate, often used functions, but I still think that it comes
down to my using CSV and Text::CSV_XS. Even though its XS, CSV is
still a pain in the ass.
> Ultimately, you might be best of using triggers instead of rules for the
> partitioning since then you could use copy. Or go to raw insert commands
> that are wrapped in a transaction.
Eh, I've put the partition loading logic in the loader, which seems to
work out pretty well, especially since I keep things sorted and am the
only one inserting into the DB and do so with bulk loads. But I'll
keep this in mind for later use.
Thanks!
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 00:14 Jim C. Nasby <jim@nasby.net>
parent: Craig A. James <cjames@modgraph-usa.com>
0 siblings, 0 replies; 52+ messages in thread
From: Jim C. Nasby @ 2006-10-25 00:14 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; g@nasby.net; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Mon, Oct 23, 2006 at 03:37:47PM -0700, Craig A. James wrote:
> Jim C. Nasby wrote:
> >http://stats.distributed.net used to use a perl script to do some
> >transformations before loading data into the database. IIRC, when we
> >switched to using C we saw 100x improvement in speed, so I suspect that
> >if you want performance perl isn't the way to go. I think you can
> >compile perl into C, so maybe that would help some.
>
> I use Perl extensively, and have never seen a performance problem. I
> suspect the perl-to-C "100x improvement" was due to some other factor, like
> a slight change in the schema, indexes, or the fundamental way the client
> (C vs Perl) handled the data during the transformation, or just plain bad
> Perl code.
>
> Modern scripting languages like Perl and Python make programmers far, far
> more productive than the bad old days of C/C++. Don't shoot yourself in
> the foot by reverting to low-level languages like C/C++ until you've
> exhausted all other possibilities. I only use C/C++ for intricate
> scientific algorithms.
>
> In many cases, Perl is *faster* than C/C++ code that I write, because I
> can't take the time (for example) to write the high-performance string
> manipulation that have been fine-tuned and extensively optimized in Perl.
Well, the code is all at
http://cvs.distributed.net/viewcvs.cgi/stats-proc/hourly/ (see logmod
directory and logmod_*.pl). There have been changes made to the C code
since we changed over, but you can find the appropriate older versions
in there. IIRC, nothing in the database changed when we went from perl
to C (it's likely that was the *only* change that happened anywhere
around that time).
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 00:17 Jim C. Nasby <jim@nasby.net>
parent: Worky Workerson <worky.workerson@gmail.com>
0 siblings, 1 reply; 52+ messages in thread
From: Jim C. Nasby @ 2006-10-25 00:17 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Tue, Oct 24, 2006 at 09:17:08AM -0400, Worky Workerson wrote:
> >http://stats.distributed.net used to use a perl script to do some
> >transformations before loading data into the database. IIRC, when we
> >switched to using C we saw 100x improvement in speed, so I suspect that
> >if you want performance perl isn't the way to go. I think you can
> >compile perl into C, so maybe that would help some.
>
> Like Craig mentioned, I have never seen those sorts of improvements
> going from perl->C, and developer efficiency is primo for me. I've
> profiled most of the stuff, and have used XS modules and Inline::C on
> the appropriate, often used functions, but I still think that it comes
> down to my using CSV and Text::CSV_XS. Even though its XS, CSV is
> still a pain in the ass.
>
> >Ultimately, you might be best of using triggers instead of rules for the
> >partitioning since then you could use copy. Or go to raw insert commands
> >that are wrapped in a transaction.
>
> Eh, I've put the partition loading logic in the loader, which seems to
> work out pretty well, especially since I keep things sorted and am the
> only one inserting into the DB and do so with bulk loads. But I'll
> keep this in mind for later use.
Well, given that perl is using an entire CPU, it sounds like you should
start looking either at ways to remove some of the overhead from perl,
or to split that perl into multiple processes.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 05:36 Craig A. James <cjames@modgraph-usa.com>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 2 replies; 52+ messages in thread
From: Craig A. James @ 2006-10-25 05:36 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Jim C. Nasby wrote:
> Well, given that perl is using an entire CPU, it sounds like you should
> start looking either at ways to remove some of the overhead from perl,
> or to split that perl into multiple processes.
I use Perl for big database copies (usually with some processing/transformation along the way) and I've never seen 100% CPU usage except for brief periods, even when copying BLOBS and such. My typical copy divides operations into blocks, for example doing
N = 0
while (more rows to go) {
begin transaction
select ... where primary_key > N order by primary_key limit 1000
while (fetch a row)
insert into ...
N = (highest value found in last block)
commit
}
Doing it like this in Perl should keep Postgres busy, with Perl using only moderate resources. If you're seeing high Perl CPU usage, I'd look first at the Perl code.
Craig
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 12:03 Worky Workerson <worky.workerson@gmail.com>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 2 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-25 12:03 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Jim C. Nasby <jim@nasby.net>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On 10/25/06, Craig A. James <cjames@modgraph-usa.com> wrote:
> Jim C. Nasby wrote:
> > Well, given that perl is using an entire CPU, it sounds like you should
> > start looking either at ways to remove some of the overhead from perl,
> > or to split that perl into multiple processes.
>
> I use Perl for big database copies (usually with some processing/transformation along the
> way) and I've never seen 100% CPU usage except for brief periods, even when copying
> BLOBS and such. My typical copy divides operations into blocks, for example doing
I'm just doing CSV style transformations (and calling a lot of
functions along the way), but the end result is a straight bulk load
of data into a blank database. And we've established that Postgres
can do *way* better than what I am seeing, so its not suprising that
perl is using 100% of a CPU.
However, I am still curious as to the rather slow COPYs from psql to
local disks. Like I mentioned previously, I was only seeing about 5.7
MB/s (1.8 GB / 330 seconds), where it seemed like others were doing
substantially better. What sorts of things should I look into?
Thanks!
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 12:16 Alex Stapleton <alexs@advfn.com>
parent: Jim C. Nasby <jim@nasby.net>
3 siblings, 0 replies; 52+ messages in thread
From: Alex Stapleton @ 2006-10-25 12:16 UTC (permalink / raw)
To: Jim C.Nasby <jim@nasby.net>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On 23 Oct 2006, at 22:59, Jim C. Nasby wrote:
> http://stats.distributed.net used to use a perl script to do some
> transformations before loading data into the database. IIRC, when we
> switched to using C we saw 100x improvement in speed, so I suspect
> that
> if you want performance perl isn't the way to go. I think you can
> compile perl into C, so maybe that would help some.
http://shootout.alioth.debian.org/gp4/benchmark.php?
test=all&lang=perl&lang2=gcc
100x doesn't totally impossible if that is even vaguely accurate and
you happen to be using bits of Perl which are a lot slower than the C
implementation would be...
The slowest things appear to involve calling functions, all the
slowest tests involve lots of function calls.
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 12:18 Markus Schaber <schabi@logix-tt.com>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 0 replies; 52+ messages in thread
From: Markus Schaber @ 2006-10-25 12:18 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-performance
Hi, Worky,
Worky Workerson wrote:
> $ psql -c "COPY my_table TO STDOUT" > my_data
> $ ls my_data
> 2018792 edgescape_pg_load
> $ time cat my_data | psql -c "COPY mytable FROM STDIN"
> real 5m43.194s
> user 0m35.412s
> sys 0m9.567s
That's via PSQL, and you get about 5 MB/Sec.
>> On a table with no indices, triggers and contstraints, we managed to
>> COPY about 7-8 megabytes/second with psql over our 100 MBit network, so
>> here the network was the bottleneck.
>
> hmm, this makes me think that either my PG config is really lacking,
> or that the SAN is badly misconfigured, as I would expect it to
> outperform a 100Mb network. As it is, with a straight pipe to psql
> COPY, I'm only working with a little over 5.5 MB/s. Could this be due
> to the primary key index updates?
Yes, index updates cause both CPU load, and random disk access (which is
slow by nature).
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 13:20 Merlin Moncure <mmoncure@gmail.com>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 1 reply; 52+ messages in thread
From: Merlin Moncure @ 2006-10-25 13:20 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-performance
On 10/23/06, Worky Workerson <worky.workerson@gmail.com> wrote:
> The disk load is where I start to get a little fuzzy, as I haven't
> played with iostat to figure what is "normal". The local drives
> contain PG_DATA as well as all the log files, but there is a
> tablespace on the FibreChannel SAN that contains the destination
> table. The disk usage pattern that I see is that there is a ton of
> consistent activity on the local disk, with iostat reporting an
> average of 30K Blk_wrtn/s, which I assume is the log files. Every
> several seconds there is a massive burst of activity on the FC
> partition, to the tune of 250K Blk_wrtn/s.
>
> > On a table with no indices, triggers and contstraints, we managed to
> > COPY about 7-8 megabytes/second with psql over our 100 MBit network, so
> > here the network was the bottleneck.
I'm guessing the high bursts are checkpoints. Can you check your log
files for pg and see if you are getting warnings about checkpoint
frequency? You can get some mileage here by increasing wal files.
Have you determined that pg is not swapping? try upping maintenance_work_mem.
What exactly is your architecture? is your database server direct
attached to the san? if so, 2gb/4gb fc? what san? have you bonnie++
the san? basically, you can measure iowait to see if pg is waiting on
your disks.
regarding perl, imo the language performance is really about which
libraries you use. the language itself is plenty fast.
merlin
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 15:06 Luke Lonergan <llonergan@greenplum.com>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 0 replies; 52+ messages in thread
From: Luke Lonergan @ 2006-10-25 15:06 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; Craig A. James <cjames@modgraph-usa.com>; +Cc: Jim C. Nasby <jim@nasby.net>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Mr. Worky Workerson,
On 10/25/06 5:03 AM, "Worky Workerson" <worky.workerson@gmail.com> wrote:
> However, I am still curious as to the rather slow COPYs from psql to
> local disks. Like I mentioned previously, I was only seeing about 5.7
> MB/s (1.8 GB / 330 seconds), where it seemed like others were doing
> substantially better. What sorts of things should I look into?
It's probable that you have a really poor performing disk configuration.
Judging from earlier results, you may only be getting 3 x 5.7 = 17 MB/s of
write performance to your disks, which is about 1/4 of a single disk drive.
Please run this test and report the time here:
1) Calculate the size of 2x memory in 8KB blocks:
# of blocks = 250,000 x memory_in_GB
Example:
250,000 x 16GB = 4,000,000 blocks
2) Benchmark the time taken to write 2x RAM sequentially to your disk:
time bash -c "dd if=/dev/zero of=bigfile bs=8k count=<# of blocks> &&
sync"
3) Benchmark the time taken to read same:
time dd if=bigfile of=/dev/null bs=8k
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 15:22 Jim C. Nasby <jim@nasby.net>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 52+ messages in thread
From: Jim C. Nasby @ 2006-10-25 15:22 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Tue, Oct 24, 2006 at 10:36:04PM -0700, Craig A. James wrote:
> Jim C. Nasby wrote:
> >Well, given that perl is using an entire CPU, it sounds like you should
> >start looking either at ways to remove some of the overhead from perl,
> >or to split that perl into multiple processes.
>
> I use Perl for big database copies (usually with some
> processing/transformation along the way) and I've never seen 100% CPU usage
> except for brief periods, even when copying BLOBS and such. My typical
> copy divides operations into blocks, for example doing
>
> N = 0
> while (more rows to go) {
> begin transaction
> select ... where primary_key > N order by primary_key limit 1000
> while (fetch a row)
> insert into ...
> N = (highest value found in last block)
> commit
> }
>
> Doing it like this in Perl should keep Postgres busy, with Perl using only
> moderate resources. If you're seeing high Perl CPU usage, I'd look first
> at the Perl code.
Wait... so you're using perl to copy data between two tables? And using
a cursor to boot? I can't think of any way that could be more
inefficient...
What's wrong with a plain old INSERT INTO ... SELECT? Or if you really
need to break it into multiple transaction blocks, at least don't
shuffle the data from the database into perl and then back into the
database; do an INSERT INTO ... SELECT with that same where clause.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 15:25 Worky Workerson <worky.workerson@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 2 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-25 15:25 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-performance
> I'm guessing the high bursts are checkpoints. Can you check your log
> files for pg and see if you are getting warnings about checkpoint
> frequency? You can get some mileage here by increasing wal files.
Nope, nothing in the log. I have set:
wal_buffers=128
checkpoint_segments=128
checkpoint_timeout=3000
which I thought was rather generous. Perhaps I should set it even
higher for the loads?
> Have you determined that pg is not swapping? try upping maintenance_work_mem.
maintenance_work_mem = 524288 ... should I increase it even more?
Doesn't look like pg is swapping ...
> What exactly is your architecture? is your database server direct
> attached to the san? if so, 2gb/4gb fc? what san? have you bonnie++
> the san? basically, you can measure iowait to see if pg is waiting on
> your disks.
I'm currently running bonnie++ with the defaults ... should I change
the execution to better mimic Postgres' behavior?
RHEL 4.3 x86_64
HP DL585, 4 Dual Core Opteron 885s
16 GB RAM
2x300GB 10K SCSI320, RAID10
HP MSA1000 SAN direct connected via single 2GB Fibre Channel Arbitrated Loop
10x300GB 10K SCSI320, RAID10
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 15:28 Jim C. Nasby <jim@nasby.net>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 0 replies; 52+ messages in thread
From: Jim C. Nasby @ 2006-10-25 15:28 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: Craig A. James <cjames@modgraph-usa.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Wed, Oct 25, 2006 at 08:03:38AM -0400, Worky Workerson wrote:
> I'm just doing CSV style transformations (and calling a lot of
> functions along the way), but the end result is a straight bulk load
> of data into a blank database. And we've established that Postgres
> can do *way* better than what I am seeing, so its not suprising that
> perl is using 100% of a CPU.
If you're loading into an empty database, there's a number of tricks
that will help you:
Turn off fsync
Add constraints and indexes *after* you've loaded the data (best to add
as much of them as possible on a per-table basis right after the table
is loaded so that it's hopefully still in cache)
Crank up maintenance_work_mem, especially for tables that won't fit into
cache anyway
Bump up checkpoint segments and wal_buffers.
Disable PITR
Create a table and load it's data in a single transaction (8.2 will
avoid writing any WAL data if you do this and PITR is turned off)
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 15:38 Merlin Moncure <mmoncure@gmail.com>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 1 reply; 52+ messages in thread
From: Merlin Moncure @ 2006-10-25 15:38 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-performance
On 10/25/06, Worky Workerson <worky.workerson@gmail.com> wrote:
> > I'm guessing the high bursts are checkpoints. Can you check your log
> > files for pg and see if you are getting warnings about checkpoint
> > frequency? You can get some mileage here by increasing wal files.
>
> Nope, nothing in the log. I have set:
> wal_buffers=128
> checkpoint_segments=128
> checkpoint_timeout=3000
> which I thought was rather generous. Perhaps I should set it even
> higher for the loads?
>
> > Have you determined that pg is not swapping? try upping maintenance_work_mem.
>
> maintenance_work_mem = 524288 ... should I increase it even more?
> Doesn't look like pg is swapping ...
nah, you already addressed it. either pg is swapping or it isnt, and
i'm guessing it isn't.
> I'm currently running bonnie++ with the defaults ... should I change
> the execution to better mimic Postgres' behavior?
just post what you have...
> RHEL 4.3 x86_64
> HP DL585, 4 Dual Core Opteron 885s
> 16 GB RAM
> 2x300GB 10K SCSI320, RAID10
> HP MSA1000 SAN direct connected via single 2GB Fibre Channel Arbitrated Loop
> 10x300GB 10K SCSI320, RAID10
in theory, with 10 10k disks in raid 10, you should be able to keep
your 2fc link saturated all the time unless your i/o is extremely
random. random i/o is the wild card here, ideally you should see at
least 2000 seeks in bonnie...lets see what comes up.
hopefully, bonnie will report close to 200 mb/sec. in extreme
sequential cases, the 2fc link should be a bottleneck if the raid
controller is doing its job.
if you are having cpu issues, try breaking your process down to at
least 4 processes (you have quad dual core box after all)...thats a no
brainer.
merlin
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 16:34 Luke Lonergan <llonergan@greenplum.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 52+ messages in thread
From: Luke Lonergan @ 2006-10-25 16:34 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; Worky Workerson <worky.workerson@gmail.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-performance
Merlin,
On 10/25/06 8:38 AM, "Merlin Moncure" <mmoncure@gmail.com> wrote:
> in theory, with 10 10k disks in raid 10, you should be able to keep
> your 2fc link saturated all the time unless your i/o is extremely
> random. random i/o is the wild card here, ideally you should see at
> least 2000 seeks in bonnie...lets see what comes up.
The 2000 seeks/sec are irrelevant to Postgres with one user doing COPY.
Because the I/O is single threaded, you will get one disk worth of seeks for
one user, roughly 150/second on a 10K RPM drive.
I suspect the problem here is the sequential I/O rate - let's wait and see
what the dd test results look like.
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 16:52 Craig A. James <cjames@modgraph-usa.com>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 0 replies; 52+ messages in thread
From: Craig A. James @ 2006-10-25 16:52 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Jim C. Nasby wrote:
> Wait... so you're using perl to copy data between two tables? And using
> a cursor to boot? I can't think of any way that could be more
> inefficient...
>
> What's wrong with a plain old INSERT INTO ... SELECT? Or if you really
> need to break it into multiple transaction blocks, at least don't
> shuffle the data from the database into perl and then back into the
> database; do an INSERT INTO ... SELECT with that same where clause.
The data are on two different computers, and I do processing of the data as it passes through the application. Otherwise, the INSERT INTO ... SELECT is my first choice.
Craig
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 17:15 Spiegelberg, Greg <gspiegelberg@cranel.com>
0 siblings, 1 reply; 52+ messages in thread
From: Spiegelberg, Greg @ 2006-10-25 17:15 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; Jim C. Nasby <jim@nasby.net>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Craig A. James
> Sent: Wednesday, October 25, 2006 12:52 PM
> To: Jim C. Nasby
> Cc: Worky Workerson; Merlin Moncure; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Best COPY Performance
>
> Jim C. Nasby wrote:
> > Wait... so you're using perl to copy data between two tables? And
> > using a cursor to boot? I can't think of any way that could be more
> > inefficient...
> >
> > What's wrong with a plain old INSERT INTO ... SELECT? Or if
> you really
> > need to break it into multiple transaction blocks, at least don't
> > shuffle the data from the database into perl and then back into the
> > database; do an INSERT INTO ... SELECT with that same where clause.
>
> The data are on two different computers, and I do processing
> of the data as it passes through the application. Otherwise,
> the INSERT INTO ... SELECT is my first choice.
Would dblink() help in any way?
Greg
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 17:28 Craig A. James <cjames@modgraph-usa.com>
parent: Spiegelberg, Greg <gspiegelberg@cranel.com>
0 siblings, 1 reply; 52+ messages in thread
From: Craig A. James @ 2006-10-25 17:28 UTC (permalink / raw)
To: Spiegelberg, Greg <gspiegelberg@cranel.com>; +Cc: Jim C. Nasby <jim@nasby.net>; Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Spiegelberg, Greg wrote:
>> The data are on two different computers, and I do processing
>> of the data as it passes through the application. Otherwise,
>> the INSERT INTO ... SELECT is my first choice.
>
> Would dblink() help in any way?
It might if perl wasn't so damned good at this. ;-)
Craig
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 18:26 Worky Workerson <worky.workerson@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 52+ messages in thread
From: Worky Workerson @ 2006-10-25 18:26 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Markus Schaber <schabi@logix-tt.com>; pgsql-performance
Merlin/Luke:
> > in theory, with 10 10k disks in raid 10, you should be able to keep
> > your 2fc link saturated all the time unless your i/o is extremely
> > random. random i/o is the wild card here, ideally you should see at
> > least 2000 seeks in bonnie...lets see what comes up.
> I suspect the problem here is the sequential I/O rate - let's wait and see
> what the dd test results look like.
Here are the tests that you suggested that I do, on both the local
disks (WAL) and the SAN (tablespace). The random seeks seem to be far
below what Merlin said was "good", so I am a bit concerned. There is
a bit of other activity on the box at the moment which is hard to
stop, so that might have had an impact on the processing.
Here is the bonnie++ output:
Version 1.03 ------Sequential Output------ --Sequential Input- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
Local Disks 31G 45119 85 56548 21 27527 8 35069 66 86506 13 499.6 1
SAN 31G 53544 98 93385 35 18266 5 24970 47 57911 8 611.8 1
And here are the dd results for 16GB RAM, i.e. 4,000,000 8K blocks:
# Local Disks
$ time bash -c "dd if=/dev/zero of=/home/myhome/bigfile bs=8k
count=4000000 && sync"
4000000+0 records in
4000000+0 records out
real 10m0.382s
user 0m1.117s
sys 2m45.681s
$ time dd if=/home/myhome/bigfile of=/dev/null bs=8k count=4000000
4000000+0 records in
4000000+0 records out
real 6m22.904s
user 0m0.717s
sys 0m53.766s
# Fibre Channel SAN
$ time bash -c "dd if=/dev/zero of=/data/test/bigfile bs=8k
count=4000000 && sync"
4000000+0 records in
4000000+0 records out
real 5m58.846s
user 0m1.096s
sys 2m18.026s
$ time dd if=/data/test/bigfile of=/dev/null bs=8k count=4000000
4000000+0 records in
4000000+0 records out
real 14m9.560s
user 0m0.739s
sys 0m53.806s
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 18:58 Markus Schaber <schabi@logix-tt.com>
parent: Craig A. James <cjames@modgraph-usa.com>
0 siblings, 0 replies; 52+ messages in thread
From: Markus Schaber @ 2006-10-25 18:58 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Spiegelberg, Greg <gspiegelberg@cranel.com>; Jim C. Nasby <jim@nasby.net>; Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Hi, Craig,
Craig A. James wrote:
>> Would dblink() help in any way?
>
> It might if perl wasn't so damned good at this. ;-)
You know that you can use Perl inside PostgreS via plperl?
HTH,
Markus
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-25 20:13 Luke Lonergan <llonergan@greenplum.com>
parent: Worky Workerson <worky.workerson@gmail.com>
0 siblings, 3 replies; 52+ messages in thread
From: Luke Lonergan @ 2006-10-25 20:13 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Markus Schaber <schabi@logix-tt.com>; pgsql-performance
Mr. Worky,
On 10/25/06 11:26 AM, "Worky Workerson" <worky.workerson@gmail.com> wrote:
> And here are the dd results for 16GB RAM, i.e. 4,000,000 8K blocks:
So, if we divide 32,000 MB by the real time, we get:
/home (WAL):
53 MB/s write
84 MB/s read
/data (data):
89 MB/s write
38 MB/s read
The write and read speeds on /home look like a single disk drive, which is
not good if you have more drives in a RAID. OTOH, it should be sufficient
for WAL writing and you should think that the COPY speed won't be limited by
WAL.
The read speed on your /data volume is awful to the point where you should
consider it broken and find a fix. A quick comparison: the same number on a
16 drive internal SATA array with 7200 RPM disks gets 950 MB/s read, about
25 times faster for about 1/4 the price.
But again, this may not have anything to do with the speed of your COPY
statements.
Can you provide about 10 seconds worth of "vmstat 1" while running your COPY
so we can get a global view of the I/O and CPU?
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-26 03:34 Jim C. Nasby <jim@nasby.net>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 1 reply; 52+ messages in thread
From: Jim C. Nasby @ 2006-10-26 03:34 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Markus Schaber <schabi@logix-tt.com>; pgsql-performance
On Wed, Oct 25, 2006 at 11:25:01AM -0400, Worky Workerson wrote:
> >I'm guessing the high bursts are checkpoints. Can you check your log
> >files for pg and see if you are getting warnings about checkpoint
> >frequency? You can get some mileage here by increasing wal files.
>
> Nope, nothing in the log. I have set:
> wal_buffers=128
> checkpoint_segments=128
> checkpoint_timeout=3000
> which I thought was rather generous. Perhaps I should set it even
> higher for the loads?
But depending on your shared_buffer and bgwriter settings (as well as
how much WAL traffic you're generating, you could still end up with big
slugs of work to be done when checkpoints happen.
If you set checkpoint_warning to 3001, you'll see exactly when
checkpoints are happening, so you can determine if that's an issue.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-27 18:39 Worky Workerson <worky.workerson@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
2 siblings, 1 reply; 52+ messages in thread
From: Worky Workerson @ 2006-10-27 18:39 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: pgsql-performance
I do have a dirty little secret, one which I wasn't completely aware
of until a little while ago. Apparently, someone decided to install
Oracle on the server, and use the SAN as the primary tablespace, so
that might have something to do with the poor performance of the SAN.
At least, I'm hoping that's what is involved. I'll be able to tell
for sure when I can rerun the benchmarks on Monday without Oracle.
Thank you all for all your help so far. I've learned a ton about
Postgres (and life!) from both this thread and this list in general,
and the "support" has been nothing less than spectacular.
I'm hoping that the corporate Oracle machine won't shut down my pg
projects. On total side note, if anyone knows how to best limit
Oracle's impact on a system (i.e. memory usage, etc), I'd be
interested.
I hate shared DB servers.
Thanks!
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-27 19:01 Merlin Moncure <mmoncure@gmail.com>
parent: Worky Workerson <worky.workerson@gmail.com>
0 siblings, 0 replies; 52+ messages in thread
From: Merlin Moncure @ 2006-10-27 19:01 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; pgsql-performance
On 10/27/06, Worky Workerson <worky.workerson@gmail.com> wrote:
> I'm hoping that the corporate Oracle machine won't shut down my pg
> projects. On total side note, if anyone knows how to best limit
> Oracle's impact on a system (i.e. memory usage, etc), I'd be
> interested.
rm -rf /usr/local/oracle?
merlin
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-27 19:08 Worky Workerson <worky.workerson@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
2 siblings, 1 reply; 52+ messages in thread
From: Worky Workerson @ 2006-10-27 19:08 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: pgsql-performance
> The read speed on your /data volume is awful to the point where you should
> consider it broken and find a fix. A quick comparison: the same number on a
> 16 drive internal SATA array with 7200 RPM disks gets 950 MB/s read, about
> 25 times faster for about 1/4 the price.
I'm hoping that the poor performance is a result of Oracle doing
random reads while I try and do the sequential read. If not (I'll
test on Monday), I'll start looking other places. Any immediate
suggestions?
> Can you provide about 10 seconds worth of "vmstat 1" while running your COPY
> so we can get a global view of the I/O and CPU?
Here it is, taken from a spot about halfway through a 'cat file |
psql' load, with the "Oracle-is-installed-and-running" caveat:
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1
1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1
1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1
1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1
2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0
1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1
1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1
1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1
1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1
1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-28 01:00 Luke Lonergan <llonergan@greenplum.com>
parent: Worky Workerson <worky.workerson@gmail.com>
0 siblings, 2 replies; 52+ messages in thread
From: Luke Lonergan @ 2006-10-28 01:00 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-performance
Worky (that your real name? :-)
On 10/27/06 12:08 PM, "Worky Workerson" <worky.workerson@gmail.com> wrote:
> Here it is, taken from a spot about halfway through a 'cat file |
> psql' load, with the "Oracle-is-installed-and-running" caveat:
>
> r b swpd free buff cache si so bi bo in cs us sy id wa
> 1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1
> 1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1
> 1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1
> 1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1
> 2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0
> 1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1
> 1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1
> 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1
> 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1
> 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1
It doesn't look like there's anything else running - the runnable "r" is
about 1. Your "bo" blocks output rate is about 16MB/s, so divide by 3 and
you're about in range with your 5MB/s COPY rate. The interesting thing is
that the I/O wait is pretty low.
How many CPUs on the machine? Can you send the result of "cat
/proc/cpuinfo"?
Is your "cat file | psql" being done on the DBMS server or is it on the
network?
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-28 03:12 Merlin Moncure <mmoncure@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
1 sibling, 1 reply; 52+ messages in thread
From: Merlin Moncure @ 2006-10-28 03:12 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Worky Workerson <worky.workerson@gmail.com>; pgsql-performance
On 10/28/06, Luke Lonergan <llonergan@greenplum.com> wrote:
> Worky (that your real name? :-)
>
>
> On 10/27/06 12:08 PM, "Worky Workerson" <worky.workerson@gmail.com> wrote:
>
> > Here it is, taken from a spot about halfway through a 'cat file |
> > psql' load, with the "Oracle-is-installed-and-running" caveat:
> >
> > r b swpd free buff cache si so bi bo in cs us sy id wa
> > 1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1
> > 1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1
> > 1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1
> > 1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1
> > 2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0
> > 1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1
> > 1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1
> > 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1
> > 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1
> > 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1
>
> It doesn't look like there's anything else running - the runnable "r" is
> about 1. Your "bo" blocks output rate is about 16MB/s, so divide by 3 and
> you're about in range with your 5MB/s COPY rate. The interesting thing is
> that the I/O wait is pretty low.
>
> How many CPUs on the machine? Can you send the result of "cat
> /proc/cpuinfo"?
>
> Is your "cat file | psql" being done on the DBMS server or is it on the
> network?
iirc, he is running quad opteron 885 (8 cores), so if my math is
correct he can split up his process for an easy gain.
merlin
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-28 03:43 Worky Workerson <worky.workerson@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
1 sibling, 0 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-28 03:43 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: pgsql-performance
> Worky (that your real name? :-)
Nope, its Mike. worky.workerson is just the email that I use for "work" :)
> How many CPUs on the machine? Can you send the result of "cat
> /proc/cpuinfo"?
Not at work at the moment, however I do have quad dual-core opterons,
like Merlin mentioned.
> Is your "cat file | psql" being done on the DBMS server or is it on the
> network?
Everything is currently being done on the same DB server. The data
that is being loaded is on the same 2-disk "RAID10" as the OS and WAL.
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-28 03:47 Worky Workerson <worky.workerson@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 52+ messages in thread
From: Worky Workerson @ 2006-10-28 03:47 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; pgsql-performance
On 10/27/06, Merlin Moncure <mmoncure@gmail.com> wrote:
> > > r b swpd free buff cache si so bi bo in cs us sy id wa
> > > 1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1
> > > 1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1
> > > 1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1
> > > 1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1
> > > 2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0
> > > 1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1
> > > 1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1
> > > 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1
> > > 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1
> > > 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1
> >
> > It doesn't look like there's anything else running - the runnable "r" is
> > about 1. Your "bo" blocks output rate is about 16MB/s, so divide by 3 and
> > you're about in range with your 5MB/s COPY rate. The interesting thing is
> > that the I/O wait is pretty low.
> >
> > How many CPUs on the machine? Can you send the result of "cat
> > /proc/cpuinfo"?
>
> iirc, he is running quad opteron 885 (8 cores), so if my math is
> correct he can split up his process for an easy gain.
Are you saying that I should be able to issue multiple COPY commands
because my I/O wait is low? I was under the impression that I am I/O
bound, so multiple simeoultaneous loads would have a detrimental
effect ...
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-28 04:07 Luke Lonergan <llonergan@greenplum.com>
parent: Worky Workerson <worky.workerson@gmail.com>
0 siblings, 2 replies; 52+ messages in thread
From: Luke Lonergan @ 2006-10-28 04:07 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
Worky,
On 10/27/06 8:47 PM, "Worky Workerson" <worky.workerson@gmail.com> wrote:
>>>> 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1
>>>> 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1
>>>> 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1
>>
>> iirc, he is running quad opteron 885 (8 cores), so if my math is
>> correct he can split up his process for an easy gain.
>
> Are you saying that I should be able to issue multiple COPY commands
> because my I/O wait is low? I was under the impression that I am I/O
> bound, so multiple simeoultaneous loads would have a detrimental
> effect ...
The reason I asked how many CPUs was to make sense of the 12% usr CPU time
in the above. That means you are CPU bound and are fully using one CPU. So
you aren't being limited by the I/O in this case, it's the CPU.
I agree with Merlin that you can speed things up by breaking the file up.
Alternately you can use the OSS Bizgres java loader, which lets you specify
the number of I/O threads with the "-n" option on a single file.
OTOH, you should find that you will only double your COPY speed with this
approach because your write speed as you previously found was limited to 30
MB/s.
For now, you could simply split the file in two pieces and load two copies
at once, then watch the same "vmstat 1" for 10 seconds and look at your "bo"
rate.
If this does speed things up, you really should check out the Bizgres Java
loader.
The other thing to wonder about though is why you are so CPU bound at 5
MB/s. What version of Postgres is this?
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-28 12:03 Michael Artz <mlartz@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
1 sibling, 1 reply; 52+ messages in thread
From: Michael Artz @ 2006-10-28 12:03 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
> > Are you saying that I should be able to issue multiple COPY commands
> > because my I/O wait is low? I was under the impression that I am I/O
> > bound, so multiple simeoultaneous loads would have a detrimental
> > effect ...
>
> The reason I asked how many CPUs was to make sense of the 12% usr CPU time
> in the above. That means you are CPU bound and are fully using one CPU. So
> you aren't being limited by the I/O in this case, it's the CPU.
>
> I agree with Merlin that you can speed things up by breaking the file up.
> Alternately you can use the OSS Bizgres java loader, which lets you specify
> the number of I/O threads with the "-n" option on a single file.
Thanks, I'll try that on Monday.
> The other thing to wonder about though is why you are so CPU bound at 5
> MB/s. What version of Postgres is this?
I was wondering about that as well, and the only thing that I can
think of is that its the PK btree index creation on the IP4.
PG 8.1.3 x86_64. I installed it via a RH rpm for their "Web Services
Beta", or something like that. I know I'm a bit behind the times, but
getting stuff in (and out) of my isolated lab is a bit of a pain.
I'll compile up a 8.2 beta as well and see how that works out.
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-28 15:41 Luke Lonergan <llonergan@greenplum.com>
parent: Michael Artz <mlartz@gmail.com>
0 siblings, 1 reply; 52+ messages in thread
From: Luke Lonergan @ 2006-10-28 15:41 UTC (permalink / raw)
To: Michael Artz <mlartz@gmail.com>; +Cc: Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Michael (aka Worky),
On 10/28/06 5:03 AM, "Michael Artz" <mlartz@gmail.com> wrote:
> PG 8.1.3 x86_64. I installed it via a RH rpm for their "Web Services
> Beta", or something like that. I know I'm a bit behind the times, but
> getting stuff in (and out) of my isolated lab is a bit of a pain.
> I'll compile up a 8.2 beta as well and see how that works out.
I think 8.1 and 8.2 should be the same in this regard.
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.
Note that the primary key is the first column.
Table "public.part"
Column | Type | Modifiers
---------------+-----------------------+-----------
p_partkey | integer | not null
p_name | character varying(55) | not null
p_mfgr | text | not null
p_brand | text | not null
p_type | character varying(25) | not null
p_size | integer | not null
p_container | text | not null
p_retailprice | double precision | not null
p_comment | character varying(23) | not null
Indexes:
"part_pkey" PRIMARY KEY, btree (p_partkey)
What is your schema for the table?
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-30 14:09 Spiegelberg, Greg <gspiegelberg@cranel.com>
0 siblings, 1 reply; 52+ messages in thread
From: Spiegelberg, Greg @ 2006-10-30 14:09 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Luke Lonergan
> Sent: Saturday, October 28, 2006 12:07 AM
> To: Worky Workerson; Merlin Moncure
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Best COPY Performance
>
> Worky,
>
> On 10/27/06 8:47 PM, "Worky Workerson"
> <worky.workerson@gmail.com> wrote:
>
> > Are you saying that I should be able to issue multiple COPY
> commands
> > because my I/O wait is low? I was under the impression
> that I am I/O
> > bound, so multiple simeoultaneous loads would have a detrimental
> > effect ...
>
> ...
> I agree with Merlin that you can speed things up by breaking
> the file up.
> Alternately you can use the OSS Bizgres java loader, which
> lets you specify the number of I/O threads with the "-n"
> option on a single file.
As a result of this thread, and b/c I've tried this in the past but
never had much success at speeding the process up, I attempted just that
here except via 2 psql CLI's with access to the local file. 1.1M rows
of data varying in width from 40 to 200 characters COPY'd to a table
with only one text column, no keys, indexes, &c took about 15 seconds to
load. ~73K rows/second.
I broke that file into 2 files each of 550K rows and performed 2
simultaneous COPY's after dropping the table, recreating, issuing a sync
on the system to be sure, &c and nearly every time both COPY's finish in
12 seconds. About a 20% gain to ~91K rows/second.
Admittedly, this was a pretty rough test but a 20% savings, if it can be
put into production, is worth exploring for us.
B/c I'll be asked, I did this on an idle, dual 3.06GHz Xeon with 6GB of
memory, U320 SCSI internal drives and PostgreSQL 8.1.4.
Greg
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-30 14:23 Luke Lonergan <llonergan@greenplum.com>
parent: Spiegelberg, Greg <gspiegelberg@cranel.com>
0 siblings, 1 reply; 52+ messages in thread
From: Luke Lonergan @ 2006-10-30 14:23 UTC (permalink / raw)
To: Spiegelberg, Greg <gspiegelberg@cranel.com>; Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
Greg,
On 10/30/06 7:09 AM, "Spiegelberg, Greg" <gspiegelberg@cranel.com> wrote:
> I broke that file into 2 files each of 550K rows and performed 2
> simultaneous COPY's after dropping the table, recreating, issuing a sync
> on the system to be sure, &c and nearly every time both COPY's finish in
> 12 seconds. About a 20% gain to ~91K rows/second.
>
> Admittedly, this was a pretty rough test but a 20% savings, if it can be
> put into production, is worth exploring for us.
Did you see whether you were I/O or CPU bound in your single threaded COPY?
A 10 second "vmstat 1" snapshot would tell you/us.
With Mr. Workerson (:-) I'm thinking his benefit might be a lot better
because the bottleneck is the CPU and it *may* be the time spent in the
index building bits.
We've found that there is an ultimate bottleneck at about 12-14MB/s despite
having sequential write to disk speeds of 100s of MB/s. I forget what the
latest bottleneck was.
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-30 15:03 Luke Lonergan <llonergan@greenplum.com>
parent: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
0 siblings, 1 reply; 52+ messages in thread
From: Luke Lonergan @ 2006-10-30 15:03 UTC (permalink / raw)
To: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>; +Cc: Spiegelberg, Greg <gspiegelberg@cranel.com>; Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Stefan,
On 10/30/06 8:57 AM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:
>> We've found that there is an ultimate bottleneck at about 12-14MB/s despite
>> having sequential write to disk speeds of 100s of MB/s. I forget what the
>> latest bottleneck was.
>
> I have personally managed to load a bit less then 400k/s (5 int columns
> no indexes) - on very fast disk hardware - at that point postgresql is
> completely CPU bottlenecked (2,6Ghz Opteron).
400,000 rows/s x 4 bytes/column x 5 columns/row = 8MB/s
> Using multiple processes to load the data will help to scale up to about
> 900k/s (4 processes on 4 cores).
18MB/s? Have you done this? I've not seen this much of an improvement
before by using multiple COPY processes to the same table.
Another question: how to measure MB/s - based on the input text file? On
the DBMS storage size? We usually consider the input text file in the
calculation of COPY rate.
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-30 15:57 Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 52+ messages in thread
From: Stefan Kaltenbrunner @ 2006-10-30 15:57 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Spiegelberg, Greg <gspiegelberg@cranel.com>; Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Luke Lonergan wrote:
> Greg,
>
> On 10/30/06 7:09 AM, "Spiegelberg, Greg" <gspiegelberg@cranel.com> wrote:
>
>> I broke that file into 2 files each of 550K rows and performed 2
>> simultaneous COPY's after dropping the table, recreating, issuing a sync
>> on the system to be sure, &c and nearly every time both COPY's finish in
>> 12 seconds. About a 20% gain to ~91K rows/second.
>>
>> Admittedly, this was a pretty rough test but a 20% savings, if it can be
>> put into production, is worth exploring for us.
>
> Did you see whether you were I/O or CPU bound in your single threaded COPY?
> A 10 second "vmstat 1" snapshot would tell you/us.
>
> With Mr. Workerson (:-) I'm thinking his benefit might be a lot better
> because the bottleneck is the CPU and it *may* be the time spent in the
> index building bits.
>
> We've found that there is an ultimate bottleneck at about 12-14MB/s despite
> having sequential write to disk speeds of 100s of MB/s. I forget what the
> latest bottleneck was.
I have personally managed to load a bit less then 400k/s (5 int columns
no indexes) - on very fast disk hardware - at that point postgresql is
completely CPU bottlenecked (2,6Ghz Opteron).
Using multiple processes to load the data will help to scale up to about
900k/s (4 processes on 4 cores).
Stefan
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-30 16:23 Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 52+ messages in thread
From: Stefan Kaltenbrunner @ 2006-10-30 16:23 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Spiegelberg, Greg <gspiegelberg@cranel.com>; Worky Workerson <worky.workerson@gmail.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Luke Lonergan wrote:
> Stefan,
>
> On 10/30/06 8:57 AM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:
>
>>> We've found that there is an ultimate bottleneck at about 12-14MB/s despite
>>> having sequential write to disk speeds of 100s of MB/s. I forget what the
>>> latest bottleneck was.
>> I have personally managed to load a bit less then 400k/s (5 int columns
>> no indexes) - on very fast disk hardware - at that point postgresql is
>> completely CPU bottlenecked (2,6Ghz Opteron).
>
> 400,000 rows/s x 4 bytes/column x 5 columns/row = 8MB/s
>
>> Using multiple processes to load the data will help to scale up to about
>> 900k/s (4 processes on 4 cores).
yes I did that about half a year ago as part of the CREATE INDEX on a
1,8B row table thread on -hackers that resulted in some some the sorting
improvements in 8.2.
I don't think there is much more possible in terms of import speed by
using more cores (at least not when importing to the same table) - iirc
I was at nearly 700k/s with two cores and 850k/s with 3 cores or such ...
>
> 18MB/s? Have you done this? I've not seen this much of an improvement
> before by using multiple COPY processes to the same table.
>
> Another question: how to measure MB/s - based on the input text file? On
> the DBMS storage size? We usually consider the input text file in the
> calculation of COPY rate.
yeah that is a good questions (and part of the reason why I cited the
rows/sec number btw.)
Stefan
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-31 20:11 Worky Workerson <worky.workerson@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
2 siblings, 2 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-31 20:11 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: pgsql-performance
> > And here are the dd results for 16GB RAM, i.e. 4,000,000 8K blocks:
>
> So, if we divide 32,000 MB by the real time, we get:
> /data (data):
> 89 MB/s write
> 38 MB/s read
... snip ...
> The read speed on your /data volume is awful to the point where you should
> consider it broken and find a fix. A quick comparison: the same number on a
> 16 drive internal SATA array with 7200 RPM disks gets 950 MB/s read, about
> 25 times faster for about 1/4 the price.
I managed to get approval to shut down the Oracle instance and reran
the dd's on the SAN (/data) and came up with about 60MB/s write (I had
missed the 'sync' in the previous runs) and about 58 MB/s read, still
no comparison on your SATA arrary. Any recommendations on what to
look at to find a fix? One thing which I never mentioned was that I
am using ext3 mounted with noatime,data=writeback.
An interesting note (at least to me) is the inverse relationship
between free memory and bo when writing with dd, i.e:
$ vmstat 5
r b swpd free buff cache si so bi bo in cs us sy id wa
0 3 244664 320688 23588 15383120 0 0 0 28 1145 197 0 1 74 25
2 6 244664 349488 22276 15204980 0 0 0 24 1137 188 0 1 75 25
2 6 244664 28264 23024 15526552 0 0 0 65102 1152 335 0 12 60 28
2 4 244664 28968 23588 15383120 0 0 1 384386 1134 372 0 19 34 47
1 5 244664 28840 23768 15215728 0 0 1 438482 1144 494 0 24 33 43
0 5 247256 41320 20144 15212788 0 524 0 57062 1142 388 0 6 43 51
1 6 247256 29096 19588 15226788 0 0 5 60999 1140 391 0 15 42 43
Is this because of the kernel attempting to cache the file in memory?
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-31 20:16 Luke Lonergan <llonergan@greenplum.com>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 0 replies; 52+ messages in thread
From: Luke Lonergan @ 2006-10-31 20:16 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-performance
Worky (!),
On 10/31/06 12:11 PM, "Worky Workerson" <worky.workerson@gmail.com> wrote:
> Any recommendations on what to
> look at to find a fix? One thing which I never mentioned was that I
> am using ext3 mounted with noatime,data=writeback.
You can try setting the max readahead like this:
/sbin/blockdev --setra 16384 /dev/sd[a-z]
It will set the max readahead to 16MB for whatever devices are in
/dev/sd[a-z] for the booted machine. You'd need to put the line(s) in
/etc/rc.d/rc.local to have the setting persist on reboot.
- Luke
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-31 20:22 Worky Workerson <worky.workerson@gmail.com>
parent: Jim C. Nasby <jim@nasby.net>
0 siblings, 0 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-31 20:22 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-performance
> >I'm guessing the high bursts are checkpoints. Can you check your log
> > >files for pg and see if you are getting warnings about checkpoint
> > >frequency? You can get some mileage here by increasing wal files.
> >
> > Nope, nothing in the log. I have set:
> > wal_buffers=128
> > checkpoint_segments=128
> > checkpoint_timeout=3000
> > which I thought was rather generous. Perhaps I should set it even
> > higher for the loads?
>
> But depending on your shared_buffer and bgwriter settings (as well as
> how much WAL traffic you're generating, you could still end up with big
> slugs of work to be done when checkpoints happen.
>
> If you set checkpoint_warning to 3001, you'll see exactly when
> checkpoints are happening, so you can determine if that's an issue.
I did that, and I get two log messages, seeing checkpoints happening
at 316 and 147 seconds apart on my load of a 1.9 GB file. Is this "an
issue"?
Thanks!
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-31 20:45 Worky Workerson <worky.workerson@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-31 20:45 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: pgsql-performance
> 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.
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-31 20:56 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 0 replies; 52+ messages in thread
From: Luke Lonergan @ 2006-10-31 20:56 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-performance
Checkpoints are not an issue here, the vmstat you included was on a 5 second interval, so the 'bursts' were bursting at a rate of 60MB/s.
- Luke
Msg is shrt cuz m on ma treo
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-31 21:03 Teemu Torma <teemu@torma.org>
parent: Worky Workerson <worky.workerson@gmail.com>
1 sibling, 0 replies; 52+ messages in thread
From: Teemu Torma @ 2006-10-31 21:03 UTC (permalink / raw)
To: pgsql-performance
On Tuesday 31 October 2006 21:11, Worky Workerson wrote:
> One thing which I never mentioned was that I
> am using ext3 mounted with noatime,data=writeback.
You might also want to try with data=ordered. I have noticed that
nowadays it seems to be a bit faster, but not much. I don't know why,
maybe it has got more optimization efforts.
Teemu
^ permalink raw reply [nested|flat] 52+ messages in thread
* Re: Best COPY Performance
@ 2006-10-31 21:13 Worky Workerson <worky.workerson@gmail.com>
parent: Luke Lonergan <llonergan@greenplum.com>
1 sibling, 0 replies; 52+ messages in thread
From: Worky Workerson @ 2006-10-31 21:13 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
> >>>> 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1
> >>>> 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1
> >>>> 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1
> >>
> >> iirc, he is running quad opteron 885 (8 cores), so if my math is
> >> correct he can split up his process for an easy gain.
> >
> > Are you saying that I should be able to issue multiple COPY commands
> > because my I/O wait is low? I was under the impression that I am I/O
> > bound, so multiple simeoultaneous loads would have a detrimental
> > effect ...
>
> The reason I asked how many CPUs was to make sense of the 12% usr CPU time
> in the above. That means you are CPU bound and are fully using one CPU. So
> you aren't being limited by the I/O in this case, it's the CPU.
... snip ...
> For now, you could simply split the file in two pieces and load two copies
> at once, then watch the same "vmstat 1" for 10 seconds and look at your "bo"
> rate.
Significantly higher on average, and a parallel loads were ~30% faster
that a single with index builds (240s vs 340s) and about ~45% (150s vs
230s) without the PK index. I'll definitely look into the bizgres
java loader.
Thanks!
^ permalink raw reply [nested|flat] 52+ messages in thread
end of thread, other threads:[~2006-10-31 21:13 UTC | newest]
Thread overview: 52+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-20 20:05 Best COPY Performance Worky Workerson <worky.workerson@gmail.com>
2006-10-21 01:11 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-23 15:10 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-23 21:59 ` Jim C. Nasby <jim@nasby.net>
2006-10-23 22:10 ` Joshua D. Drake <jd@commandprompt.com>
2006-10-23 22:37 ` Craig A. James <cjames@modgraph-usa.com>
2006-10-25 00:14 ` Jim C. Nasby <jim@nasby.net>
2006-10-24 13:17 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-25 00:17 ` Jim C. Nasby <jim@nasby.net>
2006-10-25 05:36 ` Craig A. James <cjames@modgraph-usa.com>
2006-10-25 12:03 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-25 15:06 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-25 15:28 ` Jim C. Nasby <jim@nasby.net>
2006-10-25 15:22 ` Jim C. Nasby <jim@nasby.net>
2006-10-25 16:52 ` Craig A. James <cjames@modgraph-usa.com>
2006-10-25 12:16 ` Alex Stapleton <alexs@advfn.com>
2006-10-23 09:27 ` Markus Schaber <schabi@logix-tt.com>
2006-10-23 15:11 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-23 15:40 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-25 12:18 ` Markus Schaber <schabi@logix-tt.com>
2006-10-25 13:20 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-25 15:25 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-25 15:38 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-25 16:34 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-25 18:26 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-25 20:13 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-27 18:39 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-27 19:01 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-27 19:08 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-28 01:00 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-28 03:12 ` Merlin Moncure <mmoncure@gmail.com>
2006-10-28 03:47 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-28 04:07 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-28 12:03 ` Michael Artz <mlartz@gmail.com>
2006-10-28 15:41 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-31 20:45 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-31 21:13 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-28 03:43 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-31 20:11 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-31 20:16 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-31 21:03 ` Teemu Torma <teemu@torma.org>
2006-10-26 03:34 ` Jim C. Nasby <jim@nasby.net>
2006-10-31 20:22 ` Worky Workerson <worky.workerson@gmail.com>
2006-10-25 17:15 Re: Best COPY Performance Spiegelberg, Greg <gspiegelberg@cranel.com>
2006-10-25 17:28 ` Craig A. James <cjames@modgraph-usa.com>
2006-10-25 18:58 ` Markus Schaber <schabi@logix-tt.com>
2006-10-30 14:09 Re: Best COPY Performance Spiegelberg, Greg <gspiegelberg@cranel.com>
2006-10-30 14:23 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-30 15:57 ` Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
2006-10-30 15:03 ` Luke Lonergan <llonergan@greenplum.com>
2006-10-30 16:23 ` Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
2006-10-31 20:56 Re: Best COPY Performance Luke Lonergan <LLonergan@greenplum.com>
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