Re: Workqueue performance

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,
filepathtext, 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