We're looking at different database options at the moment prior to implementing.
Is there anyone out there using Oracle as their main production database for their Moodle implementation? Any example/reference sites that anyone knows about?
Cheers.
We are!
http://qube.harlow-college.ac.uk
Our Moodle site is a bit odd though, and (as I type) the Lecturers don't know it exists yet. We're just about to unleash them on it. I've also done a LOT of hacks on it!
http://qube.harlow-college.ac.uk
Our Moodle site is a bit odd though, and (as I type) the Lecturers don't know it exists yet. We're just about to unleash them on it. I've also done a LOT of hacks on it!
We're not using Oracle ourselves, but I'm very interested in learning how well it works. Did you have make any hacks for basic functionality, or just to personalize it for your institution?
Nice looking site, by the way.
Nice looking site, by the way.
We are running a small install here. Once a couple of subtle bugs were detected and fixed (thanks Eloy!), it's been up and running without any hacks at all (just theme personalization).
Performance is not that great, but it works
Saludos. Iñaki.
Performance is not that great, but it works
Saludos. Iñaki.
Great news about it being an easy install.
Do you think the not-good performance means that the code needs to be optimized more, or is it just that you have a small server?
The reason I'm interested is that sometimes I have students who'd really like to use Moodle in their schools or colleges, and it'd be a lot easier for some of them to sell to the administration if it ran well on Oracle.
Hmm... we have a university-wide Oracle license. Maybe I'll give it a try in my copious free time.
Do you think the not-good performance means that the code needs to be optimized more, or is it just that you have a small server?
The reason I'm interested is that sometimes I have students who'd really like to use Moodle in their schools or colleges, and it'd be a lot easier for some of them to sell to the administration if it ran well on Oracle.
Hmm... we have a university-wide Oracle license. Maybe I'll give it a try in my copious free time.
> we have a university-wide Oracle license
I have a world-wide PostgreSQL license I can share if anyone wants it, unlimited support and upgrades included
Warning: slightly offtopic Oracle/Pg war stories...
Most institutions that are using Oracle internally have something much more valuable than the Oracle license: knowledgeable DBAs. And I haven't found a single Oracle DBA that after a 30 minute tour of PostgreSQL didn't "click" with it. Modern Pg (7.4 or 8.x) has almost the same tunables, and very similar performance profile.
At Catalyst we run several large systems and our DBAs deal with a mix of Oracle and PostgreSQL. The only difference we see with Oracle is that when we need support on anything that isn't a trivial RTFM, it takes a long time to get a useful response from Oracle support. With Pg if you do your homework and can explain your problem clearly on the lists, you'll get a reply from Tom, Josh or another core hacker in a couple of hours.
(That's something that DBAs find when they hit their first snag with Pg -- and are usually quite impressed. And the mailing lists are full of ex-Ora DBAs so everyone understands the Oracle lingo and the gotchas that hit a recent convert).
Those are my .02c from experience advocating Pg in a few corporate/govt/edu environments. In a few places (that shall not be named), after the CTO had said that they were a pure Oracle shop and had left the meeting... the Oracle DBAs would confess that actually they were running quite a few installs of Pg but hadn't told anyone.
At one big govt department last year Pg ended up having formal recognition as part of their IT infrastructure because of a Moodle deployment we did for them
I have a world-wide PostgreSQL license I can share if anyone wants it, unlimited support and upgrades included
Warning: slightly offtopic Oracle/Pg war stories...
Most institutions that are using Oracle internally have something much more valuable than the Oracle license: knowledgeable DBAs. And I haven't found a single Oracle DBA that after a 30 minute tour of PostgreSQL didn't "click" with it. Modern Pg (7.4 or 8.x) has almost the same tunables, and very similar performance profile.
At Catalyst we run several large systems and our DBAs deal with a mix of Oracle and PostgreSQL. The only difference we see with Oracle is that when we need support on anything that isn't a trivial RTFM, it takes a long time to get a useful response from Oracle support. With Pg if you do your homework and can explain your problem clearly on the lists, you'll get a reply from Tom, Josh or another core hacker in a couple of hours.
(That's something that DBAs find when they hit their first snag with Pg -- and are usually quite impressed. And the mailing lists are full of ex-Ora DBAs so everyone understands the Oracle lingo and the gotchas that hit a recent convert).
Those are my .02c from experience advocating Pg in a few corporate/govt/edu environments. In a few places (that shall not be named), after the CTO had said that they were a pure Oracle shop and had left the meeting... the Oracle DBAs would confess that actually they were running quite a few installs of Pg but hadn't told anyone.
At one big govt department last year Pg ended up having formal recognition as part of their IT infrastructure because of a Moodle deployment we did for them
Oh, I'm not saying that I'd switch to Oracle myself (even though it is free beer for us). What I had in mind was more of an experiment.
It's just that (as with the CTO you mentioned) "runs under Oracle" seems to be a check box for a lot of institutions, fair or not (and I agree completely that it's not fair). Oracle has a huge presence in higher ed in this country.
I'm interested in the MS SQL stuff for much the same reason. MS seems to own K-12 for large school districts, much as Oracle ownes higher ed.
If my students could say "yeah, it runs under Oracle/MS SQL" they'd have better luck at getting their higher-ups to consider it. Once the foot is in the door, then it'd be time to advocate switching to a superior "free as in freedom" product.
It's just that (as with the CTO you mentioned) "runs under Oracle" seems to be a check box for a lot of institutions, fair or not (and I agree completely that it's not fair). Oracle has a huge presence in higher ed in this country.
I'm interested in the MS SQL stuff for much the same reason. MS seems to own K-12 for large school districts, much as Oracle ownes higher ed.
If my students could say "yeah, it runs under Oracle/MS SQL" they'd have better luck at getting their higher-ups to consider it. Once the foot is in the door, then it'd be time to advocate switching to a superior "free as in freedom" product.
Do you think the not-good performance means that the code needs to be optimized more
I had several discussions with Eloy about performance with Ora and MSSQL and what we could do to support them better.
If I remember right, the perf problems with MSSQL and Oracle are:
The lack a complete and fast limit/offset implementation. That means that paginating through the list of users is horrid. Eloy may have implemented a double-nested subselect to workaround this, or perhaps we just advance the cursor over the rows we want to offset over. Either workaround is slow, grep for offset in lib/dmllib.php .
CLOB/Text inserts need to be done in 2 stages (INSERT a null, and then UPDATE with the actual data). I think that Ora10 doesn't have this limitation, but I'm not sure what Moodle/AdoDB are doing with this.
Limited/slow Oracle extension in PHP - For each database backend, the PHP engine has a library/extension that acts a bit like a driver. The extension for Ora has have 2 limitations that have a huge impact on performance:
When dealing with selects with many rows the Ora driver pulls all the rows into memory, rather than iterating over them. If that's true, it's a memory hog.
When going to a specific row in the result (like trying to advance the cursor to emulate offset) it cannot skip to the row. Instead, it moves the cursor through all the rows.
I may be accusing Ora of sins committed by MSSQL -- we were trying to solve portability issues for both and it's all mixed up in my head right now. I'm hoping Eloy can chime in with some authority here...
Note that none of these are showstoppers for small/medium sites. For a large installation, I'd consider putting some pressure on Oracle to get a fix to the issues above
At least plan for a bit of a load test with real-sized datasets. You can hedge your bets keeping Pg as an alternative...
An interesting aside on the similarities between Postgres and Oracle is that one corporation
http://www.enterprisedb.com/
has created an even more Oracle compatible version of Postgres and is selling it into organisations that choke on the cost of an Oracle license. The fact that they were able to do this shows that the two products do have significant similarities. Also the Postgres embedded langauge, pg/SQL has strong similarities with Oracle pl/SQL.
http://www.enterprisedb.com/
has created an even more Oracle compatible version of Postgres and is selling it into organisations that choke on the cost of an Oracle license. The fact that they were able to do this shows that the two products do have significant similarities. Also the Postgres embedded langauge, pg/SQL has strong similarities with Oracle pl/SQL.
Eww... icky.
No limit/offset functionality, in particular, seems like a horrible limitation. It's hard to believe that Oracle wouldn't have fixed this.
I've never really worked with Oracle, myself, and I don't think it has much mind share in academics here. The higher level DB courses here used to use it, I think, but they've been transitioning to MySQL for some time. The university administrative guys, on the other hand, are all about the Oracle.
The freshman intro to programming and databases course I used to teach used Access (emphasis on the AAAAAAACK!), until I convinced the professor in charge to switch to MySQL (big course -- two professors, 12-15 lab instructors, and ~900-1000 students)
No limit/offset functionality, in particular, seems like a horrible limitation. It's hard to believe that Oracle wouldn't have fixed this.
I've never really worked with Oracle, myself, and I don't think it has much mind share in academics here. The higher level DB courses here used to use it, I think, but they've been transitioning to MySQL for some time. The university administrative guys, on the other hand, are all about the Oracle.
The freshman intro to programming and databases course I used to teach used Access (emphasis on the AAAAAAACK!), until I convinced the professor in charge to switch to MySQL (big course -- two professors, 12-15 lab instructors, and ~900-1000 students)
To answer the original question "Who uses Oracle?", the answer in the commercial world is
everyone, his dog and his aunty. Plausible?
Go to your favorite Job advertising web site and search on Oracle and whatever else comes next. Usually there are more jobs containing the keyword Oracle than whatever other technology you might think is in demand, e.g. more than Java, more than C# more than C++ yadda yadda
I just got the following results from jobserve.co.uk
oracle 3769
c# 3032
Java 4283
(OK today there are more Java jobs, but you get the picture)
everyone, his dog and his aunty. Plausible?
Go to your favorite Job advertising web site and search on Oracle and whatever else comes next. Usually there are more jobs containing the keyword Oracle than whatever other technology you might think is in demand, e.g. more than Java, more than C# more than C++ yadda yadda
I just got the following results from jobserve.co.uk
oracle 3769
c# 3032
Java 4283
(OK today there are more Java jobs, but you get the picture)
Do you think the not-good performance means that the code needs to be optimized more, or is it just that you have a small server?
I suspect both of them. I'm told the server running this small install is not specially powerful and that Moodle is running on a non-tuned Windows 2000 system.
As most of other people have said below, Oracle was chosen because there was a lot of Oracle know-how in-house (Windows being on the same boat too).
It wasn't my favourite choice, but I'm not the one in charge of the server, so...
Saludos. Iñaki.
Hello,
in my job there is 80,000+ moodle users working on a LAMP based plattform.
We have discovered a lot of oportunities tunnig Moodle to get a better performance, but recently my IT managers consider Oracle as an alternative.
What can we expect from a Oracle and Moodle integration?
Thanks. David!
in my job there is 80,000+ moodle users working on a LAMP based plattform.
We have discovered a lot of oportunities tunnig Moodle to get a better performance, but recently my IT managers consider Oracle as an alternative.
What can we expect from a Oracle and Moodle integration?
Thanks. David!
"What can we expect from a Oracle and Moodle integration?"
You can expect things to be a bit slower. There are two main problems:
1) Oracle cannot tell the differences between the empty string "" and NULL. Moodle, however, uses both values and the difference is important. Therefore Moodle has to check all data going to and from the database, and replace empty string with another value. That slows down every interaction with the database.
2) Moodle handles a lot of textual content (CLOB columns in the database). The Oracle protocol for communicating between the database and the application server deals with CLOBS in a really inefficient way, which also slows things down.
There is a third problem not related to performance:
3) Since very few people use Oracle with Moodle, then if there are database-specific bugs (which are rare) then things are more likely to break on Oracle than on MySQL or Postgres. Also, if a community plugin fails to work on some databases, it is more likely that the one that fails is Oracle.
Therefore, you should only use Oracle if you really have no other choice.
I'd extend Tim's 3rd point slightly as well - because so few people use Oracle (relatively), if you do have any issues, there are fewer people to provide the level of support that the other databases might have through the moodle forums.
That is probably the clearest statement I have seen about Moodle and a database used by Moodle, thanks Tim.
Now, my question would be, that if so few people actually use Oracle, why is there not another, different, database, SAP for example, included as like MySQL, PostGreSQL? Why was an Oracle interface developed?
I do understand it is a complicated process developing the data extraction layer, but there has been an awful long time since a new product was used as a database for Moodle. Could it be that a new database that is connectable with Moodle would expand opportunities for Moodle?
Well, back in 2006 when the current database abstraction layer was being developed, those were the four databases they chose. Since then, there have only been occasional flurries of interest in supporting something else. (The two I am aware of being talked about are SQLlight and IBM DB2, but neither effort got to a working system, I think.)
I am not aware of a database management system called SAP. Do you have a URL for what you are talking about?
The reason to support Oracle is that some IT departments insisit on only using Oracle. Therefore, if we did not support Oracle, they would be shut out.
AFAIK, Tim, SAP is an outgrowth or descendant of Sybase, and is supposed to be, well reputed here as being, the major rival to IBM's DB2 and Oracle. Historically, DB2 was a market leader, then overtaken by Oracle. It then fell into a bit of a hole at the same time as Sybase started growing. A new player appeared, SAP, behind a vanishing Sybase, which was supposed to be more sophisticated and commanded a large part of the European market, so we heard here. I heard rumblings occasionally, that DB2 had made a comeback. At that time though, we in Oz were not really well connected to what was happening in Europe, more closely align to the US, which means Oracle and MSSQL Server, DB2 was big corporation stuff anyway. This would likely be about the time Martin began working on Moodle, ('96-97), and I changed jobs and got more involved in web design and development, but nowhere near as intense as it is now and HTML4.0 was still new I would think..
Living so far away from it all now, I have no idea what the actual state of play is with regard to what is leading or otherwise. All I am asking is if it is possible that Moodle would expand the number of database platforms it could use by producing a data extraction layer for more than just the four that are available now. I am thinking it could be an opportunity to expand Moodle's reach, into the larger, and higher end, corporate sector. DB2 and SAP are not likely to be tools used by small and medium sized businesses, so why Oracle and not the other two? Is it possible that the lack of availability of access to these dbms' is the reason? Could it be workable to develop a DA extraction layer to suit these anyway? Is it desirable for Moodle to move in this area?
Well, Google just found me http://db-engines.com/en/ranking/relational+dbms, which suggests Moodle chose the right 4.
My own opionion is that Postgres is amazing, and open source, so everyone should just use that
In my opinion, there is nothing against adding more in principle, but someone would actually have to do the work. I think there are more important things to work on, but this is open source. Anyone can volunteer to do anything.
Around the year 2000 I worked for the Press Association, which at the time was second only in web traffic to the BBC. They had 4 Mysql Databases as the front end to an Oracle database. This architecture was based on their experience that Oracle was not suitable for a high traffic web sites.
That is a long time ago but it might give some clues to why Oracle is not a first choice for use with Moodle (in addition to the comments by others in this thread).
I think it is very unlikely that Moodle will ever support the SAP adaptive server but if that was attempted it might be made easier because SAP is a descendent of Sybase and so is MS SQL server which is supported by Moodle.
But on balance use MySQL or Postgres. In addition to support from core/HQ you get better support for plugins. I try to keep to the Moodle db API but I have been tripped up by very slight peculiarities in MS SQL server in the past.
That doesn't surprise me Marcus, at one point we made the switch to Borland's Interbase, to get away from Sybase, it was touchy and pricey, whereas Interbase just seemed more stable, easier to use and was considerably cheaper. (Consideration of the clientele was the main reason for this change, what could they pay for.) Arguably, we got a better performance too. And now you mention it, I do recall there was a strong connection to Sybase by MSSQL server.
Tim, yes, it is a lot of work, and being Open Source, someone may have a go at it, so one day, perhaps. As you point out though, is it necessary?
Thanks for answer,
Oracle Database is an important piece of my Job's tech stack.
In other hand, the need for a migration to Oracle Database is based in my opinion, in a idea behind the corporate software (I don know what exactly).
But i think there is still a choice.
Moodle works fine with mysql or postgresql, but it needs a lot of measure and fine tunnig.
Thanks for the answer again!!
David, México
Hi Iñaki Arenaza,
My center is also considering Oracle DB. May I know which Moodle version you have installed? Do we need to defined all oracle.sql files for the installation?
Thanks a lot!
"We" are running 1.7.1+. I put the "we" in quotes because I don't
administer the server and haven't set it up (just helped the sysadmins
with a couple of minor glitches during the Moodle setup process).
So I don't know if you need all those .sql files you are refering to
Saludos. Iñaki.
administer the server and haven't set it up (just helped the sysadmins
with a couple of minor glitches during the Moodle setup process).
So I don't know if you need all those .sql files you are refering to
Saludos. Iñaki.
Oops! Forgot I hadn't subscribed to this forum!
After doing our Oracle/Moodle link-up I documented the process (partly because I believe we are the first college in the UK to have done this). I am hosting it on my own (personal) webserver at home at the moment - but I'll attach a copy here also (coz those instructions on my own webserver might not stay there forever).
There is one extra note that isn't included in that documentation - remember to change your PHP magic_quotes setting (or else quotes get escaped incorrectly in the database). This is now documented on the Oracle-on-Moodle Wiki (after we fell foul of it)
The major step for us was diagnosing and hacking out the lines in install.php that tries to set the character set (the "Optional Step" in my documentation) - whilst we had the charset set correctly, it simply wasn't playing with our Oracle installation and Moodle (and was preventing us from getting any further). I don't know if this is specific to our set-up or whether it constitutes a bug (hence not reporting it to the tracker). In future if more people are affected by this problem then maybe someone will want to report it to the tracker and it can be fixed (by someone that knows more about databases than I do).
Note: In a little over 2 weeks I will no longer work in education (been made an offer-I-can't-refuse to go into private sector web dev), so if there are any queries about that process, ask quickly using my direct email: cfowler@harlow-college.ac.uk
I plan (before I leave) to document the little hacks I've done and post them (in some fashion) back to the Moodle community, as some of them might be useful to include in future Moodle versions. Things like a selection mechanism for "ambiguous" usernames, a double-check mechanism for matching users with a second LDAP field (not username), and I think one or two others that I've forgotten about now ...
Craig Fowler
After doing our Oracle/Moodle link-up I documented the process (partly because I believe we are the first college in the UK to have done this). I am hosting it on my own (personal) webserver at home at the moment - but I'll attach a copy here also (coz those instructions on my own webserver might not stay there forever).
There is one extra note that isn't included in that documentation - remember to change your PHP magic_quotes setting (or else quotes get escaped incorrectly in the database). This is now documented on the Oracle-on-Moodle Wiki (after we fell foul of it)
The major step for us was diagnosing and hacking out the lines in install.php that tries to set the character set (the "Optional Step" in my documentation) - whilst we had the charset set correctly, it simply wasn't playing with our Oracle installation and Moodle (and was preventing us from getting any further). I don't know if this is specific to our set-up or whether it constitutes a bug (hence not reporting it to the tracker). In future if more people are affected by this problem then maybe someone will want to report it to the tracker and it can be fixed (by someone that knows more about databases than I do).
Note: In a little over 2 weeks I will no longer work in education (been made an offer-I-can't-refuse to go into private sector web dev), so if there are any queries about that process, ask quickly using my direct email: cfowler@harlow-college.ac.uk
I plan (before I leave) to document the little hacks I've done and post them (in some fashion) back to the Moodle community, as some of them might be useful to include in future Moodle versions. Things like a selection mechanism for "ambiguous" usernames, a double-check mechanism for matching users with a second LDAP field (not username), and I think one or two others that I've forgotten about now ...
Craig Fowler
Hi Craig,
Cool document!
I've added one reference to it from the Installing Oracle for PHP SMALL documentation.
About the NLS_LANG thing, all the installations I've performed have required it (or at least, setting that hasn't been a problem if the DB was supporting such charset). Anyway it would be great to know if more people is having that problem with their Oracle installs, to see if we can find/detect any reason/combination producing that issue.
Great addition!, ciao
Cool document!
I've added one reference to it from the Installing Oracle for PHP SMALL documentation.
About the NLS_LANG thing, all the installations I've performed have required it (or at least, setting that hasn't been a problem if the DB was supporting such charset). Anyway it would be great to know if more people is having that problem with their Oracle installs, to see if we can find/detect any reason/combination producing that issue.
Great addition!, ciao
I have used these instructions to create a step by step process for installing Moodle on an Oracle Database with a Red Hat Linux server. Here you will find the Google Doc of the instructions and a screencast to show how I did it.
Thank you so much for helping with this. Without your instructions, I would never have been able to do this.
Thank you so much for helping with this. Without your instructions, I would never have been able to do this.
Yes I am using Oracle with Moodle 1.9 now going to move to Moodle 2.0 with Oracle 10.
Time in Production: 2 years
Moodle 2.5.7
RHEL 6
Oracle 11.2.0.2
30,000+ Users
We have had some issues from time to time but if you have Oracle expertise in house generally we've been able to work around them. There are a few places, especially with plugins where developers don't fully test things on Oracle. Generally the problems are small and fixable. Null value differences that sort of thing.
Daniel Nelson
University of Massachusetts, Amherst
We have a inhouse expertise with Oracle and in process of installing Moodle 2.6 on Oracle 12c. We would like to speak with someone that has implement with the same approach and could talk to us about his experience (bug and else)
Thanks to reply back ASAP
Yvan Rousseau