forked from kevinboone/kevinboone.github.io
-
Notifications
You must be signed in to change notification settings - Fork 0
/
artemis_jdbc.html
221 lines (183 loc) · 9.66 KB
/
artemis_jdbc.html
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
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Kevin Boone: Using an Oracle database as storage for the Apache Artemis message broker -- part 1</title>
<link rel="shortcut icon" href="https://kevinboone.me/img/favicon.ico">
<meta name="msvalidate.01" content="894212EEB3A89CC8B4E92780079B68E9"/>
<meta name="google-site-verification" content="DXS4cMAJ8VKUgK84_-dl0J1hJK9HQdYU4HtimSr_zLE" />
<meta name="description" content="%%DESC%%">
<meta name="author" content="Kevin Boone">
<meta name="viewport" content="width=device-width; initial-scale=1; maximum-scale=1">
<link rel="stylesheet" href="css/main.css">
</head>
<body>
<div id="myname">
Kevin Boone
</div>
<div id="menu">
<a class="menu_entry" href="index.html">Home</a>
<a class="menu_entry" href="contact.html">Contact</a>
<a class="menu_entry" href="cv.html">CV</a>
<a class="menu_entry" href="software.html">Software</a>
<a class="menu_entry" href="articles.html">Articles</a>
<form id="search_form" method="get" action="https://duckduckgo.com/" target="_blank"><input type="text" name="q" placeholder="Search" size="5" id="search_input" /><button type="submit" id="search_submit">🔍</button><input type="hidden" name="sites" value="kevinboone.me" /><input type="hidden" name="kn" value="1" /></form>
</div>
<div id="content">
<h1>Using an Oracle database as storage for the Apache Artemis message broker -- part 1</h1>
<p>
<img class="article-top-image" src="img/activemq-logo.png"
alt="ActiveMQ logo"/>
Most users of the Artemis message broker will use the standard, file-based
message store. For fault-tolerance, however, it's necessary to replicate
this store in some way. We could use the built-in file replication
machinery in Artemis, or we could use a network file store that is
itself replicated. The former -- the built-in replication --
has the problem of requiring an extensive installation -- at least
six message brokers, or an external active/passive coordinator based on
Apache Zookeeper. The latter approach -- a replicated network file system --
requires its own infrastructure, which will probably be complex, or
expensive, or both.
</p>
<p>
An alternative approach is to use a relational database as the message
data store. For fault-tolerance this also needs to be replicated or,
at least, have a regular mirroring policy. I would not recommend anybody
to go and <i>buy</i> a relational database with replication, just to
provide a message store for Artemis; but many
organizations already have such a database. These organizations also tend
to have a lot of in-house expertise that will be useful in
setting up and tuning the database.
So using a relational database is, in some circumstances, an attractive
proposition.
</p>
<p>
This article explains in outline how to set up the Artemis broker to
use an Oracle database as its store. Artemis will work with other databases, but
Oracle is the one I have some experience with. I should point out that
unless your database is itself fault-tolerant,
<i>no advantage whatsoever</i> attaches to using it in preference to
a local file store. In fact,
there are many disadvantages -- tuning the database to get good throughput
is not a trivial operation (and it's not one I'll be going into in this
introductory article).
</p>
<h2>Basic Oracle set-up</h2>
<p>
Initially we require at the Oracle level only a user who has
rights to perform basic (<code>select</code>, <code>create table</code>,
<code>create index</code>...)
SQL operations. I assume in this article that the user is authenticated
by username and
password but, of course, Oracle provides other mechanisms.
</p>
<p>
Artemis does not require a private Oracle table space,
or even a private user. However, using a specific user with its own
table space does make administration easier. And, of course, the user
needs to have sufficient table space <i>somewhere</i>
to store the broker's data, which can be extensive.
</p>
<blockquote class="notebox"><b>Note:</b><br/>Artemis will participate in XA transactions, but it won't coordinate transactions against a relational database. You don't need the database use to have rights on transaction-related tables.</blockquote>
<p>
It helps enormously, in the set-up and tuning stages, to have SQL access
to the database. That way you'll be able to check that tables are being
created and manipulated as expected, and you'll be able to reset the database
schema during testing, should you need to start fresh.
</p>
<blockquote class="notebox"><b>Note:</b><br/>Artemis won't easily support Oracle databases earlier than 12c, because it relies on auto-increment column support. You'd have to create the database schema manually, and define custom sequences and triggers to get the auto-increment behaviour that Artemis needs.</blockquote>
<h2>Getting and installing the database driver</h2>
<p>
Drivers are available from Oracle's
<a href="https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html" target="_blank">JDBC downloads page</a>.
Because later Artemis versions support only Java versions 11 and later,
in principle you'll need one of the variants of <code>ojdbc11.jar</code> from
Oracle. Oracle issues these for each database version -- backward
compatibility is pretty good, but you shouldn't push your luck -- use
the proper version for your database.
</p>
<p>
Installation in Artemis amounts to copying
<code>ojdbc11.jar</code> to the <code>lib/</code> directory of the Artemis
instance.
</p>
<h2>Configuring Artemis</h2>
<p>
Here is a basic store configuration -- just add it to <code>broker.xml</code>
in the <core> section.
</p>
<pre class="codeblock"><b><font color="#0000FF"><store></font></b>
<b><font color="#0000FF"><database-store></font></b>
<b><font color="#0000FF"><jdbc-driver-class-name></font></b>oracle.jdbc.OracleDriver<b><font color="#0000FF"></jdbc-driver-class-name></font></b>
<b><font color="#0000FF"><jdbc-connection-url></font></b>jdbc:oracle:thin:@my_oracle_host:1521/my_service?user=kevin<b><font color="#000080">&amp;</font></b>password=kevin<b><font color="#0000FF"></jdbc-connection-url></font></b>
<b><font color="#0000FF"><bindings-table-name></font></b>B<b><font color="#0000FF"></bindings-table-name></font></b>
<b><font color="#0000FF"><message-table-name></font></b>M<b><font color="#0000FF"></message-table-name></font></b>
<b><font color="#0000FF"><page-store-table-name></font></b>MP<b><font color="#0000FF"></page-store-table-name></font></b>
<b><font color="#0000FF"><large-message-table-name></font></b>LM<b><font color="#0000FF"></large-message-table-name></font></b>
<b><font color="#0000FF"><node-manager-store-table-name></font></b>NM<b><font color="#0000FF"></node-manager-store-table-name></font></b>
<i><font color="#9A1900"><!--jdbc-user>kevin</jdbc-user></font></i>
<i><font color="#9A1900"> <jdbc-password>kevin</jdbc-password--></font></i>
<b><font color="#0000FF"></database-store></font></b>
<b><font color="#0000FF"></store></font></b>
</pre>
<p>
You can remove any existing configuration related to the file-based
message store, as it won't be used. It won't hurt to leave it in place,
but its presence may be confusing.
</p>
<p>
Notice that I have specified my credentials on the database connection
URL, even though there are specific entries in the configuration for
credentials. The Artemis broker will accept credentials in both places
<i>but the diagnostic tools won't</i>. Tools like <code>artemis data exp</code>,
that export the message store in XML format, do work with a database as the
message store -- but they only work if the credentials are specified
on the URL.
</p>
<p>
I have chosen short names for the tables. It's important to bear in mind
that some of these names are merely <i>prefixes</i> -- a 12-digit
number will be appended to them. Oracle installations often have
restrictions on the lengths of identifier names.
</p>
<h2>Creating the database schema</h2>
<p>
In the first instance, there should be no need to create the database
schema administratively. The broker will do it automatically the
first time it starts up with JDBC persistence enabled. When moving into
full production the schema will probably need to be tuned -- I will
describe the schema in more detail in later articles in this series.
</p>
<h2>Testing</h2>
<p>
If you're removed all configuration for a file-based store from your
configuration, you'll know that the installation basically works when
you can send and receive messages using messaging clients of
the broker. After all, there isn't anywhere else to store the data.
</p>
<h2>Closing remarks</h2>
<p>
Configuring Artemis to use an Oracle database for storage is straightforward
-- at least in the early stages. You'll quickly find out, however, the broker
will not be as fast as local file storage, and probably not even as fast
as networked file storage. The throughput can be improved by careful
tuning, and using a database is usually makes it much easier to set up
a fault-tolerant
broker cluster than any kind of file-based store -- so long as the datbase
infrastructure is already in place.
</p>
<p>
In the <a href="artemis_jdbc_2.html">second article in this series</a>,
I'll explain the database schema, and how it is managed by the
broker, in more detail.
</p>
<p><span class="footer-clearance-para"/></p>
</div>
<div id="footer">
<a href="rss.html"><img src="img/rss.png" width="24px" height="24px"/></a>
Categories: <a href="middleware-groupindex.html">middleware</a>
<span class="last-updated">Last update Apr 29 2023
</span>
</div>
</body>
</html>