[pgpool-general: 7137] Re: how to find the query that is generating a high load

[Tatsuo Ishii]

>>>  Hi,
>>>  generally, using the top command, to see which connection to the db is
>>>  causing a high load, just take the pid inside the brackets and make a
>>>  fuser in the following way:
>>>
>>>  String of top command:    postgres: postgres dbname192.168.0.12(59864)
>>>
>>>  Command to see shell PID of process:   fuser 59864/tcp and I will see
>>>  PID of process.
>>>
>>>  With pgpool, however, this type of search does not work and the fuser
>>>  command does not show any process.
>>>  Is there any way to understand which query corresponds to that pid
>>>  59864?
>>
>>You want to display the PID of client command?
>>I tried the following command and I think "6736" is the PID which you
>>want to display, right?
>>
>>=================
>>$ fuser 58194/tcp
>>58194/tcp:            6736
>>
>>$ ps -ef | grep psql
>>pengbo 6736 9049 0 22:19 pts/8 00:00:00 psql -h 192.168.154.7 -p 11000
>>-U pengbo postgres
>>=================
>>
> It works like this on servers without pgpool, but in my case the fuser
> command returns nothing on either server with pgpool and postgres
> Why?

>>>  String of top command:    postgres: postgres dbname192.168.0.12(59864)

This means PostgreSQL is connected from client on 192.168.0.12 using
local port 59864 of 192.168.0.12. I don't know how you actually run
fuser against pgpool but if the client is pgpool, you need to run
fuser on 192.168.0.12.  Are you sure that?

BTW, pgpool already has a tool to show the relationship between
PostgreSQL pid and pgpool pid. Try:

"show pool_pools" from psql.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp