The current AMOS data live at a server here in Liberec, running PostgreSQL. I have imported the data into MySQL database at server8.moodle.com. The performance of MySQL at server8 is significantly worse than that PostgreSQL.

AMOS data are stored in mdl_amos_repository table and there are ~3.7 millions records. I have two critical queries that must be as fast as possible. The first one is a query taking a snapshot of a given component at a given branch. Such operation is used very frequently during internal AMOS operations (commit, rebase, import, export, ...):

SELECT r.id, r.stringid, r.text, r.timemodified, r.deleted FROM mdl_amos_repository r JOIN (SELECT branch,lang,component,stringid,MAX(timemodified) AS timemodified FROM mdl_amos_repository WHERE branch=2000 AND lang='en' AND component='moodle' GROUP BY branch,lang,component,stringid) j ON (r.branch = j.branch AND r.lang = j.lang AND r.component = j.component AND r.stringid = j.stringid AND r.timemodified = j.timemodified) WHERE r.branch=2000 AND r.lang='en' AND r.component='moodle' AND deleted=0;

It takes ~1900 ms at MySQL and ~12 ms at PostgreSQL.

The second critical query is fetching data for the translation UI. An example for such a typical query is:

SELECT r.id, r.stringid, r.text, r.timemodified, r.deleted FROM mdl_amos_repository r JOIN (SELECT branch,lang,component,stringid,MAX(timemodified) AS timemodified FROM mdl_amos_repository WHERE branch IN (2000,1900) AND lang IN ('en') AND component IN ('moodle','workshop') GROUP BY branch,lang,component,stringid) j ON (r.branch = j.branch AND r.lang = j.lang AND r.component = j.component AND r.stringid = j.stringid AND r.timemodified = j.timemodified) WHERE r.branch IN (2000,1900) AND r.lang IN ('en') AND r.component IN ('moodle','workshop') ORDER BY r.component, r.stringid, r.lang, r.branch;

It takes ~20000 ms at MySQL and ~60 ms at PostgreSQL.

Both DBMS were tuned for production. PostgreSQL has 128 MB RAM for shared buffers and 64 MB RAM working memory. It is running at 4 x Intel(R) Core(TM)2 Quad @ 2.66GHz

If AMOS is expected to run at MySQL, we must do something with this performance.