Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

On Mon, Jun 19, 2017 at 1:53 PM, Dmitry O Litvintsev wrote: > yes, we had to restart database 4 days ago (and vacuum has resumed on start). > I checked the log files and discovered that autovacuum on this table takes > > pages: 0 removed, 14072307 remain >

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

suggestions in the test environment. Thank you, Dmitry From: Jeff Janes <jeff.ja...@gmail.com> Sent: Monday, June 19, 2017 1:16 PM To: Dmitry O Litvintsev Cc: Andreas Kretschmer; pgsql-general@postgresql.org Subject: Re: [GENERAL] autovacuum holds exc

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

On Mon, 19 Jun 2017 17:33:23 + Dmitry O Litvintsev wrote: > > The test stand where I was to test schema upgrade is stuck cuz vacuum is > blocking. If you're in "panic mode" I would recommend cancelling the existing vacuum, running your upgrades, then immeditely running

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

On Mon, Jun 19, 2017 at 10:33 AM, Dmitry O Litvintsev wrote: > Hi > > Since I have posted this nothing really changed. I am starting to panic > (mildly). > > The source (production) runs : > > relname | mode | granted | >

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

Dmitry O Litvintsev wrote: > Hi > > Since I have posted this nothing really changed. I am starting to panic > (mildly). ... > vacuum_cost_delay = 50ms Most likely, this value is far too high. You're causing autovacuum to sleep for a very long time with this setting. Hard to say for

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

ndr...@a-kretschmer.de> Sent: Tuesday, June 13, 2017 1:54 PM To: pgsql-general@postgresql.org; Dmitry O Litvintsev; pgsql-general@postgresql.org Subject: Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated Am 13. Juni 2017 20:04:04 MESZ schrieb Dmitry O Litv

Re: [GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

Am 13. Juni 2017 20:04:04 MESZ schrieb Dmitry O Litvintsev : > >I >wraparound)| 2017- >| t | enstore | autovacuum: VACUUM public.t_inodes (to prevent >wraparound)| 2017-06-13 12:31:04.870064-05 | >00:28:50.276437 | 40672 >chimera |

[GENERAL] autovacuum holds exclusive lock on table preventing it from to be updated

Hi, I run postgresql 9.3.17. I am preparing for a major database schema upgrade. I copied production database to test system using pg_basebackup. Having started the database and waited for all WALs to be applied I proceeded to run schema modifications. Immediately I run into issue -

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Thu, Feb 23, 2017 at 2:42 AM, Bill Moran wrote: > On Wed, 22 Feb 2017 13:19:11 -0800 > Jeff Janes wrote: > > > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure > wrote: > > > > > > On Thursday, February 16, 2017, Tom Lane

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, Feb 22, 2017 at 3:19 PM, Jeff Janes wrote: > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure wrote: >> On Thursday, February 16, 2017, Tom Lane wrote: >>> >>> Tim Bellis writes: >>> > Even though

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, 22 Feb 2017 13:19:11 -0800 Jeff Janes wrote: > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure wrote: > > > > On Thursday, February 16, 2017, Tom Lane wrote: > > > >> Tim Bellis writes: > >> >

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Fri, Feb 17, 2017 at 10:36 AM, Tim Bellis <tim.bel...@metaswitch.com> wrote: > > > > > *From:* Jeff Janes [mailto:jeff.ja...@gmail.com] > *Sent:* 17 February 2017 02:59 > *To:* Tim Bellis <tim.bel...@metaswitch.com> > *Cc:* pgsql-general@postgresql.org &

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure wrote: > > > On Thursday, February 16, 2017, Tom Lane wrote: > >> Tim Bellis writes: >> > Even though this is a read only query, is it also expected to be >> blocked behind the

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

