?
Solved

PostgreSQL 8.3.7 - How to tune up for 1024 connections

Posted on 2009-04-25
38
Medium Priority
?
2,703 Views
Last Modified: 2013-12-06
Need assistance tuning up PostgreSQL 8.3.7 for 1000 max connections, tried to set it to 500 but it returned an error about SHMMAX.

Debian Lenny
PostgreSQL 8.3.7
7.5Gb RAM

Thank you in advance
SHMMAX = 33554432
SHMMNI = 4096
SHMALL = 2097152
 
block_size	8192
maintenance_work_mem	16MB
max_connections	100
max_files_per_process	1000
max_fsm_pages	153600
max_fsm_relations	1000
max_prepared_transactions	5
shared_buffers	24MB
wal_buffers	64kB
work_mem	1MB

Open in new window

0
Comment
Question by:Raleigh Guevarra
  • 23
  • 13
36 Comments
 
LVL 62

Expert Comment

by:gheist
ID: 24233210
1024 processes for connections or 1024 connections might be of constraint. "sysctl vm" please.
Your shm (shmall and shmmax - all shm and size of single allocation) must be all fixed uses like SHM or fsm + work_mem + thread size = ~1,5GB
I suggest using pgproxy in your situation.

0
 

Author Comment

by:Raleigh Guevarra
ID: 24234737
here are the details from sysctl vm.

how to use pgproxy? would there be any effect with pgpool?
vm.swap_token_timeout = 300     0
vm.legacy_va_layout = 0
vm.vfs_cache_pressure = 100
vm.block_dump = 0
vm.laptop_mode = 0
vm.max_map_count = 65536
vm.percpu_pagelist_fraction = 0
vm.min_free_kbytes = 11223
vm.drop_caches = 0
vm.lowmem_reserve_ratio = 256   256     32
vm.swappiness = 60
vm.nr_pdflush_threads = 2
vm.dirty_expire_centisecs = 3000
vm.dirty_writeback_centisecs = 500
vm.dirty_ratio = 40
vm.dirty_background_ratio = 10
vm.page-cluster = 3
vm.overcommit_ratio = 50
vm.overcommit_memory = 0

Open in new window

0
 
LVL 62

Expert Comment

by:gheist
ID: 24235171
I mean pgpool (tm) product

sysctl fs - if you have any limitations against number of file handles per process etc.

from current:

sysctl vm.page-cluster=10
sysctl vm.page-cluster >> /etc/sysctl.conf
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 62

Expert Comment

by:gheist
ID: 24235264
You have to set kernel.shmall and kernel.shmmax to value in shm error or to something more
0
 

Author Comment

by:Raleigh Guevarra
ID: 24235286
Already set the shmmax to 256Mb and shmall to 4Mb

$ sysctl -w kernel.shmmax=268435456
$ sysctl -w kernel.shmall=4194304

then postgresql.conf max_connections = 500

Not sure with other variables, pls help me what needs to set to have the best performance.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24235301
4mb is too small for shared_buffers

make sure you save your sysctl tuning in /etc/sysctl.conf

"best performance" can be judged under load. Normally you adjust parameters in process
shared_buffers should be something like half of memory.
bigpages allocator is of help if you succeed at using it.
*fsm* can be adjusted only after vacuum
max_prepared_transactions should be like connections if you use prepared transactions.

I'd suggest pgpool firsthand - 1000 simultaneous connections do not seem to make sense. It would be something like 1 SQL/second on every connection.
0
 

Author Comment

by:Raleigh Guevarra
ID: 24235306
Save to /etc/sysctl.conf? Does it mean that it won't be saved between reboots?

what are the format for the two settings I just changed?

$ sysctl -w kernel.shmmax=268435456
$ sysctl -w kernel.shmall=4194304
0
 
LVL 62

Expert Comment

by:gheist
ID: 24235317
sysctl kernel.shmmax >> /etc/sysctl.conf

shmall is too low for any setup.

Let me suggest starting with default postgresql.conf and pgpool in front.
Pgpool will reorder SQL requests to smaller number of connections that PostgreSQL is designed to handle.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24235325
pgpool is something like tnslistener for Oracle - it holds TCP connections and invokes backends as needed.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24235332
Do not worry about bigpages and hugetlb for now - kernel has to be rebuilt in order to use that on Debian.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24235334
If you will be persistent in asking I will install PostgreSQL with full logging and explain how to react to different performance logs.
0
 

Author Comment

by:Raleigh Guevarra
ID: 24235337

Did these two already and added to sysctl.conf
sysctl kernel.shmmax >> /etc/sysctl.conf
sysctl kernel.shmall >> /etc/sysctl.conf

also, I'm already using pgpool II Version 2.1 beta1 with two nodes.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24235371
Then pgpool takes user connections.
Do you get any error from pgpool stating that backend is out of connections?
0
 
