Thread: Auto Vacuum question?
i set the auto vacuum option to enable. but my database size(hard- disk space) still increased from 420MB to 440MB in 8 hours. most of the operations in this database are the "Select" query command, just few of "Update or Insert". why it can be increased so strongly? after i executed the command " vaccumdb -f -z testdb", but the size just decreased 1 or 2MB... what's the problem? how to know which command(select,insert....) takes the hurge loading to database in this time? computer environment: System: Red Hat Enterprise 4 Postgresql Server: 8.0.13 count of client connected : about 100 pc
Charles.Hou wrote: > i set the auto vacuum option to enable. but my database size(hard- > disk > space) still increased from 420MB to 440MB in 8 hours. most of the > operations in this database are the "Select" query command, just few > of "Update or Insert". > why it can be increased so strongly? > after i executed the command " vaccumdb -f -z testdb", but the size > just decreased 1 or 2MB... > what's the problem? It is normal that the tables grow a bit to a stationary state of dead space (generated by UPDATE and DELETE). However it is not normal if it loses track of that dead space. One thing you can and should do is adjust the autovacuum settings (for example lowering the vacuum scale factor and threshold values). Another is to correctly set the FSM values. Have you done both? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 6 4 , 10 49 , alvhe...@commandprompt.com (Alvaro Herrera) wrote: > Charles.Hou wrote: > > i set the auto vacuum option to enable. but my database size(hard- > > disk > > space) still increased from 420MB to 440MB in 8 hours. most of the > > operations in this database are the "Select" query command, just few > > of "Update or Insert". > > why it can be increased so strongly? > > after i executed the command " vaccumdb -f -z testdb", but the size > > just decreased 1 or 2MB... > > what's the problem? > > It is normal that the tables grow a bit to a stationary state of dead > space (generated by UPDATE and DELETE). However it is not normal if it > loses track of that dead space. One thing you can and should do is > adjust the autovacuum settings (for example lowering the vacuum scale > factor and threshold values). Another is to correctly set the FSM > values. > > Have you done both? > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org it's my postgresql.conf autovacuum = on # enable autovacuum subprocess? autovacuum_naptime = 20 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 1 # min # of tuple updates before autovacuum_analyze_threshold = 1 # min # of tuple updates before autovacuum_vacuum_scale_factor = 1 # fraction of rel size before autovacuum_analyze_scale_factor = 1 # fraction of rel size before #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
Charles.Hou wrote: > On 6 4 , 10 49 , alvhe...@commandprompt.com (Alvaro Herrera) wrote: > > It is normal that the tables grow a bit to a stationary state of dead > > space (generated by UPDATE and DELETE). However it is not normal if it > > loses track of that dead space. One thing you can and should do is > > adjust the autovacuum settings (for example lowering the vacuum scale > > factor and threshold values). Another is to correctly set the FSM > > values. > autovacuum_vacuum_scale_factor = 1 # fraction of rel size before > autovacuum_analyze_scale_factor = 1 # fraction of rel size before These values are rather strange; they mean it would wait until the table is populated by as many dead tuples as there are live tuples, before vacuuming it. This is probably too much. Try decreasing the vacuum scale factor to 0.2 or maybe 0.1 and see how it goes. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "La fuerza no está en los medios físicos sino que reside en una voluntad indomable" (Gandhi)