Changes between Version 22 and Version 23 of Software/Productionalisation/RealDb


Ignore:
Timestamp:
03/18/14 20:02:34 (6 years ago)
Author:
smith@…
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • Software/Productionalisation/RealDb

    v22 v23  
    1 = Using to a real database = 
     1= Using a "real" database = 
    22 
    3 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/) 
     3The 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/). 
     4 
     5Some rough rules of thumb that users have discovered are as follows: 
     6* HSQL works fine up to about 100,000 events. 
     7* A real database on a VM works fine up to about 1,000,000 events. 
     8* Beyond this, you need to think about a real database on dedicated hardware. 
     9 
     10Raptor currently supports the following databases: 
     11* MySQL 
     12* Oracle 
     13* Postgres 
     14 
     15= How to switch to a real database = 
    416 
    517Switching to a real database is relatively easy within the MUA. The steps you need to follow are: 
     
    8202. Configure the Raptor MUA to use the database. 
    921 
    10 Each of these steps are detailed below using Postgres as an example. 
    11  
    12 == Installing and Configuring a database == 
     22== 1. Installing and Configuring a database == 
    1323 
    14241. Choose a database and install it using the relevant instructions: 
    15  a. 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. 
     25 * 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. 
     261. Set up the Raptor schema in the database. 
     27 * Create a database named 'mua'. e.g. in Postgres, '{{{create database mua}}}' 
     28 * Use the database schema to create the neccessary tables etc. For Postgres this is defined [Software/Productionalisation/MuaSchema here], which you can [[https://iam.cf.ac.uk/trac/RAPTOR/attachment/wiki/Software/Productionalisation/RealDb/mua-schema.3.sql|download]]. 
     291. Set up users. 
     30 * 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).  
     311. 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. 
    1632 
    17 2. Set up the Raptor schema in the database. 
    18  a. Create a database named 'mua'. e.g. in Postgres, '{{{create database mua}}}' 
    19  b. Use the database schema to create the neccessary tables etc. For Postgres this is defined [Software/Productionalisation/MuaSchema here], which you can [[https://iam.cf.ac.uk/trac/RAPTOR/attachment/wiki/Software/Productionalisation/RealDb/mua-schema.3.sql|download]]. 
    20  
    21 3. Set up users. 
    22  a. 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).  
    23  
    24 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. 
    25  
    26 == Configuring the Raptor MUA to use the database == 
     33== 2. Configuring the Raptor MUA to use the database == 
    2734 
    28351. Edit the file {{{mua/conf/database.xml}}}. 
     
    30372. Change 
    3138{{{ 
    32 <ref local="muac3p0DataSourceHSQLDB" /> 
     39<ref bean="muac3p0DataSourceHSQLDB" /> 
    3340}}} 
    34  
    35  to 
     41 to the value relevant to the database you're using: 
     42 * For MySQL 
    3643{{{ 
    37  <ref local="muac3p0DataSource" /> 
     44 <ref bean="muac3p0DataSourceMySQL" /> 
    3845}}} 
    39  
    40 3. Swap the comments around on the following code fragments. From 
     46 * For Oracle 
    4147{{{ 
     48 <ref bean="muac3p0DataSourceOracle" /> 
     49}}} 
     50 * For Postgres 
     51{{{ 
     52 <ref bean="muac3p0DataSourcePostgres" /> 
     53}}} 
     541. Find the following code fragment: 
     55{{{ 
     56<!-- All of these hibernate.dialect properties should be commented out apart from the one you want to use --> 
     57<prop key="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</prop> 
     58<!-- <prop key="hibernate.dialect"> org.hibernate.dialect.MySQLDialect</prop>--> 
     59<!-- <prop key="hibernate.dialect"> org.hibernate.dialect.OracleDialect</prop> --> 
    4260<!-- <prop key="hibernate.dialect"> org.hibernate.dialect.PostgreSQLDialect</prop>--> 
    43 <prop key="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</prop> 
    4461}}} 
    45  to 
     62 and swap the comments around such the the correct entry is uncommented. E.g. for Oracle 
    4663{{{ 
    47 <prop key="hibernate.dialect"> org.hibernate.dialect.PostgreSQLDialect</prop> 
    48 <!-- <prop key="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</prop>--> 
     64<!-- All of these hibernate.dialect properties should be commented out apart from the one you want to use --> 
     65<!--<prop key="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</prop>--> 
     66<!-- <prop key="hibernate.dialect"> org.hibernate.dialect.MySQLDialect</prop>--> 
     67<prop key="hibernate.dialect"> org.hibernate.dialect.OracleDialect</prop> 
     68<!-- <prop key="hibernate.dialect"> org.hibernate.dialect.PostgreSQLDialect</prop>--> 
    4969}}} 
    50  
    51 4. Uncomment the whole postgres datasource bean (which is a C3P0 http://www.mchange.com/projects/c3p0/index.html pooled data source). 
    52 {{{ 
    53 <bean id="muac3p0DataSource" 
    54 ... 
    55 </bean> 
    56 }}} 
    57 5. Set the username and password as appropriate for your postgres database instance, for example: 
    58 {{{ 
    59 <prop key="user">postgres</prop> 
    60 <prop key="password"></prop> 
    61 }}} 
    62  
    63 == Optimising Raptor for "real" databases == 
    64  
    65 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: 
    66  
    67 {{{ 
    68 <bean name="persistantEventHandler" class="uk.ac.cardiff.raptor.store.impl.PersistantEventHandler"> 
    69                 <constructor-arg index="0"><ref bean="dataConnectionImpl"></ref></constructor-arg> 
    70         <property name="optimiseCountQueries"><value>true</value></property> 
    71 </bean> 
    72 }}} 
    73  
    74  
    75 And changing it to look as follows: 
    76  
    77 {{{ 
    78 <bean name="persistantEventHandler" class="uk.ac.cardiff.raptor.store.impl.PersistantEventHandler"> 
    79                 <constructor-arg index="0"><ref bean="dataConnectionImpl"></ref></constructor-arg> 
    80         <property name="optimiseCountQueries"><value>true</value></property> 
    81         <property name="countClassNames"> 
    82             <list> 
    83                 <value>ShibbolethIdpAuthenticationEvent</value> 
    84                 <value>EzproxyAuthenticationEvent</value> 
    85             </list> 
    86         </property> 
    87 </bean> 
    88  
    89 }}} 
    90  
    91 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. 
    92  
    93 '''Important Note:''' Any upgrades to the MUA will overwrite this file, and these changes will need to be included again manually. 
     701. In the bean relevant for your database, change the connection parameters to match those of your database (i.e. jdbcUrl, user, and password.