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.