Forum OpenACS Q&A: Very very bad performance problem in 4.6.1 final

1: Very very bad performance problem in 4.6.1 final

Posted by Claudio Pasolini on 03/26/03 09:08 AM

Hi all,

I installed OpenACS 4.6.1 final on a RedHat 8.0 and Postgresql 7.2.3 following the directives of the installation instructions.

All is running fine except the groups management programs wich are very very slow. Particularly bad are /admin/group-types/one?group_type=group and /admin/relations-add

The relations-add program is so slow, both adding composition and membership relations, that I can't even complete its execution remotely because it times out.

I am under big pressure because I should setup a working server with a dozen of groups and about 150 members by next Friday and so any advice is very welcome.

TIA

2: Re: Very very bad performance problem in 4.6.1 final (response to 1)

Posted by Jeff Davis on 03/26/03 09:36 AM

did you do vacuum your db after populating it?
If not try "vacuumdb -f -v -z yourdb" and see if that helps.

3: Re: Very very bad performance problem in 4.6.1 final (response to 1)

Posted by Claudio Pasolini on 03/26/03 10:17 AM

Thank you Jeff, now it works!

This is my first installation with postgres 7.2.3 and I never observed a similar difference in performance with 7.1 : do you suggest running vacuumdb dayly?

As a side note this is also my first installation from source (I always used Jonathan's RPM) and another oddity is that when doing

ps -ef | grep nsd

I was used to see several threads, while now I see only one nsd process. Is this normal?

Thank You again,
Claudio

4: Re: Very very bad performance problem in 4.6.1 final (response to 3)

Posted by Matthew Walker on 03/26/03 11:41 AM

As a side note this is also my first installation from source (I always used Jonathan's RPM) and another oddity is that when doing

ps -ef | grep nsd

I was used to see several threads, while now I see only one nsd process. Is this normal?

Redhat has updated ps so that it does not expose linux's internal mapping of threads to processes starting with version 2.0.7-16 of the procps package.

5: Re: Very very bad performance problem in 4.6.1 final (response to 3)

Posted by Roberto Mello on 03/26/03 04:29 PM

Claudio,

VACUUMing your database is *crucial* to performance. It needs to be done at least once daily, and several times a day if your database receives lots of updates, inserts or deletes.

Please read the PostgreSQL Administrator's Guide and famialirize yourself with what it takes to maintain a PostgreSQL server.

Also, you're probably running with PostgreSQL defaults, which are very conservative. If you have a more modern machine, you could up those defaults and get much better performance. Search google for "postgresql performance".

-Roberto

6: Re: Very very bad performance problem in 4.6.1 final (response to 1)

Posted by Don Baccus on 03/26/03 09:46 PM

We should probably have the APM vacuum analyze after packages are installed ...

Though maybe that justs masks the problem - people gotta learn to run vacuum frequently.

7: Re: Very very bad performance problem in 4.6.1 final (response to 6)

Posted by Claudio Pasolini on 03/26/03 10:21 PM

Thank You all for your advice.

You bet I learned the vacuum lesson, but the fact is that I'm running several servers (with postgresql 7.1 and oacs 3.x) wich perform at lightning speed even if I run vacuum analyze only when I remember to do so.

One argument I have always used in favor of PostgreSQL against Oracle was that it didn't require me to spend more than one hour per year in DB tuning: it seems that combining oacs.4.x with PostgreSQL 7.2.x will require more, but oacs.4.x is so rewarding to fully justify these complications.

8: Re: Very very bad performance problem in 4.6.1 final (response to 1)

Posted by Don Baccus on 03/26/03 11:05 PM

There are a couple of issues here ...

ANALYZE: Without statistical data, the Postgres planner often makes very bad choices which causes queries to run very slowly. Much more often than Oracle. After you run ANALYZE on a fairly large set of data, it figures out it should use indexes etc. Unless the distribution of your data changes greatly you don't really need to run ANALYZE all that often (though there is no reason not to)
VACUUM: PostgreSQL doesn't reclaim space unless you tell it to explicitly. If you've got PG databases lying around that you're not VACUUMing then they'll still run fast (assuming you've run ANALYZE after your tables have grown to reasonable size) but ... they'll be much larger than necessary. Which can slow things down by causing more disk I/O etc but not nearly as drastically as the bad query plans that result from not running ANALYZE.
We do lots of joins on three, four, five or more tables in OpenACS and that makes the bad decisions made by PG when you don't run ANALYZE even worse.

An easy way to run VACUUM ANALYZE is to mount the cronjob package and run it from there. Then you have a nice simple web interface for scheduling it...