MySQL is experiencing a strange issue where only about half of the cores are being loaded, not reaching full capacity. Suddenly, the load increases significantly and causes delays in responses. This lasts for 10-50 seconds before returning to normal. The cause of this issue is unclear and has been observed before, suggesting that it may not be related to the version of the kernel, distribution, or MySQL.

Monitoring tools show that there is a high system call load, indicating a large number of calls to the kernel, intensive memory allocation, or I/O activity. However, it is challenging to monitor the specific core calls. Memory usage does not seem to be significantly allocated or taken away. I/O activity appears relatively normal.

During the glitch, the database requests are typical, without any noticeable changes in selection/update ratios or specific table requests. It was considered that intermittent tasks might be causing the issue, but stopping them did not solve the problem.

The server specifications are robust, with 2 x Xeon E5-2680v3 processors, 64GB DDR4 RAM, and a fast SSD enterprise-level storage. The operating system is Centos 7 with kernel 3.10, and Percona 5.7 is used as the MySQL version. There is nothing else running on the server except MySQL. Previously, a weaker server was used, which also experienced similar periodic issues that were temporarily resolved with MySQL configuration parameters.

Attempts to solve the problem include restarting MySQL (which provides temporary relief), restarting the server (without any effect), and tuning various parameters based on default values, the old server's configuration, and recommendations from the mysqltuner utility. None of these attempts have solved the problem.

It is important to note that the issue only occurs during rush hour when the load on the MySQL server is at its highest. During the rest of the day, everything works fine.

They are not a database administrator and do not have in-depth knowledge of MySQL and InnoDB internals. They are a Linux administrator seeking help to resolve the problem.


The MySQL configuration shown below consists of various settings for the database. The config includes parameters such as bind-address, datadir, socket, user, symbolic-links, innodb_buffer_pool_size, innodb_log_file_size, innodb_flush_method, innodb_flush_log_at_trx_commit, sql-mode, query_cache_size, join_buffer_size, thread_cache_size, max_connections, open_files_limit, explicit_defaults_for_timestamp, max_allowed_packet, log-error, log_error_verbosity, and more. These settings control different aspects of MySQL's behavior.

One suspected issue is that the configuration may be causing MySQL to request more memory than available, leading to potential glitches and performance problems.

To better understand the situation, the author suggests publishing a screenshot of the MySQL Workbench Dashboard during the buggy periods or Performance Statistics. However, they express their lack of understanding regarding these technical aspects and their desire to resolve the glitch and gain a better understanding of its cause.

It's important to note that troubleshooting and resolving such issues can be complex, often requiring in-depth knowledge of MySQL internals and system administration. Seeking assistance from experienced professionals or consulting relevant dоcumentation is highly recommended.

Based on the information provided, it seems that there is a high system call load during the glitch, indicating intensive memory allocation, I/O activity, or calls to the kernel. However, it is challenging to monitor the specific core calls, and memory usage and I/O activity appear relatively normal.

You have already tried various attempts to solve the problem, including restarting MySQL, restarting the server, and tuning different parameters based on default values, previous server's configuration, and recommendations. However, none of these attempts have resolved the issue.

One potential issue could be that the MySQL configuration is requesting more memory than available, leading to glitches and performance problems. It might be helpful to review the configuration settings and ensure they are appropriately set for your server specifications.

As you mentioned that you lack in-depth knowledge of MySQL internals and InnoDB, seeking assistance from experienced professionals or consulting relevant dоcumentation would be highly recommended. Troubleshooting and resolving such issues can be complex, and having expert guidance will likely help in identifying and resolving the root cause of the problem.

In addition to seeking assistance from professionals or consulting dоcumentation, here are a few more general troubleshooting steps you can try:

1. Monitor system resources: Use tools like top, htop, or sysstat to monitor CPU, memory, and I/O activity during the glitch periods. Look for any abnormal spikes or patterns that could indicate resource constraints.

