Thread: Setting the process title, or can I?
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.
==============================
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.
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).
Hi,
--
==============================
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.
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èrtoPS 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 <fbax@sympatico.ca> wrote:
On 03/20/12 06:14, Bèrto ëd Sèra wrote:Change your filter so it only runs once; not in a forever loop; then add the filter to cron to run every minute.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.
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.
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
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.