| From: | "fkater(at)googlemail(dot)com" <fkater(at)googlemail(dot)com> | 
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org | 
| Subject: | Inserting 8MB bytea: just 25% of disk perf used? | 
| Date: | 2010-01-14 14:29:03 | 
| Message-ID: | 20100114142903.GB2973@comppasch2 | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-performance | 
Hello together,
I need to increase the write performance when inserting
bytea of 8MB. I am using 8.2.4 on windows with libpq.
The test setting is simple:
I write 100x times a byte array (bytea) of 8 MB random data
into a table having a binary column (and oids and 3 other
int columns, oids are indexed). I realized that writing 8 MB
of 0-bytes is optimized away. With random data, the disk
space now is filled with 800MB each run as expected. I use a
transaction around the insert command.
This takes about 50s, so, 800MB/50s = 16MB/s.
However the harddisk (sata) could write 43 MB/s in the worst
case! Why is write performance limited to 16 MB/s?
Some more hints what I do:
I use PQexecParams() and the INSERT ... $001 notation to NOT
create a real escapted string from the data additionally but
use a pointer to the 8MB data buffer.
I altered the binary column to STORAGE EXTERNAL.
Some experiments with postgresql.conf (fsync off,
shared_buffers=1000MB, checkpoint_segments=256) did not
change the 50s- much (somtimes 60s sometimes a little less).
4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
Do you have any further idea why 16MB/s seems to be the
limit here?
Thank You
 Felix
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ivan Voras | 2010-01-14 14:49:16 | Re: Inserting 8MB bytea: just 25% of disk perf used? | 
| Previous Message | Dimitri Fontaine | 2010-01-14 10:44:15 | Re: performance config help |