PHPBuilder - Optimizing Postgresql Page 3



RSS Twitter
Articles Databases

Optimizing Postgresql - Page 3

by: PHP Builder Staff
|
August 21, 2001

Tailing the log file clearly explained what the problem was.
All sorts of sexy debugging info will show up in this file, which includes SQL syntax errors, the output of EXPLAIN state, emts, connection problems, authentication attempts, and so forth.
I restarted postgresql and brought our CGI online. Our jaws collectively dropped to the floor as postgresql literally flew as soon as it started to use the buffer. Server load by postgresql dropped to just under 10-percent.
One hitch I found with an early version of the system was that it had to build up and tear down a postgresql connection with each request. This was intolerable, so I started to use the connection pooling features of the C library. Server load dropped another few notches with this option. With PHP you will want to use persistent connections (pg_pconnect instead of pg_connect) to fully take advantage of this effect.

Indexes

I cannot emphasize enough the need to have proper indexing in postgresql. One early mistake that I made was to index BIGINT columns. The columns were indexed ok, but postgresql refused to make use them. After two days of tearing out my hair, it came to me that the architecture of the system was 32 bits. Could it be that postgresql refuses to make use of a 64 bit (BIGINT) index? Changing the type to INTEGER quickly solved that problem. Maybe if I had one of those new-fangled 64 bit Itanium processors.

Conclusion

There are many things that you can do with your SQL statements to also improve query response, but these are adequately covered in the interactive postgresql documentation.
Ericson Smith is a web developer at http://did-it.com.

« Previous Page
1
|
2
|
3

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:

Comment:



Comment:

(Maximum characters: 1200). You have characters left.