postgres is extrem slow

Q

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
 
q16marvin said:
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
Click to expand...


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

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

Luca
Click to expand...

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
 
q16marvin said:
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
Click to expand...

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
 
dea said:
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
Click to expand...

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?
 
q16marvin said:
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?
Click to expand...

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
 
dea said:
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
Click to expand...


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!
 
q16marvin said:
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!
Click to expand...

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
 
dea said:
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
Click to expand...

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
 
snowman66 said:
You don't need to enable DWC. The battery/BBU protects only RAID cache.

See:
http://h30499.www3.hp.com/t5/ProLia...e-cache-on-P400-Smart-Controller/td-p/4732601

Try to change IO scheduler from deadline to CFQ.

http://forum.proxmox.com/threads/14383-changing-IO-Scheduler
http://pve.proxmox.com/wiki/IO_Scheduler
Click to expand...

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:
dea said:
Regardless of any consideration you should update the firmware of P400.
Enabling controller write cache should assure better performance.

Luca
Click to expand...

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!
 
q16marvin said:
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!
Click to expand...


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
 
q16marvin said:
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!
Click to expand...

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
 
You must log in or register to reply here.

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!

Quick Navigation

Home Get Subscription Wiki Downloads Proxmox Customer Portal About

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!
Community platform by XenForo® © 2010-2024 XenForo Ltd.
Top Bottom