Hi,
I had a very strange effect on the weekend that smells like a bug, so
i'd like so share it.
Setup:
machine A: 16 CPU Cores (modern), 128GB RAM, nice 6-drive SAS Raid-10
machines B, C: 8 Cores (substantially older than A), 48GB Ram, some scsi
Raid, substantially slower than A
The workload is about 80% - 90% SELECTs with heavy sorting and grouping,
the remaining are INSERTs/UPDATEs/DELETEs.
So In the original setup A is the master, B and C are hot standby's
that process some of the SELECTs, but by far the most processing is done
on the master (A). pg version is 9.0.6. CPU utilization is about 80% on
the master and between 90-100% in the standby's, so it's decided to
upgrade to the latest 9.2 to profit from the latest performance
enhancements.
So B gets upgraded to 9.2.1-1.pgdg60+1 (from pgapt.debian.org) and
becomes master, then A becomes a hot_standby slave that takes all the
SELECTs (and C becomes another hot_standby). In the beginning everything
works as expected, CPU utilization drops from 80% to about 50-60%,
selects run faster, everything looks smoother (some queries drop from
>5s to <1s due to 9.2s index-only-scan feature). Its friday, everyone
is happy.
About 16 hours later, saturday morning around 6:00, A suddenly goes wild
and has a CPU utilization of 100% without a change in the workload, out
of the blue. Queries that used to take <1s suddenly take 5-10s, "explain
analyze" plans of these queries havn't change a bit though. Switching
the workload off causes the server to become idle. (while I'm writing
this I realize we haven't tried to restart A). Instead, $boss decides to
twitch back to the original setup, so B gets dropped, A becomes master
and gets 100% of the workload (all SELECTs/INSERTs/UPDATEs/DELETEs), and
everything becomes just like friday, CPU usage drops to 50-60%,
everything runs smothly.
I'm not sure yet if this is replication related or a 9.2.1 problem. Any
Ideas?
regards
Andreas Pfotenhauer
Ypsilon.NET AG
