I have a couple of questions regarding this subject:
1.I have transformed all my tables to use the InnoDB storage engine. The mysqltuner reports everything fine with a InnoDB buffer size slightly bigger than needed which was done deliberatly. However the moodle script in "/admin/dbperformance.php" shows "InnoDB cache hit rate 0" and everything is working well apparently. What can be wrong?
2.Is it really worth it to change to InnoDB? our moodle has about 250 courses and 1500 users with a lot of entries: from 1000 to 2000 every day. If not, is it better to switch back to MyISAM?
Thanks in advance,
Rui Ruas
Hi Rui,
You may want to see if you actually have the cache enabled within mysql.
See: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
There are several config varibables that you will want to get right... they generally begin with 'query_cache_'
Hope this helps...
ns
You may want to see if you actually have the cache enabled within mysql.
See: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
There are several config varibables that you will want to get right... they generally begin with 'query_cache_'
| query_cache_limit | Yes | Yes | Yes | Global | Yes |
| query_cache_min_res_unit | Yes | Yes | Yes | Global | Yes |
| query_cache_size | Yes | Yes | Yes | Global | Yes |
| query_cache_type | Yes | Yes | Yes | Both | Yes |
| query_cache_wlock_invalidate | Yes | Yes | Yes | Both | Yes |
Hope this helps...
ns
Hi,
I solved the mystery of the cache status not showing: it's the moodle user privileges in mysql. So, everything is fine with my DB. Thanks anyway.
My second question remains: is it worth changing to InnoDB?
I solved the mystery of the cache status not showing: it's the moodle user privileges in mysql. So, everything is fine with my DB. Thanks anyway.
My second question remains: is it worth changing to InnoDB?
I would say yes. Some reasons:
1. InnoDB uses row-level locking, rather than table-level locking, which gives you much better concurrency when you have a lot of simultaneous users.
2. MyISAM is much more likely to corrupt tables and require you to run repair tables.
3. Looking to the future, InnoDB tables support transactions, which we may start to use in Moodle 2.0 and beyond.
1. InnoDB uses row-level locking, rather than table-level locking, which gives you much better concurrency when you have a lot of simultaneous users.
2. MyISAM is much more likely to corrupt tables and require you to run repair tables.
3. Looking to the future, InnoDB tables support transactions, which we may start to use in Moodle 2.0 and beyond.
ok, thanks. I'll keep it as it is! best regards,