[pgpool-general: 1559] Re: pgpool 3.2.3 node gets down after some hours of work.

[Dzmitry]

Storage is the same - I am using amazon EBS high speed volumes.

Thanks,
  Dzmitry





On 3/29/13 4:23 AM, "Lonni J Friedman" <netllama at gmail.com> wrote:

>On Thu, Mar 28, 2013 at 6:11 PM, Dzmitry <dzmitry.nikitsin at gmail.com>
>wrote:
>> As for delay - do you know how to measure it ?
>
>There are a few mechanisms for measuring the latency, although I don't
>recall them off the top of my head, and my notes & monitoring tools
>are at $DAYJOB, and I'm currently not at $DAYJOB.    Google is your
>friend.
>
>> Software is the same, but hardware is different master - 67GB RAM, 8
>>CPUs,
>> standby 29GB RAM, 8CPUs.
>
>Typically storage is the rate limiting factor for replication,
>assuming that there are no network bottlenecks.  Do both systems have
>the same storage for postgres, or is one using something fast and the
>other something not as fast?
>
>> Is the replication latency a fixed amount? - how can I check this ?
>
>Its never a fixed amount.  If it was, then everyone would set it to 0,
>and we wouldn't be having this discussion.  The latency is determined
>primarily by the speed of your storage, and network between the
>servers.
>
>>
>>
>> Thanks,
>>   Dzmitry
>>
>>
>>
>>
>>
>> On 3/29/13 4:07 AM, "Lonni J Friedman" <netllama at gmail.com> wrote:
>>
>>>On Thu, Mar 28, 2013 at 6:00 PM, Dzmitry <dzmitry.nikitsin at gmail.com>
>>>wrote:
>>>> I don't see any network latency and we are not sending a lot of data.
>>>>I
>>>> don't know how big is delay in replication, but delay exists - it's
>>>>how
>>>> streaming replication works.
>>>
>>>A small delay of less than 1 second is how it should work.  You
>>>haven't quantified what kind of delaying you're experiencing, however
>>>if its many seconds (or more), then something is most definitely not
>>>right in your environment, and you need to determine what it is and
>>>attempt to resolve it.
>>>
>>>Are the servers identical in both software & hardware?
>>>Is the replication latency a fixed amount?
>>>
>>>
>>>> If  I do almost simultaneous 2 sql query, one to write data, second -
>>>>read
>>>> written data. Does pgpool handle this situation and do write/read from
>>>> master DB(if yes - does delay is configurable?) ? Or it can write to
>>>> master and read from slave - when data is not synchronized.
>>>
>>>The percentage of read query distributions is determined by how you
>>>configured pgpool.  However, again, if your data is non-trivially
>>>unsynchronized, then you need to understand & rectify that problem.
>>>Hacking around it in pgpool is not a viable solution, especially in a
>>>production environment, where I assume you want your data to be
>>>consistent at all times.
>>>
>>>>
>>>> Thanks,
>>>>   Dzmitry
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On 3/29/13 3:53 AM, "Lonni J Friedman" <netllama at gmail.com> wrote:
>>>>
>>>>>I think you're trying to solve the wrong problem.   Something seems
>>>>>wrong in your environment if there's a non-trivial delay in
>>>>>replication between the master & standby.  Is the standby struggling
>>>>>to keep up with the changes, or is there a lot of network latency, or
>>>>>is there some other problem?
>>>>>
>>>>>
>>>>>
>>>>>On Thu, Mar 28, 2013 at 4:48 PM, Dzmitry <dzmitry.nikitsin at gmail.com>
>>>>>wrote:
>>>>>> I installed & configured pgpool using this guide
>>>>>> 
>>>>>>http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.0/
>>>>>>.
>>>>>> But I found issue with log - you made me thinking, thank you. The
>>>>>>issue
>>>>>> was I haven't /usr/sbin/rotatelogs2 but I have /usr/sbin/rotatelogs.
>>>>>> I will monitor log to check why my node goes down.
>>>>>> One more question - my production application is connected to
>>>>>>pgpool &
>>>>>>it
>>>>>> do load balancing. When application write data - pgpool send queries
>>>>>>to
>>>>>> master DB & master do streaming replication with delay. But
>>>>>>application
>>>>>> can do read very quickly - and pgpool can send query to standy DB
>>>>>>where
>>>>>> changes is not synchronized yet.(for example user signup: 1 signup,
>>>>>>2
>>>>>>-
>>>>>> show protected page - here data can be not synchronized). Is it
>>>>>>possible
>>>>>> to resolve this situation with pgpool somehow ? Does it have option
>>>>>>to
>>>>>> send query to specified DB ? Or I need to connect my production
>>>>>> application server to master DB directly, but servers that do
>>>>>>background
>>>>>> processing - can be connected to pgpool ?
>>>>>>
>>>>>>
>>>>>> Thanks,
>>>>>>   Dzmitry
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On 3/29/13 2:24 AM, "Lonni J Friedman" <netllama at gmail.com> wrote:
>>>>>>
>>>>>>>On Thu, Mar 28, 2013 at 4:10 PM, Dzmitry
>>>>>>><dzmitry.nikitsin at gmail.com>
>>>>>>>wrote:
>>>>>>>> Dear all,
>>>>>>>>
>>>>>>>> I am using postgres 9.2 with streaming replication. I have 1
>>>>>>>>master
>>>>>>>>DB
>>>>>>>>and
>>>>>>>> 2 standby DBs, it's hosted on amazon EC2, each has it's own
>>>>>>>>server.
>>>>>>>>On
>>>>>>>> separate server i have pgpool(3.2.3) that do load balancing(in
>>>>>>>>pgpool
>>>>>>>> config i have num_init_children=290, max_pool- 2, child_life_time
>>>>>>>>-
>>>>>>>> 0child_max_connections - 100, connection_life_time- 100,
>>>>>>>>client_idle_limit
>>>>>>>> - 0. do not cache onnections.). Sometimes pgpool show server
>>>>>>>>status
>>>>>>>>as
>>>>>>>> "down", but actually server is up & running fine, due to this, i
>>>>>>>>am
>>>>>>>> getting a lot of issues.
>>>>>>>> Also i have in pgpool config:
>>>>>>>> health_check_timeout - 60
>>>>>>>> health_check_period - 30
>>>>>>>> health_check_max_retries - 3
>>>>>>>> health_check_retry_delay -5
>>>>>>>> Also pgpool is not writing log :(
>>>>>>>> i setup it
>>>>>>>> log_destination "stderr"
>>>>>>>> debug_level 0
>>>>>>>> logdir /var/tmp - i checked permissions it has 777, so it's not
>>>>>>>>the
>>>>>>>>case.
>>>>>>>> when i start pgpool i start it with "-n" option to let it write
>>>>>>>>log.
>>>>>>>>
>>>>>>>> Any ideas why server shown as down & where is my logs ?
>>>>>>>>
>>>>>>>
>>>>>>>Your logs would indicate why the nodes are down.  As for where they
>>>>>>>are, you'd have to tell us.  How did you install & configure pgpool?
>>>>>>
>>>>>>