Re: High COMMIT times
On Thu, 2021-01-07 at 10:49 -0700, Craig Jackson wrote: > We had a similar situation recently and saw high commit times that were caused > by having unindexed foreign key columns when deleting data with large tables > involved. > You might check to see if any new foreign key constraints have been added > recently or if any foreign key indexes may have inadvertently been removed. > Indexing the foreign keys resolved our issue. Were these deferred foreign key constraints? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
RE: Autovacuum not functioning for large tables but it is working for few other small tables.
Hi all, As we know, the VACUUM VERBOSE output has a lot of dependencies from production end and is indefinite as of now. We don’t have any clue till now on why exactly the auto-vacuum is not working for the table. So we need to have a work around to move ahead for the time being. Can you please suggest any workaround so that we can resolve the issue or any other way by which we can avoid this situation? Regards Tarkeshwar -Original Message- From: Tomas Vondra Sent: Thursday, December 17, 2020 7:16 AM To: M Tarkeshwar Rao ; pgsql-performa...@postgresql.org Cc: Neeraj Gupta G ; Atul Parashar ; Shishir Singh ; Ankit Sharma Subject: Re: Autovacuum not functioning for large tables but it is working for few other small tables. On 12/16/20 12:55 PM, M Tarkeshwar Rao wrote: > Hi all, > > We have facing some discrepancy in Postgresql database related to the > autovacuum functionality. > > By default autovacuum was enable on Postgres which is used to remove > the dead tuples from the database. > > We have observed autovaccum cleaning dead rows from *table_A* but same > was not functioning correctly for *table_B* which have a large > size(100+GB) in comparision to table_A. > > All the threshold level requirements for autovacuum was meet and there > are about Million’s of dead tuples but autovacuum was unable to clear > them, which cause performance issue on production server. > > Is autovacuum not working against large sized tables or Is there any > parameters which need to set to make autovacuum functioning? > No, autovacuum should work for tables with any size. The most likely explanation is that the rows in the large table were deleted more recently and there is a long-running transaction blocking the cleanup. Or maybe not, hard to say with the info you provided. A couple suggestions: 1) enable logging for autovacuum by setting log_autovacuum_min_duration = 10ms (or similar low value) 2) check that the autovacuum is actually executed on the large table (there's last_autovacuum in pg_stat_all_tables) 3) try running VACUUM VERBOSE on the large table, it may tell you that the rows can't be cleaned up yet. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Autovacuum not functioning for large tables but it is working for few other small tables.
By the way, please do not top-post (reply above, quoting the full email after) in these groups. On Fri, Jan 8, 2021 at 5:00 AM M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> wrote: > Hi all, > > As we know, the VACUUM VERBOSE output has a lot of dependencies from > production end and is indefinite as of now. What do you mean by this statement? > We don’t have any clue till now on why exactly the auto-vacuum is not > working for the table. So we need to have a work around to move ahead for > the time being. > > Can you please suggest any workaround so that we can resolve the issue or > any other way by which we can avoid this situation? > Have you tried any of the suggestions already given 3+ weeks ago? Do you have answers to any of the questions posed by me or the other three people who responded?
Re: High COMMIT times
On Thu, Jan 7, 2021 at 3:03 PM Don Seiler wrote: > On Thu, Jan 7, 2021 at 11:50 AM Craig Jackson > wrote: > >> We had a similar situation recently and saw high commit times that were >> caused by having unindexed foreign key columns when deleting data with >> large tables involved. You might check to see if any new foreign key >> constraints have been added recently or if any foreign key indexes may have >> inadvertently been removed. Indexing the foreign keys resolved our issue. >> > > Interesting, I'll run a check for any. Thanks! > > Don. > > -- > Don Seiler > www.seiler.us > Do you have standby databases and synchronous_commit = 'remote_apply'? -- José Arthur Benetasso Villanova
Re: High COMMIT times
On Fri, Jan 8, 2021 at 11:52 AM José Arthur Benetasso Villanova < jose.art...@gmail.com> wrote: > Do you have standby databases and synchronous_commit = 'remote_apply'? > We have standby databases, but synchronous_commit is "on". I found a pair of unindexed foreign key child tables but they never run deletes on those and they are very small. I did mention it to the app team as a best practice in the meantime but I don't think it would play a part in our current issue. Don. -- Don Seiler www.seiler.us
Re: High COMMIT times
Yes, these were deferrable foreign key constraints. On Fri, Jan 8, 2021 at 2:05 AM Laurenz Albe wrote: > On Thu, 2021-01-07 at 10:49 -0700, Craig Jackson wrote: > > We had a similar situation recently and saw high commit times that were > caused > > by having unindexed foreign key columns when deleting data with large > tables involved. > > You might check to see if any new foreign key constraints have been added > > recently or if any foreign key indexes may have inadvertently been > removed. > > Indexing the foreign keys resolved our issue. > > Were these deferred foreign key constraints? > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > -- Craig -- This electronic communication and the information and any files transmitted with it, or attached to it, are confidential and are intended solely for the use of the individual or entity to whom it is addressed and may contain information that is confidential, legally privileged, protected by privacy laws, or otherwise restricted from disclosure to anyone else. If you are not the intended recipient or the person responsible for delivering the e-mail to the intended recipient, you are hereby notified that any use, copying, distributing, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. If you received this e-mail in error, please return the e-mail to the sender, delete it from your computer, and destroy any printed copy of it. smime.p7s Description: S/MIME Cryptographic Signature
