Gregory Stark wrote:
"Ron St-Pierre" <ron.pgsql@xxxxxxx> writes:
We vacuum only a few of our tables nightly, this one is the last one because it
takes longer to run. I'll probably re-index it soon, but I would appreciate any
advice on how to speed up the vacuum process (and the db in general).
...
vacuum_cost_delay = 200
Well speeding up vacuum isn't really useful in itself. In fact you have vacuum
configured to run quite slowly by having vacuum_cost_delay set so high. You
have it set to sleep 200ms every few pages. If you lower that it'll run faster
but take more bandwidth away from the foreground tasks.
It's okay if it uses a lot of resources, because it's scheduled to run
during the night (our slow time). Because most of the important queries
running during the day use this table, I want the vacuum analzye
finished ASAP.
Here's the table information:
The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 indexes.
For what it's worth NUMERIC columns take more space than you might expect.
Figure a minimum of 12 bytes your rows are at about 1.5k each even if the
non-numeric columns aren't large themselves. What are the other columns?
The NUMERIC columns hold currency related values, with values ranging
from a few cents to the billions, as well as a few negative numbers.
We were running autovacuum but it interfered with the updates to we shut it
off.
Was it just the I/O bandwidth? I'm surprised as your vacuum_cost_delay is
quite high. Manual vacuum doesn't do anything differently from autovacuum,
neither should interfere directly with updates except by taking away
I/O bandwidth.
I don't know what the problem was. I tried to exclude certain tables
from autovacuuming, but it autovacuumed anyway.
We vacuum this table nightly, and it currently takes about 12 hours to
vacuum it. Not much else is running during this period, nothing that should
affect the table.
Is this time increasing over time? If once a day isn't enough then you may be
accumulating more and more dead space over time. In which case you may be
better off running it during prime time with a large vacuum_cost_delay (like
the 200 you have configured) rather than trying to get to run fast enough to
fit in the off-peak period.
deadlock_timeout = 10000
I would not suggest having this quite this high. Raising it from the default
is fine but having a value larger than your patience is likely to give you the
false impression that something is hung if you should ever get a deadlock.
Good point. I'll look into this.
Thanks
Ron
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