2. Check error logs: Review the MySQL error logs for any error messages or warnings that could provide hints about the cause of the issue. Pay attention to any specific timestamps that coincide with the glitch periods.

3. Enable slow query logging: Enable the slow query log in MySQL to identify any queries that may be causing performance issues. Analyzing slow queries can help identify potential bottlenecks or inefficient queries that could be contributing to the problem.

4. Enable general query logging: Enable the general query log in MySQL to capture all queries being executed during the glitch periods. This can help identify any unusual queries or patterns that may shed light on the issue.

5. Enable MySQL performance schema: Enable the MySQL performance schema to gather detailed performance metrics and statistics about MySQL operations. This can provide valuable insights into how MySQL is utilizing system resources and identify any potential bottlenecks.

6. Review system and MySQL configurations: Double-check your system and MySQL configurations to ensure they are properly set based on your server specifications and workload. Pay attention to settings related to buffer pool size, log file size, thread cache size, max connections, and other relevant parameters.

7. Perform a stress test: Consider running a stress test on your MySQL server during a non-peak period to simulate high load conditions and observe how it behaves. This can help identify any performance issues or limitations under heavy load.

Did you Google the leap second error? One of its symptoms could be abnormal loading of the percentage.

Another option is to check how NUMA is configured and how it functions. It might be worth enabling interleave in mysqld_safe settings (via numactl).

What about IO? What is the disk load? Take a look at iotop, for instance.

Let's talk about the parameters. Besides the muscle, is there anything else running on the server? If not, you can try reducing innodb_buffer_pool_size to about 70% of the RAM volume.

innodb_log_file_size determines the size of the innodb transaction log. The larger it is, the less often this file needs to be recreated, resulting in less disk load. However, the recovery time in case of a failure will be longer. A size of 512mb should be sufficient.

Are you absolutely sure about innodb_flush_log_at_trx_commit = 0? It's better to set it to at least 2 - it will provide the same performance but is safer. Ideally, set it to 1. It may be slower, but more reliable.

As for query_cache_size = 4096M, why is it so high? Remember that with every INSERT/UPDATE, this cache gets overwritten. Start with a value of 100mb.

The situation will likely require debugging at the request modification level within the application. As previously mentioned, during debugging, it is important to enable and analyze the log of slow queries. Some modifications may involve adding indexes or replacing regular table readings with temporary table readings. SQL itself is regulated by the tool EXPLAIN, which has been used throughout history by countless people.

One specific aspect worth noting is the issue of locks. I have frequently encountered situations where a high volume of read requests blocks write requests (or vice versa), leading to a queue overload under heavy load. The main processing power is then consumed by managing this queue. From my personal experience working as a technician in a hosting office, this problem often occurs with websites based on WordPress. While the WordPress engine is generally optimized well, the issue lies with the multitude of plugins created by different authors.


This is especially true for plugins related to statistics collection and calculation. These plugins often generate conflicting read and write requests when generating a single page. At times, the intensity of these requests leads to locks, causing two competing PHP instances to block each other's work. In such cases, limiting the number of PHP instances to one is necessary for proper functioning. Simply upgrading to a more powerful or cloud server will not solve this problem. It is inefficient and costly to attempt to solve logical errors by increasing hardware capacity. Therefore, any increase in server capacity is only a temporary solution that can delay the debugging process for a few days at most. When designing applications, whether web-based, RESTful, or standalone, it is crucial to understand the nature of each action, specifically whether it involves writing to or reading from the database. If multiple actions require extensive reading and writing from the same tables, it inevitably leads to locking issues.

In terms of troubleshooting MySQL problems, it is futile to rely on tools for analyzing I/O events, as MySQL already effectively manages I/O without overwhelming the kernel. However, there are many non-system settings in MySQL that can be inefficiently configured, and addressing these settings can help resolve the problem.

Lastly, it is worth considering that in most situations, MyISAM tables perform significantly faster than InnoDB tables.