From: Aidan Van Dyk <aidan@highrise.ca>
To: fkater@googlemail.com <fkater@googlemail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Date: Thu, 14 Jan 2010 10:07:35 -0500
Message-ID: <20100114150735.GT18076@oak.highrise.ca> (raw)
In-Reply-To: <20100114142903.GB2973@comppasch2>
References: <20100114142903.GB2973@comppasch2>
* fkater@googlemail.com <fkater@googlemail.com> [100114 09:29]:
> 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?
> 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?
So, your SATA disk can do 43MB/s of sequential writes, but you're example
is doing:
1) Sequential writes to WAL
2) Random writes to your index
3) Sequential writes to table heap
4) Sequential writes to table' toast heap
5) Any other OS-based FS overhead
Now, writes #2,3 and 4 don't happen completely concurrently with your
WAL, some of them are still in postgres buffers, but easily enough to
interrupt the stream of WAL enough to certainly make it believable that
with everything going on on the disk, you can only write WAL at a
*sustained* 16 MB/s
If you're running a whole system on a single SATA which can stream
43MB/s, remember that for *every* other read/write sent do the disk, you
lose up to 1MB/s (12ms seek time, read/write, and back). And in that
"every other", you have FS metadata updates, any other file writes the
FS flushes, etc... 20 aditional blocks being that are either read or
written to disk are going to completely chop your 43MB/s rate...
a.
--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
Attachments:
signature.asc (application/pgp-signature, 188B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aidan@highrise.ca, fkater@googlemail.com
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
In-Reply-To: <20100114150735.GT18076@oak.highrise.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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