2

We have a product log database which only produces insert sql。 But we found that The autovacuum process took so much memory。 it takes about 16 GB at peak, And it is happend about every 2 month。The folloing is the detail information, Any one know it?

And the table skytf.urs_user_log_201105 only has insert operation, and have no update,delete operations, So i think the table has no dead tuples! But why the autovacuum process takes so much memory on the table?

--top detail 
top - 16:39:46 up 225 days,  1:12,  1 user,  load average: 1.29, 1.51, 1.52
Tasks: 341 total,   2 running, 339 sleeping,   0 stopped,   0 zombie
Cpu(s):  4.8%us,  5.3%sy,  0.0%ni, 85.5%id,  4.1%wa,  0.0%hi,  0.4%si,  0.0%st

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                
29267 postgres  14  -1 27.0g  16g 2928 S  1.0 72.2 199:59.74 postgres: autovacuum launcher process

From the above, we can see that the autovacuum process taks about 16 gb;

--current sql
postgres=# select datname, current_query from pg_stat_activity where current_query !='<IDLE>';
 datname  |                                    current_query                                    
----------+-------------------------------------------------------------------------------------
 skytf   | autovacuum: VACUUM skytf.urs_user_log_201105 (to prevent wraparound)




--table size
 skytf=> \dt+ urs_user_log_201105
                          List of relations
 Schema |        Name         | Type  | Owner  | Size  | Description 
--------+---------------------+-------+--------+-------+-------------
 skytf | urs_user_log_201105 | table | skytf | 62 GB | 
(1 row)

--memory state
postgres@logdb-> free -m
             total       used       free     shared    buffers     cached
Mem:         24104      24028         75          0          4       5545
-/+ buffers/cache:      18479       5625
Swap:        16386       8824       7561
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
francs
  • 8,511
  • 7
  • 39
  • 43

2 Answers2

4

If you look at this:

autovacuum: VACUUM skytf.urs_user_log_201105 (to prevent wraparound)

this is not a regular autovacuum. it is running to prevent transaction wraparound. this kind of autovacuum must run before transaction id goes beyond two billion. read more at here: http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

you can control the behavior of it with vacuum_freeze_min_age, vacuum_freeze_max_age. you can not disable this kind of autovacuum whether you have disabled autovacuum or not.

Majid Azimi
  • 5,575
  • 13
  • 64
  • 113
1

only has insert operation, and have no update,delete operations, So i think the table has no dead tuples!

If memory serves, autovacuum will actually do two things:

  • vacuum
  • analyze

The first won't kick in if you only ever get inserts. But the second continues to do so when PG thinks the stats might have changed a bit.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • is there any solution to aviod this? – francs Jun 17 '11 at 03:29
  • Not that I'm aware of (besides disabling auto-vacuum). But you might want to ask the pg-performance list, in case there's something extra in the internals which I might be missing. (There also was a thread in the last 2-3 months or so on the exact same topic, btw.) – Denis de Bernardy Jun 17 '11 at 11:20
  • I plan to set the the parameter autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor on big log tables to a low value individually ; So the autovacuum process will be triggered more frequently。 – francs Jun 20 '11 at 07:02
  • I'm not sure if it will does. – francs Jun 20 '11 at 07:03