From: Khangelani Gama <kgama@argility.com>
To: Venkata Balaji N <nag1010@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
Date: Mon, 16 Mar 2015 07:50:10 +0200
Message-ID: <808fb4e2e74edd385181280ad1ed7c61@mail.gmail.com> (raw)
In-Reply-To: <CAEyp7J9M6v=cpUN++k0Yg4P4mR8x6miaMg7M09Rve+wXpErrTw@mail.gmail.com>
References: <e0b4ad4d3f67c3f7aaff62f2fdab2536@mail.gmail.com>
<CAEyp7J9M6v=cpUN++k0Yg4P4mR8x6miaMg7M09Rve+wXpErrTw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
*CPU Information:*
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
stepping : 2
cpu MHz : 2933.437
cache size : 12288 KB
physical id : 0
siblings : 4
core id : 0
cpu cores : 4
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts mmx fxsr sse sse2 ss ht syscall nx rdtscp lm
constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc
aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt
aes hypervisor lahf_lm ida arat epb dts
bogomips : 5866.87
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
stepping : 2
cpu MHz : 2933.437
cache size : 12288 KB
physical id : 0
siblings : 4
core id : 1
cpu cores : 4
apicid : 1
initial apicid : 1
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts mmx fxsr sse sse2 ss ht syscall nx rdtscp lm
constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc
aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt
aes hypervisor lahf_lm ida arat epb dts
bogomips : 5866.87
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
stepping : 2
cpu MHz : 2933.437
cache size : 12288 KB
physical id : 0
siblings : 4
core id : 2
cpu cores : 4
apicid : 2
initial apicid : 2
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts mmx fxsr sse sse2 ss ht syscall nx rdtscp lm
constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc
aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt
aes hypervisor lahf_lm ida arat epb dts
bogomips : 5866.87
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
stepping : 2
cpu MHz : 2933.437
cache size : 12288 KB
physical id : 0
siblings : 4
core id : 3
cpu cores : 4
apicid : 3
initial apicid : 3
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts mmx fxsr sse sse2 ss ht syscall nx rdtscp lm
constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc
aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt
aes hypervisor lahf_lm ida arat epb dts
bogomips : 5866.87
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 4
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
stepping : 2
cpu MHz : 2933.437
cache size : 12288 KB
physical id : 1
siblings : 4
core id : 0
cpu cores : 4
apicid : 4
initial apicid : 4
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts mmx fxsr sse sse2 ss ht syscall nx rdtscp lm
constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc
aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt
aes hypervisor lahf_lm ida arat epb dts
bogomips : 5866.87
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 5
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
stepping : 2
cpu MHz : 2933.437
cache size : 12288 KB
physical id : 1
siblings : 4
core id : 1
cpu cores : 4
apicid : 5
initial apicid : 5
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts mmx fxsr sse sse2 ss ht syscall nx rdtscp lm
constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc
aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt
aes hypervisor lahf_lm ida arat epb dts
bogomips : 5866.87
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 6
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
stepping : 2
cpu MHz : 2933.437
cache size : 12288 KB
physical id : 1
siblings : 4
core id : 2
cpu cores : 4
apicid : 6
initial apicid : 6
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts mmx fxsr sse sse2 ss ht syscall nx rdtscp lm
constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc
aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt
aes hypervisor lahf_lm ida arat epb dts
bogomips : 5866.87
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
processor : 7
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU X5670 @ 2.93GHz
stepping : 2
cpu MHz : 2933.437
cache size : 12288 KB
physical id : 1
siblings : 4
core id : 3
cpu cores : 4
apicid : 7
initial apicid : 7
fpu : yes
fpu_exception : yes
cpuid level : 11
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts mmx fxsr sse sse2 ss ht syscall nx rdtscp lm
constant_tsc arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc
aperfmperf unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 popcnt
aes hypervisor lahf_lm ida arat epb dts
bogomips : 5866.87
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
*Partition information:*
~> fdisk -l
Disk /dev/sda: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0004f143
Device Boot Start End Blocks Id System
/dev/sda1 * 1 64 512000 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 64 6528 51915776 8e Linux LVM
Disk /dev/sdb: 536.9 GB, 536870912000 bytes
255 heads, 63 sectors/track, 65270 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x7ab4f5df
Device Boot Start End Blocks Id System
/dev/sdb1 1 65270 524281243+ 83 Linux
Disk /dev/sdc: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x430139e1
Device Boot Start End Blocks Id System
/dev/sdc1 1 6527 52428096 83 Linux
Disk /dev/sdd: 859.0 GB, 858993459200 bytes
255 heads, 63 sectors/track, 104433 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x55dc0a43
Device Boot Start End Blocks Id System
/dev/sdd1 1 104433 838858041 83 Linux
Disk /dev/mapper/vg_centtemp-lv_root: 49.0 GB, 48997859328 bytes
255 heads, 63 sectors/track, 5956 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
Disk /dev/mapper/vg_centtemp-lv_swap: 4160 MB, 4160749568 bytes
255 heads, 63 sectors/track, 505 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
File System
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_centtemp-lv_root
45G 13G 31G 29% /
tmpfs 12G 76K 12G 1% /dev/shm
/dev/sda1 485M 38M 423M 9% /boot
/dev/sdb1 493G 107G 361G 23% /home
/dev/sdc1 50G 36G 12G 76% /usr/local/jboss
/dev/sdd1 788G 529G 219G 71% /pgsql0
//172.23.30.34/Backup
500G 188G 313G 38% /FMBackup
root@c9901.fm.co.za| ~>
*From:* Venkata Balaji N [mailto:nag1010@gmail.com]
*Sent:* 14 March 2015 12:32 AM
*To:* Khangelani Gama
*Cc:* pgsql-admin@postgresql.org
*Subject:* Re: [ADMIN] I am working on trying to improve a database
perfomance in version 9.2 Postgresql database server
I need some help in trying improve database performance on the server that
has 103 databases running in one server. Autovacuum is switched off
intentionally , it was causing issues until I implemented a script where I
run vacuum analyze every Sunday of the week, I don’t know if it’s enough or
not but each database receive about 4000 new transactions a day. I run
VACUUM ANALYZE in each active database that’s on this server. Currents are
like when I try to create a new database on the same server using a created
command, it takes long to finish, hence takes to restore a database on the
server. Sometimes the load average hits about 20 to 30 when there is many
things running on the server. It takes about 6 hours for this vacuum to
finish running in all these databases every Sunday. Is it necessary to run
any REINDEXes.
Any advices will help, see some information below:
PostgreSQL version ?
Do you have all the 103 database in one cluster ? I suspect a DISK IO and
CPU spike. Do you see that ?
Its hard to say straight if there is a need for REINDEXING. Can you let us
know the hardware specifications of the server.
We will need to understand if the server capacity is falling short for the
load being received.
How many active connections you see at the database level ?
I am thinking of increasing values on the following parameters in the conf
file after some reading ing
http://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
*Parameters:*
work_mem
shared_buffers
maintenance_work_mem
checkpoint_segments
We need to know the hardware specifications.
Some server config information currently are:
1. data/ directory size is 526G
2. Total memory is 24G
Do you see any over utilization of the memory.
Regards,
Venkata Balaji N
CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgama@argility.com, nag1010@gmail.com
Subject: Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
In-Reply-To: <808fb4e2e74edd385181280ad1ed7c61@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox