I have a lot of centos servers which are running postgres. Postgres isn't used
that heavily on any of them, but lately, the stats collector process keeps
causing tons of IO load. It seems to happen only on servers with centos 5.
The versions of postgres that are running are:
8.1.18
8.2.6
8.3.1
8
On Fri, Apr 16, 2010 at 3:22 PM, Tom Lane wrote:
> Josh Kupershmidt writes:
>> name | current_setting | source
>> --+-+
>> vacuum_cost_delay | 200ms | configuration file
>> vacuum_cost_limit | 100
Josh Kupershmidt writes:
> name | current_setting | source
> --+-+
> vacuum_cost_delay| 200ms | configuration file
> vacuum_cost_limit| 100 | configuration file
> vacuum_cost_page_hit | 6
Josh Kupershmidt wrote:
And it seems the only way
to compact the pg_catalog tables is VACUUM FULL + REINDEX on 8.3 -- I
had tried the CLUSTER on my 9.0 machine and wrongly assumed it would
work on 8.3, too.
Right; that just got implemented a couple of months ago. See the news
from http://w
On Fri, Apr 16, 2010 at 2:14 PM, Greg Smith wrote:
> Josh Kupershmidt wrote:
>>
>> SELECT name, current_setting(name), source FROM pg_settings WHERE
>> source != 'default' AND name ILIKE '%vacuum%';
>> name | current_setting | source
>> --+
Josh Kupershmidt wrote:
SELECT name, current_setting(name), source FROM pg_settings WHERE
source != 'default' AND name ILIKE '%vacuum%';
name | current_setting | source
--+-+
vacuum_cost_delay| 200ms | c
On Fri, Apr 16, 2010 at 12:48 PM, Tom Lane wrote:
> Josh Kupershmidt writes:
>> Hrm, well autovacuum is at least trying to do work: it's currently
>> stuck on those bloated pg_catalog tables, of course. Another developer
>> killed an autovacuum of pg_attribute (or maybe it was pg_attrdef)
>> afte
>
> I have had some 'idle in transaction' connections hanging out from time to
> time that have caused issues on this machine that could explain the above
> perma-bloat. That is one thing that could affect the case reported here as
> well. The worst thing about those, is you can't even force
On Apr 16, 2010, at 9:48 AM, Tom Lane wrote:
> Josh Kupershmidt writes:
>> On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane wrote:
>>> Wow. Well, we have a smoking gun here: for some reason, autovacuum
>>> isn't running, or isn't doing its job if it is. If it's not running
>>> at all, that would ex
Josh Kupershmidt writes:
> On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane wrote:
>> Wow. Well, we have a smoking gun here: for some reason, autovacuum
>> isn't running, or isn't doing its job if it is. If it's not running
>> at all, that would explain failure to prune the stats collector's file
>>
I wrote:
> So this *should* have resulted in the stats file shrinking. Did you
> happen to notice if it did, after you did this?
Oh, never mind that --- I can see that it did shrink, just from counting
the write() calls in the collector's strace. So what we have here is a
demonstration that the
On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane wrote:
> Wow. Well, we have a smoking gun here: for some reason, autovacuum
> isn't running, or isn't doing its job if it is. If it's not running
> at all, that would explain failure to prune the stats collector's file
> too.
Hrm, well autovacuum is at
Josh Kupershmidt writes:
> I made a small half-empty table like this:
> CREATE TABLE test_vacuum (i int PRIMARY KEY);
> INSERT INTO test_vacuum (i) SELECT a FROM generate_series(1,50) AS a;
> DELETE FROM test_vacuum WHERE RANDOM() < 0.5;
> and then ran:
> VACUUM test_vacuum;
> whil
Josh Kupershmidt writes:
> On Fri, Apr 16, 2010 at 11:23 AM, Tom Lane wrote:
>> Hmm. That makes me wonder if autovacuum is functioning properly at all.
>> What does pg_stat_all_tables show for the last vacuum and analyze times
>> of those tables? Try something like
>>
>> select
>> relname,n_l
On Fri, Apr 16, 2010 at 11:23 AM, Tom Lane wrote:
> Josh Kupershmidt writes:
>> I'm not sure whether this is related to the stats collector problems
>> on this machine, but I noticed alarming table bloat in the catalog
>> tables pg_attribute, pg_attrdef, pg_depend, and pg_type.
>
> Hmm. That mak
Chris writes:
> After the file was made larger and I stopped the vacuum process, I started
> seeing the problem. All other postgress processes were quiet, but the stats
> collector was constantly causing anywhere from 20-60 of the IO on the server.
> Since all the other postgres processes weren't
Josh Kupershmidt writes:
> I'm not sure whether this is related to the stats collector problems
> on this machine, but I noticed alarming table bloat in the catalog
> tables pg_attribute, pg_attrdef, pg_depend, and pg_type.
Hmm. That makes me wonder if autovacuum is functioning properly at all.
On Thu, Apr 15, 2010 at 6:31 PM, Tom Lane wrote:
> Chris writes:
>> I have a lot of centos servers which are running postgres. Postgres isn't
>> used
>> that heavily on any of them, but lately, the stats collector process keeps
>> causing tons of IO load. It seems to happen only on servers wit
Chris writes:
> I have a lot of centos servers which are running postgres. Postgres isn't
> used
> that heavily on any of them, but lately, the stats collector process keeps
> causing tons of IO load. It seems to happen only on servers with centos 5.
> The versions of postgres that are running
Chris wrote:
> I have a lot of centos servers which are running postgres. Postgres isn't
> used
> that heavily on any of them, but lately, the stats collector process keeps
> causing tons of IO load. It seems to happen only on servers with centos 5.
Does this correlate to an increase in size of
2010/4/13 Chris :
> I have a lot of centos servers which are running postgres. Postgres isn't
> used
> that heavily on any of them, but lately, the stats collector process keeps
> causing tons of IO load. It seems to happen only on servers with centos 5.
> The versions of postgres that are runni
I have a lot of centos servers which are running postgres. Postgres isn't used
that heavily on any of them, but lately, the stats collector process keeps
causing tons of IO load. It seems to happen only on servers with centos 5.
The versions of postgres that are running are:
8.1.18
8.2.6
8.3.1
8
22 matches
Mail list logo