rlowe <scott.marl...@gmail.com> Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries Tim Bellis <tim.bel...@metaswitch.com> writes: > > Even though this is a read only query, is it also expected to be blocked > > behind the vacuum? Is there a way of getting index

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Thursday, February 16, 2017, Tom Lane wrote: > Tim Bellis > writes: > > Even though this is a read only query, is it also expected to be blocked > behind the vacuum? Is there a way of getting indexes for a table which > won't be

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On 02/17/2017 11:54 PM, Michael Paquier wrote: On Sat, Feb 18, 2017 at 1:32 PM, Scott Marlowe wrote: Yes it can. Truncate has been rollbackable for a while now. Per the docs: https://www.postgresql.org/docs/9.6/static/sql-truncate.html "TRUNCATE is transaction-safe

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Sat, Feb 18, 2017 at 1:32 PM, Scott Marlowe wrote: > Yes it can. Truncate has been rollbackable for a while now. Per the docs: https://www.postgresql.org/docs/9.6/static/sql-truncate.html "TRUNCATE is transaction-safe with respect to the data in the tables: the

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Fri, Feb 17, 2017 at 1:38 PM, Rakesh Kumar wrote: > LOCK TABLE yourtable ; > CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ; > TRUNCATE yourtable; > INSERT INTO yourtable SELECT * from keep; > COMMIT; > === > the above snippet assumes truncate in PG

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

LOCK TABLE yourtable ; CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE ; TRUNCATE yourtable; INSERT INTO yourtable SELECT * from keep; COMMIT; === the above snippet assumes truncate in PG can be in a transaction. In other words, while truncate by itself is atomic, it can't be rolled

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

yourtable SELECT * from keep; COMMIT; Best regards, -hannes -Original Message- From: Hannes Erven [mailto:han...@erven.at] Sent: 17 February 2017 11:47 To: pgsql-general@postgresql.org Cc: Tim Bellis <tim.bel...@metaswitch.com> Subject: Re: [GENERAL] Autovacuum stuck for

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

From: Jeff Janes [mailto:jeff.ja...@gmail.com] Sent: 17 February 2017 02:59 To: Tim Bellis <tim.bel...@metaswitch.com> Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries On Wed, Feb 15, 2017 at 9:30 AM, Tim Bellis <tim.bel...@metas

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

] Sent: 17 February 2017 11:47 To: pgsql-general@postgresql.org Cc: Tim Bellis <tim.bel...@metaswitch.com> Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries Hi Tim, Am 2017-02-15 um 18:30 schrieb Tim Bellis: > I have a postgres 9.3.4 database table which (inter

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Hi Tim, Am 2017-02-15 um 18:30 schrieb Tim Bellis: I have a postgres 9.3.4 database table which (intermittently but reliably) > gets into a state where queries get blocked indefinitely > [..] Notes: - This database table is used for about 6 million row writes per day, > all of which

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Tom Lane wrote: > Also you might want to look into how you got into a situation where > you have an anti-wraparound vacuum that's taking so long to run. If there are ALTERs running all the time, regular (non-anti-wraparound) vacuums would be canceled and never get a chance to run. Eventually,

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Tim Bellis writes: > Even though this is a read only query, is it also expected to be blocked > behind the vacuum? Is there a way of getting indexes for a table which won't > be blocked behind a vacuum? It's not the vacuum that's blocking your read-only queries.

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On 02/16/2017 08:45 AM, Tim Bellis wrote: Thank you all - that's really useful :-) The other query that gets blocked behind the vacuum is the below (truncated). This query is generated by jdbc in this method:

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Thank you all - that's really useful :-) The other query that gets blocked behind the vacuum is the below (truncated). This query is generated by jdbc in this method: org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:4023) Even though this is a

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Scott Marlowe wrote: > Then a ddl gets in line. It has to wait on the vacuum, and the vacuum, > set to run super slow. And everybody waits. On vacuum. Note that this is normally not seen, because autovacuum cancels itself when somebody is blocked behind it -- until the table reaches the

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, Feb 15, 2017 at 3:26 PM, Scott Marlowe wrote: > On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis > wrote: >> I have a postgres 9.3.4 database table which (intermittently but reliably) >> gets into a state where queries get blocked

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis wrote: > I have a postgres 9.3.4 database table which (intermittently but reliably) > gets into a state where queries get blocked indefinitely (at least for many > hours) behind an automatic vacuum. I was under the

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On 02/15/2017 09:30 AM, Tim Bellis wrote: I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get blocked indefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should never take any

[GENERAL] Autovacuum stuck for hours, blocking queries

I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get blocked indefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should never take any blocking locks for any significant period of

Re: [GENERAL] [pgsql-GENERAL] AUTOVACUUM and Streaming Replication in v9.5

On Fri, 16 Dec 2016, 12:10 a.m. Sameer Kumar, wrote: > Hi, > > I was wondering if there is some change in the way > max_standby_streaming_delay and hot_standby_feedback work in v9.5. > > Below is a scenario which I had built to test out something but the > behavior

[GENERAL] [pgsql-GENERAL] AUTOVACUUM and Streaming Replication in v9.5

Hi, I was wondering if there is some change in the way max_standby_streaming_delay and hot_standby_feedback work in v9.5. Below is a scenario which I had built to test out something but the behavior confused me. I would like to check if there is something/some parameter which I am missing or if

Re: R: [GENERAL] Autovacuum and frequent pg_bulkload

On 11/20/2016 07:20 AM, Job wrote: Hi Adrian, Thank you for your fast reply! FYI, Postgres 8.4 is over two years past EOL. Yes, i am aware; we will migrate next year but for application compatibility reason we have at the moment to the remain here.. What are the steps in the load/delete

R: [GENERAL] Autovacuum and frequent pg_bulkload

Hi Adrian, Thank you for your fast reply! >FYI, Postgres 8.4 is over two years past EOL. Yes, i am aware; we will migrate next year but for application compatibility reason we have at the moment to the remain here.. >What are the steps in the load/delete cycle? We need to load, with

Re: [GENERAL] Autovacuum and frequent pg_bulkload

On 11/20/2016 04:51 AM, Job wrote: Hello, i use PostgreSql 8.4.22 and two times a day, i import logs wth pg_bulkload and i delete old data. I move some millions of records in a day. FYI, Postgres 8.4 is over two years past EOL. I noticed that only autovacuum seems not to be able to free

[GENERAL] Autovacuum and frequent pg_bulkload

Hello, i use PostgreSql 8.4.22 and two times a day, i import logs wth pg_bulkload and i delete old data. I move some millions of records in a day. I noticed that only autovacuum seems not to be able to free unused space. I need a periodical vacuum full but this operations takes many hours. Do

Re: [GENERAL] autovacuum worker running amok - and me too ;)

