[pgpool-hackers: 493] Re: [pgpool-general: 2762] Re: OTRS

[Tatsuo Ishii]

So, we have three candidates of the load balance tuning parameter:

1) client IP addresses

2) databases and/or tables

3) application_name

Question is, which one is the best? or should we implement all of
them? Or any better idea?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


> Hi Ishii-san,
> 
> I heard from someone an idea to use application_name for tuning load balancing,
> though I'm not sure how reasonable or useful.
> 
> On Tue, 15 Apr 2014 09:57:02 +0900 (JST)
> Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> [Moved to hackers list]
>> 
>> Hi pgpool hackers,
>> 
>> There are some demands of fine tuning for load balancing.
>> At this point there are two proposales:
>> 
>> 1) based on client IP address
>> 
>> 2) based on database and/or table
>> 
>> I think the fine tuning for load balancing itself is worth to work on,
>> but I would like to know any other way to control the knob before
>> proceeding further. Suggestions?
>> 
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>> 
>> > I think we've same problem. When You will implement the client IP address
>> > based setting solution, it will solve our problem. Thx!
>> > 
>> > In the next steps we would like to migrate some our 2 layer (thick clients
>> > - more then 100 users from different IP adressess)  application to the
>> > pgpool. If we will same Load Balance  problem, we must set out all of
>> > client IP address in the config file one by one. I think is it a little bit
>> > difficulty useable.
>> > 
>> > That reason I would like to suggest maybe the database name setting
>> > solution more usable instead of IP address if it possible.
>> > 
>> > If You can implement only the client IP based solution, of course we will
>> > be very happy too.
>> > 
>> > Thanks a lot,
>> > Csaba
>> > 
>> > 
>> > 2014-04-12 1:39 GMT+02:00 Tatsuo Ishii <ishii at postgresql.org>:
>> > 
>> >> > We examined the native replication mode, but our other databases and
>> >> > aplications using OIDs, and random values, and I afraid we will be some
>> >> > problems. That reason we're staying in streaming replication mode , and
>> >> we
>> >> > use OTRS without pool in the production area :(
>> >>
>> >> For random values (i.e. random() function) we will be able to solve
>> >> the problem by using similar technique used in handling now(). However
>> >> for OID, I have no idea how to solve it.
>> >>
>> >> > Do you planning in the future releases  some extra Load Balance
>> >> parameters?
>> >> > I'm thinking of for example white/black list to turn off/on it on the
>> >> > databases, tables, etc.
>> >>
>> >> I found an interesting TODO related to OTRS:
>> >>
>> >>
>> >> http://www.pgpool.net/mediawiki/index.php/TODO#Ability_to_load_balance_based_on_Client_IP
>> >>
>> >> The original poster of the TODO item proposed an ablility to control
>> >> load balancing based on client IP, rather than database and tables
>> >> names. Would this help you if it's implemented?
>> >>
>> >> Best regards,
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese: http://www.sraoss.co.jp
>> >>
>> >> > Thanks for your attention,
>> >> > Csaba
>> >> >
>> >> >
>> >> > 2014-04-04 9:13 GMT+02:00 Tatsuo Ishii <ishii at postgresql.org>:
>> >> >
>> >> >> > We're testing it with your suggested 0 backend weight in the slave. It
>> >> >> > working fine without load balancing mode.
>> >> >> >
>> >> >> > We would like to steer clear of the replication lag problems, that
>> >> reason
>> >> >> > we're using synchronous replication with remote_write level settings.
>> >> >>  But
>> >> >> > I see it is not enough.
>> >> >>
>> >> >> No, it's not. PostgreSQL's synchronous replication does not guarantee
>> >> >> that on standby sever the log record has been replayed when master
>> >> >> returns a commit message. In short, there's always replication lag.
>> >> >>
>> >> >> > Can You suggest us an useful solution?
>> >> >>
>> >> >> As long as you use PostgreSQL's streaming replication, there's no
>> >> >> solution for replication delay. Possible solution would be:
>> >> >>
>> >> >> 1) modify your application so that it's ready for replication lag.
>> >> >>
>> >> >> 2) use pgpool's native replication mode.
>> >> >>
>> >> >> > Thx,
>> >> >> > Csaba
>> >> >> >
>> >> >> >
>> >> >> > 2014-04-02 1:15 GMT+02:00 Tatsuo Ishii <ishii at postgresql.org>:
>> >> >> >
>> >> >> >> Humm.. there's no error messages in the pgpool log. Maybe the
>> >> >> >> replication lag? To make sure that if that is the cause of the
>> >> >> >> problem, you could set the slave's backend weight to 0 and restart
>> >> >> >> pgpool-II.
>> >> >> >>
>> >> >> >> Best regards,
>> >> >> >> --
>> >> >> >> Tatsuo Ishii
>> >> >> >> SRA OSS, Inc. Japan
>> >> >> >> English: http://www.sraoss.co.jp/index_en.php
>> >> >> >> Japanese: http://www.sraoss.co.jp
>> >> >> >>
>> >> >>
>> >>
>> _______________________________________________
>> pgpool-hackers mailing list
>> pgpool-hackers at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
> 
> 
> -- 
> Yugo Nagata <nagata at sraoss.co.jp>