<html><head><META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Chapter 5. Deployment Issues</title><link href="guide.css" rel="stylesheet" type="text/css"><meta content="DocBook XSL Stylesheets V1.65.1" name="generator"><meta name="keywords" content="Hsqldb, Guide"><meta name="keywords" content="Hsqldb, Hypersonic, Database, JDBC, Java"><link rel="home" href="index.html" title="Hsqldb User Guide"><link rel="up" href="index.html" title="Hsqldb User Guide"><link rel="previous" href="ch04.html" title="Chapter 4. Advanced Topics"><link rel="next" href="ch06.html" title="Chapter 6. Text Tables"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table summary="Navigation header" width="100%"><tr><th align="center" colspan="3">Chapter 5. Deployment Issues</th></tr><tr><td align="left" width="20%"><a accesskey="p" href="ch04.html"><img src="navicons/prev.gif" alt="Prev"></a> </td><th align="center" width="60%"> </th><td align="right" width="20%"> <a accesskey="n" href="ch06.html"><img src="navicons/next.gif" alt="Next"></a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="N10DAC"></a>Chapter 5. Deployment Issues</h2></div><div><div class="authorgroup"><div class="author"><h3 class="author"><span class="firstname">Fred</span> <span class="surname">Toussi</span></h3><div class="affiliation"><span class="orgname">HSQLDB Development Group<br></span></div><tt class="email"><<a href="mailto:ft@cluedup.com">ft@cluedup.com</a>></tt></div></div></div><div><div class="legalnotice"><p>Copyright 2005 Fred Toussi. Permission is granted to distribute
this document without any alteration under the terms of the HSQLDB
license. Additional permission is granted to the HSQLDB Development
Group to distribute this document with or without alterations under the
terms of the HSQLDB license.</p></div></div><div><p class="pubdate">$Date: 2005/06/30 22:39:11 $</p></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="ch05.html#N10DCD">Purpose</a></span></dt><dt><span class="section"><a href="ch05.html#N10DD6">Mode of Operation and Tables</a></span></dt><dd><dl><dt><span class="section"><a href="ch05.html#N10DDB">Mode of Operation</a></span></dt><dt><span class="section"><a href="ch05.html#N10DED">Tables</a></span></dt><dt><span class="section"><a href="ch05.html#N10E04">Large Objects</a></span></dt><dt><span class="section"><a href="ch05.html#N10E15">Deployment context</a></span></dt></dl></dd><dt><span class="section"><a href="ch05.html#N10E21">Memory and Disk Use</a></span></dt><dd><dl><dt><span class="section"><a href="ch05.html#N10E3E">Cache Memory Allocation</a></span></dt></dl></dd><dt><span class="section"><a href="ch05.html#N10E5F">Managing Database Connections</a></span></dt><dt><span class="section"><a href="ch05.html#N10E76">Upgrading Databases</a></span></dt><dd><dl><dt><span class="section"><a href="ch05.html#upgrade_via_script-section">Upgrading Using the SCRIPT
Command</a></span></dt><dt><span class="section"><a href="ch05.html#N10ECE">Manual Changes to the .script File</a></span></dt></dl></dd><dt><span class="section"><a href="ch05.html#N10F02">Backing Up Databases</a></span></dt></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10DCD"></a>Purpose</h2></div></div><div></div></div><p>Many questions repeatedly asked in Forums and mailing lists are
answered in this guide. If you want to use HSQLDB with your application,
you should read this guide. This document covers system related issues.
For issues related to SQL see the <a href="ch02.html" title="Chapter 2. SQL Issues">SQL Issues</a> chapter.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10DD6"></a>Mode of Operation and Tables</h2></div></div><div></div></div><p>HSQLDB has many modes of operation and features that allow it to be
used in very different scenarios. Levels of memory usage, speed and
accessibility by different applications are influenced by how HSQLDB is
deployed.</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10DDB"></a>Mode of Operation</h3></div></div><div></div></div><p>The decision to run HSQLDB as a separate server process or as an
in-process database should be based on the following:</p><p>
<div class="itemizedlist"><ul type="disc"><li><p>When HSQLDB is run as a server on a separate machine, it is
isolated from hardware failures and crashes on the hosts running
the application.</p></li><li><p>When HSQLDB is run as a server on the same machine, it is
isolated from application crashes and memory leaks.</p></li><li><p>Server connections are slower than in-process connections
due to the overhead of streaming the data for each JDBC
call.</p></li></ul></div>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10DED"></a>Tables</h3></div></div><div></div></div><p>TEXT tables are designed for special applications where the data
has to be in an interchangeable format, such as CSV. TEXT tables should
not be used for routine storage of data.</p><p>MEMORY tables and CACHED tables are generally used for data
storage. The difference between the two is as follows:</p><p>
<div class="itemizedlist"><ul type="disc"><li><p>The data for all MEMORY tables is read from the .script file
when the database is started and stored in memory. In contrast the
data for cached tables is not read into memory until the table is
accessed. Furthermore, only part of the data for each CACHED table
is held in memory, allowing tables with more data than can be held
in memory.</p></li><li><p>When the database is shutdown in the normal way, all the
data for MEMORY tables is written out to the disk. In comparison,
the data in CACHED tables that has changed is written out at
shutdown, plus a compressed backup of all the data in all cached
tables.</p></li><li><p>The size and capacity of the data cache for all the CACHED
tables is configurable. This makes it possible to allow all the
data in CACHED tables to be cached in memory. In this case, speed
of access is good, but slightly slower than MEMORY tables.</p></li><li><p>For normal applications it is recommended that MEMORY tables
are used for small amounts of data, leaving CACHED tables for
large data sets. For special applications in which speed is
paramount and a large amount of free memory is available, MEMORY
tables can be used for large tables as well</p></li></ul></div>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10E04"></a>Large Objects</h3></div></div><div></div></div><p>JDBC Clobs are supported as columns of the type LONGVARCHAR. JDBC
Blobs are supported as columns of the type LONGVARBINARY. When large
objects (LONGVARCHAR, LONGVARBINARY, OBJECT) are stored with table
definitions that contain several normal fields, it is better to use two
tables instead. The first table to contain the normal fields and the
second table to contain the large object plus an identity field. Using
this method has two benefits. (a) The first table can usually be created
as a MEMORY table while only the second table is a CACHED table. (b) The
large objects can be retrieved individually using their identity,
instead of getting loaded into memory for finding the rows during query
processing. An example of two tables and a select query that exploits
the separation between the two follows:</p><div class="informalexample"><pre class="programlisting">CREATE MEMORY TABLE MAINTABLE(MAINID INTEGER, ......);</pre><pre class="programlisting">CREATE CACHED TABLE LOBTABLE(LOBID INTEGER, LOBDATA LONGVARBINARY);</pre><pre class="programlisting">SELECT * FROM (SELECT * FROM MAINTABLE <join any other table> WHERE <various conditions apply>) JOIN LOBTABLE ON MAINID=LOBID;</pre></div><p>The inner SELECT finds the required rows without reference to the
LOBTABLE and when it has found all the rows, retrieves the required
large objects from the LOBTABLE.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10E15"></a>Deployment context</h3></div></div><div></div></div><p>The files used for storing HSQLDB database data are all in the
same directory. New files are always created and deleted by the database
engine. Two simple principles must be observed:</p><div class="itemizedlist"><ul type="disc"><li><p>The Java process running HSQLDB must have full privileges on
the directory where the files are stored. This include create and
delete privileges.</p></li><li><p>The file system must have enough spare room both for the
'permanent' and 'temporary' files. The default maximum size of the
.log file is 200MB. The .data file can grow to up to 8GB. The
.backup file can be up to 50% of the .data file. The temporary file
created at the time of a SHUTDOWN COMPACT can be equal in size to
the .data file.</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10E21"></a>Memory and Disk Use</h2></div></div><div></div></div><p>Memory used by the program can be thought of as two distinct pools:
memory used for table data, and memory used for building result sets and
other internal operations. In addition, when transactions are used, memory
is utilised for storing the information needed for a rollback.</p><p>Since version 1.7.1, memory use has been significantly reduced
compared to previous versions. The memory used for a MEMORY table is the
sum of memory used by each row. Each MEMORY table row is a Java object
that has 2 int or reference variables. It contains an array of objects for
the fields in the row. Each field is an object such as
<tt class="classname">Integer</tt>, <tt class="classname">Long</tt>,
<tt class="classname">String</tt>, etc. In addition each index on the table
adds a node object to the row. Each node object has 6 int or reference
variables. As a result, a table with just one column of type INTEGER will
have four objects per row, with a total of 10 variables of 4 bytes each -
currently taking up 80 bytes per row. Beyond this, each extra column in
the table adds at least a few bytes to the size of each row.</p><p>The memory used for a result set row has fewer overheads (fewer
variables and no index nodes) but still uses a lot of memory. All the rows
in the result set are built in memory, so very large result sets may not
be possible. In server mode databases, the result set memory is released
from the server once the database server has returned the result set.
In-process databases release the memory when the application program
releases the <tt class="classname">java.sql.ResultSet</tt> object. Server
modes require additional memory for returning result sets, as they convert
the full result set into an array of bytes which is then transmitted to
the client.</p><p>When UPDATE and DELETE queries are performed on CACHED tables, the
full set of rows that are affected, including those affected due to ON
UPDATE actions, is held in memory for the duration of the operation. This
means it may not be possible to perform deletes or updates involving very
large numbers of rows of CACHED tables. Such operations should be
performed in smaller sets.</p><p>When transactions support is enabled with SET AUTOCOMMIT OFF, lists
of all insert, delete or update operations are stored in memory so that
they can be undone when ROLLBACK is issued. Transactions that span
hundreds of modification to data will take up a lot of memory until the
next COMMIT or ROLLBACK clears the list.</p><p>Most JVM implementations allocate up to a maximum amount of memory
(usually 64 MB by default). This amount is generally not adequate when
large memory tables are used, or when the average size of rows in cached
tables is larger than a few hundred bytes. The maximum amount of allocated
memory can be set on the java ... command line that is used for running
HSQLDB. For example, with Sun JVM version 1.3.0 the parameter -Xmx256m
increases the amount to 256 MB.</p><p>1.8.0 uses a fast cache for immutable objects such as Integer or
String that are stored in the database. In most circumstances, this
reduces the memory footprint still further as fewer copies of the most
frequently-used objects are kept in memory.</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10E3E"></a>Cache Memory Allocation</h3></div></div><div></div></div><p>With CACHED tables, the data is stored on disk and only up to a
maximum number of rows are held in memory at any time. The default is up
to 3*16384 rows. The <span class="property">hsqldb.cache_scale</span> database
property can be set to alter this amount. As any random subset of the
rows in any of the CACHED tables can be held in the cache, the amount of
memory needed by cached rows can reach the sum of the rows containing
the largest field data. For example if a table with 100,000 rows
contains 40,000 rows with 1,000 bytes of data in each row and 60,000
rows with 100 bytes in each, the cache can grow to contain nearly 50,000
rows, including all the 40,000 larger rows.</p><p>An additional property,
<span class="property">hsqldb.cache_size_scale</span> can be used in conjunction
with the <span class="property">hsqldb.cache_scale</span> property. This puts a
limit in bytes on the total size of rows that are cached. When the
default values is used for both properties, the limit on the total size
of rows is approximately 50MB. (This is the size of binary images of the
rows and indexes. It translates to more actual memory, typically 2-4
times, used for the cache because the data is represented by Java
objects.)</p><p>If memory is limited, the <span class="property">hsqldb.cache_scale</span>
or <span class="property">hsqldb.cache_size_scale</span> database properties can
be reduced. In the example above, if the
<span class="property">hsqldb.cache_size_scale</span> is reduced from 10 to 8,
then the total binary size limit is reduced from 50MB to 12.5 MB. This
will allow the number of cached rows to reach 50,000 small rows, but
only 12,500 of the larger rows.</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10E5F"></a>Managing Database Connections</h2></div></div><div></div></div><p>In all running modes (server or in-process) multiple connections to
the database engine are supported. In-process (standalone) mode supports
connections from the client in the same Java Virtual Machine, while server
modes support connections over the network from several different
clients.</p><p>Connection pooling software can be used to connect to the database
but it is not generally necessary. With other database engines, connection
pools are used for reasons that may not apply to HSQLDB.</p><div class="itemizedlist"><ul type="disc"><li><p>To allow new queries to be performed while a time-consuming
query is being performed in the background. This is not possible with
HSQLDB 1.8.0 as it blocks while performing the first query and deals
with the next query once it has finished it. This capability is under
development and will be introduced in a future version.</p></li><li><p>To limit the maximum number of simultaneous connections to the
database for performance reasons. With HSQLDB this can be useful only
if your application is designed in a way that opens and closes
connections for each small task.</p></li><li><p>To control transactions in a multi-threaded application. This
can be useful with HSQLDB as well. For example, in a web application,
a transaction may involve some processing between the queries or user
action across web pages. A separate connection should be used for each
HTTP session so that the work can be committed when completed or
rolled back otherwise. Although this usage cannot be applied to most
other database engines, HSQLDB is perfectly capable of handling over
100 simultaneous HTTP sessions as individual JDBC connections.</p></li></ul></div><p>An application that is not both multi-threaded and transactional,
such as an application for recording user login and logout actions, does
not need more than one connection. The connection can stay open
indefinitely and reopened only when it is dropped due to network
problems.</p><p>When using an in-process database with versions prior to 1.7.2 the
application program had to keep at least one connection to the database
open, otherwise the database would have been closed and further attempts
to create connections could fail. This is not necessary since 1.7.2, which
does not automatically close an in-process database that is opened by
establishing a connection. An explicit SHUTDOWN command, with or without
an argument, is required to close the database. In version 1.8.0 a
connection property can be used to revert to the old behaviour.</p><p>When using a server database (and to some extent, an in-process
database), care must be taken to avoid creating and dropping JDBC
Connections too frequently. Failure to observe this will result in
unsuccessful connection attempts when the application is under heavy
load.</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10E76"></a>Upgrading Databases</h2></div></div><div></div></div><p>Any database not produced with the release version of HSQLDB 1.8.0
must be upgraded to this version. This includes databases created with the
RC versions of 1.8.0. The instructions under the <a href="ch05.html#upgrade_via_script-section" title="Upgrading Using the SCRIPT
Command">Upgrading Using the SCRIPT
Command</a>
section should be followed in all cases.</p><p>Once a database is upgraded to 1.8.0, it can no longer be used with
Hypersonic or previous versions of HSQLDB.</p><p>There may be some potential legacy issues in the upgrade which
should be resolved by editing the .script file:</p><div class="itemizedlist"><ul type="disc"><li><p>Version 1.8.0 does not accept duplicate names for indexes that
were allowed before 1.7.2.</p></li><li><p>Version 1.8.0 does not accept duplicate names for table columns
that were allowed before 1.7.0.</p></li><li><p>Version 1.8.0 does not create the same type of index for foreign
keys as versions before 1.7.2.</p></li><li><p>Version 1.8.0 does not accept table or column names that are SQL
identifiers without double quoting.</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="upgrade_via_script-section"></a>Upgrading Using the SCRIPT
Command</h3></div></div><div></div></div><p>To upgrade from 1.7.2 or 1.7.3 to 1.8.0, simply issue the SET
SCRIPTFORMAT TEXT and SHUTDOWN SCRIPT commands with the old version,
then open with the new version of the engine. The upgrade is then
complete.</p><p>To upgrade from older version database files (1.7.1 and older)
that do not contain CACHED tables, simple SHUTDOWN with the older
version and open with the new version. If there is any error in the
.script file, try again after editing the .script file.</p><p>To upgrade from older version database files (1.7.1 and older)
that contain CACHED tables, use the SCRIPT procedure below. In all
versions of HSQLDB and Hypersonic 1.43, the <tt class="literal">SCRIPT
'filename'</tt> command (used as an SQL query) allows you to save a
full record of your database, including database object definitions and
data, to a file of your choice. You can export a script file using the
old version of the database engine and open the script as a database
with 1.8.0.</p><div class="procedure"><p class="title"><b>Procedure 5.1. Upgrade Using SCRIPT procedure</b></p><ol type="1"><li><p>Open the original database in the old version of
DatabaseManager</p></li><li><p>Issue the SCRIPT command, for example <tt class="literal">SCRIPT
'newversion.script'</tt> to create a script file containing a
copy of the database.</p></li><li><p>Use the 1.8.0 version of DatabaseManager to create a new
database, in this example <tt class="literal">'newversion'</tt> in a
different directory.</p></li><li><p>SHUTDOWN this database.</p></li><li><p>Copy the <tt class="filename">newversion.script</tt> file from step
2 over the file of the same name for the new database created in
4.</p></li><li><p>Try to open the new database using DatabaseManager.</p></li><li><p>If there is any inconsistency in the data, the script line
number is reported on the console and the opening process is
aborted. Edit and correct any problems in the
<tt class="filename">newversion.script</tt> before attempting to open
again. Use the guidelines in the next section (Manual Changes to the
.script File). Use a programming editor that is capable of handling
very large files and does not wrap long lines of text.</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="N10ECE"></a>Manual Changes to the .script File</h3></div></div><div></div></div><p>In 1.8.0 the full range of ALTER TABLE commands is available to
change the data structures and their names. However, if an old database
cannot be opened due to data inconsistencies, or the use of index or
column names that are not compatible with 1.8.0, manual editing of the
SCRIPT file can be performed.</p><p>The following changes can be applied so long as they do not affect
the integrity of existing data.</p><div class="itemizedlist"><ul type="disc"><li><p>Names of tables, columns and indexes can be changed.</p></li><li><p><tt class="literal">CREATE UNIQUE INDEX ...</tt> to <tt class="literal">CREATE
INDEX ...</tt> and vice versa</p><p>A unique index can always be converted into a normal index. A
non-unique index can only be converted into a unique index if the
table data for the column(s) is unique in each row.</p></li><li><p>
<tt class="literal">NOT NULL</tt>
</p><p>A not-null constraint can always be removed. It can only be
added if the table data for the column has no null values.</p></li><li><p>
<tt class="literal">PRIMARY KEY</tt>
</p><p>A primary key constraint can be removed or added. It cannot be
removed if there is a foreign key referencing the column(s).</p></li><li><p>
<tt class="literal">COLUMN TYPES</tt>
</p><p>Some changes to column types are possible. For example an
INTEGER column can be changed to BIGINT, or DATE, TIME and TIMESTAMP
columns can be changed to VARCHAR.</p></li></ul></div><p>After completing the changes and saving the modified *.script
file, you can open the database as normal.</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="N10F02"></a>Backing Up Databases</h2></div></div><div></div></div><p>The data for each database consists of up to 5 files in the same
directory. The endings are *.properties, *.script, *.data, *.backup and
*.log (a file with the *.lck ending is used for controlling access to the
database and should not be backed up). These should be backed up together.
The files can be backed up while the engine is running but care should be
taken that a CHECKPOINT or SHUTDOWN operation does not take place during
the backup. It is more efficient to perform the backup immediately after a
CHECKPOINT. The *.data file can be excluded from the backup. In this case,
when restoring, a dummy *.data file is needed which can be an empty, 0
length file. The engine will expand the *.backup file to replace this
dummy file if the backup is restored. If the *.data file is not backed up,
the *.properties file may have to be modified to ensure it contain
modified=yes instead of modified=no prior to restoration. If a backup
immediately follows a checkpoint, then the *.log file can also be
excluded, reducing the significant files to *.properties, *.script and
*.backup. Normal backup methods, such as archiving the files in a
compressed bundle can be used.</p></div></div><div class="navfooter"><hr><table summary="Navigation footer" width="100%"><tr><td align="left" width="40%"><a accesskey="p" href="ch04.html"><img src="navicons/prev.gif" alt="Prev"></a> </td><td align="center" width="20%"><a accesskey="u" href="index.html"><img src="navicons/up.gif" alt="Up"></a></td><td align="right" width="40%"> <a accesskey="n" href="ch06.html"><img src="navicons/next.gif" alt="Next"></a></td></tr><tr><td valign="top" align="left" width="40%">Chapter 4. Advanced Topics </td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="navicons/home.gif" alt="Home"></a></td><td valign="top" align="right" width="40%"> Chapter 6. Text Tables</td></tr></table></div></body></html>
distrib
>
Mageia
>
1
>
i586
>
media
>
core-updates
>
by-pkgid
>
58de6be3705c875194e822c24ebf1a0a
>
files
>
21
