pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedI am working on trying to improve a database perfomance in version 9.2 Postgresql database server
4+ messages / 2 participants
[nested] [flat]
* I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
@ 2015-03-13 09:32 Khangelani Gama <kgama@argility.com>
0 siblings, 1 reply; 4+ messages in thread
From: Khangelani Gama @ 2015-03-13 09:32 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
@ 2015-03-13 22:32 Venkata Balaji N <nag1010@gmail.com>
parent: Khangelani Gama <kgama@argility.com>
0 siblings, 2 replies; 4+ messages in thread
From: Venkata Balaji N @ 2015-03-13 22:32 UTC (permalink / raw)
To: Khangelani Gama <kgama@argility.com>; +Cc: pgsql-admin
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
@ 2015-03-16 05:50 Khangelani Gama <kgama@argility.com>
parent: Venkata Balaji N <nag1010@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Khangelani Gama @ 2015-03-16 05:50 UTC (permalink / raw)
To: Venkata Balaji N <nag1010@gmail.com>; +Cc: pgsql-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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: I am working on trying to improve a database perfomance in version 9.2 Postgresql database server
@ 2015-03-16 08:15 Khangelani Gama <kgama@argility.com>
parent: Venkata Balaji N <nag1010@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Khangelani Gama @ 2015-03-16 08:15 UTC (permalink / raw)
To: Venkata Balaji N <nag1010@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-03-16 08:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-03-13 09:32 I am working on trying to improve a database perfomance in version 9.2 Postgresql database server Khangelani Gama <kgama@argility.com>
2015-03-13 22:32 ` Venkata Balaji N <nag1010@gmail.com>
2015-03-16 05:50 ` Khangelani Gama <kgama@argility.com>
2015-03-16 08:15 ` Khangelani Gama <kgama@argility.com>
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