PostgreSQL Slowness?

Folks,

This afternoon I imported 1,800 authors into Bricolage. The
formerly  
snappy Admin->Publishing->Contributors section
immediately became  
sluggish in all interactions with it. Simply creating a new
author  
record requires a 60-90 delay for the insertion. Checking
out the  
server processes, I found that Postgres is eating up 97-99%
of the CPU  
cycles during that period with its confusingly-named
"postmaster"  
process.

Postgres is installed solely for Bricolage -- it's not being
accessed  
for any other purpose on this dedicated server -- and I'm
the only  
here using Bricolage. I'm running PostgreSQL 7.4.17 and
Bricolage  
1.10.3. Some quality time reading through list archives
doesn't yield  
anything that would seem to explain this.

Being a MySQL guy, I have no idea of how to delve into
things to make  
sure that everything is indexed properly, not crufty, etc.,
but my  
assumption is that Bricolage would take care of all of this
on its  
own. Is this a known problem with Bricolage, a result of a
large  
number of contributors, or maybe a known problem with
PostgreSQL?

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223

Folks,

To spare anybody else the trouble, I think I've figured out
my own  
problem.

I've been going through and correcting all of the names that
didn't  
import quite right (accented characters, en dashes, etc.),
which  
requires listing out all 1,800 contributors. That means
that, after  
each POST of the changes, Bricolage needs to return the list
of 1,800  
contributors. Now, there's just no reason why it should take
60-90  
seconds to pull 1,800 rows out of Postgres, but if I'm
working on only  
a subset (say, only those with the last name
"Smith") there's only  
perhaps a 5-10 second delay after submitting changes to a  
contributor's record.

The solution is simply not to list all contributors in one
fell swoop  
and, if you do, expect it to be slow. There are worse
things.

Best,
Waldo


On Apr 21, 2008, at 3:56 PM, Waldo Jaquith wrote:
> Folks,
>
> This afternoon I imported 1,800 authors into Bricolage.
The formerly  
> snappy Admin->Publishing->Contributors section
immediately became  
> sluggish in all interactions with it. Simply creating a
new author  
> record requires a 60-90 delay for the insertion.
Checking out the  
> server processes, I found that Postgres is eating up
97-99% of the  
> CPU cycles during that period with its
confusingly-named  
> "postmaster" process.
>
> Postgres is installed solely for Bricolage -- it's not
being  
> accessed for any other purpose on this dedicated server
-- and I'm  
> the only here using Bricolage. I'm running PostgreSQL
7.4.17 and  
> Bricolage 1.10.3. Some quality time reading through
list archives  
> doesn't yield anything that would seem to explain
this.
>
> Being a MySQL guy, I have no idea of how to delve into
things to  
> make sure that everything is indexed properly, not
crufty, etc., but  
> my assumption is that Bricolage would take care of all
of this on  
> its own. Is this a known problem with Bricolage, a
result of a large  
> number of contributors, or maybe a known problem with
PostgreSQL?
>
> Best,
> Waldo

Waldo Jaquith <waldovqronline.org> writes:
> Folks,
>
> This afternoon I imported 1,800 authors into Bricolage.
The formerly
> snappy Admin->Publishing->Contributors section
immediately became
> sluggish in all interactions with it.

Did you try:

 $ psql ...      # into your bric db
 bric=> vacuum analyze;
 bric=> vacuum;


That's some kind of garbage collection in Postgres.

I had learned context about this in Bricolage in this
thread:

  http://www.gossamer-threa
ds.com/lists/bricolage/users/10749?search_string=vacuum;#107
49

and the surrounding posts, where someone mentioned:

  http://www.postgresql.org/docs/8.1/static/maintenance.
html

for some background info.

Not sure if it all maps exactly to your 7.x postgres, but I
think it
originally was a 7.x issue, so it should apply.

Regards,
Steffen
-- 
Steffen Schwigon <ss5renormalist.net>
Dresden Perl Mongers <http://dresden-pm.org/>

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

On Apr 21, 2008, at 12:56, Waldo Jaquith wrote:

> Being a MySQL guy, I have no idea of how to delve into
things to  
> make sure that everything is indexed properly, not
crufty, etc., but  
> my assumption is that Bricolage would take care of all
of this on  
> its own. Is this a known problem with Bricolage, a
result of a large  
> number of contributors, or maybe a known problem with
PostgreSQL?

Be sure to read Bric:BA for
database tuning tips. I find that  
following the tips there nearly always takes care of
performance  
problems.

Oh, and you really should upgrade your PostgreSQL, too. 7.4
quite old  
(four major releases since then!).

Best,

David

On Apr 22, 2008, at 2:07 AM, David E. Wheeler wrote:
> Oh, and you really should upgrade your PostgreSQL, too.
7.4 quite  
> old (four major releases since then!).

I'd love to, but the dependencies to get Bricolage installed
in the  
first place were so hair-pullingly, brain-achingly difficult
to  
resolve that I don't dare change anything that I don't
absolutely have  
to.

Thank you, David and Steffen, for the pointers on PostgreSQL
 
maintenance. I'll give them a whirl this afternoon.

Best,
Waldo

---
Virginia Quarterly Review
One West Range, Box 400223
University of Virginia
Charlottesville, VA 22904-4223

On Apr 22, 2008, at 07:40, Waldo Jaquith wrote:

> I'd love to, but the dependencies to get Bricolage
installed in the  
> first place were so hair-pullingly, brain-achingly
difficult to  
> resolve that I don't dare change anything that I don't
absolutely  
> have to.

Well, the PostgreSQL server is just about the easiest part
of it, IME.  
A major PostgreSQL version upgrade is kind of a PITA because
you have  
to dump and reload the database, but if you have all your
other  
dependencies in place, the simplest thing to do would be to
`make  
clone`, upgrade PostgreSQL, and then `make && make
install` from the  
cloned package.

> Thank you, David and Steffen, for the pointers on
PostgreSQL  
> maintenance. I'll give them a whirl this afternoon.

Good luck!

David