I'm running 9.3.5 on a virtual machine with 5 cores and 24 GB of
memory. Disk is on a SAN.
I have a task that runs weekly that processes possibly as many as 120
months worth of data, one month at a time. Since moving to 9.3.5
(from 8.2!!) the average time for a month has been 3 minutes or less.
However, when this job ran this Tuesday, it ran fine for a number of
months, but then started slowing down dramatically, 300 minutes for
one month and then 167 minutes for the next. I stopped and restarted
postgresql, the next block also ran really slow (157 minutes.) I then
rebooted the server and the remaining blocks ran at the usual fast
speed again, so restarting postgresql didn't fix the problem but
rebooting the server did.
Looking at the logs, I see queries with a function call that would
normally take no more than 100-200 milliseconds, usually far less,
that were taking 100 seconds or longer. This function gets called
thousands of times for each month, so that appears to be one source of
the slowdown.
I don't suspect a memory leak in the calling program (in php), because
since moving to this server in December this weekly task has run
several times over the same range of months, making pretty much the
same function calls each time. I also ran the entire range several
times during testing.
One change made to the server since the previous week's run was that I
moved up to the latest Centos kernel (Linux version
3.10.0-123.13.2.el7.x86_64).
As far as I can tell, the other virtual servers weren't being slowed
down, so I don't suspect problems with the virtual server or the SAN.
If this happens again, what sorts of settings in postgresq.conf or
other tools should I be using to try to track down what's causing
this?
--
Mike Nolan