Re: Autovacuum query - Mailing list pgsql-general
From | Steven Erickson |
---|---|
Subject | Re: Autovacuum query |
Date | |
Msg-id | 73A89A6A88301E40A10F770F0F54FCCCE517FA@CORPEXPROD02.dtn.com Whole thread Raw |
In response to | Re: Autovacuum query (Bill Moran <wmoran@potentialtech.com>) |
List | pgsql-general |
Another option, depending on the nature of the data and deletes, would be to partition the table. I created 7 tables thatinherited from the original, one table for each day of the week. A nightly cron job then runs, leaving alone yesterday'sand today's tables but truncating the other 5. Runs in < 10 msec and vacuum doesn't need to run. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Bill Moran Sent: Thursday, March 26, 2015 6:07 AM To: Mitu Verma Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Autovacuum query On Thu, 26 Mar 2015 03:58:59 +0000 Mitu Verma <mitu.verma@ericsson.com> wrote: > > We have a customer complaining about the time taken by one of the application scripts while deleting older data from thelog tables. > During the deletion, customer reported that he often sees the below error and because of which table size doesn?t reduce. > > ERROR: canceling autovacuum task > Date: 2015-03-14 04:29:19 > Context: automatic analyze of table "fm_db_Server3.mmsuper.audittraillogentry" > > We have the following queries in this regard: > > - How often is the autovacuum task invoked by postgres As needed. Read: http://www.postgresql.org/docs/9.4/static/routine-vacuuming.html > - If the task gets cancelled (while we were deleting data from the database) would this task be re-invoked ata later time? Yes. Read the above > - If insertion of data into a table also impact this task? No. Vacuum operations are not triggered by INSERTs. > - If we can manually schedule this task to a particular time (like off peak hours)? Yes, but given the questions you're asking, you probably do not have a good enough understanding of the situation to scheduleit correctly and will make the problem worse. You can run it manually any time you want, but I don't recommend thatyou disable autovacuum unless you have a good understanding of what you're doing. Let me take a guess at the problem: The table gets LOTs of inserts, constantly, and somewhere there's a job that runs outof cron or some similar scheduler that DELETEs a lot of those rows in a big chunk. The DELETE process probably runs infrequently,like once a day or even once a week because the designers thought it would be best to get everything taken careof all at once during some real or perceived slow period on the database. One solution to this is to run the DELETE process more frequently, such as every 15 minutes. In such a case, the processwill run much faster, make less changes, and require less work on the part of autovacuum to clean up after. Peoplefrequently complain that "this will impact performance if run during normal use hours," but in every case I've seen, nobody had actually tested to see if that statement was true, and running smaller purges morefrequently actually solved the problem. Another option would be to manually run vacuum after the big DELETE runs. See http://www.postgresql.org/docs/9.4/static/sql-vacuum.html Don't fall into the trap of running VACUUM FULL. This is usually a bad idea. If the client is complaining about reclaimingdisk space, start asking some hard questions: How much space is too much? Why are you convinced that the spaceis wasted? Usually the correct answer is to add more disk space, since Postgres tends to fall into a groove with a particular tablewhereby the "unused" space is actually being used and reclaimed by data tuples as the data in the table changes. It'snot unusal for the table to be 2x the size of the actual data on a heavily updated table. -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information.Any unauthorized use, disclosure or distribution is prohibited. If you are not the intended recipient, pleasecontact the sender by reply email and destroy all copies of the original message.
pgsql-general by date: