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.
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.
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.
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.
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.
improve query response, but these are adequately covered in the
interactive postgresql documentation.
Ericson Smith is a web developer at http://did-it.com.
