1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
|
<html>
<head>
<title>firstworks SQL Relay Load Balancing and Failover</title>
<link href="css/styles.css" rel="stylesheet">
</head>
<body>
<span class="heading1">SQL Relay Load Balancing and Failover</span><br><br>
<ul>
<li><a href="#backendloadbalancing">Distributing Load Over a Pool of Database Servers</a></li>
<li><a href="#backendfailover">Database Server Failover</a></li>
<li><a href="#frontendloadbalancing">Distributing Load Over a Pool of SQL Relay Servers</a></li>
<li><a href="#frontendfailover">SQL Relay Server Failover</a></li>
</ul>
<a name="backendloadbalancing"></a>
<span class="heading1">Distributing Load Over a Pool of Database Servers</span><br><br>
<p>There are probably dozens of commercial and Open Source Load Balancers
available today. They are often implemented differently, and some have
unique features, but they all provide a single IP address for a pool of servers
and distribute incoming connections over the pool.</p>
<p>It's possible to set up a pool of database servers behind a Load Balancer.
Database clusters such as Oracle RAC come with their own built-in
load-balancer. SQL Relay should work just fine with these systems. Just
configure the instance of SQL Relay to connect to the IP address of the pool or
cluster and the Load Balancer will distribute the connections over the pool of
servers.</p>
<p>If you don't have a Load Balancer or would rather not use one, SQL Relay can
still distribute load over a pool of database servers. In effect, SQL Relay is
a very specialized Load Balancer. An instance of SQL Relay can be configured
to maintain connections to more than one database server and distribute client
sessions over the pool of servers it's connected to.</p>
<p>Note that SQL Relay distributes client sessions, not individual queries.
When a client connects to the SQL Relay listener daemon, it will be assigned to
one of the database servers in the pool, and as long as it remains connected,
all of it's queries will be run against the same database server. If the
client disconnects and reconnects, it may be assigned to a different database
server the next time around.</p>
<p>To configure an instance of SQL Relay to connect to a set of database
servers, you have to define multiple connection tags, as follows:</p>
<blockquote>
<pre>
<font color="#0000ff"><?</font><font color="#2e8b57"><b>xml</b></font><font color="#2e8b57"><b> </b></font><font color="#2e8b57"><b>version</b></font>=<font color="#ff00ff">"1.0"</font><font color="#0000ff">?></font>
<font color="#008b8b"><!</font><font color="#a52a2a"><b>DOCTYPE</b></font> instances <font color="#a52a2a"><b>SYSTEM</b></font> <font color="#ff00ff">"sqlrelay.dtd"</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">instances</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">"example"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">"9000"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">"/tmp/example.socket"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">"oracle8"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">"15"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">"15"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">"0"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">"1"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">"60"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">"commit"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">"600"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">"nobody"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">"nobody"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">"5"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">"listener"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">"pass"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">""</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">""</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">"none"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">"65536"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">"4000"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">"71680"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">"-1"</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">users</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">"user1"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">"password1"</font><font color="#008b8b">/></font>
<font color="#008b8b"></users></font>
<font color="#008b8b"><</font><font color="#008b8b">connections</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">"DB1"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">"user=exampleuser1;password=examplepassword1;oracle_sid=EXAMPLE1;"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">"1"</font><font color="#008b8b">/></font>
<font color="#008b8b"><</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">"DB2"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">"user=exampleuser2;password=examplepassword2;oracle_sid=EXAMPLE2;"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">"1"</font><font color="#008b8b">/></font>
<font color="#008b8b"><</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">"DB3"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">"user=exampleuser3;password=examplepassword3;oracle_sid=EXAMPLE3;"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">"1"</font><font color="#008b8b">/></font>
<font color="#008b8b"></connections></font>
<font color="#008b8b"></instance></font>
<font color="#008b8b"></instances></font>
</pre>
</blockquote>
<p>In this example, SQL Relay will maintain 15 persistent database connections.
Since this instance is configured to connect to 3 different database servers,
SQL Relay will maintain 5 persistent connections to each server.</p>
<p>The <b>metric</b> attribute may be used to alter the distribution of
connections over the databases in the pool. Lets say that the server running
DB1 and DB2 are old machines, but the server running DB3 is brand new and can
handle twice as many connections as DB1 or DB2. Assigning a metric of 1 to DB1
and DB2 and 2 to DB3 will cause twice as many connections to be started to
DB3 than either DB1 or DB2, making it twice as likely that a client will use
DB3 than either DB1 or DB2. In this example, only 15 connections will be
started, but 7 or 8 will be started against DB3 and 3 or 4 will be started
against each of DB1 and DB2.</p>
<p>For instance:</p>
<blockquote>
<pre>
<font color="#0000ff"><?</font><font color="#2e8b57"><b>xml</b></font><font color="#2e8b57"><b> </b></font><font color="#2e8b57"><b>version</b></font>=<font color="#ff00ff">"1.0"</font><font color="#0000ff">?></font>
<font color="#008b8b"><!</font><font color="#a52a2a"><b>DOCTYPE</b></font> instances <font color="#a52a2a"><b>SYSTEM</b></font> <font color="#ff00ff">"sqlrelay.dtd"</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">instances</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">instance</font><font color="#008b8b"> </font><font color="#2e8b57"><b>id</b></font>=<font color="#ff00ff">"example"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>port</b></font>=<font color="#ff00ff">"9000"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>socket</b></font>=<font color="#ff00ff">"/tmp/example.socket"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>dbase</b></font>=<font color="#ff00ff">"oracle8"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connections</b></font>=<font color="#ff00ff">"15"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxconnections</b></font>=<font color="#ff00ff">"15"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxqueuelength</b></font>=<font color="#ff00ff">"0"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>growby</b></font>=<font color="#ff00ff">"1"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>ttl</b></font>=<font color="#ff00ff">"60"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>endofsession</b></font>=<font color="#ff00ff">"commit"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>sessiontimeout</b></font>=<font color="#ff00ff">"600"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasuser</b></font>=<font color="#ff00ff">"nobody"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>runasgroup</b></font>=<font color="#ff00ff">"nobody"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>cursors</b></font>=<font color="#ff00ff">"5"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>authtier</b></font>=<font color="#ff00ff">"listener"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>handoff</b></font>=<font color="#ff00ff">"pass"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>deniedips</b></font>=<font color="#ff00ff">""</font><font color="#008b8b"> </font><font color="#2e8b57"><b>allowedips</b></font>=<font color="#ff00ff">""</font><font color="#008b8b"> </font><font color="#2e8b57"><b>debug</b></font>=<font color="#ff00ff">"none"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxquerysize</b></font>=<font color="#ff00ff">"65536"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxstringbindvaluelength</b></font>=<font color="#ff00ff">"4000"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>maxlobbindvaluelength</b></font>=<font color="#ff00ff">"71680"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>idleclienttimeout</b></font>=<font color="#ff00ff">"-1"</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">users</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">user</font><font color="#008b8b"> </font><font color="#2e8b57"><b>user</b></font>=<font color="#ff00ff">"user1"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>password</b></font>=<font color="#ff00ff">"password1"</font><font color="#008b8b">/></font>
<font color="#008b8b"></users></font>
<font color="#008b8b"><</font><font color="#008b8b">connections</font><font color="#008b8b">></font>
<font color="#008b8b"><</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">"DB1"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">"user=exampleuser1;password=examplepassword1;oracle_sid=EXAMPLE1;"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">"1"</font><font color="#008b8b">/></font>
<font color="#008b8b"><</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">"DB2"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">"user=exampleuser2;password=examplepassword2;oracle_sid=EXAMPLE2;"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">"1"</font><font color="#008b8b">/></font>
<font color="#008b8b"><</font><font color="#008b8b">connection</font><font color="#008b8b"> </font><font color="#2e8b57"><b>connectionid</b></font>=<font color="#ff00ff">"DB3"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>string</b></font>=<font color="#ff00ff">"user=exampleuser3;password=examplepassword3;oracle_sid=EXAMPLE3;"</font><font color="#008b8b"> </font><font color="#2e8b57"><b>metric</b></font>=<font color="#ff00ff">"2"</font><font color="#008b8b">/></font>
<font color="#008b8b"></connections></font>
<font color="#008b8b"></instance></font>
<font color="#008b8b"></instances></font>
</pre>
</blockquote>
<p>If the <b>maxconnections</b> attribute is greater than the <b>connections</b>
attribute and conditions are such that new connections need to be spawned, the
number of new connections that will be spawned against each database server is
proportional to the metric for that database server. In our example, (if
<b>maxconnections</b> were 25) if 10 new connections were spawned, 5 would be
spawned against DB3 and 2 or 3 would be spawned against each of DB1 and DB2.</p>
<a name="backendfailover"></a>
<span class="heading1">Database Server Failover</span><br><br>
<p>SQL Relay doesn't have any built-in database server failover mechanism. If
a database server that SQL Relay is connected to goes down, SQL Relay doesn't
currently open new connections to a different "failover" database to make up
for it. This is on the TODO list, but has not yet been implemented.</p>
<p>Currently, if an SQL Relay connection daemon notices that the database server
it is connected to has gone down it will mark itself unavailable to clients,
log out and loop, attempting to re-connect to that database server. If that
connection daemon is configured with the <b>behindloadbalancer</b> attribute
set to "no", then it will also raise a flag and all connection daemons
connected to that database server mark themselves unavailable to clients, close
their connections and loop, attempting to re-connect to that database server.
When the database server comes back up, as each connection daemon successfully
re-connects to the database server, it marks itself available to clients again.
While one database server is down, client sessions are still distributed over
the servers that are still up, albiet through a smaller pool of persistent
database connections.</p>
<p>On the other hand, some Load Balancers can facilitate failover. Some
Load Balancers can be configured to keep track of whether the servers it's
distributing load over are up and running. If one of them isn't, the Load
Balancer removes it from the pool and adds it back later if it comes back up.
If one of the databases SQL Relay is connected to through a Load Balancer goes
down, when SQL Relay tries to re-connect, it will end up connecting to
a different database server.</p>
<a name="frontendloadbalancing"></a>
<span class="heading1">Distributing Load Over a Pool of SQL Relay Servers</span><br><br>
<p>If your pool of application servers and web servers is sufficiently large,
you might want to set up a pool of SQL Relay servers between them rather than
just a single server.</p>
<p>You can use a Load Balancer to make a pool of SQL Relay servers appear to be
a single server. If you do though, you need to make sure that the
<b>handoff</b> attribute is set to "pass" rather than "reconnect" and you need
to make sure that handoff="pass" actually works on the platform you're running
SQL Relay on. See <a href="configuring.html">Configuring SQL Relay</a> for
more information about the <b>handoff</b> attribute.</p>
<p>If you don't have a Load Balancer or would rather not use one, you can
still set up a pool of SQL Relay servers and distribute client connections over
them using <a href="http://hacks.oreilly.com/pub/h/79">Round Robin DNS</a>.</p>
<p>If you use Round Robin DNS to distribute load over a pool of SQL Relay
servers, you can set the <b>handoff</b> attribute to either "pass" or
"reconnect". On platforms where handoff="pass" does not work, Round Robin DNS
is the only option for balancing load over a pool of SQL Relay servers.
See <a href="configuring.html">Configuring SQL Relay</a> for more information
about the <b>handoff</b> attribute.</p>
<a name="frontendfailover"></a>
<span class="heading1">SQL Relay Server Failover</span><br><br>
<p>SQL Relay clients can only be configured to connect to a single SQL Relay
server. The client can also be configured to attempt to reconnect if the
server is unavailable. This ability, combined with a Load Balancer or
Round Robin DNS can facilitate failover. In effect, the SQL Relay client can
be coerced into trying to log into a server, then if that server is down, try
another server, then another, and so on until either all servers are determined
to be down or the client successfully logs into one of them.</p>
<p>Some Load Balancers can be configured to keep track of whether the servers
it's distributing load over are up and running. If one of them isn't, the Load
Balancer removes it from the pool and adds it back later if it comes back up.
When running against an SQL Relay server behind a Load Balancer, the client
can be configured to attempt to log in to the SQL Relay server several times
with a short pause between each attempt. If an attempt fails, the Load
Balancer should soon realize that the SQL Relay server is down and remove it
from the pool. After that, it's likely that a future attempt to log in to the
SQL Relay server will be directed to a server that is running and will succeed.
</p>
<p>For example, this C++ client is configured to log into the server sqlrserver
on port 9000. It will try to log in 10 times with a 1 second pause between
tries.</p>
<blockquote>
<pre>
sqlrconnection sqlrcon(<font color="#ff00ff">"sqlrserver"</font>,<font color="#ff00ff">9000</font>,<font color="#ff00ff">NULL</font>,<font color="#ff00ff">"sqlruser"</font>,<font color="#ff00ff">"sqlrpass"</font>,<font color="#ff00ff">1</font>,<font color="#ff00ff">10</font>);
</pre>
</blockquote>
<p>If you don't have a Load Balancer or would rather not use one, you can
still implement SQL Relay server failover using
<a href="http://hacks.oreilly.com/pub/h/79">Round Robin DNS</a>.</p>
<p>The SQL Relay client has a minimal built-in failover facility. If Round
Robin DNS is set up, when the SQL Relay client looks up the IP address of the
host it's trying to connect to, it actually gets back a list of all of the IP
addresses in the pool. It tries to connect to the first address in the list.
If it fails, it tries the next address, then the next, etc. Eventually, it will
either succeed in connecting to one of the servers or run out of addresses and
fail.</p>
<p>For example, this C++ client is configured to log into the server sqlrserver
on port 9000. It will try to log in to each address that <b>sqlrserver</b>
resolves to.</p>
<blockquote>
<pre>
sqlrconnection sqlrcon(<font color="#ff00ff">"sqlrserver"</font>,<font color="#ff00ff">9000</font>,<font color="#ff00ff">NULL</font>,<font color="#ff00ff">"sqlruser"</font>,<font color="#ff00ff">"sqlrpass"</font>,<font color="#ff00ff">0</font>,<font color="#ff00ff">1</font>);
</pre>
</blockquote>
</body>
</html>
|
