Обсуждение: Poor performance of UPDATE against busy table

Поиск
Список
Период
Сортировка

Poor performance of UPDATE against busy table

От
"Jeff Boes"
Дата:
We have a table being used as a job queue. There's one 'scheduler'
process that manipulates jobs (inserting, updating, and deleting rows/jobs as
they change status). There are a number (~24) of processes reading and
updating rows as they 'claim' and 'complete' jobs.

Our problem is that throughput on this table is fairly low. Updates by
the 'scheduler' sometimes take 15 seconds (!!).  We've tried various
combinations of LOCK TABLE commands, but without much success.

Does anyone know if there are ways to improve the performance of UPDATE
against a table where there are many readers?


--
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com

Re: Poor performance of UPDATE against busy table

От
"Ross J. Reedstrom"
Дата:
You don't mention your version of pgsql, but if you get a lot of
throughput on that table, how often do you VACUUM it? All those dead
tuples take space that has to be scanned through, since indices
don't keep track of tuple state.

Ross


On Thu, Sep 06, 2001 at 02:31:51PM -0400, Jeff Boes wrote:
> We have a table being used as a job queue. There's one 'scheduler'
> process that manipulates jobs (inserting, updating, and deleting rows/jobs as
> they change status). There are a number (~24) of processes reading and
> updating rows as they 'claim' and 'complete' jobs.
>
> Our problem is that throughput on this table is fairly low. Updates by
> the 'scheduler' sometimes take 15 seconds (!!).  We've tried various
> combinations of LOCK TABLE commands, but without much success.
>
> Does anyone know if there are ways to improve the performance of UPDATE
> against a table where there are many readers?
>
>
> --
> Jeff Boes                                             vox 616.226.9550
> Database Engineer                                     fax 616.349.9076
> Nexcerpt, Inc.                                      jboes@nexcerpt.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

Re: Poor performance of UPDATE against busy table

От
"Jeff Boes"
Дата:
In article <20010906141522.A3371@rice.edu>, "Ross J. Reedstrom"
<reedstrm@rice.edu> wrote:

> On Thu, Sep 06, 2001 at 02:31:51PM -0400, Jeff Boes wrote:
>> Does anyone know if there are ways to improve the performance of UPDATE
>> against a table where there are many readers?

> You don't mention your version of pgsql, but if you get a lot of
> throughput on that table, how often do you VACUUM it? All those dead
> tuples take space that has to be scanned through, since indices don't
> keep track of tuple state.

Bingo, you got it.  Actually, I came to this conclusion shortly after I
posted the original note, and did a VACUUM, which improved performance by
a factor of 100.

Excuses:

1) This is my first PostgreSQL database, so I wasn't aware of how
critical VACUUM can be in this kind of situation.

2) It's a development system, not a production system, so we don't have
regular backup/reorg set up yet (but it just jumped to the head of the
list of things-I-need-to-do-yesterday!).

--
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com