Обсуждение: 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

От
Khangelani Gama
Дата:

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

От
Venkata Balaji N
Дата:

 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

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

От
Khangelani Gama
Дата:

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.

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

От
Khangelani Gama
Дата:

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.