Thread: Longer and longer updates

Longer and longer updates

From
Steve Wilmarth
Date:
Hi all-

In doing some stress testing I noticed that our database was slowing
down significantly after doing the same update repeatedly on a table
with no indexes, so I tried this:

1) created an empty database

2) made a simple table with a single row:

   create table test (key int4, data int4);
   insert into test values (1, 0);

3) executed this statement tons of times:

   update test set data=1234 where key=1

Here are the results -- it's pretty discouraging, I hope I'm making some
simple mistake, or maybe this is expected behavior for some reason?

After this many updates        ...it took this long for 1000 more updates
-----------------------        ------------------------------------------
        0                                   10880 ms
      5,000                                 10549 ms
     10,000                                 17380 ms
     15,000                                 20040 ms
     20,000                                 20060 ms
     25,000                                 20589 ms
     30,000                                 30749 ms
     35,000                                 30350 ms
     40,000                                 30910 ms
     45,000                                 37570 ms
     50,000                                 40379 ms

This seems to be independent of starting and stopping my client and the
postmaster, running vacuum, praying, etc.  I'm on RedHat6.2
running with the 7.1beta4 rpms.

Anyone know what's going on here?

Steve Wilmarth









Re: Longer and longer updates

From
Alfred Perlstein
Date:
* Steve Wilmarth <swilmarth@eknow.com> [010205 12:50] wrote:
>
> Hi all-
>
> In doing some stress testing I noticed that our database was slowing
> down significantly after doing the same update repeatedly on a table
> with no indexes, so I tried this:
>
> 1) created an empty database
>
> 2) made a simple table with a single row:
>
>    create table test (key int4, data int4);
>    insert into test values (1, 0);
>
> 3) executed this statement tons of times:
>
>    update test set data=1234 where key=1
>
> Here are the results -- it's pretty discouraging, I hope I'm making some
> simple mistake, or maybe this is expected behavior for some reason?
>
> After this many updates        ...it took this long for 1000 more updates
> -----------------------        ------------------------------------------
>         0                                   10880 ms
>       5,000                                 10549 ms
>      10,000                                 17380 ms
>      15,000                                 20040 ms
>      20,000                                 20060 ms
>      25,000                                 20589 ms
>      30,000                                 30749 ms
>      35,000                                 30350 ms
>      40,000                                 30910 ms
>      45,000                                 37570 ms
>      50,000                                 40379 ms
>
> This seems to be independent of starting and stopping my client and the
> postmaster, running vacuum, praying, etc.  I'm on RedHat6.2
> running with the 7.1beta4 rpms.
>
> Anyone know what's going on here?

You're not vacuuming.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Re: Longer and longer updates

From
ender
Date:
if you're doing updates in a single transaction, you'll realize speed gains
by distributing the updates into multiple transactions. postgres won't have
to keep multiple copies that way.

hth

kapil


> >
> > 3) executed this statement tons of times:
> >
> >    update test set data=1234 where key=1
> >
> > Here are the results -- it's pretty discouraging, I hope I'm making some
> > simple mistake, or maybe this is expected behavior for some reason?
> >
> > After this many updates        ...it took this long for 1000 more updates
> > -----------------------        ------------------------------------------
> >         0                                   10880 ms
> >       5,000                                 10549 ms
> >      10,000                                 17380 ms
> >      15,000                                 20040 ms
> >      20,000                                 20060 ms
> >      25,000                                 20589 ms
> >      30,000                                 30749 ms
> >      35,000                                 30350 ms
> >      40,000                                 30910 ms
> >      45,000                                 37570 ms
> >      50,000                                 40379 ms
> >
> > This seems to be independent of starting and stopping my client and the
> > postmaster, running vacuum, praying, etc.  I'm on RedHat6.2
> > running with the 7.1beta4 rpms.

Re: Longer and longer updates

From
Steve Wilmarth
Date:

On Mon, 5 Feb 2001, Alfred Perlstein wrote:

> > ...
> > 3) executed this statement tons of times:
> >
> >    update test set data=1234 where key=1
> >
> > Here are the results -- it's pretty discouraging, I hope I'm making some
> > simple mistake, or maybe this is expected behavior for some reason?
> >
> > After this many updates        ...it took this long for 1000 more updates
> > -----------------------        ------------------------------------------
> >         0                                   10880 ms
> >       5,000                                 10549 ms
> >      10,000                                 17380 ms
> >      15,000                                 20040 ms
> >      20,000                                 20060 ms
> >      25,000                                 20589 ms
> >      30,000                                 30749 ms
> >      35,000                                 30350 ms
> >      40,000                                 30910 ms
> >      45,000                                 37570 ms
> >      50,000                                 40379 ms
> >
> > This seems to be independent of starting and stopping my client and the
> > postmaster, running vacuum, praying, etc.  I'm on RedHat6.2
> > running with the 7.1beta4 rpms.
> >
> > Anyone know what's going on here?
>
> You're not vacuuming.

VACUUMing shows some improvement -- it takes me back to the 20000ms level
or so, but I can never seem to get back to the 10000ms. Is this what you'd
expect?

Also, any thoughts on how often to run VACUUM?  The manual recommends
"nightly" for an operational database, but mine was slowing down
significantly after only a couple of hours of use.  Are there any rules
of thumb people use?  Also, VACUUM can take a few seconds to run -- is
that something people just live with and let the waiting transactions pile
up?  Or do you do something like VACUUM a single table at a time and
rotate which table gets done?  Is that a reasonable thing to do?


Thanks very much for the help-

Steve Wilmarth