Re: [PERFORM] Queries intermittently slow

[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
>locking could be causing problems, and it won't release it's locks until
>the deadlock detector kicks in.
>--
>Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
>Experts in Analytics, Data Architecture and PostgreSQL
>Data in Trouble? Get it in Treble! http://BlueTreble.com

Hi Jim, Tom,

Thanks both of you for your help, by the way.

I lowered the deadlock_timeout this morning to 100ms and ran for a little 
while, but I did not see any lock waits even when the statements ran slowly.

Jim, to your point, I enabled auto vacuum logging 
(log_autovacuum_min_duration=0) but no vacuums are taking place even when I see 
slow queries.

Any other ideas?

Thanks,
Scott
This email message contains information that Motus, LLC considers confidential 
and/or proprietary, or may later designate as confidential and proprietary. It 
is intended only for use of the individual or entity named above and should not 
be forwarded to any other persons or entities without the express consent of 
Motus, LLC, nor should it be used for any purpose other than in the course of 
any potential or actual business relationship with Motus, LLC. If the reader of 
this message is not the intended recipient, or the employee or agent 
responsible to deliver it to the intended recipient, you are hereby notified 
that any dissemination, distribution, or copying of this communication is 
strictly prohibited. If you have received this communication in error, please 
notify sender immediately and destroy the original message.

Internal Revenue Service regulations require that certain types of written 
advice include a disclaimer. To the extent the preceding message contains 
advice relating to a Federal tax issue, unless expressly stated otherwise the 
advice is not intended or written to be used, and it cannot be used by the 
recipient or any other taxpayer, for the purpose of avoiding Federal tax 
penalties, and was not written to support the promotion or marketing of any 
transaction or matter discussed herein.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Queries intermittently slow

[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 correlate the slow queries with spikes in load
average, swap activity, etc?

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Queries intermittently slow

[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.  What else is going on on this box?  Have you
>made any attempt to correlate the slow queries with spikes in load
>average, swap activity, etc?
>
>regards, tom lane

This box is a dedicated postgresql box, the load is running around 2-3 (on a 
24-core box).  No swapping.

I’ll keep digging!

Thanks,
Scott
This email message contains information that Motus, LLC considers confidential 
and/or proprietary, or may later designate as confidential and proprietary. It 
is intended only for use of the individual or entity named above and should not 
be forwarded to any other persons or entities without the express consent of 
Motus, LLC, nor should it be used for any purpose other than in the course of 
any potential or actual business relationship with Motus, LLC. If the reader of 
this message is not the intended recipient, or the employee or agent 
responsible to deliver it to the intended recipient, you are hereby notified 
that any dissemination, distribution, or copying of this communication is 
strictly prohibited. If you have received this communication in error, please 
notify sender immediately and destroy the original message.

Internal Revenue Service regulations require that certain types of written 
advice include a disclaimer. To the extent the preceding message contains 
advice relating to a Federal tax issue, unless expressly stated otherwise the 
advice is not intended or written to be used, and it cannot be used by the 
recipient or any other taxpayer, for the purpose of avoiding Federal tax 
penalties, and was not written to support the promotion or marketing of any 
transaction or matter discussed herein.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Queries intermittently slow

[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 in original 2.6 kernels (3.2 or
so?), but red hat had backported it to their 2.6.32 kernel.


> I am not sure whether modern kernels have improved this area.

I think the problem has largely been solved around 3.16. Around 4.1 I
could still  reproduce problems, but the regressions were only in the
sub 10% range in my test workload.


Andres


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Queries intermittently slow

[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 systems on Linux, or only for
> specific use cases?

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 am not sure whether modern kernels have improved this area.

I think you can get an idea of how big a problem you have by noting
the accumulated runtime of the khugepaged daemon.

(BTW, it would likely be a good thing to collect some current wisdom
in this area and add it to section 17.4 of our docs.)

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow query help

[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:
>http://adj.com.br/erp/data_schema/

Hello,

It seems that you don't pay much attention to column alignment.
e.g. http://adj.com.br/erp/data_schema/tables/ERP_PUBLIC_sys_person.html

This probably won't make any significant difference in your case,
but this is something to be aware of when dealing with large tables.
here is a good starting link for this topic:
http://stackoverflow.com/questions/12604744/does-the-order-of-columns-in-a-postgres-table-impact-performance

regards,

Marc Mamin

Re: [PERFORM] Queries intermittently slow

[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.


I think the feature wasn't introduced in original 2.6 kernels (3.2 or
so?), but red hat had backported it to their 2.6.32 kernel.



I am not sure whether modern kernels have improved this area.


I think the problem has largely been solved around 3.16. Around 4.1 I
could still  reproduce problems, but the regressions were only in the
sub 10% range in my test workload.


BTW, looks like Scott blogged about this along with some nice graphs: 
https://sdwr98.wordpress.com/2016/01/07/transparent-huge-pages-or-why-dbas-are-great/

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance