PERFORMANCE

Hello,

I´m new on the list and I´ve been using postgres on a production
environment for 3 months. My team and I have developed a system for a
Hospital in which we work with php-postgres-linux. In the begining
everything was fine. As soon as the tables grew larger, we´ve begun to
experience an enormous performance fall. By now, the system is almost
crawling. We´ve already made all possible changes on hardware, which is a
COMPAQ ML370, scsi, with 2 PIII 800 and 768 MB RAM. The only test we will
still do is to change the scsi controller for an ultra 160. On the other
hand, searching the archives of this list, I´ve collected many complaints
about degradation of performance of insert/update, as tables are
populated. My question is:

"IS THERE ANYONE THAT CANT GIVE US SOME HOPE TO PERSIST ON POSTGRES OR IS
THIS A LOST BATLE?"

If so, we need some advice to change this situation, instead of changing
database or bying a supercomputer?

Thanks

Paulo



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: PERFORMANCE

Hi,

I think there are many people who can help you, but you have to share
more details about your configuration (for example sw, versions, etc)
scheme information can be helpful quite helpful as well.

how did you config your database? (show us a postgres.conf)

So, it's still your turn.


----- Original Message -----=20
From:
To:
Sent: Wednesday, April 17, 2002 5:26 PM
Subject: [PHP] PERFORMANCE


| Hello,
|=20
| I=B4m new on the list and I=B4ve been using postgres on a production
| environment for 3 months. My team and I have developed a system for a
| Hospital in which we work with php-postgres-linux. In the begining
| everything was fine. As soon as the tables grew larger, we=B4ve begun to
| experience an enormous performance fall. By now, the system is almost
| crawling. We=B4ve already made all possible changes on hardware, which i=
s a
| COMPAQ ML370, scsi, with 2 PIII 800 and 768 MB RAM. The only test we will
| still do is to change the scsi controller for an ultra 160. On the other
| hand, searching the archives of this list, I=B4ve collected many complain=
ts
| about degradation of performance of insert/update, as tables are
| populated. My question is:
|=20
| "IS THERE ANYONE THAT CANT GIVE US SOME HOPE TO PERSIST ON POSTGRES OR IS
| THIS A LOST BATLE?"
|=20
| If so, we need some advice to change this situation, instead of changing
| database or bying a supercomputer?
|=20
| Thanks
|=20
| Paulo
|=20
|=20
|=20
| ---------------------------(end of broadcast)---------------------------
| TIP 5: Have you checked our extensive FAQ?
|=20
| http://www.postgresql.org/users-lounge/docs/faq.html


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: PERFORMANCE

hi,

I forgot to say that you should consult the posgtres admin's manual.

+ Use explain to figure out which query should be speeded up.
+ Consider using seperate disks for logging, for database cluster and
for indexes.
+ vacuum db frequently
+ revise indices (insufficient, unused, unneccessary, too many), etc.

ps: stick to postgres, though it may be a love of war somtimes.
----- Original Message -----=20
From: "Papp, Gyozo"
To: ;
Sent: Wednesday, April 17, 2002 6:17 PM
Subject: Re: [PHP] PERFORMANCE


Hi,

I think there are many people who can help you, but you have to share
more details about your configuration (for example sw, versions, etc)
scheme information can be helpful quite helpful as well.

how did you config your database? (show us a postgres.conf)

So, it's still your turn.


----- Original Message -----=20
From:
To:
Sent: Wednesday, April 17, 2002 5:26 PM
Subject: [PHP] PERFORMANCE


