Hello.
The server I'm using is (8 cores, 32gb, linux debian) with mysql 5.5.31. It can handle several thousand requests at a time without any issues.
In the morning, everything runs smoothly. However, from 15:30 onwards, the load increases and nginx continuously generates a 502 error.

During normal operation:
screencast.com/t/ZpATtcji
On average, the query duration is 0.3sec. This means that the MYSQL query itself is not too heavy.

During problematic times:
screencast.com/t/PsUD9fIssttL

At the same time, the top command shows the CPU load of the mysql process ranging from 100% to 350%.
What's even worse is that it sometimes gives the error message "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (11)."

I've attempted to configure mysql on my own using various Internet manuals, but without much success. The problems persisted. I don't have experience with setting up highly loaded servers yet.

To resolve the error "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (11)," I tried installing open_files_limit 8192, but it didn't make a difference. It seems like something needs to be fixed in Linux itself, but it's unclear what exactly.

There is nothing noteworthy in the apache and mysql logs. There are no errors, and it seems like logging is not working properly.

Clearly, I've missed something. The server configuration is not weak at all, so these 2-3 thousand connections are probably not the limit.

Thank you in advance for your assistance.

First and foremost, it is essential to ensure the proper organization of the database by placing indexes where they are needed.
Moreover, optimizing the application itself can eliminate the need for numerous requests.
Enabling the slow query log in MySQL can be beneficial.

Additionally, MySQL offers an option to set the maximum number of simultaneous connections. However, it is advisable not to modify this setting unless necessary. If all other aspects are optimized properly, the default value should suffice for optimal performance.

0) Are you confident that the error is specifically related to MySQL? It's worth considering if there may be any issues with PHP (if implemented) or with Nginx itself. Have you checked their respective logs?

1) Take a look at persistent MySQL connections for further information.

2) Consider enabling query logging without indexes.

3) Familiarize yourself with the recommendations from tools like mysqltuner/tuning-primer; they often provide helpful advice.

4) Check both the server and web server logs, as they may indicate any complaints about the number of open files. If necessary, adjust the limits in the system.

5) Allocate more memory to MySQL, especially for InnoDB.

6) It appears that the timeouts are set rather long (4 hours). Perhaps there are multiple unused processes lingering.

7) Connect to the MySQL console and view the list of processes using "show processlist;". This will provide insight into what's happening, and you may immediately notice any locks.

Switching to TCP/IP can potentially resolve the open file error, but it may also affect overall performance.

    While local connections minimize the involvement of the entire IP stack, they still rely on a significant portion of it. A Unix socket serves as a lightweight, faster alternative.

Regardless, this is merely a temporary workaround and not a definite solution. Eventually, the same error may manifest elsewhere.

9) Observe the iowait values in top/iotop; the issue may lie in the disk subsystem rather than the queries. If that's the case, moving MySQL to a separate partition could be beneficial.

10) Monitor the CPU load (press 1 in top); it is possible that one or two processors are heavily utilized while the rest remain idle. In such scenarios, distributing tasks proportionally may be advantageous.

Is this a web server? If so, the initial step is to examine the code of the web application and identify any areas where it encounters obstacles or experiences significant delays, particularly in relation to the database. This will provide insights into problematic queries and help pinpoint bottlenecks.

Additionally, analyze the URLs that trigger the 502 error. If there is a common pattern among them, consider implementing logging for the specific URL and investigate the relevant logs to uncover any underlying issues.

The high CPU load and the error message "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (11)" indicate that there might be some underlying issues that need to be addressed.

First, let's address the connection error. This error message usually occurs when the MySQL server is not running or the socket file is missing. You can try restarting the MySQL service to see if that resolves the issue. If the problem persists, you may need to check the MySQL configuration file and ensure that the socket file path is correct.

Regarding the high CPU load, it could be caused by various factors, such as inefficient queries, inadequate indexing, or insufficient server resources. To investigate this further, you can enable slow query logging in MySQL to identify any queries that are taking a long time to execute. This will help you optimize those queries or identify problematic areas in your database schema.

Additionally, you mentioned that you have tried adjusting the open_files_limit setting, but it didn't make a difference. In that case, it's possible that the issue lies elsewhere. You might want to check other MySQL configuration parameters such as max_connections or innodb_buffer_pool_size to ensure they are properly configured for your workload.

Lastly, it's important to monitor your server's resource utilization during peak times. This includes CPU, memory, disk I/O, and network usage. By analyzing these metrics, you can identify any bottlenecks and take appropriate action, such as adding more resources or optimizing your application.

Here are a few additional steps you can take to further investigate and resolve the performance issues you're experiencing with your MySQL server:

1. Check MySQL Configuration: Review your my.cnf (or my.ini) configuration file and ensure that it is properly optimized for your server's resources. Pay attention to settings like innodb_buffer_pool_size, key_buffer_size, and thread_cache_size. These values should be set based on the available memory and the size of your database.

2. Monitor Resource Usage: Continuously monitor the resource utilization of your server during peak times. Use tools like top or htop to track CPU usage, memory consumption, and disk I/O. This information can help you identify any resource bottlenecks that may be impacting the performance of your MySQL server.

3. Optimize Queries: Analyze your application's queries, especially those that are frequently executed and consume a significant amount of resources. Ensure that these queries are properly indexed and consider rewriting them if necessary. You can use the EXPLAIN statement in MySQL to analyze query execution plans and identify areas for optimization.

4. Enable Query Cache: Enable the query cache in MySQL if it is not already enabled. The query cache can help improve performance by caching the results of frequently executed queries. However, keep in mind that the query cache might not be beneficial for all workloads, so evaluate its impact and adjust the configuration accordingly.

5. Consider InnoDB Buffer Pool: If you are using the InnoDB storage engine in MySQL, ensure that the innodb_buffer_pool_size is appropriately configured. This setting determines the amount of memory allocated for caching data and indexes. Setting it too low can result in excessive disk I/O, while setting it too high can lead to memory contention.

6. Analyze Slow Query Log: Enable the slow query log in MySQL to identify queries that are taking a long time to execute. Once identified, optimize these queries by adding appropriate indexes, rewriting them, or adjusting the schema if necessary. The slow query log can provide valuable insights into potential performance bottlenecks.

7. Consider Database Sharding: If your database is growing rapidly and you're experiencing scalability issues, you may consider implementing database sharding. Sharding involves splitting your data across multiple servers, allowing for better distribution of the workload and improved performance.