MYSQL connection
Answered
German Shepherd Dog posted this in #help-forum
Original message was deleted.
Answered by B33fb0n3
yea, that would happen. Many (me included) build a singleton to resuse one connection in the same request. So you can use one connection for one whole request, that can contain many requests to the database. And I am using a idle timeout, to close them automatically, when they are not used. My current idle timeout is at 1 minute, so my serverless functions can open a connection, can do 1 minutes (just execution time) their stuff inside it and after that it will be automatically closed. Of course you can also close them manuelly after you done
27 Replies
Original message was deleted
thats an expected behavior, because nextjs is serverless. So there is not server, that saves any pools, details, states, ...
@B33fb0n3 thats an expected behavior, because nextjs is server**less**. So there is not server, that saves any pools, details, states, ...
German Shepherd Dog
Is this a developer mode issue or an production issue too?
@German Shepherd Dog Is this a developer mode issue or an production issue too?
None of them. It's the way how nextjs is build
@B33fb0n3 None of them. It's the way how nextjs is build
German Shepherd Dog
But, wouldn't that create thousands of mysql connection labeled as sleep? And how would I get rid of them? Cuz I can deal with it locally, but if it happens to my production server more things then just that struggle with it
@German Shepherd Dog But, wouldn't that create thousands of mysql connection labeled as sleep? And how would I get rid of them? Cuz I can deal with it locally, but if it happens to my production server more things then just that struggle with it
yea, that would happen. Many (me included) build a singleton to resuse one connection in the same request. So you can use one connection for one whole request, that can contain many requests to the database. And I am using a idle timeout, to close them automatically, when they are not used. My current idle timeout is at 1 minute, so my serverless functions can open a connection, can do 1 minutes (just execution time) their stuff inside it and after that it will be automatically closed. Of course you can also close them manuelly after you done
Answer
German Shepherd Dog
Ahh yeah, a few other things call to the same database, all mysql2 and pooled. Just not nextJS, would changing the idle time out effect that?
German Shepherd Dog
I am also pretty much new to the term singleton,
class Singleton {
static #instance: Singleton;
/**
* The Singleton's constructor should always be private to prevent direct
* construction calls with the `new` operator.
*/
private constructor() { }
/**
* The static getter that controls access to the singleton instance.
*
* This implementation allows you to extend the Singleton class while
* keeping just one instance of each subclass around.
*/
public static get instance(): Singleton {
//MySQL create connection with( pooling? ) would be here ?
if (!Singleton.#instance) {
Singleton.#instance = new Singleton();
}
return Singleton.#instance;
}
}@B33fb0n3 yea, that would happen. Many (me included) build a singleton to resuse one connection in the same request. So you can use one connection for one whole request, that can contain many requests to the database. And I am using a idle timeout, to close them automatically, when they are not used. My current idle timeout is at 1 minute, so my serverless functions can open a connection, can do 1 minutes (just execution time) their stuff inside it and after that it will be automatically closed. Of course you can also close them manuelly after you done
German Shepherd Dog
I'd assume that someth like this would be what I'll be looking for then^?
@German Shepherd Dog I am also pretty much new to the term singleton,
class Singleton {
static #instance: Singleton;
/**
* The Singleton's constructor should always be private to prevent direct
* construction calls with the `new` operator.
*/
private constructor() { }
/**
* The static getter that controls access to the singleton instance.
*
* This implementation allows you to extend the Singleton class while
* keeping just one instance of each subclass around.
*/
public static get instance(): Singleton {
//MySQL create connection with( pooling? ) would be here ?
if (!Singleton.#instance) {
Singleton.#instance = new Singleton();
}
return Singleton.#instance;
}
}
that looks more like java for me. I am using this:
let queryClient; // is saved inside a variable
if (!queryClient)
queryClient = postgres(getDbConnectionString());
export const db = drizzle(queryClient, {schema}); // <--- this will be used to access the db. German Shepherd Dog
Yeah I got confused about that too tbf post said it was tsx.
So I'd assume I'll have to get rid of pooling, and just export the mysql connection where you make the drizzle call, then import the new singleton file and just call to db?
So I'd assume I'll have to get rid of pooling, and just export the mysql connection where you make the drizzle call, then import the new singleton file and just call to db?
@B33fb0n3 yea, that would happen. Many (me included) build a singleton to resuse one connection in the same request. So you can use one connection for one whole request, that can contain many requests to the database. And I am using a idle timeout, to close them automatically, when they are not used. My current idle timeout is at 1 minute, so my serverless functions can open a connection, can do 1 minutes (just execution time) their stuff inside it and after that it will be automatically closed. Of course you can also close them manuelly after you done
German Shepherd Dog
And the closing is either .close() or doing the idle time out change in my db settings?
yea, the idle timeout is a db setting and
.close() depends on your backend and software that you are using. So check if there is any .close() function or similar@B33fb0n3 yea, the idle timeout is a db setting and .close() depends on your backend and software that you are using. So check if there is any .close() function or similar
German Shepherd Dog
Alr I'd assume clearing up "sleeping" connections is also better performance wise?
@German Shepherd Dog Alr I'd assume clearing up "sleeping" connections is also better performance wise?
yea, especially when you limited your connections
German Shepherd Dog
(I am very new to next and serverless in general, and never had issues like this, my apologies)
@B33fb0n3 yea, especially when you limited your connections
German Shepherd Dog
1 min is more then enough I'd assume?
@German Shepherd Dog (I am very new to next and serverless in general, and never had issues like this, my apologies)
I had the same problem in the past, so no worries
@German Shepherd Dog 1 min is more then enough I'd assume?
I am hosting on vercel and iirc vercel kill your serverless function after 15 seconds. So for me 1 minute is completelly fine. I guess for you as well
@B33fb0n3 I am hosting on vercel and iirc vercel kill your serverless function after 15 seconds. So for me 1 minute is completelly fine. I guess for you as well
German Shepherd Dog
Yeah imma host it myself xd, other things i should be prepared for?
@German Shepherd Dog Yeah imma host it myself xd, other things i should be prepared for?
oh yea, then check the execution time of your stuff that you do with your db. Normally your db is pretty fast, so 1 minute is a very long time for a database query
@B33fb0n3 oh yea, then check the execution time of your stuff that you do with your db. Normally your db is pretty fast, so 1 minute is a very long time for a database query
German Shepherd Dog
I'm also running a discord bots that calls to the same Db, better play it safe if that makes sense
@German Shepherd Dog I'm also running a discord bots that calls to the same Db, better play it safe if that makes sense
I guess that's the best, yea. Is you question solved like that?
German Shepherd Dog
Yeah, thanks
German Shepherd Dog
Thansk for the help^
@B33fb0n3
German Shepherd Dog
Error:
GET /api/clientsize 200 in 25ms
Error fetching data: Error: Can't add new command when connection is in closed state
at PromisePoolConnection.query (webpack-internal:///(rsc)/./node_modules/mysql2/promise.js:94:22)DB file with singleton:
import mysql, { Pool } from 'mysql2/promise';
let pool: Pool;
const createPool = (): Pool => {
return mysql.createPool({
host: process.env.DB_HOST as string,
user: process.env.DB_USER as string,
password: process.env.DB_PASSWORD as string,
database: process.env.DB_NAME as string,
connectionLimit: 20,
});
};
const getPool = (): Pool => {
if (!pool) {
pool = createPool();
}
return pool;
};
export default getPool();API file:
https://pastebin.com/GdiSRG8k
Component file:
https://pastebin.com/3QG1TuGQ
This happens when I try reloading a page after the connection is closed. (So the connecton thats labled as sleep is deleted) Any suggestions forfixes?
EDIT: seems resolved.. maybe a wrong thing with importing