Better calculation of default repo pool-size (#1541)

Wojciech Kapcia (Tigase) opened 8 months ago

Currently we calculate size of the repo-pool based on the available CPUs which could result error on machines with better specification:

"java.sql.SQLNonTransientConnectionException: Too many connections"

The problem stems from the fact that MySQL by default has limit of 151 connections (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections) and on bigger machines (like 40 CPU cores or more) we would have gigantic pool.

I think, even on bigger machine, we should have some sane maximum instead of going for "sky is the limit".

Relevant code

https://github.com/tigase/tigase-server/blob/8b4d87aa916b4a9d55411a9453fad9e442afb0b9/src/main/java/tigase/db/beans/MDPoolConfigBean.java#L63

    @ConfigField(alias = POOL_SIZE, desc = "Pool size", allowAliasFromParent = false)
    protected int poolSize = RepositoryFactory.USER_REPO_POOL_SIZE_PROP_VAL;

https://github.com/tigase/tigase-server/blob/9dc72919a73d1cb2f8f700cf852c809d55ea1a12/src/main/java/tigase/db/RepositoryFactory.java#L142):

public static final int USER_REPO_POOL_SIZE_PROP_VAL = Math.max(10, Runtime.getRuntime().availableProcessors() *
            REPO_POOL_SIZE_FACTOR_PROP_VAL);

Activities

Andrzej Wójcik (Tigase) commented 8 months ago

It would be good to have sane but configurable maximum. I wonder if applying some transformation (instead of a linear function) would be a better fit. Maybe a logarithmic function to calculate no. of connections to open? (as a default)
Wojciech Kapcia (Tigase) commented 8 months ago

Seems reasonable.

I sometime wonder if we shouldn't switch to HikariCP (https://github.com/brettwooldridge/HikariCP; quite tiny [150K], compatible licence). They seem to be quite focused on performance and has this tidbid there: https://github.com/brettwooldridge/HikariCP?tab=readme-ov-file#youre-probably-doing-it-wrong:

AKA "What you probably didn't know about connection pool sizing". Watch a video from the Oracle Real-world Performance group, and learn about why connection pools do not need to be sized as large as they often are. In fact, oversized connection pools have a clear and demonstrable negative impact on performance; a 50x difference in the case of the Oracle demonstration. Read on to find out. (https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing)
Login to comment

Type	Improvement
Priority	Normal
Assignee	Andrzej Wójcik (Tigase)
Version	none
Sprints	n/a
Customer	n/a

Issue Votes (0)

Watchers (3)

Reference

tigase/_server/server-core#1541