On Mon, Aug 14, 2017 at 1:53 PM, Jeremy Finzel <finzelj@gmail.com> wrote:
> This particular db is on 9.3.15. Recently we had a serious performance
> degradation related to a batch job that creates 4-5 temp tables and 5
> indexes. It is a really badly written job but what really confuses us is
> that this job has been running for years with no issue remotely approaching
> this one. We are also using pgpool.
>
> The job would kick off with 20-30 of similar queries running at once. The
> thing normally takes only 30ms or so to run - it only operates on 1 customer
> at a time (yes, it's horribly written). All of a sudden the cluster started
> thrashing and performance seriously degraded. We tried a number of things
> with no success:
>
> Analyzed the whole database
> Turned off full logging
> Turned off synchronous commit
> Vacuumed several of the catalog tables
> Checked if we had an abnormal high amount of traffic this time - we didn't
> No abnormal disk/network issues (we would have seen much larger issues if
> that had been the case)
> Tried turning down the number of app nodes running
>
> What ended up completely resolving the issue was converting the query to use
> ctes instead of temp tables. That means we avoided the disk writing and the
> catalog churn, and useless indexes. However, we are baffled as to why this
> could make such a big difference when we had no issue like this before, and
> we have seen no systematic performance degradation in our system.
>
> Any insights would be greatly appreciated, as we are concerned not knowing
> the root cause.
How are your disks setup? One big drive with everything on it?
Separate disks for pg_xlog and pg's data dir and the OS logging? IO
contention is one of the big killers of db performance.
Logging likely isn't your problem, but yeah you don't need to log
ERRYTHANG to see the problem either. Log long running queries temp
usage, buffer usage, query plans on slow queries, stuff like that.
You've likely hit a "tipping point" in terms of data size. Either it's
cause the query planner to make a bad decision, or you're spilling to
disk a lot more than you used to.
Be sure to log temporary stuff with log_temp_files = 0 in your
postgresql.conf and then look for temporary file in your logs. I bet
you've started spilling into the same place as your temp tables are
going, and by default that's your data directory. Adding another drive
and moving pgsql's temp table space to it might help.
Also increasing work_mem (but don't go crazy, it's per sort, so can
multiply fast on a busy server)
Also log your query plans or run explain / explain analyze on the slow
queries to see what they're doing that's so expensive.