RE: Strange 3 minute sleep on Publish

Have you vacuumed your DB lately?

-----Original Message-----
From: Steffen Schwigon [mailto:schwigonwebit.de]

Sent: Friday, July 20, 2007 9:53 AM
To: userslists.bricolage.cc
Subject: Re: Strange 3 minute sleep on Publish

Kenneth Marshall <ktmrice.edu> writes:
> Steffen,
>
> Is this that GECO threshold problem that David posted
about
> earlier. Try bumping it.

Just tried. It didn't help.

I'm just installing another Postgres (8.2.4), to see whether
it helps.

(Greeti+Tha)nX
Steffen 
-- 
Steffen Schwigon <schwigonwebit.de>
Dresden Perl Mongers <http://dresden-pm.org/>

Deutscher Perl-Workshop <http://www.perl-work
shop.de/>

"Beaudet, David P." <D-BeaudetNGA.GOV> writes:
> Have you vacuumed your DB lately?

I'm not sure what vacuumed means? Emptied or cleared?
I occasionally dump/restore it from another Bric instance.

Do you have a hint in mind?

GreetinX
Steffen 
-- 
Steffen Schwigon <http://renormalist.net>

On Fri, 20 Jul 2007, Steffen Schwigon wrote:
> "Beaudet, David P." <D-BeaudetNGA.GOV> writes:
>> Have you vacuumed your DB lately?
>
> I'm not sure what vacuumed means? Emptied or cleared?
> I occasionally dump/restore it from another Bric
instance.
>
> Do you have a hint in mind?

What is:

select count(*) from resource;

?

We recently went through a process of clearing unnecessary
stuff out of the database which has really sped everything
up...
One of the things to clear out (and one of the things I've
found takes a relatively long time during SQL queries)
is the resource table.

Steffen Schwigon <schwigonwebit.de> writes:
> "Beaudet, David P." <D-BeaudetNGA.GOV> writes:
>> Have you vacuumed your DB lately?
>
> I'm not sure what vacuumed means? Emptied or cleared?
> I occasionally dump/restore it from another Bric
instance.
>
> Do you have a hint in mind?

In the meantime I replaced the 8.1.8 version with 8.2.4. Now
it works
again.

Unfortunately I cannot try whether one of your ideas would
have
helped. Anyway, thanks for all your ideas.

Steffen
-- 
Steffen Schwigon <schwigonwebit.de>
Dresden Perl Mongers <http://dresden-pm.org/>

Deutscher Perl-Workshop <http://www.perl-work
shop.de/>

On Fr, 2007-07-20 at 16:12 +0200, Steffen Schwigon wrote:
> "Beaudet, David P." <D-BeaudetNGA.GOV> writes:
> > Have you vacuumed your DB lately?
> 
> I'm not sure what vacuumed means? Emptied or cleared?

VACUUM physically removes deleted rows from the storage
system and
updates the query planner statistics.

> I occasionally dump/restore it from another Bric
instance.
> 
> Do you have a hint in mind?

I had an similar issue with another postgres database in
another
project. I generated some test data (usually around 1
million rows each
test run), cleared (not deleted!) the table and made another
test run.
The issue appearing was, that postgres isn't deleting the
rows
physicaly, it just marks them as deleted (which led to the
side effect
that the estimated row count in pgadmin III increased by 1
million with
each test run). So the table became slower and slower with
each test run
until i deleted and recreated it after each simple select
for the
primary key went for minutes.

Maybe you have similar problem here if you often
delete/insert lots of
rows into the same table.

Try vacuuming the table and delete the whole table instead
of all
records in it if you do a dump/restore.

> 
> GreetinX
> Steffen 

Greetings,

Johannes

Then it probably needs another index or something.

Best,

David

Sent from my iPhone

On Jul 20, 2007, at 7:23 AM, Scott Lanning <lanningswho.int> wrote:

> On Fri, 20 Jul 2007, Steffen Schwigon wrote:
>> "Beaudet, David P." <D-BeaudetNGA.GOV> writes:
>>> Have you vacuumed your DB lately?
>>
>> I'm not sure what vacuumed means? Emptied or
cleared?
>> I occasionally dump/restore it from another Bric
instance.
>>
>> Do you have a hint in mind?
>
> What is:
>
> select count(*) from resource;
>
> ?
>
> We recently went through a process of clearing
unnecessary
> stuff out of the database which has really sped
everything up...
> One of the things to clear out (and one of the things
I've
> found takes a relatively long time during SQL queries)
> is the resource table.