> >So what seems more likely after more thought is that the pages are staying >in our shared buffer arena just fine, but the kernel is randomly choosing >to swap out parts of the arena, and the delays correspond to swap-in >waits. (There would still have to be a mighty crummy disk subsystem >underlying things for swap-in to take so long, but this is a more >plausible theory for exactly what's invoking the disk read.) > >Postgres can't directly see when this is happening, but you could try >watching "iostat 1" and noticing whether swap-in events seem to be >correlated with the slow queries. > >If this is the problem, then the answer is to reduce the pressure on >system memory so that swap-outs are less likely. You might find that >a smaller shared_buffer arena is a good thing (so that all of it stays >"hot" and unswappable from the kernel's perspective). Or reduce the >number of active backend processes. > >regards, tom lane One change to my setup that my server guy pointed out - this database server is *not* a cloud server, but in fact a physical Dell box with SSDs. So unless something is majorly wrong with our disk setup, disk IO should not be a factor. I also checked and the box is not swapping at all. I guess we’re back to lock contention? 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
