Re: [PERFORM] Queries intermittently slow

2016-01-07 Thread Scott Rankin
On 1/6/16, 11:14 PM, "Jim Nasby" wrote: >On 1/6/16 12:01 PM, Scott Rankin wrote: >> I guess we’re back to lock contention? > >Is there by chance an anti-wraparound vacuum happening on that table? > >Actually, for that matter... if autovacuum is hitting that table it's

Re: [PERFORM] Queries intermittently slow

2016-01-07 Thread Tom Lane
Scott Rankin writes: > Any other ideas? If there's no lock waits, and the tables are static, it's difficult to arrive at any other conclusion but that the process is simply losing the CPU for long intervals. What else is going on on this box? Have you made any attempt to

Re: [PERFORM] Queries intermittently slow

2016-01-07 Thread Scott Rankin
On 1/7/16, 10:19 AM, "Tom Lane" wrote: >Scott Rankin writes: >> Any other ideas? > >If there's no lock waits, and the tables are static, it's difficult to >arrive at any other conclusion but that the process is simply losing the >CPU for long intervals.

Re: [PERFORM] Queries intermittently slow

2016-01-07 Thread Andres Freund
On 2016-01-07 13:34:51 -0500, Tom Lane wrote: > It's fairly well established that the implementation of transparent > huge pages in Linux kernels from the 2.6-or-so era sucks, and you're > best off turning it off if you care about consistency of performance. I think the feature wasn't introduced

Re: [PERFORM] Queries intermittently slow

2016-01-07 Thread Tom Lane
Rick Otten writes: > On Thu, Jan 7, 2016 at 12:51 PM, Scott Rankin wrote: >> Winner! Both of those settings were set to always, and as soon as I >> turned them off, the query times smoothed right out. > Is this generally true for all PostgreSQL

Re: [PERFORM] Slow query help

2016-01-07 Thread Marc Mamin
>I ask your help to solve a slow query which is taking more than 14 seconds to >be executed. >Maybe I am asking too much both from you and specially from postgresql, as it >is really huge, envolving 16 tables. > >Explain: >http://explain.depesz.com/s/XII9 > >Schema:

Re: [PERFORM] Queries intermittently slow

2016-01-07 Thread Jim Nasby
On 1/7/16 1:47 PM, Andres Freund wrote: On 2016-01-07 13:34:51 -0500, Tom Lane wrote: It's fairly well established that the implementation of transparent huge pages in Linux kernels from the 2.6-or-so era sucks, and you're best off turning it off if you care about consistency of performance.