Thread
-
Maybe problems with autovaccum?
Franz Philipp Moser <fpmedv@gmail.com> — 2025-09-22T12:06:43Z
Hello, VM with /usr/libexec/qemu-kvm Linux HOSTNAME 5.14.0-570.37.1.el9_6.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Aug 26 10:33:12 EDT 2025 x86_64 x86_64 x86_64 GNU/Linux CPU: processor : 39 vendor_id : GenuineIntel cpu family : 6 model : 85 model name : Intel Xeon Processor (Skylake, IBRS) stepping : 4 microcode : 0x1 cpu MHz : 3092.734 cache size : 4096 KB postgres=# SELECT version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit (1 row) # cat /etc/os-release NAME="AlmaLinux" VERSION="9.6 (Sage Margay)" ID="almalinux" ID_LIKE="rhel centos fedora" VERSION_ID="9.6" PLATFORM_ID="platform:el9" PRETTY_NAME="AlmaLinux 9.6 (Sage Margay)" ANSI_COLOR="0;34" LOGO="fedora-logo-icon" CPE_NAME="cpe:/o:almalinux:almalinux:9::baseos" HOME_URL="https://almalinux.org/" DOCUMENTATION_URL="https://wiki.almalinux.org/" BUG_REPORT_URL="https://bugs.almalinux.org/" ALMALINUX_MANTISBT_PROJECT="AlmaLinux-9" ALMALINUX_MANTISBT_PROJECT_VERSION="9.6" REDHAT_SUPPORT_PRODUCT="AlmaLinux" REDHAT_SUPPORT_PRODUCT_VERSION="9.6" SUPPORT_END=2032-06-01 Postgresql up for ~22 days (enabled over systemctl, so VM runtime normaly matches postgresql runtime) # w 13:42:00 up 22 days, 4:06, 1 user, load average: 4.53, 5.59, 5.56 We have a table that has a high frequency on insert, update and delete statements: SELECT relname AS table_name, schemaname AS schema_name, coalesce(seq_scan, 0) + coalesce(idx_scan, 0) AS total_selects, n_tup_ins AS inserts, n_tup_upd AS updates, n_tup_del AS deletes, vacuum_count, analyze_count, autovacuum_count, autoanalyze_count FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER by (coalesce(seq_scan,0) + coalesce(idx_scan,0) + n_tup_ins + n_tup_upd + n_tup_del) DESC; table_name | schema_name | total_selects | inserts | updates | deletes | vacuum_count | analyze_count | autovacuum_count | autoanalyze_count --------------------------+-------------+---------------+-----------+-----------+-----------+--------------+---------------+------------------+------------------- ... TABLE1 | public | 78878577 | 105398387 | 459085 | 105398208 | 3 | 3 | 38719 | 25583 ... our autovacume have near the standard settings, except: log_autovacuum_min_duration 0 # log all autovacuum autovacuum_vacuum_scale_factor 0.002 autovacuum_analyze_scale_factor 0.001 The TABLE1 has a maximum of 10000 entrys. Seen with count(1) over the day. This causes the autovacuum to run 73 times per our (38719 / 22 / 24 = 73,3....) * Thats interesting because we left the autovacuum_naptime to the default of 1 min. Why does it vaccum more than once per minute? Second thing was very strage. Inserts on TABLE1 where stalled for a long time, our application was stalled to. In the postgressql log I see the following: 2025-09-20 13:51:34.760 GMT team fsc 10.4.82.131(53190) 68bf3cb2.2180caWARNING: there is no transaction in progress 2025-09-20 14:00:04.782 GMT 68ceb157.1562c1ERROR: canceling autovacuum task 2025-09-20 14:00:04.782 GMT 68ceb157.1562c1CONTEXT: while truncating relation "public.TABLE1" to 235 blocks automatic vacuum of table "DATABASENAME.public.TABLE1" * And exact on 14:00:04.784 all INSERTs went through. How can I analyse that, or is there a known bug regarding autovacuum? Unfortunable I can not reproduce it and it happend for the first time after an upgrade with pg_upgrade from postgresql16-16.9-3PGDG.rhel9.x86_64 to postgresql17-17.6-1PGDG.rhel9.x86_64 => 3 weeks ago. I hope you can help us and we are looking forward to your answers, regards Philipp