Thanks for your advice. The performance is much better now after we use a faster disk.
However, when we perform a load test which runs for an hour having 300 users in each of the 5 Moodle courses, i.e. 1500 users in total performing different tasks, e.g. reading notes, submitting assignment, quiz and discussion, the following problems are found repeatedly and frequently when the number of users running in the load test system is over 1000.
However, there is no problem at all when we use a pure MySQL single database instance for the load test with the same setting.
1) Galera cluster running in 1 data node
a) Default exception handler: Error writing to database Debug: Lock wait timeout exceeded; try restarting transaction\n UPDATE mdl_forum_discussions SET usermodified = ? WHERE id = ?\n[array (\n 0 => '73063',\n 1 => 36937,\n)]\nError code: dmlwriteexception\n* line 429 of /lib/dml/moodle_database.php: dml_write_exception thrown\n* line 1285 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()\n* line 1565 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->set_field_select()\n* line 4335 of /mod/forum/lib.php: call to moodle_database->set_field()\n* line 691 of /mod/forum/post.php: call to forum_add_new_post()\n, referer: http://xxx/mod/forum/post.php?reply=82918
Is it because the same table row is being updated/read concurrently by many users? This lock wait problem may become worser in Galera cluster.
2) Galera cluster running in 3 data nodes
a) "Duplicate entry 'j0709vo1d6h01kcuvo4m7bf706' for key 'mdl_sess_sid_uix'"\n ["sql"]=>\n string(116) "INSERT INTO mdl_sessions (state,sid,sessdata, userid,timemodified,timecreated,lastip,firstip) VALUES(?,?,?,?,?,?,?,?)
b) Default exception handler: Error writing to database Debug: Deadlock found when trying to get lock; try restarting transaction\n UPDATE mdl_forum_discussions SET timemodified = ? WHERE id = ?\n[array (\n 0 => 1387254354,\n 1 => 36920,\n)]\nError code: dmlwriteexception\n* line 429 of /lib/dml/moodle_database.php: dml_write_exception thrown \n* line 1285 of /lib/dml/mysqli_native_moodle_database.php
Is it due to the optimistic locking of the Galera cluster as mentioned in http://www.severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads?
May i have your advice?
Thank you very much.