Sequoia Tutorial – A quick start
Authors of previous version :
Nicolas Modrzyk (Nicolas.Modrzyk@inrialpes.fr) Emmanuel Cecchet (Emmanuel.Cecchet@inrialpes.fr)
Author :
Stephane Giron (Stephane.Giron@emicnetworks.com)
Version Date
1.0 03/08/06
Table of Contents
1. 2. 3. 4. 5. 6. 7. Introduc tion ..................................................................................................................................................2 Getting Sequoia .........................................................................................................................................4 Installing Sequoia compone n t s .............................................................................................................5 A quick tour of the Sequoia directory .................................................................................................9 Description of the RAIDb1 Demo .......................................................................................................11 Description of the distribute d - raidb1 Demo .................................................................................12 Start and play with the RAIDb1 demo ..............................................................................................13 Starting the demo ....................................................................................................................................13 Playing with iSQL, the SQL console ...................................................................................................14 8. First steps with the Administra tion Console ................................................................................16 Starting the console ................................................................................................................................16 Disabling a backen d ................................................................................................................................17 Enabling a disabled backend ...............................................................................................................17 Creating a backup of a backend .........................................................................................................18 Adding a backen d to the cluster. .......................................................................................................18 9. Further readings .......................................................................................................................................21
1
1. Introduction
This is a docu me n t to help you learn the basics and get started with the databa se clustering software called Sequoia. This docum e n t is based on C- JDBC tutorial – A quick start (http: / / c jdbc.objectweb.org / c u r r e n t / d o c / t u t o r ial.p d f ) To procee d with this tutorial you will only need a Java Virtual Machine complian t to J2SE 1.4.2 or greater. Sequoia is a databas e cluster middlewa re that allows any Java application (standalo n e application, servlet or EJB container, ...) to trans p a r e n tly access a cluster of databa se s through JDBC(tm). You do not have to modify client applications, application servers or databa se server software. You just have to ensure that all databa se accesses are perfor m e d through JDBC. Sequoia provides a flexible architecture that allows you to achieve scalability, high availability and failover with your database tiers. Sequoia instan tiates the concep t of RAIDb : Redun da n t Array of Inexpe nsive Database s. The databa s e is distribu te d and replicate d among several nodes and Sequoia load balance the queries between these nodes. Sequoia is a free , open source initiative. Sequoia provides a generic JDBC driver to be used by the clients. The client drivers forwar d the SQL reques ts to the Sequoia controller that balances the m on a cluster of replicate d database s (reads are load balanced and writes are broadca s te d). Sequoia can be used with any RDBMS providing a JDBC driver, that is to say almost all existing open source and com mercial databa ses.
2
Sequoia principle Sequoia allows to build any cluster configura tion including mixing heteroge neo u s databa se s. The main features provided by Sequoia are perfor ma nc e scalability, fault tolerance and high availability. Additional features such as monitoring, logging, SQL reques t s caching can be provide d as well. The architect ur e is widely open to allow anyone to plug custo m request s sched ulers, load balancers, connection manage rs, caching policies, ...
3
4
2. Getting Sequoia
•
You can get the latest release of Sequoia on the continu e n t.org site: http:/ / s e q u oia.continuent.org /
•
Choose an installer distribution in downloa d section: https: / / f o rg e.c ontinue nt.org / fr s / ? gro up_id = 6 Or click the following link to downloa d sequoia 2.1, the version we will assu me we are working with in this tutorial. https: / / f o rg e.c ontinue nt.org / fr s / d o w n l o a d.php / 4 / s e q u o ia - 2.1 - bininstaller.jar
When the download is complete d, double click on the jar file. If nothing hap pe n s, you have to type the com ma n d: java - jar Sequoia - 2.1- bin- installer.jar
•
Then the installation screen should appear:
5
3. Installing Sequoia components
•
First step is to read and accept the Apache license used by Sequoia.
6
•
Then choose the place where you want all the files to be installed. The directory will be create d if it does not exist. All the files will be overwritte n, if it was previously create d.
•
Next step is to select the different compo n e n t s to be installed:
The different compone n t s are: Sequoia Driver This is the JDBC driver that will be used by the client application talking to the databa s e.(ie: Tomcat, JOnAS,JBOSS,...) Sequoia Controller This is the main compo n e n t of Sequoia. This is the Sequoia controller that will handle and process reque sts coming from the Sequoia driver. Sequoia Console 7
To administra te, monito r, view statistics, and recover failed database backends, you can use a regular text console. Sequoia Documentation Contains the Sequoia reference docu me n t a tio n including an extensive description of how Sequoia is working, and how to configure it to the needs of the application. Sequoia Demo A set of demo files to easily set up replication on Hyperso nicSQL backends. If you do not select this package, Hyperso nicSQL will not be installed. iSQL This is a graphical SQL Console client. For the tutorial, we recom me n d installing all the comp o n e n t s, as we will describe them all in the next parts.
The installation proceeds and then we are done with the installer. It also generate s an uninstaller script to easily clean up files.
8
9
4. A quick tour of the Sequoia directory
•
After installation, this is the conten t of the new directory:
demo All the scripts for the examples are here config Location of the different configuration files. (Controller, VirtualDataba se, loggers, language files ...) doc The user guide and other configuration example files can be found here drivers The Sequoia driver can be found here. At runtime, other drivers can be unjar in this directory to be found by the java classloader. lib All the java libraries used by Sequoia log The logs generate d by the logging syste m can be found here. Also the Sequoia report will be generate d in this folder Uninstaller Scripts to remove all the files from this installation. xml The DTD and XSL files used by Sequoia. The two main DTDs can be found there to validate controller configuration files and virtual database configuration files.
3rdParty This is where HypersonicSQL and iSQL libraries can be found bin All the scripts to start / s t o p applications are here
•
Here we will give a short description of the differen t scripts that can be foun d in the bin directory of the installation. Sequoia script files : cons ole.bat / . s h : This is to start the ad ministra tio n console. controller.bat /.s h : This starts the controller with a default configuration file: $ SEQUOIA_HOME/config / co nt roller /co n troller.x ml Demo Files: demo - distributed - raidb1 - controller1.bat /. s h : This start s the distribu te d 1 st controller for the distribute d demo. demo - distributed - raidb1 - controller2.bat /. s h : This starts the secon d controller demo for the distribu te d demo. demo - raidb1.bat /. s h : This is anoth er demo. It will start a single controller with two hypersonic backends. Third Party Software Files:
10
isql.bat /.s h : The graphical SQL client hsqldb.bat /.s h : This starts a simple hsqldb DBMS databas e. It is a lightweight java sql server. It is used for the demos prese n te d in this tutorial.
11
5. Description of the RAIDb1 Demo
The raidb1 demo will start a total of three Hyperso nicSQL data ba se servers. 1. A first backen d to use as the main database, it will be named “localhost” in the tutorial 2. A second backend, to use as a failover databa se, it will be name d “localhost2” in the tutorial 3. A third backen d, used to store recovery informa tio n and to log recovery request s.
When the three database s have been started, the demo will start a controller on port 25323, and will autom a tically load a virtual data bas e, name d “myDB” that will have a schem a identical to the two backe nd s. This is a RAIDb1 replication scheme, meaning that all the backen d s have the exact same data. The schema is define d by the following create state me n t s:
CREATE TABLE ADDRESS( ID INTEGER NOT NULL PRIMARY KEY, FIRSTNAME VARCHAR(255), LASTNAME VARCHAR(255), STREET VARCHAR(255), CITY VARCHAR(255)) CREATE TABLE PRODUCT( ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(255), COST DECIMAL) CREATE TABLE DOCUMENT( ID INTEGER NOT NULL PRIMARY KEY, ADDRESSID INTEGER, TOTAL DECIMAL) CREATE TABLE PPOSITION( DOCUMENTID INTEGER NOT NULL, PPOSITION INTEGER NOT NULL, PRODUCTID INTEGER,QUANTITY INTEGER, PRICE DECIMAL)
While using iSQL, connecting to the Sequoia controller, you will have a databa se corres po n di ng to this view. You can connect to the controller, from the Sequoia driver using the following URL: jdbc:sequoia:/ /localhost / m y DB
12
6. Description of the distributed - raidb1 Demo
The distribute d - raidb1 demo will start a total of four Hyperso nicSQL datab a se servers. 1. Two backends for one controller, on port 25322. 2. Two other backend s for anothe r controller, on port 25323. There is no recovery log define d in the distribute d demo, but you can easily add one by copying the simple raidb1 demo files for each controller. The demo will then start the two controllers, and in the outp u t log, you will see a group comm u nication will be establishe d between the two. The data ba se schem a of the cluster is the same as the one define d in the RAIDb1 demo, and still we are in a RAIDb1 distribution scheme, so all the four backen d s, will have the same data. You can access the cluster with the following URL: jdbc:sequoia:/ /localhost:25322,localhost:25323 / m y DB The simple URL describe d above will also work (jdbc:seq uoia: / / l o cal h o s t / m y DB) as the driver will find by itself a cluster has been forme d.
13
7. Start and play with the RAIDb1 demo
•
Starting the demo
Let's execute the script called demo - raidb1.sh (under Unix) or demo - raidb 1.ba t (under windows), located in the $SEQUOIA_HOME/de m o directory. If you have a shell window open, this should have the following outp u t:
This effectively starts 2 database s HypersonicSQL and a Sequoia controller. The two databa se s will be manage d and accesse d via the Sequoia controller. Note: Under windows, you have to press a key once the two Hyperso nicSQL backen d s have displaye d “Listening ...”.
14
•
Playing with iSQL, the SQL console
Now we can start iSQL, using the script (iSQL.sh /.bat foun d in the bin directory), and we should quickly get this:
iSQL with Sequoia is configure d with the Sequoia driver and the Hyperso nicSQL drivers. There are four services available straight away: sequoia This is the database that the application will see. To connect to it, the application will use the Sequoia driver. hsqldb - 9001 This is one of the two backen ds that the controller manages. hsqldb - 9002 This is the replication of the above databa s e. hsqldb - recover y This is the database used to store recovery informa tio n and to log recovery request s.
15
Double clicking on any of the alias will connect you to the service. Just type the query you want in the text panel and click on the 'Execute Curre nt SQL Comma n d ' butto n.
16
8. First steps with the Administration Console
•
Starting the console
To start the console, we use the console.sh / b a t script file. The console script accept s several options:
-d or --debug: show stack trace when error occurs. -f or --file: Use a given file as the source of comma n d s instea d of reading comma n d s interactively. -h or --help: displays usage infor ma tio n. -i or --ip: IP addres s of the host name where the JMX Server hosting the controller is running (the default is '0.0.0.0'). -p or --port: JMX/RMI port number of (the default is 1090). -s or --secret: Password for JMX connectio n. -u or --username: userna m e for JMX connectio n. -v or --version: displays version infor ma tio n.
The console will start, showing differen t initializatio n message s :
Launching the Sequoia controller console Initializing Controller module... Initializing VirtualDatabase Administration module... Initializing Monitoring module... Initializing SQL Console module... Sequoia driver (v2.1) successfully loaded.
There's not much we can do if we don't have a virtual data bas e to work with. Let's check which virtual databa se s are available on the controller with the com ma n d 'show virtualdat a ba s e s'. As a result, you should see :
myDB
The next com ma n d to issue is 'admin myDB', to administra t e the virtual databa se. You will have to enter one of the admi nistr at o r login that has been specified in the configuration file of the virtual databa se. If you starte d the raidb1 demo, the login is admin with no passwor d. The result is as following :
host:1090 > admin myDB Virtual database Administrator Login > admin Virtual database Administrator Password > Ready to administrate virtual database myDB myDB(admin) >
To check backen ds states, use the com ma n d 'show backen d s' (this will list all backen d s in the virtual databa se) and 'show backen d localhos t' (for exam ple, to check the properties of the backend called 'localhos t')
myDB(admin) > show backends localhost localhost2 myDB(admin) > show backend localhost +--------------------------+-----------------------------------+ | Backend Name | localhost |
17
| Driver | org.hsqldb.jdbcDriver | | URL | jdbc:hsqldb:hsql://localhost:9001 | | Active transactions | 0 | | Pending Requests | 0 | | Read Enabled | true | | Write Enabled | true | | Is Initialized | true | | Static Schema | false | | Connection Managers | 1 | | Total Active Connections | 5 | | Total Requests | 3 | | Total Transactions | 0 | | Last known checkpoint |
| +--------------------------+-----------------------------------+
•
Disabling a backend
The comm a n d to disable a backend is 'disable' followed by the name of the backen d, e.g. 'localhost 2' . For this com ma n d, you have to be in virtual databas e administerin g mode.
host:1090 > admin myDB Virtual database Administrator Login > admin Virtual database Administrator Password > Ready to administrate virtual database myDB myDB(admin) > show backends localhost localhost2 myDB(admin) > disable localhost2 Disabling backend localhost2 with automatic checkpoint. myDB(admin) > show backend localhost2 +--------------------------+----------------------------------------------------------+ | Backend Name | localhost2 | | Driver | org.hsqldb.jdbcDriver | | URL | jdbc:hsqldb:hsql://localhost:9002 | | Active transactions | 0 | | Pending Requests | 0 | | Read Enabled | false | | Write Enabled | false | | Is Initialized | false | | Static Schema | false | | Connection Managers | 1 | | Total Active Connections | 4 | | Total Requests | 3 | | Total Transactions | 0 | | Last known checkpoint | disable_backend_localhost2_Fri Aug 19 09:54:50 CEST 2005 | +--------------------------+----------------------------------------------------------+
The backend is going to be disabled, meaning that no more reques ts will be sent to it from the Sequoia controller.
•
Enabling a disabled backend
From the disabled backend, you can play a few reques t s using squirrel. You could also possibly try to insert some values, and check they still arrive on the hsqldb 'localhos t' backend. Now we want to enable our previously disable d backen d 'localhost2 ', with the comma n d 'enable' followed by the name of the backe nd as shown below.
myDB(admin) > enable localhost2 Enabling backend localhost2 from its last known checkpoint myDB(admin) > show backend localhost2 +--------------------------+-----------------------------------+ | Backend Name | localhost2 | | Driver | org.hsqldb.jdbcDriver | | URL | jdbc:hsqldb:hsql://localhost:9002 | | Active transactions | 0 | | Pending Requests | 0 | | Read Enabled | true |
18
| Write Enabled | true | | Is Initialized | true | | Static Schema | false | | Connection Managers | 1 | | Total Active Connections | 10 | | Total Requests | 6 | | Total Transactions | 0 | | Last known checkpoint | | +--------------------------+-----------------------------------+
No need to select a checkpoint, as the last known checkpoint for this backen d will be trans pa r e n tly used. After that the backen d will be in a recovery state for a few secon d s. It will actually replay all the reques t s that were misse d while the backen d was disabled.
•
Creating a backup of a backend
The two backen ds are online again. To backu p a backen d, use this comma n d : backup Example :
myDB(admin) > backup localhost2 backup Octopus /tmp/ Backend login for backup process > test Backend password for backup process > Backup backend localhost2 in dump file backup.zip
When creating a backup, you will be asked for a valid user of the backen d. If you starte d the raidb1 demo, the login is test with no passwor d. During backu p, request s can still be sent to the Sequoia controller because the backen d 'localhos t' is still active. From now on, you can use this dum p to add new backen d s or restore backe nd s that have failed and have come out of sync with the rest of the backen d s.
•
Adding a backend to the cluster.
The script to start HypersonicSQL process was: hsqldb.s h / . b a t We will start a new process of these on a new port using the following com ma n d: ./hsqldb.sh - port 9010 The comm a n d has started a new server process. The outp u t of the comma n d should be:
[Server@1a758cb]: [Server@1a758cb]: [Server@1a758cb]: ms. [Server@1a758cb]: [Server@1a758cb]: [Server@1a758cb]: [Server@1a758cb]: Initiating startup sequence... Server socket opened successfully in 13 ms. Database [index=0, id=0, db=file:test, alias=] opened sucessfully in 421 Startup sequence completed in 445 ms. 2005-08-19 11:41:46.064 HSQLDB server 1.8.0 is online To close normally, connect and execute SHUTDOWN SQL From command line, use [Ctrl]+[C] to abort abruptly
Now we want to add this backend to the cluster.
myDB(admin) > show backends localhost localhost2 myDB(admin) > expert on Expert mode on myDB(admin) > clone backend config localhost2 localhost3 jdbc:hsqldb:hsql://localhost:9010 Replicate localhost2 onto localhost3 with new url jdbc:hsqldb:hsql://localhost:9010 myDB(admin) > show backends localhost localhost2 localhost3
The newly create d backend is disabled.
myDB(admin) > show backend localhost3
19
+--------------------------+-----------------------------------+ | Backend Name | localhost3 | | Driver | org.hsqldb.jdbcDriver | | URL | jdbc:hsqldb:hsql://localhost:9010 | | Active transactions | 0 | | Pending Requests | 0 | | Read Enabled | false | | Write Enabled | false | | Is Initialized | false | | Static Schema | false | | Connection Managers | 1 | | Total Active Connections | 50 | | Total Requests | 0 | | Total Transactions | 0 | | Last known checkpoint | | +--------------------------+-----------------------------------+
We can now restore the previously create d dum p file on this new backen d :
myDB(admin) > restore backend localhost3 backup Backend login for restore process > test Backend password for restore process > Restoring backend localhost3 with dump backup
where backup is then name of the backup to use as found with the comm a n d 'show dump s':
myDB(admin) > show dumps +------------+-------------------------------------------------+----------------------...+ | Name | Checkpoint | Format ...| +------------+-------------------------------------------------+----------------------...+ | backup | cp_for_backup.zip_Fri Aug 19 11:25:30 CEST 2005 | Octopus v2.8 database...| +------------+-------------------------------------------------+----------------------...+
Once the restore process if finished, the backen d is still in disabled mode. We can now safely enable it, the recovery log will replay the missing queries since the du mp was made :
myDB(admin) > enable localhost3 Enabling backend localhost3 from its last known checkpoint show backend localhost3 +--------------------------+-----------------------------------+ | Backend Name | localhost3 | | Driver | org.hsqldb.jdbcDriver | | URL | jdbc:hsqldb:hsql://localhost:9010 | | Active transactions | 0 | | Pending Requests | 0 | | Read Enabled | true | | Write Enabled | true | | Is Initialized | true | | Static Schema | false | | Connection Managers | 1 | | Total Active Connections | 5 | | Total Requests | 1 | | Total Transactions | 0 | | Last known checkpoint | | +--------------------------+-----------------------------------+
We now have a cluster made of three backen d s. This step can be repeate d until the num be r of necessa ry backend s has been reache d.
•
Help
The administ ration console provides help on the syntax of comma n d s, by just typing help :
help Commands available for the Controller module are: admin Administrate a virtual database connect controller Connect to a p/cluster controller help Print this help message history [] Display history of commands for this module load virtualdatabase configuration Send a virtual database XML configuration file to the controller and load it
20
quit Quit this console reload logging configuration Refresh the trace system by reloading the logging configuration file save configuration Save the current configuration of the controller as an XML file show controller config Show Controller configuration show logging config Show logging configuration and the most recent traces show virtualdatabases Show the names of the virtual databases for this controller shutdown Shutdown the controller (all its virtual databases must be shut down before) shutdown virtualdatabase [mode] Shutdown a virtual database. Mode parameter must be: 1 -- wait for all client connections to be closed, does not work with a connection pool 2 -- mode safe (default value), waits for all current transactions to complete 3 -- mode force, immediate shutdown without consistency: recovery will be needed on restart sql client Open a SQL client console for the virtual database specified by the p/cluster URL upload driver Upload a driver to the controller
If you are administ ering a virtual database, the 'help' comm a n d will show :
>admin DB1 Virtual database Administrator Login > admin Virtual database Administrator Password > Ready to administrate virtual database DB1 DB1(admin) > help Commands available for the VirtualDatabase Administration module are: backup [] Backup a backend into a dump file and associate a checkpoint with this dump delete dump Delete a dump disable Disable the specified backend and automatically set a checkpoint * means that all backends of this virtual database must be disabled enable Enable the specified backend * means that all backends of this virtual database must be enabled expert Switch to expert mode (commands for advanced users are available) help Print this help message history [] Display history of commands for this module initialize Initialize the current virtual database taking the specified backend as reference backend purge log Purge the recovery log up to the specified dump quit Quit this console restore backend [] Starts the recovery process of the given backend for a given dump name show backend Show information on backend of this virtual database * means to show information for all the backends of this virtual database show backends Show the names of the backends of this virtual database on the current controller show backupers Show the backupers available for backup show controllers Show the names of the controllers hosting this virtual database show dumps Show all dumps available for database recovery show virtualdatabase config Show the XML configuration of the virtual database transfer dump [nocopy] Make a dump available for restore on another controller. Optional 'nocopy' (default: false) flag specifies not to copy the dump.
Some other comm a n d s can be found in expert mode. These comma n d s are describe d when using 'help' com ma n d in expert mode (i.e. after executing the 'expert on' comm a n d in VirtualDataba s e Administra tion mod ule )
clone backend config [driverPath=] [driver=] [connectionTestStatement=] Clone the configuration of a backend in the current virtual database This virtually allows to add a new backend. disable read Disable read requests on a backend enable read Enable read requests on a backend force checkpoint Force the last know checkpoint of a disabled backend force disable Force the disabling of a backend without storing any checkpoints Warning! The backend will not be in a consistent state after this operation! * means that all backends of this virtual database must be disabled by force force enable Force the enabling of a backend without checking for checkpoints Warning! This command can break the cluster consistency, only use it if you know what you are doing! * means that all backends of this virtual database must be enabled by force
21
force path Update the path of the dump get backend schema Display backend schema or save it to a file restore log Restore a remote recovery log from the local recovery log show checkpoints Show all checkpoints available in the recovery log transfer backend Transfer a backend from a controller to an other controller truncate log Truncate the recovery log upto specified checkpoint
22
9. Further readings
All the new docume n t a tion can be foun d online on the contin ue n t.org website in the docum e n t a tion section at: http: / / s e q u oi a.contin ue n t.org /Ma nu al s
A general presenta tion of C- JDBC : http: / / c - jdbc.objectweb.org / c u r r e n t / d o c / C - JDBC_Solution s_Linux_2004.p p t
If you have any question concerning the use of Sequoia or feature s you would like to see include d, please write an email to:
mailto:sequoia@continue n t.org
Thanks for reading !
23