MySQL: Too Many Threads Too Little Time（mysql线程过多）

MySQL is a popular open source relational database management system (RDBMS) used by many companies. The strength of MySQL lies in its multi-threading system. The fact that MySQL can operate on different threads at the same time can lead to being an efficient solution to a lot of queries.

But, it’s possible to have too many threads running at once, with “Too Many Threads Too Little Time” being a common complaint for system administrators who manage MySQL clusters. When too many threads are running, it can lead to a decrease in the speed of MySQL queries, locking up resources and leading to a lack of performance.

One of the main causes of having too many threads running is improper configuration. This can occur when the configurations are set too high, leading to too many threads being initiated. If a query is very intensive then this will lead to resource contention. To alleviate this issue, MySQL allows the system administrator to set an upper limit on the number of threads that can access a certain system.

Additionally, an inefficient query can increase the number of threads running, even if the configuration is set correctly. Inefficient queries are those that query the entire table, or the entire database, instead of querying just a few specific fields. Filtering results is important to make sure the query is only getting the data it needs and that the same query is not being executed multiple times.

In addition to the configuration settings, there are other steps that can be taken to ensure that MySQL is running with an optimal number of threads. One example is to ensure that MySQL is properly tuned. Tuning involves adjusting the internal statistics, adjusting the buffer size, and optimizing all of the parameters based on the specific data, query, and index specifications. This process can help improve the efficiency of smaller queries and keep the number of threads running to a minimum.

It’s important to also look into “read-only” or “write-only” configurations. This distributes the load placed upon a MySQL instance among multiple threads, avoiding lock contention, as well as limiting any danger of deadlocks.

Finally, MySQL provides access to “thread pooling”, which restricts the number of threads based on certain criteria. This can be used to limit the number of queries that are running in the background, and prevent them from scaling up beyond the limits of the system.

All in all, too many threads too little time can be a problem, but there are measures in place to limit this issue and ensure that MySQL is operating at optimal performance levels without overwhelming resources. With the correct configuration, tuning and query optimization, it’s possible to keep MySQL running at its best.