Hi, we are currently seeing some strange performance issues related to our Postgresql Database. Our Setup currently contains: - 1 Master with 32GB Ram and 6 x 100GB SSDs in RAID10 and 2 Quad Core Intel Processors (this one has a failover Box, the data volume is shared via DRBD) - 2 Slaves with 16GB Ram and 6 x 100GB SAS Disks in RAID 10 and 1 Quad Core Processor connected via streaming replication
We currently use Postgresql 9.0.6 from Debian Squeeze Backports with a 2.6.39-bpo.2 Backports Squeeze Kernel. All Servers use Pgbouncer as a connection Pooler, which is installed on each box. In times of higher usage, we see some strange issues on our Master box, the connections start stacking up in an "<idle in transaction>" state, and the Queries get slower and slower when using the Master Server. We traced the Application which is connected via a private LAN, and could not find any hangups that could cause these states in the Database. During this time, the load of the Master goes up a bit, but the CPU Usage and IOwait is still quite low at around a Load of 5-8. The usual Load is around 1 - 1.5. 19:14:28.654 4838 LOG Stats: 3156 req/s, in 1157187 b/s, out 1457656 b/s,query 6119 us 19:15:28.655 4838 LOG Stats: 3247 req/s, in 1159833 b/s, out 1421552 b/s,query 5025 us 19:16:28.660 4838 LOG Stats: 3045 req/s, in 1096349 b/s, out 1377927 b/s,query 3713 us 19:17:28.680 4838 LOG Stats: 2779 req/s, in 1030783 b/s, out 1343547 b/s,query 11977 us 19:18:28.688 4838 LOG Stats: 1723 req/s, in 664282 b/s, out 789989 b/s,query 67144 us 19:19:28.665 4838 LOG Stats: 1371 req/s, in 472587 b/s, out 622347 b/s,query 48417 us 19:20:28.668 4838 LOG Stats: 2161 req/s, in 748727 b/s, out 995794 b/s,query 2794 us As you can see in the pgbouncer logs, the query exec time shoots up. We took a close look at locking issues during that time, but we don't see any excessive amount of locking during that time. The issue suddenly popped up, we had times of higher usage before and the Postgresql DB handled it without any problems. We also did not recently change anything in this setup. We also did take a look at the Slow Queries Log during that time. This did now show anything unusual during the time of the slowdown. Does anyone have any idea what could cause this issue or how we can further debug it? Thanks for your Input! Sebastian -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