On 3/9/15 3:56 AM, wambacher wrote: Hi paul just found my system (24 GB Mem, 72 GB Swap) running nearly at it's limits: The current vaccum is using 66.7 GB (sixty-six dot seven) GB of memory and my System is nearly down. I'm sorry, but that must be an bug. Remember: It's the Analyze of an

Re: [GENERAL] Autovacuum query

On Thu, 26 Mar 2015 03:58:59 + Mitu Verma mitu.ve...@ericsson.com wrote: We have a customer complaining about the time taken by one of the application scripts while deleting older data from the log tables. During the deletion, customer reported that he often sees the below error and

Re: [GENERAL] Autovacuum query

. Runs in 10 msec and vacuum doesn't need to run. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@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

Re: [GENERAL] Autovacuum query

On March 25, 2015 09:31:24 PM David G. Johnston wrote: On Wed, Mar 25, 2015 at 8:58 PM, Mitu Verma mitu.ve...@ericsson.com wrote: Correcting the subject ​And this is why it is considered good form to do compose new message instead of replying to an existing one. Injecting your new topic

Re: [GENERAL] Autovacuum query

On Wed, Mar 25, 2015 at 8:58 PM, Mitu Verma mitu.ve...@ericsson.com wrote: Correcting the subject ​And this is why it is considered good form to do compose new message instead of replying to an existing one. Injecting your new topic into an existing unrelated mail thread is mildly annoying.

[GENERAL] Autovacuum query

Correcting the subject From: Mitu Verma Sent: March 26, 2015 9:28 AM To: pgsql-general@postgresql.org Cc: pgsql-general@postgresql.org Subject: RE: [GENERAL] Populating missing dates in postgresql data Hi, We have a customer complaining about the time taken by one of the application scripts

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Hi paul just found my system (24 GB Mem, 72 GB Swap) running nearly at it's limits: The current vaccum is using 66.7 GB (sixty-six dot seven) GB of memory and my System is nearly down. I'm sorry, but that must be an bug. Remember: It's the Analyze of an GIN-Index that is making that problems.

Re: [GENERAL] autovacuum worker running amok - and me too ;)

