I think you understand correctly. A table with 1,000,000 rows should get vacuumed approx every 2,000,000 changes (assuming default values for -V ). FYI and insert and a delete count as one change, but and update counts as two.
Unfortunately, the running with -d2 would show the numbers that pg_autovacuum is using to decide if it when it should vacuum or analyze. Also, are you sure that it vacuumed more than once and wasn't doing analyzes most of the time?
Yeah, I'm sure. Snippets from the log:
[...lots-o-tables...]
[2004-03-14 12:44:48 PM] added table: specdb."public"."parametric_states"
[2004-03-14 12:49:48 PM] Performing: VACUUM ANALYZE "public"."transaction_data"
[2004-03-14 01:29:59 PM] Performing: VACUUM ANALYZE "public"."transaction_data"
[2004-03-14 02:08:26 PM] Performing: ANALYZE "public"."out_of_spec"
[2004-03-14 02:08:26 PM] Performing: VACUUM ANALYZE "public"."transaction_data"
[2004-03-14 02:22:44 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
[2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."out_of_spec"
[2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."transaction_data"
[2004-03-14 03:19:51 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
[2004-03-14 03:21:09 PM] Performing: ANALYZE "public"."parametric_states"
[2004-03-14 03:54:57 PM] Performing: ANALYZE "public"."out_of_spec"
[2004-03-14 03:54:57 PM] Performing: VACUUM ANALYZE "public"."transaction_data"
[2004-03-14 04:07:52 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
[2004-03-14 04:09:33 PM] Performing: ANALYZE "public"."equip_status_history"
[2004-03-14 04:09:33 PM] Performing: VACUUM ANALYZE "public"."parametric_states"
[2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."out_of_spec"
[2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."transaction_data"
[2004-03-14 04:56:35 PM] Performing: VACUUM ANALYZE "public"."spc_graphs"
[2004-03-14 04:58:32 PM] Performing: ANALYZE "public"."parametric_states"
[2004-03-14 05:28:58 PM] added database: specdb
This is the entire period of the first test, with default autovac settings. The table "public"."transaction_data" is the one with 28 million active rows. The entire test run inserts about 600 x 600 = 360,000 rows, out of which roughly two-thirds are later deleted.
That's unfortunate as that is the detail we need to see what pg_autovacuum thinks is really going on. We had a similar sounding crash on FreeBSD due to some unitialized variables that were being printed out by the debug code, however that was fixed a long time ago. Any chance you can look into this?
I can try. The server belongs to another department, and they are under the gun to get back on track with their testing. Also, they compiled without debug symbols, so I need to get permission to recompile.
Yes I would be very curious to see the results with the vacuum delay patch installed (is that patch applied to HEAD?)
Any idea where I can get my hands on the latest version. I found the original post from Tom, but I thought there was a later version with both number of pages and time to sleep as knobs.
Thanks,
Joe
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
