Re: Migration study, step 1: bulk write performance optimization

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(at)%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
>