Too many connections - NextJS14 + MySQL2

Question

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.

Answer 1

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.