10 configurations that must be adjusted after MySQL is installed

English Original: MySQL settings to tune after installation

10 configurations that must be adjusted after installing MySQL

When we were hired to monitor MySQL performance, people wanted us to look at the MySQL configuration and give some suggestions for improvement. Many people were very surprised afterwards because we suggested that they

Just change a few settings, even if there are hundreds of configuration items here. The goal of this article is to give you a very important list of configuration items.

We gave this advice in our blog a few years ago, but the world of MySQL is changing so fast!

Write before the start ...

Even seasoned people can make mistakes and cause a lot of trouble. So before you blindly apply these recommendations, 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 handy way to quickly undo changes after a problem has been made. However, to be permanently effective you need to

to be in the configuration file make changes.

• A change that doesn't work even if you restart MySQL? Make sure you are using the correct configuration file. Make sure you put the configuration in the correct area (all this article

the configuration mentioned is all part of the [mysqld])

• The server does not start after changing a configuration: Make sure you are using the correct unit. For example, innodb_buffer_pool_size units are MB and max_connection

There are no units.

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

Do not use the real calculation method, for example, "Now my server memory is twice times before, so I have to change all the values to twice times before."

Basic Configuration

You need to look at the following 3 configuration items frequently. Otherwise, it may soon be a problem.

innodb_buffer_pool_size: This is the first option you should set when you finish installing InnoDB. The buffer pool is where the data and index caches are: the larger the value, the better, which guarantees that you will

Most read operations use memory instead of hard disks. Typical values are 5-6GB (8GB memory), 20-25GB (32GB memory), 100-120GB (128GB memory)

innodb_log_file_size : This is the size of the redo log. The redo log is used to ensure that the write operation is fast and reliable and recovers when it crashes. Until MySQL 5.1, it's hard to adjust,

Because on the one hand you want to make it bigger to improve performance, on the other hand you want it to be smaller to make it faster to recover after a crash. Fortunately, after MySQL 5.5, the performance of the crash recovery was greatly improved,

This allows you to have high write performance and crash recovery performance at the same time. Up to MySQL the total size of the 5.5,redo log is limited to 4GB (the default can be 2 log files). This in

MySQL 5.6 is improved. Setting the Innodb_log_file_size to 512M in the first place (so there is a 1GB redo log) will give you ample space to write. If you know that your application

The sequence requires frequent write data and when you use MySQL 5.6, You can start by turning it into 4G.

max_connections: If you often see the ' Too many connections ' error, it is because the value of max_connections is too low. This is very common because the application does not have the correct

To close the database connection, you need a value that is larger than the default number of 151 connections. A major drawback after the Max_connection value is set higher (for example, 1000 or higher) is when the server is running 1000

or higher active transactions will become unresponsive. Using a connection pool in your application or using a process pool in MySQL can help solve this problem.

InnoDB Configuration

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

innodb_file_per_table: This setting tells InnoDB whether the data and indexes of all tables need to be stored in the shared tablespace (innodb_file_per_table = OFF) or

The data for each table is placed separately in an. ibd file (innodb_file_per_table = on). One file per table allows you to reclaim disk space when you drop, truncate, or rebuild tables

Room This is also necessary for some advanced features, such as data compression. But it doesn't bring any performance gains. The main scenario where you don't want each table to be a file is: There are a lot of tables

(e.g. 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 the previous version you must add this property before loading the data

Set to on because it affects only the newly created table.

innodb_flush_log_at_trx_commit: The default value is 1, which means that InnoDB fully supports acid characteristics. When your primary concern is data security, this value is the most appropriate.

, such as on a master node. However, for a system with slow disk (read-write), it can be costly because additional fsyncs are required each time the change is flush to the redo log.

Setting its value to 2 results in less reliable (unreliable) because the committed transaction is only flush once per second to the redo log, but it is acceptable for some scenarios, such as for the main section

The value of the point's backup node is acceptable. If the value is 0 faster, you may lose some data when the system crashes: only for 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 standalone cache uses Write-back

Mechanism and has a battery power-off protection, the configuration should be set to o_direct; otherwise, it should be Fdatasync (the default) in most cases. Sysbench is one that can help

A good tool for you to decide on this option.

innodb_log_buffer_size: This configuration determines the cache that is allocated for transactions that have not yet been executed. Its default value (1MB) is generally sufficient, but if your transaction contains a

Binary large objects or large text fields, this cache is quickly filled and triggers additional I/O operations. Look at the innodb_log_waits state variable, if it is not 0, increase the innodb_log_buffer_size.

Other settings

query_cache_size: Query cache is a well-known bottleneck, even when there is not a lot of concurrency. The best option is to deactivate it from the beginning,

Set query_cache_size = 0 (now the default for MySQL 5.6) and use other methods to speed up queries: Optimize indexes, increase copy spread load, or enable additional caches (such as

Memcache or Redis). If you have enabled query cache for your app and have not found any problems, query cache may be useful to you. This is if you want to deactivate it,

Then you have to be careful.

Log_bin: If you want the database server to act as a backup node for the master node, it is necessary to turn on the binary log. If you do this, don't forget to set server_id as a

A unique value. Even if you have only one server, if you want to do data recovery based on point-in-time, this (turning on binary logging) is also useful: recovering from your most recent backup (full backup),

and apply the modifications in the binary log (incremental backup). Once the binary log is created, it is permanently saved. So if you don't want to run out of disk space, you can use PURGE BINARY LOGS

To clear the old file, or set expire_logs_days to specify how many days the log will be automatically cleared. Logging binary logs is not without overhead, so if you are in a non-master node

is not required on the replication node, it is recommended to turn this option off.

skip_name_resolve: When a client connects to a database server, the server makes host name resolution, and when DNS is slow, establishing a connection can be slow. It is therefore recommended that the boot service

This option is turned off without DNS lookups. The only limitation is that only IP addresses can be used later in the GRANT statement, so you must be extra careful in adding this setting to an existing system

Summarize

Of course, there are other settings that can work, depending on your load or hardware: You'll need special tweaks in slow and fast disks, high concurrency, and write-intensive loads. But here's the

The goal is to allow you to quickly get a robust MySQL configuration without spending too much time tweaking some of the irrelevant MySQL settings or reading documents to find out which settings are very

Important on.

10 configurations (RPM) that must be adjusted after MySQL is installed