Обсуждение: Verify My Database Isn't Slammed

I've got a dedicated database server running PostgreSQL 9.1.2 which I
thought was working fine but I am now having users slowly trickle back
into the office and using some PHP web applications that use my
database server and strangely the site will load forever for some
users. They simply close the browser and or wait a few minutes and
then they can get in fine. I checked my Apache web logs and found this
error:

[Tue Jan 03 11:34:09 2012] [error] [client 192.168.1.16] MRBS:
Fatalerror: unfortunately the database is not available at the
moment.,referer: https://mysite.tld

I have also a webmail application called RoundCube that over 200 users
login to and this connects directly to the database server and there's
never been any communication issue what so ever. I'm going to assume
the issue is a configuration setting in MRBS in speaking with the
database and not the database itself but how does one who is extremely
novice with PostgreSQL determine if their database server is over
worked or running fine?

That sounds something like there's a missing indexes or incorrect indexes.  Are both apps having problems being slow or
justMRBS?  Is there anything about any particular queries that failed in some error log? 

Without having more data on the size of the tables and how old the app is it's hard to say for sure.  You could try
turningon the slow query log and see what it logs and then EXPLAIN and ANALYZE to see what it is doing.  Then create
indexesfor those queries. 

On 4 January 2012 21:30, Carlos Mennens <carlos.mennens@gmail.com> wrote:
> I've got a dedicated database server running PostgreSQL 9.1.2 which I
> thought was working fine but I am now having users slowly trickle back
> into the office and using some PHP web applications that use my
> database server and strangely the site will load forever for some
> users. They simply close the browser and or wait a few minutes and
> then they can get in fine. I checked my Apache web logs and found this
> error:
>
> [Tue Jan 03 11:34:09 2012] [error] [client 192.168.1.16] MRBS:
> Fatalerror: unfortunately the database is not available at the
> moment.,referer: https://mysite.tld
>
> I have also a webmail application called RoundCube that over 200 users
> login to and this connects directly to the database server and there's
> never been any communication issue what so ever. I'm going to assume
> the issue is a configuration setting in MRBS in speaking with the
> database and not the database itself but how does one who is extremely
> novice with PostgreSQL determine if their database server is over
> worked or running fine?

Firstly it's worth looking at PostgreSQL's logs and look at the
entries for the timestamp around the time you're seeing the error in
Apache.  Also, at the time users are experiencing problems, run:

SELECT count(*) from pg_stat_activity;

This will give you a count of how many active connections there are on
your database.  Compare this with the result of:

SHOW max_connections;

This will tell you if you have reached or nearly reached your limit.
If you believe your max_connections is too low, either look at
increasing it in your postgresql.conf file or consider a connection
pooler like pgbouncer.

Regards

Thom