Скачать или смотреть Resolving Too Many Connections Errors in MariaDB: A Guide to Managing Sleeping Queries

Discover effective solutions to the `Too Many Connections` error in MariaDB, especially for apps using persistent connections. Learn how to manage sleep processes and optimize your database settings.
---
This video is based on the question https://stackoverflow.com/q/63580167/ asked by the user 'Wylls' ( https://stackoverflow.com/u/10633314/ ) and on the answer https://stackoverflow.com/a/63813738/ provided by the user 'Wylls' ( https://stackoverflow.com/u/10633314/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Large numbers of sleeping queries in MariaDB Process List despite of "wait_timeout" decreased

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Resolving Too Many Connections Errors in MariaDB: A Guide to Managing Sleeping Queries

When transitioning your PHP application from an older version of MySQL to MariaDB, you may encounter frustrating issues, including the dreaded “Too many connections” error. This can be especially challenging when dealing with large numbers of sleeping queries remaining in the MariaDB process list, despite modifications to the wait_timeout setting. Let's delve into the problem and explore effective solutions to alleviate it.

Understanding the Problem

What is the Too Many Connections Error?

The error "Too many connections" occurs in MariaDB when your application tries to create new connections but you’ve exceeded the maximum number of allowed connections set in your server configuration. This can severely hinder your application's performance and lead to downtime.

Why Are Sleeping Queries Problematic?

Sleeping queries are connections that are idle. They hold resources and prevent new connections from being established:

Each sleeping connection occupies a worker thread.

If leftover for too long, they can max out your connection limit.

In your case, you’ve adjusted your server settings:

[[See Video to Reveal this Text or Code Snippet]]

However, sleeping queries still persist beyond the wait_timeout, indicating that something else is affecting your configuration.

Diagnosing the Issue

Configuration Conflicts

After investigating the problem, it became clear that the issue stemmed from misconfigurations in server settings. When you updated the my.cnf file, some variables did not reflect the intended changes upon server restart.

Here’s how you can identify the root cause:

Locate Additional Configuration Files: MariaDB may utilize multiple configuration files. Check if there are any additional files such as my.cnf.d/, or other .cnf files, as these might override settings from your primary configuration file.

Search for Overriding Variables: Check all relevant configuration files to ensure variables such as wait_timeout haven't been reset elsewhere.

Solutions for Managing Sleeping Queries

Correcting Configuration Files

Edit the Right File:

Ensure that you are modifying the configuration file that the MariaDB server is loading.

Use the command SHOW VARIABLES LIKE 'global%'; in the MariaDB shell to check the current values and validate your settings.

Relaunch the Server: After editing the proper file, restart the MariaDB service:

[[See Video to Reveal this Text or Code Snippet]]

Consider Connection Management Strategies

Persistent Connections: If your application is using persistent connections, evaluate whether they're beneficial. While they can enhance performance, misuse can lead to resource exhaustion.

Connection Pooling: Implement connection pooling through your PHP application which can reduce the overhead of opening and closing connections frequently.

Regular Monitoring: Monitor your connections using tools like mysqladmin or integrated monitoring solutions to keep track of active, idle, and sleeping connections.

Conclusion

Managing sleeping queries and connection limits in MariaDB, especially after a significant upgrade, can be complex. By identifying the correct configuration files, ensuring your settings are applied appropriately, and considering refining your app’s connection management strategies, you can mitigate these issues effectively.

With careful configuration and monitoring, the threat of the “Too many connections” error can be significantly reduced, allowing your application to perform optimally.

Take Action: Review your MariaDB configurations today and stick to best p