[MDL-25373] Faster user sync in auth/db - Moodle Tracker

XML

Word

Printable

Database:

Any
Affected Branches:
MOODLE_20_STABLE, MOODLE_23_STABLE, MOODLE_24_STABLE, MOODLE_28_STABLE
Pull from Repository:
https://github.com/danielneis/moodle.git
Pull Main Branch:
~~MDL-25373~~-master
Pull Main Diff URL:
https://github.com/danielneis/moodle/compare/master...MDL-25373-master
Testing Instructions:
Hide

You must have an external database with users

Enable auth/db

Configure the plugin to match you external database configs

Configure some profile fields to sync

Run the syncronization via cron

See that all users are imported much faster

Alter some details of your users in external database

Run the syncronization via cron

See that users you have altered are updated much faster

You may have a somewhat big database, with at least 10K+ users to sync, to really see the difference in speed.
Show
You must have an external database with users Enable auth/db Configure the plugin to match you external database configs Configure some profile fields to sync Run the syncronization via cron See that all users are imported much faster Alter some details of your users in external database Run the syncronization via cron See that users you have altered are updated much faster You may have a somewhat big database, with at least 10K+ users to sync, to really see the difference in speed.

This patch does two things for auth/db:

Faster user sync
- Use temporary tables to store all users with all fields
- Select which users to update/insert/revive using (left) joins
- Update user info using only one UPDATE statemente (instead of 1 udpate for each field for each user)

has a non-specific relationship to

MDL-39725 PostgreSQL performance can be poor with large temp tables

has been marked as being related by

MDL-25372 Use temporary tables in sync_users of external database authentication

Participants:: Anthony Borrow, CiBoT, Damyon Wiese, Daniel Neis Araujo, Dan Poltawski, Frédéric Massart, John Stabinger, José Norberto Guiz Fernandes Corrêa, Marina Glancy, Matt Porritt, Petr Skoda, Russell Smith