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

Backup including logs of external database uses a lot of DB connections

XMLWordPrintable

    • MOODLE_311_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE, MOODLE_35_STABLE, MOODLE_400_STABLE
    • MDL-62972_master
    • Moderate
    • Hide

      Requirements:

      1. testing this issue requires a developer (DB creation, MySQL CLI...)

      Setup external database

      1. Create a database other than your Moodle database.
      2. Create a table in the above database identical to the logstore_standard in your Moodle database.

      Configuration of external database

      1. Go to Site administration ► Plugins ► Logging ► Manage log stores
      2. Enable External database log
      3. Go to settings for the external logstore
      4. Enter your external database and table details and save
      5. Click on "Test connection" and make sure you can successfully connect to the database table.

      Step-By-Step

      1. Run a manual backup of a course (GUI) with a good number of activities. Ensure to include course logs in backup
      2. In parallel monitor the mysql connections over the CLI e.g. 

        while [ true ]; do mysql -u MYSQLUSERNAME -p'MYSQLUSERPASSWORD' --execute='SHOW FULL processlist;'; sleep 1; done;

      Show
      Requirements: testing this issue requires a developer (DB creation, MySQL CLI...) Setup external database Create a database other than your Moodle database. Create a table in the above database identical to the logstore_standard in your Moodle database. Configuration of external database Go to Site administration ► Plugins ► Logging ► Manage log stores Enable External database log Go to settings for the external logstore Enter your external database and table details and save Click on "Test connection" and make sure you can successfully connect to the database table. Step-By-Step Run a manual backup of a course (GUI) with a good number of activities. Ensure to include course logs in backup In parallel monitor the mysql connections over the CLI e.g. while [ true ]; do mysql -u MYSQLUSERNAME -p 'MYSQLUSERPASSWORD' --execute= 'SHOW FULL processlist;' ; sleep 1 ; done;

      Mysql connections to external logstore are not reused/cached, so the amount of mysql connections is increasing.
      This can best be seen during automated course backup. Every database reading creates a new database connection. This connections are terminated when backup process is finished.

      Probably solution: Use a global variable $DBEXT like $DB, where the external mysql connection is cached.

       

        1. broken-30-06-_2021_08-39-53.mp4
          1.80 MB
        2. database settings.png
          database settings.png
          56 kB
        3. enable external logging.png
          enable external logging.png
          33 kB
        4. fixed-30-06-_2021_08-42-02.mp4
          2.20 MB
        5. many sleeping mysql connections.png
          many sleeping mysql connections.png
          81 kB

            Unassigned Unassigned
            voegi Sascha Vogel
            Ilya Tregubov Ilya Tregubov
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 hours, 30 minutes
                2h 30m

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