Hello,
We set up a Moodle system in the second half of last year with Oracle 10gR2 as the database. Actually, we've now set up three Moodle systems (dev/test/live), all with Oracle at the back end. We're having some speed issues alluded to in other forum posts here, but the trick of changing the 'value' column's datatype in the 'config' table from a CLOB to a varchar2 sped things up (on our test system at least) by a factor of around three.
FYI, we are using RHEL5 app servers, RHEL5 RPM version of Apache, compiled version of PHP 5.2.10 with OCI8 1.3.5.
Does anyone have any thoughts about these questions:
* Would we get any performance increase from migrating the database(s) to Oracle 11g and using Database-Resident Connection Pooling?
* Can we change every CLOB datatype in the database to varchar2(4000) and be confident that nothing will break? Does anyone know of anything in Moodle 1.9(.5-7) that relies on CLOBs in particular?
* The forum posts I found here relating to Oracle with Moodle were all around 1-2 years old. Would anyone care to share their latest success stories/tales of woe/tips and tricks regarding running Moodle with Oracle?
Thanks,
Guy.
I don't know much about Oracle.
The Moodle database structure is defined in files called install.xml in each plugin. (You can see these by going to Administration -> Miscellaneous -> XMLDB editor.
Moodle distinguishes three sizes of text column. Small, medium and large.
I would have thought it would be safe to change all the small ones to varchar2(4000). The mapping from the generic types in install.xml files to actual database types is documented on Development:XMLDB_column_types.
The Moodle database structure is defined in files called install.xml in each plugin. (You can see these by going to Administration -> Miscellaneous -> XMLDB editor.
Moodle distinguishes three sizes of text column. Small, medium and large.
I would have thought it would be safe to change all the small ones to varchar2(4000). The mapping from the generic types in install.xml files to actual database types is documented on Development:XMLDB_column_types.
Thanks Tim, I'll investigate and do some testing on our test system. Nice koala in your profile pic BTW 
I switched to that picture over a year ago to prove I was in Australia for a year.
I have now been back in the UK for 6 months, I really must get around to changing it to something more appropriate. A picture of me standing in the rain with an umbrella, perhaps?
I have now been back in the UK for 6 months, I really must get around to changing it to something more appropriate. A picture of me standing in the rain with an umbrella, perhaps?
We're running multiple load-balanced Moodle front ends on RHEL5 with clustered Oracle 10g RAC databases, so we've been down this path before. Hopefully I can offer some suggestions.
"Would we get any performance increase from migrating the database(s) to Oracle 11g and using Database-Resident Connection Pooling?"
Our DBAs are dead keen on this, and although I cannot provide any data on it we will be testing it ourselves inside the next 3 months.
"Can we change every CLOB datatype in the database to varchar2(4000) and be confident that nothing will break?"
Do NOT do that. We tried it in our test environment and it failed in a number of places, particularly the 'modinfo' field in the m_course table. In quite a few other places it may work with varchar2(4000) but we could not be certain of that, so we decided to stick with CLOBs for now, with the exception of the m_config table as we were reasonably certain we would not run into an issue with it.
"Does anyone know of anything in Moodle 1.9(.5-7) that relies on CLOBs in particular?"
Well, any field that relies on unlimited length text fields. 'TEXT' in MySQL is a roughly equivilent data type to CLOB. So, you're stuck with it for now i'm afraid.
"The forum posts I found here relating to Oracle with Moodle were all around 1-2 years old. Would anyone care to share their latest success stories/tales of woe/tips and tricks regarding running Moodle with Oracle?"
We are in the process of moving to Moodle and currently we're running around 5% of our courses on Moodle so far which went live on 15th of Feb; we had over 10,000 individual students log in within the first 5 days. We have 3 RHEL5 front end servers and 3 x Oracle 10g RAC servers, so far very very stable but we're also seeing some performance issues like you generally related to CLOBs, and we're looking into the issue at the moment. I've discussed it a little more below.
Excusing the CLOB stuff, we have had no major issues and everything works flawlessly.
Other tips:
If you're running load-balanced, don't use Database sessions. These were killing our performance and luckily because of how our front-end cluster is setup we did have the option of running Moodle with our sessions saved on the file system which has reduced load quite signifigantly.
Initially we also went with non-persistant Oracle connections due to a percieved stability issue at the time, and we're currently still running with it. Our DBA's have investigated this and it appears that we're using 42% of the database time on authenticating and disconnecting in each script. Yes, each and every script. Literally as I type this we are load testing this in our test environment and will be comparing against Persistant connections within the next 24 hours.
There is a bug fix for some CLOB issues in the newest OCI8 module 1.4.1, I have installed it on our development server but the specific function call it fixes isn't used in Moodle (ADODB actually) so I am not convinced it will fix the CLOB problems.
Many people have said that Oracle 11g fixes the CLOB problems, we're looking at setting up an Oracle 11g sandbox inside the next month to see how well it works.
In addition to this, we found some extra performance by using PHP 5.2.x, this version has a number of performance improvements built in that have helped us.
I assume you are also using APC, too? Using APC as a pure opcode cache has provided some great performance improvements also and is well worth testing.
Hopefully some of that is helpful!
"Would we get any performance increase from migrating the database(s) to Oracle 11g and using Database-Resident Connection Pooling?"
Our DBAs are dead keen on this, and although I cannot provide any data on it we will be testing it ourselves inside the next 3 months.
"Can we change every CLOB datatype in the database to varchar2(4000) and be confident that nothing will break?"
Do NOT do that. We tried it in our test environment and it failed in a number of places, particularly the 'modinfo' field in the m_course table. In quite a few other places it may work with varchar2(4000) but we could not be certain of that, so we decided to stick with CLOBs for now, with the exception of the m_config table as we were reasonably certain we would not run into an issue with it.
"Does anyone know of anything in Moodle 1.9(.5-7) that relies on CLOBs in particular?"
Well, any field that relies on unlimited length text fields. 'TEXT' in MySQL is a roughly equivilent data type to CLOB. So, you're stuck with it for now i'm afraid.
"The forum posts I found here relating to Oracle with Moodle were all around 1-2 years old. Would anyone care to share their latest success stories/tales of woe/tips and tricks regarding running Moodle with Oracle?"
We are in the process of moving to Moodle and currently we're running around 5% of our courses on Moodle so far which went live on 15th of Feb; we had over 10,000 individual students log in within the first 5 days. We have 3 RHEL5 front end servers and 3 x Oracle 10g RAC servers, so far very very stable but we're also seeing some performance issues like you generally related to CLOBs, and we're looking into the issue at the moment. I've discussed it a little more below.
Excusing the CLOB stuff, we have had no major issues and everything works flawlessly.
Other tips:
If you're running load-balanced, don't use Database sessions. These were killing our performance and luckily because of how our front-end cluster is setup we did have the option of running Moodle with our sessions saved on the file system which has reduced load quite signifigantly.
Initially we also went with non-persistant Oracle connections due to a percieved stability issue at the time, and we're currently still running with it. Our DBA's have investigated this and it appears that we're using 42% of the database time on authenticating and disconnecting in each script. Yes, each and every script. Literally as I type this we are load testing this in our test environment and will be comparing against Persistant connections within the next 24 hours.
There is a bug fix for some CLOB issues in the newest OCI8 module 1.4.1, I have installed it on our development server but the specific function call it fixes isn't used in Moodle (ADODB actually) so I am not convinced it will fix the CLOB problems.
Many people have said that Oracle 11g fixes the CLOB problems, we're looking at setting up an Oracle 11g sandbox inside the next month to see how well it works.
In addition to this, we found some extra performance by using PHP 5.2.x, this version has a number of performance improvements built in that have helped us.
I assume you are also using APC, too? Using APC as a pure opcode cache has provided some great performance improvements also and is well worth testing.
Hopefully some of that is helpful!
Thanks for the reply James...
I haven't had a chance yet to play with Oracle 11g and DRCP, although I should get time within the next few months to do it. I'll post the results into this thread when I do.
Re: changing every CLOB to varchar2(4000), I examined the amount of data in all our CLOB fields and a lot of them were over 4000 characters, so the only column we have changed is the one in the 'config' table (can't remember the name now and I'm at home so can't check). In our testing, this sped up the system by over three times, so that's good enough for now.
Re: sessions, we're also running file-based sessions rather than database sessions.
Re: persistent or not, do you mean setting persistent in config.php? Ours is set to non-persistent there, but it didn't seem to make any difference when I tested it. Couldn't swear to it though.
Re: OCI8, I'm running 1.3.5, but it seems to have a memory leak, as apache processes don't degrade gracefully on a 'service httpd stop' (I think it detects a double-free). Looking forward to the new version there... didn't know 1.4.1 was out, I'll check it out.
Re: PHP, we're using 5.2.10 at the moment.
Re: accelerator, we're using EAccelerator, but it doesn't seem to be working at the moment... haven't had time to look into that. Facing an issue with cron at the moment... the cron script is dying when it runs <moodle>/search/update.php. Priority is to get to the bottom of that.
Cheers,
Guy.
I haven't had a chance yet to play with Oracle 11g and DRCP, although I should get time within the next few months to do it. I'll post the results into this thread when I do.
Re: changing every CLOB to varchar2(4000), I examined the amount of data in all our CLOB fields and a lot of them were over 4000 characters, so the only column we have changed is the one in the 'config' table (can't remember the name now and I'm at home so can't check). In our testing, this sped up the system by over three times, so that's good enough for now.
Re: sessions, we're also running file-based sessions rather than database sessions.
Re: persistent or not, do you mean setting persistent in config.php? Ours is set to non-persistent there, but it didn't seem to make any difference when I tested it. Couldn't swear to it though.
Re: OCI8, I'm running 1.3.5, but it seems to have a memory leak, as apache processes don't degrade gracefully on a 'service httpd stop' (I think it detects a double-free). Looking forward to the new version there... didn't know 1.4.1 was out, I'll check it out.
Re: PHP, we're using 5.2.10 at the moment.
Re: accelerator, we're using EAccelerator, but it doesn't seem to be working at the moment... haven't had time to look into that. Facing an issue with cron at the moment... the cron script is dying when it runs <moodle>/search/update.php. Priority is to get to the bottom of that.
Cheers,
Guy.
"persistent or not, do you mean setting persistent in config.php? Ours is set to non-persistent there, but it didn't seem to make any difference when I tested it. Couldn't swear to it though."
Yes, that is what I was reffering to. We've now also load-tested persistant connections and we also saw very little improvement so at this point I think we will be sticking with non-persistant connections simply because we won't have to restart the webservers if/when the DB 'goes away'.
"OCI8, I'm running 1.3.5, but it seems to have a memory leak, as apache processes don't degrade gracefully on a 'service httpd stop' (I think it detects a double-free). Looking forward to the new version there... didn't know 1.4.1 was out, I'll check it out."
Like persistant connections we've also load-tested this and saw a minor improvement as it includes some fixes with regards to CLOB handling. It is likely we will move to this new OCI8 module in the near future on our production system as we did see a minor performance improvement.
"changing every CLOB to varchar2(4000), I examined the amount of data in all our CLOB fields and a lot of them were over 4000 characters, so the only column we have changed is the one in the 'config' table (can't remember the name now and I'm at home so can't check). In our testing, this sped up the system by over three times, so that's good enough for now."
We didn't load test this specifically this time as we were already running with this modification, however we have been tracking down some issues with excess load on login and because we're using the LDAP authentication plugin, we were also reading a number of fields from the m_config_plugins table which also contains a CLOB column. We modified this table to use varchar2(4000) in addition to m_config and this also gave an impressive improvement in performance when the site is under load from users logging in. We don't use Moodle Networking so we had no need to keep the MNet SSL key stored in this table.
I hacked up a quick and dirty Moodle script for our load tests to try and prove CLOBs were a major issue.. and they are
The script included 'config.php' so that it loaded the usual Moodle libraries and went through the 'normal' Moodle connection processes, but we intentionally avoided doing anything such as calling authentication functions to avoid any load generated by loading modules and plugins as much as possible. I picked 10 tables with clobs and built queries of these tables selecting all the columns including CLOBS for one test, and all columns excluding CLOBS for the second test.
The difference was staggering; average response time for the no-clob queries was 3 seconds, average for the queries with CLOBs was 29 seconds. Yep, 29 seconds. Admittedly there was a lot of load, but the difference is still huge considering the CLOB script was only selecting around 10 columns more in total.
We can reduce load and improve performance slightly with all these one-percenters, but in reality there is still a major issue with CLOBs, at least in 10g. Unfortunately if it's not fixed in 10g by now, I think it's unlikely to ever be fixed especially considering people are saying it appears to be fixed in 11g.
Yes, that is what I was reffering to. We've now also load-tested persistant connections and we also saw very little improvement so at this point I think we will be sticking with non-persistant connections simply because we won't have to restart the webservers if/when the DB 'goes away'.
"OCI8, I'm running 1.3.5, but it seems to have a memory leak, as apache processes don't degrade gracefully on a 'service httpd stop' (I think it detects a double-free). Looking forward to the new version there... didn't know 1.4.1 was out, I'll check it out."
Like persistant connections we've also load-tested this and saw a minor improvement as it includes some fixes with regards to CLOB handling. It is likely we will move to this new OCI8 module in the near future on our production system as we did see a minor performance improvement.
"changing every CLOB to varchar2(4000), I examined the amount of data in all our CLOB fields and a lot of them were over 4000 characters, so the only column we have changed is the one in the 'config' table (can't remember the name now and I'm at home so can't check). In our testing, this sped up the system by over three times, so that's good enough for now."
We didn't load test this specifically this time as we were already running with this modification, however we have been tracking down some issues with excess load on login and because we're using the LDAP authentication plugin, we were also reading a number of fields from the m_config_plugins table which also contains a CLOB column. We modified this table to use varchar2(4000) in addition to m_config and this also gave an impressive improvement in performance when the site is under load from users logging in. We don't use Moodle Networking so we had no need to keep the MNet SSL key stored in this table.
I hacked up a quick and dirty Moodle script for our load tests to try and prove CLOBs were a major issue.. and they are
The script included 'config.php' so that it loaded the usual Moodle libraries and went through the 'normal' Moodle connection processes, but we intentionally avoided doing anything such as calling authentication functions to avoid any load generated by loading modules and plugins as much as possible. I picked 10 tables with clobs and built queries of these tables selecting all the columns including CLOBS for one test, and all columns excluding CLOBS for the second test.
The difference was staggering; average response time for the no-clob queries was 3 seconds, average for the queries with CLOBs was 29 seconds. Yep, 29 seconds. Admittedly there was a lot of load, but the difference is still huge considering the CLOB script was only selecting around 10 columns more in total.
We can reduce load and improve performance slightly with all these one-percenters, but in reality there is still a major issue with CLOBs, at least in 10g. Unfortunately if it's not fixed in 10g by now, I think it's unlikely to ever be fixed especially considering people are saying it appears to be fixed in 11g.
I'm replying to my own post here, but mainly to correct some of the things I said!
We have done extensive load testing (and I do mean extensive) since that previous post in conjunction with an Oracle staff member who is familiar with the CLOB 'problem' (even though they don't admit to one) and we have found a LOT of performance.
After going through all the graphs, in some situations we are seeing a 10 fold improvement in performance under load, yes it's that much...
There is quite a lot of OCI8 settings you can modify for better performance, we have made these changes and performance has improved signifigantly. In addition to that, we have enabled persistant connections - given a correctly configured OCI8 module we have seen a large improvement here, and surprisingly we have even gone back to storing our sessions in the database and seen no performance degredation.
We are also using Oracle caching on a number of the clob fields, this has helped quite a lot also. Session data clob's aren't being cached as yet due to the DBA's being a little worried about the cache filling the memory. We're monitoring that in Production now and will enable the cache on these columns if they don't use too much memory over time, personally I don't think they will due to garbage collection but the DBA's are right to be wary of it.
Finally, we have also identified a number of mount options for the cluster file system which improved performance as well. We are planning to move to GFS2 as well, yet another performance improvement to be gained from that aparently.
We have done extensive load testing (and I do mean extensive) since that previous post in conjunction with an Oracle staff member who is familiar with the CLOB 'problem' (even though they don't admit to one) and we have found a LOT of performance.
After going through all the graphs, in some situations we are seeing a 10 fold improvement in performance under load, yes it's that much...
There is quite a lot of OCI8 settings you can modify for better performance, we have made these changes and performance has improved signifigantly. In addition to that, we have enabled persistant connections - given a correctly configured OCI8 module we have seen a large improvement here, and surprisingly we have even gone back to storing our sessions in the database and seen no performance degredation.
We are also using Oracle caching on a number of the clob fields, this has helped quite a lot also. Session data clob's aren't being cached as yet due to the DBA's being a little worried about the cache filling the memory. We're monitoring that in Production now and will enable the cache on these columns if they don't use too much memory over time, personally I don't think they will due to garbage collection but the DBA's are right to be wary of it.
Finally, we have also identified a number of mount options for the cluster file system which improved performance as well. We are planning to move to GFS2 as well, yet another performance improvement to be gained from that aparently.
Hey James,
Yes we have done some nice jmeter scripts for testing and they seems to work pretty well. However, our problem arises on the 11g RAC with multiple nodes. What is occurring is that after approx 5-7 days there seems to be some sort of contention build up between the nodes which then escalates the wait times until the nodes all fail. I am currently looking at modifying the load test script so it can be run continuously so we can easily reproduce this problem. Our current move was to place the session"high load CLOB" data onto the file system in an attempt to solve this contention between the nodes however it just re-appeared anyway. So it seems there is a more deep rooted issue with the node communication and CLOB data transfer between them.
My worry is that if we do solve the problem by removing/altering the high load segments such as sessions, are we just postponing the inevitable problem that will occur as soon as a table with clob data gets similar high load? In reality the CLOB data type is a very dated type and just a nice wrapper over the LOB which was completely inadequate. It would be nice if Oracle could take the time to create another type varchar3 which would allow for the norm in web development without all the hacking and appending just to insert a few paragraphs of text into the db.
Yes we have done some nice jmeter scripts for testing and they seems to work pretty well. However, our problem arises on the 11g RAC with multiple nodes. What is occurring is that after approx 5-7 days there seems to be some sort of contention build up between the nodes which then escalates the wait times until the nodes all fail. I am currently looking at modifying the load test script so it can be run continuously so we can easily reproduce this problem. Our current move was to place the session"high load CLOB" data onto the file system in an attempt to solve this contention between the nodes however it just re-appeared anyway. So it seems there is a more deep rooted issue with the node communication and CLOB data transfer between them.
My worry is that if we do solve the problem by removing/altering the high load segments such as sessions, are we just postponing the inevitable problem that will occur as soon as a table with clob data gets similar high load? In reality the CLOB data type is a very dated type and just a nice wrapper over the LOB which was completely inadequate. It would be nice if Oracle could take the time to create another type varchar3 which would allow for the norm in web development without all the hacking and appending just to insert a few paragraphs of text into the db.
"Re: accelerator, we're using EAccelerator, but it doesn't seem to be working at the moment... haven't had time to look into that. Facing an issue with cron at the moment... the cron script is dying when it runs <moodle>/search/update.php. Priority is to get to the bottom of that."
In relation to this, we're using APC as an Opcode cache on each front end node with great results. For example on one production node, we are seeing 99.99% cache hits. Service was restarted around 6 hours ago and so far there has been over 1.6 million cache hits, versus just over 900 cache misses - mostly when the cache itself was primed I suspect and some files that have outlived their TTL. We run APC with apc.stat set to 0 which means we need to restart the webservers when we deploy changes, but for the performance gain it is well worth it.
Highly reccomended.
In relation to this, we're using APC as an Opcode cache on each front end node with great results. For example on one production node, we are seeing 99.99% cache hits. Service was restarted around 6 hours ago and so far there has been over 1.6 million cache hits, versus just over 900 cache misses - mostly when the cache itself was primed I suspect and some files that have outlived their TTL. We run APC with apc.stat set to 0 which means we need to restart the webservers when we deploy changes, but for the performance gain it is well worth it.
Highly reccomended.
We've had exactly the same problem with the search. I had to start by raising the memory limit to in admin/cron.php (line 11, the command was raise_memory_limit).
Then in search/cron_php5.php on line 14 there is a 300 second time limit. When I raised that I discovered that the update was taking 30 minutes(!) I think we're going to have to pull this out of the regular cron job and run it by itself late at night.
We're using 1.9.5 so the line numbers are probably wrong. I hope that helps. If not please post your solution for when we have the same problems here
Then in search/cron_php5.php on line 14 there is a 300 second time limit. When I raised that I discovered that the update was taking 30 minutes(!) I think we're going to have to pull this out of the regular cron job and run it by itself late at night.
We're using 1.9.5 so the line numbers are probably wrong. I hope that helps. If not please post your solution for when we have the same problems here
James, APC sounds good, I'll check it out.
Bruce, I ended up changing a couple of lines in search/add.php and search/update.php... the line that says:
$docIds = get_records_sql_menu($query, array($mod->name));
became:
$docIds = get_records_sql_menu($query);
, and the cron job now finishes rather than dying with a PHP 'Unsupported operand types' error.
I also upped the max_execution_time and memory_limit in cron_php5.php because the cron job ran out of both time and memory the first time it ran after I fixed the above. Perhaps it would be OK on the default settings in cron_php5.php now, but I'll leave it how it is.
Bruce, I ended up changing a couple of lines in search/add.php and search/update.php... the line that says:
$docIds = get_records_sql_menu($query, array($mod->name));
became:
$docIds = get_records_sql_menu($query);
, and the cron job now finishes rather than dying with a PHP 'Unsupported operand types' error.
I also upped the max_execution_time and memory_limit in cron_php5.php because the cron job ran out of both time and memory the first time it ran after I fixed the above. Perhaps it would be OK on the default settings in cron_php5.php now, but I'll leave it how it is.
Hey Guy,
We are currently using oracle 11g RDCP and having massive problems with performance. It cannot be 100% proven that it is caused by the CLOBS but I am pretty sure that is the problem. I am currently looking into solutions for all of the sql clob related errors which are popping up in our system and hoping that ther is a generic way to fix them without hacking the entire system with dbms_lob.substr(). I have already written a fuction that will cater for the IN lists being over 1000 elements and placed that where ever needed but these comparisons, distincts, order by's are going to be a complete hack to remove from the system.
I would be up for any suggestions on your solutions for these problems?
Cheers David Binney
We are currently using oracle 11g RDCP and having massive problems with performance. It cannot be 100% proven that it is caused by the CLOBS but I am pretty sure that is the problem. I am currently looking into solutions for all of the sql clob related errors which are popping up in our system and hoping that ther is a generic way to fix them without hacking the entire system with dbms_lob.substr(). I have already written a fuction that will cater for the IN lists being over 1000 elements and placed that where ever needed but these comparisons, distincts, order by's are going to be a complete hack to remove from the system.
I would be up for any suggestions on your solutions for these problems?
Cheers David Binney
Hi David,
You're ahead of me I'm afraid... been too busy to play with Oracle 11g and DRCP. Changing the config table's CLOB column to a varchar2(4000) was pretty much the trick for us... performance is now OK. I used Apache Bench to test load times of the Moodle home page when the relevant column in the config table was CLOB and varchar2(4000), and the load time difference was more than three times better with varchar2(4000).
I then looked at how many characters were in fields for other CLOBs in the database, and there were some that contained more than 4000 characters, so I elected to leave the rest of the CLOB columns as they were.
How are you testing performance? I found Apache Bench too limited when testing so acquainted myself with JMeter... I can share a JMeter test plan with you if you like.
I'm still seeing the 'IN lists being over 1000 elements' problem in the output from the cron job, but haven't addressed that yet... care to share your function that fixes it?
Cheers,
Guy.
You're ahead of me I'm afraid... been too busy to play with Oracle 11g and DRCP. Changing the config table's CLOB column to a varchar2(4000) was pretty much the trick for us... performance is now OK. I used Apache Bench to test load times of the Moodle home page when the relevant column in the config table was CLOB and varchar2(4000), and the load time difference was more than three times better with varchar2(4000).
I then looked at how many characters were in fields for other CLOBs in the database, and there were some that contained more than 4000 characters, so I elected to leave the rest of the CLOB columns as they were.
How are you testing performance? I found Apache Bench too limited when testing so acquainted myself with JMeter... I can share a JMeter test plan with you if you like.
I'm still seeing the 'IN lists being over 1000 elements' problem in the output from the cron job, but haven't addressed that yet... care to share your function that fixes it?
Cheers,
Guy.
We are currently using jmeter for the testing and it seems to perform quite well. In that the main point that the oracle db is falling over is when intercommunication between the nodes is occurring and possibly the tranfer of the clobs is causing massive waits. This in turn is making the entire db fall over ;) . We are testing load of up to 500 concurrent users for the test and it comes back with pretty average results.
As far as the clob conversion goes we have changed a few to varchar but because of the limitation of 4000 the is not much opportunity to do this anyway.
I have solved the problem of the IN list being over 1000 elements by writing a function to split and rewrite the query in smaller chunks. I have just added this function to wherever i have seen this error occurring. The problem i am having is wondering if there is a better way to solve this problem without having to find all the affected queries and then add the function. I am still waiting for a response to this question.
I have attached the grep for the changes i have made concerning the 1000 list and also the files that has the function to solve it. The function will accept an array or a list of values and also catres for 0 and less than 1000. Below is the patch file which I could not attache 2 so had to insert sorry ;).
As far as the clob conversion goes we have changed a few to varchar but because of the limitation of 4000 the is not much opportunity to do this anyway.
I have solved the problem of the IN list being over 1000 elements by writing a function to split and rewrite the query in smaller chunks. I have just added this function to wherever i have seen this error occurring. The problem i am having is wondering if there is a better way to solve this problem without having to find all the affected queries and then add the function. I am still waiting for a response to this question.
I have attached the grep for the changes i have made concerning the 1000 list and also the files that has the function to solve it. The function will accept an array or a list of values and also catres for 0 and less than 1000. Below is the patch file which I could not attache 2 so had to insert sorry ;).
diff --git a/../trunk-split/fix-1000-in-list-ora-error.patch b/../trunk-split/fix-1000-in-list-ora-error.patch
new file mode 100644
index 0000000..e69de29
diff --git a/../vendor-split/lib.php b/../trunk-split/lib.php
index d5147a1..12dec69 100755
--- a/../vendor-split/lib.php
+++ b/../trunk-split/lib.php
@@ -829,8 +845,8 @@ class assignment_base {
COALESCE(SIGN(SIGN(s.timemarked) + SIGN(s.timemarked - s.timemodified)), 0) AS status ';
$sql = 'FROM '.$CFG->prefix.'user u '.
'LEFT JOIN '.$CFG->prefix.'assignment_submissions s ON u.id = s.userid
- AND s.assignment = '.$this->assignment->id.' '.
- 'WHERE u.id IN ('.implode(',', $users).') ';
+ AND s.assignment = '.$this->assignment->id.' '.
+ "WHERE " . split_query_in_list("u.id", 300, $users); //CQU -david - function to split the query list
if ($sort = flexible_table::get_sql_sort('mod-assignment-submissions')) {
$sort = 'ORDER BY '.$sort.' ';
@@ -1154,9 +1203,9 @@ class assignment_base {
s.timemodified, s.timemarked,
COALESCE(SIGN(SIGN(s.timemarked) + SIGN(s.timemarked - s.timemodified)), 0) AS status ';
$sql = 'FROM '.$CFG->prefix.'user u '.
- 'LEFT JOIN '.$CFG->prefix.'assignment_submissions s ON u.id = s.userid
- AND s.assignment = '.$this->assignment->id.' '.
- 'WHERE '.$where.'u.id IN ('.implode(',',$users).') ';
+ 'LEFT JOIN '.$CFG->prefix.'assignment_submissions s ON u.id = s.userid
+ AND s.assignment = '.$this->assignment->id.' ' .
+ "WHERE " . $where . split_query_in_list("u.id", 300, $users); //CQU -david - function to split the query list
$table->pagesize($perpage, count($users));
@@ -2795,10 +2884,10 @@ function assignment_count_real_submissions($cm, $groupid=0) {
$userlists = implode(',', $users);
return count_records_sql("SELECT COUNT('x')
- FROM {$CFG->prefix}assignment_submissions
- WHERE assignment = $cm->instance AND
- timemodified > 0 AND
- userid IN ($userlists)");
+ FROM {$CFG->prefix}assignment_submissions
+ WHERE assignment = $cm->instance AND
+ timemodified > 0
+ AND " . split_query_in_list("userid", 300, $userlists) );
}
diff --git a/../vendor-split/report.php b/../trunk-split/report.php
index 622c10b..0292944 100755
--- a/../vendor-split/report.php
+++ b/../trunk-split/report.php
@@ -583,7 +583,7 @@ class quiz_report extends quiz_default_report {
$table->add_separator();
if ($groupstudentslist){
- $groupaveragesql = $averagesql." AND qg.userid IN ($groupstudentslist)";
+ $groupaveragesql = $averagesql." AND " . split_query_in_list("qg.userid", 300, $groupstudentslist );
$groupaverage = get_record_sql($groupaveragesql);
$groupaveragerow = array('fullname' => get_string('groupavg', 'grades'),
'sumgrades' => round($groupaverage->grade, $quiz->decimalpoints),
@@ -594,7 +594,7 @@ class quiz_report extends quiz_default_report {
}
$table->add_data_keyed($groupaveragerow);
}
- $overallaverage = get_record_sql($averagesql." AND qg.userid IN ($studentslist)");
+ $overallaverage = get_record_sql($averagesql." AND " . split_query_in_list("qg.userid", 300, $studentslist ) );
$overallaveragerow = array('fullname' => get_string('overallaverage', 'grades'),
'sumgrades' => round($overallaverage->grade, $quiz->decimalpoints),
'feedbacktext'=> quiz_report_feedback_for_grade($overallaverage->grade, $quiz->id));
diff --git a/../vendor-split/reportlib.php b/../trunk-split/reportlib.php
index 3a09a1b..737c98f 100755
--- a/../vendor-split/reportlib.php
+++ b/../trunk-split/reportlib.php
@@ -47,8 +47,8 @@ function quiz_get_average_grade_for_questions($quiz, $userids){
"{$CFG->prefix}question_states qs " .
"WHERE qns.attemptid = qa.uniqueid AND " .
"qa.quiz = {$quiz->id} AND " .
- ($qmfilter?$qmfilter.' AND ':'').
- "qa.userid IN ({$userids}) AND " .
+ ($qmfilter?$qmfilter.' AND ':'') .
+ split_query_in_list("qa.userid", 300, $userids) . " AND " .
"qs.event IN (".QUESTION_EVENTS_GRADED.") AND ".
"qns.newgraded = qs.id GROUP BY qs.question";
return get_records_sql_menu($questionavgssql);
@@ -64,7 +64,7 @@ function quiz_get_total_qas_graded_and_ungraded($quiz, $questionids, $userids){
"{$CFG->prefix}question_states qs " .
"WHERE " .
"qa.quiz = {$quiz->id} AND " .
- "qa.userid IN ({$userids}) AND " .
+ split_query_in_list("qa.userid", 300, $userids) . " AND " .
"qns.attemptid = qa.uniqueid AND " .
"qns.newgraded = qs.id AND " .
"qs.question IN ({$questionids}) " .
In reply to David Binney
Re: Databases: Moodle 1.9 and Oracle performance issues
by Amanda Doughty -
Thanks for this David. I have added your function to our code. I made a small change to include a parameter for determining whether the SQL should be 'IN' or 'NOT IN'
Very useful for taming Oracle
Very useful for taming Oracle
There are a number of patches that radically improve performance w Oracle 10 and 11, that are in the catalyst-hosted 19-ora branch.
They were a bit risky and Oracle-only so they never made it into an official release. They are being used by some large Oracle installs with no problems AFAIK. Talk to the Catalyst crowd about getting them ported to latest 1.9.x.
(Working for OLPC, I have very little excuse or interest in fixing Oracle things
).
What kills your performance is the blocking tcp roundtrip involved in *every* CLOB transfer. A switch to varchar2(4000) might work, if the Ora*Net protocol avoids that blocking tcp roundtrip. And if you have no content past the 4000 bytes mark.
But there is a bigger if: truncating content.
Take a mature Moodle installation and write a php script to do SELECT MAX(length(fieldname) from tablename -- running this for each TEXT / CLOB column.
Using HTML-formatted content, I am certain that you will see content exceeding 4000 bytes.
One alternative is to use your leverage as a license-paying customer and get Oracle to fix the bug in the Ora*Net protocol or to provide 64KB columns that are fast to store, query and transfer, like every FOSS DB has had for the last 10 years.
They were a bit risky and Oracle-only so they never made it into an official release. They are being used by some large Oracle installs with no problems AFAIK. Talk to the Catalyst crowd about getting them ported to latest 1.9.x.
(Working for OLPC, I have very little excuse or interest in fixing Oracle things
What kills your performance is the blocking tcp roundtrip involved in *every* CLOB transfer. A switch to varchar2(4000) might work, if the Ora*Net protocol avoids that blocking tcp roundtrip. And if you have no content past the 4000 bytes mark.
But there is a bigger if: truncating content.
Take a mature Moodle installation and write a php script to do SELECT MAX(length(fieldname) from tablename -- running this for each TEXT / CLOB column.
Using HTML-formatted content, I am certain that you will see content exceeding 4000 bytes.
One alternative is to use your leverage as a license-paying customer and get Oracle to fix the bug in the Ora*Net protocol or to provide 64KB columns that are fast to store, query and transfer, like every FOSS DB has had for the last 10 years.
In reply to Martín Langhoff
Re: Databases: Moodle 1.9 and Oracle performance issues
by David Binney -
Well thanks info but i have already done the whole length thing and even for the cols that can allow a shorter length it is a real hack to reduce them and you can also not be sure of the consequences of that action either ;( .
As far as the oracle support goes "hahahahha" we are paying customers and have been for years and our current outstanding issue "all oracle nodes fall over after approx 5-7 days of traffic" so sort of serious, has been outstanding for 3 weeks. As far as I am concerned the whole paid oracle thing seems like a bit of a joke which is sad to say as I was a bit of a fan.
As far as the oracle support goes "hahahahha" we are paying customers and have been for years and our current outstanding issue "all oracle nodes fall over after approx 5-7 days of traffic" so sort of serious, has been outstanding for 3 weeks. As far as I am concerned the whole paid oracle thing seems like a bit of a joke which is sad to say as I was a bit of a fan.
In reply to David Binney
Re: Databases: Moodle 1.9 and Oracle performance issues
by Martín Langhoff -
Then you might want to try out a recent version of PostgreSQL. It's very close to Oracle in concepts and administration (very friendly to Oracle refugees ), has some very nice features, scales like crazy with moodle...
In reply to Martín Langhoff
Re: Databases: Moodle 1.9 and Oracle performance issues
by James McLean -
Easier said than done, Martín
In reply to Martín Langhoff
Re: Databases: Moodle 1.9 and Oracle performance issues
by James McLean -
Martín, is it possible you could point me/us to the 19-ora branch you mentioned? I've been looking at http://git.catalyst.net.nz/gitweb?p=moodle-r2.git;a=heads but can't see it in the list. I'm sure I'm probably looking in the wrong spot (I'm a SVN/CVS user previously, GIT is kinda new to me).
We're motivated enough (and skilled enough) to port it to the latest Moodle ourselves if we need to
We're motivated enough (and skilled enough) to port it to the latest Moodle ourselves if we need to
In reply to James McLean
Re: Databases: Moodle 1.9 and Oracle performance issues
by Martín Langhoff -
Right there on that page, mdl19-dbperf . I had mis-remembered the name. Make sure you switch on the MCACHE thing, so $CFG and various other CLOBS that are read on every request are cached appropriately.
An update from me on this thread...
So far, all we've really done is move to PHP 5.2.13 + Oracle 11 client + OCI8 1.4.1, and have done away with EAccelerator in favour of APC. OCI8 1.4.1 seems stable and APC is great -- much quicker than with no accelerator of course. Doing some load testing against one apache server, I was getting about 10 pages per second delivered without APC and 24 pages per second with APC using the same test plan.
Regarding Oracle 11 with DRCP, we're about to start testing with Oracle 11, so I should have some test results soon.
We haven't got around to implementing your solution for the Oracle 'IN' 100 element limit yet David, but that should be coming up soon too.
James, did you decide to port the 'mdl19-dbperf' to a contemporary version of Moodle perchance? Also, would you mind sharing your php.ini settings for OCI8?
So far, all we've really done is move to PHP 5.2.13 + Oracle 11 client + OCI8 1.4.1, and have done away with EAccelerator in favour of APC. OCI8 1.4.1 seems stable and APC is great -- much quicker than with no accelerator of course. Doing some load testing against one apache server, I was getting about 10 pages per second delivered without APC and 24 pages per second with APC using the same test plan.
Regarding Oracle 11 with DRCP, we're about to start testing with Oracle 11, so I should have some test results soon.
We haven't got around to implementing your solution for the Oracle 'IN' 100 element limit yet David, but that should be coming up soon too.
James, did you decide to port the 'mdl19-dbperf' to a contemporary version of Moodle perchance? Also, would you mind sharing your php.ini settings for OCI8?
Not as yet unfortunately Guy, we've got our 10g performance to a point where it is performing quite well - so my priorities have been shifted onto other development tasks.
I did have a look at it in some detail and there will be a reasonable amount of work to implement the changes as suggested by Martin, but nothing too difficult. Some of the changes have already been implemented anyway.
I did have a look at it in some detail and there will be a reasonable amount of work to implement the changes as suggested by Martin, but nothing too difficult. Some of the changes have already been implemented anyway.
In reply to James McLean
Re: Databases: Moodle 1.9 and Oracle performance issues
by Esteban Sánchez -
Hello James,
First of all I would like to say sorry because my answer is not to give you a solution. The thing is that replying your thread was the unique way I had to be in touch with you.
I have migrated from moodle 1.9 to moodle 2.3 both with oracle 10g and the performance has been seriously degradated and one of the things I have decided to try is to use persisten connections to the database. Searching in forums I have read the following thread:
"https://moodle.org/mod/forum/discuss.php?d=143376"
where you says that you have configured your OCI8 to improve performance using persistent connections. If you don´t mind I would like to ask you to show me that configuration. I would appreciate it very much because I am alone in the project. The version of oci8 is 1.4.6 and it is for Oracle 11g (we have decided to install this version because we´ll update from 10g to 11g soon). The configuration I have set is:
oci8.connection_class no value
oci8.default_prefetch 100
oci8.events Off
oci8.max_persistent -1
oci8.old_oci_close_semantics
oci8.persistent_timeout -1
oci8.ping_interval 60
oci8.privileged_connect Off
oci8.statement_cache_size 100
Thanks in advance