1

In my system I have more than 10k tables that are very similar, I only do insert operations in these tables and delete old data via a cron schedule one time per day.

These insertions come each minute, a mean of 5 rows per table which are inserted in a single insert query, meaning something around 50k rows per minute.

The issue I'm seeing is that stats collector (I disabled all stats except track_counts) uses a lot of CPU and IO all the time.

The same happens to autovacuum which will also use a lot of the PC resources for itself.

Another issue is that they never seem to stop, autovacuum and stats collector are always running as long as I'm always adding new data in the rate shown above.

If I disable track_counts and autovacuum, it fixes my issue entirelly, the database is kept idle most of the time.

The problem with this approach is that I obviously are not running vacuum anymore.

So, my question is, would it be a good solution for my case to disable autovacuum entirelly and run VACUUM FULL after my cron script deleted old data? This would mean that I would trigger a full vacuum query for each table one time per day.

If not, do you have some sugestions for postgres configuration changes to improve the situation without disabling autovacuum?

This is part of my postgresql.conf (feel free to ask for more information about it if needed):

track_activities = off
track_counts = on
track_io_timing = off
track_functions = none

log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------

autovacuum = on

log_autovacuum_min_duration = -1

autovacuum_max_workers = 6
autovacuum_naptime = 3600

I already saw some answers for cases of append-only tables or big tables, but I think I'm seeing this issue because in my case I have a LOT of tables.

Also, for completeness, I'm running the database in Postgres 12 and with extension TimescaleDB 1.7.1 inside docker.

1
  • Are all 10k+ tables in one database, or spread over multiple? Did you try putting stats_temp_directory onto a ram disk, like /dev/shm?
    – jjanes
    Aug 7 '20 at 0:41
1

Disabling autovacuum is a very bad idea. Autovacuum removes dead lines and updates statistics. Disabling autovacuum will quickly lead to performance loss. Also, when autovacuum is disabled, sooner or later you will encounter a wraparound problem.

Since you are using TimescaleDB, why not use the drop_chunks() function to remove old data? This will significantly reduce the need to perform vacuum on tables. However, autovacuum will still need to handle tables to prevent the wraparound I wrote about above. In addition, you can change the size of the chunk so that the size of the data to be processed by autovacuum is not so large.

There are no universal values ​​for the autovacuum setting. Try adjusting the following settings for the best performance in your case:

autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
autovacuum_vacuum_cost_delay

For statistics:

autovacuum_analyze_threshold
autovacuum_analyze_scale_factor

You can also configure cost parameters for the autovacuum daemon to process pages:

vacuum_cost_page_hit
vacuum_cost_page_miss
vacuum_cost_page_dirty
vacuum_cost_limit
1

How many more than 10k tables do you have? There is a lot of room between 10k and infinity.

If I disable track_counts and autovacuum, it fixes my issue entirelly,

Does it also solve the problem if you only disable autovacuum and leave track_counts on?

So, my question is, would it be a good solution for my case to disable autovacuum entirelly and run VACUUM FULL after my cron script deleted old data?

Why VACUUM FULL rather than just regular VACUUM ANALYZE? VACUUM FULL seems overboard. I'd rather get to the bottom of what is fundamentally going on, but doing it this way could also work. As long as the job doesn't break without being noticed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.