Re: Per thread Connection memory - Mailing list pgsql-admin
| From | Ankur Kaushik |
|---|---|
| Subject | Re: Per thread Connection memory |
| Date | |
| Msg-id | CALXoLqzk5CkZq=tKvv=W+ugc43gr6Ug-ELo9HnjM9pxJ0Pm-FQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Per thread Connection memory (Wei Shan <weishan.ang@gmail.com>) |
| Responses |
Re: Per thread Connection memory
(Thorsten Schöning <tschoening@am-soft.de>)
Re: Per thread Connection memory (Thomas Kellerer <spam_eater@gmx.net>) |
| List | 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 belowlisten_addresses = '*' # what IP address(es) to listen on;max_connections = 1000 # (change requires restart)shared_buffers = 5GB # min 128kBdynamic_shared_memory_type = posix # the default is the first optioneffective_cache_size = 4GBlog_destination = 'stderr' # Valid values are combinations oflogging_collector = on # Enable capturing of stderr and csvloglog_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 thelog_rotation_age = 1d # Automatic rotation of logfiles willlog_rotation_size = 0 # Automatic rotation of logfiles willlog_min_duration_statement = 100 # -1 is disabled, 0 logs all statementslog_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 messagelc_monetary = 'en_US.UTF-8' # locale for monetary formattinglc_numeric = 'en_US.UTF-8' # locale for number formattinglc_time = 'en_US.UTF-8' # locale for time formattingdefault_text_search_config = 'pg_catalog.english'I Am Monitoring the Connection for postgresqlwhile true; do (ps auxxx | grep postgres |wc -l); sleep 2; donePer sec as connection grows near to 180 , Need to restart Tomcat .Is PostgreSQL Take per thread Memory to0 High to assign
pgsql-admin by date: