Re: high throughput 9.3, master/slave question

Matthew Chambers <mchambers@xxxxxxxxxxxx> · Fri, 07 Mar 2014 09:41:39 +1300



    On 02/03/14 05:08, bricklen wrote:

    
          On Fri, Feb 28, 2014 at 1:54 PM,
            Matthew Chambers <mchambers@xxxxxxxxxxxx>
            wrote:

            
              Initially, I had my application servers using the slave
              for short, read only queries, but this turned out to be
              highly unstable. The slave would start refusing
              connections, and the logs would fill with:

              
              ERROR:  canceling statement due to conflict with recovery

              
              I've tried these 2 settings:

              
              max_standby_archive_delay = -1

              max_standby_streaming_delay = -1

              
              But then I starting getting these:

              DETAIL:  User transaction caused buffer deadlock with
              recovery.

              
              Read requests come in at anywhere between 200 and
              1000/second.

              
              I was wondering if there is some combination of
              configuration settings that would safely let me use the
              slave for read only queries?

                
          Have you tried setting max_standby_archive|streaming_delay to
          several minutes (or whatever makes sense for your typical
          query durations), rather than disabling those settings with
          -1?

        
    No, I have not tired this, but I'll give it a go.  Is there anyone
    doing this that has a configuration I can check out?  Is this even
    possible to do?

    
    -Matt