MySQL Assistant configuration

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

The read operation is using memory instead of the hard disk. 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 one side

Face 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 is greatly improved so that you can simultaneously

has high write performance and crash recovery performance. 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

When 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 have the correct number of shutdowns

You need a larger value than the default number of 151 connections, according to the library connection.

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 connection pooling 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 for each table

Data 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 for some advanced features

is also necessary, 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 is only for new

The table created has an impact.

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 in a

On the 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 causes no

Too reliable (unreliable) because the committed transaction is only flush once per second to the redo log, it is acceptable for some scenarios, such as the value of the backup node for the primary node. If

A value of 0 is faster, but some data may be lost 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 the write-back mechanism, and has

Battery power-off protection, the configuration should be set to o_direct; otherwise, it should be Fdatasync (the default) in most cases. Sysbench is a good job to help you decide this option.

With.

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

object or a large text field, this cache is quickly filled and triggers additional I/O operations. Look at the innodb_log_waits state variable, if it's not 0, increase

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, setting

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 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 binary log

Modify (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 erase 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. We recommend that you close the server when you start it

The Skip_name_resolve option does not perform 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 the goal here is to make

You can quickly get a robust MySQL configuration without having to spend too much time tweaking some trivial MySQL settings or reading documents to find out which settings are important to you.

Technology sharing: www.kaige123.com

MySQL Assistant configuration