5. Database Configuration
Prev |  | Next

## 5. Database Configuration

### 5.1. Migrating from an Earlier Version

A database conversion is typically required between Sakai versions. Database conversion scripts - in distinct versions for MySQL and Oracle, respectively - are found in the `reference/docs/conversion` folder of the release or on subversion:

MySQL: | https://source.sakaiproject.org/svn/reference/tags/sakai_2-3-1/docs/conversion/sakai_2_2_1-2_3_1_mysql_conversion.sql
Oracle: | https://source.sakaiproject.org/svn/reference/tags/sakai_2-3-1/docs/conversion/sakai_2_2_1-2_3_1_oracle_conversion.sql

In the same directory you'll also find conversion scripts for earlier Sakai versions. Migration from an earlier version will require the successive application of all intermediate scripts. You cannot, for example, move from 2.2.0 to 2.3.1 by applying a single script. You will need to move first from 2.2.0 to 2.2.1, and then from 2.2.1 to 2.3.1 (there was no database conversion between 2.2.1 and 2.2.2, and while there is a conversion script from 2.3.0 to 2.3.1 for those who may have already migrated to 2.3.0, the 2.2.1-2.3.0 conversion had a significant gradebook error, and is no longer made available).

### Examine before using

As a general rule, be sure to read through these conversion scripts before applying them. They do not take into account any special customizations you may have made - such as new roles, or the deployment of additional tools - and they may complicate your migration with unintended consequences if you execute them blindly.

### 5.2. Deploy Drivers

The supported production-grade databases include MySQL 4.1.12+ (but MySQL 5.0 is not yet recommended for production - see the Configuration for Performance section below) and Oracle 9i+. The version of the JDBC driver (or connector) is also important: for MySQL a 3.1.12+ connector should be used, while for Oracle the 10g driver must be used, even if the database is Oracle 9i. These drivers should be copied into your `$CATALINA_HOME/common/lib` directory, and they are available from the official sites:

MySQL: | http://dev.mysql.com/downloads/connector/j/3.1.html
Oracle: | http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

### Driver Versions

Database driver versions are a common source of problems. It's worth emphasizing again that the Oracle 10g driver must be used for Sakai installations running against Oracle, even when the database is Oracle 9i.

Problems have been reported for both the 3.1.10 and 3.1.11 MySQL drivers. 3.1.12+ will be adequate, but 3.1.14 is the latest recommended version.

### 5.3. Create Database and User

