Ejabberd: Adding one item into shared roster group results into >500 SQL queries

[sarsonj]

Environment

• ejabberd version: 19.9.1
• Erlang version: Erlang/OTP 19 [erts-8.2.1] [source] [64-bit] [smp:2:2] [async-threads:10] [kernel-poll:false]
• OS: Linux (Debian)
• Installed from: docker image

Bug description

We are using shared roster groups - they are moderate sized (up to 20 members in each group), but we have many of groups (thousands). We realised, that adding and removing user from shared roster group is very slow, even with 20 users in group. The groups are very simple - no @all@ or no inclusion of other groups. Just plain users.

We are using SQL backend, that is recommended in ejabberd installation guide. But it looks, that shared roster group plugin is written very unoptimal to be used with SQL.

We attached SQL log as an example - for shared roster group, where we have 19 items and then we added 1 more item. The log shows queries executed when addind 1 user into shared roster group. As you can see in attached log, there is about 500 SQL queries just to add one user into shared roster group. It looks like, that the complexity grows with number of items in shared roster groups, I tried to add 1 user into group with 100 users the server just wrote Connection timeout.

How to reproduce

1. install ejabberd with SQL backend (we are using Postgresql)
2. create Shared Roster Group in ejabberd admin
3. add 19 items
4. add one more item

Results when running step 4: hundred of SQL queries and about 10 seconds of execution time on normal server. With more items in group (like 100) the web interface timeouts.

Expected results: maximum tens of queries and no significant performance increate with more users in shared roster group

shared-roster-group-add-sql-log.log

[sarsonj]

I looked into source code and realised, that LDAP version of shared roster groups (mod_shared_roster_ldap.erl) is using etc_cache for caching. Maybe same approach could be used there?

[prefiks]

Hello,

In commit d8899ca9acc7e2b198d1c03f9534ca46b4f524b2 i added ability to use cache, which should significantly reduce number of sql queries shared roster generates.