Feedback request: Compatibility Layer for Moodle

I’ve recently submitted a pull request to moodle for support for cockroach db. It’s full featured, and the only downside I can see is that it isn’t as fast as PostgreSQL. I’m uncertain if that’s due to the use of sequential indexes for moodle support or if there’s other optimizations that could be made.

In either case I welcome any sort of feedback.
The diff is here: https://github.com/moodle/moodle/compare/master...Agilicus:mdl-68145

And this is the moodle ticket:
https://tracker.moodle.org/browse/MDL-68145?jql=text%20~%20"cockroachdb"

Hi @jamiechapmanbrn,
Thanks for adding support for CockroachDB to Moodle!

It’s difficult to tell from looking at your code what might be slower. Are there specific queries that are slower on CockroachDB than on PostgreSQL? We’d be happy to help if you can give a bit more context about what you have been seeing and how that compares to your expectations.

Thanks,
Rebecca

Thanks for your work here @jamiechapmanbrn!

One setting did stand out to me – I see that you are using SET experimental_serial_normalization TO sql_sequence in order to use sequences and increment IDs one-by-one. Because CockroachDB is a distributed database, using this setting requires inter-node communication and adds more contention overhead, and ultimately lowers performance. There’s some more information in these docs.

Does Moodle require true sequences? Would it be possible to use unique_rowid() to generate IDs automatically instead, or use UUIDs?

One more thing to look out for is the queries against the pg_catalog tables. How are they performing for you? More recent versions of CockroachDB have improvements in querying those tables. Which version are you developing against?

I checked my notes, that isn’t the full install, that’s just queries that seem to have taken longer than 400ms. I can post the full log when I get home later.

I did ask about using non-sequential ids in the moodle forums because I’m not clear on the the ramifications for moodle. It’s something I may poke at if I have time.

There’s two primary ways I’m seeing performance issues: The install takes about 5 times as long as PostgreSQL on a single node, and page loads/browsing seem significantly slower, perhaps by about half. For development I’m using the official docker container with the storage directory mounted to localhost on an ssd. During moodle install I’m seeing cockroachdb saturate a cpu core. The performance discrepancy doesn’t seem worse moving to a production setup with replication and regionally distributed nodes.

The most recent code was improved for v20.1.2, befitting from the temp tables and nested session support, We were using slightly different code for v19 for some time before that.

When I was looking at profiling the performance semi-recently I found it difficult to check what was actually performing poorly as the built the built-in profiler was showing query latency, which meant the high latency calls were just the last lines in a long transaction. See the below slow query log as an example.

Summary
[n1,client=172.23.0.4:46902,hostnossl,user=root] 3 724.220ms exec "" {} "CREATE TABLE m_config_plugins (id INT8 NOT NULL DEFAULT nextval('m_config_plugins_id_seq'), plugin VARCHAR(100) NOT NULL DEFAULT 'core', name VARCHAR(100) NOT NULL DEFAULT '', value STRING NOT NULL, CONSTRAINT m_confplug_id_pk PRIMARY KEY (id))" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 6 1402.010ms exec "" {} "CREATE TABLE m_config_log (id INT8 NOT NULL DEFAULT nextval('m_config_log_id_seq'), userid INT8 NOT NULL, timemodified INT8 NOT NULL, plugin VARCHAR(100), name VARCHAR(100) NOT NULL DEFAULT '', value STRING, oldvalue STRING, CONSTRAINT m_conflog_id_pk PRIMARY KEY (id))" {} 0 "" 0

719.8059999999998 [n1,client=172.23.0.4:46902,hostnossl,user=root] 9 2303.044ms exec "" {} "CREATE INDEX m_conflog_use_ix ON m_config_log (userid)" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 10 2788.890ms exec "" {} "CREATE TABLE m_upgrade_log (id INT8 NOT NULL DEFAULT nextval('m_upgrade_log_id_seq'), type INT8 NOT NULL, plugin VARCHAR(100), version VARCHAR(100), targetversion VARCHAR(100), info VARCHAR(255) NOT NULL DEFAULT '', details STRING, backtrace STRING, userid INT8 NOT NULL, timemodified INT8 NOT NULL, CONSTRAINT m_upgrlog_id_pk PRIMARY KEY (id))" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 13 3368.772ms exec "" {} "CREATE INDEX m_upgrlog_typtim_ix ON m_upgrade_log (type, timemodified)" {} 0 "" 0

