MySQL must be tuned for 10 configuration MySQL Optimizer

From:http://www.opsers.org/server/10-configuration-mysql-mysql-must-adjust-optimization.html

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 active you need to make changes in the configuration file.

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 the configurations mentioned in this article belong to [mysqld])

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

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

Do not use naïve calculations, such as "Now my server's 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 higher the value, the better, which guarantees that you will use memory instead of the hard disk for most of the read operations. 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 crash recovery is greatly improved, so you can 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 is improved in MySQL 5.6.

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 needs to write data frequently and you are using 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 properly close the database connection and 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 that it becomes unresponsive when the server is running 1000 or higher active transactions. 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 if 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. 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 very many 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 the newly created table.

Innodb_flush_log_at_trx_commit: The default value is 1, which means that InnoDB fully supports acid characteristics. This value is most appropriate when your primary concern is data security, such as on a primary 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 (reliable) because the committed transaction is only flush once per second to the redo log, but it is acceptable for some scenarios, such as the value of the backup node for the primary node. 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. In general, if you have a hardware RAID controller and its standalone cache is write-back and has a battery power-down protection, you should set the configuration to O_direct; otherwise, you should make it Fdatasync (the default) in most cases. Sysbench is a great tool to help you 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 binary large objects or large text fields, this cache will quickly fill up and trigger 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 (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 start, set query_cache_size = 0 (now the default for MySQL 5.6) and use other methods to speed up the query: Optimize the index, increase the 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 stop using 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 to 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 applying 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 purge old files, or set Expire_logs_days to specify how many days the log will be automatically cleared.

Logging binary logs is not cost-free, so it is recommended to turn off this option if you do not need it on a copy node of a non-primary node.

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. Therefore, we recommend that you turn off the skip_name_resolve option when you start the server 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. The goal here, however, is to get a robust MySQL configuration quickly, without having to spend too much time tweaking some trivial MySQL settings or reading documents to find out which settings are important to you.