32

I manage a big (some hundreds of gigs) database containing tables with various roles, some of them holding millions of records. Some tables only receive large number of inserts and deletes, some other few inserts and large number of updates.

Database runs on PostgreSQL 8.4 on a Debian 6.0 amd64 system with 16 gigabytes of RAM.

The question is sometimes autovacuum process on a table, takes a very long time (days) to complete. I want to be able to roughly tell how much time a particular vacuum command will take, to be able to decide whether to cancel it or not. Also if there were a progress indicator for postgres vacuum operations, it would be really helpful.

Edit:

I'm not looking for a bullet-proof solution. Just a rough hint on the number of dead tuples or necessary I/O bytes is enough to decide. It is really annoying to have no clue when VACUUM will finish, whatsoever.

I've seen that pg_catalog.pg_stat_all_tables has a column for number of dead tuples. So it is possible to have an estimation, even if it means one has to ANALYZE the table before. On the other hand, autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor settings alone prove that postgres itself knows something about the amount of change on the tables and probably puts it in the hands of the DBA too.

I'm not sure what query to run, because when I run VACUUM VERBOSE, I see that not only tables, but indexes on them are being processed too.

zaadeh
  • 465
  • 1
  • 5
  • 9

4 Answers4

53

On my PostgreSQL (8.3) I use this trick:

  1. I get table's disk size using pg_total_relation_size() - this includes indexes and TOAST size, which is what VACUUM processes. This gives me the idea of how many bytes the VACUUM has to read.
  2. I run VACUUM on the table.
  3. I find the pid of the VACUUM process (in pg_catalog.pg_stat_activity).
  4. In Linux shell I run while true; do cat /proc/123/io | grep read_bytes; sleep 60; done (where 123 is the pid) - this shows me bytes read by the process from the disk so far.

This gives me rough idea on how many bytes are processed (read) every minute by the VACUUM. I presume that the VACUUM must read through the whole table (including indexes and TOAST), whose disk size I know from the step 1.

I presume that the table is large enough so that the majority of it's pages must be read from disk (they are not present in Postgres shared memory), so the read_bytes field is good enough to be used as a progress counter.

Everytime I did this, the total bytes read by the process was no more than 5% from the total relation size, so I guess this approach may be good enough for You.

Roman Hocke
  • 646
  • 6
  • 5
  • 2
    Nasty :) Does this work for later versions, too? And, more importantly, for autovacuum? – András Váczi Mar 06 '17 at 07:11
  • I haven't tried it for newer versions. It should work for VACUUM FULL on 9.0+, as it completely rewrites the table. It should work for regular VACUUM, too, but I haven't tested it yet. For autovacuum it would work if You were able to catch the autovacuum worker process on given table, but I don't know how to achieve this. – Roman Hocke Mar 06 '17 at 10:05
  • Do you have any suggestions for how to achieve this with RDS? Naturally we don't have access to a linux shell when using RDS, but we would very much like to be able to estimate this as well. – jwg2s Aug 27 '18 at 17:13
  • @jwg2s What do You mean by "RDS", please? Amazon's database services? If so, I am unfortunately not familiar with it :-( Maybe their support would help. – Roman Hocke Aug 28 '18 at 09:46
  • 2
    Seems to work well on PG 10 with vacuum full as well. – DylanYoung Oct 01 '19 at 17:04
  • 2
    I've tried to use this technique on Postgres 9.5 to time-estimate my VACCUM ANALYSE VERBOSE bigtable, which has been running for 5.5 hours now. What I see in pg_total_relation_size() is 718GB for bigtable, but while true; do cat /proc/123/io | grep read_bytes; sleep 60; on the VACCUM pid shows 2256301645824 bytes read so far (over 2TB!). What am I missing? How do I estimate the remaining time for this long-running VACCUM? – 100grams Feb 20 '20 at 10:33
  • 6
    Since PostgreSQL 9.6 you can vacuum progress info with the pg_stat_progress_vacuum table – pdw Aug 14 '20 at 09:40
  • 2
    To get it as a percentage you can run while true; do echo "$(sudo cat /proc/1234/io | grep read_bytes | tr -dc '0-9') * 100 / 50000000" | bc; sleep 30; done where 50000000 is the amount of bytes form SELECT pg_total_relation_size('table_name'); – adriaan Oct 06 '20 at 21:46
  • @100grams Maybe your process didn't start with zero. The same process may serve more requests in sequence. – Roman Hocke Aug 05 '21 at 07:54
  • In PG 12+ you can run select * from pg_stat_progress_cluster to look up the progress of VACUUM FULL. Source: https://severalnines.com/database-blog/progress-reporting-enhancements-postgresql-12 and https://www.postgresql.org/docs/current/progress-reporting.html – Gili Jun 03 '22 at 03:21
13

I found this post and this post helpful, but like others have mentioned, it can be difficult to calculate the overall progress of vacuum, since the process involves a few separate operations.

I use this query to monitor the progress of vacuum's table scanning, which seems to be the bulk of the work:

SELECT heap_blks_scanned/cast(heap_blks_total as numeric)*100 as heap_blks_percent, progress.*, activity.query
FROM pg_stat_progress_vacuum AS progress
INNER JOIN pg_stat_activity AS activity ON activity.pid = progress.pid;

However, this won't include the index scanning, which happens afterwards, and can take just as long, if not longer, if you have a ton of indexes. Unfortunately, I can find no way to monitor index scanning/vacuuming.

Cerin
  • 1,355
  • 5
  • 24
  • 36
10

This is very hard to determine. You can tune autovacuuming to be more agressive or to be milder. But when set to mild and it is lagging behind and the base I/O load is too high, it can happen that it never reaches a proper vacuumed state - then you see the process running and running and running. Furthermore, later PostreSQL editions have much improved autovacuum capabilities, this alone may be enough to move to one of them (preferably 9.2 as the most recent one).

The progress bar sounds a good idea but I imagine it is not that easy to implement meaningfully. As you have constant load on your tables it is quite possible that the progress is apparently going backwards (I mean that the dead row count/percentage increases instead of decreasing) - then what conclusion do you draw?

Community
  • 1
András Váczi
  • 31,278
  • 13
  • 101
  • 147
  • 2
    I prefer to see some sort of progress indicator, even if it goes backward, rather than nothing. – zaadeh Jun 18 '13 at 09:18
  • 3
    VACUUM ANALYZE VERBOSE at least prints some activity to the console as it does it's thing. It's better then just staring at a static prompt wondering if something is stuck for hours. – Fake Name Nov 04 '15 at 03:57
  • The question asks about "vacuum/autovacuum". The above is only useful for VACUUM, not autovacuum, but it's still something. – Fake Name Nov 04 '15 at 17:38
  • @FakeName Eh, I misread the question - missed the manual vacuum part. Sorry, Iám deleting my comment. – András Váczi Nov 04 '15 at 23:34
5

In our production one of the biggest tables had this log:

pages: 0 removed, 1801722 remain
tuples: 238912 removed, 42582083 remain, 1396 are dead but not yet removable
buffer usage: 9477565 hits, 3834218 misses, 2220101 dirtied
avg read rate: 2.976 MB/s, avg write rate: 1.723 MB/s
system usage: CPU 68.47s/177.49u sec elapsed 10065.08 sec

This is by far the worst resource consumption, all other tables have taken less than 2 s.

To see these types of logs you should execute this:

alter system set log_autovacuum_min_duration TO 5;

(for 5 ms), reload the configuration file.

Andriy M
  • 22,983
  • 6
  • 59
  • 103
stonelazy
  • 151
  • 1
  • 1