On 5/11/2010 3:28 AM, Jason Armstrong wrote:
I have a performance question with a database workqueue I am working on.
I have two tables, the first containing information about files, and
the second is a workqueue table with 'jobs', intended for a worker
process to transfer the files to another machine:
create table log.file (id uuid, created timestamp default NOW() not null, filetype_id smallint, process_id smallint, filepath text, UNIQUE (id, filetype_id));
create table fileworkqueue.job (id uuid, filetype_id smallint, filepath text, attempt smallint not null default 0);
create index fwq_id_filetype_id_idx on fileworkqueue.job(id, filetype_id);
When we are processing without the workers running (ie just insert
into the log.file table, with the fileworkqueue.job table being filled
up by the trigger), we see a rate of about 3 milliseconds per insert.
When it is run with the workers removing data from the
fileworkqueue.job table, this drops to below 50 Ms.
Thanks for any ideas.
Does the worker keep a transaction open for a long period of time?
ie. worker:
startTransaction
select jobs
process job for a while
delete from job where...
commit
If so, that might be a problem.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
