wiki:Software/Productionalisation/RealDb

Version 22 (modified by smartp@…, 6 years ago) (diff)

--

Using to 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/)

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.

Each of these steps are detailed below using Postgres as an example.

Installing and Configuring a database

  1. Choose a database and install it using the relevant instructions:
    1. For example, postgres on linux using YUM - yum install postgres, or using the Windows installer. For Postgres guides see http://wiki.postgresql.org/wiki/Detailed_installation_guides.
  1. Set up the Raptor schema in the database.
    1. Create a database named 'mua'. e.g. in Postgres, 'create database mua'
    2. Use the database schema to create the neccessary tables etc. For Postgres this is defined here, which you can download.
  1. Set up users.
    1. 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).
  1. 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.

Configuring the Raptor MUA to use the database

  1. Edit the file mua/conf/database.xml.
  1. Change
    <ref local="muac3p0DataSourceHSQLDB" />
    

to

 <ref local="muac3p0DataSource" />
  1. Swap the comments around on the following code fragments. From
    <!-- <prop key="hibernate.dialect"> org.hibernate.dialect.PostgreSQLDialect</prop>-->
    <prop key="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</prop>
    
    to
    <prop key="hibernate.dialect"> org.hibernate.dialect.PostgreSQLDialect</prop>
    <!-- <prop key="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</prop>-->
    
  1. Uncomment the whole postgres datasource bean (which is a C3P0 http://www.mchange.com/projects/c3p0/index.html pooled data source).
    <bean id="muac3p0DataSource"
    ...
    </bean>
    
  2. Set the username and password as appropriate for your postgres database instance, for example:
    <prop key="user">postgres</prop>
    <prop key="password"></prop>
    

Optimising Raptor for "real" databases

If you choose to use a "real" database for your event store implementation, then you may experience a general slowdown of the MUA when it performs SQL Count operations on multiple tables - which Raptor does by default to find out how many events it has from each authentication system table in the database. This can be improved however by modifying the mua-core.xml file, finding the following bean definition:

<bean name="persistantEventHandler" class="uk.ac.cardiff.raptor.store.impl.PersistantEventHandler">
		<constructor-arg index="0"><ref bean="dataConnectionImpl"></ref></constructor-arg>
        <property name="optimiseCountQueries"><value>true</value></property>
</bean>

And changing it to look as follows:

<bean name="persistantEventHandler" class="uk.ac.cardiff.raptor.store.impl.PersistantEventHandler">
		<constructor-arg index="0"><ref bean="dataConnectionImpl"></ref></constructor-arg>
        <property name="optimiseCountQueries"><value>true</value></property>
        <property name="countClassNames">
            <list>
                <value>ShibbolethIdpAuthenticationEvent</value>
                <value>EzproxyAuthenticationEvent</value>
            </list>
        </property>
</bean>

When determining the number of events the mua has, it will then perform a 'select count(*) from <TableName>' query on each of the listed countClassNames, as opposed to performing a single count query which unions all relevant tables together (which Hibernate does automatically). Of note, if the class names change, this list of class names would need maintained.

Important Note: Any upgrades to the MUA will overwrite this file, and these changes will need to be included again manually.

Attachments