sorry, 64 GB swap -- View this message in context: http://postgresql.nabble.com/autovacuum-worker-running-amok-and-me-too-tp5840299p5841075.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Hi, some final results: I monitored the vaccum process and logged some data using one big table and doing analyze/vaccum by hand. Table has two btree-indexes and one gin. maintenance_work_mem was 1GB. the analyze job used abot 1.2 GB virt mem during the whole task, no problems at all. The

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Karsten Hilbert wrote Of course, I am not suggesting you provide 48GB of swap and your problem is magically solved _but_ one thing we might take away from that old adage is that one might hope things to work better (say, while debugging) if there is at least as much swap as there is physical

Re: [GENERAL] autovacuum worker running amok - and me too ;)

wambacher wrote hi, waiting for the index (104/121GB), i read his document http://www.postgresql.org/docs/9.4/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT and will do some changes before the next analyze: some comments: - the OOM did not kill the Postmaster but the Analyze-Job.

Re: [GENERAL] autovacuum worker running amok - and me too ;)

hi, waiting for the index (104/121GB), i read his document http://www.postgresql.org/docs/9.4/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT and will do some changes before the next analyze: some comments: - the OOM did not kill the Postmaster but the Analyze-Job. - started with 24GB real

Re: [GENERAL] autovacuum worker running amok - and me too ;)

On Fri, Mar 06, 2015 at 02:39:34AM -0700, wambacher wrote: some comments: - the OOM did not kill the Postmaster but the Analyze-Job. - started with 24GB real and 18GB Swap - that must be enought! -- killed Back in the days it was conventional wisdom to have twice as much swap as you've got

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Jim Nasby-5 wrote Which is it? Is the vacuum process is using 1.2GB (5% of memory) or is it using 90% (~22GB)? i ran the job 2-3 times. - first with 18GB swap too. I heared it thrashing, performance went extremly down and after 2 hours i killed the job (reboot system, no other way to do it)

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Hi, in my first post you can see all params: maintenance_work_mem = 64MB and two workers. i configured my system to the absolutely minimum ( got 24 GB real memory) and the problem was still there. Last night i rebuilt one index (122GB Size) and just in this minutes i started a manual analyze

Re: [GENERAL] autovacuum worker running amok - and me too ;)

... this will need some hours. Done after 30 Minutes :) nearly 50% dead rows - strange. Now i'll run a vacuum verbose planet_osm_ways because the system crashed during the autovacuum. cross my fingers. Walter -- View this message in context:

Re: [GENERAL] autovacuum worker running amok - and me too ;)

crashed: no idea what to do now. walter -- View this message in context: http://postgresql.nabble.com/autovacuum-worker-running-amok-and-me-too-tp5840299p5840696.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list

Re: [GENERAL] autovacuum worker running amok - and me too ;)

On 3/5/15 2:06 PM, wambacher wrote: crashed: no idea what to do now. Crashed? Or hit by the OOM killer? What's the log say? While this is going on you might as well disable autovac for that table. It'll keep crashing and will interfere with your manual vacuums. It sounds at this point

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Jim Nasby-5 wrote On 3/5/15 2:06 PM, wambacher wrote: Crashed? Or hit by the OOM killer? What's the log say? killed by OOM, but has only 1.2 GB mem, which is ok to me. While this is going on you might as well disable autovac for that table. It'll keep crashing and will interfere with your

Re: [GENERAL] autovacuum worker running amok - and me too ;)

On 3/5/15 7:36 PM, wambacher wrote: Jim Nasby-5 wrote On 3/5/15 2:06 PM, wambacher wrote: Crashed? Or hit by the OOM killer? What's the log say? killed by OOM, but has only 1.2 GB mem, which is ok to me. Ok, but... What's the largest memory size that a vacuum/autovac against that table

Re: [GENERAL] autovacuum worker running amok - and me too ;)

wambacher wnordm...@gmx.de wrote: watching the memory usage of the autovaccum process: is was getting bigger and bigger at nearly constant speed. some MB per minute, iir. What are your settings for maintenance_work_mem and autovacuum_max_workers? -- Kevin Grittner EDB:

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Circling back on this one, I had a look at our analyze code. I found one place where *maybe* we weren't freeing memory and freed it, but analyzing a 2M record table I barely see any bump up in memory usage (from 22M up to 24M at peak) during analyze. And the change I made didn't appear to alter

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Paul Ramsey wrote Though maybe with a really big table? (with really big objects?) Though still, doesn't analyze just pull a limited sample (30K approx max) so why would table size make any difference after a certain point? Hi paul, my table is quite big (about 293.049.000 records) but the

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Roxanne Reid-Bennett wrote Most definitely ask on the Postgis list. Identify the full Postgis version and Postgres versions as well. Hi Roxanne, seconds before sending it to the postgis-list i checked the table planet_osm_ways and there is no geometry: That can't be a postgis problem. I'll

