Re: performance of insert/delete/update

On Tue, 26 Nov 2002, Andrew Sullivan wrote:

> On Mon, Nov 25, 2002 at 07:41:03PM -0600, Ron Johnson wrote:
> >
> > What if you are in a 24x365 environment?  Doing a VACUUM ANALYZE would
> > really slow down the nightly operations.
>
> Why?  After upgrading to 7.2, we find it a good idea to do frequent
> vacuum analyse on frequently-changed tables.  It doesn't block, and
> if you vacuum frequently enough, it goes real fast.

For example, I just ran pgbench -c 20 -t 200 (20 concurrent's) with a
script in the background that looked like this:

#!/bin/bash
for ((a=0;a=1;a=0)) do {
        vacuumdb -z postgres
}
done

(i.e. run vacuumdb in analyze against the database continuously.)

Output of top:

71 processes: 63 sleeping, 8 running, 0 zombie, 0 stopped
CPU0 states: 66.2% user, 25.1% system,  0.0% nice,  8.1% idle
CPU1 states: 79.4% user, 18.3% system,  0.0% nice,  1.2% idle
Mem:   254660K av,  249304K used,    5356K free,   26736K shrd,   21720K
buff
Swap: 3084272K av,    1300K used, 3082972K free                  142396K
cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
21381 postgres  11   0  1304 1304   868 S    10.8  0.5   0:00 pgbench
21393 postgres  14   0  4832 4832  4116 R     8.4  1.8   0:00 postmaster
21390 postgres   9   0  4880 4880  4164 S     7.8  1.9   0:00 postmaster
21385 postgres  14   0  4884 4884  4168 R     6.7  1.9   0:00 postmaster
21399 postgres   9   0  4768 4768  4076 S     6.3  1.8   0:00 postmaster
21402 postgres   9   0  4776 4776  4076 S     6.1  1.8   0:00 postmaster
21383 postgres  14   0  4828 4828  4112 R     5.9  1.8   0:00 postmaster
21386 postgres  14   0  4872 4872  4156 R     5.9  1.9   0:00 postmaster
21392 postgres   9   0  4820 4820  4104 S     5.9  1.8   0:00 postmaster
21409 postgres  11   0  4600 4600  3544 R     5.8  1.8   0:00 postmaster
21387 postgres   9   0  4824 4824  4108 S     5.4  1.8   0:00 postmaster
21394 postgres   9   0  4808 4808  4092 S     5.4  1.8   0:00 postmaster
21391 postgres   9   0  4816 4816  4100 S     5.0  1.8   0:00 postmaster
21398 postgres   9   0  4796 4796  4088 S     5.0  1.8   0:00 postmaster
21384 postgres   9   0  4756 4756  4040 R     4.8  1.8   0:00 postmaster
21389 postgres   9   0  4788 4788  4072 S     4.8  1.8   0:00 postmaster
21397 postgres   9   0  4772 4772  4056 S     4.6  1.8   0:00 postmaster
21388 postgres   9   0  4780 4780  4064 S     4.4  1.8   0:00 postmaster
21396 postgres   9   0  4756 4756  4040 S     4.3  1.8   0:00 postmaster
21395 postgres  14   0  4760 4760  4044 S     4.1  1.8   0:00 postmaster
21401 postgres  14   0  4736 4736  4036 R     4.1  1.8   0:00 postmaster
21400 postgres   9   0  4732 4732  4028 S     2.9  1.8   0:00 postmaster
21403 postgres   9   0  1000 1000   820 S     2.4  0.3   0:00 vacuumdb
21036 postgres   9   0  1056 1056   828 R     2.0  0.4   0:27 top
18615 postgres   9   0  1912 1912  1820 S     1.1  0.7   0:01 postmaster
21408 postgres   9   0   988  988   804 S     0.7  0.3   0:00 psql

So, pgbench is the big eater of CPU at 10%, each postmaster using about
5%, and vacuumdb using 2.4%.  Note that after a second, the vacuumdb use
drops off to 0% until it finishes and runs again.  The output of the
pgbench without vacuumdb running, but with top, to be fair was:

number of clients: 20
number of transactions per client: 200
number of transactions actually processed: 4000/4000
tps = 54.428632 (including connections establishing)
tps = 54.847276 (excluding connections establishing)

While the output with the vacuumdb running continuously was:

number of clients: 20
number of transactions per client: 200
number of transactions actually processed: 4000/4000
tps = 52.114343 (including connections establishing)
tps = 52.873435 (excluding connections establishing)

So, the difference in performance was around 4% slower.

I'd hardly consider that a big hit against the database.

Note that in every test I've made up and run, the difference is at most 5%
with vacuumdb -z running continuously in the background.  Big text fields,
lots of math, lots of fks, etc...

Yes, vacuum WAS a problem long ago, but since 7.2 came out it's only a
"problem" in terms of remember to run it.