Is sql_schema_multihost: true supposed to enable a shared pool of DB connections?

[marc0s]

I was asking in the MUC about configuring ejabberd to use a shared pool of DB connections among all the virtual hosts. I was told that I'd achieve that by setting sql_schema_multihost: true.

I tried it but it didn't give me the result I expected. I'd like to have a fixed number of DB connections and have all hosts share those.

The config I've been trying:

default_db: sql
sql_schema_multihost: true
update_sql_schema: true
sql_type: pgsql
sql_server: postgresql
sql_database: xmpp
sql_username: xmpp
sql_password: changeme
sql_pool_size: 2

This, with a list of three enabled hosts, gaves me 6 open connections to the DB:

postgres=# SELECT datname, COUNT(datid) FROM pg_stat_activity GROUP BY datname;
      datname       | count 
--------------------+-------
 xmpp               |     6

If I add another host and reload, the count rises to 8:

postgres=# SELECT datname, COUNT(datid) FROM pg_stat_activity GROUP BY datname;
      datname       | count 
--------------------+-------
 xmpp               |     8

Is this an expected behaviour? If I want to keep the amount of connections to PostgreSQL capped, should I resort to external solutions like pgBouncer?

Thanks!

[badlop]

Is sql_schema_multihost: true supposed to enable a shared pool of DB connections?

Looking at the documentation site and to the source code, i think no.

This is just a quick look at what I found in the source code:

• A) ejabberd_sql_sup:init/1 for each vhost, it reads its sql_pool_size option and starts the corresponding processes that will run ejabberd_sql:start_link for that vhost. Each process starts a connection to the SQL server.

• B) On the other hand, the multihost schema option is used in ejabberd_sql_pt when preparing the SQL queries: instead of calling different databases for each vhost, it calls the same database for several hosts, and adds "server_host=" to the SQL query

The query for a vhost built in B) is sent over one of the processes related to that vhost in the pool started in A).

I was asking in the MUC about configuring ejabberd to use a shared pool of DB connections among all the virtual hosts.

I don't remember such a thing being possible.

I was told that I'd achieve that by setting sql_schema_multihost: true.

I don't see any mention in the documentation site of that possibility. Maybe there's some sentence that allowed to think that? If that's the case, it would be great to know what sentence is confusing, and clarify it. Or maybe add a clarification to the multihost section.

I'd like to have a fixed number of DB connections and have all hosts share those.

In summary, I think that is not implemented (unless I missed something).

[marc0s]

Thanks for the detailed response, @badlop. Appreciated.