Re: Per thread Connection memory - Mailing list pgsql-admin
| From | Ankur Kaushik |
|---|---|
| Subject | Re: Per thread Connection memory |
| Date | |
| Msg-id | CALXoLqxODO4UTFrgDs7YZh+mArE=4tZsx1jiAHiUKYi97AvZ4g@mail.gmail.com Whole thread Raw |
| In response to | Re: Per thread Connection memory (Thorsten Schöning <tschoening@am-soft.de>) |
| Responses |
Re: Per thread Connection memory
|
| List | pgsql-admin |
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@am-soft.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.Schoening@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-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
pgsql-admin by date: