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

[Tatsuo Ishii]

[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
>> >> >>
>> >>
>>