Asynchronous connections to HANA Database

[hapvlz]

Hello everyone,

Yes, I've seen that this project is actually closed as node-hdb has been moved under SAP development and it's under @sap/hana-client, however I would like to ask here for assistance since I do not see any possible support from SAP about it.

My target is to create simple NodeJS HANA Availability monitoring, it already works fine but not as I would expect it to work, together with new databases in monitoring the connection is taking longer and longer time.

I am running async database connections to check it availability, and then disconnect.
My code runs asynchronous but the problem seems to be in the hana library itself, as with every additional connection, the time needed to perform connections is way longer.

My code is:

async function hdbAsync(DB_SID, DB_HOSTNAME, DB_PORT) {
        const hdbConn = {
            host: DB_HOSTNAME,
            port: DB_PORT,
            uid: '*****',
            pwd: '*****'
        };

        return new Promise((resolve, reject) => {
            let hdb = han.createConnection();

            hdb.connect(hdbConn, function (error) {
                if (error) {
                    reject(false)
                    return
                }

                hdb.exec('SELECT * FROM SYS.DUMMY', function (error, res) {
                    if (error) {
                        hdb.disconnect();
                        reject(false)
                        return
                    }

                    hdb.disconnect();
                    resolve(true);
                    return
                })
            })
        })
    }

Above code runs asynchronously, when I enable trace I can see that hdb.connect is being executed in parallel in the same second. But the connection is taking longer and longer with each added connection. It looks like some parameter or something that limit maximum connections in parallel - i am not sure.

Are you able to advise? Thank you in advance.

[IanMcCurdy]

The hana-client driver will only spawn a limited number of threads at once. This limit is governed by the UV_THREADPOOL_SIZE environment variable (the default is 4). Can you try increasing this value and see if it fixes the issue?

[hapvlz]

Hello IanMcCurdy,

thank you for response. I've already increased this parameter for my oracle connections, in that case helped. Currently it's set to 64. However in case of HANA that doesn't change anything.

[IanMcCurdy]

I did an experiment using your code with varying numbers of concurrent connections. With UV_THREADPOOL_SIZE set to 32 I got the following results:

# Connections	Average Connection Time
4	765ms
8	687ms
16	751ms
32	707ms
64	1387ms
128	2502ms

Then, with UV_THREADPOOL_SIZE set to 64 I got the following:

# Connections	Average Connection Time
4	692ms
8	667ms
16	685ms
32	687ms
64	732ms
128	1313ms

This seems to make sense to me, the average connection time stays stable as the number of concurrent connections increases until the number of connections surpasses the number of available threads. Can you repeat this experiment and let me know what you find?

[hapvlz]

Hello IanMcCurdy,

thanks for answer.
I've performed tests as you suggested,

With UV_THREADPOOL_SIZE set to 32 -

Connections | Average Connection time

4 | 713ms
8 | 1803ms
16 | 2776ms
32 | 5550ms
64 | 11077ms
128 | 22606ms

UV_THREADPOOL_SIZE set to 64

Connections | Average Connection time

4 | 701ms
8 | 1393ms
16 | 2775ms
32 | 7506ms
64 | 11072ms
128 | 22241ms

Doesn't looks that good like yours.
here is the code that run async the function, also it's important in my case the tests are performed with providing invalid credentials. What about yours?

async function lop() {
        let conn = [];
        for (let i = 0; i < 128; i++) {
            console.log('Execution no. ' + i);
            conn[i] = hdbAsync('SID', 'localhost', 30047);
        }

        return Promise.allSettled(conn)
    }

    time.measureTime(true, "HealthChecks");
    console.log(await lop())
    time.measureTime(false, "HealthChecks");

[hapvlz]

Any ideas? Can you share your code that you used?

[hapvlz]

Refreshing, I would really appreciate any kind of support.

[hapvlz]

I did an experiment using your code with varying numbers of concurrent connections. With UV_THREADPOOL_SIZE set to 32 I got the following results:

Connections Average Connection Time

4 765ms
8 687ms
16 751ms
32 707ms
64 1387ms
128 2502ms

Then, with UV_THREADPOOL_SIZE set to 64 I got the following:

Connections Average Connection Time

4 692ms
8 667ms
16 685ms
32 687ms
64 732ms
128 1313ms

This seems to make sense to me, the average connection time stays stable as the number of concurrent connections increases until the number of connections surpasses the number of available threads. Can you repeat this experiment and let me know what you find?

Are you able to share your code for that test? Also u used node-hdb library of latest hana-client from SAP?

[AndrewWUw]

hi hapvlz

We're experiencing the similar performance issue with concurrent connections. Have you found a solution for the problem? Changing UV_THREADPOOL_SIZE doesn't seem to help from my testing neither.

Regards
Andrew