Two postgresql server with same configuration, different performance

Question

I got two identical servers, in both is installed postgresql server version 9.0.4 with the same configuration. If I launch a .sql file that performs about 5k inserts, on the first one it takes a couple of seconds, on the second one it takes 1 minute and 30 seconds.

If I set synchronous_commit, speed dramatically reduces (as expected), and the performances of the two servers are comparable. But if I set synchronous_commit to on, on one server the insert script execution time increases of less than one second, on the other one it increases too much, as I said in the first period.

Any idea about this difference in performances? Am I missing some configuration?

Update: tried a simple disk test: time sh -c "dd if=/dev/zero of=ddfile bs=8k count=200000 && sync"

fast server output:

1638400000 bytes (1.6 GB) copied, 1.73537 seconds, 944 MB/s

real    0m32.009s
user    0m0.018s
sys 0m2.298s

slow server output:

1638400000 bytes (1.6 GB) copied, 4.85727 s, 337 MB/s

real    0m35.045s
user    0m0.019s
sys 0m2.221s

Common features (both servers):

SATA, RAID1, controller: Intel Corporation 82801JI (ICH10 Family) SATA AHCI Controller, distribution: linux centOS. mount -v output:
/dev/md2 on / type ext3 (rw)
proc on /proc type proc (rw)
none on /dev/pts type devpts (rw,gid=5,mode=620)
/dev/md1 on /boot type ext3 (rw)

fast server: kernel 2.6.18-238.9.1.el5 #1 SMP

Disk /dev/sda: 750.1 GB, 750156374016 bytes
255 heads, 63 sectors/track, 91201 cylinders, total 1465149168 sectors
Units = sectors of 1 * 512 = 512 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1            3906     4209029     2102562   fd  Linux raid autodetect
/dev/sda2         4209030     4739174      265072+  fd  Linux raid autodetect
/dev/sda3         4739175  1465144064   730202445   fd  Linux raid autodetect

slow server: kernel 2.6.32-71.29.1.el6.x86_64 #1 SMP

Disk /dev/sda: 750.2 GB, 750156374016 bytes
64 heads, 32 sectors/track, 715404 cylinders, total 1465149168 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0006ffc4

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1            2048     4194303     2096128   fd  Linux raid autodetect
/dev/sda2         4194304     5242879      524288   fd  Linux raid autodetect
/dev/sda3         5242880  1465147391   729952256   fd  Linux raid autodetect

Could it be useful to address the performance issue?

Both servers have same hardware configuration and disk layout as well? — taro
– taro, Commented Sep 27, 2011 at 15:58
both yes. if needed, I can post the output of lspci / hdparm commands — Lorenzo Marcon
– Lorenzo Marcon, Commented Sep 27, 2011 at 16:11
Please run your time command multiple times (10 times for example). Also try it in single user mode — so any other process won't interact with disk during execution. Do you get similar results every time? — Tometzky
– Tometzky, Commented Sep 27, 2011 at 19:19
Are your drives SSDs, SATA, SAS, in RAID, which RAID, what kind of controller, which distribution, is it updated, which kernel version, are drive barriers enabled (mount -v), are your partitions 4k block aligned fdisk -l -u=sectors? — Tometzky
– Tometzky, Commented Sep 27, 2011 at 19:28

Tometzky · Accepted Answer · 2011-09-28 08:08:00Z

1

I suppose your slow server with newer kernel has working barriers. This is good, as otherwise you can loose data in case of a power failure. But it is of course slower than running with write cache enabled and without barriers, aka running with scissors.

You can check if barriers are enabled using mount -v — search for barrier=1 in output. You can disable barriers for your filesystem (mount -o remount,barrier=0 /) to speed up, but then you risk data corruption.

Try to do your 5k inserts in one transaction — Postgres won't have to write to disk on every row inserted. The theoretical limit for number of transactions per second wound be comparable to disk rotational speed (7200rpm disk ≈ 7200/60 tps = 120 tps) as a disk can only write to a sector once per rotation.

edited Sep 28, 2011 at 8:08

answered Sep 28, 2011 at 5:51

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Lorenzo Marcon Over a year ago

2 seconds against 1m33s? Can working barriers make a difference so huge? I know, a single transaction would be faster, but still I want to investigate on this difference of performance. In fact, I performed the same 5k insert test on a third (virtual) server with kernel 2.6.32 (the same of the slow one), it run in a bunch of seconds. How can I check if these barriers are enabled, and in case try to disable these barriers, at least for test purposes?

Tometzky Over a year ago

@lorenzo: I've updated my answer to address your doubts. Yes — it can be 2s vs 90s. With no barriers — update 5k times small amount of data in disk cache. With barriers — wait 5k times for this data to be written to physical, rotating disk platter in this particular place.

Lorenzo Marcon Over a year ago

mount -v output on slow server:

/dev/md2 on / type ext3 (rw) proc on /proc type proc (rw) none on /dev/pts type devpts (rw,gid=5,mode=620) /dev/md1 on /boot type ext3 (rw) none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)

I can't see anything related to barrier..

Tometzky Over a year ago

@lorenzo: I can not find how to check if barriers are enabled on software RAID. They're enabled by default in CentOS6/RHEL6 and a warning is written on boot to /var/log/messages about them being disabled. There's a tool in source Postgres distribution, pg_test_fsync (test_fsync in older Postgres), that benchmarks number of operations per second. If it is very high (for example 1000 of ops/sec) then there must be some kind of cache. If it's not backed by a battery then it is dangerous.

Lorenzo Marcon Over a year ago

pg_test_fsync is very high (~3000 ops/sec or so) on the fast server, while is very slow (~50 ops/sec) on the slow server. how would I know if there is a battery backup? In each case, would it be nice to know where to act to enable / disable that kind of cache, which I cannot manage to identify.

|

user330315 · Accepted Answer · 2011-09-27 15:57:45Z

1

To me this sounds like in the "fast" server there is a write cache enbled for the harddisk(s), whereas in the slow server the harddisk(s) are really writing the data when PG writes it (by calling fsync)

answered Sep 27, 2011 at 15:57

user330315

1 Comment

Lorenzo Marcon Over a year ago

according to hdparm -i, both boxes' drive(s) have write cache enabled. is there something else that I should check?

Collectives™ on Stack Overflow

Two postgresql server with same configuration, different performance

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related