pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPer thread Connection memory
10+ messages / 6 participants
[nested] [flat]
* Per thread Connection memory
@ 2016-01-27 07:28 Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ankur Kaushik @ 2016-01-27 07:28 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
@ 2016-01-27 10:23 ` Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Wei Shan @ 2016-01-27 10:23 UTC (permalink / raw)
To: Ankur Kaushik <ankurkaushik@gmail.com>; +Cc: pgsql-admin
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!
On 27 Jan 2016 15:29, "Ankur Kaushik" <ankurkaushik@gmail.com> wrote:
>
> 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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
@ 2016-01-27 11:09 ` Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 14:33 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
2016-02-01 11:57 ` Re: Per thread Connection memory Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 2 replies; 10+ messages in thread
From: Ankur Kaushik @ 2016-01-27 11:09 UTC (permalink / raw)
To: Wei Shan <weishan.ang@gmail.com>; +Cc: pgsql-admin
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 ?
On Wed, Jan 27, 2016 at 3:53 PM, Wei Shan <weishan.ang@gmail.com> wrote:
> 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!
> On 27 Jan 2016 15:29, "Ankur Kaushik" <ankurkaushik@gmail.com> wrote:
>
>>
>> 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
>>
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
@ 2016-01-27 14:33 ` Thorsten Schöning <tschoening@am-soft.de>
2016-01-29 12:16 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Thorsten Schöning @ 2016-01-27 14:33 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 14:33 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
@ 2016-01-29 12:16 ` Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-29 13:18 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
0 siblings, 1 reply; 10+ messages in thread
From: Ankur Kaushik @ 2016-01-29 12:16 UTC (permalink / raw)
To: Thorsten Schöning <tschoening@am-soft.de>; +Cc: 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 14:33 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
2016-01-29 12:16 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
@ 2016-01-29 13:18 ` Thorsten Schöning <tschoening@am-soft.de>
2016-02-01 11:23 ` Re: Per thread Connection memory jaime soler gómez <jsoler@hoplasoftware.com>
2016-02-01 11:32 ` Re: Per thread Connection memory jaime soler <jaime.soler@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Thorsten Schöning @ 2016-01-29 13:18 UTC (permalink / raw)
To: pgsql-admin
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.
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 14:33 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
2016-01-29 12:16 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-29 13:18 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
@ 2016-02-01 11:23 ` jaime soler gómez <jsoler@hoplasoftware.com>
2016-02-08 10:01 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: jaime soler gómez @ 2016-02-01 11:23 UTC (permalink / raw)
To: Thorsten Schöning <tschoening@am-soft.de>; pgsql-admin
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
>
> 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
>
>
>
--
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/
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 14:33 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
2016-01-29 12:16 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-29 13:18 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
2016-02-01 11:23 ` Re: Per thread Connection memory jaime soler gómez <jsoler@hoplasoftware.com>
@ 2016-02-08 10:01 ` Ankur Kaushik <ankurkaushik@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Ankur Kaushik @ 2016-02-08 10:01 UTC (permalink / raw)
To: jsoler@hoplasoftware.com; +Cc: Thorsten Schöning <tschoening@am-soft.de>; pgsql-admin
Given Tomcat setting works ,,,, Thanks
On Mon, Feb 1, 2016 at 4:53 PM, jaime soler gómez <jsoler@hoplasoftware.com>
wrote:
> 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
>
> >
> > 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
> >
> >
> >
> --
> 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/
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 14:33 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
2016-01-29 12:16 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-29 13:18 ` Re: Per thread Connection memory Thorsten Schöning <tschoening@am-soft.de>
@ 2016-02-01 11:32 ` jaime soler <jaime.soler@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: jaime soler @ 2016-02-01 11:32 UTC (permalink / raw)
To: Thorsten Schöning <tschoening@am-soft.de>; pgsql-admin
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
>
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Per thread Connection memory
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Re: Per thread Connection memory Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Re: Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
@ 2016-02-01 11:57 ` Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 10+ messages in thread
From: Thomas Kellerer @ 2016-02-01 11:57 UTC (permalink / raw)
To: pgsql-admin
> 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
You should configure your connection pool in the Java application to _release_ those connections
e.g. if you are using the Tomcat 7 pool, I would configure something like maxIdle="10", minIdle="5" and maxActive="150" and then you need to define a timeout when an idle connection is closed by the pool, e.g. minEvictableIdleTimeMillis="300000" to close an idle connection after 5 minutes.
Depending on your workload you might want to close that faster (e.g. after 1 minute).
> Most queries found in processlist is which full the Java memory pool , Hibernate is used in Application
Those queries look suspiciously like the query that is used by DatabaseMetaData.getTables().
I think the c3p0 connection pool has a pretty stupid default configuration where the validation query (that is used to check if a connection is still "alive") uses exactly that call. And I also think this is configured to be called each time a connection is returned to the pool - which imposes a huge (and unnecessary load) on the database server.
If that is the case you should configure a different validation query that is less invasive. Something like: preferredTestQuery="select 42", once that is configure c3p0 will no longer call the (expensive) DatabaseMetaData.getTables() method. It's one of the many reasons I don't like the c3p0 pool. I think Tomcat7's pool is a much better choice (I'm _not_ talking about DBCP, but about the new "native" pool introduced with Tomcat7)
I also recommend to _not_ test the connections when the pools hands them out or if they are returned, because those validation query will then put too much load on the database server.
It's better to configure the pool to validate the connections when they are idle. Again, how you configure this, depends on the pool you are using.
> Please Let me know in Postgresql pgbouncer is essential use for handling multithread applications?
No, if you already have a connection pool in your application, then you don't need another one.
pgBouncer (or pgPool) are usually used for either load balancing or if the applications themselves don't do pooling.
Thomas
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2016-02-08 10:01 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-01-27 07:28 Per thread Connection memory Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 10:23 ` Wei Shan <weishan.ang@gmail.com>
2016-01-27 11:09 ` Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-27 14:33 ` Thorsten Schöning <tschoening@am-soft.de>
2016-01-29 12:16 ` Ankur Kaushik <ankurkaushik@gmail.com>
2016-01-29 13:18 ` Thorsten Schöning <tschoening@am-soft.de>
2016-02-01 11:23 ` jaime soler gómez <jsoler@hoplasoftware.com>
2016-02-08 10:01 ` Ankur Kaushik <ankurkaushik@gmail.com>
2016-02-01 11:32 ` jaime soler <jaime.soler@gmail.com>
2016-02-01 11:57 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox