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
> tuples: 43524292 removed,
suggestions in the test environment.
Thank you,
Dmitry
From: Jeff Janes
Sent: Monday, June 19, 2017 1:16 PM
To: Dmitry O Litvintsev
Cc: Andreas Kretschmer; pgsql-general@postgresql.org
Subject: Re: [GENERAL] autovacuum holds exclusive lock on table
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 VACUUM FREEZE ANALYZ
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 |
> substr
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 certain
hmer
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 Litvintsev :
>
&
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 | t_inodes
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 wrote:
> > >
> > >> Tim Bellis writes:
> > >> > Even though this is a
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 this is a read only query, is it also expected to be
>>> > blocked behind the vacuum? Is th
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:
> >> > Even though this is a read only query, is it also expected to be
> >> blocked behind the vacu
On Fri, Feb 17, 2017 at 10:36 AM, Tim Bellis
wrote:
>
>
>
>
> *From:* Jeff Janes [mailto:jeff.ja...@gmail.com]
> *Sent:* 17 February 2017 02:59
> *To:* Tim Bellis
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] Autovacuum stuck for hours, blocking que
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 vacuum? Is there a way of getting indexes for a table
>> which won't b
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: 16 February 2017 22:40
To: Tim Bellis
Cc: Adrian Klaver ; pgsql-general@postgresql.org;
Alvaro Herrera ; Scott Marlowe
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries
Tim Bellis writes
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 blocked behind a vacuum?
>
> It's not the vacuum that's blocking
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 with respect to the data in
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 truncation will be safely rolled
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 can be in a transaction. In ot
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 b
e;
INSERT INTO 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
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking querie
From: Jeff Janes [mailto:jeff.ja...@gmail.com]
Sent: 17 February 2017 02:59
To: Tim Bellis
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
mailto:tim.bel...@metaswitch.com>> wrote:
I
.@erven.at]
Sent: 17 February 2017 11:47
To: pgsql-general@postgresql.org
Cc: Tim Bellis
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 (intermittently but
> reliably
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 a
On Wed, Feb 15, 2017 at 9: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
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,
au
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. It's the
ALTER TABLE, which nee
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:
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseM
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
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
freeze_ma
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 indefinitely (at least for many
>> hours) behind an automat
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 impression that vacuum
> should nev
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 block
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 un
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
GIN-
On March 25, 2015 09:31:24 PM David G. Johnston wrote:
> On Wed, Mar 25, 2015 at 8:58 PM, Mitu Verma 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
> existin
he other 5. 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
Sub
On Thu, 26 Mar 2015 03:58:59 +
Mitu Verma 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
> because of which tabl
On Wed, Mar 25, 2015 at 8:58 PM, Mitu Verma 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.
David J.
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 mak
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. Va
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 vacu
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 Anal
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 p
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'v
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
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)
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
>
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 yo
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 like
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 (pgsql-genera
> ... 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:
http://postgresql.nabble.com/autovacuum-
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
wambacher 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: http://www.enterprisedb.com
The En
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 t
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 tha
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
On 3/3/2015 6:17 PM, Tom Lane wrote:
wambacher 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
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:
http://postgresql.nabble.com/autovacuum-worker-running-amok-and-me-to
wambacher 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 detail that ANAL
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 a
wambacher 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 configurati
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 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 public.t
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
http://www.postgresql.org/message-i
On Mon, Dec 30, 2013 at 11:02 PM, Leonardo M. Ramé 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 transparently if co
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 manuall
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_autovacu
tim truman 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 table "client.public
On Tue, Jun 25, 2013 at 6:43 PM, Nicolau Roca 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 "estudis1314"
> 2
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_f
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: 3.3
On Sat, Apr 13, 2013 at 9:55 AM, Ascot Moss 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 autovacuum th
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:
Lonni J Friedman writes:
> On Fri, Jun 1, 2012 at 10:54 AM, Tom Lane 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. Â pg_relation_size('tablename') is
>> pro
On Fri, Jun 1, 2012 at 10:54 AM, Tom Lane wrote:
> Lonni J Friedman writes:
>> On Fri, Jun 1, 2012 at 10:34 AM, Tom Lane wrote:
>>> This seems to have been noticed and fixed in HEAD:
>>> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=b4e0741727685443657b55932da0c06f028fbc00
>>
Lonni J Friedman writes:
> On Fri, Jun 1, 2012 at 10:34 AM, Tom Lane wrote:
>> This seems to have been noticed and fixed in HEAD:
>> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=b4e0741727685443657b55932da0c06f028fbc00
>> I wonder whether that should've been back-patched.
>
On Fri, Jun 1, 2012 at 10:34 AM, Tom Lane wrote:
> Lonni J Friedman 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
Lonni J Friedman 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 behavior. When I s
On Fri, Mar 9, 2012 at 12:15 AM, pawel_kukawski 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 in order to
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 m
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 w
On Sat, Feb 18, 2012 at 8:59 AM, Gregg Jaskiewicz 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 autovacuum
were
On Tue, Nov 22, 2011 at 10:51 PM, Robert Treat wrote:
> On Tue, Nov 22, 2011 at 11:00 PM, Lonni J Friedman wrote:
>> On Tue, Nov 22, 2011 at 7:49 PM, Tom Lane wrote:
>>> Lonni J Friedman writes:
I suspect you're right. I just ran strace against that PID again, and
now all the lseek &
On Tue, Nov 22, 2011 at 11:00 PM, Lonni J Friedman wrote:
> On Tue, Nov 22, 2011 at 7:49 PM, Tom Lane wrote:
>> Lonni J Friedman writes:
>>> I suspect you're right. I just ran strace against that PID again, and
>>> now all the lseek & read FD's are referrring to a different number
>>> (115), so
On Tue, Nov 22, 2011 at 7:49 PM, Tom Lane wrote:
> Lonni J Friedman writes:
>> I suspect you're right. I just ran strace against that PID again, and
>> now all the lseek & read FD's are referrring to a different number
>> (115), so that means its moved onto something new since I looked a few
>>
Lonni J Friedman writes:
> I suspect you're right. I just ran strace against that PID again, and
> now all the lseek & read FD's are referrring to a different number
> (115), so that means its moved onto something new since I looked a few
> hours ago?
> Anyway, I think this is what you were refe
On Tue, Nov 22, 2011 at 7:19 PM, Tom Lane wrote:
> Lonni J Friedman writes:
>> Thanks for your prompt reply. I was pretty sure that I was using the
>> default, but just to confirm, I just ran:
>> 'SHOW vacuum_cost_delay;'
>
> What about autovacuum_vacuum_cost_delay? The selects seem to be
> del
Hi,
On 23 November 2011 13:20, Lonni J Friedman wrote:
> I investigated, and found that for the past ~18 hours,
> there's one autovacuum process that has been running, and not making
> any obvious progress:
snip...
> I'm using the defaults for all the *vacuum* options in
> postgresql.conf, exc
Lonni J Friedman writes:
> Thanks for your prompt reply. I was pretty sure that I was using the
> default, but just to confirm, I just ran:
> 'SHOW vacuum_cost_delay;'
What about autovacuum_vacuum_cost_delay? The selects seem to be
delaying for 32msec, which is not the default for anything.
>
On Tue, Nov 22, 2011 at 6:57 PM, Tom Lane wrote:
> Lonni J Friedman writes:
>> When I strace PID 30188, I see tons of this scrolling past quickly,
>> but I'm not really sure what it means beyond a 'Timeout' not looking
>> good:
>> select(0, NULL, NULL, NULL, {0, 32000}) = 0 (Timeout)
>> lseek(95,
Lonni J Friedman writes:
> When I strace PID 30188, I see tons of this scrolling past quickly,
> but I'm not really sure what it means beyond a 'Timeout' not looking
> good:
> select(0, NULL, NULL, NULL, {0, 32000}) = 0 (Timeout)
> lseek(95, 753901568, SEEK_SET) = 753901568
> read(95, "\2
2011/6/23 Gábor Farkas :
> 2011/6/23 Thom Brown :
>> 2011/6/23 Gábor Farkas :
>>> hi,
>>>
>>> postgresql8.4.7 here.
>>>
>>> i checked the pg_stat_user_tables table, and it have a lot of rows
>>> there where the "last_autovacuum" and/or "last_autoanalyze" are null.
>>> does this mean that autovacuum
2011/6/23 Thom Brown :
> 2011/6/23 Gábor Farkas :
>> hi,
>>
>> postgresql8.4.7 here.
>>
>> i checked the pg_stat_user_tables table, and it have a lot of rows
>> there where the "last_autovacuum" and/or "last_autoanalyze" are null.
>> does this mean that autovacuum never worked on those tables?
>>
>
2011/6/23 Gábor Farkas :
> hi,
>
> postgresql8.4.7 here.
>
> i checked the pg_stat_user_tables table, and it have a lot of rows
> there where the "last_autovacuum" and/or "last_autoanalyze" are null.
> does this mean that autovacuum never worked on those tables?
>
> roughly 70% of all the tables ha
We are still seeing the spike in vacuums every 8 days, even after upgrading
to 9.0.3. Any suggestions on how to spread them out?
Thanks,
George Woodring
>
> On Wed, Mar 16, 2011 at 7:12 PM, Aleksey Tsalolikhin <
> atsaloli.t...@gmail.com> wrote:
>
> Do you see this issue on 9.0.3, the current
On 3. April 2011, Joshua D. Drake wrote:
> On Sat, 2 Apr 2011 19:26:56 +0200, "Henry C."
wrote:
> > On Sat, April 2, 2011 14:17, Jens Wilke wrote:
> >> Nevertheless since at least 8.4 IMO there's no need to bother
> >> with manual vacuum any more.
>
> Uhh, this is entirely untrue. There are plent
On Sun, Apr 3, 2011 at 2:39 PM, Joshua D. Drake wrote:
> On Sat, 2 Apr 2011 19:26:56 +0200, "Henry C." wrote:
>> On Sat, April 2, 2011 14:17, Jens Wilke wrote:
>>> Nevertheless since at least 8.4 IMO there's no need to bother with
>>> manual vacuum any more.
>
> Uhh, this is entirely untrue. Ther
On Sat, 2 Apr 2011 19:26:56 +0200, "Henry C." wrote:
> On Sat, April 2, 2011 14:17, Jens Wilke wrote:
>> Nevertheless since at least 8.4 IMO there's no need to bother with
>> manual vacuum any more.
Uhh, this is entirely untrue. There are plenty of cases where 8.4
autovacuum can't cut it.
>
> S
On Sat, April 2, 2011 22:30, Tom Lane wrote:
>> Have you tried upping the aggressiveness of autovacuum?
>>
>
> I'm wondering about poor selection of the cost_delay settings in
> particular. It's quite easy to slow autovacuum to the point that it takes
> forever to do anything.
It's been on the de
Scott Marlowe writes:
> On Sat, Apr 2, 2011 at 11:26 AM, Henry C. wrote:
>> Sadly, in my case, the db is so busy that autovac processes run for weeks and
>> never catch up (insufficient h/w for the app quite frankly - the addition of
>> some more SSD drives have already helped).
> Have you tried
On Sat, April 2, 2011 21:26, Scott Marlowe wrote:
> On Sat, Apr 2, 2011 at 11:26 AM, Henry C. wrote:
>
>> On Sat, April 2, 2011 14:17, Jens Wilke wrote:
>>
>>> Nevertheless since at least 8.4 IMO there's no need to bother with
>>> manual vacuum any more.
>>
>> Sadly, in my case, the db is so busy
1 - 100 of 478 matches
Mail list logo
