Thread: Debugging Postgres?
The issue I'm seeing is that randomly I'm getting these pauses, or stalls, while trying to simply connect to the database server(s) from connected clients using the psql command line client. I cannot tell if the server is even getting the request for service as they don't seem to show up in the logs at the time the event is occurring, so I'm thinking it's maybe a client-side issue.
http://stackoverflow.com/questions/722221/how-to-log-postgres-sql-queries
Is there a good general starting place for debugging these types of issues ?HelloI'm having an interesting issue with PGSQL. It seems that I'm experiencing timeouts at various times. The servers are not busy and have plenty of resources. The databases are ~50GB in size, the systems typically have 8-12GB physical RAM, and the connections are low (less than 15 at any given time).
The issue I'm seeing is that randomly I'm getting these pauses, or stalls, while trying to simply connect to the database server(s) from connected clients using the psql command line client. I cannot tell if the server is even getting the request for service as they don't seem to show up in the logs at the time the event is occurring, so I'm thinking it's maybe a client-side issue.-B.
On 08/13/2013 01:51 PM, Barth Weishoff wrote: > Hello > > I'm having an interesting issue with PGSQL. It seems that I'm > experiencing timeouts at various times. The servers are not busy and > have plenty of resources. The databases are ~50GB in size, the > systems typically have 8-12GB physical RAM, and the connections are > low (less than 15 at any given time). > > The issue I'm seeing is that randomly I'm getting these pauses, or > stalls, while trying to simply connect to the database server(s) from > connected clients using the psql command line client. I cannot tell > if the server is even getting the request for service as they don't > seem to show up in the logs at the time the event is occurring, so I'm > thinking it's maybe a client-side issue. > > Is there a good general starting place for debugging these types of > issues ? General recommendations: 1. A copy of "PostgreSQL High Performance" by Greg Smith should be on your shelf. 2. Increase your logging and be sure to log connections so you can correlate what you observe with your PostgreSQL logs. I'm currently logging all queries and connections with minimal overhead (though I'm routing the logs to a separate server as they accumulate about 4GB/week). 3. Use a log-analyzer. Pgbadger for PostgreSQL and, perhaps, a general system activity graphing program. Sysusage is easy to install if you don't have something in place already for system activity graphing. 4. Be specific when reporting your issue. There are issues that only occur with certain versions of PostgreSQL or on selected operating-systems (even on specific kernel versions). When you have seemingly random slowdowns and have eliminated the usual suspects like unusually large queries or request spikes you should look at write cache sizing. The default settings on many Linux distributions are not optimal for a dedicated database server and can lead to periodic storms of writes. Since the OS kernel parameters are ratios of available RAM, large amounts of RAM can actually exacerbate this problem since it allows the OS to accumulate larger amounts of data that require writing to disk before hitting aggressive disk write trigger threshold. Cheers, Steve
tcpdump and/or wireshark will help with detecting network issues. Wireshark is a little easier to grok if you are not used to doing packet dumps. http://www.wireshark.org/
Dennis
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jayadevan M
Sent: Wednesday, August 14, 2013 9:14 PM
To: Barth Weishoff
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Debugging Postgres?
Hi,
Having a look at the PostgreSQL log may help.
http://stackoverflow.com/questions/722221/how-to-log-postgres-sql-queries
You could also try logging in via psql on the database host itself, to ensure it is not a network issue.
Regards,
Jayadevan
On Wed, Aug 14, 2013 at 2:21 AM, Barth Weishoff <bweishoff@gmail.com> wrote:
Hello
I'm having an interesting issue with PGSQL. It seems that I'm experiencing timeouts at various times. The servers are not busy and have plenty of resources. The databases are ~50GB in size, the systems typically have 8-12GB physical RAM, and the connections are low (less than 15 at any given time).
The issue I'm seeing is that randomly I'm getting these pauses, or stalls, while trying to simply connect to the database server(s) from connected clients using the psql command line client. I cannot tell if the server is even getting the request for service as they don't seem to show up in the logs at the time the event is occurring, so I'm thinking it's maybe a client-side issue.
Is there a good general starting place for debugging these types of issues ?
-B.