I should have been more clear. I virtually never delete or do updates, but I insert *a lot*. So the table does change quite a bit, but only in one direction.
I was unable to disable autovacuum universally (due to the cant_change_runtime_param error) but I was able to disable it on individual tables. Still, I know this is heavy handed and sub-optimal. I tried set autovacuum_naptime='6min' but got the same 55P02 error. Should/can I set that per table? I did look at autovacuum_vacuum_threshold and autovacuum_vacuum_scale_**factor but couldn't make sense out of them. (Besides, I'd probably get the same 55P02 error if I tried to change them.) On Thu, Feb 23, 2012 at 7:18 AM, Andy Colson <a...@squeakycode.net> wrote: > On 2/23/2012 6:34 AM, Thom Brown wrote: > >> On 22 February 2012 23:50, Alessandro Gagliardi<alessan...@path.com> >> wrote: >> >>> I have a database where I virtually never delete and almost never do >>> updates. (The updates might change in the future but for now it's okay to >>> assume they never happen.) As such, it seems like it might be worth it to >>> set autovacuum=off or at least make it so vacuuming hardly ever occurs. >>> Actually, the latter is probably the more robust solution, though I don't >>> know how to do that (hence me writing this list). I did try turning >>> autovacuum off but got: >>> >>> ERROR: parameter "autovacuum" cannot be changed now >>> SQL state: 55P02 >>> >>> Not sure what, if anything, I can do about that. >>> >> >> Autovacuum is controlled by how much of a table has changed, so if a >> table never changes, it never gets vacuumed (with the exceptional case >> being a forced vacuum freeze to mitigate the transaction id >> wrap-around issue). The settings which control this are >> autovacuum_vacuum_threshold and autovacuum_vacuum_scale_**factor. >> Therefore it isn't necessary to disable autovacuum. >> >> But if you are adamant about disabling it, you need to change it in >> your postgresql.conf file and restart the server. >> >> > Agreed, don't disable autovacuum. It's not that demanding, and if you do > need it and forget to run it, it might cause you more problems. > > I have a db that's on a VM that doesnt get hit very much. I've noticed IO > is a little busy (we are talking small percents of percents less than one) > but still more that I thought should be happening on a db with next to no > usage. > > I found setting autovacuum_naptime = 6min made the IO all but vanish. > > And if I ever get a wild hair and blow some stuff away, the db will clean > up after me. > > -Andy >
