Обсуждение: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
Hi all
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 analyse 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:
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
Some server config information currently are:
1. data/ directory size is 526G
2. Total memory is 24G
postgresql.conf:
# - Memory -
shared_buffers = 1024MB # min 128kB
# actively intend to use prepared transactions.
work_mem = 128MB # min 64kB
maintenance_work_mem = 512MB # min 1MB
checkpoint_segments = 30 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
From /etc/sysctl.conf file I have:
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
Thanks
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.
Re: 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:
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
Some server config information currently are:
1. data/ directory size is 526G
2. Total memory is 24G
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
500G 188G 313G 38% /FMBackup
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.
Any advices will help, see some information below:
Linux is CentOS release 6.4 (Final)
PostgreSQL version ?
Ø 9.2.4
Do you have all the 103 database in one cluster ? I suspect a DISK IO and CPU spike. Do you see that ?
Ø Yes all 103 database are in one cluster
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 ?
Ø Max connections is 200 because it was giving problems with 100 max connections
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.