As you may know, MySQL allows different tables to be operated by different engines. The default that we all use is MyISAM. I wanted to try out the newer InnoDB type as it's supposed to be better for performance on large/busy tables.
Changing the table type is easy, you can do it in phpmyadmin, or use this quick script I knocked up in cvs:/moodle/admin/innodb.php.
After testing a few other sites, I ran that script on moodle.org on Tuesday night. It took about 30 minutes to complete (this site has a lot of data!) and it just kept working. So far so good.
Then moodle.org started really slowing down, and the CPU load went through the roof (this is why moodle.org was so slow yesterday). When I realised this (unfortunately after sleeping all night and then being out all day) I did some tweaking of InnoDB parameters to basically allow a lot more memory and everything stabilised again.
After watching it for a day or so since then, some results:
GOOD: Yes, I think it is a little faster than the MyISAM version.
BAD: The server is under a constantly higher load, and more memory is being consumed. Also, all the data is stored in a big blob file of about 1.4Gb, which makes me nervous.
At the momennt I feel I'll have to migrate everything back soon.
Anyone else played with InnoDB yet? Should I stick with it and experiment more with tuning?
Interesting that you find InnoDB faster than MyISAM; I figured the results would be the opposite.
It makes sense to store all the tables in one file when many of your queries are spanning multiple tables with joins (only one file to open). Conversely, without foreign keys in MyISAM, a file per table makes sense for the simple queries that are often written to it.
FYI, it looks like MySQL has introduced a conf setting to allow every individual table its own file. In my.cnf add:
[mysqld]
innodb_file_per_table
It makes sense to store all the tables in one file when many of your queries are spanning multiple tables with joins (only one file to open). Conversely, without foreign keys in MyISAM, a file per table makes sense for the simple queries that are often written to it.
FYI, it looks like MySQL has introduced a conf setting to allow every individual table its own file. In my.cnf add:
[mysqld]
innodb_file_per_table
It's all much more complicated than you might at first think. I know I have a bad habit of recommending books but...
High Performance MySQL
Zawodny & Balling
O'Reilly
covers the table types in quite a bit of detail. The crux of the matter is actually the locking algorithms employed by the different engines and what the ratio of reads to writes is like. It's an interesting read.
High Performance MySQL
Zawodny & Balling
O'Reilly
covers the table types in quite a bit of detail. The crux of the matter is actually the locking algorithms employed by the different engines and what the ratio of reads to writes is like. It's an interesting read.
I've just reverted back to MyISAM (it was much quicker going this way!)
It is slightly slower but the overall load on this machine is much better.
It is slightly slower but the overall load on this machine is much better.
I think this illustrates both the benefits and drawbacks of MySQL.
Drawbacks
A limited subset of what is expected from a fully featured database system (e.g. transaction processing, full sql etc etc)
Benefits
Performance and the ability to use an alternative database system, e.g. MyISAM or InnoDB, and thus the ability to get some of those features if you want such as transaction processing.
But software is more than technology, arguably the number of humans messing with the technology is as important.At the moment Innodb is a minority sport. My only and very limited experience with Innodb was when my Win Moodle installation with almost no courses occupied 500Mb of my disk. Now there may be some very good reason for this, but as Joe sixpack my response was "can I have MyIsam please"
This inevitable brings up the traditional database religious question "why not postgres", any views Martin?
Drawbacks
A limited subset of what is expected from a fully featured database system (e.g. transaction processing, full sql etc etc)
Benefits
Performance and the ability to use an alternative database system, e.g. MyISAM or InnoDB, and thus the ability to get some of those features if you want such as transaction processing.
But software is more than technology, arguably the number of humans messing with the technology is as important.At the moment Innodb is a minority sport. My only and very limited experience with Innodb was when my Win Moodle installation with almost no courses occupied 500Mb of my disk. Now there may be some very good reason for this, but as Joe sixpack my response was "can I have MyIsam please"
This inevitable brings up the traditional database religious question "why not postgres", any views Martin?
As I think I have said before I have considerable real-life experience with huge enterprise level MySql systems. To me Moodle's use of mysql isn't really what databases where designed for anyway. To say that MySql doesn't support a full set of database features is irrelevant in the Moodle context as Moodle doesn't use all of the features or power that Mysql does have. Let's face it, the Moodle database isn't a properly optimized relational database anyway, so many performance issues lie nearer to home than the choice of database.
I would say *why* postgres. Again in the real world everybody uses Apache-PHP-MySql - it's the best supported and understood platform.
I would say *why* postgres. Again in the real world everybody uses Apache-PHP-MySql - it's the best supported and understood platform.
Part of the reason Moodle's use is quite simple is because anytime I tried anything too complex then MySQL performance would suffer. It also keeps the PHP coding very easy to understand.
What sort of "proper optimizing" did you have in mind?
What sort of "proper optimizing" did you have in mind?
I'm sure we have had this discussion before and I certainly don't want to be a pain in the butt (well no more than usual anyway). However, I think it's unfortunate that the Moodle database does not conform to conventional relational database rules. It is often necessary to do multiple selects in the PHP rather than doing one 'complex' joined select. I have consistently found that MySQL is much better at doing selects than one can do in the code by using multiple selects. However, it's easy for me to talk as I have a lot of db experience and I guess it's probably is easier to understand without lots of fancy sql.
The other common place for inefficiency is with table indexes. The thing most people don't realise is that MySQL will only ever use one index per select, so if you define two table indexes and your WHERE clause specifies them both then it will only use one. It tries to 'guess' the best one to use. So careful thought about indexing can make very big performance differences. Come to think of it I've never looked at this in Moodle.
Table locking is another thing - myISAM and innoDB have completely different alogorithms. myISAM is very old fashioned in that it locks the whole table. The result is that you get good performance from myISAM where the ratio of reads to writes is either higher than 90% or less than 10%. InnoDB uses MVCC locking which is basically version controlled row locking - the same thing that Oracle uses. InnoDB also famously has transactions - these are a hell of hit on memory and cpu time. I take it you asked not to have transactions for the Moodle tables?
There are a number of other performance enhancing strategies. A good place to start is to switch on slow query logging and gradually winding back the slow query time. This finds the (surprise!) slowest queries in a system so identifying the ones to expend some effort on.
The other common place for inefficiency is with table indexes. The thing most people don't realise is that MySQL will only ever use one index per select, so if you define two table indexes and your WHERE clause specifies them both then it will only use one. It tries to 'guess' the best one to use. So careful thought about indexing can make very big performance differences. Come to think of it I've never looked at this in Moodle.
Table locking is another thing - myISAM and innoDB have completely different alogorithms. myISAM is very old fashioned in that it locks the whole table. The result is that you get good performance from myISAM where the ratio of reads to writes is either higher than 90% or less than 10%. InnoDB uses MVCC locking which is basically version controlled row locking - the same thing that Oracle uses. InnoDB also famously has transactions - these are a hell of hit on memory and cpu time. I take it you asked not to have transactions for the Moodle tables?
There are a number of other performance enhancing strategies. A good place to start is to switch on slow query logging and gradually winding back the slow query time. This finds the (surprise!) slowest queries in a system so identifying the ones to expend some effort on.
> However, I think it's unfortunate that the Moodle database does not
> conform to conventional relational database rules.
Where? This is what puzzles me about what you're saying. An example of what you mean would be good. I'm not being defensive, I would really like you to back up what you're saying so I can learn from your "considerable" experience and improve things.
With a few exceptions where lists-within-a-variable proved more efficient, I think in general the tables are all quite well normalized to 3NF.
Mostly there are multiple indexes defined, and there are many many SQL statements that use JOINS on three or more tables. Most of the indexes use multiple columns and the order of them from lefttoright has been laid out purposefully to help the widest range of queries.
> conform to conventional relational database rules.
Where? This is what puzzles me about what you're saying. An example of what you mean would be good. I'm not being defensive, I would really like you to back up what you're saying so I can learn from your "considerable" experience and improve things.
With a few exceptions where lists-within-a-variable proved more efficient, I think in general the tables are all quite well normalized to 3NF.
Mostly there are multiple indexes defined, and there are many many SQL statements that use JOINS on three or more tables. Most of the indexes use multiple columns and the order of them from lefttoright has been laid out purposefully to help the widest range of queries.
> I can learn from your experience and improve things.
Hey, you're in trouble now
I tell my partner never to listen to me and it's the best advice I give anyone. Ok then, bear with me - I'll see if I can sort out some examples....
Hey, you're in trouble now
Ok a couple of quick things.... (excuse me screwing up the detail if I have, but I think it is illustrative)
table course_sections has a field called sequence - this is a comma separated list of of the modules therein. This notionally links, I assume, to the course_modules table - except it doesn't because MySql doesn't understand the comma separated list, so one cannot write a join here.
Next, the course_modules table is linked nicely to the modules table, so no problem here a join is perfectly possible. However the link from the module table to the actual data about the table is broken as the module table contains the name of the module type. Therefore even programmatically it is necessary to choose the name of the table to search based on the content on the module table row.
So, if you wanted, for arguments sake, to write a query to list the names of non-empty forums in a particular course section - you can't. I don't know why you would want to, but if everything was properly 'relational' then you could.
Now, I understand that I am being pedantic and that with the relatively small tables we are dealing with this is probably splitting hairs. It would be seriously difficult to change (fix?) now anyway. If I have a moan it's that I actually think that a conventional and well laid out database is simpler and less error prone even if it's structure is at first sight more complex - if you see what I mean. Just, as ever, my $00.02.
table course_sections has a field called sequence - this is a comma separated list of of the modules therein. This notionally links, I assume, to the course_modules table - except it doesn't because MySql doesn't understand the comma separated list, so one cannot write a join here.
Next, the course_modules table is linked nicely to the modules table, so no problem here a join is perfectly possible. However the link from the module table to the actual data about the table is broken as the module table contains the name of the module type. Therefore even programmatically it is necessary to choose the name of the table to search based on the content on the module table row.
So, if you wanted, for arguments sake, to write a query to list the names of non-empty forums in a particular course section - you can't. I don't know why you would want to, but if everything was properly 'relational' then you could.
Now, I understand that I am being pedantic and that with the relatively small tables we are dealing with this is probably splitting hairs. It would be seriously difficult to change (fix?) now anyway. If I have a moan it's that I actually think that a conventional and well laid out database is simpler and less error prone even if it's structure is at first sight more complex - if you see what I mean. Just, as ever, my $00.02.
Yes, the "sequence" field is the main exception I was referring to (there's another in the quiz tables). I wanted to avoid having continually renumbering a sequencing field to keep track of the order of activities (I love PHP's explode/implode functions, they're terrifically convenient and makes the PHP code for this job very short).
You can still actually write a join because the course_modules table contains a section field, which links back to the "course_sections" field, but you're right this is not well normalised (two tables referring to each other!)
About the table names matching the module name, well, this is pretty much necessary to make the modules work easily ... I suppose there could have been a generic table of data somehow but that would mean all the data for each forum/workshop/glossary would have to be spread out over a number of rows ... to me this sounds messy. Alternatively there could have been a standard shared table containing just core information like id, moduleid, name, and then everything else in the module referring to that, but it would mean doing JOINs all the time rather than just sometimes.
Anyhow, yes, seriously difficult to change at this point, but good to keep in mind for the next LMS I write.
You can still actually write a join because the course_modules table contains a section field, which links back to the "course_sections" field, but you're right this is not well normalised (two tables referring to each other!)
About the table names matching the module name, well, this is pretty much necessary to make the modules work easily ... I suppose there could have been a generic table of data somehow but that would mean all the data for each forum/workshop/glossary would have to be spread out over a number of rows ... to me this sounds messy. Alternatively there could have been a standard shared table containing just core information like id, moduleid, name, and then everything else in the module referring to that, but it would mean doing JOINs all the time rather than just sometimes.
Anyhow, yes, seriously difficult to change at this point, but good to keep in mind for the next LMS I write.
Let's start a rumour....
"Martin's leaving Moodle to start a new LMS project..."
"Martin's leaving Moodle to start a new LMS project..."
This is an interesting thread. To keep it going: any thoughts on transactions? If transactions were implemented using ADODB would it just ignore the transaction for MyISAM tables?
Also, are foreign keys created during installation w/ a foreign-key-capable database?
Also, are foreign keys created during installation w/ a foreign-key-capable database?
To my understanding is MySQL will accept transaction statements and just ignore them if it is using the MyISAM driver(even using direct mysql functions rather than thro ADODB. I also believe that MySQL will accept foreign key statements in create database statemetns and again will ignore them if the driver doesn't use them. I think it might be a good idea to include them in the scripts even if just for documentation statements.
With reference to fields with multiple values, it appears to break the fundamental relational database concept of each field representing a single (atomic value). It's not what I would do and its not what the professors would do, but then neither I nor the professors have built the worlds finest Virtual Learning Environment (free or commercial).
With reference to the PostGres/MySQL issue and the relative simplicity of the sql in Moodle. I believe one may have followed on from the other. Thus I suspect Martin may have coded within the limitations of MySQL whereas if he was Postgres head he would have taken advantage of some of those features. One of the benefits of the limitations of MySQL is that porting to alternative databases can often be easier simply because of this.
With reference to fields with multiple values, it appears to break the fundamental relational database concept of each field representing a single (atomic value). It's not what I would do and its not what the professors would do, but then neither I nor the professors have built the worlds finest Virtual Learning Environment (free or commercial).
With reference to the PostGres/MySQL issue and the relative simplicity of the sql in Moodle. I believe one may have followed on from the other. Thus I suspect Martin may have coded within the limitations of MySQL whereas if he was Postgres head he would have taken advantage of some of those features. One of the benefits of the limitations of MySQL is that porting to alternative databases can often be easier simply because of this.
MyISAM does not support transactions. InnoDB does, but they are total overkill for Moodle, never mind the hit on the server that they cause.
MySQL is a 'proper' relational database that supports the *concept* of foreign keys. A foreign key is just a value that links you to a primary key in another table that is used to do joins - you don't actually specify them, you just do it in the WHERE part of the select. I think you may be thinking of things like Access where you can draw a pretty picture of table relationships (specifying foreign keys) and the system enforces referential integrity for you.
Re. postres/mysql, and this is just my opinion, I think that Apache-Mysql-PHP (AMP) is such an industry standard (which runs on *all* common platforms) that I am unconvinced that we should support anything else... but yes, I know that there's a flame war in there somewhere
MySQL is a 'proper' relational database that supports the *concept* of foreign keys. A foreign key is just a value that links you to a primary key in another table that is used to do joins - you don't actually specify them, you just do it in the WHERE part of the select. I think you may be thinking of things like Access where you can draw a pretty picture of table relationships (specifying foreign keys) and the system enforces referential integrity for you.
Re. postres/mysql, and this is just my opinion, I think that Apache-Mysql-PHP (AMP) is such an industry standard (which runs on *all* common platforms) that I am unconvinced that we should support anything else... but yes, I know that there's a flame war in there somewhere
Sorry I'm nearly a year late on this but InnoDB can be configured to use individual files per table using the option "innodb_file_per_table"(1).
Jose
(1) From http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html
Jose
(1) From http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html
Jose good point. Just wondering if there is any large moodle sites that are using mixed storage engines (both InnoDB and MyISAM)? On the page of http://docs.moodle.org/en/Performance#MySQL_performance it mentioned "If you must use InnoDB, you'll have to convert all of Moodle's tables." Does anyone know if this is 100% true and if so, what is this statement based on? Thanks!
The operative word in the performance docs is "all" - it was based on the code in the innodb.php script which does exactly that and converts *all* the tables to InnoDB. A couple of points about the innodb_file_per_table option:
1. It affects only table creation - so you need to use it when upgrading or creating new moodle tables. So which tables would be best for converting to InnoDB? Certainly those with high numbers of updates and a relatively large number of records, e.g. mdl_log and mdl_cache_text.
2. There appears to be no evidence of this option improving read/write performance in terms reduction in table fragmentation (see http://forums.mysql.com/read.php?22,180491,180645#msg-180645).
It may be prudent to amend the performance docs to reflect this. Agree?
Ken
1. It affects only table creation - so you need to use it when upgrading or creating new moodle tables. So which tables would be best for converting to InnoDB? Certainly those with high numbers of updates and a relatively large number of records, e.g. mdl_log and mdl_cache_text.
2. There appears to be no evidence of this option improving read/write performance in terms reduction in table fragmentation (see http://forums.mysql.com/read.php?22,180491,180645#msg-180645).
It may be prudent to amend the performance docs to reflect this. Agree?
Ken
Ken I agree, will you amend these to the performance docs to reflect this discussion? Thanks!
BTW I'm hoping to address some of these issues (MyISAM vs Innodb benchmarking) with the Benchmark-Framework for Moodle in the upcoming GSOC project with Visvanath Ratnaweera.
BTW I'm hoping to address some of these issues (MyISAM vs Innodb benchmarking) with the Benchmark-Framework for Moodle in the upcoming GSOC project with Visvanath Ratnaweera.
Wen
1. Done. I've updated the performance docs - hope it's OK.
2. Ultra cool
. I think everyone who is interested in Moodle performance is looking forward to the work you and Visvanath are doing. Good luck! - if you need extra help, just let me know as I'm happy to provide it!
Ken
1. Done. I've updated the performance docs - hope it's OK.
2. Ultra cool
. I think everyone who is interested in Moodle performance is looking forward to the work you and Visvanath are doing. Good luck! - if you need extra help, just let me know as I'm happy to provide it!Ken