Tuning MySQL for Sakai
This is a work in progress. Expect to find additional information added here as we continue with the tunning. Change 1 - 4 are currently on our production server.
DOCUMENTATION: Please add to install doc
1. Make sure you tune MySQL itself. As shipped, it is configured for a small desktop system. Various caches are disabled. In mysql/support-files you will find sample configuration files. I recommend copying my-large.cnf or my-huge.cnf to /etc/my.cnf. Use the one most appropriate for the amount of memory you have available. When you restart mysql it will read /etc/my.cnf.
2. Add to sakai.properties the following line:
This allows certain expensive transactions to be cached. The developer intended this to be the default, but it was inadvertently overridden in the default XML file.
3. A few indices should be added:
create index SAKAI_EVENT_SESSION_ID_I on SAKAI_EVENT (SESSION_ID)
create index SAKAI_SITE_USER_USER_ID_I on SAKAI_SITE_USER (USER_ID)
The second is particularly important. It is used by a query that happens for every web page generation.
BUG FIX, I guess.
4. Some SQL needs to be rewritten. This is done in the java code. See the new version of DbRealmService.java.
WARNING: This code may be inappropriate for Oracle. It may not even work with Oracle.
The SQL changes are needed for two reasons: (1) MySQL often is not able to use indices when processing a query with sub-selects. I have had to rewrite them using joins. This is always possible, and generally leads to better code. (2) MySQL does not use an index when doing a query with "where x in (LIST)", whether the list is generated by a subselect or simply a list of constants. Above 3 or 4 elements in the list, it decides not to use an index. Where you need to use one, you have to add a "force index" phrase.
DOCUMENTATION: Install doc
5. Sakai uses prepared statements, but doesn't enable the JDBC feature that causes them actually to work. You'd think doing so would save CPU time on the server, maybe lots of CPU time under heavy load. To do this, you need to change the jdbc URL in sakai.properties. Here's the fixed version:
Note that we're not running with this change in our production server, though I have used it on a development server.
6. We have some concern about performance of the table SAKAI_EVENT. There's a lot of both writing and reading to it. myisam is not a wonderful format in that case. If it's a problem, may need to set delayed write, or even use a different table format.
DOCUMENTATION: Install doc
7. You may need to increase the following variables in /etc/my.cnf:
max_connections. Seems to default to 100. That seems to be OK for one Tomcat, but might not be enough for lots. Use netstat to see how many connections are open and give yourself some safety.
table_cache. Set to 256 in the sample we used. One source suggested that it should be max_connections * number of tables used in a join. So if you increase max_connections you may need to increase this.n