wiki:Software/Productionalisation/RealDb

Version 25 (modified by smith@…, 5 years ago) (diff)

--

Using a "real" database

The default install of the Raptor MUA ships with a built-in lightweight database namely, HyperSQL (http://hsqldb.org/). Raptor uses HyperSQL with persistent disk cached tables as opposed to the faster persistent memory tables - which limits the size of tables to the size of working memory. As a result, this configuration should work well for test environments or production environments with small amounts of data, but for real production environments with larger data sets (number of stored events), it is recommended to switch to a heavy weight database e.g. Postgres (http://www.postgresql.org/).

Some rough rules of thumb that users have discovered are as follows:

  • HSQL works fine up to about 100,000 events.
  • A real database on a VM works fine up to about 1,000,000 events.
  • Beyond this, you need to think about a real database on dedicated hardware.

Raptor currently supports the following databases:

  • MySQL
  • Oracle
  • Postgres

How to switch to a real database

Switching to a real database is relatively easy within the MUA. The steps you need to follow are:

  1. Install and configure a production ready database (either on the same server or a different server).
  2. Configure the Raptor MUA to use the database.
  3. Setting up the database schema

1. Installing and Configuring a database

  1. Choose a database and install it using the relevant instructions:
  2. Set up the Raptor schema in the database.
    • Create a blank database named 'mua'. e.g. in Postgres, 'create database mua'
  3. Set up users.
    • Add a user "raptor" with a password of your choosing with admins rights (Alternatively you could choose to use the default Postgres user for ease of setup).
  4. Make sure the user running the raptor software (root unless you've changed it) can access the database. e.g. on postgres change /var/lib/pgsql/data/pg_hba.conf to allow connections from anyone on localhost.

2. Configuring the Raptor MUA to use the database

  1. Edit the file mua/conf/database.xml.
  1. Change
    <ref bean="muac3p0DataSourceHSQLDB" />
    
    to the value relevant to the database you're using:
    • For MySQL
       <ref bean="muac3p0DataSourceMySQL" />
      
    • For Oracle
       <ref bean="muac3p0DataSourceOracle" />
      
    • For Postgres
       <ref bean="muac3p0DataSourcePostgres" />
      
  2. Find the following code fragment:
    <!-- All of these hibernate.dialect properties should be commented out apart from the one you want to use -->
    <prop key="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</prop>
    <!-- <prop key="hibernate.dialect"> org.hibernate.dialect.MySQLDialect</prop>-->
    <!-- <prop key="hibernate.dialect"> org.hibernate.dialect.OracleDialect</prop> -->
    <!-- <prop key="hibernate.dialect"> org.hibernate.dialect.PostgreSQLDialect</prop>-->
    
    and swap the comments around such the the correct entry is uncommented. E.g. for Oracle
    <!-- All of these hibernate.dialect properties should be commented out apart from the one you want to use -->
    <!--<prop key="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</prop>-->
    <!-- <prop key="hibernate.dialect"> org.hibernate.dialect.MySQLDialect</prop>-->
    <prop key="hibernate.dialect"> org.hibernate.dialect.OracleDialect</prop>
    <!-- <prop key="hibernate.dialect"> org.hibernate.dialect.PostgreSQLDialect</prop>-->
    
  3. In the bean relevant for your database, change the connection parameters to match those of your database (i.e. jdbcUrl, user, and password).

3. Setting up the database schema

The most reliable way to set up the database schema is to get hibernate to do it for you. To do this, perform the following steps:

  1. Edit the file mua/conf/database.xml.
  2. Find the bean with id of "muaSessionFactor" (should be one of the very first in the file)
  3. In this, find the property with name "hibernate properties".
  4. In this, find the prop with the key of "hibernate.hbm2ddl.auto".
  5. Change the value from "update" to "create".
  6. Restart the MUA. Hibernate will now create the schema relevant to the dialect you choose in section 2 of this guide.
  7. IMPORTANT: Once the MUA has started, change the value from "create" back to "update" - if you do not do this, next time the MUA is started hibernate will wipe out your database and recreate it from scratch, losing all data in the process!

Attachments