[ADMIN] Per thread Connection memory

Ankur Kaushik

unread,

Jan 27, 2016, 2:30:33 AM1/27/16

to

Hi ,

For 16 GB Ram 8 core CPU , My Postgresql configuration are as below

listen_addresses = '*' # what IP address(es) to listen on;

max_connections = 1000 # (change requires restart)

shared_buffers = 5GB # min 128kB

dynamic_shared_memory_type = posix # the default is the first option

effective_cache_size = 4GB

log_destination = 'stderr' # Valid values are combinations of

logging_collector = on # Enable capturing of stderr and csvlog

log_directory = 'pg_log' # directory where log files are written,

log_filename = 'postgresql-%a.log' # log file name pattern,

log_truncate_on_rotation = on # If on, an existing log file with the

log_rotation_age = 1d # Automatic rotation of logfiles will

log_rotation_size = 0 # Automatic rotation of logfiles will

log_min_duration_statement = 100 # -1 is disabled, 0 logs all statements

log_line_prefix = '< %m >' # special values:

log_timezone = 'Asia/Kolkata'

datestyle = 'iso, mdy'

timezone = 'Asia/Kolkata'

lc_messages = 'en_US.UTF-8' # locale for system error message

lc_monetary = 'en_US.UTF-8' # locale for monetary formatting

lc_numeric = 'en_US.UTF-8' # locale for number formatting

lc_time = 'en_US.UTF-8' # locale for time formatting

default_text_search_config = 'pg_catalog.english'

I Am Monitoring the Connection for postgresql

while true; do (ps auxxx | grep postgres |wc -l); sleep 2; done

Per sec as connection grows near to 180 , Need to restart Tomcat .

Is PostgreSQL Take per thread Memory to0 High to assign

Wei Shan

unread,

Jan 27, 2016, 5:24:56 AM1/27/16

to

Hi,

I'm not sure why do you need to restart tomcat? Could you explain further?

Also, max_connections=1000 is way too higher for 5GB of shared buffers. You could try setting it to 300 and put a connection pooler like pgbouncer infront

Thanks!

Ankur Kaushik

unread,

Jan 27, 2016, 6:11:46 AM1/27/16

to

I am Monitor per sec query count using below command

====

while true; do (ps auxxx | grep postgres |wc -l); sleep 2; done

that around 390

=====

Actually The database is Migrated from Mysql to Postgresql

======

====

Most queries found in processlist is which full the Java memory pool , Hibernate is used in Application

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relnam

e AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind W

HEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TAB

LE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY SEQUENCE' WH

EN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c' T

HEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm' THEN 'MATERIALIZED VIEW' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n, pg_cat

alog.pg_class c L

====

Application Using Java Program , They have there java Pool system , when this Java pool memory full , tomcat need to restart in every 30- 60 min

Please Let me know in Postgresql pgbouncer is essential use for handling multithread applications ?

Thorsten Schöning

unread,

Jan 27, 2016, 9:35:49 AM1/27/16

to

Guten Tag Ankur Kaushik,
am Mittwoch, 27. Januar 2016 um 12:09 schrieben Sie:

> Application Using Java Program , They have there java Pool system ,
> when this Java pool memory full , tomcat need to restart in every 30- 60 min

So why do you think your problem is with Postgres? Client side
connections out of Java are not influenced by server side Postgres
configuration and Tomcat and Postgres don't share any memory.

Sounds like you should provide the exact error message you get.

> Please Let me know in Postgresql pgbouncer is essential use for
> handling multithread applications ?

No and you already successfully use your setup, else you wouldn't run
into problems, your only problem is with available resources and how
to balance them. But first you need to know where exactly your
bottleneck is and it sounds more like Java/Tomcat to me.

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

--
Sent via pgsql-admin mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Ankur Kaushik

unread,

Jan 29, 2016, 7:19:21 AM1/29/16

to

Hi ,

I have install pgbouncer Below are configurations

[pgbouncer]

logfile = /var/log/pgbouncer/pgbouncer.log

pidfile = /var/run/pgbouncer/pgbouncer.pid

listen_addr = *

#listen_addr = 127.0.0.1

listen_port = 5434

auth_type = md5

#auth_type = trust

auth_file = /etc/pgbouncer/userlist.txt

admin_users = postgres

stats_users = stats, postgres

pool_mode = session

#pool_mode = transaction

server_reset_query = DISCARD ALL

ignore_startup_parameters = extra_float_digits

max_client_conn = 500

default_pool_size = 40

#reserve_pool_timeout = 3

#server_idle_timeout = 60

Below processlist

pg_terminate_backend | state | pid | query

----------------------+-------+-------+----------------------------------

t | idle | 15366 | SET extra_float_digits = 3

t | idle | 15377 | SET extra_float_digits = 3

t | idle | 15378 | SHOW TRANSACTION ISOLATION LEVEL

t | idle | 15379 | SET extra_float_digits = 3

t | idle | 15380 | SET extra_float_digits = 3

t | idle | 15381 | SET extra_float_digits = 3

t | idle | 15382 | SET extra_float_digits = 3

