On 2010-11-23 20.56, anypossibility wrote:
Thank you for your advice.I reviewed the query and it is the most simple one column value update with primary key query.
I would like to share this with you and would like to receive advice as to whether I am on the right track.
Facts: the connection maxed out and i could not even terminate postgres with SIGINT. I was afraid to do SIGQUIT so restarted the server itself (not sure if this was better decision).... the server has been running just fine until a few days ago. No hardware update. Other servers that has exactly same spec (code, version, hardware) is having no issue... from this facts, I am leaning towards hardware issue.. though I have no idea where to start... This started to happen on one server a few days ago. So far this happens once a day. No pattern what's so ever in terms of client request, time of the day...
Anything interesting in postgresql.log? Maybe you have to increase
logging to find anything.
Perhaps enable log_min_duration_statement to see if there are any long
running statements that could give you a hint.
For details see
http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html
My observation: From the look of output from the ps -ef | grep postgres (pasted below) what was happening is that postgres is not returning result or waiting for something. and that one process is holding up the rest of the process - basically nothing is processed yet postgres continue to receive request until it reaches to max connection. Where can I start to diagnose this issue? Any advice would be appreciated.
-----------------------------------------------------------------------------------
please note: Actual IP was replaced with Client_IP
SQL:~ root# ps -ef | grep postgres
502 891 1 0 0:05.61 ?? 0:06.54 /Library/PostgresPlus/8.3/bin/postgres -D /data
502 892 891 0 0:01.41 ?? 0:01.98 postgres: logger process
502 894 891 0 0:17.91 ?? 0:27.16 postgres: writer process
502 895 891 0 0:05.43 ?? 0:06.88 postgres: wal writer process
502 896 891 0 0:01.59 ?? 0:03.26 postgres: autovacuum launcher process
502 897 891 0 1:09.83 ?? 1:35.88 postgres: stats collector process
502 1007 891 0 2:10.40 ?? 33:38.91 postgres: DBA DB_Name Client_IP(60096) UPDATE
I would be curious about this process since it stands out by the amount
of time it been running. I would watch for a similar long running
process and try to see what pg_stat_activity and pg_lock says about it.
What hardware are you running on and what size database? Could it be you
have a very large table on slow hardware and some client is trying to
update all of that table?
Any other services running on the same host? Could it be a shared
storage used by some other host?
/r
502 1008 891 0 0:00.82 ?? 0:20.91 postgres: DBA DB_Name Client_IP(60097) UPDATE
502 45397 891 0 0:00.01 ?? 0:00.11 postgres: DBA DB_Name Client_IP(64007) SELECT
502 45398 891 0 0:00.06 ?? 0:00.59 postgres: DBA DB_Name Client_IP(64008) idle
502 45399 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64009) SELECT
502 45400 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64012) SELECT
502 45401 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64013) SELECT
502 45402 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64014) SELECT
502 45403 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64015) SELECT
502 45404 891 0 0:00.01 ?? 0:00.03 postgres: DBA DB_Name Client_IP(64016) SELECT
502 45405 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64017) SELECT
502 45406 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64018) UPDATE
502 45407 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64019) SELECT
502 45408 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64020) SELECT
502 45409 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64021) SELECT
502 45410 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64022) SELECT
502 45411 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64023) SELECT
502 45412 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64024) SELECT
502 45413 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64025) SELECT
502 45414 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64026) SELECT
502 45415 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64027) UPDATE
502 45416 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64028) SELECT
502 45417 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64029) SELECT
502 45418 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64030) SELECT
502 45419 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64031) SELECT
502 45430 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64032) SELECT
502 45434 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64033) startup
502 45435 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64034) startup
502 45436 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64035) startup
502 45437 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64036) startup
502 45438 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64037) startup
502 45439 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64038) startup
502 45440 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64039) startup
502 45441 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64040) startup
502 45442 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64043) startup
502 45443 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64044) startup
502 45444 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64045) startup
502 45445 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64046) startup
502 45446 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64047) startup
502 45447 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64048) startup
502 45448 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64049) startup
502 45449 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64050) startup
502 45450 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64051) startup
502 45451 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64052) startup
502 45452 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64053) startup
502 45453 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64054) startup
502 45456 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64055) startup
502 45470 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64056) startup
502 45471 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64057) startup
502 45472 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64058) startup
502 45473 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64059) startup
502 45474 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64060) startup
502 45475 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64061) startup
502 45476 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64062) startup
502 45477 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64063) startup
502 45478 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64064) startup
502 45479 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64065) startup
502 45480 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64068) startup
502 45481 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64069) startup
502 45482 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64070) startup
502 45483 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64071) startup
502 45484 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64072) startup
502 45485 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64074) startup
502 45486 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64075) startup
502 45487 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64076) startup
502 45488 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64077) startup
502 45489 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64078) startup
502 45490 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64079) startup
502 45491 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64080) startup
502 45505 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64081) startup
502 45506 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64082) startup
502 45507 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64083) startup
502 45508 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64084) startup
502 45509 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64085) startup
502 45510 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64086) startup
502 45511 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64087) startup
502 45512 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64088) startup
502 45513 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64089) startup
502 45514 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64090) startup
502 45515 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64092) startup
502 45516 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64094) startup
502 45517 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64095) startup
502 45518 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64096) startup
502 45519 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64097) startup
502 45520 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64098) startup
502 45521 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64099) startup
502 45522 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64100) startup
502 45523 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64101) startup
502 45537 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64104) startup
502 45539 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(64105) startup
502 45572 891 0 0:00.00 ?? 0:00.00 postgres: postgres DB_Name 127.0.0.1(53520) startup
502 45581 891 0 0:00.00 ?? 0:00.00 postgres: DBA DB_Name Client_IP(63737) startup
0 45764 45760 0 0:00.00 ttys000 0:00.00 grep postgres
0 45588 45585 0 0:00.01 ttys001 0:00.01 su - postgres
---- On Tue, 23 Nov 2010 04:00:07 -0800 Robert GravsjÃ<robert@xxxxxxxx> wrote ----
On 2010-11-22 20.41, anypossibility wrote:
> Server: OS X 10.5
> PostgreSQL version: 8.3
>
>
> We experience this connection maxing out once in the full moon.
> The request from client reaches to the server but client never receive response back.
> The queries are very simple update on one record or select one record using primary key (checked current_query from pg_stat_activity).
> Once this started, I normally disconnect all the client (quit client programs) however, the processes don't die on postgres server.
> So I tried to quit PostgreSQL server using pg_ctl, no response. The only way to terminate is to restart the server (I will try kill -s INT next time.. i didn't know better).
> Is there anything you can tell me from this symptoms or anything I can do to generate useful log to analyze?
If track_activities is on in your postgresql.conf you can check
pg_stats_activity to see what query creates the problem.
Maybe http://www.postgresql.org/docs/8.3/static/monitoring-stats.html
can give you some more ideas.
--
Regards,
Robert "roppert" GravsjÃ
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