LVL 62

Expert Comment

by:gheist
ID: 24235392
You have to tune up shared_buffers to keep shared buffer hit rate above 90%
You have to tune up *fsm* based on vacuum results, so that vacuum does not have to free pages and can go easy.
You have to keep system shm parameters high enough to cover shm required by PostgreSQL
You have to make sure that you allocate enough prepared statements so that they are prepared immediatly instead of waiting for prepare slot to free up - preparation takes CPU time.

It is hard to tell without seeing real system load signs.
0
 

Author Comment

by:Raleigh Guevarra
ID: 24235413
Is it ok to change it to sysctl -w kernel.shmall=8388608 (8Mb)?
0
 
LVL 62

Expert Comment

by:gheist
ID: 24235459
kernel.shmall=2147483648
== 2GB


Yes it is OK
postgresql log tells minimum value acceptable in error message
0
 

Author Comment

by:Raleigh Guevarra
ID: 24238753
would that be safe to set the shmall to 2Gb for 7.5Gb of RAM? what about the shmmax? currently set to 256Mb only?
0
 
LVL 62

Expert Comment

by:gheist
ID: 24239629
Set both to same huge value
All shm segments will never exceed shmall
Single request will not exceed shmmax
PostgreSQL does one single shm allocation only, so it is logical to keep them to same value.
0
 

Author Comment

by:Raleigh Guevarra
ID: 24258817
ok, I set some Kernel and PostgreSQL variables, the only good thing is, the mem goes down but the CPU goes up to almost 98%

Please let me know what needs to tweak to lower the CPU usage


SHMMAX = 2684354560
SHMALL = 2684354560
 
shared_buffers = 300MB
temp_buffers = 16MB
max_prepared_transactions = 500
work_mem = 20MB
maintenance_work_mem = 320MB
max_stack_depth = 7680kB
max_fsm_pages = 204800
max_fsm_relations = 12000

Open in new window

0
 
LVL 62

Accepted Solution

by:
gheist earned 2000 total points
ID: 24258847
You should not lower CPU usage. You have covered bottleneck in your installation - now PostgreSQL runs at full power.

Linux has fair process scheduler for this to not happen.

If you feel PostgreSQL is slowing down webfront on same machine - you can run PostgreSQL at lower priority by adding nice into its startup script.

Now you have to look to tune disk IO - there is no more shortcomings in current mem/cpu use

Now you have to watch vmstat for long IO waits and adjust db spaces across disks.

Also setting WAL to SSD drive or at least to single-purpose independent drive is of use.
0
 

Author Comment

by:Raleigh Guevarra
ID: 24259110
my two perl scripts that crawls (8 instances) and indexes (70 instances) the text they gathered slows down too, it slows down inserting data.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24259195
Do they use same disks for temp as used by wal or database?
Is speedy of help with perl scripts?
0
 

Author Comment

by:Raleigh Guevarra
ID: 24259251
The perl scripts was saved in /home/user but symlinked to other volumes, the files are saved in the other volume but symlinked to /home/etc, same with /var/lib symlinked. does this settings caused the slowness?
0
 
LVL 62

Expert Comment

by:gheist
ID: 24259434
I guess there are more execution resources for interactive sessions and this takes something off from others.
It causes problems only if IO to places accessed by PERL is concurrent with increased database IO - but it does not slow doen running PERL - only its startup.

I found this quite close to what I could suggest for db tuning:
http://ossipedia.ipa.go.jp/en/capacity/EV0603300126EN/

My new guess on bottleneck is:
You might be running out of WAL space now - check if WAL directory contains log_count+1 files - then it is the bottleneck for now. Double count until not fully used.

But I guess that guide from Japan is of more education value than addressing single bottlenec every day and hitting new one.
0
 

Author Comment

by:Raleigh Guevarra
ID: 24259601
Where can I check the WAL space or WAL directory? log_count+1?
0
 
LVL 62

Expert Comment

by:gheist
ID: 24259681
Not wal - flown somewhere far behind the screen.
WAL is basically delayed disk write to make it optimal by writing entire 8kb blocks.

You have to check pg_xlog and rise checkpoint_segments to some value that:

They never get full - i.e count never reaches limit
They do not take too much storage say 1GB or something you are used to round to.

Start with 3->5->10->20->50(==800MB!!!!)
0
 

Author Comment

by:Raleigh Guevarra
ID: 24259865
Should I change these two variables? my shmmax and shmall are set to 2.5Gb and RAM is 7.5Gb, volume for /var/lib/postgres has 30Gb alone

