0

I'm currently creating a web-app for a project. It worked fine for a couple hours, then I experienced a "Too many connections" error on my MySQL database.

So, I ran select count(host) from information_schema.processlist; and show processlist; to find that the processlist is filled with Sleep connections, and when I refresh the page, a new connection is created.

This is the result from show processlist;

+------+-----------------+-----------------+------------+---------+--------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+-----------------+-----------------+------------+---------+--------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 430697 | Waiting on empty queue | NULL | | 1107 | root | localhost:53644 | test_db | Sleep | 681 | | NULL | | 1108 | root | localhost:53817 | test_db | Sleep | 653 | | NULL | | 1109 | root | localhost:55438 | test_db | Sleep | 392 | | NULL | | 1110 | root | localhost:55769 | test_db | Query | 0 | init | show processlist | | 1111 | root | localhost:50072 | test_db | Sleep | 240 | | NULL | | 1112 | root | localhost:50075 | test_db | Sleep | 240 | | NULL | | 1113 | root | localhost:50117 | test_db | Sleep | 234 | | NULL | | 1114 | root | localhost:50185 | test_db | Sleep | 224 | | NULL | | 1115 | root | localhost:50288 | test_db | Sleep | 208 | | NULL | | 1116 | root | localhost:50331 | test_db | Sleep | 202 | | NULL | | 1117 | root | localhost:50427 | test_db | Sleep | 188 | | NULL | +------+-----------------+-----------------+------------+---------+--------+------------------------+------------------+

And these are my codes for the project:

// app\test\db.js import mysql from 'mysql2/promise' let pool function createPool() { try { if (!pool) { pool = mysql.createPool({ host: process.env.DB_HOST, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, connectionLimit: 10, waitForConnections: true, queueLimit: 0, enableKeepAlive: true, keepAliveInitialDelay: 0, idleTimeout: 60000, }) } return pool } catch (error) { console.error(`Could not connect - ${error.message}`) throw new Error('Database connection failed') } } const db = createPool() export async function executeQuery(sql, values = []) { const connection = await db.getConnection() try { const [results] = await connection.execute(sql, values) return results } catch (error) { console.error('Query execution failed:', error) throw error } finally { connection.release() } } export async function closePool() { if (pool) { await pool.end() } }

(The code above is generated by ChatGPT.)

// app\test\actions.js import { executeQuery } from "./db" export default async function testQuery() { try { const result = await executeQuery('SELECT 1 + 1 AS solution') return [result[0].solution] } catch (error) { console.error('API error:', error) return ["Error"] } } 
// app\test\page.js import testQuery from "./actions" const res = await testQuery() export default function DBAccess() { return ( <div className="flex w-screen h-screen justify-center items-center"> <p className="">RESULT: {res[0]}</p> </div> ) }

I managed to "fix" it by changing connection.release() to connection.destroy() but that defeats the purpose of a pool. And I have tried using pool.promise(), and that didn't work either.

Improve this question

1 Answer 1

Reset to default
0

Settings

All MySQL servers and all MariaDB servers limit the number of simultaneous connections. You can check the current value with:

mysql -e 'show variables like "max_connections"' +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 2000 | +-----------------+-------+

You can change the current value with:

mysql -e 'set global max_connections = 2500'

But this value is reset on restart of the database server. To change the value permanently, you have to edit the config file in /etc/, sometimes /etc/my.cnf, sometimes /etc/mysql/... (depends on operating system). Add to section [mysqld]:

[mysqld] ... max_connections = 2500

The maximum number of connections that have been in use simultaneously since the server started and the number of currently open connections can be queried as follows:

mysql -e 'show status where variable_name like "max_used_conn%" or variable_name="threads_connected"' +---------------------------+---------------------+ | Variable_name | Value | +---------------------------+---------------------+ | Max_used_connections | 162 | | Max_used_connections_time | 2024-08-07 11:15:12 | | Threads_connected | 75 | +---------------------------+---------------------+

What is a good number of max_connections?

This question cannot be answered because the number of connections required depends on many things.

Typically, each website establishes exactly one connection per client to the database server. Therefore, for a first estimate, the maximum number of clients allowed by the web server (Apache, nginx,...) +200 should be used. The +200 then offers enough reserve for backend connections, such as those used by cron jobs.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That isn't the problem here. The connection from the client to the database server stays and doesn't go away, even if connection.release() is called. There should be one connection from the client when the page loads.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.