Обсуждение: autovacuum

Поиск
Список
Период
Сортировка

autovacuum

От
"Sriram Dandapani"
Дата:

Hi

 

I have a table about 17G in size. I did a pg_relation_size before deleting 2 millions rows. I waited a few hours(actually overnight) and then checked the table size with pg_relation_size. The size reported was exactly the same.

 

My autovacuum is set to run every 50 minutes.

 

Settings:

 

autovacuum = on                 # enable autovacuum subprocess?

autovacuum_naptime = 3000               # time between autovacuum runs, in secs

autovacuum_vacuum_threshold = 10000     # min # of tuple updates before

                                        # vacuum

autovacuum_analyze_threshold = 1500     # min # of tuple updates before

                                        # analyze

autovacuum_vacuum_scale_factor = 0.4    # fraction of rel size before

                                        # vacuum

autovacuum_analyze_scale_factor = 0.2   # fraction of rel size before

                                        # analyze

autovacuum_vacuum_cost_delay = -1

 

vacuum_cost_delay = 200                 # 0-1000 milliseconds

#vacuum_cost_page_hit = 1               # 0-10000 credits

#vacuum_cost_page_miss = 10             # 0-10000 credits

#vacuum_cost_page_dirty = 20            # 0-10000 credits

vacuum_cost_limit = 50

 

 

work_mem = 802400                       # min 64, size in KB

maintenance_work_mem = 102400           # min 1024, size in KB

#max_stack_depth = 2048                 # min 100, size in KB

 

# - Free Space Map -

max_fsm_pages = 2000000

 

 

Why doesn’t pg_relation_size take the autovacuum maintenance into effect

Re: autovacuum

От
"Chris Hoover"
Дата:
Autovacuum does not release space back to the OS.  It only marks the tuples avaiable for reuse.  To give the space back to the OS, you would have to run a vacuum full on the table.

Chris

On 6/1/06, Sriram Dandapani <sdandapani@counterpane.com> wrote:

Hi

 

I have a table about 17G in size. I did a pg_relation_size before deleting 2 millions rows. I waited a few hours(actually overnight) and then checked the table size with pg_relation_size. The size reported was exactly the same.

 

My autovacuum is set to run every 50 minutes.

 

Settings:

 

autovacuum = on                 # enable autovacuum subprocess?

autovacuum_naptime = 3000               # time between autovacuum runs, in secs

autovacuum_vacuum_threshold = 10000     # min # of tuple updates before

                                        # vacuum

autovacuum_analyze_threshold = 1500     # min # of tuple updates before

                                        # analyze

autovacuum_vacuum_scale_factor = 0.4    # fraction of rel size before

                                        # vacuum

autovacuum_analyze_scale_factor = 0.2   # fraction of rel size before

                                        # analyze

autovacuum_vacuum_cost_delay = -1

 

vacuum_cost_delay = 200                 # 0-1000 milliseconds

#vacuum_cost_page_hit = 1               # 0-10000 credits

#vacuum_cost_page_miss = 10             # 0-10000 credits

#vacuum_cost_page_dirty = 20            # 0-10000 credits

vacuum_cost_limit = 50

 

 

work_mem = 802400                       # min 64, size in KB

maintenance_work_mem = 102400           # min 1024, size in KB

#max_stack_depth = 2048                 # min 100, size in KB

 

# - Free Space Map -

max_fsm_pages = 2000000

 

 

Why doesn't pg_relation_size take the autovacuum maintenance into effect