[GENERAL] autovacuum worker running amok - and me too ;)

Hi, running postgresql on ubuntu for many years, but now i'm in big trouble. My system has 24GB of real memory but after some hours one autovacuum worker is using 80-90% of memory, the OOM-Killer (out of memory killer) kills the process with kill -9 and the postgresql-server is restarting

Re: [GENERAL] autovacuum worker running amok - and me too ;)

wambacher wnordm...@gmx.de writes: My system has 24GB of real memory but after some hours one autovacuum worker is using 80-90% of memory, the OOM-Killer (out of memory killer) kills the process with kill -9 and the postgresql-server is restarting because of that problem. i changed the base

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Tom Lane-2 wrote Maybe you could reduce the statistics targets for that table. don't understand what you mean. do you mean how often that table is autovacuumed? at the moment about once a day or once in two days, i think. I think we've heard that the analyze functions for PostGIS data types

Re: [GENERAL] autovacuum worker running amok - and me too ;)

wambacher wnordm...@gmx.de writes: Tom Lane-2 wrote Maybe you could reduce the statistics targets for that table. don't understand what you mean. do you mean how often that table is autovacuumed? at the moment about once a day or once in two days, i think. No, I mean the amount of stats

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Tom Lane-2 wrote See ALTER TABLE SET STATISTICS TARGET. thanks, will try it regards walter btw: the postgis analyze problem has been fixed more than one year ago, but i'll ask them too. -- View this message in context:

Re: [GENERAL] autovacuum worker running amok - and me too ;)

On 3/3/2015 6:17 PM, Tom Lane wrote: wambacher wnordm...@gmx.de writes: My system has 24GB of real memory but after some hours one autovacuum worker is using 80-90% of memory, the OOM-Killer (out of memory killer) kills the process with kill -9 and the postgresql-server is restarting because

Re: [GENERAL] autovacuum with inheritance

yhe wrote From looking at the log, [...] Any reason not to share it with the people from whom you are asking for help? I was thinking that autovacuum should only happen on updated table so it should only autovacuum on one child and not the others for my case. At the point of stop usage on

Re: [GENERAL] Autovacuum on partitioned tables in version 9.1

On 11/11/2014 07:52 PM, David G Johnston wrote: TL;DR - the interaction of ANALYZE and inheritance hierarchies seems to be broken for the uncommon use case where the inserts temporarily remain on the master table in order to allow RETURNING to work. Yeah, if I do: # vacuum verbose

[GENERAL] Autovacuum on partitioned tables in version 9.1

Hello People, Before the question, this is the scenario: I have a postgresql 9.1 cluster with a size of 1.5 TB and composed of 70 databases. In every database I have 50 tables (master partition), each one have an associated trigger that insert the record into a child table of its own. The

Re: [GENERAL] Autovacuum on partitioned tables in version 9.1

TL;DR - the interaction of ANALYZE and inheritance hierarchies seems to be broken for the uncommon use case where the inserts temporarily remain on the master table in order to allow RETURNING to work. Note - I have not played with this scenario personally but

Re: [GENERAL] AutoVacuum Daemon

On Mon, Dec 30, 2013 at 11:02 PM, Leonardo M. Ramé l.r...@griensu.com wrote: On 2013-12-30 13:45:43 +, Haribabu kommi wrote: On 30 December 2013 19:11 Leonardo M. Ramé wrote: Hi, I want know if I should run the auto-vacuum daemon (from /etc/init.d/) or it runs automatically and

[GENERAL] AutoVacuum Daemon

Hi, I want know if I should run the auto-vacuum daemon (from /etc/init.d/) or it runs automatically and transparently if configured in postgres.conf?. If it must be configured manually, what is the script to be run, I didn't find pg_autovacuum or similar. I didn't find information about this on

Re: [GENERAL] AutoVacuum Daemon

On 30 December 2013 19:11 Leonardo M. Ramé wrote: Hi, I want know if I should run the auto-vacuum daemon (from /etc/init.d/) or it runs automatically and transparently if configured in postgres.conf?. If it must be configured manually, what is the script to be run, I didn't find pg_autovacuum

