Le Mercredi 17 Avril 2002 17:26, valeria@xxxxxxxxxxxxxxxxxx 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
