Question about --routine-vacuum parameter
Closed this issue · 2 comments
gglybin commented
Hello,
I'm still testing your tool to beat some bloat in my database on test lab. There is a critical table in my db and autovacuum is running every ~ 20-30 min. If autovacuum is not running for a long time => queries (customers) to that table experiencing a problems.
From pgcompacttable man:
-R
--routine-vacuum
Turn on the routine vacuum. By default all the vacuums are off.
Based on above statement I conclude that during default pgcompacttable run autovacuum will not be able to run for that table.
If I will pass additional parameter -R this allow autovacuum to be run during pgcompacttable work, right? I can't check it on my test lab, that why I want to get some confirmation that I understood it correctly.
Am I right? If yes - then how you actually "blocking" autovacuum run during pgcompacttable run? I thought you are temporary setting autovacuum_enabled = off, but looks like it's not. Can you share this trick?
Thank you.
gglybin commented
Uhh, looks like I misunderstood this parameter meaning. I've ran pgcompacttable with and without -R option.
Without:
[Wed Jun 22 15:20:22 2022] (bench_db) Connecting to database
[Wed Jun 22 15:20:22 2022] (bench_db) Postgres backend pid: 7580
[Wed Jun 22 15:20:22 2022] (bench_db) Handling tables. Attempt 1
[Wed Jun 22 15:20:22 2022] (bench_db:public.pgbench_accounts) Start handling table public.pgbench_accounts
[Wed Jun 22 15:20:32 2022] (bench_db:public.pgbench_accounts) Vacuum initial: 221948 pages left, duration 9.886 seconds.
[Wed Jun 22 15:20:34 2022] (bench_db:public.pgbench_accounts) Bloat statistics with pgstattuple: duration 1.249 seconds.
[Wed Jun 22 15:20:34 2022] (bench_db:public.pgbench_accounts) Statistics: 221948 pages (277190 pages including toasts and indexes), it is expected that ~48.880% (108469 pages) can be compacted with the estimated space saving being 847.419MB.
[Wed Jun 22 15:20:34 2022] (bench_db:public.pgbench_accounts) Processing forced.
[Wed Jun 22 15:20:34 2022] (bench_db:public.pgbench_accounts) Update by column: bid.
[Wed Jun 22 15:20:34 2022] (bench_db:public.pgbench_accounts) Set pages/round: 5.
[Wed Jun 22 15:20:34 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 4439.
[Wed Jun 22 15:21:34 2022] (bench_db:public.pgbench_accounts) Progress: 31%, 33880 pages completed.
[Wed Jun 22 15:22:34 2022] (bench_db:public.pgbench_accounts) Progress: 61%, 66360 pages completed.
[Wed Jun 22 15:23:34 2022] (bench_db:public.pgbench_accounts) Progress: 91%, 99430 pages completed.
[Wed Jun 22 15:24:20 2022] (bench_db:public.pgbench_accounts) Vacuum final: cannot clean 110511 pages, 221948 pages left, duration 25.849 seconds.
[Wed Jun 22 15:24:20 2022] (bench_db:public.pgbench_accounts) Analyze final: duration 0.196 second.
[Wed Jun 22 15:24:21 2022] (bench_db:public.pgbench_accounts) Bloat statistics with pgstattuple: duration 1.116 seconds.
[Wed Jun 22 15:24:33 2022] (bench_db:public.pgbench_accounts) Reindex forced: public.pgbench_accounts_pkey, initial size 55178 pages(431.078MB), has been reduced by 66% (287.531MB), duration 11 seconds.
[Wed Jun 22 15:24:33 2022] (bench_db:public.pgbench_accounts) Processing incomplete.
[Wed Jun 22 15:24:33 2022] (bench_db:public.pgbench_accounts) Processing results: 221948 pages (240386 pages including toasts and indexes), size has been reduced by -296.000KB (287.219MB including toasts and indexes) in total. This attempt has been initially expected to compact ~48% more space (108246 pages, 845.675MB)
[Wed Jun 22 15:24:33 2022] (bench_db:public.pgbench_accounts) Finish handling table public.pgbench_accounts
[Wed Jun 22 15:24:33 2022] (bench_db:public.pgbench_accounts) Handling tables. Attempt 2
[Wed Jun 22 15:24:33 2022] (bench_db:public.pgbench_accounts) Start handling table public.pgbench_accounts
[Wed Jun 22 15:24:39 2022] (bench_db:public.pgbench_accounts) Vacuum initial: 221948 pages left, duration 6.153 seconds.
[Wed Jun 22 15:24:40 2022] (bench_db:public.pgbench_accounts) Bloat statistics with pgstattuple: duration 1.172 seconds.
[Wed Jun 22 15:24:40 2022] (bench_db:public.pgbench_accounts) Statistics: 221948 pages (240386 pages including toasts and indexes), it is expected that ~48.770% (108256 pages) can be compacted with the estimated space saving being 845.751MB.
[Wed Jun 22 15:24:40 2022] (bench_db:public.pgbench_accounts) Processing forced.
[Wed Jun 22 15:24:40 2022] (bench_db:public.pgbench_accounts) Update by column: bid.
[Wed Jun 22 15:24:40 2022] (bench_db:public.pgbench_accounts) Set pages/round: 5.
[Wed Jun 22 15:24:40 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 4439.
[Wed Jun 22 15:25:40 2022] (bench_db:public.pgbench_accounts) Progress: 83%, 90260 pages completed.
[Wed Jun 22 15:26:01 2022] (bench_db:public.pgbench_accounts) Vacuum final: cannot clean 110331 pages, 221948 pages left, duration 7.839 seconds.
[Wed Jun 22 15:26:01 2022] (bench_db:public.pgbench_accounts) Analyze final: duration 0.147 second.
[Wed Jun 22 15:26:02 2022] (bench_db:public.pgbench_accounts) Bloat statistics with pgstattuple: duration 0.988 seconds.
[Wed Jun 22 15:26:11 2022] (bench_db:public.pgbench_accounts) Reindex forced: public.pgbench_accounts_pkey, initial size 18374 pages(143.547MB), has been reduced by 0% (0.000B), duration 8 seconds.
[Wed Jun 22 15:26:11 2022] (bench_db:public.pgbench_accounts) Processing incomplete.
[Wed Jun 22 15:26:11 2022] (bench_db:public.pgbench_accounts) Processing results: 221948 pages (240386 pages including toasts and indexes), size has been reduced by -296.000KB (287.219MB including toasts and indexes) in total. This attempt has been initially expected to compact ~48% more space (108119 pages, 844.686MB)
[Wed Jun 22 15:26:11 2022] (bench_db:public.pgbench_accounts) Finish handling table public.pgbench_accounts
[Wed Jun 22 15:26:11 2022] (bench_db) Processing incomplete: 1 tables left.
[Wed Jun 22 15:26:11 2022] (bench_db) Processing results: size reduced by -296.000KB (287.219MB including toasts and indexes) in total.
[Wed Jun 22 15:26:11 2022] (bench_db) Disconnecting from database
[Wed Jun 22 15:26:11 2022] Processing incomplete: 1 databases left.
[Wed Jun 22 15:26:11 2022] Processing results: size reduced by -296.000KB (287.219MB including toasts and indexes) in total, -296.000KB (287.219MB) bench_db.
With -R:
[Thu Jun 23 10:37:05 2022] (bench_db) Connecting to database
[Thu Jun 23 10:37:05 2022] (bench_db) Postgres backend pid: 12296
[Thu Jun 23 10:37:05 2022] (bench_db) Handling tables. Attempt 1
[Thu Jun 23 10:37:05 2022] (bench_db:public.pgbench_accounts) Start handling table public.pgbench_accounts
[Thu Jun 23 10:37:06 2022] (bench_db:public.pgbench_accounts) Vacuum initial: 191558 pages left, duration 0.964 seconds.
[Thu Jun 23 10:37:07 2022] (bench_db:public.pgbench_accounts) Bloat statistics with pgstattuple: duration 1.088 seconds.
[Thu Jun 23 10:37:07 2022] (bench_db:public.pgbench_accounts) Statistics: 191558 pages (232085 pages including toasts and indexes), it is expected that ~42.030% (80510 pages) can be compacted with the estimated space saving being 628.986MB.
[Thu Jun 23 10:37:07 2022] (bench_db:public.pgbench_accounts) Processing forced.
[Thu Jun 23 10:37:07 2022] (bench_db:public.pgbench_accounts) Update by column: bid.
[Thu Jun 23 10:37:07 2022] (bench_db:public.pgbench_accounts) Set pages/round: 5.
[Thu Jun 23 10:37:07 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3832.
[Thu Jun 23 10:37:25 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1153.0 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:37:25 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 187723 pages left, duration 0.586 seconds.
[Thu Jun 23 10:37:25 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3755.
[Thu Jun 23 10:37:42 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1166.9 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:37:42 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 183968 pages left, duration 0.587 seconds.
[Thu Jun 23 10:37:42 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3680.
[Thu Jun 23 10:37:59 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1165.0 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:37:59 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 180288 pages left, duration 0.582 seconds.
[Thu Jun 23 10:37:59 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3606.
[Thu Jun 23 10:38:07 2022] (bench_db:public.pgbench_accounts) Progress: 14%, 1855 pages completed.
[Thu Jun 23 10:38:15 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1163.6 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:38:16 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 176678 pages left, duration 0.577 seconds.
[Thu Jun 23 10:38:16 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3534.
[Thu Jun 23 10:38:32 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1164.4 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:38:33 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 173143 pages left, duration 0.576 seconds.
[Thu Jun 23 10:38:33 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3463.
[Thu Jun 23 10:38:49 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1173.6 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:38:49 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 169678 pages left, duration 0.579 seconds.
[Thu Jun 23 10:38:49 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3394.
[Thu Jun 23 10:39:05 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1172.3 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:39:05 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 166283 pages left, duration 0.576 seconds.
[Thu Jun 23 10:39:05 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3326.
[Thu Jun 23 10:39:07 2022] (bench_db:public.pgbench_accounts) Progress: 28%, 445 pages completed.
[Thu Jun 23 10:39:21 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1169.1 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:39:21 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 162953 pages left, duration 0.580 seconds.
[Thu Jun 23 10:39:21 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3260.
[Thu Jun 23 10:39:36 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1173.0 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:39:36 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 159693 pages left, duration 0.575 seconds.
[Thu Jun 23 10:39:36 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3194.
[Thu Jun 23 10:39:51 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1172.7 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:39:51 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 156498 pages left, duration 0.569 seconds.
[Thu Jun 23 10:39:51 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3130.
[Thu Jun 23 10:40:06 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1167.4 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:40:06 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 153368 pages left, duration 0.565 seconds.
[Thu Jun 23 10:40:06 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3068.
[Thu Jun 23 10:40:08 2022] (bench_db:public.pgbench_accounts) Progress: 43%, 285 pages completed.
[Thu Jun 23 10:40:20 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1168.6 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:40:20 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 150298 pages left, duration 0.562 seconds.
[Thu Jun 23 10:40:20 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 3006.
[Thu Jun 23 10:40:34 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1173.4 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:40:35 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 147288 pages left, duration 0.558 seconds.
[Thu Jun 23 10:40:35 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2946.
[Thu Jun 23 10:40:48 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1175.7 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:40:48 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 144338 pages left, duration 0.560 seconds.
[Thu Jun 23 10:40:48 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2887.
[Thu Jun 23 10:41:02 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1163.3 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:41:02 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 141448 pages left, duration 0.570 seconds.
[Thu Jun 23 10:41:02 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2829.
[Thu Jun 23 10:41:08 2022] (bench_db:public.pgbench_accounts) Progress: 57%, 1150 pages completed.
[Thu Jun 23 10:41:15 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1174.6 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:41:16 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 138618 pages left, duration 0.556 seconds.
[Thu Jun 23 10:41:16 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2773.
[Thu Jun 23 10:41:28 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1177.0 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:41:29 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 135843 pages left, duration 0.554 seconds.
[Thu Jun 23 10:41:29 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2717.
[Thu Jun 23 10:41:41 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1155.7 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:41:42 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 133123 pages left, duration 0.555 seconds.
[Thu Jun 23 10:41:42 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2663.
[Thu Jun 23 10:41:54 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1163.9 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:41:54 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 130458 pages left, duration 0.550 seconds.
[Thu Jun 23 10:41:54 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2610.
[Thu Jun 23 10:42:06 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1170.6 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:42:07 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 127848 pages left, duration 0.552 seconds.
[Thu Jun 23 10:42:07 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2557.
[Thu Jun 23 10:42:08 2022] (bench_db:public.pgbench_accounts) Progress: 71%, 115 pages completed.
[Thu Jun 23 10:42:19 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1174.2 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:42:19 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 125288 pages left, duration 0.551 seconds.
[Thu Jun 23 10:42:19 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2506.
[Thu Jun 23 10:42:31 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1169.9 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:42:31 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 122778 pages left, duration 0.551 seconds.
[Thu Jun 23 10:42:31 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2456.
[Thu Jun 23 10:42:42 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1168.9 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:42:43 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 120318 pages left, duration 0.546 seconds.
[Thu Jun 23 10:42:43 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2407.
[Thu Jun 23 10:42:54 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1167.4 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:42:54 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 117908 pages left, duration 0.545 seconds.
[Thu Jun 23 10:42:54 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2359.
[Thu Jun 23 10:43:05 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1161.9 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:43:06 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 115548 pages left, duration 0.545 seconds.
[Thu Jun 23 10:43:06 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2311.
[Thu Jun 23 10:43:08 2022] (bench_db:public.pgbench_accounts) Progress: 86%, 370 pages completed.
[Thu Jun 23 10:43:16 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1174.4 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:43:17 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 113233 pages left, duration 0.537 seconds.
[Thu Jun 23 10:43:17 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2265.
[Thu Jun 23 10:43:27 2022] (bench_db:public.pgbench_accounts) Cleaning in average: 1169.8 pages/second (0.004 seconds per 5 pages).
[Thu Jun 23 10:43:28 2022] (bench_db:public.pgbench_accounts) Vacuum routine: 110968 pages left, duration 0.543 seconds.
[Thu Jun 23 10:43:28 2022] (bench_db:public.pgbench_accounts) Set pages/vacuum: 2220.
[Thu Jun 23 10:43:34 2022] (bench_db:public.pgbench_accounts) Vacuum final: 109837 pages left, duration 1.492 seconds.
[Thu Jun 23 10:43:34 2022] (bench_db:public.pgbench_accounts) Analyze final: duration 0.165 second.
[Thu Jun 23 10:43:35 2022] (bench_db:public.pgbench_accounts) Bloat statistics with pgstattuple: duration 0.659 seconds.
[Thu Jun 23 10:43:42 2022] (bench_db:public.pgbench_accounts) Reindex forced: public.pgbench_accounts_pkey, initial size 40471 pages(316.180MB), has been reduced by 54% (172.633MB), duration 6 seconds.
[Thu Jun 23 10:43:42 2022] (bench_db:public.pgbench_accounts) Processing complete.
[Thu Jun 23 10:43:42 2022] (bench_db:public.pgbench_accounts) Processing results: 109837 pages left (128244 pages including toasts and indexes), size reduced by 702.430MB (875.266MB including toasts and indexes) in total.
[Thu Jun 23 10:43:42 2022] (bench_db:public.pgbench_accounts) Finish handling table public.pgbench_accounts
[Thu Jun 23 10:43:42 2022] (bench_db) Processing complete.
[Thu Jun 23 10:43:42 2022] (bench_db) Processing results: size reduced by 702.430MB (875.266MB including toasts and indexes) in total.
[Thu Jun 23 10:43:42 2022] (bench_db) Disconnecting from database
[Thu Jun 23 10:43:42 2022] Processing complete: 1 retries to process has been done
[Thu Jun 23 10:43:42 2022] Processing results: size reduced by 702.430MB (875.266MB including toasts and indexes) in total, 702.430MB (875.266MB) bench_db.
Closing.
Melkij commented
Hello
No, we do not block autovacuum. By default pgcompacttable run vacuum manually before (specifying --no-initial-vacuum will skip this vacuum) and after processing of the table. If --routine-vacuum specified, few additional manual vacuum will be called during table processing. Usually autovacuum does this job well.
By default all the vacuums are off.
Really wrong description. I'll try to rewrite it.