I am posting back to let you know that the DB is working fine since the changes
in the autovacuum settings.
I am including the changes I made for later reference to anyone that may face
similar issues.
Thank you all for your time and help !
On Sep 28, 2012, at 9:52, Kiriakos Tsourapas
-Original Message-
From: Thomas Kellerer [mailto:spam_ea...@gmx.net]
Sent: Tuesday, September 25, 2012 7:24 AM
To: pgsql-performance@postgresql.org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Kiriakos Tsourapas, 25.09.2012 13:01:
Thank you,
I will take this
On Tue, Sep 25, 2012 at 5:24 AM, Thomas Kellerer spam_ea...@gmx.net wrote:
I think an upgrade from 8.3 to 8.4 was harder due to the removal of a lot
of implicit type casts.
FYI that was from 8.2 to 8.3 that implicit casts were removed.
--
Sent via pgsql-performance mailing list
Dear all,
Just letting you know that making the autovacuum policy more aggressive seems
to have fixed the problem.
It's been 4 days now and everything is running smoothly.
Just a reminder, what I changed was :
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.005
making
Kiriakos Tsourapas, 25.09.2012 13:01:
Thank you,
I will take this into consideration, since upgrading to 9 will be much harder I
assume...
I think an upgrade from 8.3 to 8.4 was harder due to the removal of a lot of
implicit type casts.
8.4 to 9.x shouldn't be that problematic after all
Dear all,
I am taking your suggestions one step at a time.
I changed my configuration to a much more aggressive autovacuum policy (0.5%
for analyzing and 1% for autovacuum).
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
[resending because I accidentally failed to include the list]
Kiriakos Tsourapas wrote:
I am taking your suggestions one step at a time.
I changed my configuration to a much more aggressive autovacuum
policy (0.5% for analyzing and 1% for autovacuum).
autovacuum_naptime = 1min
Hi Kevin,
On Sep 26, 2012, at 14:39, Kevin Grittner wrote:
I am concerned that your initial email said that you had this
setting:
autovacuum_naptime = 28800
This is much too high for most purposes; small, frequently-modified
tables won't be kept in good shape with this setting.
Thank you,
I will take this into consideration, since upgrading to 9 will be much harder I
assume...
On Sep 24, 2012, at 15:23, MirrorX wrote:
i remember having a server with 8.4.4 where we had multiple problems with
autovacuum.
if i am not mistaken there are some bugs related with vacuum
Hi Mark,
When the problem appears, vacuuming is not helping. I ran vacuum manually and
the problem was still there. Only full vacuum worked.
As far as I have understood, autovacuuming is NOT doing FULL vacuum. So,
messing around with its values should not help me in any way.
Thanks
Hi,
Suggestion noted.
Nevertheless, I cannot imagine what it would help. Actually, the cost_delay
makes autovacuum freeze when it takes more time than expected, therefore,
having it enabled should help the system.
I may try it as a last possible resolution (remember that I have to wait for a
Hi,
On 25 September 2012 21:10, Kiriakos Tsourapas kts...@gmail.com wrote:
Suggestion noted.
Nevertheless, I cannot imagine what it would help. Actually, the cost_delay
makes autovacuum freeze when it takes more time than expected, therefore,
having it enabled should help the system.
Yes,
Hi,
The problem : Postgres is becoming slow, day after day, and only a full vacuum
fixes the problem.
Information you may need to evaluate :
The problem lies on all tables and queries, as far as I can tell, but we can
focus on a single table for better comprehension.
The queries I am running
Sorry, forgot to mention the most obvious and important information :
My postgres is 8.4.2
On Sep 24, 2012, at 13:33, Kiriakos Tsourapas wrote:
Hi,
The problem : Postgres is becoming slow, day after day, and only a full
vacuum fixes the problem.
Information you may need to evaluate :
Hello,
1) upgrade your PostgreSQL installation, there have been numerous
bugfixes releases since 8.4.2
2) you'll have to show us an explain analyze of the slow queries. If I
take a look at those you provided everything run i less than 1ms.
3) with 200 records you'll always have a seqscan
4)
On Monday, September 24, 2012 02:21:09 PM Julien Cigar wrote:
5) synchronous_commit = off should only be used if you have a
battery-backed write cache.
Huh? Are you possibly confusing this with full_page_writes?
Greetings,
Andres
--
Andres Freund
On 09/24/2012 14:34, Andres Freund wrote:
On Monday, September 24, 2012 02:21:09 PM Julien Cigar wrote:
5) synchronous_commit = off should only be used if you have a
battery-backed write cache.
Huh? Are you possibly confusing this with full_page_writes?
indeed...! sorry for that
(note that
Hi,
Thank you for your response.
Please find below my answers/comments.
On Sep 24, 2012, at 15:21, Julien Cigar wrote:
Hello,
1) upgrade your PostgreSQL installation, there have been numerous bugfixes
releases since 8.4.2
Not possible right now. It will have to be the last solution.
2)
On Monday, September 24, 2012 02:53:59 PM Julien Cigar wrote:
On 09/24/2012 14:34, Andres Freund wrote:
On Monday, September 24, 2012 02:21:09 PM Julien Cigar wrote:
5) synchronous_commit = off should only be used if you have a
battery-backed write cache.
Huh? Are you possibly
On 09/24/2012 15:51, Kiriakos Tsourapas wrote:
Hi,
Thank you for your response.
Please find below my answers/comments.
On Sep 24, 2012, at 15:21, Julien Cigar wrote:
Hello,
1) upgrade your PostgreSQL installation, there have been numerous bugfixes
releases since 8.4.2
Not possible right
i remember having a server with 8.4.4 where we had multiple problems with
autovacuum.
if i am not mistaken there are some bugs related with vacuum until 8.4.7.
i would suggest you to upgrade to the latest 8.4.x version
BR,
--
View this message in context:
Hi,
On 24 September 2012 20:33, Kiriakos Tsourapas kts...@gmail.com wrote:
The problem : Postgres is becoming slow, day after day, and only a full
vacuum fixes the problem.
Information you may need to evaluate :
The problem lies on all tables and queries, as far as I can tell, but we can
On 24/09/12 22:33, Kiriakos Tsourapas wrote:
Hi,
The problem : Postgres is becoming slow, day after day, and only a full vacuum
fixes the problem.
My postgresql.conf file :
==
port = 5433 # (change requires restart)
max_connections = 100
23 matches
Mail list logo
