Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server

*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(at)c9901(dot)fm(dot)co(dot)za| ~>

*From:* Venkata Balaji N [mailto:nag1010(at)gmail(dot)com]
*Sent:* 14 March 2015 12:32 AM
*To:* Khangelani Gama
*Cc:* pgsql-admin(at)postgresql(dot)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.