Re: [GENERAL] AutoVacuum Daemon

On 2013-12-30 13:45:43 +, Haribabu kommi wrote: On 30 December 2013 19:11 Leonardo M. Ramé wrote: Hi, I want know if I should run the auto-vacuum daemon (from /etc/init.d/) or it runs automatically and transparently if configured in postgres.conf?. If it must be configured manually,

[GENERAL] autovacuum out of memory errors

Hi, Starting this morning I have been getting out of memory errors from the postgres autovacuum process. I have searched through previous similar questions but not found anything other than suggestions to reduce either 'shared_buffers' or 'maintenance_work_mem' but these seem very instance

Re: [GENERAL] autovacuum out of memory errors

tim truman mitram...@gmail.com wrote: [ memory usage map ] There doesn't seem to be any memory context using an unusually large amount of RAM.   522f9128.1151 ERROR:  out of memory   522f9128.1151 DETAIL:  Failed on request of size 336150396.   522f9128.1151 CONTEXT:  automatic vacuum of

[GENERAL] autovacuum: found orphan temp table

Hi, after a server crash the following messages appear in the log file every minute: 2013-06-25 15:02:15 CEST [::18264:1:] LOG: autovacuum: found orphan temp table pg_temp_47.est_backup_ids_temp in database estudis1314 2013-06-25 15:02:15 CEST [::18264:2:] LOG: autovacuum: found orphan

Re: [GENERAL] autovacuum: found orphan temp table

On 06/25/13 06:13, Nicolau Roca wrote: 2013-06-25 15:02:15 CEST [::18264:1:] LOG: autovacuum: found orphan temp table pg_temp_47.est_backup_ids_temp in database estudis1314 2013-06-25 15:02:15 CEST [::18264:2:] LOG: autovacuum: found orphan temp table pg_temp_47.est_backup_files_temp in

Re: [GENERAL] autovacuum: found orphan temp table

On Tue, Jun 25, 2013 at 6:43 PM, Nicolau Roca nicolau.r...@uib.cat wrote: Hi, after a server crash the following messages appear in the log file every minute: 2013-06-25 15:02:15 CEST [::18264:1:] LOG: autovacuum: found orphan temp table pg_temp_47.est_backup_ids_temp in database

Re: [GENERAL] Autovacuum doesn't work if the table has large number of records

Hi Jeff, Thanks for your reply. Yes, it took a while to complete the autovacuum. I checked it again this morning: last_autovacuum: 2013-04-14 06:17:02.9464+08 last_autoanalyze: 2013-04-13 20:27:12.396048+08 n_dead_tup: nill (or blank) n_live_tup: 334453396 relpages: 2654868 reltuples:

[GENERAL] Autovacuum doesn't work if the table has large number of records

On Saturday, April 13, 2013, Ascot Moss wrote: Hi Jeff, Thanks for your reply. Yes, it took a while to complete the autovacuum. I checked it again this morning: last_autovacuum: 2013-04-14 06:17:02.9464+08 last_autoanalyze: 2013-04-13 20:27:12.396048+08 ... I also checked the

[GENERAL] Autovacuum doesn't work if the table has large number of records

(please ignore if duplicated) Hi, I am new to PostgreSQL. I have a PostgreSQL table named test with 300,000,000 records inserted, it has only two fields (id, and int i). For testing purposes, after inserts, all records have been updated once in order to make n_dead_tup = 300,000,000 Current

Re: [GENERAL] Autovacuum doesn't work if the table has large number of records

On Sat, Apr 13, 2013 at 9:55 AM, Ascot Moss ascot.m...@gmail.com wrote: Current stat of test table: pg_class.reltuples: 3.8415e+08 pg_class.relpages: 1703069 last_autovacuum: null (or blank) last_autoanalyze: 2013-04-13 20:27:12.396048+08 pg_stat_user_tables.n_dead_tup: 3 The

[GENERAL] Autovacuum doesn't work

Hi, I am new to PostgreSQL. I have a PostgreSQL table named test with 300,000,000 records inserted, it has only two fields (id, and int i). For testing purposes, after inserts, all records have been updated once in order to make n_dead_tup = 300,000,000 Current PostgreSQL settings: autovacuum:

