[SQL] Setting the process title, or can I?

[Bèrto ëd Sèra]

Hi all,

running pg 8.3.5 on RHEL (no, they won't upgrade anyway and yes, they know
they should).

I currently have an emergency problem with an external java process that
sends in rubbish, resulting in deadlocks (might simply be "I take forever"
statements that get reported as deadlocks) that do not get resolved. As an
emergency procedure we have set a script that each minute has a look at the
situation and runs pg_cancel_backend() against anything that has been
waiting for more than X secs. Then it sleeps one more minute. There is no
data loss as the java source will simply queue the message for resubmission
after any failed attempt. For some reasons, however, this very simple 2
lines cycle sometimes freezes.

We do not have enough resources to investigate the causes and obviously we
are throwing all we have towards finding out the root of the problem, i.e.,
how garbage is formed outside PG. So as a dirty and quick hack to make sure
our failure filter works I wanted to have an external process kill and
relaunch the filter from cron each 30 minutes.

Is there anyway I can mark the process running the filter, maybe using the
update_process_title feature? I'd like to have something I can see from a
ps command, grep for it and kill -15 it, without wasting our scarce
resources on one more pg process to use info from pg_stat_activity.

Cheerio
Bèrto

-- 
==
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

Re: [SQL] Setting the process title, or can I?

[Frank Bax]

On 03/20/12 06:14, Bèrto ëd Sèra wrote:

So as a dirty and quick hack to
make sure our failure filter works I wanted to have an external process
kill and relaunch the filter from cron each 30 minutes.

Is there anyway I can mark the process running the filter, maybe using
the update_process_title feature? I'd like to have something I can see
from a ps command, grep for it and kill -15 it, without wasting our
scarce resources on one more pg process to use info from pg_stat_activity.



Change your filter so it only runs once; not in a forever loop; then add 
the filter to cron to run every minute.


If this new filter continues to freeze; then use pkill to get rid of it.
Hopefully RHEL has a pkill command which works like (ps | grep | kill).

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Setting the process title, or can I?

[Bèrto ëd Sèra]

Hi,

one thing I was trying to avoid was the connection overhead. For some
reason this box has been configured to 1500 connections (sic), I have no
immediate permission to install a pooler and obviously the overhead in
creating new processes is one of the things that kill it... So I was trying
to make it as painless as possible.

Cheerio
Bèrto

PS sorry Frank, you got mailed twice. I mistakenly hit a "reply only" the
first time and it did not come to the list.

On 20 March 2012 11:26, Frank Bax  wrote:

> On 03/20/12 06:14, Bèrto ëd Sèra wrote:
>
>> So as a dirty and quick hack to
>> make sure our failure filter works I wanted to have an external process
>> kill and relaunch the filter from cron each 30 minutes.
>>
>> Is there anyway I can mark the process running the filter, maybe using
>> the update_process_title feature? I'd like to have something I can see
>> from a ps command, grep for it and kill -15 it, without wasting our
>> scarce resources on one more pg process to use info from pg_stat_activity.
>>
>
>
> Change your filter so it only runs once; not in a forever loop; then add
> the filter to cron to run every minute.
>
> If this new filter continues to freeze; then use pkill to get rid of it.
> Hopefully RHEL has a pkill command which works like (ps | grep | kill).
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql
>



-- 
==
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

Re: [SQL] Setting the process title, or can I?

[Steve Crawford]

On 03/20/2012 03:14 AM, Bèrto ëd Sèra wrote:


I currently have an emergency ... As an emergency procedure we have 
set a script that each minute has a look at the situation and runs 
pg_cancel_backend() against anything that has been waiting for more 
than X secs. Then it sleeps one more minute...

...
Is there anyway I can mark the process running the filter, maybe using 
the update_process_title feature? I'd like to have something I can see 
from a ps command, grep for it and kill -15 it, without wasting our 
scarce resources on one more pg process to use info from pg_stat_activity.



Several things spring to mind.

1. The script that starts the filter can note its pid and set an at-job 
to kill it after 30 minutes. I've used this technique with success.


2. Create a new role specifically to run the filter. The role is 
generally visible in ps.


But this all begs the question. Why not:

3. Set the statement_timeout so long-running statements will be 
automatically killed. This can be done on a per-role basis so simply set 
it as desired for the role used by the problematic Java program. It is a 
default and can be changed by the user so if there are individual 
statements that need a longer timeout you can change just for those 
statements.


Cheers,
Steve


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Setting the process title, or can I?

[Bèrto ëd Sèra]

Hi!


> 3. Set the statement_timeout so long-running statements will be
> automatically killed. This can be done on a per-role basis so simply set it
> as desired for the role used by the problematic Java program. It is a
> default and can be changed by the user so if there are individual
> statements that need a longer timeout you can change just for those
> statements.
>

Short answer: because I ignored it was there :) Thanks, you just made my
day :)

Bèrto
-- 
==
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.