Re: Per thread Connection memory

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%

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

On Wed, Jan 27, 2016 at 8:03 PM, Thorsten Schöning <tschoening(at)am-soft(dot)de>
wrote:

> 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(dot)Schoening(at)AM-SoFT(dot)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-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>