Hello!
My idea is to open and iterate over the test data file and create a hashset of schema name and connection in set up stage so all VUs can use the same connections when needed as the intent is not to load test DB with these connections and to query for some decision making in the test.
The current restriction from the K6 site is it only allows passing data (JSON) between stages.
Is there any alternate option to this?
Hi there, welcome to the forum 
I’m assuming that you’re using the xk6-sql extension.
You’re right that it’s not possible to pass the DB connection from, say, setup() to the default function.
But why do you need to do that? When you create the connection in the init context/stage, all VUs would use that same connection, so you don’t need to pass it from setup().
There’s a minor quirk with how the init context is evaluated, since the code there is run at least twice, but at most 2 DB connections will be created regardless of how many VUs you run with. You can confirm this on Postgres with select count(*) from pg_stat_activity;.
Also see this this comment on a related issue for details.
I am confused over how many times init code executes. I see the solution from 973 says init code is executed MaxVus + 3 Times. Does that mean it would create SQL connections multiple times instead of twice you mentioned ?
Yes, the init code runs several times. No, only 2 DB connections would be created regardless of how many VUs you run the test with.
Please confirm this on your RDBMS, and let us know if that’s not the case.
I opened the connections per schema in init stage(Example: 2 connections for 2 schemas).
Inside default function, each VU would query choosing those open connection depending on their test data input. (Example: Max VUs:- 5, Out of 5, 4 VUs use same schema (so they suppose to share same connection) and rest 1 use another schema which uses another connection).
I observe there exists 1 pid per schema in pg_stat_activity at the beginning of the test. Later on, I see a separate pid for each VU as they start to query. Example: 4 pids trying to query same schema and 1 pid querying another schema.
I took another look at this, and you’re right, a connection is created for each VU. I reopened issue #25 so we can discuss potential solutions to this, but it’s unlikely this will be changed anytime soon, sorry. We currently don’t have the bandwidth to prioritize this, and as I mentioned here, I’m not sure if this is an issue.
2 Likes
BTW, as a workaround, you can open the connection inside the default() function, and only do it once for one VU:
import sql from 'k6/x/sql';
import exec from 'k6/execution';
let db;
export function teardown() {
if (db) {
db.close();
}
}
export default function () {
if (exec.vu.idInInstance == 1 && exec.vu.iterationInInstance == 0) {
db = sql.open('postgres', 'postgres://...');
db.exec('...');
}
// other VU code
}
You can also open it inside setup() and just return static data to all VUs. In both cases only one connection will be opened.
But if you want all VUs to be able to execute queries while sharing a single connection, then that’s not possible at the moment.
1 Like
Thanks for the workaround and exploring the potential solutions if at all needed.
import { check, sleep } from 'k6';
import encoding from 'k6/encoding';
import http from 'k6/http';
import sql from 'k6/x/sql';
function createConnectionPool(poolSize) {
console.log("here");
const connectionPool = [];
const username = __ENV.DB_USERNAME;
const password = __ENV.DB_PASSWORD;
const host = __ENV.DB_HOST;
const database = __ENV.DB_DATABASE;
console.log(`${host}`);
for (let i = 0; i < poolSize; i++) {
const db = sql.open("mysql", `${username}:${password}@tcp(${host}:3306)/${database}`);
if (db) {
let l = connectionPool.push(db);
console.log(`${l}`);
}
}
return {
getConnection: () => connectionPool.pop(),
releaseConnection: (db) => connectionPool.push(db),
close: () => {
connectionPool.forEach(db => db.close());
}
};
}
let poolManager ;
const endpoints = [
{
method: 'POST',
url: 'http://qa-lb01-auth-api.mykaarma.com/authenticateAndAuthorize/dealerLevelScope',
tag: 'Authenticate Dealer Level Scope'
},
{
method: 'POST',
url: 'http://qa-lb01-auth-api.mykaarma.com/authenticateAndAuthorize/departmentLevelScope',
tag: 'Authenticate Department Level Scope'
}
];
function base64Encode(str='') {
if (typeof str !== 'undefined' && str !== null) {
return encoding.b64encode(str);
}else{
return "";
}
}
function getRandomApiScopeFromDB(db){
const startTime = new Date();
let scope = sql.query(db, `SELECT Scope FROM ApiScope ORDER BY RAND() LIMIT 1;`);
const duration = new Date() - startTime;
// Log the time taken for the operation
console.log(`Time taken to fetch random SS Auth Token: ${duration} ms`);
return scope;
}
function getRandomSSAuthTokenFromDB(db){
const startTime = new Date();
console.log(`${db}`);
let { userName, passWord } = sql.query(db, `SELECT username, password FROM ServiceSubscriber ORDER BY RAND() LIMIT 1;`);
const duration = new Date() - startTime;
// Log the time taken for the operation
console.log(`Time taken to fetch random SS Auth Token: ${duration} ms`);
return base64Encode(`${userName}:${passWord}`);
}
function getRandomDealerUuidFromDB(db){
const startTime = new Date();
const username = __ENV.DB_USERNAME;
const password = __ENV.DB_PASSWORD;
const host = __ENV.DB_HOST;
const database = __ENV.DB_DATABASE;
let dealerUUID = sql.query(db, `SELECT UUID FROM Dealer ORDER BY RAND() LIMIT 1;`);
const duration = new Date() - startTime;
// Log the time taken for the operation
console.log(`Time taken to fetch random SS Auth Token: ${duration} ms`);
return dealerUUID
}
function getRandomDealerDepartmentUuidFromDB(db){
const username = __ENV.DB_USERNAME;
const password = __ENV.DB_PASSWORD;
const host = __ENV.DB_HOST;
const database = __ENV.DB_DATABASE;
let dealerDepartmentUUID = sql.query(db, `SELECT UUID FROM DealerDepartment ORDER BY RAND() LIMIT 1;`);
return dealerDepartmentUUID;
}
export const options = {
scenarios: {
ramping_arrival_rate: {
executor: 'ramping-arrival-rate',
startRate: 10, // Start with 10 iterations/second
timeUnit: '1s', // RPS will be calculated per second
preAllocatedVUs: 50, // Preallocate 50 VUs
maxVUs: 3500, // Maximum number of VUs
stages: [
{ target: 500, duration: '3m' }, // Ramp up to 500 RPS in 3 minutes
{ target: 1000, duration: '1m' }, // Ramp up to 1000 RPS in the next 2 minutes
{ target: 1000, duration: '2m' }, // Ramp up to 1500 RPS in the next 2 minutes
{ target: 0, duration: '1m' }, // Ramp down to 0 RPS over 2 minutes
],
},
},
};
// Setup function: Open the database connection once
export function setup() {
console.log("setup started");
poolManager = createConnectionPool(100);
console.log("connection setup");
}
// Teardown function: Close the database connection once
export function teardown() {
console.log("closing connection");
if (poolManager) {
poolManager.close();
}
console.log("connection closed");
}
export default function () {
const ss_username = __ENV.SERVICE_SUSBCRIBER_USERNAME;
const ss_password = __ENV.SERVICE_SUSBCRIBER_PASSWORD;
const apiToken = base64Encode(`${ss_username}:${ss_password}`);
if(!poolManager){
console.log("nooooo!!!!");
}
let db = poolManager.getConnection();
const ssToken = getRandomSSAuthTokenFromDB(db);
poolManager.releaseConnection(db);
db = poolManager.getConnection();
const randomApiScope = getRandomApiScopeFromDB(db);
poolManager.releaseConnection(db);
// randomly fetching any enpdoint from array of endpoints
const distribution = Math.floor(Math.random() * 2);
let randomEndpoint = endpoints[distribution];
const { method, url, tag } = randomEndpoint;
let tags = { endpoint: tag };
let response = null;
if (tag === 'Authenticate Dealer Level Scope') {
console.log("Requesting authenticate Dealer Level Scope endpoint");
db = poolManager.getConnection();
const randomDealerUuidFromDB = getRandomDealerUuidFromDB(db);
poolManager.releaseConnection(db);
const payload = {
"authToken": `Basic ${ssToken}`,
"requesterUuid": "load Tester",
"apiScope": randomApiScope,
"filterDealerUuid": [randomDealerUuidFromDB]
};
response = http.post(url,JSON.stringify(payload), {
headers: {
'Authorization': `Basic ${apiToken}`},
tags});
} else {
console.log("Requesting authenticate department Level Scope endpoint");
db = poolManager.getConnection();
const randomDealerDepartmentUuidFromDB = getRandomDealerDepartmentUuidFromDB(db);
poolManager.releaseConnection(db);
const payload = {
"authToken": `Basic ${ssToken}`,
"requesterUuid": null,
"apiScope": randomApiScope,
"filterDepartmentUuid": [randomDealerDepartmentUuidFromDB]
};
response = http.post(url,JSON.stringify(payload), {
headers: {
'Authorization': `Basic ${apiToken}`},
tags});
}
console.log("response received!!!!");
check(response, {
'is status 200': (r) => r.status === 200,
});
}```. is this possible to maintain a pool of connections across VUs and use them in default function ?