pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInserting 8MB bytea: just 25% of disk perf used?
16+ messages / 8 participants
[nested] [flat]
* Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 14:29 fkater@googlemail.com <fkater@googlemail.com>
0 siblings, 6 replies; 16+ messages in thread
From: fkater@googlemail.com @ 2010-01-14 14:29 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 14:49 Ivan Voras <ivoras@freebsd.org>
parent: fkater@googlemail.com <fkater@googlemail.com>
5 siblings, 1 reply; 16+ messages in thread
From: Ivan Voras @ 2010-01-14 14:49 UTC (permalink / raw)
To: pgsql-performance
fkater@googlemail.com 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.
>
> 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?
>
>
> Do you have any further idea why 16MB/s seems to be the
> limit here?
Are you doing it locally or over a network? If you are accessing the
server over a network then it could be the location of the bottleneck.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 15:04 Matthew Wakeling <matthew@flymine.org>
parent: fkater@googlemail.com <fkater@googlemail.com>
5 siblings, 1 reply; 16+ messages in thread
From: Matthew Wakeling @ 2010-01-14 15:04 UTC (permalink / raw)
To: fkater@googlemail.com <fkater@googlemail.com>; +Cc: pgsql-performance
On Thu, 14 Jan 2010, fkater@googlemail.com wrote:
> 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?
Several reasons:
The data needs to be written first to the WAL, in order to provide
crash-safety. So you're actually writing 1600MB, not 800.
Postgres needs to update a few other things on disc (indexes on the large
object table maybe?), and needs to call fsync a couple of times. That'll
add a bit of time.
Your discs can't write 43MB/s in the *worst case* - the worst case is lots
of little writes scattered over the disc, where it would be lucky to
manage 1MB/s. Not all of the writes Postgres makes are sequential. A handy
way of knowing how sequential the writes are is to listen to the disc as
it writes - the clicking sounds are where it has to waste time moving the
disc head from one part of the disc to another.
Matthew
--
No trees were killed in the sending of this message. However a large
number of electrons were terribly inconvenienced.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 15:07 Aidan Van Dyk <aidan@highrise.ca>
parent: fkater@googlemail.com <fkater@googlemail.com>
5 siblings, 1 reply; 16+ messages in thread
From: Aidan Van Dyk @ 2010-01-14 15:07 UTC (permalink / raw)
To: fkater@googlemail.com <fkater@googlemail.com>; +Cc: pgsql-performance
* 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 15:14 Florian Weimer <fweimer@bfk.de>
parent: fkater@googlemail.com <fkater@googlemail.com>
5 siblings, 1 reply; 16+ messages in thread
From: Florian Weimer @ 2010-01-14 15:14 UTC (permalink / raw)
To: fkater\@googlemail.com <fkater@googlemail.com>; +Cc: pgsql-performance
> Do you have any further idea why 16MB/s seems to be the limit here?
BYTEA deserialization is very slow, and this could be a factor here.
Have you checked that you are in fact I/O bound?
You can speed things up by sending the data in binary, by passing
approriate parameters to PQexecParams().
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 16:08 Pierre Frédéric Caillaud <lists@peufeu.com>
parent: fkater@googlemail.com <fkater@googlemail.com>
5 siblings, 1 reply; 16+ messages in thread
From: Pierre Frédéric Caillaud @ 2010-01-14 16:08 UTC (permalink / raw)
To: fkater@googlemail.com <fkater@googlemail.com>; pgsql-performance
> 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.
Big CPU and slow disk...
You should add another disk just for the WAL -- disks are pretty cheap
these days.
Writing the WAL on a second disk is the first thing to do on a
configuration like yours, if you are limited by writes.
It also reduces the fsync lag a lot since the disk is only doing WAL.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 20:09 fkater@googlemail.com <fkater@googlemail.com>
parent: Ivan Voras <ivoras@freebsd.org>
0 siblings, 0 replies; 16+ messages in thread
From: fkater@googlemail.com @ 2010-01-14 20:09 UTC (permalink / raw)
To: Ivan Voras <ivoras@freebsd.org>; +Cc: pgsql-performance
Thank You for your reply.
Ivan Voras:
> Are you doing it locally or over a network? If you are accessing the
> server over a network then it could be the location of the bottleneck.
All is done locally (for now).
Felix
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 20:14 fkater@googlemail.com <fkater@googlemail.com>
parent: fkater@googlemail.com <fkater@googlemail.com>
5 siblings, 0 replies; 16+ messages in thread
From: fkater@googlemail.com @ 2010-01-14 20:14 UTC (permalink / raw)
To: Hannu Krosing <hannu@2ndquadrant.com>; +Cc: pgsql-performance
Thanks a lot for your reply.
Hannu Krosing:
> > 4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
>
> try inserting the same data using 4 parallel connections or even 8
> parallel ones.
Interesting idea -- I forgot to mention though that 2-3
cores will be occupied soon with other tasks.
Felix
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 20:18 fkater@googlemail.com <fkater@googlemail.com>
parent: Matthew Wakeling <matthew@flymine.org>
0 siblings, 0 replies; 16+ messages in thread
From: fkater@googlemail.com @ 2010-01-14 20:18 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
Thanks a lot for the detailed reply.
Matthew Wakeling:
> On Thu, 14 Jan 2010, fkater@googlemail.com wrote:
> > 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?
>
> Several reasons:
>
> The data needs to be written first to the WAL, in order to provide
> crash-safety. So you're actually writing 1600MB, not 800.
I understand. So the actual throughput is 32MB/s which is
closer to 43 MB/s, of course.
Can I verify that by temporarily disabling WAL writes
completely and see if the thoughput is then doubled?
Felix
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 21:23 fkater@googlemail.com <fkater@googlemail.com>
parent: Aidan Van Dyk <aidan@highrise.ca>
0 siblings, 1 reply; 16+ messages in thread
From: fkater@googlemail.com @ 2010-01-14 21:23 UTC (permalink / raw)
To: Aidan Van Dyk <aidan@highrise.ca>; +Cc: pgsql-performance
Aidan Van Dyk:
> 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
Ok, I see. Thanks a lot for the detailed answer! Especially
writing to WAL may eat up 50% as I've learned now. So,
16MB/s x 2 would in fact be 32 MB/s, plus some extras...
However, does that mean: If I have a raw sequential
performance of 100%, I will get a binary write (like in my
example) which is about 33% as a general rule of thumb?
Just to mention:
* The system has two hard disks, the first for
WinXP, the second purely for the postgres data.
* I was doing nothing else simultanously on the newly
installed OS.
* The consumed time (50s, see my test case) were needed to
99.9 % just by PGexecParam() function.
* No network connect to the postgres server (everything
local).
* No complex sql command; just inserting 100x times using
PGexecParam(), as a transaction.
* The binary data was marked as such in PGexecParam
(Format = 1).
* What I meant by 43 MB/s "worst case": I downloaded
some hd benchmarks which showed a performance of
43-70 MB/s. (Whereas repetitions of my postgres test did
never vary, but *constantly* performed at 16MB/s).
Hm.
Nevertheless: If your explanation covers all what can be
said about it then replacing the hard disk by a faster one
should increase the performance here (I'll try to check that
out).
Thanks again!
Felix
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 21:27 fkater@googlemail.com <fkater@googlemail.com>
parent: Florian Weimer <fweimer@bfk.de>
0 siblings, 1 reply; 16+ messages in thread
From: fkater@googlemail.com @ 2010-01-14 21:27 UTC (permalink / raw)
To: Florian Weimer <fweimer@bfk.de>; +Cc: pgsql-performance
Florian Weimer:
> > Do you have any further idea why 16MB/s seems to be the limit here?
>
> BYTEA deserialization is very slow, and this could be a factor here.
> Have you checked that you are in fact I/O bound?
Could you elaborate that a bit? It sounds interesting but I
do not get what you mean by:
"bytea deserialization": Do you mean from an escaped string
back to real binary data? Does that apply to my case (I use
PGexecParam and have the Format arg set to 1, binary) ?
"I/O bound": What do you mean by that?
> You can speed things up by sending the data in binary, by passing
> approriate parameters to PQexecParams().
Do you mean the Format arg =1 ? If not, what is appropriate
here?
Felix
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 21:28 fkater@googlemail.com <fkater@googlemail.com>
parent: Pierre Frédéric Caillaud <lists@peufeu.com>
0 siblings, 1 reply; 16+ messages in thread
From: fkater@googlemail.com @ 2010-01-14 21:28 UTC (permalink / raw)
To: Pierre Frédéric Caillaud <lists@peufeu.com>; +Cc: pgsql-performance
Pierre Frédéric Caillaud:
> > 4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
>
> Big CPU and slow disk...
>
> You should add another disk just for the WAL -- disks are pretty cheap
> these days.
> Writing the WAL on a second disk is the first thing to do on a
> configuration like yours, if you are limited by writes.
> It also reduces the fsync lag a lot since the disk is only doing WAL.
Good idea -- where can I set the path to WAL?
Felix
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 21:33 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: fkater@googlemail.com <fkater@googlemail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Fernando Hevia @ 2010-01-14 21:33 UTC (permalink / raw)
To: fkater@googlemail.com; 'Aidan Van Dyk' <aidan@highrise.ca>; +Cc: pgsql-performance
> -----Mensaje original-----
> De: fkater@googlemail.com
> Nevertheless: If your explanation covers all what can be said
> about it then replacing the hard disk by a faster one should
> increase the performance here (I'll try to check that out).
>
Moving the pg_xlog directory to the OS drive should make a difference and it
will cost you zero.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-14 23:19 Pierre Frédéric Caillaud <lists@peufeu.com>
parent: fkater@googlemail.com <fkater@googlemail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Pierre Frédéric Caillaud @ 2010-01-14 23:19 UTC (permalink / raw)
To: fkater@googlemail.com <fkater@googlemail.com>; +Cc: pgsql-performance
On Thu, 14 Jan 2010 22:28:07 +0100, fkater@googlemail.com
<fkater@googlemail.com> wrote:
> Pierre Frédéric Caillaud:
>
>> > 4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
>>
>> Big CPU and slow disk...
>>
>> You should add another disk just for the WAL -- disks are pretty cheap
>> these days.
>> Writing the WAL on a second disk is the first thing to do on a
>> configuration like yours, if you are limited by writes.
>> It also reduces the fsync lag a lot since the disk is only doing WAL.
>
> Good idea -- where can I set the path to WAL?
At install, or use a symlink (they exist on windows too !...)
http://stackoverflow.com/questions/1901405/postgresql-wal-on-windows
I've no idea of the other needed NTFS tweaks, like if there is a
noatime/nodiratime ?...
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-15 05:38 Magnus Hagander <magnus@hagander.net>
parent: Pierre Frédéric Caillaud <lists@peufeu.com>
0 siblings, 0 replies; 16+ messages in thread
From: Magnus Hagander @ 2010-01-15 05:38 UTC (permalink / raw)
To: Pierre Frédéric Caillaud <lists@peufeu.com>; +Cc: fkater@googlemail.com <fkater@googlemail.com>; pgsql-performance
2010/1/15 Pierre Frédéric Caillaud <lists@peufeu.com>:
> On Thu, 14 Jan 2010 22:28:07 +0100, fkater@googlemail.com <fkater@googlemail.com> wrote:
>
>> Pierre Frédéric Caillaud:
>>
>>> > 4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
>>>
>>> Big CPU and slow disk...
>>>
>>> You should add another disk just for the WAL -- disks are pretty cheap
>>> these days.
>>> Writing the WAL on a second disk is the first thing to do on a
>>> configuration like yours, if you are limited by writes.
>>> It also reduces the fsync lag a lot since the disk is only doing WAL.
>>
>> Good idea -- where can I set the path to WAL?
>
> At install, or use a symlink (they exist on windows too !...)
>
> http://stackoverflow.com/questions/1901405/postgresql-wal-on-windows
>
> I've no idea of the other needed NTFS tweaks, like if there is a noatime/nodiratime ?...
It does. See http://www.hagander.net/talks/Advanced%20PostgreSQL%20on%20Windows.pdf
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Inserting 8MB bytea: just 25% of disk perf used?
@ 2010-01-15 08:02 Florian Weimer <fweimer@bfk.de>
parent: fkater@googlemail.com <fkater@googlemail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Florian Weimer @ 2010-01-15 08:02 UTC (permalink / raw)
To: fkater\@googlemail.com <fkater@googlemail.com>; +Cc: pgsql-performance
> Florian Weimer:
>
>> > Do you have any further idea why 16MB/s seems to be the limit here?
>>
>> BYTEA deserialization is very slow, and this could be a factor here.
>> Have you checked that you are in fact I/O bound?
>
> Could you elaborate that a bit? It sounds interesting but I
> do not get what you mean by:
>
> "bytea deserialization": Do you mean from an escaped string
> back to real binary data?
Yes, that is what I meant.
> Does that apply to my case (I use PGexecParam and have the Format
> arg set to 1, binary) ?
Yes, this was my suggestion. There is probably some other issue,
then.
> "I/O bound": What do you mean by that?
You should check (presumably using the Windows performance monitoring
tools, but I'm not familiar with Windows) if the PostgreSQL process is
indeed waiting on disk I/O.
--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2010-01-15 08:02 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-14 14:29 Inserting 8MB bytea: just 25% of disk perf used? fkater@googlemail.com <fkater@googlemail.com>
2010-01-14 14:49 ` Ivan Voras <ivoras@freebsd.org>
2010-01-14 20:09 ` fkater@googlemail.com <fkater@googlemail.com>
2010-01-14 15:04 ` Matthew Wakeling <matthew@flymine.org>
2010-01-14 20:18 ` fkater@googlemail.com <fkater@googlemail.com>
2010-01-14 15:07 ` Aidan Van Dyk <aidan@highrise.ca>
2010-01-14 21:23 ` fkater@googlemail.com <fkater@googlemail.com>
2010-01-14 21:33 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2010-01-14 15:14 ` Florian Weimer <fweimer@bfk.de>
2010-01-14 21:27 ` fkater@googlemail.com <fkater@googlemail.com>
2010-01-15 08:02 ` Florian Weimer <fweimer@bfk.de>
2010-01-14 16:08 ` Pierre Frédéric Caillaud <lists@peufeu.com>
2010-01-14 21:28 ` fkater@googlemail.com <fkater@googlemail.com>
2010-01-14 23:19 ` Pierre Frédéric Caillaud <lists@peufeu.com>
2010-01-15 05:38 ` Magnus Hagander <magnus@hagander.net>
2010-01-14 20:14 ` fkater@googlemail.com <fkater@googlemail.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