MySQL Connection Pool size and close/timeout issue

Issue type:

[x ] question [ ] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ x] mysql / mariadb [ ] oracle [ ] postgres [ ] sqlite [ ] sqljs [ ] react-native

TypeORM version:

[x ] latest [ ] @next [ ] 0.x.x (or put your version here)

Does TypeORM for MySQL by default uses connection pool? (I believe it does)
How do I specify the connectionPool limit in TypeORM for MySQL?
Should I manually release the connection from connectionPool? If yes, how do I release the connection?

I have followed the documentation for the implementation of TypeORM. Following is the ormconfig.json `[

{
    "name": "default",
    "type": "mysql",
    "host": process.env.DATABASE_HOST,
    "port": process.env.DATABASE_PORT,
    "username": process.env.DATABASE_USER,
    "password": process.env.DATABASE_PASSWORD,
    "connectTimeout": 1500,
    "database": process.env.DATABASE_NAME,
    "entities": [
        "src/models/entities/**/*.ts"
    ],
    "logging": true
}

I am having issues with TypeORM with MySQL.

Immediately after NodeJS application is started , the MySQL query takes 1 second to respond. If no more requests are received to the server, the connection to MySQL is still active, but under sleep state(By running show processlist; on MySQL - application user is still active.). After the idle time of around 10 minutes, If there is other request, I get the response in around 20 seconds for the 1st time and then immediately, for subsequent requests.

I have traced the issue to the following line of code in my Application: await getConnectionManager().get().transaction // Takes around 20sec. I have tried with getManager().transaction too but the issue persists. By splitting the above statement into 2 lines, const connectionManager = getManager(); connectionManager.transaction();, getting the transaction is taking the time.

The connections are never closed on the MySQL server side. In the following image, where application user is unified. 20+ users are under sleep connection even when there were no requests to the server.

There are sleep state, even when the application is not receiving any requests. Shouldn’t the connection pool maintain just one connection and close others if not needed. - (Reference to 3 question above)

@pleerock - This is my first post on GitHub forum. I hope I have explained the questions/issues in detail. Please let me know if any other details are required.

Issue Analytics

State:
Created 5 years ago
Comments:24 (14 by maintainers)

Top GitHub Comments

10reactions

Slessicommented, Aug 22, 2018

I found my problem. Not sure if it is also what caused the OP’s problem.

tl;dr when using const qRunner = connection.createQueryRunner() you must at some point call qRunner.release() yourself to avoid running out of free connections

I followed the documentation for transactions here: docs/transactions.md

Specifically, I was opting for “Using QueryRunner to create and control state of single database connection”.

This documentation does not mention anything about needing to manually release the connection when using query runner, and the docs/query-runner.md doc is marked as TBD.

If you look at src/driver/mysql/MysqlQueryRunner.ts you will see that neither commitTransaction nor rollbackTransaction releases the connection for you, so you will need to release it yourself, otherwise you will eventually fill up your pool (default limit 10) with unreleased connections.

@pleerock Maybe you can shed some light? Is there a bug, incomplete documentation or am I just stupid?

2reactions

ioclaudiocommented, Aug 9, 2019

TypeORM creates a connection pool and uses connection for a single operation (find, save, remove, etc.) - it takes the connection, uses it and releases it. That’s it. If all connections are busy it waits until any of them is released and then use it. You should not have issues you are talking about. I guess its something in your setup. Please provide some minimal reproduction code that shows the problem you have.

Hi, pleerock we are experiencing the same problem. We don’t have specified the option: extra: { connectionLimit: 50, } So I think that the default value is used.

The service uses code similar to this to manage transactions:

const connection = getConnection();
const queryRunner = connection.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();
 
try {
	//... many queries
	await queryRunner.commitTransaction();

} catch (err) {
	// Since we have errors lets rollback changes we made
	console.info(err);
	await queryRunner.rollbackTransaction();
	res.status(409).send();
	return;
} finally {
	// Release query runner
	await queryRunner.release();
}

However, the number of connections increases over time. We have seen this with the Mysql command “show status like ‘Conn%’;”. Why are the connections never closed and why they never expire?

cld