2

I am using PostgreSQL 9.1 with pgbouncer as a connection pool and my os is Linux5 x86_64 GNU/Linux. but my problem is its working fine after server start up and all my queries from application executes well but after few hours my data base become slow and after few minutes it got completely stuck in fact I can't see my activity using

select * from pg_stat_activity;

but after restarting my jboss and database it again works fine.And it happens in busy transaction time.
I think that my memory is not released from database after a query.

faran
  • 3,713
  • 1
  • 19
  • 12
smn_onrocks
  • 1,282
  • 1
  • 18
  • 33
  • 1
    Anything in the log files? Try turning checkpoint logging on. PostgreSQL version, operating system, and PostgreSQL settings? – Craig Ringer Mar 04 '14 at 05:52
  • postgresql 9.1.7.12, RHEL 5.8 x86_64 GNU/Linux. – smn_onrocks Mar 04 '14 at 06:19
  • @ Craig Ringer will you be little specific sir about which parameter of checkpoint you are talking about i got checkpoint_segments,checkpoint_timeout,checkpoint_completion_target,checkpoint_warning parameters – smn_onrocks Mar 04 '14 at 07:42
  • https://wiki.postgresql.org/wiki/Logging_Checkpoints – Craig Ringer Mar 04 '14 at 07:43
  • thanks for the link but it didn't help any other idea.... – smn_onrocks Mar 04 '14 at 08:18
  • So, *what* is happening? Is the machine running at 100% CPU, disk I/O, memory? – Richard Huxton Mar 04 '14 at 09:03
  • `avg-cpu: %user %nice %system %iowait %steal %idle 5.08 0.00 0.56 5.17 0.00 89.19` `Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 151.94 903.76 4536.37 2582075282 12960554560 sda1 41.34 8.20 949.40 23425178 2712453760 sda2 24.06 5.71 457.36 16320575 1306684800 sda3 0.03 1.20 1.20 3419552 3418576 sda4 86.50 888.65 3128.42 2538909553 8937997424` – smn_onrocks Mar 04 '14 at 09:54
  • cpu use 100% amd mem 21.94% – smn_onrocks Mar 04 '14 at 09:56
  • What is the process using the CPU? `$ top` – Clodoaldo Neto Mar 04 '14 at 11:18
  • It is almost 99% above all the time.. – smn_onrocks Mar 04 '14 at 11:24
  • ` PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 18517 enterpri 18 0 2520m 2.1g 2.0g R 21.9 13.3 2:23.96 postgres: enterprisedb etrans 127.0.0.1[32771] SELECT ` one of my heavy process showing this status any help.. – smn_onrocks Mar 04 '14 at 12:37
  • I could not able to solve my prob yet. please any one can help me on this.. – smn_onrocks Mar 05 '14 at 04:59
  • Do you have query logging enabled? Please see the discussion here http://stackoverflow.com/questions/722221/how-to-log-postgres-sql-queries. Generating a log and feeding it to pgbadger will be very useful. Even if query logging is not enabled, pgbadger may provide some useful output. Also, I find output of htop more useful than that of top. htop uses - http://www.thegeekstuff.com/2011/09/linux-htop-examples/ – Jayadevan Mar 05 '14 at 05:01
  • Do PostgreSQL have any query performance impact on the available space in my physical storage like hard disk – smn_onrocks Mar 05 '14 at 07:44
  • any more solutions??? – smn_onrocks Mar 07 '14 at 06:00

1 Answers1

-1

After a long research i have solve my problem by implementing pgbounce with its proper configuration ... and m very happy now with it.. Thanks all of my friend who have tried to add some value by commenting..

smn_onrocks
  • 1,282
  • 1
  • 18
  • 33
  • 2
    Can you share the experience with pgbounce?. How can I configure it with my existing application? – Sajeev Jul 06 '15 at 07:30