Re: [PERFORM] Slow Query
In the 'not exists' cluster, you do not have to search table Vacancy as v again. I think it would be faster to use the outer Vacancy table as below. In your case, that do the same work. NOT EXISTS ( SELECT 1 FROM CategoryOption_TableRow ct126 WHERE Vacancy.Template AND ct126.CategoryOptionID IN (34024,35254,35255,35256) AND ct126.Category_TableID = 126 AND ct126.RowID = Vacancy.ID )
[PERFORM] Strange query stalls on replica in 9.3.9
Setup: * PostgreSQL 9.3.9 * 1 master, 1 replica * Tiny database, under 0.5GB, completely cached in shared_buffers * 90% read query traffic, which is handled by replica * Traffic in the 1000's QPS. The wierdness: Periodically the master runs an update all rows query on the main table in the database. When this update hits the replica via replication stream, *some* (about 5%) of the queries which do seq scans will stall for 22 to 32 seconds (these queries normally take about 75ms). Queries which do index scans seem not to be affected. Thing is, the update all rows only takes 2.5 seconds to execute on the master. So even if the update is blocking the seq scans on the replica (and I can't see why it would), it should only block them for 3 seconds. Anyone seen anything like this? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Strange query stalls on replica in 9.3.9
On Thu, Aug 13, 2015 at 10:09 AM, Josh Berkus j...@agliodbs.com wrote: Setup: * PostgreSQL 9.3.9 * 1 master, 1 replica * Tiny database, under 0.5GB, completely cached in shared_buffers * 90% read query traffic, which is handled by replica * Traffic in the 1000's QPS. The wierdness: Periodically the master runs an update all rows query on the main table in the database. When this update hits the replica via replication stream, *some* (about 5%) of the queries which do seq scans will stall for 22 to 32 seconds (these queries normally take about 75ms). Queries which do index scans seem not to be affected. Thing is, the update all rows only takes 2.5 seconds to execute on the master. So even if the update is blocking the seq scans on the replica (and I can't see why it would), it should only block them for 3 seconds. Anyone seen anything like this? Sounds like another manifestation of this: [PERFORM] Planner performance extremely affected by an hanging transaction (20-30 times)? http://www.postgresql.org/message-id/CAMkU=1yy-YEQVvqj2xJitT1EFkyuFk7uTV_hrOMGyGMxpU=n...@mail.gmail.com Each backend that does a seqscan, for each tuple it scans which is not yet resolved (which near the end of the bulk update is going to be nearly equal to 2*reltuples, as every tuple has both an old and a new version so one xmax from one and one xmin from the other must be checked), it has to lock and scan the proc array lock to see if the tuple-inserting transaction has committed yet. This creates profound contention on the lock. Every scanning backend is looping over every other backend for every tuple Once the commit of the whole-table update has replayed, the problem should go way instantly because at that point each backend doing the seqscan will find the the transaction has committed and so will set the hint bit that means all of the other seqscan backends that come after it can skip the proc array scan for that tuple. So perhaps the commit of the whole-table update is delayed because the startup process as also getting bogged down on the same contended lock? I don't know how hard WAL replay hits the proc array lock. Cheers, Jeff
Re: [PERFORM] Slow Query
Hi, Doing this returns 0 records On Thu, Aug 13, 2015 at 7:22 PM, 林士博 [via PostgreSQL] ml-node+s1045698n5862008...@n5.nabble.com wrote: In the 'not exists' cluster, you do not have to search table Vacancy as v again. I think it would be faster to use the outer Vacancy table as below. In your case, that do the same work. NOT EXISTS ( SELECT 1 FROM CategoryOption_TableRow ct126 WHERE Vacancy.Template AND ct126.CategoryOptionID IN (34024,35254,35255,35256) AND ct126.Category_TableID = 126 AND ct126.RowID = Vacancy.ID ) -- If you reply to this email, your message will be added to the discussion below: http://postgresql.nabble.com/Slow-Query-tp5861835p5862008.html To unsubscribe from Slow Query, click here http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5861835code=cm9iY2FtcGJlbGw3M0BnbWFpbC5jb218NTg2MTgzNXwxOTc1MDc2ODM4 . NAML http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml -- Regards Robert Campbell +61412062971 robcampbel...@gmail.com -- View this message in context: http://postgresql.nabble.com/Slow-Query-tp5861835p5862122.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Re: [PERFORM] Slow Query
Is the Vacancy.ID a primary key? Or is unique in Vacancy table?
