Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YyYlL-0002iW-BF for pgsql-admin@arkaria.postgresql.org; Sat, 30 May 2015 04:52:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YyYlJ-0004v3-Cw for pgsql-admin@arkaria.postgresql.org; Sat, 30 May 2015 04:52:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YyYlI-0004ut-5G for pgsql-admin@postgresql.org; Sat, 30 May 2015 04:52:44 +0000 Received: from biglist.com ([207.99.53.219]) by magus.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1YyYlD-0005Mq-O3 for pgsql-admin@postgresql.org; Sat, 30 May 2015 04:52:43 +0000 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=biglist.com; s=bravo-dkim; t=1432961559; bh=GcGOccu6BIRNFJzsIMY2LrRp/KI=; h=Received:Received:Content-Type:Mime-Version:Subject:From: In-Reply-To:Date:Cc:Content-Transfer-Encoding:Message-Id: References:To:X-Mailer; b=PMGxc5UWiSD3BuNSXUrWeNevyf2f9OlJOr2FrDm+ciiy03wxbUJzd05wgGADz6J5i 1xMa+5N0PoWtm0+KBjaNIYYa2MEEj3s9x2HKLGYKYkmzX2yd4Kv4Jtckz6PEOiiMWs QvAaMZh4K0MrUOiPz2TDNHmxIMLtrzE4yk5YXUcA= Received: (qmail 37750 invoked from network); 30 May 2015 04:52:36 -0000 Received: from foon.sporktines.com (HELO fruitcake.sporklab.com) (spork@96.57.144.66) by biglist.com with ESMTPA; 30 May 2015 04:52:36 -0000 Content-Type: text/plain; charset=windows-1252 Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\)) Subject: Re: autovacuum was not vacuuming From: Charles Sprickman In-Reply-To: <20150527180006.GD5885@postgresql.org> Date: Sat, 30 May 2015 00:52:32 -0400 Cc: Jorge Torralba , pgsql-admin@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <95185366-B2AE-4CC0-88A4-1F4F8312D7B1@biglist.com> References: <1E6196B0-6B63-4BC9-8E14-DF54AC2A1F34@biglist.com> <20150527180006.GD5885@postgresql.org> To: Alvaro Herrera X-Mailer: Apple Mail (2.1878.6) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org On May 27, 2015, at 2:00 PM, Alvaro Herrera wrot= e: > Charles Sprickman wrote: >> On May 27, 2015, at 1:37 PM, Jorge Torralba w= rote: >>=20 >>> run this for validation >>>=20 >>> select relname, last_autovacuum from pg_stat_user_tables order by last_= autovacuum; >>=20 >> Hi - thanks, I=92d run some similar query before to see what wasn=92t be= ing vacuumed. >>=20 >> While some tables seem to be now, here=92s an example of an active db th= at=92s still got some blanks in the last_autovacuum column: >=20 > Does your system crash frequently? autovacuum would not process files > unless they appear to cross the threshold values; and the statistics are > reset whenever there's a crash recovery.=20=20 No frequent crashes. Not any that I know of even since moving to this server (up 1046 days). So thats one thing to rule out. I also know that no one has manually reset any of the stats counters. > Perhaps those tables don't > have enough recent activity. I see your oldest autovac dates are > 2014-07 so perhaps you had crashes sometime before that. This is starting to make sense. On the same database, this data is kind of interesting: relname | n_tup_ins | n_tup_upd | n_tup_del | last_auto= vacuum | autovacuum_count ---------------------+-----------+-----------+-----------+-----------------= --------------+------------------ optin | 7252274 | 0 | 1975631 | 2014-07-21 06:31= :47.66319-04 | 3 log | 43045279 | 0 | 31641637 | 2014-10-18 04:55= :22.227628-04 | 8 complaint | 3002663 | 6154 | 3002973 | 2014-10-18 10:43= :18.224247-04 | 31 arch_subscriber | 8472659 | 8184029 | 96798 | 2014-11-19 08:27= :32.895688-05 | 2 subscriber | 13625052 | 28592779 | 8473154 | 2014-12-05 10:12= :48.340305-05 | 14 transactions | 2514676 | 5182 | 257117 | 2015-02-18 12:47= :42.406487-05 | 3 post | 32521 | 50722 | 1557 | 2015-05-23 01:46= :47.039179-04 | 1 post_actions | 98395891 | 43836 | 44881522 | 2015-05-26 10:03= :43.259077-04 | 3 bounce | 631024 | 81 | 595087 | 2015-05-26 10:29= :34.888937-04 | 13 bounce_reason | 7961281 | 32736 | 7953428 | 2015-05-26 10:29= :34.918845-04 | 6 list_sub | 14562995 | 69515258 | 9009496 | 2015-05-28 15:58= :13.754569-04 | 30 post_job | 8127 | 15908 | 8070 | 2015-05-28 21:52= :54.923614-04 | 263 list_data | 7350 | 756705 | 6063 | 2015-05-29 12:34= :42.323142-04 | 425 post_stats | 90155 | 40750025 | 108 | 2015-05-29 13:14= :43.307775-04 | 569 limit_tracking | 30248 | 12689580 | 30192 | 2015-05-29 13:54= :42.06234-04 | 50780 email | 0 | 0 | 0 | = | 0 sending_rule_fields | 0 | 0 | 0 | = | 0 sending_rule_files | 0 | 0 | 0 | = | 0 post_threads | 0 | 0 | 0 | = | 0 forwards | 0 | 0 | 0 | = | 0 list | 9 | 28 | 7 | = | 0 sub_active | 840019 | 0 | 0 | = | 0 subscriber_db_field | 0 | 0 | 0 | = | 0 slice_fields | 0 | 0 | 0 | = | 0 sending_rule_esps | 0 | 0 | 0 | = | 0 client | 1 | 0 | 0 | = | 0 slices | 0 | 0 | 0 | = | 0 fields | 11 | 0 | 0 | = | 0 arch_list_sub | 8909636 | 0 | 101140 | = | 0 sending_rules | 0 | 0 | 0 | = | 0 subscriber_db_query | 0 | 0 | 0 | = | 0 engagement | 0 | 0 | 0 | = | 0 reject_rule | 608 | 0 | 0 | = | 0 list_criterion | 0 | 0 | 0 | = | 0 repltest | 0 | 0 | 0 | = | 0 post_attachment | 1 | 0 | 0 | = | 0 filters | 0 | 0 | 0 | = | 0 messages | 0 | 0 | 0 | = | 0 post_domain | 262398 | 1177 | 290 | = | 0 track_url | 786 | 1394 | 1 | = | 0 subscriber_data | 0 | 0 | 0 | = | 0 message_attachments | 0 | 0 | 0 | = | 0 migrations | 11 | 0 | 0 | = | 0 subscriptions | 8606373 | 463446 | 0 | = | 0 mailer | 0 | 0 | 0 | = | 0 subscriber_db_file | 0 | 0 | 0 | = | 0 track_hit | 29733544 | 86038 | 6383080 | = | 0 engagement_rules | 0 | 0 | 0 | = | 0 (48 rows) Of all the tables that have NOT been autovacuumed, only a handful have any real write activity. And on those I cant really get any data as to WHEN the last activity was (unless I reset stats and wait) although looking a bit closer I see some timestamped columns and none of these tables have been touched in years. Good, I think. Should I be worried about limit_tracking which has been vacuumed about 50,000 times in the last few days? :) That seems a little extreme. Now if we assume the tables that werent vacuumed are not seeing any new activity, my next problem is how to satisfy my nagios check_postgres checks that look for tables that have not been vacuumed in X days. > How many workers are currently processing tables? (See > pg_stat_activity) The more workers there are, the slower they become. By workers, I assume you just mean pg processes? It varies, but anywhere between a few dozen and a hundred. > Perhaps they are all busy processing large tables and they never finish > because of the vacuum_delay. On the other hand, naptime=3D40min means > that from one worker start to the next one there's a 40min wait, which > seems a bit too long. Normally the default of 1min is appropriate; why > did you change that? I think because I dont fully grasp how all these variables interact. IIRC, when I set this it had something to do with compensating for the number of databases and the number of workers. Im looking at pgbadger=92s analysis of vacuums and I think Im mostly OK with whats happening. Im still stumped on why changes from defaults were necessary to get autovacuum to kick in though Thanks, Charles >=20 > --=20 > =C1lvaro Herrera http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >=20 >=20 > --=20 > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin