The number of MySQL handles occupies too much.

The number of MySQL handles occupies too much.

Install MySQL in Windows and use the official Configuration Wizard to generate my. ini, I thought it was very stable. Who knows that after more than 10 hours, the system response was very slow. Looking at the performance card of the resource manager, I found that the number of handles reached 0.1 million! No wonder no matter what program is used, it gets stuck.

Search for innodb_buffer_pool_size. The default 8 m is too large, but I already have skip-innodb.

Later, I saw another setting of innodb_flush_log_at_trx_commit.

Innodb_flush_log_at_trx_commit (this is useful)

Why is Innodb 100 times slower than MyISAM? You probably forgot to adjust this value. The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. This is time-consuming. Especially when Battery backed up cache is used. Set to 2 is applicable to many applications, especially the conversion from the MyISAM table. It means writing data to the system cache instead of writing data to the hard disk. Logs are flushed to the hard disk every second, so you will not lose updates that exceed 1-2 seconds. Setting 0 is faster, but the security is poor. Even if MySQL fails, the transaction data may be lost. Value 2 can only lose data when the entire operating system is down.

　　Later it was set to innodb_flush_log_at_trx_commit = 2.

The restart of the MYSQL service is indeed much better. The number of handles is eventually around 20 thousand, which is stable and other programs are much faster.

It seems that innodb in the configuration file still uses the system memory and disk IO even if the skip is lost. It seems that the configuration of MYSQL still needs to be studied manually, it cannot depend entirely on the built-in Configuration tool.

To sum up, for innodb databases, setting innodb_flush_log_at_trx_commit to 2 can improve MySQL performance and solve the problem of occupying too many handles.

The above is the solution to occupying too many MySQL handles. I hope to give you a reference and learn more about MySQL syntax. You can follow MySQL 5.1 reference manual and hope you can support more help.