Thank's for your response.
The situation is that the top result is when the server is already
exhibiting problems.
The number of rows returned by the query varies, right now is:
49 row(s)
Total runtime: 3,965.718 ms
The table currently has 971582 rows.
But the problem is that when database server is restarted everything works
fine and fast. No heavy loads of the processor and as time passes
situation with the processor is worsen.
I forget to mention that php scrip is executed as a web application
(Apache web server 2.2.3, php installed as a Server API Apache 2.0
Handler) called periodically each 8 seconds. After the restart of the
postgres server everything works fine for several hours, the web
application has a fast response when opening a web page. But after some
time postmaster process (sometimes two postmaster process both owned by
postgres user) rises and response time of the web application becomes
slow, i.e. opening a php page with postgres access last for 8-10 seconds
or even more. The php configuration for the postgres is default
PostgreSQL Support enabled
PostgreSQL(libpq) Version 8.1.8
Multibyte character support enabled
SSL support enabled
Active Persistent Links 1
Active Links 1
Directive Local Value Master Value
pgsql.allow_persistent On On
pgsql.auto_reset_persistent Off Off
pgsql.ignore_notice Off Off
pgsql.log_notice Off Off
pgsql.max_links Unlimited Unlimited
pgsql.max_persistent Unlimited Unlimited
>> If that's what it looks like your server is running just fine. Load
>> of 1.31, 85+% idle, no wait time. Or is that top and vmstat output
>> from when the server is running fine?
>
> Don't forget that there are 8 CPUs, and the backend will only run on one
> of them.
>
> But I concur that this seems ok.
> How many rows are returned? Is 0.6 seconds an unacceptable time for that?
>
> If there is a lot of sorting going on and the pages are residing in the
> buffer, I would expect high CPU load.
>
> Normally, I am quite happy if my database is CPU bound. I start worrying
> if I/O wait grows too high.
>
> Yours,
> Laurenz Albe
>
