The number of MySQL handles is too high

MySQL is installed under Windows, using the official configuration wizard to generate my.ini, I thought it was very stable, but after more than 10 hours, the system response is very slow, look at the performance card of the resource manager, found that the number of handles has reached 100,000! No wonder whatever program you use is stuck.

innodb_buffer_pool_size The default 8M is too big, but I already have skip-innodb.

Then you see a setting innodb_flush_log_at_trx_commit

ES26en_ES27en_ES28en_ES29en_ES30en_commit (this works well)

Complaining about Innodb being 100 times slower than MyISAM? So you probably forgot to adjust this value. The default value of 1 means that every transaction commit or out-of-transaction instruction needs to write the log to the disk (flush), which can be time-consuming. Especially when using battery-powered cache (Battery backed up cache). A set of 2 is fine for many applications, especially for transfers from MyISAM tables. It means not to write to disk but to the system cache. The log will still be sending ES43en to disk every second, so you won't lose more than 1-2 seconds of updates. Setting it to 0 will make it 1 point faster, but it is less secure and may lose transaction data even if MySQL is suspended. A value of 2 will only lose data if the entire operating system is down.

It was later set to innodb_flush_log_at_trx_commit = 2.

Restart the MYSQL service, indeed much better, the number of handles eventually remained at about 20,000, in a stable state, other programs are much faster.

It seems that innodb in the configuration file, even if skip dropped, is still using the memory in the system, disk IO, it seems that the configuration of MYSQL or to study manually, can not completely rely on its own configuration tools.

To summarize, for innodb libraries, setting innodb_flush_log_at_trx_commit to 2 can improve the performance of MySQL and solve the problem of excessive handle usage.