wal_buffers = 64kB
checkpoint_segments = 3
top - 20:57:11 up  8:13,  6 users,  load average: 4.25, 3.24, 2.38
Tasks: 417 total,   9 running, 408 sleeping,   0 stopped,   0 zombie
Cpu(s): 70.0%us,  2.5%sy,  0.0%ni,  5.1%id,  3.6%wa,  0.2%hi,  0.2%si, 18.5%st
Mem:   7864320k total,  3298308k used,  4566012k free,   131716k buffers
Swap:        0k total,        0k used,        0k free,  1885980k cached
 
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
10392 postgres  21   0  408m  97m  95m R   74  1.3   0:21.33 postgres
10439 postgres  17   0  408m  68m  66m R   38  0.9   0:01.16 postgres
10440 postgres  17   0  408m  18m  16m R    5  0.2   0:00.16 postgres

Open in new window

0
 
LVL 62

Assisted Solution

by:gheist
gheist earned 2000 total points
ID: 24260122
It is notmal that postgresql uses CPU for logic processing.
If you increase shared buffers to gigabyte you will move more data closer to cpu, and it will use CPU more efficiently.

Start with checkpoint_segments=30 as in guide linked above.
There is very little IO wait (%wa) , nothing to improve by means of IO

As you see in this guide they got best improvement from changing sync method and moving wal == checkpoint files to independent disk.

wal buffers are memory used to buffer writes to checkpoint files.
lets try 512 wal buffers (4MB )
and 30 checkpoint_segments files.

And effective_cache_size = 2GB seems to reflect your header of "top"

There is some utility named pgtune - but be careful - it tunes as if PostgreSQL is only app on your system.
0
 

Author Comment

by:Raleigh Guevarra
ID: 24261453
I used pgtune that you mentioned above

./pgtune -i /etc/postgresql/8.3/main/postgresql.conf -o postgresql.txt -c 500

and changed it according to the recommended settings below, (I tried to use -M 7864320kB but returned an error)

default_statistics_target = 50
maintenance_work_mem = 480MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 5632MB
work_mem = 7680kB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 1920MB


top - 23:16:03 up  9:05,  4 users,  load average: 11.72, 4.90, 3.22
Tasks: 163 total,  23 running, 135 sleeping,   0 stopped,   5 zombie
Cpu(s): 51.8%us, 17.5%sy,  0.0%ni,  4.7%id,  7.6%wa,  0.2%hi,  0.2%si, 18.0%st
Mem:   7864320k total,  7312620k used,   551700k free,    38376k buffers
Swap:        0k total,        0k used,        0k free,  4973360k cached
 
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 8463 postgres  16   0  420m 334m 323m R   12  4.4  23:29.22 postgres
15079 postgres  15   0  437m 147m 120m R    6  1.9   0:00.74 postgres
15483 postgres  16   0  435m  67m  45m R    4  0.9   0:00.28 postgres
15558 postgres  15   0  418m  31m  17m S    4  0.4   0:00.11 postgres
15474 postgres  15   0     0    0    0 Z    3  0.0   0:00.29 postgres <defunct>
15536 postgres  15   0     0    0    0 Z    3  0.0   0:00.17 postgres <defunct>

Open in new window

0
 
LVL 62

Expert Comment

by:gheist
ID: 24264019
Now it puts even more load on CPU.
Have a look how your spider perl script performs - should be better with new checkpoint segment and wal setup.
Are you sure you need that many postgresql processes running? it seems like 4 only are working (or 11 if we consider load average data)
I guess something like 100 concurent sessions fits better in your picture.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24264021
I mean 100 postgres connections behind pgpool to not put thing to overload.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24264189
Also evaluate sync_method for 20% improvement.
If you run ext3 filesystem - investigate noatime and writeback options (carefuly - it may not mount in some setups after)
0
 
LVL 62

Expert Comment

by:gheist
ID: 24286862
What I found strange - Windows PostgreSQL asked to install thing named "EnterpriseDB Optimizer" which optimized config to very small shared_buffers, but kept WAL and checkpoint parameters high.

I am confused which is right way. Maybe shared buffers are really just a disk cache - a part which OS is able to share with other apps.
0
 

Author Comment

by:Raleigh Guevarra
ID: 24286956
I reset the settings back to the default and seems the scripts are running fine but still eating lots of resources. I'm trying to make a RAID0, hoping it will help with the disk I/O bottleneck but having trouble with mdadm on EC2 kernel. still in testing
0
 
LVL 62

Expert Comment

by:gheist
ID: 24287006
Adjust only WAL to 8MB and checkpoint segments to 30 - at least both optimization tools had same opinion there.
0
 
LVL 62

Expert Comment

by:gheist
ID: 24467521
My last comment gets B.
I went one optimizing way - which does not provide means of running other apps on same host.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document is written for Red Hat Enterprise Linux AS release 4 and ORACLE 10g.  Earlier releases can be installed using this document as well however there are some additional steps for packages to be installed see Metalink. Disclaimer: I hav…
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question