wiki:Software/Productionalisation/RealDb

Version 23 (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.

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 database named 'mua'. e.g. in Postgres, 'create database mua'
    • Use the database schema to create the neccessary tables etc. For Postgres this is defined here, which you can download.
  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.

Attachments