postgres is extrem slow

q16marvin

Renowned Member
Jul 16, 2013
50
0
71
hi,

i use a windows server 2008 r2 on proxmox version 2.3-13, everything is running fine, only the postgres database is extrem slow, simpelst querys needs minutes :(

it look like the same reason like this post: http://forum.proxmox.com/archive/index.php/t-2599.html

i use virtio driver for disk and for network, i trie to change back from qcow2 to raw, nothing works :(

have you a idea?

thx!

here are my stats:

root@proxmox1:/etc/pve/qemu-server# pveversion -v
pve-manager: 2.3-13 (pve-manager/2.3/7946f1f1)
running kernel: 2.6.32-19-pve
proxmox-ve-2.6.32: 2.3-93
pve-kernel-2.6.32-16-pve: 2.6.32-82
pve-kernel-2.6.32-19-pve: 2.6.32-93
lvm2: 2.02.95-1pve2
clvm: 2.02.95-1pve2
corosync-pve: 1.4.4-4
openais-pve: 1.1.4-2
libqb: 0.10.1-2
redhat-cluster-pve: 3.1.93-2
resource-agents-pve: 3.9.2-3
fence-agents-pve: 3.1.9-1
pve-cluster: 1.0-36
qemu-server: 2.3-18
pve-firmware: 1.0-21
libpve-common-perl: 1.0-49
libpve-access-control: 1.0-26
libpve-storage-perl: 2.3-6
vncterm: 1.0-3
vzctl: 4.0-1pve2
vzprocps: 2.0.11-2
vzquota: 3.1-1
pve-qemu-kvm: 1.4-8
ksm-control-daemon: 1.1-1


root@proxmox1:/etc/pve/qemu-server# pveperf
CPU BOGOMIPS: 47998.88
REGEX/SECOND: 1001228
HD SIZE: 94.49 GB (/dev/mapper/pve-root)
BUFFERED READS: 16.92 MB/sec
AVERAGE SEEK TIME: 23.96 ms
FSYNCS/SECOND: 1490.61
DNS EXT: 65.23 ms
DNS INT: 12.56 ms
 
hi,

i use a windows server 2008 r2 on proxmox version 2.3-13, everything is running fine, only the postgres database is extrem slow, simpelst querys needs minutes :(

it look like the same reason like this post: http://forum.proxmox.com/archive/index.php/t-2599.html

i use virtio driver for disk and for network, i trie to change back from qcow2 to raw, nothing works :(

have you a idea?

thx!

here are my stats:

root@proxmox1:/etc/pve/qemu-server# pveversion -v
pve-manager: 2.3-13 (pve-manager/2.3/7946f1f1)
running kernel: 2.6.32-19-pve
proxmox-ve-2.6.32: 2.3-93
pve-kernel-2.6.32-16-pve: 2.6.32-82
pve-kernel-2.6.32-19-pve: 2.6.32-93
lvm2: 2.02.95-1pve2
clvm: 2.02.95-1pve2
corosync-pve: 1.4.4-4
openais-pve: 1.1.4-2
libqb: 0.10.1-2
redhat-cluster-pve: 3.1.93-2
resource-agents-pve: 3.9.2-3
fence-agents-pve: 3.1.9-1
pve-cluster: 1.0-36
qemu-server: 2.3-18
pve-firmware: 1.0-21
libpve-common-perl: 1.0-49
libpve-access-control: 1.0-26
libpve-storage-perl: 2.3-6
vncterm: 1.0-3
vzctl: 4.0-1pve2
vzprocps: 2.0.11-2
vzquota: 3.1-1
pve-qemu-kvm: 1.4-8
ksm-control-daemon: 1.1-1


root@proxmox1:/etc/pve/qemu-server# pveperf
CPU BOGOMIPS: 47998.88
REGEX/SECOND: 1001228
HD SIZE: 94.49 GB (/dev/mapper/pve-root)
BUFFERED READS: 16.92 MB/sec
AVERAGE SEEK TIME: 23.96 ms
FSYNCS/SECOND: 1490.61
DNS EXT: 65.23 ms
DNS INT: 12.56 ms


BUFFERED READS: 16.92 MB/sec -> Terrible slow
AVERAGE SEEK TIME: 23.96 ms -> Very high seek time

Luca
 
BUFFERED READS: 16.92 MB/sec -> Terrible slow
AVERAGE SEEK TIME: 23.96 ms -> Very high seek time

Luca

okay but what could be the reason for?

here are my iostat:

Linux 2.6.32-19-pve (proxmox1) 07/16/2013 _x86_64_ (8 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
7.89 0.00 7.42 4.65 0.00 80.04

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
cciss/c0d0 1028.45 37184.04 21618.31 415858072 241774412
dm-0 22.65 111.93 128.93 1251762 1441904
dm-1 11.83 26.84 67.79 300160 758184
dm-2 2115.96 37012.80 21421.59 413943014 239574330
 
okay but what could be the reason for?

here are my iostat:

Linux 2.6.32-19-pve (proxmox1) 07/16/2013 _x86_64_ (8 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
7.89 0.00 7.42 4.65 0.00 80.04

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
cciss/c0d0 1028.45 37184.04 21618.31 415858072 241774412
dm-0 22.65 111.93 128.93 1251762 1441904
dm-1 11.83 26.84 67.79 300160 758184
dm-2 2115.96 37012.80 21421.59 413943014 239574330

CCISS module is HP Smartarray controller.
Which model ?
RAID type ? (1-5-6-10-50-60) ?
HDD bus ? SATA or SAS ? 7200, 10k or 15k ?

Luca
 
Mhhh P400 *MUST* be more rapid.
1) RAID 5 in state n-1 ? One disk is bad ?
2) Firmware P400 && disks is upgraded ?

I estimate at least 150 Mbyte/sec on P400 && RAID 5 && SAS disks

Luca
 
Mhhh P400 *MUST* be more rapid.
1) RAID 5 in state n-1 ? One disk is bad ?
2) Firmware P400 && disks is upgraded ?

I estimate at least 150 Mbyte/sec on P400 && RAID 5 && SAS disks

Luca

this is all what i got from raid controller:

PHP:
Smart Array P400 in Slot 1
   Bus Interface: PCI
   Slot: 1
   Serial Number: PAFGK0M9VWL19R
   Cache Serial Number: PA2270J9VWJ1GZ
   RAID 6 (ADG) Status: Enabled
   Controller Status: OK
   Hardware Revision: Rev E
   Firmware Version: 7.18
   Rebuild Priority: Medium
   Expand Priority: Medium
   Surface Scan Delay: 15 secs
   Surface Scan Mode: Idle
   Wait for Cache Room: Disabled
   Surface Analysis Inconsistency Notification: Disabled
   Post Prompt Timeout: 0 secs
   Cache Board Present: True
   Cache Status: OK
   Accelerator Ratio: 25% Read / 75% Write
   Drive Write Cache: Disabled
   Total Cache Size: 512 MB
   No-Battery Write Cache: Disabled
   Cache Backup Power Source: Batteries
   Battery/Capacitor Count: 1
   Battery/Capacitor Status: OK
   SATA NCQ Supported: True

   Array: A
      Interface Type: SAS
      Unused Space: 0 MB
      Status: OK



      Logical Drive: 1
         Size: 820.2 GB
         Fault Tolerance: RAID 5
         Heads: 255
         Sectors Per Track: 32
         Cylinders: 65535
         Strip Size: 64 KB
         Status: OK
         Array Accelerator: Enabled
         Parity Initialization Status: Initialization Completed
         Unique Identifier: 600508B100104D3956574C3139520006
         Disk Name: /dev/cciss/c0d0
         Mount Points: /boot 511 MB
         OS Status: LOCKED
         Logical Drive Label: A0023670PAFGK0M9VWL19R18B8

      physicaldrive 1I:1:5
         Port: 1I
         Box: 1
         Bay: 5
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDC
         Serial Number: 3NM8BG1700009921S21K
         Model: HP      DG146BB976
         Current Temperature (C): 30
         Maximum Temperature (C): 41
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 1I:1:6
         Port: 1I
         Box: 1
         Bay: 6
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPD6
         Serial Number: BS05P930WA000914
         Model: HP      DG146BABCF
         Current Temperature (C): 33
         Maximum Temperature (C): 81
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 1I:1:7
         Port: 1I
         Box: 1
         Bay: 7
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPD6
         Serial Number: BS05P880BPH80833
         Model: HP      DG146BABCF
         Current Temperature (C): 33
         Maximum Temperature (C): 44
         PHY Count: 2
         PHY Transfer Rate: Unknown, 3.0GBPS

      physicaldrive 2I:1:1
         Port: 2I
         Box: 1
         Bay: 1
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDB
         Serial Number:         P4XBH0NA
         Model: HP      DG146BAAJB
         Current Temperature (C): 36
         Maximum Temperature (C): 46
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 2I:1:2
         Port: 2I
         Box: 1
         Bay: 2
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDB
         Serial Number:         P4XBBHDA
         Model: HP      DG146BAAJB
         Current Temperature (C): 36
         Maximum Temperature (C): 46
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 2I:1:3
         Port: 2I
         Box: 1
         Bay: 3
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDC
         Serial Number: 3NM53A4100009837QP6R
         Model: HP      DG146BB976
         Current Temperature (C): 31
         Maximum Temperature (C): 59
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 2I:1:4
         Port: 2I
         Box: 1
         Bay: 4
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDC
         Serial Number: 3NM79JE900009909S7A9
         Model: HP      DG146BB976
         Current Temperature (C): 29
         Maximum Temperature (C): 39
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 1I:1:8
         Port: 1I
         Box: 1
         Bay: 8
         Status: OK
         Drive Type: Spare Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPD6
         Serial Number: BS05P940Y1H90917
         Model: HP      DG146BABCF
         Current Temperature (C): 33
         Maximum Temperature (C): 90
         PHY Count: 2
         PHY Transfer Rate: 3.0GBPS, Unknown

what do you think what did i have to do?
 
this is all what i got from raid controller:

PHP:
Smart Array P400 in Slot 1
   Bus Interface: PCI
   Slot: 1
   Serial Number: PAFGK0M9VWL19R
   Cache Serial Number: PA2270J9VWJ1GZ
   RAID 6 (ADG) Status: Enabled
   Controller Status: OK
   Hardware Revision: Rev E
   Firmware Version: 7.18
   Rebuild Priority: Medium
   Expand Priority: Medium
   Surface Scan Delay: 15 secs
   Surface Scan Mode: Idle
   Wait for Cache Room: Disabled
   Surface Analysis Inconsistency Notification: Disabled
   Post Prompt Timeout: 0 secs
   Cache Board Present: True
   Cache Status: OK
   Accelerator Ratio: 25% Read / 75% Write
   Drive Write Cache: Disabled
   Total Cache Size: 512 MB
   No-Battery Write Cache: Disabled
   Cache Backup Power Source: Batteries
   Battery/Capacitor Count: 1
   Battery/Capacitor Status: OK
   SATA NCQ Supported: True

   Array: A
      Interface Type: SAS
      Unused Space: 0 MB
      Status: OK



      Logical Drive: 1
         Size: 820.2 GB
         Fault Tolerance: RAID 5
         Heads: 255
         Sectors Per Track: 32
         Cylinders: 65535
         Strip Size: 64 KB
         Status: OK
         Array Accelerator: Enabled
         Parity Initialization Status: Initialization Completed
         Unique Identifier: 600508B100104D3956574C3139520006
         Disk Name: /dev/cciss/c0d0
         Mount Points: /boot 511 MB
         OS Status: LOCKED
         Logical Drive Label: A0023670PAFGK0M9VWL19R18B8

      physicaldrive 1I:1:5
         Port: 1I
         Box: 1
         Bay: 5
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDC
         Serial Number: 3NM8BG1700009921S21K
         Model: HP      DG146BB976
         Current Temperature (C): 30
         Maximum Temperature (C): 41
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 1I:1:6
         Port: 1I
         Box: 1
         Bay: 6
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPD6
         Serial Number: BS05P930WA000914
         Model: HP      DG146BABCF
         Current Temperature (C): 33
         Maximum Temperature (C): 81
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 1I:1:7
         Port: 1I
         Box: 1
         Bay: 7
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPD6
         Serial Number: BS05P880BPH80833
         Model: HP      DG146BABCF
         Current Temperature (C): 33
         Maximum Temperature (C): 44
         PHY Count: 2
         PHY Transfer Rate: Unknown, 3.0GBPS

      physicaldrive 2I:1:1
         Port: 2I
         Box: 1
         Bay: 1
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDB
         Serial Number:         P4XBH0NA
         Model: HP      DG146BAAJB
         Current Temperature (C): 36
         Maximum Temperature (C): 46
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 2I:1:2
         Port: 2I
         Box: 1
         Bay: 2
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDB
         Serial Number:         P4XBBHDA
         Model: HP      DG146BAAJB
         Current Temperature (C): 36
         Maximum Temperature (C): 46
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 2I:1:3
         Port: 2I
         Box: 1
         Bay: 3
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDC
         Serial Number: 3NM53A4100009837QP6R
         Model: HP      DG146BB976
         Current Temperature (C): 31
         Maximum Temperature (C): 59
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 2I:1:4
         Port: 2I
         Box: 1
         Bay: 4
         Status: OK
         Drive Type: Data Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPDC
         Serial Number: 3NM79JE900009909S7A9
         Model: HP      DG146BB976
         Current Temperature (C): 29
         Maximum Temperature (C): 39
         PHY Count: 2
         PHY Transfer Rate: Unknown, Unknown

      physicaldrive 1I:1:8
         Port: 1I
         Box: 1
         Bay: 8
         Status: OK
         Drive Type: Spare Drive
         Interface Type: SAS
         Size: 146 GB
         Rotational Speed: 10000
         Firmware Revision: HPD6
         Serial Number: BS05P940Y1H90917
         Model: HP      DG146BABCF
         Current Temperature (C): 33
         Maximum Temperature (C): 90
         PHY Count: 2
         PHY Transfer Rate: 3.0GBPS, Unknown

what do you think what did i have to do?

Why "Drive Write Cache: Disabled" ?
Battery/Capacitor Count: 1
Battery/Capacitor Status: OK
You can enable write cache....

Firwmare version in old...
7.24 -> 4 set 2012
You use 7.18

Luca
 
Why "Drive Write Cache: Disabled" ?
Battery/Capacitor Count: 1
Battery/Capacitor Status: OK
You can enable write cache....

Firwmare version in old...
7.24 -> 4 set 2012
You use 7.18

Luca


i have read, when the cache is enabled and the machine is loosing energy or put off the server will lost data, is it not true? How can i activate the write cache without installing anything new?

and how can i install the firmware upgrade? is there a howto?

thank you so much!
 
i have read, when the cache is enabled and the machine is loosing energy or put off the server will lost data, is it not true? How can i activate the write cache without installing anything new?

and how can i install the firmware upgrade? is there a howto?

thank you so much!

If you use the server under UPS and BCC is enabled you can safer use write cache.
I suggest to download the last HP firmware DVD, you can upgrade all components (HDD, controller, NIC, ILO, ecc).
DVD is bootable.

Luca
 
If you use the server under UPS and BCC is enabled you can safer use write cache.
I suggest to download the last HP firmware DVD, you can upgrade all components (HDD, controller, NIC, ILO, ecc).
DVD is bootable.

Luca

sounds good, the server is in a good datacenter, so they have massive UPS :)

and i can upgrade all components and wont destroy something, i dont want to reinstall proxmox and the virtual machines.

thx!


Erik
 

Mhh, ok, but I use an internal smartarray P400i on Proxmox 3.0 without modify scheduler and speedup 10x faster.
BBU does not protect 512 Mbyte on board on controller P400 ? what other cache you intend?
I was referring to the cache on the controller board.

Luca

PS update the firmwares does not reinstall the system or lose data, only restart from official firmware DVD.
 
Regardless of any consideration you should update the firmware of P400.
Enabling controller write cache should assure better performance.

Luca
 
I (and the other guy) think that "Drive Write Cache: Disabled" is not the controller cache, but the single disks cache, that is usually enabled but in raid must be disabled and you have to rely on CONTROLLER (raid card) cache only (my experience is setup with LSI controllers).
 
AFAIK onboard raid controller cache and disk/drive cache are not the same thing.

DWC = Drive Write Cache

edit: i see mmenaz already answered what i'm trying to explain.
 
Last edited:
Regardless of any consideration you should update the firmware of P400.
Enabling controller write cache should assure better performance.

Luca

thank you soo much, look at this:

root@proxmox1:~# pveperf
CPU BOGOMIPS: 48000.12
REGEX/SECOND: 1041922
HD SIZE: 94.49 GB (/dev/mapper/pve-root)
BUFFERED READS: 199.18 MB/sec
AVERAGE SEEK TIME: 5.77 ms
FSYNCS/SECOND: 2591.38
DNS EXT: 52.87 ms
DNS INT: 13.56 ms (absolute-contacts.com)



thx thx thx!

PS: i only activate dwc!
 
thank you soo much, look at this:

root@proxmox1:~# pveperf
CPU BOGOMIPS: 48000.12
REGEX/SECOND: 1041922
HD SIZE: 94.49 GB (/dev/mapper/pve-root)
BUFFERED READS: 199.18 MB/sec
AVERAGE SEEK TIME: 5.77 ms
FSYNCS/SECOND: 2591.38
DNS EXT: 52.87 ms
DNS INT: 13.56 ms (absolute-contacts.com)



thx thx thx!

PS: i only activate dwc!


BUFFERED READS: 199.18 MB/sec -> OK for P400 RAID5 (best performance on RAID 10)
AVERAGE SEEK TIME: 5.77 ms -> OK for 10k rpm SAS disks

*BUT* you *MUST* upgrade P400 firmware... it is possible that the RAID be corrupted in rare conditions.
Several years ago, on P200 controller (HP) adding two hard drives in a *new* RAID 1 volume, the P200 corrupted an existing RAID 1 due to a firmware bug.
My fault that I had not updated the system firmware.

Luca
 
thank you soo much, look at this:

root@proxmox1:~# pveperf
CPU BOGOMIPS: 48000.12
REGEX/SECOND: 1041922
HD SIZE: 94.49 GB (/dev/mapper/pve-root)
BUFFERED READS: 199.18 MB/sec
AVERAGE SEEK TIME: 5.77 ms
FSYNCS/SECOND: 2591.38
DNS EXT: 52.87 ms
DNS INT: 13.56 ms (absolute-contacts.com)



thx thx thx!

PS: i only activate dwc!

I'm really surprised that enabling WRITE hd cache, your bugger READS are improved.
Also, if is really HD (and not controller's) write cache you have enabled, it's not protected by raid controller bbu so you will loose data in case of black out
 

About

The Proxmox community has been around for many years and offers help and support for Proxmox VE, Proxmox Backup Server, and Proxmox Mail Gateway.
We think our community is one of the best thanks to people like you!

Get your subscription!

The Proxmox team works very hard to make sure you are running the best software and getting stable updates and security enhancements, as well as quick enterprise support. Tens of thousands of happy customers have a Proxmox subscription. Get yours easily in our online shop.

Buy now!