Re: Workqueue performance

[Jason Armstrong <ja@xxxxxxxxxxxxxx>]

Thank-you for the tips about this issue.

I found two things:

1. NOTIFY/LISTEN was causing performance to degrade badly over time. I
have reworked my code to poll the database instead.

2. There was a further function trigger that I didn't include in the
description. I wanted to keep a status of the jobs in the workqueue. I
created two triggers on the job table, that would update a 'status'
table:

> create table fileworkqueue.status(filetype_id smallint not null, num integer not null default 0 check (num >= 0));

> CREATE FUNCTION fileworkqueue.add_status() RETURNS TRIGGER AS
$$
BEGIN
  UPDATE fileworkqueue.status SET num=num+1 WHERE filetype_id=NEW.filetype_id;
  return NULL;
END;
$$ LANGUAGE plpgsql;

> CREATE FUNCTION fileworkqueue.del_status() RETURNS TRIGGER AS
$$
BEGIN
  UPDATE fileworkqueue.status SET num=num-1 WHERE filetype_id=OLD.filetype_id;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fileworkqueue_add_trigger
  AFTER INSERT ON fileworkqueue.job
  FOR EACH ROW EXECUTE PROCEDURE fileworkqueue.add_status();

CREATE TRIGGER fileworkqueue_del_trigger
  AFTER DELETE ON fileworkqueue.job
  FOR EACH ROW EXECUTE PROCEDURE fileworkqueue.del_status();

So there were actually two triggers in the original design:

log.file -> fileworkqueue.job -> fileworkqueue.status

When I removed the second trigger to the 'status' table, performance
jumped tenfold. But now I had no means of monitoring how my workqueue
was performing. I decided to do this in application code instead, via
IPC.

-- 
Jason Armstrong

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