Re: Bi-modal streaming replication throughput
On Tue, Aug 14, 2018 at 7:50 PM Andres Freund wrote: > Hi, > > On 2018-08-14 10:46:45 -0700, Andres Freund wrote: > > On 2018-08-14 15:18:55 +0200, Alexis Lê-Quôc wrote: > > > + 30.25%26.78% postgres postgres [.] mdnblocks > > > > This I've likely fixed ~two years back: > > > > > http://archives.postgresql.org/message-id/72a98a639574d2e25ed94652848555900c81a799 > > Err, wrong keyboard shortcut *and* wrong commit hash: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=45e191e3aa62d47a8bc1a33f784286b2051f45cb > > - Andres > Thanks for the commit in the first place and the reference now; that's a very logical explanation. We're now off 9.3.
Re: Bi-modal streaming replication throughput
On 2018-08-17 15:21:19 +0200, Alexis Lê-Quôc wrote: > On Tue, Aug 14, 2018 at 7:50 PM Andres Freund wrote: > > > Hi, > > > > On 2018-08-14 10:46:45 -0700, Andres Freund wrote: > > > On 2018-08-14 15:18:55 +0200, Alexis Lê-Quôc wrote: > > > > + 30.25%26.78% postgres postgres [.] mdnblocks > > > > > > This I've likely fixed ~two years back: > > > > > > > > http://archives.postgresql.org/message-id/72a98a639574d2e25ed94652848555900c81a799 > > > > Err, wrong keyboard shortcut *and* wrong commit hash: > > > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=45e191e3aa62d47a8bc1a33f784286b2051f45cb > > > > - Andres > > > > Thanks for the commit in the first place and the reference now; that's a > very logical explanation. We're now off 9.3. Glad to help. Did the migration appear to have resolved the worst performance issues? Greetings, Andres Freund
Re: Guideline To Resolve LWLock:SubtransControlLock
Aurora Postgres 9.6.3 So, no chance to recompile (AFAIK). Is there a design anti-pattern at the schema or data access level that we should look for and correct? And as for the recompile, are you thinking 'NUM_SUBTRANS_BUFFERS'? Thanks On Thu, Aug 16, 2018 at 2:36 PM Alvaro Herrera wrote: > On 2018-Aug-16, Fred Habash wrote: > > > One of our database API's is run concurrently by near 40 sessions. We see > > all of them waiting back and forth on this wait state. > > What version are you running? > > > Why is it called Subtrans Control Lock? > > It controls access to the pg_subtrans structure, which is used to record > parent/child transaction relationships (as you say, savepoints and > EXCEPTIONs in plpgsql are the most common uses, but not the only ones). > Normally lookup of these is optimized away, but once you cross a > threshold it cannot any longer. > > > What are the common user session scenarios causing this wait? > > - I have read some describe the use of SQL savepoints or PL/pgSQL > > exception handling. > > What are known resolution measures? > > Are you in a position to recompile Postgres? > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > -- Thank you
Re: Guideline To Resolve LWLock:SubtransControlLock
On 2018-Aug-17, Fred Habash wrote: > Aurora Postgres 9.6.3 Oh, okay, I don't know this one. Did you contact Amazon support? > So, no chance to recompile (AFAIK). > Is there a design anti-pattern at the schema or data access level that we > should look for and correct? Maybe ... > And as for the recompile, are you thinking 'NUM_SUBTRANS_BUFFERS'? Yes, that's one option, but there's also TOTAL_MAX_CACHED_SUBXIDS. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