| Hello,
|=20
| I=B4m new on the list and I=B4ve been using postgres on a production
| environment for 3 months. My team and I have developed a system for a
| Hospital in which we work with php-postgres-linux. In the begining
| everything was fine. As soon as the tables grew larger, we=B4ve begun to
| experience an enormous performance fall. By now, the system is almost
| crawling. We=B4ve already made all possible changes on hardware, which i=
s a
| COMPAQ ML370, scsi, with 2 PIII 800 and 768 MB RAM. The only test we will
| still do is to change the scsi controller for an ultra 160. On the other
| hand, searching the archives of this list, I=B4ve collected many complain=
ts
| about degradation of performance of insert/update, as tables are
| populated. My question is:
|=20
| "IS THERE ANYONE THAT CANT GIVE US SOME HOPE TO PERSIST ON POSTGRES OR IS
| THIS A LOST BATLE?"
|=20
| If so, we need some advice to change this situation, instead of changing
| database or bying a supercomputer?
|=20
| Thanks
|=20
| Paulo
|=20
|=20
|=20
| ---------------------------(end of broadcast)---------------------------
| TIP 5: Have you checked our extensive FAQ?
|=20
| http://www.postgresql.org/users-lounge/docs/faq.html


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: PERFORMANCE

Folks,

I have proposed a new list, PGSQL-PERFORM. This list should be up
soon, and should focus the discussion of performance issues.

In the meantime, please look at the articles at:
http://techdocs.postgresql.org

-Josh Berkus

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: PERFORMANCE

The single most important question - have you logged into your database and
run:

vacuum analyze;

Recently?

You can use the vacuumdb commandline utility to do this once a day from a
cron job.

Chris

> -----Original Message-----
> From: pgsql-php-owner@postgresql.org
> [mailto:pgsql-php-owner@postgresql.org]On Behalf Of
> valeria@saolucas-se.com.br
> Sent: Wednesday, 17 April 2002 11:27 PM
> To: pgsql-php@postgresql.org
> Subject: [PHP] PERFORMANCE
>
>
> Hello,
>
> I´m new on the list and I´ve been using postgres on a production
> environment for 3 months. My team and I have developed a system for a
> Hospital in which we work with php-postgres-linux. In the begining
> everything was fine. As soon as the tables grew larger, we´ve begun to
> experience an enormous performance fall. By now, the system is almost
> crawling. We´ve already made all possible changes on hardware, which is a
> COMPAQ ML370, scsi, with 2 PIII 800 and 768 MB RAM. The only test we will
> still do is to change the scsi controller for an ultra 160. On the other
> hand, searching the archives of this list, I´ve collected many complaints
> about degradation of performance of insert/update, as tables are
> populated. My question is:
>
> "IS THERE ANYONE THAT CANT GIVE US SOME HOPE TO PERSIST ON POSTGRES OR IS
> THIS A LOST BATLE?"
>
> If so, we need some advice to change this situation, instead of changing
> database or bying a supercomputer?
>
> Thanks
>
> Paulo
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: PERFORMANCE

Le Mercredi 17 Avril 2002 17:26, valeria@saolucas-se.com.br a =E9crit :
> "IS THERE ANYONE THAT CANT GIVE US SOME HOPE TO PERSIST ON POSTGRES OR IS
> THIS A LOST BATLE?"

Dear Valeria,

PostgreSQL and PHP are the best available solution today. PostgreSQL is muc=
h=20
faster than any other open-source database because it gives access to a wid=
e=20
range of optimization techniques.

Maybe you should try these steps:

1) First step: system optimization

a) Increase shared memory

A good article can be found at=20
http://www.phpbuilder.com/columns/smith20010821.php3

In your case, PostgreSQL probably need a shared-memory increase (you have 7=
68=20
MB RAM, right?). Try to set shmax to 256000000. This will load schema objec=
ts=20
and data in memory.

b) Upgrade to PostgreSQL latest release

Upgrade PostgreSQL to the latest 7.2.1 release which includes important=20
bugfixes. If you wish to upgrade easily, it is recommanded to use the lates=
t=20
RPMs which can be found on http://www.rpmfind.net.

2) Second step : software optimization

a) Analyse queries using EXPLAIN

The basis of software optimization starts with running the EXPLAIN command =
as=20
stated in http://www.postgresql.org/idocs/index.php?sql-explain.html

b) Teach the query planner using VACUUM ANALYSE

VACUUM ANALYSE should be run every day to teach the query planner. This can=
be=20
done using "cron jobs" (Webmin provides an easy to use interface if you don=
't=20
want to run them manually.

c) Reduce PHP <-> PostgreSQL number of queries