[GENERAL] Autovacuum Launcher Process Exception

Hello, I have been running PostgreSQL for many months and just recently started getting this exception upon start up. Does anyone know what the issue might be? LOG: database system is ready to accept connections LOG: autovacuum launcher started LOG: autovacuum launcher process PID 7060

Re: [GENERAL] Autovacuum Launcher Process Exception

elli...@cpi.com wrote: Hello, I have been running PostgreSQL for many months and just recently started getting this exception upon start up. Does anyone know what the issue might be? LOG: database system is ready to accept connections LOG: autovacuum launcher started LOG:

Re: [GENERAL] autovacuum running for a long time on a new table with 1 row

Lonni J Friedman netll...@gmail.com writes: Running 9.1.3 on Linux-x86_64. I'm seeing autovacuum running for the past 6 hours on a newly created table that only has 1 row of data in it. This table did exist previously, but was dropped recreated. I'm not sure if that might explain this

Re: [GENERAL] autovacuum running for a long time on a new table with 1 row

On Fri, Jun 1, 2012 at 10:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: Running 9.1.3 on Linux-x86_64.  I'm seeing autovacuum running for the past 6 hours on a newly created table that only has 1 row of data in it.  This table did exist previously, but

Re: [GENERAL] autovacuum running for a long time on a new table with 1 row

Lonni J Friedman netll...@gmail.com writes: On Fri, Jun 1, 2012 at 10:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: This seems to have been noticed and fixed in HEAD: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=b4e0741727685443657b55932da0c06f028fbc00 I wonder whether that

Re: [GENERAL] autovacuum running for a long time on a new table with 1 row

On Fri, Jun 1, 2012 at 10:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: Lonni J Friedman netll...@gmail.com writes: On Fri, Jun 1, 2012 at 10:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: This seems to have been noticed and fixed in HEAD:

Re: [GENERAL] autovacuum running for a long time on a new table with 1 row

Lonni J Friedman netll...@gmail.com writes: On Fri, Jun 1, 2012 at 10:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: ... if you really did drop and recreate the table, then at this point it should only have a single page, I would think.  It might be worth checking the actual file size.  

[GENERAL] autovacuum running for a long time on a new table with 1 row

Running 9.1.3 on Linux-x86_64. I'm seeing autovacuum running for the past 6 hours on a newly created table that only has 1 row of data in it. This table did exist previously, but was dropped recreated. I'm not sure if that might explain this behavior. When I strace the autovacuum process, I

Re: [GENERAL] autovacuum and transaction id wraparound

Hi Jens, Thanks for answer. One more question: Can the manual VACUUM operate on database where there are long lasting transactions? In other words, do I need to restart the server or kill long lasting transactions in order to allow manual VACUUM to clear old XIDs? Regards, Pawel -- View this

Re: [GENERAL] autovacuum and transaction id wraparound

On Fri, Mar 9, 2012 at 12:15 AM, pawel_kukawski kukis...@gmail.com wrote: Hi Jens, Thanks for answer. One more question: Can the manual VACUUM operate on database where there are long lasting transactions? In other words, do I need to restart the server or kill long lasting transactions

Re: [GENERAL] autovacuum and transaction id wraparound

On Wednesday 07 March 2012 21:13:26 pawel_kukawski wrote: Hi, Do you know any real reason why the autovacuum may fail to clear old XIDs? If it's unable to keep up. Or may be, if there're very long running idle in transactions. Is this highly probable ? postmaster will shutdown to prevent

[GENERAL] autovacuum and transaction id wraparound

Hi, I have a question regarding the following snippet in the official Postgres documentation: If for some reason autovacuum fails to clear old XIDs from a table, the system will begin to emit warning messages like this when the database's oldest XIDs reach ten million transactions from the

[GENERAL] autovacuum and deadlocks

What is a likelihood of a deadlock occurring, caused (or helped by) auto vacuum. This is on 8.3. The table with deadlocks was quite busy with updates, etc. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] autovacuum and deadlocks

On Sat, Feb 18, 2012 at 8:59 AM, Gregg Jaskiewicz gryz...@gmail.com wrote: What is a likelihood of a deadlock occurring, caused (or helped by) auto vacuum. This is on 8.3. The table with deadlocks was quite busy with updates, etc. The only locking issues (not deadlocks) I've ever had with

  1   2   3   4   5   6   >