pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMigration study, step 1: bulk write performance optimization
26+ messages / 13 participants
[nested] [flat]
* Migration study, step 1: bulk write performance optimization
@ 2006-03-20 14:59 Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:12 ` Re: Migration study, step 1: bulk write performance optimization Craig A. James <cjames@modgraph-usa.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-20 16:35 ` Re: Migration study, step 1: bulk write performance optimization PFC <lists@peufeu.com>
2006-03-20 17:44 ` Re: Migration study, step 1: bulk write performance optimization Dave Cramer <pg@fastcrypt.com>
2006-03-21 20:44 ` Re: Migration study, step 1: bulk write performance Simon Riggs <simon@2ndquadrant.com>
0 siblings, 5 replies; 26+ messages in thread
From: Mikael Carneholm @ 2006-03-20 14:59 UTC (permalink / raw)
To: pgsql-performance
Ok, here's the deal:
I am responisble for an exciting project of evaluating migration of a medium/large application for a well-known swedish car&truck manufacturer from a proprietary DB to Postgres. The size of the database is currently about 50Gb, annual growth depending on sales, but probably in the 30-50Gb range.
Migrating the schema was easily done, mostly involving a search/replace of some vendor specific datatypes. The next step is to migrate the data itself, and for this we have written a Java app relying on JDBC metadata to map the tables in the source schema to the target schema. The goal right now is to find the set of parameters that gives as short bulk insert time as possible, minimizing downtime while the data itself is migrated.
The machine used for the study is a Dell PE2850, 6GB memory, 1xXEON 3.0GHz/2MB cache, internal SCSI 0+1 raid (currently 4x36GB 10000rpm striped+mirrored, two more 146GB 15000rpm disks will arrive later). Not sure about the brand/model of the raid controller, so I'll leave that for now. File system is ext3(I know, maybe not the optimal choice but this is how it was when I got it) with a 8k block size. The OS currently installed is CentOS4.
Until the new disks arrive, both the OS itself, pg_xlog and the data reside on the same disks. When they arrive, I will probably move the data to the new disks (need two more to get raid 0+1, though) and leave the OS + pg_xlog on the 10000rpm disks. Mounting the 15000rpm data disks with the noatime option (this is safe, right?) and using a 16kb block size (for read performance) will probably be considered as well.
NOTE: this machine/configuration is NOT what we will be using in production if the study turns out OK, it's just supposed to work as a development machine in the first phase whose purpose more or less is to get familiar with configurating Postgres and see if we can get the application up and running (we will probably use a 64bit platform and either a FC SAN or internal raid with a battery backed cache for production use, if all goes well).
The first thing I did when I got the machine was to do a raw dd write test:
# time bash -c "(dd if=/dev/zero of=/opt/bigfile count=1310720 bs=8k && sync)"
1310720+0 records in
1310720+0 records out
real 2m21.438s
user 0m0.998s
sys 0m51.347s
(10*1024)Mb/~141s => ~75.5Mb/s
As a simple benchmark, I created a simple table without PK/indexes with 1k wide rows:
create table iotest.one_kb_rows
(
the_col char(1024) not null
);
To fill the table, I use this simple function:
create or replace function iotest.writestress(megs integer) returns void as $$
declare
char_str char(1024) := repeat('x', 1024);
begin
for i in 1..megs loop
for j in 1..1024 loop
insert into one_kb_rows(the_col) values (char_str);
end loop;
end loop;
end;
$$
language plpgsql;
Then, I tested how long it takes to write 10Gb of data to this table:
iotest=> \timing
Timing is on.
iotest=> select writestress((10*1024));
writestress
-------------
(1 row)
Time: 379971.252 ms
This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off), compared to the raw dd result (~75.5Mb/s).
I assume this difference is due to:
- simultaneous WAL write activity (assumed: for each byte written to the table, at least one byte is also written to WAL, in effect: 10Gb data inserted in the table equals 20Gb written to disk)
- lousy test method (it is done using a function => the transaction size is 10Gb, and 10Gb will *not* fit in wal_buffers :) )
- poor config
- something else?
I have tried to read up as much as possible on Postgres configuration (disk layout, buffer management, WAL sync methods, etc) and found this post regarding bgwriter tweaking: http://archives.postgresql.org/pgsql-performance/2006-03/msg00218.php - which explains the bgwriter config below.
All params in postgresql.conf that are not commented out:
---------------------------------------------------------
max_connections = 100
superuser_reserved_connections = 2
shared_buffers = 16000
bgwriter_lru_percent = 20
bgwriter_lru_maxpages = 160
bgwriter_all_percent = 10
bgwriter_all_maxpages = 320
fsync = off
wal_sync_method = open_sync
wal_buffers = 128
checkpoint_segments = 3
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1440
log_line_prefix = '%m: (%u@%d) '
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
fsync can safely be kept off during data migration as we are able to restart the procedure without losing data if something goes wrong. Increasing chekpoint_segments to 8/16/32 only increased the insert time, so I kept it at the default. I will increase shared_buffers and effective_cache_size as soon as it's time to tweak read performance, but for now I'm just focusing on write performance.
Postgres version used:
iotest=> select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
(1 row)
I want to make sure I have made correct assumptions before I carry on, so comments are welcome.
- Mikael
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance optimization
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
@ 2006-03-20 15:12 ` Craig A. James <cjames@modgraph-usa.com>
2006-03-21 00:27 ` Re: Migration study, step 1: bulk write performance optimization Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 1 reply; 26+ messages in thread
From: Craig A. James @ 2006-03-20 15:12 UTC (permalink / raw)
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; +Cc: pgsql-performance
Mikael Carneholm wrote:
> I am responisble for an exciting project of evaluating migration of a
> medium/large application for a well-known swedish car&truck manufacturer
> ... The goal right now is to find the set of parameters that gives as
> short bulk insert time as possible, minimizing downtime while the data
> itself is migrated.
If you haven't explored the COPY command yet, check it out. It is stunningly fast compared to normal INSERT commands.
http://www.postgresql.org/docs/8.1/static/sql-copy.html
pg_dump and pg_restore make use of the COPY command. Since you're coming from a different vendor, you'd have to dump the data into a COPY-compatible set of files yourself. But it will be worth the effort.
Craig
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance optimization
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:12 ` Re: Migration study, step 1: bulk write performance optimization Craig A. James <cjames@modgraph-usa.com>
@ 2006-03-21 00:27 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 26+ messages in thread
From: Tom Lane @ 2006-03-21 00:27 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> If you haven't explored the COPY command yet, check it out. It is stunningly fast compared to normal INSERT commands.
Note also that his "benchmark" is testing multiple INSERTs issued within
a loop in a plpgsql function, which has got nearly nothing to do with
the performance that will be obtained from INSERTs issued by a client
(especially if said INSERTs aren't prepared and/or aren't batched into
transactions).
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
@ 2006-03-20 15:19 ` Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
4 siblings, 1 reply; 26+ messages in thread
From: Csaba Nagy @ 2006-03-20 15:19 UTC (permalink / raw)
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; +Cc: pgsql-performance
Mikael,
I've just recently passed such an experience, i.e. migrating from
another vendor to postgres of a DB about the same size category you
have.
I think you got it right with the fsync turned off during migration
(just don't forget to turn it back after finishing ;-), and using tables
without indexes/foreign keys. In our case recreating all the
indexes/foreign keys/other constraints took actually longer than the raw
data transfer itself... but it's possible that the process was not tuned
100%, we are still learning how to tune postgres...
What I can add from our experience: ext3 turned out lousy for our
application, and converting to XFS made a quite big improvement for our
DB load. I don't have hard figures, but I think it was some 30%
improvement in overall speed, and it had a huge improvement for heavy
load times... what I mean is that with ext3 we had multiple parallel big
tasks executing in more time than if we would have executed them
sequentially, and with XFS that was gone, load scales linearly. In any
case you should test the performance of your application on different FS
and different settings, as this could make a huge difference.
And another thing, we're still fighting with performance problems due to
the fact that our application was designed to perform well with the
other DB product... I think you'll have more work to do in this regard
than just some search/replace ;-)
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
@ 2006-03-21 11:27 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
0 siblings, 1 reply; 26+ messages in thread
From: Jim C. Nasby @ 2006-03-21 11:27 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
On Mon, Mar 20, 2006 at 04:19:12PM +0100, Csaba Nagy wrote:
> What I can add from our experience: ext3 turned out lousy for our
> application, and converting to XFS made a quite big improvement for our
> DB load. I don't have hard figures, but I think it was some 30%
> improvement in overall speed, and it had a huge improvement for heavy
> load times... what I mean is that with ext3 we had multiple parallel big
> tasks executing in more time than if we would have executed them
> sequentially, and with XFS that was gone, load scales linearly. In any
> case you should test the performance of your application on different FS
> and different settings, as this could make a huge difference.
Did you try mounting ext3 whith data=writeback by chance? People have
found that makes a big difference in performance.
http://archives.postgresql.org/pgsql-performance/2003-01/msg00320.php
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-21 11:52 ` Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 26+ messages in thread
From: Csaba Nagy @ 2006-03-21 11:52 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
> Did you try mounting ext3 whith data=writeback by chance? People have
> found that makes a big difference in performance.
I'm not sure, there's other people here doing the OS stuff - I'm pretty
much ignorant about what "data=writeback" could mean :-D
They knew however that for the data partitions no FS journaling is
needed, and for the WAL partition meta data journaling is enough, so I
guess they tuned ext3 for this.
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
@ 2006-03-21 11:56 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 11:59 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 12:01 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 26+ messages in thread
From: Steinar H. Gunderson @ 2006-03-21 11:56 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
On Tue, Mar 21, 2006 at 12:52:46PM +0100, Csaba Nagy wrote:
> They knew however that for the data partitions no FS journaling is
> needed, and for the WAL partition meta data journaling is enough, so I
> guess they tuned ext3 for this.
For the record, that's the wrong way round. For the data partitioning
metadata journaling is enough, and for the WAL partition you don't need any
FS journaling at all.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2006-03-21 11:59 ` Csaba Nagy <nagy@ecircle-ag.com>
1 sibling, 0 replies; 26+ messages in thread
From: Csaba Nagy @ 2006-03-21 11:59 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
> For the record, that's the wrong way round. For the data partitioning
> metadata journaling is enough, and for the WAL partition you don't need any
> FS journaling at all.
Yes, you're right: the data partition shouldn't loose file creation,
deletion, etc., which is not important for the WAL partition where the
WAL files are mostly recycled... right ?
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2006-03-21 12:01 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:10 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:48 ` Re: Migration study, step 1: bulk write performance Michael Stone <mstone+postgres@mathom.us>
1 sibling, 2 replies; 26+ messages in thread
From: Jim C. Nasby @ 2006-03-21 12:01 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
On Tue, Mar 21, 2006 at 12:56:18PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 12:52:46PM +0100, Csaba Nagy wrote:
> > They knew however that for the data partitions no FS journaling is
> > needed, and for the WAL partition meta data journaling is enough, so I
> > guess they tuned ext3 for this.
>
> For the record, that's the wrong way round. For the data partitioning
> metadata journaling is enough, and for the WAL partition you don't need any
> FS journaling at all.
Are you sure? Metadate changes are probably a lot more common on the WAL
partition. In any case, I don't see why there should be a difference.
The real issue is: is related filesystem metadata sync'd as part of a
file being fsync'd?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:01 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-21 12:10 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:18 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 26+ messages in thread
From: Steinar H. Gunderson @ 2006-03-21 12:10 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
On Tue, Mar 21, 2006 at 06:01:58AM -0600, Jim C. Nasby wrote:
> Are you sure? Metadate changes are probably a lot more common on the WAL
> partition. In any case, I don't see why there should be a difference.
> The real issue is: is related filesystem metadata sync'd as part of a
> file being fsync'd?
I've been told on this list that PostgreSQL actually takes care to fill a new
WAL file with zeroes etc. when initializing it; dig a few months back and I'm
sure it's there.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:01 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:10 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2006-03-21 12:18 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:29 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 26+ messages in thread
From: Jim C. Nasby @ 2006-03-21 12:18 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
On Tue, Mar 21, 2006 at 01:10:32PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 06:01:58AM -0600, Jim C. Nasby wrote:
> > Are you sure? Metadate changes are probably a lot more common on the WAL
> > partition. In any case, I don't see why there should be a difference.
> > The real issue is: is related filesystem metadata sync'd as part of a
> > file being fsync'd?
>
> I've been told on this list that PostgreSQL actually takes care to fill a new
> WAL file with zeroes etc. when initializing it; dig a few months back and I'm
> sure it's there.
That's fine and all, but does no good if the filesystem doesn't know
that the file exists on a crash. The same concern is also true on the
data partition, although it's less likely to be a problem because if you
happen to crash soon after a DDL operation it's likely that you haven't
had a checkpoint yet, so the operation will likely be repeated during
WAL replay. But depending on that is a race condition.
Basically, you need to know for certain that if PostgreSQL creates a
file and then fsync's it that that file is safely on disk, and that the
filesystem knows how to find it (ie: the metadata is also on disk in
some fashion).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:01 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:10 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:18 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-21 12:29 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:45 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 26+ messages in thread
From: Steinar H. Gunderson @ 2006-03-21 12:29 UTC (permalink / raw)
To: pgsql-performance
On Tue, Mar 21, 2006 at 06:18:39AM -0600, Jim C. Nasby wrote:
> Basically, you need to know for certain that if PostgreSQL creates a
> file and then fsync's it that that file is safely on disk, and that the
> filesystem knows how to find it (ie: the metadata is also on disk in
> some fashion).
It seems to do, quoting Tom from
http://archives.postgresql.org/pgsql-performance/2005-11/msg00184.php:
== snip ==
No, Mike is right: for WAL you shouldn't need any journaling. This is
because we zero out *and fsync* an entire WAL file before we ever
consider putting live WAL data in it. During live use of a WAL file,
its metadata is not changing. As long as the filesystem follows
the minimal rule of syncing metadata about a file when it fsyncs the
file, all the live WAL files should survive crashes OK.
We can afford to do this mainly because WAL files can normally be
recycled instead of created afresh, so the zero-out overhead doesn't
get paid during normal operation.
You do need metadata journaling for all non-WAL PG files, since we don't
fsync them every time we extend them; which means the filesystem could
lose track of which disk blocks belong to such a file, if it's not
journaled.
== snip ==
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:01 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:10 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:18 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:29 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2006-03-21 12:45 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 26+ messages in thread
From: Jim C. Nasby @ 2006-03-21 12:45 UTC (permalink / raw)
To: pgsql-performance
On Tue, Mar 21, 2006 at 01:29:54PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 06:18:39AM -0600, Jim C. Nasby wrote:
> > Basically, you need to know for certain that if PostgreSQL creates a
> > file and then fsync's it that that file is safely on disk, and that the
> > filesystem knows how to find it (ie: the metadata is also on disk in
> > some fashion).
>
> It seems to do, quoting Tom from
> http://archives.postgresql.org/pgsql-performance/2005-11/msg00184.php:
404 :(
>
> == snip ==
> its metadata is not changing. As long as the filesystem follows
> the minimal rule of syncing metadata about a file when it fsyncs the
> file, all the live WAL files should survive crashes OK.
And therin lies the rub: file metadata *must* commit to disk as part of
an fsync, and it's needed for both WAL and heap data. It's needed for
heap data because as soon as a checkpoint completes, PostgreSQL is free
to erase any WAL info about previous DDL changes.
On FreeBSD, if you're using softupdates, the filesystem will properly
order writes to the drive so that metadata must be written before file
data; this ensures that an fsync on the file will first write any
metadata before writing the data itself.
With fsync turned off, any metadata-changing commands will wait for the
metadata to commit to disk before returning (unless you run async...)
I'm not really sure how this all plays out on a journalling filesystem.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:01 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-21 12:48 ` Michael Stone <mstone+postgres@mathom.us>
1 sibling, 0 replies; 26+ messages in thread
From: Michael Stone @ 2006-03-21 12:48 UTC (permalink / raw)
To: pgsql-performance
On Tue, Mar 21, 2006 at 06:01:58AM -0600, Jim C. Nasby wrote:
>On Tue, Mar 21, 2006 at 12:56:18PM +0100, Steinar H. Gunderson wrote:
>> For the record, that's the wrong way round. For the data partitioning
>> metadata journaling is enough, and for the WAL partition you don't need any
>> FS journaling at all.
>
>Are you sure?
Yes. :) You actually shouldn't need metadata journaling in either
case--fsck will do the same thing. But fsck can take a *very* long time
on a large paritition, so for your data partition the journaling fs is a
big win. But your wal partition isn't likely to have very many files
and should fsck in a snap, and data consistency is taken care of by
synchronous operations. (Which is the reason you really don't need/want
data journalling.)
Mike Stone
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance optimization
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
@ 2006-03-20 16:35 ` PFC <lists@peufeu.com>
4 siblings, 0 replies; 26+ messages in thread
From: PFC @ 2006-03-20 16:35 UTC (permalink / raw)
To: Mikael Carneholm <Mikael.Carneholm@wirelesscar.com>; pgsql-performance
> using a 16kb block size (for read performance) will probably be
> considered as well.
Hm, this means that when postgres wants to write just one 8k page, the OS
will have to read 16k, replace half of it with the new block, and write
16k again... I guess it should be better to stick with the usual block
size. Also, it will have to read 16k every time it rally wants to read one
page... which happens quite often except for seq scan.
> NOTE: this machine/configuration is NOT what we will be using in
> production if the study turns out OK, it's just supposed to work as a
> development machine in the first phase whose purpose more or less is to
> get familiar with configurating Postgres and see if we can get the
> application up and running (we will probably use a 64bit platform and
Opteron xDDD
Use XFS or Reiser... ext3 isn't well suited for this. use noatime AND
nodiratime.
It's safe to turn off fsync while importing your data.
For optimum speed, put the WAL on another physical disk.
Look in the docs which of maintenance_work_mem, or work_mem or sort_mem
is used for index creation, and set it to a very large value, to speed up
that index creation. Create your indexes with fsync=off also.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance optimization
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
@ 2006-03-20 17:44 ` Dave Cramer <pg@fastcrypt.com>
4 siblings, 0 replies; 26+ messages in thread
From: Dave Cramer @ 2006-03-20 17:44 UTC (permalink / raw)
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; +Cc: pgsql-performance
Others are reporting better performance on 8.1.x with very large
shared buffers. You may want to try tweaking that possibly as high as
20% of available memory
Dave
On 20-Mar-06, at 9:59 AM, Mikael Carneholm wrote:
> Ok, here's the deal:
>
> I am responisble for an exciting project of evaluating migration of
> a medium/large application for a well-known swedish car&truck
> manufacturer from a proprietary DB to Postgres. The size of the
> database is currently about 50Gb, annual growth depending on sales,
> but probably in the 30-50Gb range.
>
> Migrating the schema was easily done, mostly involving a search/
> replace of some vendor specific datatypes. The next step is to
> migrate the data itself, and for this we have written a Java app
> relying on JDBC metadata to map the tables in the source schema to
> the target schema. The goal right now is to find the set of
> parameters that gives as short bulk insert time as possible,
> minimizing downtime while the data itself is migrated.
>
> The machine used for the study is a Dell PE2850, 6GB memory, 1xXEON
> 3.0GHz/2MB cache, internal SCSI 0+1 raid (currently 4x36GB 10000rpm
> striped+mirrored, two more 146GB 15000rpm disks will arrive later).
> Not sure about the brand/model of the raid controller, so I'll
> leave that for now. File system is ext3(I know, maybe not the
> optimal choice but this is how it was when I got it) with a 8k
> block size. The OS currently installed is CentOS4.
>
> Until the new disks arrive, both the OS itself, pg_xlog and the
> data reside on the same disks. When they arrive, I will probably
> move the data to the new disks (need two more to get raid 0+1,
> though) and leave the OS + pg_xlog on the 10000rpm disks. Mounting
> the 15000rpm data disks with the noatime option (this is safe,
> right?) and using a 16kb block size (for read performance) will
> probably be considered as well.
>
> NOTE: this machine/configuration is NOT what we will be using in
> production if the study turns out OK, it's just supposed to work as
> a development machine in the first phase whose purpose more or less
> is to get familiar with configurating Postgres and see if we can
> get the application up and running (we will probably use a 64bit
> platform and either a FC SAN or internal raid with a battery backed
> cache for production use, if all goes well).
>
> The first thing I did when I got the machine was to do a raw dd
> write test:
>
> # time bash -c "(dd if=/dev/zero of=/opt/bigfile count=1310720
> bs=8k && sync)"
> 1310720+0 records in
> 1310720+0 records out
>
> real 2m21.438s
> user 0m0.998s
> sys 0m51.347s
>
> (10*1024)Mb/~141s => ~75.5Mb/s
>
> As a simple benchmark, I created a simple table without PK/indexes
> with 1k wide rows:
>
> create table iotest.one_kb_rows
> (
> the_col char(1024) not null
> );
>
> To fill the table, I use this simple function:
>
> create or replace function iotest.writestress(megs integer) returns
> void as $$
> declare
> char_str char(1024) := repeat('x', 1024);
> begin
> for i in 1..megs loop
> for j in 1..1024 loop
> insert into one_kb_rows(the_col) values (char_str);
> end loop;
> end loop;
> end;
> $$
> language plpgsql;
>
> Then, I tested how long it takes to write 10Gb of data to this table:
>
> iotest=> \timing
> Timing is on.
>
> iotest=> select writestress((10*1024));
> writestress
> -------------
>
> (1 row)
>
> Time: 379971.252 ms
>
> This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off),
> compared to the raw dd result (~75.5Mb/s).
>
> I assume this difference is due to:
> - simultaneous WAL write activity (assumed: for each byte written
> to the table, at least one byte is also written to WAL, in effect:
> 10Gb data inserted in the table equals 20Gb written to disk)
> - lousy test method (it is done using a function => the transaction
> size is 10Gb, and 10Gb will *not* fit in wal_buffers :) )
> - poor config
> - something else?
>
> I have tried to read up as much as possible on Postgres
> configuration (disk layout, buffer management, WAL sync methods,
> etc) and found this post regarding bgwriter tweaking: http://
> archives.postgresql.org/pgsql-performance/2006-03/msg00218.php -
> which explains the bgwriter config below.
>
> All params in postgresql.conf that are not commented out:
> ---------------------------------------------------------
> max_connections = 100
> superuser_reserved_connections = 2
> shared_buffers = 16000
> bgwriter_lru_percent = 20
> bgwriter_lru_maxpages = 160
> bgwriter_all_percent = 10
> bgwriter_all_maxpages = 320
> fsync = off
> wal_sync_method = open_sync
> wal_buffers = 128
> checkpoint_segments = 3
> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
> log_rotation_age = 1440
> log_line_prefix = '%m: (%u@%d) '
> lc_messages = 'C'
> lc_monetary = 'C'
> lc_numeric = 'C'
> lc_time = 'C'
>
> fsync can safely be kept off during data migration as we are able
> to restart the procedure without losing data if something goes
> wrong. Increasing chekpoint_segments to 8/16/32 only increased the
> insert time, so I kept it at the default. I will increase
> shared_buffers and effective_cache_size as soon as it's time to
> tweak read performance, but for now I'm just focusing on write
> performance.
>
>
> Postgres version used:
>
> iotest=> select version();
> version
> ----------------------------------------------------------------------
> -----------------------------
> PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 3.4.4 20050721 (Red Hat 3.4.4-2)
> (1 row)
>
>
> I want to make sure I have made correct assumptions before I carry
> on, so comments are welcome.
>
> - Mikael
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
@ 2006-03-21 20:44 ` Simon Riggs <simon@2ndquadrant.com>
2006-03-20 21:17 ` Re: Migration study, step 1: bulk write performance Ron <rjpeace@earthlink.net>
4 siblings, 1 reply; 26+ messages in thread
From: Simon Riggs @ 2006-03-21 20:44 UTC (permalink / raw)
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; +Cc: pgsql-performance
On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
> This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off), compared to the raw dd result (~75.5Mb/s).
>
> I assume this difference is due to:
> - simultaneous WAL write activity (assumed: for each byte written to the table, at least one byte is also written to WAL, in effect: 10Gb data inserted in the table equals 20Gb written to disk)
> - lousy test method (it is done using a function => the transaction size is 10Gb, and 10Gb will *not* fit in wal_buffers :) )
> - poor config
> checkpoint_segments = 3
With those settings, you'll be checkpointing every 48 Mb, which will be
every about once per second. Since the checkpoint will take a reasonable
amount of time, even with fsync off, you'll be spending most of your
time checkpointing. bgwriter will just be slowing you down too because
you'll always have more clean buffers than you can use, since you have
132MB of shared_buffers, yet flushing all of them every checkpoint.
Please read you're logfile, which should have relevant WARNING messages.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-21 20:44 ` Re: Migration study, step 1: bulk write performance Simon Riggs <simon@2ndquadrant.com>
@ 2006-03-20 21:17 ` Ron <rjpeace@earthlink.net>
2006-03-21 21:19 ` Re: Migration study, step 1: bulk write performance Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-21 21:20 ` Re: Migration study, step 1: bulk write performance Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 26+ messages in thread
From: Ron @ 2006-03-20 21:17 UTC (permalink / raw)
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
At 03:44 PM 3/21/2006, Simon Riggs wrote:
>On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
>
> > This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off),
> compared to the raw dd result (~75.5Mb/s).
> >
> > I assume this difference is due to:
> > - simultaneous WAL write activity (assumed: for each byte written
> to the table, at least one byte is also written to WAL, in effect:
> 10Gb data inserted in the table equals 20Gb written to disk)
> > - lousy test method (it is done using a function => the
> transaction size is 10Gb, and 10Gb will *not* fit in wal_buffers :) )
> > - poor config
>
> > checkpoint_segments = 3
>
>With those settings, you'll be checkpointing every 48 Mb, which will be
>every about once per second. Since the checkpoint will take a reasonable
>amount of time, even with fsync off, you'll be spending most of your
>time checkpointing. bgwriter will just be slowing you down too because
>you'll always have more clean buffers than you can use, since you have
>132MB of shared_buffers, yet flushing all of them every checkpoint.
IIRC, Josh Berkus did some benches that suggests in pg 8.x a value of
64 - 256 is best for checkpoint_segments as long as you have the RAM available.
I'd suggest trying values of 64, 128, and 256 and setting
checkpoint_segments to the best of those.
Ron
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-21 20:44 ` Re: Migration study, step 1: bulk write performance Simon Riggs <simon@2ndquadrant.com>
2006-03-20 21:17 ` Re: Migration study, step 1: bulk write performance Ron <rjpeace@earthlink.net>
@ 2006-03-21 21:19 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-21 21:56 ` Re: Migration study, step 1: bulk write performance Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Scott Marlowe @ 2006-03-21 21:19 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
On Mon, 2006-03-20 at 15:17, Ron wrote:
> At 03:44 PM 3/21/2006, Simon Riggs wrote:
> >On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
> >
> > > This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off),
> > compared to the raw dd result (~75.5Mb/s).
> > >
> > > I assume this difference is due to:
> > > - simultaneous WAL write activity (assumed: for each byte written
> > to the table, at least one byte is also written to WAL, in effect:
> > 10Gb data inserted in the table equals 20Gb written to disk)
> > > - lousy test method (it is done using a function => the
> > transaction size is 10Gb, and 10Gb will *not* fit in wal_buffers :) )
> > > - poor config
> >
> > > checkpoint_segments = 3
> >
> >With those settings, you'll be checkpointing every 48 Mb, which will be
> >every about once per second. Since the checkpoint will take a reasonable
> >amount of time, even with fsync off, you'll be spending most of your
> >time checkpointing. bgwriter will just be slowing you down too because
> >you'll always have more clean buffers than you can use, since you have
> >132MB of shared_buffers, yet flushing all of them every checkpoint.
> IIRC, Josh Berkus did some benches that suggests in pg 8.x a value of
> 64 - 256 is best for checkpoint_segments as long as you have the RAM available.
>
> I'd suggest trying values of 64, 128, and 256 and setting
> checkpoint_segments to the best of those.
I've also found that modest increases in commit_siblings and
commit_delay help a lot on certain types of imports.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-21 20:44 ` Re: Migration study, step 1: bulk write performance Simon Riggs <simon@2ndquadrant.com>
2006-03-20 21:17 ` Re: Migration study, step 1: bulk write performance Ron <rjpeace@earthlink.net>
2006-03-21 21:19 ` Re: Migration study, step 1: bulk write performance Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-03-21 21:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-21 22:23 ` Re: Migration study, step 1: bulk write performance Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2006-03-21 21:56 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Ron <rjpeace@earthlink.net>; Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> I've also found that modest increases in commit_siblings and
> commit_delay help a lot on certain types of imports.
On a data import? Those really should have zero effect on a
single-process workload. Or are you doing multiple concurrent imports?
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-21 20:44 ` Re: Migration study, step 1: bulk write performance Simon Riggs <simon@2ndquadrant.com>
2006-03-20 21:17 ` Re: Migration study, step 1: bulk write performance Ron <rjpeace@earthlink.net>
2006-03-21 21:19 ` Re: Migration study, step 1: bulk write performance Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-21 21:56 ` Re: Migration study, step 1: bulk write performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-21 22:23 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 26+ messages in thread
From: Scott Marlowe @ 2006-03-21 22:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ron <rjpeace@earthlink.net>; Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
On Tue, 2006-03-21 at 15:56, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > I've also found that modest increases in commit_siblings and
> > commit_delay help a lot on certain types of imports.
>
> On a data import? Those really should have zero effect on a
> single-process workload. Or are you doing multiple concurrent imports?
That, and it's a machine that's doing other things. Also, a lot of the
imports are NOT bundled up into groups of transactions. i.e. lots and
lots of individual insert queries.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performance
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-21 20:44 ` Re: Migration study, step 1: bulk write performance Simon Riggs <simon@2ndquadrant.com>
2006-03-20 21:17 ` Re: Migration study, step 1: bulk write performance Ron <rjpeace@earthlink.net>
@ 2006-03-21 21:20 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 26+ messages in thread
From: Alvaro Herrera @ 2006-03-21 21:20 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; pgsql-performance
Ron wrote:
> IIRC, Josh Berkus did some benches that suggests in pg 8.x a value of
> 64 - 256 is best for checkpoint_segments as long as you have the RAM
> available.
I think you are confusing checkpoint_segments with wal_buffers.
checkpoint_segments certainly has little to do with available RAM!
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performanceoptimization
@ 2006-03-22 09:04 Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-22 12:55 ` Re: Migration study, step 1: bulk write performanceoptimization Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 26+ messages in thread
From: Mikael Carneholm @ 2006-03-22 09:04 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-performance
>>On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:
>> This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off), compared to the raw dd result (~75.5Mb/s).
>>
>> I assume this difference is due to:
>> - simultaneous WAL write activity (assumed: for each byte written to the table, at least one byte is also written to WAL, in effect: 10Gb data inserted in the table equals 20Gb written to disk)
>> - lousy test method (it is done using a function => the transaction size is 10Gb, and 10Gb will *not* fit in wal_buffers :) )
>> - poor config
>> checkpoint_segments = 3
>With those settings, you'll be checkpointing every 48 Mb, which will be
>every about once per second. Since the checkpoint will take a reasonable
>amount of time, even with fsync off, you'll be spending most of your
>time checkpointing. bgwriter will just be slowing you down too because
>you'll always have more clean buffers than you can use, since you have
>132MB of shared_buffers, yet flushing all of them every checkpoint.
>Please read you're logfile, which should have relevant WARNING messages.
It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)") However, I tried increasing checkpoint_segments to 32 (512Mb) making it checkpoint every 15 second or so, but that gave a more uneven insert rate than with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value? If I set checkpoint_segments to 64, what would a reasonable bgwriter setup be? I still need to improve my understanding of the relations between checkpoint_segments <-> shared_buffers <-> bgwriter... :/
- Mikael
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performanceoptimization
2006-03-22 09:04 Re: Migration study, step 1: bulk write performanceoptimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
@ 2006-03-22 12:55 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-22 14:34 ` Re: Migration study, step 1: bulk write performanceoptimization Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: Jim C. Nasby @ 2006-03-22 12:55 UTC (permalink / raw)
To: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-performance
On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
> It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)") However, I tried increasing checkpoint_segments to 32 (512Mb) making it checkpoint every 15 second or so, but that gave a more uneven insert rate than with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value? If I set checkpoint_segments to 64, what would a reasonable bgwriter setup be? I still need to improve my understanding of the relations between checkpoint_segments <-> shared_buffers <-> bgwriter... :/
Probably the easiest way is to set checkpoint_segments to something like
128 or 256 (or possibly higher), and then make bg_writer more aggressive
by increasing bgwriter_*_maxpages dramatically (maybe start with 200).
You might want to up lru_percent as well, otherwise it will take a
minimum of 20 seconds to fully scan.
Basically, slowly start increasing settings until performance smooths
out.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performanceoptimization
2006-03-22 09:04 Re: Migration study, step 1: bulk write performanceoptimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-22 12:55 ` Re: Migration study, step 1: bulk write performanceoptimization Jim C. Nasby <jnasby@pervasive.com>
@ 2006-03-22 14:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 26+ messages in thread
From: Tom Lane @ 2006-03-22 14:34 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>; Simon Riggs <simon@2ndquadrant.com>; pgsql-performance
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
>> It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)") However, I tried increasing checkpoint_segments to 32 (512Mb) making it checkpoint every 15 second or so, but that gave a more uneven insert rate than with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value? If I set checkpoint_segments to 64, what would a reasonable bgwriter setup be? I still need to improve my understanding of the relations between checkpoint_segments <-> shared_buffers <-> bgwriter... :/
> Probably the easiest way is to set checkpoint_segments to something like
> 128 or 256 (or possibly higher), and then make bg_writer more aggressive
> by increasing bgwriter_*_maxpages dramatically (maybe start with 200).
Definitely. You really don't want checkpoints happening oftener than
once per several minutes (five or ten if possible). Push
checkpoint_segments as high as you need to make that happen, and then
experiment with making the bgwriter parameters more aggressive in order
to smooth out the disk write behavior. Letting the physical writes
happen via bgwriter is WAY cheaper than checkpointing.
bgwriter parameter tuning is still a bit of a black art, so we'd be
interested to hear what works well for you.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Migration study, step 1: bulk write performanceoptimization
@ 2006-03-22 13:12 Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
0 siblings, 0 replies; 26+ messages in thread
From: Mikael Carneholm @ 2006-03-22 13:12 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-performance
Thanks, will try that. I'll report on the progress later, I have some unit tests to set up first but as soon as that is done I'll go back to optimizing insert performance.
Regards,
Mikael.
-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: den 22 mars 2006 13:55
To: Mikael Carneholm
Cc: Simon Riggs; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Migration study, step 1: bulk write
performanceoptimization
On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
> It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)") However, I tried increasing checkpoint_segments to 32 (512Mb) making it checkpoint every 15 second or so, but that gave a more uneven insert rate than with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value? If I set checkpoint_segments to 64, what would a reasonable bgwriter setup be? I still need to improve my understanding of the relations between checkpoint_segments <-> shared_buffers <-> bgwriter... :/
Probably the easiest way is to set checkpoint_segments to something like
128 or 256 (or possibly higher), and then make bg_writer more aggressive
by increasing bgwriter_*_maxpages dramatically (maybe start with 200).
You might want to up lru_percent as well, otherwise it will take a
minimum of 20 seconds to fully scan.
Basically, slowly start increasing settings until performance smooths
out.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2006-03-22 14:34 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-20 14:59 Migration study, step 1: bulk write performance optimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-20 15:12 ` Craig A. James <cjames@modgraph-usa.com>
2006-03-21 00:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-20 15:19 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:27 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 11:52 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 11:56 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 11:59 ` Re: Migration study, step 1: bulk write performance Csaba Nagy <nagy@ecircle-ag.com>
2006-03-21 12:01 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:10 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:18 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:29 ` Re: Migration study, step 1: bulk write performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-03-21 12:45 ` Re: Migration study, step 1: bulk write performance Jim C. Nasby <jnasby@pervasive.com>
2006-03-21 12:48 ` Re: Migration study, step 1: bulk write performance Michael Stone <mstone+postgres@mathom.us>
2006-03-20 16:35 ` PFC <lists@peufeu.com>
2006-03-20 17:44 ` Dave Cramer <pg@fastcrypt.com>
2006-03-21 20:44 ` Re: Migration study, step 1: bulk write performance Simon Riggs <simon@2ndquadrant.com>
2006-03-20 21:17 ` Re: Migration study, step 1: bulk write performance Ron <rjpeace@earthlink.net>
2006-03-21 21:19 ` Re: Migration study, step 1: bulk write performance Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-21 21:56 ` Re: Migration study, step 1: bulk write performance Tom Lane <tgl@sss.pgh.pa.us>
2006-03-21 22:23 ` Re: Migration study, step 1: bulk write performance Scott Marlowe <smarlowe@g2switchworks.com>
2006-03-21 21:20 ` Re: Migration study, step 1: bulk write performance Alvaro Herrera <alvherre@commandprompt.com>
2006-03-22 09:04 Re: Migration study, step 1: bulk write performanceoptimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.com>
2006-03-22 12:55 ` Re: Migration study, step 1: bulk write performanceoptimization Jim C. Nasby <jnasby@pervasive.com>
2006-03-22 14:34 ` Re: Migration study, step 1: bulk write performanceoptimization Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 13:12 Re: Migration study, step 1: bulk write performanceoptimization Mikael Carneholm <Mikael.Carneholm@WirelessCar.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