Cloud SQL can not created thread to handle new connection

[easonwang]

My environment is as follows:

CPU: 24 cores
RAM: 96 GB
CloudSQL MySQL version: 5.7
I have already set max_connections to 10,000. However, during stress testing, both CPU and RAM usage remain sufficient. But when the number of active connections reaches around 32xxx, an error message "Can not create thread to handle new connection (error = 11)" appears when trying to connect to Cloud SQL.

Looking at the CloudSQL startup messages, it shows that the number of threads is 32,726. Is it possible to adjust this value?

[ms4446]

In MySQL, the thread_cache_size parameter specifies the number of threads the server should keep in cache for reuse. When a client disconnects, its threads are returned to this cache if the total number of cached threads is below the thread_cache_size. This is distinct from the max_connections parameter, which defines the upper limit of simultaneous connections the MySQL server can handle.

The error "Can not create thread to handle new connection (error = 11)" arises when the max_connections threshold is reached. While enhancing the thread_cache_size might mitigate the frequency of this error, it's not a direct remedy.

Performance Considerations

Modifying the thread_cache_size aims to minimize the overhead associated with creating and destroying threads, a common scenario in environments with frequent client connections and disconnections. However, setting this value too high can inadvertently degrade performance due to increased memory consumption and potential resource contention.

Recommendations

The optimal thread_cache_size largely depends on your workload's connection and disconnection rates. For instance:

• For workloads with rapid client connection/disconnection cycles, consider setting thread_cache_size to double the average number of active connections.
• For more stable workloads with fewer connection changes, aligning thread_cache_size with the average active connections or even setting it slightly lower might be more appropriate.

To adjust the thread_cache_size in Cloud SQL, use the gcloud sql instances patch command.

Tweaking the thread_cache_size can enhance performance in environments with frequent client connectivity changes. However, it's crucial to differentiate it from max_connections and to calibrate thread_cache_size based on your specific workload characteristics.

[easonwang]

Hi @ms4446 

In my scenario, I need to handle a concurrent connection count of 80,000. However, when the number of connections reaches 32,767, I encounter the "can not create threads for new connection" error. Subsequent connection attempts also fail. Upon examining CloudSQL's thread_handling setting, I found it is set to one-thread-per-connection. This suggests that in CloudSQL MySQL 5.7 Enterprise Version, the maximum connection count is limited to 32,767.

I have also attempted to modify the thread_handling value to pool-of-threads in CloudSQL flags, but it seems that this value cannot be changed.

[ms4446 · Accepted solution]

Given your scenario where you need to handle a concurrent connection count of 80,000, it's understandable that you're encountering challenges when the number of connections approaches 32,767. Let's break down the situation and explore potential solutions:

Technical Insights:

• In Cloud SQL MySQL 5.7 Enterprise Version, when the thread_handling setting is configured to one-thread-per-connection, each connection requires its own dedicated thread. This configuration leads to the limitation you've observed, where the maximum number of connections is capped at 32,767.
• The error message "can not create threads for new connection" is a direct result of this limitation. Once this threshold is reached, the system is unable to spawn new threads for additional connections, leading to connection failures.
• While the pool-of-threads setting could potentially alleviate this issue by using a pool of threads to manage connections, it appears that this option is not modifiable in CloudSQL MySQL 5.7 Enterprise Version.

Potential Solutions:

1. Upgrade to a Newer Version: Consider upgrading to Cloud SQL MySQL 8.0 or a higher version. These versions support the pool-of-threads setting, which can significantly increase the maximum number of concurrent connections.
2. Alternative Database Solutions: If upgrading isn't feasible, you might want to explore other database solutions within the Google Cloud ecosystem. For instance, Cloud Spanner is a scalable, fully-managed relational database service capable of handling millions of concurrent connections.
3. Connection Pooling: Implementing a connection pooler can help manage the number of active connections to the database. By reusing existing connections, you can effectively reduce the number of simultaneous connections, potentially bypassing the 32,767 limitation.
4. Optimize Application Code: Review and optimize your application code to minimize the number of simultaneous database connections required.
5. Load Balancing: Consider using a load balancer to distribute incoming traffic across multiple CloudSQL instances, effectively increasing the overall connection limit.

Additional Considerations:

• Database Sharding: Splitting your database into smaller, more manageable pieces (shards) can distribute the load across multiple servers, enhancing performance and scalability.
• Read Replicas: Utilizing read replicas can offload some of the read requests from the primary database, improving read performance.
• Caching: Implementing caching mechanisms can reduce the number of database queries, enhancing read operations' performance.
• Continuous Monitoring: Regularly monitor your database's performance and make necessary adjustments. This includes tweaking the database configuration, optimizing application code, or allocating additional resources.

In conclusion, while the connection limitation in Cloud SQL MySQL 5.7 Enterprise Version poses challenges, several strategies and workarounds can help you manage a high number of concurrent connections effectively.