Have a deep look into your PHP code. You should try to limit the number of=
=20
queries between PostgreSQL and PHP. This should be the case with any=20
database.

- SELECT foo FROM bar LIMIT x : avoid SELECT * and use LIMIT to retrieve al=
l=20
records at once. Do not select records "one by one".
- When performing UPDATES on multiple tables, try to use triggers to perfor=
m=20
all updates in one query.

d) Use a server-side language
Ultimately, using a server-side language is a "killer-solution". PostgreSQL=
=20
supports many server-side languages like SQL, PLpgSQL, PLpgPERL, PLpgPYTHON=
,=20
PLpgTK and soon PLpgJAVA. The easiest language is PLpgSQL

You migt ask why use a server-side language with PHP at the same time? The=
=20
answer is quite simple : all transactional steps should be carried on=20
PostgreSQL side in ONE SINGLE STEP.

For example : when creating a patient record, you may need to update a numb=
er=20
of other fields, records, tables, etc... The advantage of asking PostgreSQL=
=20
to perform these steps are:
i) use transactions <-> comply with ACID rules.
ii) reduce network traffic.
iii) boost speed dramatically.

In the end, you should be aware that the software optimization approach is =
10=20
times more important than hardware optimization. A well-structure database=
=20
will run faster on a $100 computer (i586, IDE drives, 128Mo) than on a doub=
le=20
pentium with SCSI drives.

This is why PostgreSQL is by definition superior to beginner tools like MyS=
QL.=20
The only drawback is that, like for Oracle or IBM DB2, a good knowledge of=
=20
database internals is necessary.

Last of all: maybe you should try pgAdmin2 (http://pgadmin.postgresql.org),=
=20
PostgreSQL Windows GUI. This will give you access to all PostgreSQL objects=
=20
(tables, views, triggers, functions, rules, etc...) and allow you to use an=
y=20
server-side language.

Do not hesitate to write back on the mailing list,
Cheers, Jean-Michel POURE





---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: PERFORMANCE

another articles with pgsql performance tips:

http://candle.pha.pa.us/main/writings/pgsql/performace.pdf



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: PERFORMANCE

Le Jeudi 18 Avril 2002 21:54, valeria@saolucas-se.com.br a =E9crit :
> Top result when the server is almost stoping ...
>
> 4:19pm up 2 days, 3:04, 2 users, load average: 4.26, 4.06, 4.59
> 102 processes: 96 sleeping, 6 running, 0 zombie, 0 stopped
> CPU0 states: 83.0% user, 15.0% system, 0.0% nice, 0.1% idle
> CPU1 states: 82.0% user, 16.0% system, 0.0% nice, 0.1% idle
> Mem: 771384K av, 699676K used, 71708K free, 0K shrd, 6488K buff
> Swap: 522232K av, 77420K used, 444812K free

Load avarage is 4.26. A large query seems to overflow the system.
Type command "top" to make sure PostgreSQL is really at stake.

Then, modify /etc/init.d/postgresql to run PostgreSQL in debug mode:
postmaster ... -d debug-level=20

Restart PostgreSQL service entering:=20
"service postgresql restart"

This will log all queries and may help you understand what is going on.

Best regards,
Jean-Michel POURE

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: PERFORMANCE

> "IS THERE ANYONE THAT CANT GIVE US SOME HOPE TO PERSIST ON POSTGRES OR IS
> THIS A LOST BATLE?"

I figured out the only way to "crash" PostgreSQL was entering an endless lo=
op.=20

This happened to me sereval times (a trigger was spreading to another trigg=
er,=20
which in turned called back the first trigger; or simply an endless loop in=
a=20
PLpgSQL function). When entering an endless loop, PostgreSQL activity climb=
s=20
up, untill it reaches 100% user activity and dramaticaly slows down Linux.=
=20
Isn't what happens to you? Turn on debuging to notice where the endless loo=
p=20
happens.

This will be probably fixed in the future with an "endless loop protection"=
=20
feature.

Feel free to give us your feedback as soon as you discovers what is going o=
n.

Cheers,
Jean-Michel





---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: PERFORMANCE

Le Jeudi 18 Avril 2002 10:55, Papp, Gyozo a =E9crit :
> another articles with pgsql performance tips:
> http://candle.pha.pa.us/main/writings/pgsql/performace.pdf

Very interesting article. It should be included in the Administrator on-lin=
e=20
manual.

My personal feeling is that Valeria has an endless loop, otherwize Linux an=
d=20
PostgreSQL would not slow down dramatically. Valeria: could you turn=20
debugging on and provide us with more feedback.

Cheers, Jean-Michel POURE


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: PERFORMANCE

Hi,

Paulo wrote:

| I=B4m new on the list and I=B4ve been using postgres on a production=20
| environment for 3 months. My team and I have developed a system for a
| Hospital in which we work with php-postgres-linux. In the begining=20
| everything was fine. As soon as the tables grew larger, we=B4ve begun to
| experience an enormous performance fall. By now, the system is

and Jean-Michel wrote:
| I figured out the only way to "crash" PostgreSQL was entering an endless =
loop.=20

| This happened to me sereval times (a trigger was spreading to another tri=
gger,=20
| which in turned called back the first trigger; or simply an endless loop =
in a=20
| PLpgSQL function). When entering an endless loop, PostgreSQL activity cli=
mbs=20
| up, untill it reaches 100% user activity and dramaticaly slows down Linux=
..=20
| Isn't what happens to you? Turn on debuging to notice where the endless l=
oop=20
| happens.

Yes, Jean-Michel is right, please try to debug what queries sent to the bac=
kend.
BTW, I'm doubt that it would be an endless loop rather than mis configured =
or
misconcepted (if you understand what I mean) database design.
Paulo stated there were times when everything was fine, pg just recently sl=
owed
down. It might be a very evil trigger which starts to misbehave as tables g=
row.
BTW, it could be, really. If that trigger works too slowly...





---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: PERFORMANCE

http://candle.pha.pa.us/main/writings/pgsql/

browse this directory, there is another online (HTML) performance
docs from Bruce. It contains more proccessable :)) informations.
If not so, I'm looking the exact URL from the archive.

