Postgres runs very slow

performance postgresql

5,010

Newer Linux kernels, such as the one included in Ubuntu 10.10, use a different method to flush out information to your drive when the database writes. Linux uses what are called write barriers when the database makes one of its regular fsync calls, which happen whenever you commit a transaction. Your one cluster is slow because the newer write barrier code is more careful to push information out of the individual hard drive write caches. Older Linux systems let it stay in the drive's cache even after the database requested it be written.

See Reliable Writes for a lot more information about this topic. The newer, slow cluster is actually doing the right thing as far as reliable database writing goes. Your faster systems probably could lose data in the case of a crash in their configuration, while the slower one doesn't have that problem.

If you care about the possibility of data loss in a crash, you should be worried about the faster systems more than the slow one. There are two easy things you could do to speed up the slow system regardless:

Edit your postgresql.conf file, turn off the parameter synchronous_commit, and reload the server. That disables the database asking the operating system to flush data out, and therefore the write barrier flush whose performance is so different on the one server. This is safe from database corruption issues on the new box (the old ones are still at risk), but you can lose some data in a crash.
Remount your filesystem with the "nobarrier" option. You'll be back to the possibility of database corruption during a crash here, the same as your older systems.

You may want to look at your code and find out if it's possible to commit to the database less often too. Sometimes data writing works much faster if you do it in large blocks of rows at a time. From your problem, I would guess you might be committing after each write into the database.

5,010

evgeni

Updated on September 18, 2022

Comments

evgeni over 1 year

Hi and thank you for reading.

I am working on a distributed system and have 8 clusters, which have a Postgres instance each. However, one of the Postgres instances writes data extremely slow compared to the others. All computers run the same verion of Ubuntu, have the same specs and the same Postgres version installed.

Here is what I do:

Execute some Java code and write the created data to database through a JDBC connection. All happens in one Java instance. The exact data varies, but is of the same scale.

For the slow cluster I have the following execution times (in seconds):

Create Data with Java    WriteData
92                       11055

For other clusters I have more something like this:

Create Data with Java    WriteData
95                       266

I have already checked that the tablespace has enough diskspace and also completely reinstalled the Postgres instance, but have no further idea what the problem might be.

Thank you for any suggestions where I could look for potential issues!

UPDATE:

Some performace parameters:

diskSlow: sudo hdparm -t /dev/sda6 
/dev/sda6: Timing buffered disk reads: 76 MB in 3.05 seconds = 24.92 MB/sec

diskOther: sudo hdparm -t /dev/sda6 
/dev/sda6: Timing buffered disk reads: 314 MB in 3.00 seconds = 104.62 MB/sec

The timing cached reads are comparable.

UPDATE2: Ok, I found out that the slow cluster has been reinstalled recently and has a newer Ubuntu version.

SlowCluster                       OtherClusters: 
Release:    Ubuntu 10.10          Release:    9.10
Codename:   maverick              Codename:   karmic

lspci output:

Cluster slow:

00:00.0 Host bridge: Intel Corporation 4 Series Chipset DRAM Controller (rev 03)
00:02.0 VGA compatible controller: Intel Corporation 4 Series Chipset Integrated Graphics Controller (rev 03)
00:02.1 Display controller: Intel Corporation 4 Series Chipset Integrated Graphics Controller (rev 03)
00:1a.0 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #4
00:1a.1 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #5
00:1a.2 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #6
00:1a.7 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB2 EHCI Controller #2
00:1b.0 Audio device: Intel Corporation 82801JI (ICH10 Family) HD Audio Controller
00:1c.0 PCI bridge: Intel Corporation 82801JI (ICH10 Family) PCI Express Root Port 1
00:1c.5 PCI bridge: Intel Corporation 82801JI (ICH10 Family) PCI Express Root Port 6
00:1d.0 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #1
00:1d.1 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #2
00:1d.2 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #3
00:1d.7 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB2 EHCI Controller #1
00:1e.0 PCI bridge: Intel Corporation 82801 PCI Bridge (rev 90)
00:1f.0 ISA bridge: Intel Corporation 82801JIB (ICH10) LPC Interface Controller
00:1f.2 IDE interface: Intel Corporation 82801JI (ICH10 Family) 4 port SATA IDE Controller #1
00:1f.3 SMBus: Intel Corporation 82801JI (ICH10 Family) SMBus Controller
00:1f.5 IDE interface: Intel Corporation 82801JI (ICH10 Family) 2 port SATA IDE Controller #2
01:00.0 Ethernet controller: Realtek Semiconductor Co., Ltd. RTL8111/8168B PCI Express Gigabit Ethernet controller (rev 01)

Cluster others:

00:00.0 Host bridge: Intel Corporation 4 Series Chipset DRAM Controller (rev 03)
00:01.0 PCI bridge: Intel Corporation 4 Series Chipset PCI Express Root Port (rev 03)
00:1a.0 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #4
00:1a.1 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #5
00:1a.2 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #6
00:1a.7 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB2 EHCI Controller #2
00:1b.0 Audio device: Intel Corporation 82801JI (ICH10 Family) HD Audio Controller
00:1c.0 PCI bridge: Intel Corporation 82801JI (ICH10 Family) PCI Express Port 1
00:1c.5 PCI bridge: Intel Corporation 82801JI (ICH10 Family) PCI Express Port 6
00:1d.0 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #1
00:1d.1 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #2
00:1d.2 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB UHCI Controller #3
00:1d.7 USB Controller: Intel Corporation 82801JI (ICH10 Family) USB2 EHCI Controller #1
00:1e.0 PCI bridge: Intel Corporation 82801 PCI Bridge (rev 90)
00:1f.0 ISA bridge: Intel Corporation 82801JIB (ICH10) LPC Interface Controller
00:1f.2 IDE interface: Intel Corporation 82801JI (ICH10 Family) 4 port SATA IDE Controller
00:1f.3 SMBus: Intel Corporation 82801JI (ICH10 Family) SMBus Controller
00:1f.5 IDE interface: Intel Corporation 82801JI (ICH10 Family) 2 port SATA IDE Controller
01:00.0 VGA compatible controller: nVidia Corporation GT200 [GeForce GT 220] (rev a2)
01:00.1 Audio device: nVidia Corporation Device 0be2 (rev a1)
02:00.0 Ethernet controller: Realtek Semiconductor Co., Ltd. RTL8111/8168B PCI Express Gigabit Ethernet controller (rev 01)
03:00.0 Network controller: RaLink RT2860

