<div dir="ltr"><div><div><div><div><div><div>Hi Tatsuo,<br><br></div>We have two independent replication clusters. One system has a single master/slave setup in our primary data center. The other has a master with two slaves. Both systems replicate to a colocation data center, with one big server acting as the slave for both masters. (The connection to the co-lo is over gig fiber, so our latency remains low)<br><br>So, that's partially why Slony comes into play. We need that fine grained control over replication. The second reason, is that our write traffic is 47% of our total db traffic. (And that's a separate, long, shameful story) But the fact is, pg's streaming replication had too much overhead from copying the WAL files around, and choked up our servers during peak write traffic. We also had Enterprise DB do a consulting job for us, and they came to the conclusion that the way we're doing it is the only reliable way.<br><br></div>So I think that circles around back to square one, of being able to direct specific select queries to the master under certain conditions. I really like your new feature that adds that ability, but would it be possible to trigger that condition with a client SET variable in the same client connection? Something like:<br><br></div><span style="font-family:courier new,monospace"> SET force_master = on;<br></span></div><span style="font-family:courier new,monospace"> SELECT * from hot_data;<br></span></div><span style="font-family:courier new,monospace"> SET force_master = off;<br></span></div><span style="font-family:courier new,monospace"> SELECT * from static_data;</span><br><div><div><div><div><div><div><br></div><div>We have a mix of clients with various abilities, but creating a separate database connection for the master node, and a separate connection for the slave nodes is going to be problematic. The main feature I like about pgpool is having one single client connection work on the replicated cluster. A single SET command on that connection to control the master logic is feasible to implement in our system. We can identify the hot queries and add it in manually. But two separate connections requires a lot more work. Is something like that possible to do in pgpool?<br><br></div><div>Thank you for any advice you have!<br><br></div><div>--Cal<br></div><div><br></div></div></div></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Oct 17, 2014 at 6:53 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Using pgpool-II's own replication mode is an idea. However it has it's<br>
own downside: write performance could drop up to 50% of single<br>
PostgreSQL, you may have difficulties with certain queries (see manual<br>
for more details). I recommend you to test before switching to the<br>
mode.<br>
<br>
BTW why do you use slony? From my experience PostgreSQL's streaming<br>
replication shows better performance than slony.<br>
<br>
If you are going to use streaming replication, you may be interested<br>
in upcoming pgpool-II 3.4's new feature: more control on load<br>
balancing. From 3.4 doc:<br>
<br>
database_redirect_preference_list<br>
you can set "database name:node id" pair to specify the node id<br>
when connecting to the database. For example, by specifying<br>
"test:1", pgpool-II always redirects SELECT to node 1 in case of<br>
connecting to database "test". You can specify multiple "database<br>
name:node id" pair by separating them using comma (,). Regular<br>
expressions are accepted for database name. Special keyword<br>
"primary" indicates the primary node and "standby" indicates one<br>
of standby nodes.<br>
<br>
Here is an example.<br>
<br>
database_redirect_preference_list = 'postgres:primary,mydb[01]:1,mydb2:standby'<br>
<br>
<br>
SELECTs will be redirected to primary if you connect to postgres<br>
database. Connecting to mydb0 or mydb1 will redirect SELECTs to<br>
node 1. Connecting to mydb2 will redirect SELECTs to one of<br>
standby nodes.<br>
<br>
You need to reload pgpool.conf if you change this directive.<br>
<br>
app_name_redirect_preference_list<br>
<br>
you can set "application name:node id" pair to specify the node id<br>
when the application is used. "Application name" is a name<br>
specified by a client when it connects to database. You can use it<br>
in PostgreSQL 9.0 or later. For example, application of psql<br>
command is "psql". pgpool-II recognize application names only when<br>
clients sends a start up packet. Clients can send application<br>
names later on but pgpool-II will not recognize them.<br>
<br>
The notion of app_name_redirect_preference_list is same as<br>
database_redirect_preference_list. Thus you can use regular<br>
expressions for application name.<br>
<br>
Here is an example.<br>
<br>
app_name_redirect_preference_list = 'psql:primary,myapp1:1,myapp2:standby'<br>
<br>
<br>
In this example, psql sends SELECTs to primary node, myapp1 sends<br>
to node 1, and myapp2 sends to one of standby nodes.<br>
<br>
app_name_redirect_preference_list takes precedence over<br>
database_redirect_preference_list. See the next example.<br>
<br>
database_redirect_preference_list = 'bigdb:primary'<br>
app_name_redirect_preference_list = 'myapp:2'<br>
<br>
<br>
Applications connecting to bigdb database send SELECTs to primary<br>
node. However myapp sends SELECTs to node 2 even if it connects to<br>
bigdb. This is useful in a scenario: myapp2 sends very heavy<br>
SELECTs to execute analysis jobs. You want to use node 2 solely<br>
for analysis purpose.<br>
<br>
You need to reload pgpool.conf if you change this directive.<br>
<span class=""><br>
<br>
> Thank you Tatsuo,<br>
><br>
> What solution is there for implementing HA in such a scenario? Threaded<br>
> requests, to threaded client connections, to a master/slave replication<br>
> system. Would I have to use pgpool's Replication Mode and ditch the Slony<br>
> approach?<br>
><br>
> --Cal<br>
><br>
</span>> <<a href="mailto:cal@fbsdata.com">cal@fbsdata.com</a>><br>
<div class="HOEnZb"><div class="h5">><br>
> On Thu, Oct 16, 2014 at 6:06 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>> wrote:<br>
><br>
>> > Hi everyone,<br>
>> ><br>
>> > I have encountered a problem with pgpool using Slony for replication to<br>
>> > slaves. The client to pgpool in my case is an API, so requests come in<br>
>> > concurrently on independent connections. If we have an insert directly<br>
>> > followed by a select for that record, the select is load balanced between<br>
>> > the master and slave, resulting in a 50% chance of a failure. Since the<br>
>> > select ends up coming from a separate httpd child process, there is no<br>
>> way<br>
>> > to keep the insert and select in the same transaction.<br>
>> ><br>
>> > Am I missing something in the documentation and configs? Can pgpool<br>
>> > intelligently recognize specific records that have been inserted to a<br>
>> > table, then direct all selects for that record to the master until Slony<br>
>> > has replicated it to the slaves?<br>
>><br>
>> No. That's the limitaion of Slony (more presicely, asynchronous<br>
>> replication systems include streaming replication).<br>
>><br>
>> Best regards,<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
</div></div></blockquote></div><br></div></div>