t | idle | 15383 | SET extra_float_digits = 3

t | idle | 15384 | SET extra_float_digits = 3

t | idle | 15385 | SET extra_float_digits = 3

t | idle | 15389 | SET extra_float_digits = 3

t | idle | 15397 | SET extra_float_digits = 3

t | idle | 15398 | SET extra_float_digits = 3

t | idle | 15399 | SET extra_float_digits = 3

t | idle | 15400 | SET extra_float_digits = 3

t | idle | 15401 | SET extra_float_digits = 3

t | idle | 15402 | SET extra_float_digits = 3

t | idle | 15403 | SET extra_float_digits = 3

t | idle | 15404 | SET extra_float_digits = 3

t | idle | 15415 | SET extra_float_digits = 3

t | idle | 15416 | SET extra_float_digits = 3

t | idle | 15350 | SET extra_float_digits = 3

t | idle | 15351 | SET extra_float_digits = 3

Applied Script

PGPASSWORD="postgres" psql -p 5434 -U postgres -c "SELECT pg_terminate_backend(pid),state,pid,query FROM pg_stat_activity WHERE datname = 'nmmt_its' AND pid <> pg_backend_pid() AND state like 'idle%' AND state_change < current_timestamp - INTERVAL '2' SECOND" >/tmp/log.log

To Kill idle connection in every 10 sec , But after 15 -30 Min top command show as below

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

10454 root 20 0 7101m 2.4g 11m S 751.3 15.4 84:47.60 java

17637 root 20 0 15160 1408 988 R 0.3 0.0 0:00.01 top

1 root 20 0 19232 1496 1224 S 0.0 0.0 0:01.21 init

Where Jave load is shown 85%

==================

Thorsten Schöning

unread,

Jan 29, 2016, 8:20:33 AM1/29/16

to

Guten Tag Ankur Kaushik,
am Freitag, 29. Januar 2016 um 13:16 schrieben Sie:

> To Kill idle connection in every 10 sec , But after 15 -30 Min top command show as below

Killing idle connections is completely pointless, because those are
kept open for performance reasons, pgbouncer is designed to keep
connections open and idle, so that applications use them faster. If
you want less idle connections, reduce the configuration for those in
pgbouncer, but in the end you defeat its purpose that way.

> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 10454 root 20 0 7101m 2.4g 11m S 751.3 15.4 84:47.60 java

You problem is obviously that you don't have any clue what your
problem is, therefore you're just guessing things around. Don't do
that, at least look at the logs of your Tomcat, you may even use Java
Mission Control to monitor the performance of Tomcat and so on. This
high load might even be the result of your script introducing some
kind of a race condition, where you kill a connection just in that
moment where it was given to a using process in Tomcat.

If your problem is with Tomcat consuming too much memory or having
unexplainable high CPU load, don't waste your time with advancing the
complexity of your setup by installing pgbouncer. Focus on Tomcat and
its logs first.

jaime soler

unread,

Feb 1, 2016, 6:33:53 AM2/1/16

to

El vie, 29-01-2016 a las 14:18 +0100, Thorsten Schöning escribió:
> Guten Tag Ankur Kaushik,
> am Freitag, 29. Januar 2016 um 13:16 schrieben Sie:
>
> > To Kill idle connection in every 10 sec , But after 15 -30 Min top
> > command show as below
>
> Killing idle connections is completely pointless, because those are
> kept open for performance reasons, pgbouncer is designed to keep
> connections open and idle, so that applications use them faster. If
> you want less idle connections, reduce the configuration for those in
> pgbouncer, but in the end you defeat its purpose that way.

+1. If you don't want to keep many connections open in idle state, you
can configure them at pgbouncer level or pool configuration in tomcat
or application level.

>
>
> > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
> > COMMAND
> > 10454 root 20 0 7101m 2.4g 11m S 751.3 15.4
> > 84:47.60 java
>
> You problem is obviously that you don't have any clue what your
> problem is, therefore you're just guessing things around. Don't do
> that, at least look at the logs of your Tomcat, you may even use Java
> Mission Control to monitor the performance of Tomcat and so on. This
> high load might even be the result of your script introducing some
> kind of a race condition, where you kill a connection just in that
> moment where it was given to a using process in Tomcat.

Java is consuming 7/8 core of cpu, so i think you your look closer into
tomcat and java processes.

How about your work_mem parameter and top stats relatives of postgresql
server ?

>
> If your problem is with Tomcat consuming too much memory or having
> unexplainable high CPU load, don't waste your time with advancing the
> complexity of your setup by installing pgbouncer. Focus on Tomcat and
> its logs first.

+1

jaime soler gómez

unread,

Feb 1, 2016, 9:41:46 AM2/1/16

to

Jaime Soler Gómez
HOPLA Software
EnterpriseDB exclusive distributor ES/PT/IT & LatAm
| P : (+34) 616 71 44 41 | SKYPE : jaimesolergomez | W : http://www.hoplasoftware.com/

Ankur Kaushik

unread,

Feb 8, 2016, 5:04:04 AM2/8/16

to

Given Tomcat setting works ,,,, Thanks