Received: from www1.translationforge.com (unknown [212.11.36.58]) by postgresql.org (Postfix) with ESMTP id 3053047592A for ; Thu, 18 Apr 2002 04:18:06 -0400 (EDT) Received: from linus.translationforge.com ([192.168.1.102]) by www1.translationforge.com (8.12.1/8.12.1) with ESMTP id g3I8Hwtb029209; Thu, 18 Apr 2002 10:18:01 +0200 Content-Type: text/plain; charset="iso-8859-1" From: Jean-Michel POURE Reply-To: jmpoure@translationforge.com Organization: Translationforge To: , Subject: Re: PERFORMANCE Date: Thu, 18 Apr 2002 10:17:56 +0200 X-Mailer: KMail [version 1.4] References: <1463.192.168.1.205.1019057195.squirrel@www.saolucas-se.com.br> In-Reply-To: <1463.192.168.1.205.1019057195.squirrel@www.saolucas-se.com.br> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200204181017.56709.jmpoure@translationforge.com> X-Archive-Number: 200204/59 X-Sequence-Number: 1083 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