Sudden Query slowdown on our Postgresql Server

Поиск
Список
Период
Сортировка
От Sebastian Melchior
Тема Sudden Query slowdown on our Postgresql Server
Дата
Msg-id 03615FD7-20B3-49AB-AC5E-484544D03933@mailz.de
обсуждение исходный текст
Ответы Re: Sudden Query slowdown on our Postgresql Server  (Stephen Frost <sfrost@snowman.net>)
Список pgsql-performance
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,
thedata 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
intransaction>" state, and the Queries get slower and slower when using the Master Server. We traced the Application
whichis connected via a private LAN, and could not find any hangups that could cause these states in the Database.
Duringthis time, the load of the Master goes up a bit, but the CPU Usage and IOwait is still quite low at around a Load
of5-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
thattime. 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


В списке pgsql-performance по дате отправления:

Предыдущее
От: Mark Kirkwood
Дата:
Сообщение: Re: Write workload is causing severe slowdown in Production
Следующее
От: Stephen Frost
Дата:
Сообщение: Re: Sudden Query slowdown on our Postgresql Server