September 29th, 2009

...now browsing by day

 

Steps taken to increase performance in Moodle using Oracle database

Tuesday, September 29th, 2009

This may be a little off topic, but I was on a team to help resolve a performance issue with Moodle using Oracle for the backend database. The following is part of the solution that was found.

Changing the CLOB datatype to VARCHAR2(4000) in the m_config database table provided the most help. The following links were the resources I found to point this out.

https://tracker.moodle.org/browse/MDL-16955
http://moodle.org/mod/forum/discuss.php?d=87509#p389413

Using SQLDeveloper, I performed the following actions to the data after stopping the Apache Web server.

create table m_config_original as (select * from m_config);
drop table m_config;
CREATE TABLE M_CONFIG
(
ID NUMBER(10,0) NOT NULL ENABLE,
NAME VARCHAR2(255 BYTE) NOT NULL ENABLE,
VALUE VARCHAR2(4000 BYTE) NOT NULL ENABLE
);
insert into m_config (select * from m_config_original);

Restart the Apache web server

With this single change, the pages displayed in 2/3 less time based on
the original display time. This was a significant amount – 15 seconds
down to 5 seconds.

Additional Notes:
Before applying the changes to the m_config table, I needed to determine
if the oci8 driver was the main problem. I was able to verify that the
OCI8 driver was not the issue by running the following test page on the
moodle server. Here is the PHP code:

<?php

# Sample code for make connection with Oracle Database and send simple DDL,DML and query commands from oci8 extension.
# Database instance: fuju
# host name :  fuju.exzilla.net
# lintener port number : 1521
# user : scott
# Password : tiger
# You can connect to database server from this program without setting tnsnames.ora.
# fuju at exizilla dot net, Jan10,2002
# Modified from original of " http://www.php.net/manual/en/function.ocilogon.php "

print "<HTML><PRE>";
# Sample connection string, use your own values before testing
# $db = "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = fuju.exzilla.net)(PORT = 1521)))(CONNECT_DATA=(SID=fuju)))";
$c1 = ocilogon("username","password",$db);
$c2 = ocilogon("username","password",$db);

function create_table($conn)
{ $stmt = ociparse($conn,"create table hellotable (hellocol varchar2(64))");
ociexecute($stmt);
echo $conn." created hellotable\n\n";
}

function drop_table($conn)
{ $stmt = ociparse($conn,"drop table hellotable");
ociexecute($stmt);
echo $conn." dropped hellotable\n\n";
}

function insert_data($conn)
{ $stmt = ociparse($conn,"insert into hellotable
values('$conn' || ' ' || to_char(sysdate,'DD-MON-YY HH24:MI:SS'))");
ociexecute($stmt,OCI_DEFAULT);
echo $conn." inserted hellotable\n\n";
}

function delete_data($conn)
{ $stmt = ociparse($conn,"delete from hellotable");
ociexecute($stmt,OCI_DEFAULT);
echo $conn." deleted hellotable\n\n";
}

function commit($conn)
{ ocicommit($conn);
echo $conn." committed\n\n";
}

function rollback($conn)
{ ocirollback($conn);
echo $conn." rollback\n\n";
}

function select_data($conn)
{ $stmt = ociparse($conn,"select * from hellotable");
ociexecute($stmt,OCI_DEFAULT);
echo $conn."----selecting\n\n";
while (ocifetch($stmt))
echo $conn." <".ociresult($stmt,"TEST").">\n\n";
echo $conn."----done\n\n";
}

# start Main program
echo "<h3> Start :: Simple oci8 extension functions test </h3><p><hr><p>";

create_table($c1);
insert_data($c1);   // Insert a row using c1
insert_data($c2);   // Insert a row using c2

select_data($c1);   // Results of both inserts are returned
select_data($c2);

rollback($c1);      // Rollback using c1

select_data($c1);   // Both inserts have been rolled back
select_data($c2);

insert_data($c2);   // Insert a row using c2
commit($c2);        // commit using c2

select_data($c1);   // result of c2 insert is returned

delete_data($c1);   // delete all rows in table using c1
select_data($c1);   // no rows returned
select_data($c2);   // no rows returned
commit($c1);        // commit using c1

select_data($c1);   // no rows returned
select_data($c2);   // no rows returned

drop_table($c1);
print "</PRE></HTML>";

echo "<hr><h3> End :: Simple oci8 extension functions test </h3>";

?>

This test confirmed that the OCI8 driver was functioning correctly and
was not the problem.

With this change, performance improved by reducing the time to display pages by 67%!