Thread: PERFORMANCE

PERFORMANCE

From
Date:
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



Re: PERFORMANCE

From
"Papp, Gyozo"
Date:
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 -----
From: <valeria@saolucas-se.com.br>
To: <pgsql-php@postgresql.org>
Sent: Wednesday, April 17, 2002 5:26 PM
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


Re: PERFORMANCE

From
"Papp, Gyozo"
Date:
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 -----
From: "Papp, Gyozo" <pgerzson@freestart.hu>
To: <valeria@saolucas-se.com.br>; <pgsql-php@postgresql.org>
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 -----
From: <valeria@saolucas-se.com.br>
To: <pgsql-php@postgresql.org>
Sent: Wednesday, April 17, 2002 5:26 PM
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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



Re: PERFORMANCE

From
"Josh Berkus"
Date:
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

Re: PERFORMANCE

From
"Christopher Kings-Lynne"
Date:
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
>


Re: PERFORMANCE

From
Jean-Michel POURE
Date:
Le Mercredi 17 Avril 2002 17:26, valeria@saolucas-se.com.br a écrit :
> "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 much
faster than any other open-source database because it gives access to a wide
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
http://www.phpbuilder.com/columns/smith20010821.php3

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

b) Upgrade to PostgreSQL latest release

Upgrade PostgreSQL to the latest 7.2.1 release which includes important
bugfixes. If you wish to upgrade easily, it is recommanded to use the latest
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
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
done using "cron jobs" (Webmin provides an easy to use interface if you don't
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
queries between PostgreSQL and PHP. This should be the case with any
database.

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

d) Use a server-side language
Ultimately, using a server-side language is a "killer-solution". PostgreSQL
supports many server-side languages like SQL, PLpgSQL, PLpgPERL, PLpgPYTHON,
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
answer is quite simple : all transactional steps should be carried on
PostgreSQL side in ONE SINGLE STEP.

For example : when creating a patient record, you may need to update a number
of other fields, records, tables, etc... The advantage of asking PostgreSQL
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
times more important than hardware optimization. A well-structure database
will run faster on a $100 computer (i586, IDE drives, 128Mo) than on a double
pentium with SCSI drives.

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

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

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





Re: PERFORMANCE

From
"Papp, Gyozo"
Date:
another articles with pgsql performance tips:

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



Re: PERFORMANCE

From
Jean-Michel POURE
Date:
Le Jeudi 18 Avril 2002 21:54, valeria@saolucas-se.com.br a écrit :
> 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

Restart PostgreSQL service entering:
"service postgresql restart"

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

Best regards,
Jean-Michel POURE

Re: PERFORMANCE

From
Jean-Michel POURE
Date:
> "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 loop.

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

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

Feel free to give us your feedback as soon as you discovers what is going on.

Cheers,
Jean-Michel





Re: PERFORMANCE

From
Jean-Michel POURE
Date:
Le Jeudi 18 Avril 2002 10:55, Papp, Gyozo a écrit :
> 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-line
manual.

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

Cheers, Jean-Michel POURE


Re: PERFORMANCE

From
"Papp, Gyozo"
Date:
Hi,

Paulo wrote:

| 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

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

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

Yes, Jean-Michel is right, please try to debug what queries sent to the backend.
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 slowed
down. It might be a very evil trigger which starts to misbehave as tables grow.
BTW, it could be, really. If that trigger works too slowly...





Re: PERFORMANCE

From
Chadwick Rolfs
Date:
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 �crit :
> > 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-line
> manual.
>
> My personal feeling is that Valeria has an endless loop, otherwize Linux and
> PostgreSQL would not slow down dramatically. Valeria: could you turn
> 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
>

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*--


Re: PERFORMANCE

From
"Papp, Gyozo"
Date:
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 -----
From: "Jean-Michel POURE" <jmpoure@translationforge.com>
To: "Papp, Gyozo" <pgerzson@freestart.hu>; <valeria@saolucas-se.com.br>; <pgsql-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 écrit :
> 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-line
manual.

My personal feeling is that Valeria has an endless loop, otherwize Linux and
PostgreSQL would not slow down dramatically. Valeria: could you turn
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