Optimize mysql servers and mysql servers

Optimize mysql servers and mysql servers

1. Run the show variables and show status commands to view the static server parameter values and dynamic running status information of MySQL.

2. You can use mysqld -- verbose -- help | more to view the definition of a parameter. Add grep filter.

3. key_buffer_size is used for the global index cache of the MyISAM engine.

4. Number of table caches opened by table_cache database users. You can use open_tables (the number of opened tables currently) to determine the size.

5. innodb_buffer_pool_size defines the maximum memory buffer size for table data and index data of the InnoDB Storage engine.

6. innodb_flush_log_at_trx_commit controls the operation time when data in the buffer zone is written to the log file and the data in the log file is refreshed to the disk. Affects performance and data security.

The default value is 1, which is the safest.

7. innodb_additional_mem_pool_size the size of the memory pool storing the data structure and other internal data structures will automatically increase, and there is no need to increase too much.

8. When innodb_lock_wait_timeout is deadlocked, roll back after the specified time.

9. Whether innodb_support_xa supports distributed transactions. It is supported by default.

10. innodb_log_buffer_size: the log cache size. Set the memory size for one second.

11. innodb_log_file_size the size of files in each day in a log group.