Re: PERFORMANCE

Le Mercredi 17 Avril 2002 17:26, valeria(at)saolucas-se(dot)com(dot)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