Обсуждение: huge difference in TPS depending of synchornous_commit setting
Hi.
I'm having problems with PostgreSQL performance.
My server has good CPU and lots of memory, but just two SATA 7200 hard drives in software RAID1.
When running pgbench I'm getting 68 TPS with synchronous_commit turned on, and 3100 TPS with synchronous commit turned off.
Can somebody tell me why there is such big difference? Is it normal to have it like this?
PS: During run of pgbench with synchronous commit on system is mostly idle, iowait is 0.12%
--
Best regards,
Sergey
Best regards,
Sergey
That is to be expected see http://momjian.us/main/blogs/pgblog/2012.html#August_1_2012 Jesper
Thanks. I was thinking that is is related to rotational delay too. But 68 TPS still look a bit slow for me.
On the another (slower) server with 4 SATA disks in RAID-10, I'm getting ~250 TPS, which is almost 4X of my new server.
And this is weird for me since, if I'm getting everything right — 4 disks in RAID-10 should be only 2x faster than 2 disks in RAID-1
--
Best regards,
Sergey
On Thu, Aug 9, 2012 at 10:07 AM, Jesper Krogh <jesper@krogh.cc> wrote:
That is to be expected see
http://momjian.us/main/blogs/pgblog/2012.html#August_1_2012
Jesper
Best regards,
Sergey
On 9 August 2012 07:44, Sergey Kirillov <sergey.kirillov@gmail.com> wrote: > I'm having problems with PostgreSQL performance. > > My server has good CPU and lots of memory, but just two SATA 7200 hard > drives in software RAID1. > > When running pgbench I'm getting 68 TPS with synchronous_commit turned on, > and 3100 TPS with synchronous commit turned off. > > Can somebody tell me why there is such big difference? Is it normal to have > it like this? synchronous_commit = off is a performance feature, so yes it is normal to experience a large gain in performance when using it. It is a trade off between performance and durability. With setting off you don't need to wait for the disk, with setting on then you'll go at the speed of your disks. The setting can be set differently for each transaction, so if you know which transactions you want to favour you can achieve 80% of the performance while losing only 20% of the durability. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services