Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-71857

Add option to create new indexes for existing tables in an online fashion

    • MOODLE_311_STABLE, MOODLE_404_STABLE
    • MDL-71857-master
    • Hide

      Prerequisites

      1. 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.
      2. 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

       

      1. Use Postgresql as the database

        export MOODLE_DOCKER_DB=pgsql

      2. Clone/Create a Moodle site (but don't install it)
      3. Apply the patch
      4. Edit the config.php, and add the "concurrentnotuniqueindexing":

        $CFG->dboptions = array (
        ...
        'concurrentnotuniqueindexing' => true
        );

      5. Run install
      6. VERIFY there are no errors during the installation process.
      7. Navigate to Site Admin >> Server >> Tasks >> Ad hock tasks
      8. VERIFY you don't see "concurrent_notunique_index_task" in the Component/Class name column.
      9. Run http://yourmoodlesite/gettagindexes.php.
      10. VERIFY that there is no index "m_tag_nam_ix" and "m_tag_iss_uix".
      11. Run http://yourmoodlesite/adduniqueindex.php
      12. VERIFY that there is index "m_tag_iss_uix".
      13. Run http://yourmoodlesite/addnonuniqueindex.php
      14. Open your terminal.
      15. Go to the moodle docker directory
      16. Run the below command:

        bin/moodle-docker-compose exec webserver php admin/cli/adhoc_task.php --classname="\core\task\concurrent_notunique_index_task"

      17. Verify that the process is run successfully without any errors.
      18. Back to the browser
      19. Navigate to Site Admin >> Server >> Tasks >> Ad hock tasks
      20. VERIFY there is "concurrent_notunique_index_check_task" in the Component/Class name column.
      21. Back to your terminal
      22. Run the below command:

        bin/moodle-docker-compose exec webserver php admin/cli/adhoc_task.php --classname="\core\task\concurrent_notunique_index_check_task"

      23. Verify that the process is run successfully without any errors.
      24. Run http://yourmoodlesite/gettagindexes.php. Verify that now you have "m_tag_nam_ix".
      25. Run PHPUnit lib/ddl/tests/ddl_test.php. Verify there are no errors.
      26. Repeat from Step 1 to Step 25 with the remaining dbtype at the above table.
      Show
      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

      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.

        1. addnonuniqueindex.php
          0.7 kB
        2. adduniqueindex.php
          0.5 kB
        3. gettagindexes.php
          0.2 kB
        4. MDL-71857_Test1_step14.png
          MDL-71857_Test1_step14.png
          47 kB
        5. MDL-71857_Test1_step15.png
          MDL-71857_Test1_step15.png
          52 kB
        6. MDL-71857_Test1_step16.png
          MDL-71857_Test1_step16.png
          40 kB
        7. MDL-71857_Test1_step8.png
          MDL-71857_Test1_step8.png
          63 kB
        8. MDL-71857_Test1_step9.png
          MDL-71857_Test1_step9.png
          41 kB
        9. MDL-71857_Test2_step14.png
          MDL-71857_Test2_step14.png
          49 kB
        10. MDL-71857_Test2_step15.png
          MDL-71857_Test2_step15.png
          47 kB
        11. MDL-71857_Test2_step16.png
          MDL-71857_Test2_step16.png
          38 kB
        12. MDL-71857_Test2_step8.png
          MDL-71857_Test2_step8.png
          56 kB
        13. MDL-71857_Test2_step9.png
          MDL-71857_Test2_step9.png
          41 kB
        14. MDL-71857_Test3_step14.png
          MDL-71857_Test3_step14.png
          52 kB
        15. MDL-71857_Test3_step15.png
          MDL-71857_Test3_step15.png
          53 kB
        16. MDL-71857_Test3_step16.png
          MDL-71857_Test3_step16.png
          39 kB
        17. MDL-71857_Test3_step8.png
          MDL-71857_Test3_step8.png
          57 kB
        18. MDL-71857_Test3_step9.png
          MDL-71857_Test3_step9.png
          43 kB
        19. MDL-71857_Test4_step14.png
          MDL-71857_Test4_step14.png
          52 kB
        20. MDL-71857_Test4_step15.png
          MDL-71857_Test4_step15.png
          46 kB
        21. MDL-71857_Test4_step16.png
          MDL-71857_Test4_step16.png
          38 kB
        22. MDL-71857_Test4_step8.png
          MDL-71857_Test4_step8.png
          62 kB
        23. MDL-71857_Test4_step9.png
          MDL-71857_Test4_step9.png
          40 kB
        24. mssql error.png
          mssql error.png
          127 kB
        25. oracle error.png
          oracle error.png
          138 kB

            meirza.arson@moodle.com Meirza
            emerrill Eric Merrill
            Raquel Ortega Raquel Ortega
            Ilya Tregubov Ilya Tregubov
            Votes:
            6 Vote for this issue
            Watchers:
            18 Start watching this issue

              Created:
              Updated:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 weeks, 4 days, 5 hours, 17 minutes
                2w 4d 5h 17m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.