A Sakai database and privileged user must be prepared for Sakai's use. Consult your database documentation for details, but below are sample commands for MySQL.

    C:\sakai\reference\sql\legacy\mysql\>
    mysql -u root -p
    Enter password: ******
    Welcome to the MySQL monitor. Commands end with ; or \g.

    Your MySQL connection id is 51 to server version: 4.1.5-gamma-nt

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql> create database sakai default character set utf8;
    Query OK, 1 row affected (0.00 sec)

    mysql> grant all on sakai.* to sakaiuser@'localhost' identified by 'sakaipassword';
    Query OK, 0 rows affected (0.00 sec

    mysql> grant all on sakai.* to sakaiuser@'127.0.0.1' identified by 'sakaipassword';
    Query OK, 0 rows affected (0.00 sec)

    mysql> quit

### UTF-8 Character Set

When the database is created you must be sure to create it to use the UTF-8 character set, just as Tomcat was configured to use UTF-8. If you don't you may run into a range of issues when attempting to use Unicode characters in Sakai, and this goes for both MySQL and Oracle. Consult your DB documentation or a local DBA for instructions on how to do this.

If you're not certain how your database is currently configured, you can check with a query. Here is a sample query from Oracle showing the correct value:

    SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

    VALUE
    --------------------------------------------------------------------------------
    AL32UTF8

Converting a database from one character set to another is non-trivial, particularly if it's a large production database, and so it's strongly recommended that you verify this aspect of your database creation before deploying Sakai.

### 5.4. Database Properties

There are settings in sakai.properties that also define the database technology and connection information. Appropriate `sakai.properties` settings for Oracle and MySQL, respectively, are listed below, and you need only modify them with your local particulars:

MySQL: |
    hibernate.dialect=org.hibernate.dialect.MySQLDialect
    vendor@org.sakaiproject.db.api.SqlService=mysql
    driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver
    url@javax.sql.BaseDataSource=jdbc:mysql://SERVER:3306/DB?useUnicode=true&characterEncoding=UTF-8
    username@javax.sql.BaseDataSource=USER
    password@javax.sql.BaseDataSource=PASSWORD
    validationQuery@javax.sql.BaseDataSource=select 1 from DUAL
    defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED
Oracle: |
    hibernate.dialect=org.hibernate.dialect.Oracle9Dialect
    vendor@org.sakaiproject.db.api.SqlService=oracle
    driverClassName@javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver
    url@javax.sql.BaseDataSource=jdbc:oracle:thin:@SERVER:1521:DB
    username@javax.sql.BaseDataSource=USER
    password@javax.sql.BaseDataSource=PASSWORD
    validationQuery@javax.sql.BaseDataSource=select 1 from DUAL
    defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

### 5.5. Configuring for Performance

Larger institutions have found the database to be the bottleneck when it comes to Sakai performance. Some additional DB configuration settings may be worth considering.

#### 5.5.1. Oracle Performance

Oracle may have performance problems with some of the SQL settings that work for HSQL and MySQL. Sakai installations using Oracle should strongly consider the following settings in `sakai.properties` to avoid these problems:

    # For improved Oracle performance (from the University of Michigan)
    validationQuery@javax.sql.BaseDataSource=
    defaultTransactionIsolationString@javax.sql.BaseDataSource=
    testOnBorrow@javax.sql.BaseDataSource=false

Oracle should be set to the proper settings for the first two items (above) automatically; setting them with each use may affect performace. In addition, validating the connection on each transaction caused problems in at least one large production environment (University of Michigan).

#### 5.5.2. MySQL Performance

MySQL performance can be considerably improved by caching queries (Oracle does this already, without any extra configuration). You can set this in sakai.properties by altering the connection string (i.e. this will replace the simpler value indicated above) as below, all on one line.

         url@javax.sql.BaseDataSource=jdbc:mysql://localhost:3306/sakai?useUnicode=true&characterEncoding=UTF-8
    &useServerPrepStmts=false&cachePrepStmts=true&prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096

(all one line)

The parameter that enables use of the query cache is 'useServerPrepStmts=false', while the others (cachePrepStmts=true, prepStmtCacheSize=4096, and prepStmtCacheSqlLimit=4096) are for caching the parsing of prepared statements on the server side. Based on production experience, we'd recommend a query cache size of 64M, configurable in /etc/my.cnf like this:

    [mysqld]
    query_cache_size = 64M

There are some other settable properties for the query cache, but there doesn't seem to be a need to change the defaults. To learn more, you can visit:

- http://dev.mysql.com/doc/refman/4.1/en/query-cache-configuration.html

- http://dev.mysql.com/doc/refman/4.1/en/connector-j-reference-configuration-properties.html

### MySQL 5

The above configuration properties work for MySQL 4.1 only. The connection parameters that allow MySQL caching on MySQL 4 cause problems with bit types on MySQL 5. We will likely have a better answer for the next Sakai release, but the issue was not resolved in time for Sakai 2.3. In the interim the basic, non-caching string must be used for MySQL 5, and since this means poorer performance, MySQL 5 is currently not recommended for production.

#### 5.5.3. Disk I/O on Linux (SLES9/RHEL4)

A Linux database server may see big disk-hanging delays (especially if the DB and the web server are on the same machine) under load. This appears to be a side effect of the default SLES9 I/O scheduler, CFQ. The Deadline scheduler, which has a maximum latency for serving requests, is therefore a better choice for database operations - although pages may still render slowly under load, performance will degrade more gracefully, avoiding hangs, and provide some feedback to users.

To switch to the Deadline scheduler, the boot parameter elevator=deadline must be passed to the kernel that's being used. You can do so by editing the `/etc/grub.conf` file and adding the parameter to the kernel that's being used:

    title Red Hat Enterprise Linux AS (2.4.21-32.0.1.ELhugemem)
        root (hd0,0)
        kernel /vmlinuz-2.4.21-32.0.1.ELhugemem ro root=/dev/sda2 elevator=deadline reboot=warm
        initrd /initrd-2.4.21-32.0.1.ELhugemem.img

Make sure to reboot the system to activate the new scheduler. You can also see these links for more information:

- http://www.puschitz.com/TuningLinuxForOracle.shtml

- http://nextre.it/oracledocs/ioscheduler_01.html

- http://www.open-mag.com/features/Vol_104/SLES9/SLES9.htm

### 5.6. Startup

Once you've configured the database appropriately, you need only stop and restart Tomcat. As Tomcat is coming up you can watch its log to see if there are any database connection errors (see the Troubleshooting section).

### Oracle and Tests & Quizzes

If you're running Oracle you should check the datatype of the MEDIA column in the SAM_MEDIA_T table. Hibernate tries to choose the right data type for a field, but has a habit of choosing the wrong one for Oracle. The correct types for each database are:

HSQL: | varbinary
MySQL: | longblob
Oracle: | blob

If you need to change this type for your database, this will also involve finding the primary key constraint, dropping it and then recreating it. Contact your local DBA for further information on making this change. Below is some sample Oracle SQLplus output to better illustrate (SYS_C0064435 is the example constraint; replace it with yours):

    SQL> alter table SAM_MEDIA_T modify MEDIA BLOB;

    Table altered.

    SQL> select constraint_name from user_constraints where table_name='SAM_MEDIA_T'
    and CONSTRAINT_TYPE='P';

    CONSTRAINT_NAME
    ------------------------------
    SYS_C0064435

    SQL> alter table sam_media_t drop constraint SYS_C0064435;

    Table altered.

    SQL> alter table SAM_MEDIA_T add constraint SYS_C0064435 primary key (MEDIAID);

    Table altered.

    SQL> desc SAM_MEDIA_T;

    [table with BLOB type]

    SQL> select constraint_name from user_constraints where table_name='SAM_MEDIA_T'
    and CONSTRAINT_TYPE='P';

    CONSTRAINT_NAME
    ------------------------------
    SYS_C0064435

    SQL> commit;

    Commit complete.

Prev |  | Next
4. Post-Installation Configuration | Home | 6. Getting Started with the Software