>> I'd like to phone in with a slightly different opinion on VACUUM FULL.
Yeah,
>> it should be avoided when possible, but it's not always possible. In our
>> case, I've got 300ish databases backing to a single database server. Each
of
>> those dbs has a couple of hundred tables and a hundred or more views. The
>> product (Journyx Timesheet) is pretty complex, and I find that if I do
_not_
>> perform a full vacuum once per week, my customer dbs start to slow down
>> inordinately. Queries which would run in 1-2 seconds will run in 30-40
>> seconds after a few weeks of not performing a full vacuum.
>Wait, full vacuum on the whole db, or vacuum full?
Vac full analyze on each and every database weekly. Sepcifically:
PGCMD = 'vacuumdb -a -f -v -z'
>> I've got autovac
>> running on all dbs.
>>
>> Now, that could well be due to index bloat with complex indexes, or it
could
>> be due to a variety of other factors, but also my pg_clog directory does
not
>> clear out, but continues to create new clog segments. Running my weekly
>> vac-full-analyze resolves that problem for me. This might not be the case
>> for you if you have a less complex schema, especially noting how you say
you
>> use it.
>You likely have very long running transactions. Look for idle in
>transaction queries in the pg_stat_activity table.
>It may be that right now vacuum full is the only fix but if you can
>identify a reason regular vacuum isn't working you could eliminate the
>need for vacuum full.
I suspect it has to do with our architecture. Each application (out of the
box) has 2 daemons constantly connected to the pg backend, or there are more
if configured. My _guess_ would be that since the database is detected as
"in use," autovac isn't fully clearing. I could be wrong. Some of my
backend_starts go back to February of this year (last time I restarted the
server, as I recall). I would _think_ that a vac full would have the same
issues, but maybe not. In the case of the vac full, I'm not stopping and
restarting the app servers, so it should be the same as an autovac, in
theory.
In my pg_stat_activity, all I see is constant "command string not enabled."
stats_command_string is commented out in my conf (default is off). What's
the performance implications, if any, of turning that on?