Re: Per thread Connection memory

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(dot)ang(at)gmail(dot)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(at)gmail(dot)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
>>
>>