-
Improvement
-
Resolution: Unresolved
-
Major
-
None
-
3.11, 4.4
-
MOODLE_311_STABLE, MOODLE_404_STABLE
-
MDL-71857-master
-
Prerequisites
- We need to test the Moodle site using four databases: PostgreSQL, MySQL (MariaDB), Oracle, and Microsoft SQL Server. The easiest way to set up Moodle with those databases is by using Moodle-docker. https://github.com/moodlehq/moodle-docker.
- Testing index creation while upgrading, we need a quick way to do it, so please download the adduniqueindex.php
, addnonuniqueindex.php and gettagindexes.php save the files to your moodle root directory.
Testing
Dbtype tabel:
Database name dbtype PostgreSQL pgsql MySQL mysql MariaDB mariadb Oracle oracle Microsoft SQL Server mssql - Use Postgresql as the database
export MOODLE_DOCKER_DB=pgsql - Clone/Create a Moodle site (but don't install it)
- Apply the patch
- Edit the config.php, and add the "concurrentnotuniqueindexing":
$CFG->dboptions = array (...'concurrentnotuniqueindexing' => true
); - Run install
- VERIFY there are no errors during the installation process.
- Navigate to Site Admin >> Server >> Tasks >> Ad hock tasks
- VERIFY you don't see "concurrent_notunique_index_task" in the Component/Class name column.
- Run http://yourmoodlesite/gettagindexes.php.
- VERIFY that there is no index "m_tag_nam_ix" and "m_tag_iss_uix".
- Run http://yourmoodlesite/adduniqueindex.php
- VERIFY that there is index "m_tag_iss_uix".
- Run http://yourmoodlesite/addnonuniqueindex.php
- Open your terminal.
- Go to the moodle docker directory
- Run the below command:
bin/moodle-docker-compose exec webserver php admin/cli/adhoc_task.php --classname="\core\task\concurrent_notunique_index_task"
- Verify that the process is run successfully without any errors.
- Back to the browser
- Navigate to Site Admin >> Server >> Tasks >> Ad hock tasks
- VERIFY there is "concurrent_notunique_index_check_task" in the Component/Class name column.
- Back to your terminal
- Run the below command:
bin/moodle-docker-compose exec webserver php admin/cli/adhoc_task.php --classname="\core\task\concurrent_notunique_index_check_task"
- Verify that the process is run successfully without any errors.
- Run http://yourmoodlesite/gettagindexes.php. Verify that now you have "m_tag_nam_ix".
- Run PHPUnit lib/ddl/tests/ddl_test.php. Verify there are no errors.
- Repeat from Step 1 to Step 25 with the remaining dbtype at the above table.
Prerequisites We need to test the Moodle site using four databases: PostgreSQL, MySQL (MariaDB), Oracle, and Microsoft SQL Server. The easiest way to set up Moodle with those databases is by using Moodle-docker. https://github.com/moodlehq/moodle-docker . Testing index creation while upgrading, we need a quick way to do it, so please download the adduniqueindex.php , addnonuniqueindex.php and gettagindexes.php save the files to your moodle root directory. Testing Dbtype tabel: Database name dbtype PostgreSQL pgsql MySQL mysql MariaDB mariadb Oracle oracle Microsoft SQL Server mssql Use Postgresql as the database export MOODLE_DOCKER_DB=pgsql Clone/Create a Moodle site (but don't install it) Apply the patch Edit the config.php, and add the "concurrentnotuniqueindexing": $CFG->dboptions = array ( ... 'concurrentnotuniqueindexing' => true ); Run install VERIFY there are no errors during the installation process. Navigate to Site Admin >> Server >> Tasks >> Ad hock tasks VERIFY you don't see " concurrent_notunique_index_task " in the Component/Class name column. Run http://yourmoodlesite/gettagindexes.php . VERIFY that there is no index "m_tag_nam_ix" and "m_tag_iss_uix". Run http://yourmoodlesite/adduniqueindex.php VERIFY that there is index "m_tag_iss_uix". Run http://yourmoodlesite/addnonuniqueindex.php Open your terminal. Go to the moodle docker directory Run the below command: bin/moodle-docker-compose exec webserver php admin/cli/adhoc_task.php --classname= "\core\task\concurrent_notunique_index_task" Verify that the process is run successfully without any errors. Back to the browser Navigate to Site Admin >> Server >> Tasks >> Ad hock tasks VERIFY there is " concurrent_notunique_index_check_task " in the Component/Class name column. Back to your terminal Run the below command: bin/moodle-docker-compose exec webserver php admin/cli/adhoc_task.php --classname= "\core\task\concurrent_notunique_index_check_task" Verify that the process is run successfully without any errors. Run http://yourmoodlesite/gettagindexes.php . Verify that now you have "m_tag_nam_ix". Run PHPUnit lib/ddl/tests/ddl_test.php. Verify there are no errors. Repeat from Step 1 to Step 25 with the remaining dbtype at the above table.
-
10
-
Team Hedgehog 2023 Sprint 3.3, Team Hedgehog 2023 Review 3, Team Hedgehog 2023 Sprint 4.1, Team Hedgehog 2023 Sprint 4.2, Team Hedgehog 2023 Sprint 4.3, Team Hedgehog 2023 Review 4, Team Hedgehog 2024 Sprint 1.1, Team Hedgehog 2024 Sprint 1.2, Team Hedgehog 2024 Sprint 2.1, Team Hedgehog 2024 Sprint 2.2, Team Hedgehog 2024 Sprint 2.3
Original Report
During upgrade steps where new indexes are added to existing tables, this can be very very time consuming, and the site is locked in upgrade mode when it is happening.
For sites that prioritize uptime, that can be undesirable, and most databases support some form of concurrent/non-locking index creation.
I propose having a site admin setting that allows the site to be configured to use concurrent index creation. If selected, new indexes would be created (with the DB correct keywords for concurrent index creation) using an adhoc task that was registered during the upgrade.
Initial findings
Concurrent indexing support varies across the various databases that Moodle LMS supports. A summary of each based on some initial research is below.
Postgres
Postgres is straightforward, it is adding the “concurrently” keyword to an indexing operation. For example: `CREATE INDEX CONCURRENTLY idx_name ON table_name(column_name);`
This allows indexes to be created without locking writes on the table, the downside is that the index creation will take longer overall. But this is “hidden” as you can continue writing to the table.
The only “annoying” thing with the postgres behaviour is it can fail if there are “too many” updates to the column the index is being added to, and this is more or less a silent fail. You can find/diagnose a failed index but it’s a bit fiddly itself.
MySQL/MariaDB
There isn’t an explicit option for MySQL. If you use the InnoDB storage engine it allows concurrent inserts, updates, and deletes during the index-building process, but with some limitations. At a high level these are:
Still get brief locking at the start and the end of the process. The start doesn’t matter, but it might at the end if the site is “up” and under load.
If the table has foreign key constraints, the operation may require a table-level exclusive lock, depending on the operation itself. This shouldn’t be much of an issue in current Moodle.
There might be some complexities with spatial indexing, which we don’t currently support in Moodle.
Oracle
Oracle is straightforward, it is adding the “online” keyword. For example: `CREATE INDEX idx_name ON table_name(column_name) ONLINE;`
I don’t have much experience with Oracle, but research says that creating indexes is slower. But again this is “hidden” as you can continue writing to the table.
MSSQL
MSSQL is straightforward, it is adding the “online = on” command. For example: `CREATE INDEX idx_name ON table_name(column_name) WITH (ONLINE = ON);`
The main issue here is this function is only available for the “Enterprise edition” of MSSQL.
Approach
Overall the code changes for this should be minimal, and can be done at the “driver” level in Moodle. Adding the option or not to the generated index statement. But I haven’t looked at the code relating to this as part of this research.
In terms of how this would be enabled; this would be implemented as a new value in the ` dboptions` array set in `config.php`. For example: $CFG->dboptions = [‘concurrentindexing’ => ‘true’]; // Default false.
