CODEWITHSUNDEEP
node.jsmariadb
KristinnVikarJ
KristinnVikarJ

Reputation: 23

MariaDB connection very slow in NodeJS

I'm currently using MariaDB version 10.5.12 and the mariadb-connector-nodejs package to interact with it from NodeJS

The pool is created as such

const pool = MariaDB.createPool({
    host: process.env.DB_HOST, 
    user: process.env.DB_USER, 
    password: process.env.DB_PASS,
    database: process.env.DB_DATABASE,
    connectionLimit: 25
});

A simple request such as connection.Query("SELECT * FROM Users"); Where the users table only contains 5 users can take anywhere between 90ms and 650ms

profiling the query in MariaDB says the query itself only takes a few nanoseconds.

The time a request takes seems to be pretty random, the first request could be 100ms and the second one right after using the same connection could take 600ms which I feel is way too high for a query that takes less than a millisecond to process.

But I'm unable to find where the issue is coming from, I have tried doing all the changes mysqltuner recommended, I've tried disabling name resolve with no visible speedup (I assume the requests are being cached as all requests come from the same ip)

Upvotes: 0

Views: 589

Answers (1)

Wilson Hauck
Wilson Hauck

Reputation: 2343

top indicates there is NO Swap space available.
Consider enabling 6G of swap space to survive busy situations with minimal delay and a surviving system.

Please share the code generally used to Connect, Process, Close connections. Your threads_connected count indicates the Close is being missed and has left 83 threads connected in 10 days.

Suggestions to consider for your my.cnf [mysqld] section,

log_error=/var/log/mysql/mariadb-error.log  # from 0 to allow viewing ERRORS detected.
innodb_max_dirty_pages_pct_lwm=1  # from 0 percent to enable pre-flushing
innodb_max_dirty_pages_pct=1  # from 90 percent to minimize innodb_buffer_pool_pages_dirty of 367.
innodb_adaptive_hash_index=ON  # from OFF to minimize deadlocks
max_connect_errors=10  # from 100 to frustrate hackers/crackers after 10 attempts.
connect_timeout=30  # from 10 seconds to be more tolerant of connection attempts.

Observations, connections were 120,604 in 10 days and you had 28984 aborted_connects about 1/4 of the events. You must have a lot of unhappy people trying to use your system or they are hackers trying to break in.

View profile for contact info and free downloadable Utility Scripts to assist with performance tuning.

Upvotes: 1

Related Questions