764.0360000000001 [n1,client=172.23.0.4:46902,hostnossl,user=root] 14 4132.808ms exec "" {} "CREATE INDEX m_upgrlog_use_ix ON m_upgrade_log (userid)" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 15 4612.693ms exec "" {} "CREATE TABLE m_course (id INT8 NOT NULL DEFAULT nextval('m_course_id_seq'), category INT8 NOT NULL DEFAULT 0, sortorder INT8 NOT NULL DEFAULT 0, fullname VARCHAR(254) NOT NULL DEFAULT '', shortname VARCHAR(255) NOT NULL DEFAULT '', idnumber VARCHAR(100) NOT NULL DEFAULT '', summary STRING, summaryformat INT2 NOT NULL DEFAULT 0, format VARCHAR(21) NOT NULL DEFAULT 'topics', showgrades INT2 NOT NULL DEFAULT 1, newsitems INT8 NOT NULL DEFAULT 1, startdate INT8 NOT NULL DEFAULT 0, enddate INT8 NOT NULL DEFAULT 0, relativedatesmode INT2 NOT NULL DEFAULT 0, marker INT8 NOT NULL DEFAULT 0, maxbytes INT8 NOT NULL DEFAULT 0, legacyfiles INT2 NOT NULL DEFAULT 0, showreports INT2 NOT NULL DEFAULT 0, visible INT2 NOT NULL DEFAULT 1, visibleold INT2 NOT NULL DEFAULT 1, groupmode INT2 NOT NULL DEFAULT 0, groupmodeforce INT2 NOT NULL DEFAULT 0, defaultgroupingid INT8 NOT NULL DEFAULT 0, lang VARCHAR(30) NOT NULL DEFAULT '', calendartype VARCHAR(30) NOT NULL DEFAULT '', theme VARCHAR(50) NOT NULL DEFAULT '', timecreated INT8 NOT NULL DEFAULT 0, timemodified INT8 NOT NULL DEFAULT 0, requested INT2 NOT NULL DEFAULT 0, enablecompletion INT2 NOT NULL DEFAULT 0, completionnotify INT2 NOT NULL DEFAULT 0, cacherev INT8 NOT NULL DEFAULT 0, CONSTRAINT m_cour_id_pk PRIMARY KEY (id))" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 19 5657.076ms exec "" {} "CREATE INDEX m_cour_sho_ix ON m_course (shortname)" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 20 6112.185ms exec "" {} "CREATE INDEX m_cour_sor_ix ON m_course (sortorder)" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 21 6596.054ms exec "" {} "CREATE TABLE m_course_categories (id INT8 NOT NULL DEFAULT nextval('m_course_categories_id_seq'), name VARCHAR(255) NOT NULL DEFAULT '', idnumber VARCHAR(100), description STRING, descriptionformat INT2 NOT NULL DEFAULT 0, parent INT8 NOT NULL DEFAULT 0, sortorder INT8 NOT NULL DEFAULT 0, coursecount INT8 NOT NULL DEFAULT 0, visible INT2 NOT NULL DEFAULT 1, visibleold INT2 NOT NULL DEFAULT 1, timemodified INT8 NOT NULL DEFAULT 0, depth INT8 NOT NULL DEFAULT 0, path VARCHAR(255) NOT NULL DEFAULT '', theme VARCHAR(50), CONSTRAINT m_courcate_id_pk PRIMARY KEY (id))" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 24 7257.425ms exec "" {} "CREATE TABLE m_course_completion_aggr_methd (id INT8 NOT NULL DEFAULT nextval('m_course_completion_aggr_methd_id_seq'), course INT8 NOT NULL DEFAULT 0, criteriatype INT8, method INT2 NOT NULL DEFAULT 0, value DECIMAL(10,5), CONSTRAINT m_courcompaggrmeth_id_pk PRIMARY KEY (id))" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 27 7926.179ms exec "" {} "CREATE INDEX m_courcompaggrmeth_cri_ix ON m_course_completion_aggr_methd (criteriatype)" {} 0 "" 0

[n1,client=172.23.0.4:46902,hostnossl,user=root] 28 8392.711ms exec "" {} "CREATE UNIQUE INDEX m_courcompaggrmeth_coucri_uix ON m_course_completion_aggr_methd (course, criteriatype)" {} 0 "" 0

I attempted to do some performance troubleshooting for the install by estimating the time each line took by the difference between the last one, and resetting if it didn’t go up and got something like this:
image

Unfortunately I don’t have the histograms by query type, but here is the link to the full install slow-running queries (>100ms) log. Unfortunately there’s a lot of calls and very little stands out as low hanging fruit.

When you mentioned that the install is 5 times slower, are you referring to installing Moodle with a CockroachDB backend? I would guess that the reason for that is because this process would involve many schema changes (i.e. CREATE or ALTER statements). Is that accurate? We have a tracking issue here that is about speeding up slow schema changes: https://github.com/cockroachdb/cockroach/issues/47790

I think it’s pretty likely that the page load/browsing slowness could be explained by the serial_normalization setting that I mentioned above.

Finally, you might be able to get better query statistics if you are able to look at the Statements page on the CockroachDB AdminUI.