16 квітня 2010 р. 17:19 Tom Lane написав:
> =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= writes:
> > I've thought and someone in this list've told me that this should be done
> > automatically.
>
> As was pointed out, even if we had such logic it wouldn't apply in this
> example, because the equality c
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
How many autovac workers are there?
Max_workers is set to 3. However, I've never seen more than one active
at a time.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.c
Josh Berkus wrote:
> Tom,
>
> Neither database has and per-table autovacuum settings.
>
> However, since this is a production database, I had to try
> something, and set vacuum_cost_limit up to 1000. The issue with
> vacuuming one page at a time went away, or at least I have not seen
> it repeat
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
Tom,
Neither database has and per-table autovacuum settings.
However, since this is a production database, I had to try something,
and set vacuum_cost_limit up to 1000. The issue with vacuuming one page
at a time went away, or at least I have not seen it repeat in the last
16 hours.
--
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
Yeb Havinga writes:
> New expensive planner infrastructure to support from a>b and b>c infer
> a>c, yes.
> But I wonder if something like Leibniz's principle of identity holds for
> members of the same equivalence class, e.g. like if x,y are both members
> of the same EC, then for every predic
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
Tom Lane wrote:
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= writes:
I've thought and someone in this list've told me that this should be done
automatically.
No, that's not true. We do make deductions about transitive equalities,
ie, given WHERE a=b AND b=c the planner will infer a=c and us
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
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= writes:
> I've thought and someone in this list've told me that this should be done
> automatically.
No, that's not true. We do make deductions about transitive equalities,
ie, given WHERE a=b AND b=c the planner will infer a=c and use that if
it's helpful.
16 квітня 2010 р. 16:21 Yeb Havinga написав:
> Віталій Тимчишин wrote:
>
>>
>> BTW: Changing slow query to inner joins do not make it fast
>>
> I'm interested to see the query andplan of the slow query with inner joins.
>
>
> Here you are. The query:
select * from company this_ inner join compan
Віталій Тимчишин wrote:
BTW: Changing slow query to inner joins do not make it fast
I'm interested to see the query andplan of the slow query with inner joins.
Another thing is it seems that the number of rows guessed is far
off from the actual number of rows, is the number 500
16 квітня 2010 р. 11:25 Hannu Krosing написав:
> On Fri, 2010-04-16 at 11:02 +0300, Віталій Тимчишин wrote:
> > Hello.
> >
> >
> > I have a query that performs very poor because there is a limit on
> > join column that is not applied to other columns:
> >
> >
> > select * from company this_ left
16 квітня 2010 р. 11:31 Yeb Havinga написав:
> Віталій Тимчишин wrote:
>
>> Hello.
>>
>> I have a query that performs very poor because there is a limit on join
>> column that is not applied to other columns:
>>
>> select * from company this_ left outer join company_tag this_1_ on
>> this_.id=thi
Віталій Тимчишин wrote:
Hello.
I have a query that performs very poor because there is a limit on
join column that is not applied to other columns:
select * from company this_ left outer join company_tag this_1_ on
this_.id=this_1_.company_id left outer join company_measures
companymea2_ on
Hello.
I have a query that performs very poor because there is a limit on join
column that is not applied to other columns:
select * from company this_ left outer join company_tag this_1_ on
this_.id=this_1_.company_id left outer join company_measures companymea2_ on
this_.id=companymea2_.company
30 matches
Mail list logo
