pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed8.3 synchronous_commit
12+ messages / 7 participants
[nested] [flat]
* 8.3 synchronous_commit
@ 2008-01-21 21:55 Hannes Dorbath <light@theendofthetunnel.de>
0 siblings, 4 replies; 12+ messages in thread
From: Hannes Dorbath @ 2008-01-21 21:55 UTC (permalink / raw)
To: pgsql-performance
I might completely misunderstand this feature. Shouldn't
"synchronous_commit = off" improve performance?
Whatever I do, I find "synchronous_commit = off" to degrade performance.
Especially it doesn't like the CFQ I/O scheduler, it's not so bad with
deadline. Synthetic load like
pgbench -i -s 10 -U pgsql -d bench && pgbench -t 1000 -c 100 -U pgsql -d
bench
or the same with scale 100.
Maybe it's just my test box.. single SATA-II drive, XFS on top of LVM.
I'll retry without LVM once I have another drive.. I've seen LVM mess
with other things in the past.
--
Best regards,
Hannes Dorbath
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-21 22:11 Pavel Stehule <pavel.stehule@gmail.com>
parent: Hannes Dorbath <light@theendofthetunnel.de>
3 siblings, 0 replies; 12+ messages in thread
From: Pavel Stehule @ 2008-01-21 22:11 UTC (permalink / raw)
To: Hannes Dorbath <light@theendofthetunnel.de>; +Cc: pgsql-performance
Hello
synchronous_commit = off is well for specific load,
try only one connect pgbench ~ it is analogy for database import or
some administrator's work.
Regards
Pavel Stehule
On 21/01/2008, Hannes Dorbath <light@theendofthetunnel.de> wrote:
> I might completely misunderstand this feature. Shouldn't
> "synchronous_commit = off" improve performance?
>
> Whatever I do, I find "synchronous_commit = off" to degrade performance.
>
> Especially it doesn't like the CFQ I/O scheduler, it's not so bad with
> deadline. Synthetic load like
>
> pgbench -i -s 10 -U pgsql -d bench && pgbench -t 1000 -c 100 -U pgsql -d
> bench
>
> or the same with scale 100.
>
> Maybe it's just my test box.. single SATA-II drive, XFS on top of LVM.
>
> I'll retry without LVM once I have another drive.. I've seen LVM mess
> with other things in the past.
>
>
> --
> Best regards,
> Hannes Dorbath
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-21 22:25 Jeff Davis <pgsql@j-davis.com>
parent: Hannes Dorbath <light@theendofthetunnel.de>
3 siblings, 0 replies; 12+ messages in thread
From: Jeff Davis @ 2008-01-21 22:25 UTC (permalink / raw)
To: Hannes Dorbath <light@theendofthetunnel.de>; +Cc: pgsql-performance
On Mon, 2008-01-21 at 22:55 +0100, Hannes Dorbath wrote:
> I might completely misunderstand this feature. Shouldn't
> "synchronous_commit = off" improve performance?
>
> Whatever I do, I find "synchronous_commit = off" to degrade performance.
>
> Especially it doesn't like the CFQ I/O scheduler, it's not so bad with
> deadline. Synthetic load like
>
The CFQ scheduler is bad for performance in the tests that I have run.
When I have a chance I'll put together some tests to try to demonstrate
that.
The reason it may be bad in your case is if you have many backends
commit many transactions asynchronously, and then the WAL writer tries
to make those transactions durable, CFQ might think that the WAL writer
is "unfairly" using a lot of I/O. This is just speculation though.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-21 23:31 Greg Smith <gsmith@gregsmith.com>
parent: Hannes Dorbath <light@theendofthetunnel.de>
3 siblings, 2 replies; 12+ messages in thread
From: Greg Smith @ 2008-01-21 23:31 UTC (permalink / raw)
To: Hannes Dorbath <light@theendofthetunnel.de>; +Cc: pgsql-performance
On Mon, 21 Jan 2008, Hannes Dorbath wrote:
> pgbench -i -s 10 -U pgsql -d bench && pgbench -t 1000 -c 100 -U pgsql -d
> bench
pgbench doesn't handle 100 clients at once very well on the same box as
the server, unless you have a pretty serious system. The pgbench program
itself has a single process model that doesn't handle the CFQ round-robin
very well at all. On top of that, the database scale should be bigger
than the number of clients or everybody just fights for the branches
table. You said you tried with a larger scale as well, but that also
vastly increases the size of the database which shifts to a completely
different set of bottlenecks. See
http://www.westnet.com/~gsmith/content/postgresql/pgbench-scaling.htm for
more on this.
Try something more in the range of 4 clients/CPU and set the scale to
closer to twice that (so with a dual-core system you might do 8 clients
and a scale of 16). If you really want to simulate a large number of
clients, do that on another system and connect to the server remotely.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-21 23:45 Jeff Davis <pgsql@j-davis.com>
parent: Greg Smith <gsmith@gregsmith.com>
1 sibling, 2 replies; 12+ messages in thread
From: Jeff Davis @ 2008-01-21 23:45 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: Hannes Dorbath <light@theendofthetunnel.de>; pgsql-performance
On Mon, 2008-01-21 at 18:31 -0500, Greg Smith wrote:
> pgbench doesn't handle 100 clients at once very well on the same box as
> the server, unless you have a pretty serious system. The pgbench program
> itself has a single process model that doesn't handle the CFQ round-robin
> very well at all. On top of that, the database scale should be bigger
He was referring to the CFQ I/O scheduler. I don't think that will
affect pgbench itself, because it doesn't read/write to disk, right?
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-22 00:26 Greg Smith <gsmith@gregsmith.com>
parent: Jeff Davis <pgsql@j-davis.com>
1 sibling, 0 replies; 12+ messages in thread
From: Greg Smith @ 2008-01-22 00:26 UTC (permalink / raw)
To: pgsql-performance
On Mon, 21 Jan 2008, Jeff Davis wrote:
> He was referring to the CFQ I/O scheduler. I don't think that will
> affect pgbench itself, because it doesn't read/write to disk, right?
It does if you are writing latency log files but it shouldn't be in the
cases given. But there's something weird about the interplay between the
server disk I/O under CFQ and the time slices the pgbench client gets when
they're all on the same system. pgbench is certainly a badly scaling (in
regards to how it handles simulating many clients) single process utility
and anything that starves its execution sometimes is deadly. I never dug
deep into the exact scheduler issues, but regardless in this case it's
kind of unrealistic for Hannes to expect to simulate 100 clients on a
small system and still get a true view of how the server on that system
performs.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-22 00:48 Fernando Ike <fike@midstorm.org>
parent: Jeff Davis <pgsql@j-davis.com>
1 sibling, 0 replies; 12+ messages in thread
From: Fernando Ike @ 2008-01-22 00:48 UTC (permalink / raw)
To: pgsql-performance
Em Mon, 21 Jan 2008 15:45:54 -0800
Jeff Davis <pgsql@j-davis.com> escreveu:
> On Mon, 2008-01-21 at 18:31 -0500, Greg Smith wrote:
> > pgbench doesn't handle 100 clients at once very well on the same
> > box as the server, unless you have a pretty serious system. The
> > pgbench program itself has a single process model that doesn't
> > handle the CFQ round-robin very well at all. On top of that, the
> > database scale should be bigger
>
> He was referring to the CFQ I/O scheduler. I don't think that will
> affect pgbench itself, because it doesn't read/write to disk, right?
>
No. But pgbench local running, it will be concurrency with
PostgreSQL. I'm realized some test and really confirm disable
*synchronous_commit* performance degrade with CFQ and Deadline.
Kind Regards,
--
Fernando Ike
http://www.midstorm.org/~fike/weblog
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-22 08:32 Florian Weimer <fweimer@bfk.de>
parent: Hannes Dorbath <light@theendofthetunnel.de>
3 siblings, 1 reply; 12+ messages in thread
From: Florian Weimer @ 2008-01-22 08:32 UTC (permalink / raw)
To: Hannes Dorbath <light@theendofthetunnel.de>; +Cc: pgsql-performance
* Hannes Dorbath:
> I might completely misunderstand this feature. Shouldn't
> "synchronous_commit = off" improve performance?
Indeed.
We've seen something similar in one test, but couldn't reproduce it in
a clean environment so far.
> Maybe it's just my test box.. single SATA-II drive, XFS on top of LVM.
Ours was ext3, no LVM or RAID.
--
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] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-22 08:49 Guillaume Smet <guillaume.smet@gmail.com>
parent: Florian Weimer <fweimer@bfk.de>
0 siblings, 2 replies; 12+ messages in thread
From: Guillaume Smet @ 2008-01-22 08:49 UTC (permalink / raw)
To: Florian Weimer <fweimer@bfk.de>; +Cc: Hannes Dorbath <light@theendofthetunnel.de>; pgsql-performance
On Jan 22, 2008 9:32 AM, Florian Weimer <fweimer@bfk.de> wrote:
> > Maybe it's just my test box.. single SATA-II drive, XFS on top of LVM.
>
> Ours was ext3, no LVM or RAID.
Also with SATA? If your SATA disk is lying about effectively SYNCing
the data, I'm not that surprised you don't see any improvement. Being
slower is a bit surprising though.
--
Guillaume
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-22 08:56 Florian Weimer <fweimer@bfk.de>
parent: Guillaume Smet <guillaume.smet@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Florian Weimer @ 2008-01-22 08:56 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>; +Cc: Hannes Dorbath <light@theendofthetunnel.de>; pgsql-performance
* Guillaume Smet:
> On Jan 22, 2008 9:32 AM, Florian Weimer <fweimer@bfk.de> wrote:
>> > Maybe it's just my test box.. single SATA-II drive, XFS on top of LVM.
>>
>> Ours was ext3, no LVM or RAID.
>
> Also with SATA?
Yes, desktop-class SATA.
> If your SATA disk is lying about effectively SYNCing the data, I'm
> not that surprised you don't see any improvement.
It still should be faster since the commit doesn't even have to leave
the kernel write cache. For us, this is the common case because most
non-desktop systems have battery-backed write cache anyway.
> Being slower is a bit surprising though.
Exactly.
--
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] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-22 11:56 Hannes Dorbath <light@theendofthetunnel.de>
parent: Guillaume Smet <guillaume.smet@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Hannes Dorbath @ 2008-01-22 11:56 UTC (permalink / raw)
To: Guillaume Smet <guillaume.smet@gmail.com>
Guillaume Smet wrote:
> Also with SATA? If your SATA disk is lying about effectively SYNCing
> the data, I'm not that surprised you don't see any improvement. Being
> slower is a bit surprising though.
The disc is not lying, but LVM does not support write barriers, so the
result is the same. Indeed nothing is flushing the disc's write cache on
fsync. However I could disable the disc's write cache entirely. One
reason why I'm a ZFS fan boy lately. It just get's all of this right by
*default*.
Anyway, with some further testing my benchmark results vary so much that
further discussion seems pointless. I'll repost when I really have
reproducible values and followed Greg Smith's advice.
--
Best regards,
Hannes Dorbath
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 8.3 synchronous_commit
@ 2008-01-22 12:13 Hannes Dorbath <light@theendofthetunnel.de>
parent: Greg Smith <gsmith@gregsmith.com>
1 sibling, 0 replies; 12+ messages in thread
From: Hannes Dorbath @ 2008-01-22 12:13 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>
Greg Smith wrote:
> Try something more in the range of 4 clients/CPU and set the scale to
> closer to twice that (so with a dual-core system you might do 8 clients
> and a scale of 16). If you really want to simulate a large number of
> clients, do that on another system and connect to the server remotely.
With 4 clients and scale 10 I get 246 TPS for synchronous_commit
disabled and 634 TPS for synchronous_commit enabled. So the effect just
got even stronger. That was for CFQ.
For deadline they are now pretty close, but synchronous_commit disabled
is still slower. 690 to 727.
Values are AVG from 3 runs each. DROP/CREATE DATABASE and CHECKPOINT;
before each run.
--
Best regards,
Hannes Dorbath
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-01-22 12:13 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-21 21:55 8.3 synchronous_commit Hannes Dorbath <light@theendofthetunnel.de>
2008-01-21 22:11 ` Pavel Stehule <pavel.stehule@gmail.com>
2008-01-21 22:25 ` Jeff Davis <pgsql@j-davis.com>
2008-01-21 23:31 ` Greg Smith <gsmith@gregsmith.com>
2008-01-21 23:45 ` Jeff Davis <pgsql@j-davis.com>
2008-01-22 00:26 ` Greg Smith <gsmith@gregsmith.com>
2008-01-22 00:48 ` Fernando Ike <fike@midstorm.org>
2008-01-22 12:13 ` Hannes Dorbath <light@theendofthetunnel.de>
2008-01-22 08:32 ` Florian Weimer <fweimer@bfk.de>
2008-01-22 08:49 ` Guillaume Smet <guillaume.smet@gmail.com>
2008-01-22 08:56 ` Florian Weimer <fweimer@bfk.de>
2008-01-22 11:56 ` Hannes Dorbath <light@theendofthetunnel.de>
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