Thread: 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
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
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
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
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 >
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
another articles with pgsql performance tips: http://candle.pha.pa.us/main/writings/pgsql/performace.pdf
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
> "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
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
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...
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*--
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