Database Configuration and Tuning

By default, all distributions of Sakai are configured to use an in-memory version of HSQL db, which is adequate for very basic testing, but does not offer the same reliability and scalability as a more robust relational database. Many developers and the vast majority of Sakai installations choose to use either MySQL or Oracle in production. The reference sakai.properties files include example configurations for Oracle and MySQL. Rather than copying and pasting this code, you should comment out the database configurations you aren't using, and then uncomment and modify the settings appropriate for your database.

Here is a sample configuration block for Oracle:

Here is a sample configuration block for MySQL:

When using this block or uncommenting an existing block, you will need to modify the connection settings specified by the setting url@javax.sql.BaseDataSource at the very least. In addition, you'll need to set the username and password options whichever database you choose, as in:

Configuring for Performance

Larger institutions have found the database to be the bottleneck when it comes to Sakai performance. Setting additional database configuration settings may be worth considering (see below for tips for Oracle and MySQL).

Oracle

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

These settings will reduce the DB load by not forcing these settings with each use or transaction.

Oracle and Tests & Quizzes  

If you're running Oracle and using the Tests&Quizzes tool 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.

MySQL

Case Sensitivity

By default, table names in MySQL are case insensitive on Windows, and case sensitive on UNIX systems. Portions of Sakai that attempt to access tables directly may specify table names in all uppercase or lowercase, which may cause problems if the name specified in the query does not correspond exactly to the name of the table. The solution to this problem (other than waiting for code to be cleaned up) is to configure MySQL to think of table names as being case insensitive. This can be accomplished by editing /etc/my.cnf and adding the following:

Note If you already are running at standard unix mode 0, you have to convert all of your database and table names to lowercase first as per the notice in the guide before you can switch to one. These statements worked for me to convert all of the tables in one database. Insert your database name that you want to convert here. You could probably do all tables in all databases by just getting rid of the where clause. You also need to rename databases (with the RENAME DATABASE) command if the contain upper case characters.

Query Caching

MySQL performance can be considerably improved by caching queries. You can do this by editing your sakai.properties file and altering the connection string 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

The parameter that enables use of the query cache is 'useServerPrepStmts=false', while the others (cachePrepStmts=true, prepStmtCacheSize=4096, and prepStmtCacheSqlLimit=4096) enable caching of prepared statements on the server side. Based on production experience at UniSA, a query cache size of 128M is recommended. The query cache size is typically configured on UNIX systems by editing /etc/my.cnf to like this:

[mysqld]
query_cache_size = 128M

You will need to restart your instance of mysql after making the change.

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

SAKAI_PRESENCE as an in-memory table

Running SAKAI_PRESENCE as an in-memory table can have a significant positive effect on performance for MySQL. The SQL necessary to convert is just:

DROP TABLE SAKAI_PRESENCE;

CREATE TABLE `SAKAI_PRESENCE` (
`SESSION_ID` varchar(36) default NULL,
`LOCATION_ID` varchar(255) default NULL,
KEY `SAKAI_PRESENCE_SESSION_INDEX` (`SESSION_ID`),
KEY `SAKAI_PRESENCE_LOCATION_INDEX` (`LOCATION_ID`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

But be sure to run this at a quiet time as otherwise you'd be sure to generate some odd results for users.

Disk I/O issues on Linux (Suse Linux Enterprise Server 9/Red Hat Enterprise Linux 4)

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 will 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

You will need to reboot the system to activate the new scheduler.

For more information about these Database issues, refer to the following:

• http://www.puschitz.com/TuningLinuxForOracle.shtml
• http://nextre.it/oracledocs/ioscheduler_01.html
• http://www.open-mag.com/features/Vol_104/SLES9/SLES9.htm