Postgres运行非常缓慢

嗨，谢谢你的阅读。

我正在分布式系统上工作，有8个集群，每个集群都有一个Postgres实例。但是，其中一个Postgres实例与其他实例写入数据的速度相当缓慢。所有的电脑运行相同的Ubuntu版本，具有相同的规格和相同的Postgres版本安装。

这是我做的：

执行一些Java代码，并通过JDBC连接将创build的数据写入数据库。全部发生在一个Java实例中。确切的数据不尽相同，但规模相同。

对于慢集群，我有以下执行时间（以秒为单位）：

Create Data with Java WriteData 92 11055

对于其他集群我有更多这样的事情：

 Create Data with Java WriteData 95 266

我已经检查过表空间是否有足够的磁盘空间，并且完全重新安装了Postgres实例，但是没有进一步的了解问题是什么。

感谢您的任何build议，我可以find潜在的问题！

更新：

一些性能参数：

 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

定时caching读取是可比较的。

UPDATE2：好吧，我发现最近已经重新安装了慢集群，并且有一个更新的Ubuntu版本。

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

lspci输出：

集群缓慢：

 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)

集群其他：

 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

这两者的主要区别在于：

 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)

来自lshw：

集群缓慢

 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

其他集群：

  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)

我将不胜感激任何提示。所有集群都有（几乎）相同的硬件。这可能是一些司机或configuration不同，但我根本不了解这些话题。请让我知道我可以走哪条路。

谢谢！

较新的Linux内核（如Ubuntu 10.10中包含的内核）使用不同的方法在数据库写入时将信息清除到驱动器。当数据库使用常规的fsync调用之一时，Linux使用所谓的写屏障，每当您提交事务时就会发生这种情况。您的一个群集速度较慢，因为较新的写入屏障代码更加谨慎，将信息从各个硬盘写入caching中推出。即使在数据库要求写入数据库之后，较旧的Linux系统也会保留在驱动器的caching中。

请参阅Reliable Writes了解关于此主题的更多信息。就可靠的数据库写作而言，更新，更慢的集群实际上是在做正确的事情。在系统崩溃的情况下，更快的系统可能会丢失数据，而速度较慢的系统则没有这个问题。

如果你关心在崩溃中丢失数据的可能性，那么你应该担心的是速度更快的系统比慢速系统更重要。无论如何，有两件简单的事情可以加快缓慢的系统：

编辑你的postgresql.conf文件，closures参数synchronous_commit，并重新加载服务器。这会禁用数据库，要求操作系统刷新数据，因此在一台服务器上的写入屏障刷新性能如此不同。这是新的数据库损坏问题的安全（旧的仍然有风险），但你可以在崩溃中丢失一些数据。
用“nobarrier”选项重新安装文件系统。在这里，你会回到数据库崩溃的可能性，就像你的旧系统一样。

你可能想看看你的代码，并找出是否有可能更less提交到数据库。有时，如果您一次以大块的方式执行数据写入，则工作速度会更快。从你的问题，我想你可能会在每次写入数据库后提交。