Ad Widget

Collapse

postgresql: database grows rapidly

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • harribal
    Junior Member
    • Mar 2022
    • 7

    #1

    postgresql: database grows rapidly

    All,

    I do have a problem with database growing rapidly. I think it is due to auto vacuum not being able to purge table history_uint.
    Background: This is zabbix 6.0.12 and Postgres 13. Housekeeping is set to clear history after 30 days.

    Autovacuum works on at least some other tables:
    Code:
          relname      | n_live_tup | n_dead_tup |    dead_percent     |          last_vacuum          |        last_autovacuum        | autovacuum_count
    -------------------+------------+------------+---------------------+-------------------------------+-------------------------------+------------------
    ...
     history           |  128642188 |      74456 | 0.05787836879764514 | 2023-01-23 11:33:10.218843+02 | 2023-01-21 00:52:28.789278+02 |                1
     history_str       |     773932 |      27738 |   3.584035806763385 |                               | 2023-01-22 07:22:45.220621+02 |                1
     history_text      |    5037827 |      10740 | 0.21318715390584075 |                               | 2023-01-23 08:14:05.414644+02 |                2
     history_uint      |  723448060 |  164961825 |   22.80216564545076 |                               |                               |                0​
    ...
    Manually analysing vacuum produces the following
    Code:
    # vacuum (verbose, analyze) history_uint;
    INFO:  aggressively vacuuming "public.history_uint"
    ERROR:  cannot freeze committed xmax 203701611
    CONTEXT:  while scanning block 420246 of relation "public.history_uint"​
    I guess that this is not expected result.

    What could be done to correct the situation and stop growth of the database?
Working...