MySQL optimization II: My SQL Server Performance optimization

1 Installation optimization

In general, the more complex the system functions, the worse the performance will be. Therefore, when compiling and installing MySQL, only the required function modules are installed. such as the storage engine, the required character set, and so on, make the system as simple as possible.

2nd Log Set Optimization

Because the performance loss caused by logging directly is the most expensive I/O resource in the database, MySQL only turns on the error log by default and closes all other logs. In a production environment, however, at a minimum, binary and slow query logs need to be turned on, which is the basis for incremental backups, which facilitates further optimization of the database. Typically, a generic query log is rarely turned on in a production environment, and if opened, the database records every action record that has a significant impact on system I/O performance.

To view binary log settings, turn off by default:

Mysql> Show variables like '%log%bin% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| Log_bin | OFF |

|       Log_bin_basename | |

|       Log_bin_index | |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| Sql_log_bin | On |

+---------------------------------+-------+

6 rows in Set (0.00 sec)

To view the slow query log settings, close by default:

Mysql> Show variables like "slow%";

+---------------------+-----------------------------+

| variable_name | Value |

+---------------------+-----------------------------+

| Slow_launch_time | 2 |

| Slow_query_log | OFF |

| Slow_query_log_file | /data/mysql/slave2-slow.log |

+---------------------+-----------------------------+

3 Memory optimization

Key_buffer_size:myisam engine index cache size, 4G memory recommended set to 256M, maximum not more than 4G

Read_buffer_size: The buffer size used by the read query (sequential read), which defaults to 128KB, is not too large for each connection, and should be tested multiple times in a production environment to find the best value (in multiples of 4KB)

Read_rnd_buffer_size: Read query (Random read) using the buffer size, the default is 256KB, the same is each connection exclusive, generally speaking, can be properly adjusted

Max_connections:mysql Maximum number of connections allowed, default is 151, 500-800 more appropriate if host performance and memory are allowed

Maximum number of connection threads that can be cached in the Thread_cache_size:thread_cache pool, within 100 recommended

This article from "10,000 years too long, seize" blog, please be sure to keep this source http://zengwj1949.blog.51cto.com/10747365/1920738

MySQL optimization II: My SQL Server Performance optimization