MySQL-10 configuration items that must be adjusted for newly installed MySQL (episode) and 10 configuration items for MySQL

MySQL-10 configuration items that must be adjusted for newly installed MySQL (episode) and 10 configuration items for MySQL

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46324419

I am still worried about the newly installed mysql service. I don't know which default configurations to modify? Mysql has more than 100 adjustable parameters. Please try now! Now! Which of the most important parameters are adjusted?

This article mainly introduces 10 configuration items that must be adjusted for MySQL optimization. Using these methods allows you to quickly obtain a stable MySQL configuration. For more information, see:

When we were hired to monitor MySQL performance, people wanted us to check MySQL configurations and give some suggestions for improvement. Many people are surprised afterwards, because we suggest that they modify only a few settings, even if there are hundreds of configuration items. The purpose of this article is to provide you with a list of important configuration items.

We gave such suggestions in our blog a few years ago, but the world of MySQL is changing too fast!

Before you start...

Even experienced people may make mistakes, which may cause a lot of trouble. Therefore, before blindly using these recommendations, remember the following content:

Only one setting is changed at a time! This is the only way to test whether changes are beneficial.

Most configurations can be changed using set global at runtime. This is a very convenient method that enables you to quickly cancel a change after a problem occurs. However, to take effect permanently, you need to make changes in the configuration file.

Does a change work even if MySQL is restarted?

Make sure you have used the correct configuration file. Are you sure you have placed the configuration in the correct region (all the configurations mentioned in this Article belong to [mysqld])

The server cannot start after a configuration change: Make sure you have used the correct unit.

For example, the unit of innodb_buffer_pool_size is MB, while that of max_connection is not.

Do not duplicate configuration items in a configuration file. If you want to track changes, use version control.

Do not use naive calculation methods. For example, "my server's memory is twice that of the previous one, so I have to change all the values to the previous two times".

I. basic configuration

You need to check the following three configuration items frequently. Otherwise, problems may occur soon.

Innodb_buffer_pool_size:

This is the first option you should set after installing InnoDB.

The buffer pool is the place where data and index caches are located: the larger the value, the better. This ensures that you use the memory instead of the hard disk for most read operations. The typical values are 5-6 GB (8 GB memory), 20-25 GB (32 GB memory), and 100-120 GB (GB memory ).

Innodb_log_file_size:

This is the size of the redo log. Redo logs are used to ensure that write operations are fast and reliable and can be recovered during crash.

Until MySQL 5.1, it is difficult to adjust, because on the one hand you want to make it larger to improve performance, on the other hand you want to make it smaller to make faster recovery after the crash. Fortunately, the crash recovery performance has been greatly improved since MySQL 5.5, so that you can have high Write Performance and crash recovery performance at the same time. Until MySQL 5.5, the total size of the redo log is limited to 4 GB (two log files can be created by default ). This is improved in MySQL 5.6.

In the beginning, setting innodb_log_file_size to 512 MB (with 1 GB of redo logs) will give you plenty of write operation space. If you know that your application needs to write data frequently and MySQL 5.6 is used, you can set it to 4 GB at the beginning.

Max_connections:

If you often see the 'too many ons' error, it is because the max_connections value is Too low. This is very common because the application does not properly close the database connection, and you need a greater value than the default 151 connections. A major defect after the max_connection value is set to a higher value (for example, 1000 or higher) is that when the server runs 1000 or higher active transactions, it will become unresponsive. Using a connection pool in an application or using a process pool in MySQL helps solve this problem.

Ii. InnoDB Configuration

Since MySQL 5.5, InnoDB is the default storage engine, and it is used much more than any other storage engine. That's why it needs to be configured with caution.

Innodb_file_per_table:

This setting tells InnoDB whether to store the data and indexes of all tables in the shared tablespace (innodb_file_per_table = OFF) or separately store the data of each table. ibd file (innodb_file_per_table = ON ). One file in each table allows you to reclaim disk space when you drop, truncate, or rebuild a table. This is also necessary for some advanced features, such as data compression. But it will not bring any performance benefits. The main scenario where you don't want each table to be a file is: There are a lot of tables (such as 10 k + ).

In MySQL 5.6, the default value of this attribute is ON, so you do not need to do anything in most cases. For previous versions, you must set this attribute to ON before loading data, because it only affects newly created tables.

Innodb_flush_log_at_trx_commit:

The default value is 1, indicating InnoDB fully supports the ACID feature. This value is most appropriate when your primary focus is on data security, for example, on a master node. However, for systems with slow disk (read/write) speeds, it will incur huge overhead, because each time the flush to redo logs are changed, additional fsyncs will be required. Setting its value to 2 will lead to unreliable (reliable) Because committed transactions are flushed to redo logs only once per second, but it is acceptable in some scenarios, for example, the value of backup node on the master node is acceptable. If the value is 0, it will be faster, but some data may be lost when the system crashes: Only applicable to backup nodes.

Innodb_flush_method:

This configuration determines how data and logs are written to the hard disk. Generally, if you have a hardware RAID Controller and Its Independent cache adopts the write-back mechanism, and the battery power-off protection is enabled, set it to O_DIRECT. Otherwise, in most cases, it should be set to fdatasync (default ). Sysbench is a good tool that helps you decide this option.

Innodb_log_buffer_size:

This configuration determines the cache allocated for unexecuted transactions. The default value (1 MB) is enough, but if your transaction contains a binary large object or a large text field, this cache will soon be filled up and trigger additional I/O operations. Check the Innodb_log_waits status variable. If it is not 0, add innodb_log_buffer_size.

3. Other settings Query_cache_size:

Query cache is a well-known bottleneck, even when there are not many concurrent queries.

The best option is to disable it from the very beginning. Set query_cache_size = 0 (the default value of MySQL 5.6 now) and use other methods to accelerate the query: optimize indexes, increase copy distributed loads, or enable additional caches (such as memcache or redis ). If you have enabled query cache for your application and haven't found any problems, query cache may be useful to you. If you want to stop it, you have to be careful.

Log_bin:

If you want the database server to act as the backup node of the master node, enabling binary logs is required. After doing so, do not forget to set server_id to a unique value. Even if there is only one server, if you want to recover data based on time points, this (enable binary logs) is also very useful: recover from your recent backup (full backup ), and apply modifications in binary logs (Incremental Backup ). Binary logs are permanently saved once they are created. Therefore, if you do not want to exhaust disk space, you can use purge binary logs to clear old files, or set expire_logs_days to specify how many days LOGS will be automatically cleared.

Recording binary logs does not have no overhead. If you do not need it on a replication node other than the master node, we recommend that you disable this option.

Skip_name_resolve:

When the client connects to the database server, the server will perform host name resolution, and when the DNS is slow, the connection will also be slow. Therefore, we recommend that you disable the skip_name_resolve option when starting the server without performing DNS lookup. The only restriction is that only IP addresses can be used in subsequent GRANT statements. Therefore, you must be careful when adding this setting to an existing system.

Summary

Of course there are other settings that can work, depending on your load or hardware: in the case of slow memory and fast disk, high concurrency and write-intensive load, you will need special adjustments. However, the goal here is to allow you to quickly obtain a stable MySQL configuration, instead of spending too much time adjusting irrelevant MySQL settings or reading documents to find out which settings are very important to you.