Main difference between those two:

03:00.0 Network controller: RaLink RT2860
01:00.1 Audio device: nVidia Corporation Device 0be2 (rev a1)
00:01.0 PCI bridge: Intel Corporation 4 Series Chipset PCI Express Root Port (rev 03)

From lshw:

Cluster slow

width: 64 bits
    capabilities: vsyscall64 vsyscall32
  *-core
       description: Motherboard
       physical id: 0
     *-memory
          description: System memory
          physical id: 0
          size: 7967MiB
     *-cpu
          product: Intel(R) Core(TM)2 Quad CPU    Q8300  @ 2.50GHz
          vendor: Intel Corp.
          physical id: 1
          bus info: cpu@0
          size: 2003MHz
          capacity: 2003MHz
          width: 64 bits
          capabilities: fpu fpu_exception wp vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx x86-64 constant_tsc arch_perfmon pebs bts rep_good aperfmperf pni dtes64 monitor ds_cpl est tm2 ssse3 cx16 xtpr pdcm sse4_1 xsave lahf_lm cpufreq
 *-pci
      description: Host bridge
      product: 4 Series Chipset DRAM Controller
      vendor: Intel Corporation
      physical id: 100
      bus info: pci@0000:00:00.0
      version: 03
      width: 32 bits
      clock: 33MHz
      configuration: driver=agpgart-intel
      resources: irq:0

Other Clusters:

    width: 64 bits
    capabilities: vsyscall64 vsyscall32
  *-core
       description: Motherboard
       physical id: 0
     *-memory
          description: System memory
          physical id: 0
          size: 8960MiB
     *-cpu
          product: Intel(R) Core(TM)2 Quad CPU    Q8300  @ 2.50GHz
          vendor: Intel Corp.
          physical id: 1
          bus info: cpu@0
          size: 2003MHz
          capacity: 2003MHz
          width: 64 bits
          capabilities: fpu fpu_exception wp vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx x86-64 constant_tsc arch_perfmon pebs bts rep_good pni dtes64 monitor ds_cpl est tm2 ssse3 cx16 xtpr pdcm sse4_1 xsave lahf_lm cpufreq
 *-pci
      description: Host bridge
      product: 4 Series Chipset DRAM Controller
      vendor: Intel Corporation
      physical id: 100
      bus info: pci@0000:00:00.0
      version: 03
      width: 32 bits
      clock: 33MHz
    *-pci:0
         description: PCI bridge
         product: 4 Series Chipset PCI Express Root Port
         vendor: Intel Corporation
         physical id: 1
         bus info: pci@0000:00:01.0
         version: 03
         width: 32 bits
         clock: 33MHz
         capabilities: pci bus_master cap_list
         configuration: driver=pcieport-driver
         resources: irq:24 ioport:d000(size=4096) memory:fd000000-fe9fffff ioport:de000000(size=301989888)
       *-display UNCLAIMED
            description: VGA compatible controller
            product: GT200 [GeForce GT 220]
            vendor: nVidia Corporation
            physical id: 0
            bus info: pci@0000:01:00.0
            version: a2
            width: 64 bits
            clock: 33MHz
            capabilities: bus_master cap_list
            configuration: latency=0
            resources: memory:fd000000-fdffffff memory:e0000000-efffffff(prefetchable) memory:de000000-dfffffff(prefetchable) ioport:dc00(size=128) memory:fe900000-fe97ffff(prefetchable)

I would appreciate any hints. All clusters have (nearly) the same hardware. It might be that some drivers or configurations differ, but I am not at all familiar with these topics. Please let me know which way I could go.

Thanks!

Admin about 13 years

did you tried the disk write speed on each instance, with hdparm for example?
Admin about 13 years

Could you give the specs? Like for instance the raid controller and configuration used? First thing I would check are cache settings. If you have raid also check if the array is degraded (a disk failed).
Admin about 13 years

Is load on the systems otherwise comparable? Are there other processes running on them?
Admin about 13 years

@regilero: diskSlow => sudo hdparm -t /dev/sda6 => /dev/sda6: Timing buffered disk reads: 76 MB in 3.05 seconds = 24.92 MB/sec || diskFast => /dev/sda6: Timing buffered disk reads: 314 MB in 3.00 seconds = 104.62 MB/sec || The Timing cached reads are comparable.
Admin about 13 years

@ Frank: there are no processes running (the pre-database part of the java program performs just fine). How could I test the load?
Admin about 13 years

@Eelke tried /proc/mdstat, but its empty. Could you provide me some info on how to get the raid info and what info you need. Sorry I'm not familiar with hardware close settings, but would be happy to learn more.
Admin about 13 years

@evgeni, you still haven't told us anything about the hardware. What mainboard is there in your system? What additional storage controllers? How many and what type of disks? If you don't know maybe you should find someone who knows more about those systems. Atleast post the output of lspci.
Admin almost 13 years

@Eelke: Thanks I added more information please let me know what else could be of help.