Re: [PERFORM] Queries intermittently slow
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
Re: [PERFORM] Queries intermittently slow
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] Slow query help
>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
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] Queries intermittently slow
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
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
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