Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "which configurations need to be adjusted after installing MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Write before you begin...

Even experienced people can make mistakes and cause a lot of trouble. So before you apply these recommendations blindly, keep in mind the following:

Change only one setting at a time! This is the only way to test whether the change is beneficial.

Most configurations can be changed using SET GLOBAL at run time. This is a very convenient way to undo changes quickly after something goes wrong. However, to take effect permanently, you need to make changes in the configuration file.

A change doesn't work even if MySQL is restarted? Please make sure that you use the correct configuration file. Make sure you put the configuration in the correct area (all the configurations mentioned in this article belong to [mysqld])

The server will not boot after changing a configuration: please make sure you are using the correct unit. For example, the unit of innodb_buffer_pool_size is MB while max_connection has no unit.

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

Don't use naive calculations, such as "now my server has twice as much memory, so I have to change all the values to twice as much as before."

Basic configuration

You need to review the following 3 configuration items frequently. Otherwise, something may go wrong soon.

Innodb_buffer_pool_size: this is the first option you should set after you install InnoDB. A buffer pool is where data and indexes are cached: the higher the value, the better, which ensures that you use memory instead of the hard drive for most read operations. Typical values are 5-6GB (8GB memory), 20-25GB (32GB memory), and 100-120GB (128GB memory).

Innodb_log_file_size: this is the size of the redo log. Redo logs are used to ensure that writes are fast and reliable and recover in the event of a crash. Up to 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 it recover faster after a crash. Fortunately, crash recovery performance has been greatly improved since MySQL 5.5, so you can have higher write performance and crash recovery performance at the same time. Until the total size of the MySQL 5.5 redo log is limited to 4GB (there can be 2 log files by default). This is improved in MySQL 5.6.

Setting innodb_log_file_size to 512m at the beginning (so that you have 1GB's redo log) will give you plenty of room to write. If you know that your application needs to write data frequently and you use MySQL 5.6, you can start by turning it into 4G.

Max_connections: if you often see Too many connections' errors, it's because the value of max_connections is too low. This is very common because the application does not close the database connection correctly, and you need a higher value than the default number of 151 connections. A major drawback after the max_ connection value is set high (for example, 1000 or higher) is that the server becomes unresponsive when running 1000 or more active transactions. Using connection pooling in applications or process pooling in MySQL can help solve this problem.

InnoDB configuration

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

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

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

Innodb_flush_log_at_trx_commit: the default value is 1, which means that InnoDB fully supports the ACID feature. This value is most appropriate when your main concern is data security, such as on a primary node. But for systems with slow disk (read and write) speeds, it can be costly because additional fsyncs is required for each change from flush to redo logs. Setting it to 2 makes it unreliable (reliable) because committed transactions only flush to the redo log once a second, but it is acceptable for some scenarios, such as for the backup node of the primary node. A value of 0 is faster, but some data may be lost in the event of a system crash: only for backup nodes.

Innodb_flush_method: this configuration determines how data and logs are written to the hard disk. In general, if you have a hardware RAID controller, and its independent cache uses write-back mechanism, and has battery power-off protection, then you should set it to fdatasync; otherwise, you should set it to fdatasync (the default) in most cases. Sysbench is a good tool to help you decide this option.

Innodb_log_buffer_size: this configuration determines the cache allocated for transactions that have not yet been executed. Its default value (1MB) is generally sufficient, but if your transaction contains binary large objects or large text fields, the cache will quickly fill up and trigger additional Icano operations. Take a look at the Innodb_log_waits state variable, and if it is not 0, add innodb_log_buffer_size.

Other settin

Query_cache_size: query cache (query caching) is a well-known bottleneck, even when concurrency is not high. The best option is to deactivate it from the start, set query_cache_size = 0 (now the default of MySQL 5.6) and speed up the query in other ways: optimize the index, increase the copy to spread the load, or enable additional caching (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 using it, you have to be careful.

Log_bin: if you want the database server to act as a backup node for the primary node, it is necessary to turn on binary logging. If you do, don't forget to set server_id to a unique value. Even if there is only one server, this (open binary log) is useful if you want to do point-in-time data recovery: restore from your most recent backup (full backup) and apply changes in the binary log (incremental backup). Once the binary log is created, it will be saved permanently. So if you don't want to run out of disk space, you can use PURGE BINARY LOGS to clean up old files, or set expire_logs_days to specify how many days after the log will be cleared automatically.

Recording binary logs is not without overhead, so it is recommended that you turn off this option if you do not need it on a replicated node that is not the primary node.

Skip_name_resolve: when the client connects to the database server, the server does hostname resolution, and when DNS is slow, it is slow to establish a connection. Therefore, it is recommended that you turn off the skip_name_resolve option when starting the server without doing DNS lookups. The only limitation is that only IP addresses can be used in subsequent GRANT statements, so you have to 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 disks, high concurrency and write-intensive loads, you will need special adjustments. The goal here, however, is to enable you to quickly get a robust MySQL configuration without spending too much time adjusting unimportant MySQL settings or reading the documentation to find out which settings are important to you.

This is the end of the content of "what configurations need to be adjusted after installing MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.