----- Original Message -----=20
From: "Jean-Michel POURE"
To: "Papp, Gyozo" ; ; gsql-php@postgresql.org>
Sent: Saturday, April 20, 2002 9:16 AM
Subject: Re: [PHP] PERFORMANCE


Le Jeudi 18 Avril 2002 10:55, Papp, Gyozo a =E9crit :
> another articles with pgsql performance tips:
> http://candle.pha.pa.us/main/writings/pgsql/performace.pdf


Very interesting article. It should be included in the Administrator on-lin=
e=20
manual.

My personal feeling is that Valeria has an endless loop, otherwize Linux an=
d=20
PostgreSQL would not slow down dramatically. Valeria: could you turn=20
debugging on and provide us with more feedback.

Cheers, Jean-Michel POURE


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: PERFORMANCE

I'm having a problem with this link.... 404 -- performance is
misspelled...

What a great document, though! In fact, what a great directory. May I
use the advantages presentation? May I forward the link to others who may
be interested?

On Sat, 20 Apr 2002, Jean-Michel POURE wrote:

> Le Jeudi 18 Avril 2002 10:55, Papp, Gyozo a =E9crit :
> > another articles with pgsql performance tips:
> > http://candle.pha.pa.us/main/writings/pgsql/performace.pdf
>=20
> Very interesting article. It should be included in the Administrator on-l=
ine=20
> manual.
>=20
> My personal feeling is that Valeria has an endless loop, otherwize Linux =
and=20
> PostgreSQL would not slow down dramatically. Valeria: could you turn=20
> debugging on and provide us with more feedback.
>=20
> Cheers, Jean-Michel POURE
>=20
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>=20
> http://archives.postgresql.org
>=20

Chadwick Rolfs - cmr@gis.net
Cleveland State University - Student
Music Major - The Holden Arboretum Volunteer
Computer Programmer - Student Employee
--*I finally found powdered water;
I just can't figure out what to add to it*--


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html