Re: Inserting 8MB bytea: just 25% of disk perf used?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jan 14, 2010 at 9:29 AM, fkater@xxxxxxxxxxxxxx
<fkater@xxxxxxxxxxxxxx> wrote:
> 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?

postgres is simply not geared towards this type of workload.  16mb
isn't too bad actually, and I bet you could significantly beat that
with better disks and multiple clients sending data, maybe even close
to saturate a gigabit line.  However, there are other ways to do this
(outside the db) that are more appropriate if efficiency is a big
concern.

merlin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux