Optimizing MySQL Server

First, use the show variables and show status commands to view MySQL's server static parameter values and dynamic run state information.

Second, you can use mysqld--verbose--help|more to view the definition of a parameter. Plus grep filtering.

Key_buffer_size the global index cache for the MyISAM engine.

Iv. Table_cache The number of cached database user open tables. You can use Open_tables (the number of currently open tables) to decide the size.

Innodb_buffer_pool_size defines the maximum memory buffer size of the table data and index data for the INNODB storage engine.

Six, Innodb_flush_log_at_trx_commit control the data in the buffer write to the log file and log file data flushing to disk operation time. Impact performance and data security.

The default is 1, which is the safest.

Seven, innodb_additional_mem_pool_size storage data structure and other internal data structure of the memory pool size, will automatically grow, not necessarily too large.

Viii. Innodb_lock_wait_timeout waits for a specified time to roll back when a deadlock occurs.

Ix. Innodb_support_xa supports distributed transactions, supported by default.

Ten, innodb_log_buffer_size log cache size, set the required memory space for one second.

Xi. innodb_log_file_size the size of each day file in a log group.

Optimizing MySQL Server