Shulou(Shulou.com)06/01 Report--

1 installation optimization

In general, the more complex the system is, the worse the performance will be. Therefore, when compiling and installing MySQL, only the required functional modules are installed. Such as the storage engine, the required character set, etc., to make the system as simple as possible.

2 optimization of log settings

Because the direct performance loss caused by logging is the most expensive I _ mano resource in the database, by default, MySQL only turns on the error log and closes all other logs. However, in a production environment, it is necessary to open at least binary logs and slow query logs, the former is the basis of incremental backup, while the latter is conducive to further optimization of the database. In general, the general query log is rarely opened in the production environment, and if it is opened, the database will record each operation record, which has a great impact on the performance of the system.

View the binary log settings, which are turned 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)

View the slow query log settings, which are turned off 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

The size of the key_buffer_size:MyISAM engine index cache. It is recommended to set the 4G memory to 256m, with a maximum of no more than 4G.

Read_buffer_size: the buffer size used by the read query (sequential read). The default is 128KB. This value is exclusive to each connection and therefore cannot be too large. In a production environment, multiple tests should be performed to find the best value (a multiple of 4KB).

Read_rnd_buffer_size: the buffer size used by the read query (random read). The default is 256KB, which is also exclusive to each connection. Generally speaking, it can be adjusted appropriately.

The maximum number of connections allowed by max_connections:MySQL. Default is 151.500800 is more appropriate if host performance and memory allow.

The maximum number of connection threads that can be cached in the thread_cache_size:Thread_cache pool. It is recommended to be less than 100.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.