I would like to set autovacuum parameters for a specified table.
My problem is that AUTOVACUUM ANALYSE on a small table takes way to long to complete.
This table has only 2 columns (including the primary key) and 150 rows but it is frequently updated. Each row gets updated every minute after the client process has started.
I've found out that the autovacuum process on this table is taking up to 20 seconds.
From what I've understood so far, this is because the autovacuum process is not executed as frequently as it should.
Following are the parameters set on the server configuration :
autovacuum_analyse_threshold => 50
autovacuum_analyse_scale_factor => 0.1
What parameters do you suggest I set specifically on this table ?
Thanks in advance.
UPDATE 1 : In production, the autovacuum analyse process runs on this table every 2 minutes and it take more than 10s.
UPDATE 2 : Table size is 15998 pages.
UDPATE 3 : I have 99 users (client connections) updating the table every 15 seconds.
Best Answer
Since your table has 15998 pages but only 150 rows, it must be quite bloated. A one time
VACUUM FULLorCLUSTERshould fix that. Then the question would be whether it will just get bloated again.In order to make it more aggressive, you would probably need to lower autovacuum_naptime (unless you have already done so) because it is already being vacuumed very often.
10 seconds is about how long I would expect it to take to analyze a table of that size under default autovac io throttling settings. But in that case, it is hard to believe that it is consuming a meaningful amount of CPU time. Have you measured the CPU usage?
What is the lock interfering with? If you are routinely running commands that conflict with an ANALYZE, that could explain why the table got so bloated in the first place.