Обсуждение: Log messages regarding automatic vacuum and exclusive locks
Good morning. I'm seeing several of the following log messages each morning (for example, there were five this morning, spaced approximately one minute apart, with the closest interval between messages being 44 seconds). They're occurring during a daily cycle of deleting all rows from a table and then repopulating it from another database; the first message came approximately seven minutes after the process started and the last one came about a minute before the process ended. There are approximately one million rows in the database after repopulation. LOG: automatic vacuum of table "x.y.z": could not (re)acquire exclusive lock for truncate scan I'm using default settings for autovacuum, with server version 9.2.4. Some other smaller tables produce the same messages during the same process, but apparently less frequently. Any thoughts on the cause? Chapters 18 and 23 of the documentation provided some hints, but nothing conclusive. Thanks in advance, Dominic Jones
"jonesd@xmission.com" <jonesd@xmission.com> wrote: > I'm seeing several of the following log messages each > morning (for example, there were five this morning, spaced > approximately one minute apart, with the closest interval between > messages being 44 seconds). They're occurring during a daily cycle of > deleting all rows from a table and then repopulating it from another > database; the first message came approximately seven minutes after the > process started and the last one came about a minute before the > process ended. There are approximately one million rows in the > database after repopulation. > > LOG: automatic vacuum of table "x.y.z": could not (re)acquire > exclusive lock for truncate scan > > I'm using default settings for autovacuum, with server version 9.2.4. > > Some other smaller tables produce the same messages during the same > process, but apparently less frequently. > > Any thoughts on the cause? Chapters 18 and 23 of the documentation > provided some hints, but nothing conclusive. As long as these messages stop before the statistics for the related table have a chance to get out line with reality, these messages can be safely ignored. They are happening because autovacuum noticed a large amount of free space at the end of the table, and was attempting to remove that space from the table and give it back to the filesystem, but noticed conflicting access to the table. It steps aside to avoid holding up the other process, and is just making a note of that. If the table is being repopulated, it is probably just as well that autovacuum does not truncate the table, since that would just result in reallocation as rows are added. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, Apr 23, 2013 at 8:50 AM, <jonesd@xmission.com> wrote: > Good morning. I'm seeing several of the following log messages each morning > (for example, there were five this morning, spaced approximately one minute > apart, with the closest interval between messages being 44 seconds). > They're occurring during a daily cycle of deleting all rows from a table and > then repopulating it from another database; the first message came If all the rows are deleted from the table every time to repopulate it later, I would suggest using TRUNCATE table_name; instead DELETE FROM table_name. TRUNCATE does not left any free space in the table so autovacuum will have nothing to do with it. > LOG: automatic vacuum of table "x.y.z": could not (re)acquire exclusive > lock for truncate scan Hence you will not get this messages and BTW it will be faster. > > I'm using default settings for autovacuum, with server version 9.2.4. > > Some other smaller tables produce the same messages during the same process, > but apparently less frequently. > > Any thoughts on the cause? Chapters 18 and 23 of the documentation provided > some hints, but nothing conclusive. > > > Thanks in advance, > > Dominic Jones > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev Database and Software Consultant Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
I'll give using TRUNCATE to clear the tables a try and see what happens. Dominic Jones Quoting Sergey Konoplev <gray.ru@gmail.com>: > On Tue, Apr 23, 2013 at 8:50 AM, <jonesd@xmission.com> wrote: >> Good morning. I'm seeing several of the following log messages each morning >> (for example, there were five this morning, spaced approximately one minute >> apart, with the closest interval between messages being 44 seconds). >> They're occurring during a daily cycle of deleting all rows from a table and >> then repopulating it from another database; the first message came > > If all the rows are deleted from the table every time to repopulate it > later, I would suggest using TRUNCATE table_name; instead DELETE FROM > table_name. TRUNCATE does not left any free space in the table so > autovacuum will have nothing to do with it. > >> LOG: automatic vacuum of table "x.y.z": could not (re)acquire exclusive >> lock for truncate scan > > Hence you will not get this messages and BTW it will be faster. > >> >> I'm using default settings for autovacuum, with server version 9.2.4. >> >> Some other smaller tables produce the same messages during the same process, >> but apparently less frequently. >> >> Any thoughts on the cause? Chapters 18 and 23 of the documentation provided >> some hints, but nothing conclusive. >> >> >> Thanks in advance, >> >> Dominic Jones >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > -- > Kind regards, > Sergey Konoplev > Database and Software Consultant > > Profile: http://www.linkedin.com/in/grayhemp > Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 > Skype: gray-hemp > Jabber: gray.ru@gmail.com >