I have some interogations about the mariadb connector (and the whole mariadb database’s functioning) on connections handling.
In my typescript program (using mariadb-connector v3.4.0 with a mariadb server v10.6.21) I’m instantiating a pool at my program start as well as a web server using expressJS. In each functions accross my app I’m using the async’s pool.query("<query>", [<params>]); directly (without creating proper connections nor releasing them after their use) like this:
// in my main file
...
const pool = createPool({
...config.mysql,
trace: level === Level.Debug,
insertIdAsNumber: true,
bigIntAsNumber: true,
connectionLimit: 10 // I know this is the default value
});
// Here let's imagine I'm brining up a web server through expressJS and have some routes
// from which I need to retrieve data from my mariadb database
// in another file, I have a collection of functions like this:
export class ElementRepository{
constructor(private pool: Pool) {}
async getElementById = (id: number): Promise<Element> => {
const query = "SELECT * FROM elements WHERE id = :id";
const params = [id];
const [result] = await this.pool.query<Element[]>(query, params);
console.log("some logs");
return result;
}
async getElements = (): Promise<Element[]> => {
const query = "SELECT * FROM elements";
const result = await this.pool.query<Element[]>(query, []);
console.log("some logs");
return result;
}
}
So first question:
As you can see, I’m not using getConnections() with a .release() at the end, but it does work, should I worry about it ? or is it managed under the hood ? I guess now 10 connections are reserved by the mariadb-connector and will never be killed even if idle ? Am I wrong ?
When my application start, I need to fetch a lot of data, and I can’t do it in one query (because the SQL query would looks like quite hideous with more than 300 lines), so I make only one simple query which fetch all my elements (those I call “raw elements”), and then I’ll iterate over each elements to make a multiple SQL queries that may take up to 5sec to compute (depending of the amount of elements in my table) to compute the element’s utilization rate. Using multiple seconds to compute element’s utilization rate is kinda “normal” because it may compute it based on millions entries.
My code looks like something like this:
const rawElems = await pool.query("SELECT * FROM elements", []);
const chunkSize = 10;
const chunkedElems = [...Array(Math.ceil(rawElems.length / chunkSize))].map((_) => rawElems.splice(0, chunkSize));
const elements: Element[] = [];
const start = performance.now();
// Iterate over each chunk and concurrently fetch their elements use rates
for (const elem of chunkedElems ) {
const result = await Promise.all<Element>(
elem.map(async (element) => {
const lastMaintenanceDate = await this.getLastMaintenanceDate(element.id);
// counter represent the raw counter value that is computed differently depending of the element's type
const counter = await (async (mode: ConfigurationMode) => {
switch (mode) {
case ConfigurationMode.Occurrence:
return this.getCurrentAmount(element.id, lastMaintenanceDate);
case ConfigurationMode.Duration:
return this.getCurrentDuration(element.id, lastMaintenanceDate);
default:
return 0;
}
})(element.mode);
return { ...element, debounce: false, counter };
})
);
elements.push(...result);
// Display the percentage of ready observed elements
const percentage = `${((elements.length / chunkSize / chunkedElems .length) * 100).toFixed(0)}%`;
console.log("still processing observed elements...", percentage);
}
const stop = performance.now();
const elapsedTime = ((stop - start) / 1000).toFixed(2);
console.log(`finished to load element's counter in ${elapsedTime}s!`);
So with this snippet, I will take 10 connection from the pool (it’s currently hardcodded but since it’s the pool’s connectionLimit I guess it’s okay), and I will segment my rawElems in chunck of 10, and sequentially iterate on each chunck of 10 rawElems and use an awaited Promise.all() to compute their use rate in the same time. It works.
But now let’s imagine I want to increase the connectionLimit in my pool, I could safely retrieve it with a mere pool.getActiveConnections() ? can I increase the connectionLimit property up to the maximim available connection on my MariaDB instance ? I have the default number, 151, that I get from SHOW variables LIKE 'max_connections';, and since the following query SHOW status LIKE 'Max_used_connections'; returned a Value of 16, it means I used 72 simultaneous connections, so I may increase connectionLimit by 50 without much issues ?
Second question:
Did I understood correctly ? or am I mixing multiple things / concept ?