MySQL Configuration optimization

MySQL configuration file optimization

[Client]

Port = 3306 #客户端端口号为3306

Socket =/data/3306/mysql.sock #

Default-character-set = UTF8 #客户端字符集, (Control character_set_client, Character_set_connection, Character_set_results)

[MySQL]

No-auto-rehash #仅仅允许使用键值的updates和deletes

[Mysqld] #组包括了mysqld服务启动的参数, it involves many aspects, including MySQL directory and files, communication, network, information security, memory management, optimization, query buffer, as well as MySQL log settings.

user = MySQL #mysql_safe脚本使用MySQL运行用户 (--user=mysql specified at compile time), it is recommended to use MySQL user.

Port = 3306 #MySQL服务运行时的端口号. It is recommended that you change the default port to be vulnerable by default.

Socket =/data/3306/mysql.sock #socket文件是在Linux/unix Environment, the client connection in Linux/unix environment can not use UNIX directly through TCP/IP network. Connect the socket to MySQL.

Basedir =/application/mysql #mysql程序所存放路径, often used for MySQL boot, configuration files, logs, etc.

DataDir =/data/3306/data #MySQL数据存放文件 (extremely important)

Character-set-server = UTF8 #数据库和数据库表的默认字符集. (Recommended UTF8, so as not to cause garbled)

Log-error=/data/3306/mysql_xuliangwei.err #mysql错误日志存放路径及名称 (start error must look at the error log, absolutely can be resolved by error log.) )

Pid-file=/data/3306/mysql_xuliangwei.pid #MySQL_pid文件记录的是当前mysqld进程的pid, PID is ProcessID.

Skip-locking #避免MySQL的外部锁定 to reduce the chance of error and enhance stability.

Skip-name-resolv #禁止MySQL对外部连接进行DNS解析, this option can be used to eliminate the DNS resolution of MySQL. However, it is important to note that if this option is turned on, all remote host connection authorizations will have to use the IP address method, otherwise MySQL won't be able to handle the connection request properly!

skip-networking #开启该选项可以彻底关闭MySQL的TCP/IP connection, if the Web server is remotely connected to the MySQL database server, do not turn on this option, otherwise it will not connect properly!

Open_files_limit = 1024x768 #MySQLd能打开文件的最大个数, if too mant openfiles is present, the value needs to be adjusted.

Back_log = 384 #back_log参数是值指出在MySQL暂时停止响应新请求之前, how many requests in a short time can be present in the stack. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have their own limits on the size of this queue. If you try to set back_log higher than the operating system limit will be invalid, its default value is 50. For Linux systems, the recommended setting is an integer less than 512.

Max_connections = #指定MySQL允许的最大连接进程数. If the Too many connections error is frequently encountered while accessing the blog, you need to increase the parameter value.

Max_connect_errors = 6000 #设置每个主机的连接请求异常中断的最大次数, when this number of times is exceeded, the MySQL server will disallow host connection requests until the MySQL server restarts or through flush The hosts command clears information about this host.

Wait_timeout = #指定一个请求的最大连接时间, it can be set to 5~10 for servers with about 4GB of memory.

Table_cache = 614K #table_cache指示表高速缓冲区的大小. When MySQL accesses a table, if there is room in the MySQL buffer, then the table is opened and placed in the table buffer, and the benefit is that the contents of the table can be accessed more quickly. In general, you can see the status values for the peak time of the database Open_tables and open_tables to determine if you need to increase the value of Table_cache, that is, if Open_tables is close to Table_cache, and Opened_ Tables This value is gradually increasing, it is necessary to consider increasing the size of this value.

external-locking = FALSE #MySQL选项可以避免外部锁定. True to open.

Max_allowed_packet =16m #服务器一次能处理最大的查询包的值 is also the largest query that the server program can handle

Sort_buffer_size = 1M #设置查询排序时所能使用的缓冲区大小, the system default size is 2MB.

Note: The allocated memory for this parameter is exclusive for each connection, and if there are 100 connections, the actual allocation of the total sort buffer size is X6=600MB. Therefore, for servers that have around 4GB, it is recommended to set it to 6MB~8MB

Join_buffer_size = 8M #联合查询操作所能使用的缓冲区大小, as with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.

Thread_cache_size = #设置Thread Maximum number of connection threads that can be cached in the cache pool and can be set to 0~ 16384, the default is 0. This value indicates that the number of threads stored in the cache can be re-used, and if there is room in the cache when disconnected, the client's thread will be placed in the cache, and if the thread is requested again, then the request will be read from the cache if it is empty or a new request in the cache. Then this thread will be recreated, and if there are many threads, increasing this value can improve system performance. By comparing the variables of the connections and threads_created states, you can see the effect of this variable. We can set the rules according to the physical memory as follows: 1GB memory we configured for 8,2GB memory we configured for 16,3GB we configured for 32,4GB or 4GB above we give this value to 64 or greater value.

Thread_concurrency = 8 #该参数取值为服务器逻辑CPU数量x 2, in this case, the server has two physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4 x 2 = 8. This is also the configuration of the dual quad-core mainstream server.

Query_cache_size = 64M #指定MySQL查询缓冲区的大小. It is possible to observe in the MySQL console that if the value of the qcache_lowmem_prunes is very large, there is often insufficient buffering, and if the value of qcache_hits is very large, the query buffer is used very frequently. In addition, if the value of the small change will affect efficiency, then you can consider not to query buffering. For Qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.

Query_cache_limit = 2M #只有小于此设置值的结果才会被缓存

Query_cache_min_res_unit = 2k #设置查询缓存分配内存的最小单位, to set this parameter appropriately, you can reduce the number of requests and allocations of memory fast, but setting too large may cause memory fragmentation to rise. The default value is 4K and the recommended setting is 1k~16k.

Default_table_type = InnoDB #默认表的类型为InnoDB

Thread_stack = 256K #设置MySQL每个线程的堆栈大小, the default value is large enough to satisfy normal operation. Can be set to 128KB to 4GB, the default is 192KB

#transaction_isolation = level #数据库隔离级别 (read UNCOMMITTED) read COMMITTED (read submissions) repeatable read (can be reread) SERIALIZABLE (Serializable))

Tmp_table_size = 64M #设置内存临时表最大值. If this value is exceeded, the temporary table is written to disk with a range of 1KB to 4GB.

Max_heap_table_size = 64M #独立的内存表所允许的最大容量.

Table_cache = 614 #给经常访问的表分配的内存, the larger the physical memory, the greater the setting. This value, in general, can reduce disk IO, but the corresponding will take up more memory, which is set to 614.

Table_open_cache = #设置表高速缓存的数目. Each connection comes in with at least one table cache open. Therefore, the size of the Table_cache should be related to the Max_connections setting. For example, for 200 parallel-running connections, the table should have at least 200xN of cache, where N is the maximum number of tables in a join of a query that the application can execute. In addition, some additional file descriptors need to be reserved for temporary tables and files.

Long_query_time = 1 #慢查询的执行用时上限, default setting is 10s, recommended (1S~2S)

Log_long_format #没有使用索引的查询也会被记录. (Recommended, according to the business to adjust)

Log-slow-queries =/data/3306/slow.log #慢查询日志文件路径 (it is recommended to open this log if you turn on slow queries)

Log-bin =/data/3306/mysql-bin #logbin数据库的操作日志, such as update, delete, create, etc. are stored to binlog log, and can be incrementally restored through Logbin

Relay-log =/data/3306/relay-bin #relay-log Log is logged from the server I/O thread that reads the primary server's binary log to the local file from the server. The SQL thread then reads the contents of the Relay-log log and applies it to the slave server

Relay-log-info-file =/data/3306/relay-log.info #从服务器用于记录中继日志相关信息的文件, the default name is relay-log.info in the data directory.

Binlog_cache_size = 4M #在一个事务中binlog为了记录sql状态所持有的cache大小, if you often use large, multi-declarative transactions, you can increase this value to achieve greater performance, and all the state of affairs is buffered in the Binlog buffer, It is then committed once to the binlog, and if the transaction is larger than this value, the temporary file on disk is substituted, which is created when each linked transaction is updated the state for the first time.

Max_binlog_cache_size = 8M #最大的二进制Cache日志缓冲尺寸.

Max_binlog_size = 1G #二进制日志文件的最大长度 (default 1GB) when a binary file information exceeds this maximum length, the MySQL server automatically provides a new binary log file on the continuation.

Expire_logs_days = 7 #超过7天的binlog, mysql program is automatically deleted (if the data is important, it is recommended not to turn on this option)

Key_buffer_size = 256M #指定用于索引的缓冲区大小, increase it to get better index processing performance. This parameter can be set to 256MB or 384MB for a server that has around 4GB in it.

Note: If this parameter value is set too large, it will reduce the overall efficiency of the server!

Read_buffer_size = 4M #读查询操作所能使用的缓冲区大小. As with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.

Read_rnd_buffer_size = 16M #设置进行随机读的时候所使用的缓冲区. This parameter is in contrast to the buffer set by Read_buffer_size, one that is used when sequential reads, and one that is randomly read. But both are set for the thread and each thread can produce either of the two types of buffer. The default value is 256KB and the maximum value is 4GB.

Bulk_insert_buffer_size = 8M #如果经常性的需要使用批量插入的特殊语句来插入数据, the parameters can be adjusted to 16MB~32MB, recommended 8MB.

#myisam_sort_buffer_size = 8M #设置在REPAIR table or the buffer size allocated by the sort index in the process of creating index or Alter table with CREATE INDEX, can be set to a range of 4Bytes to 4GB, the default is 8MB

Lower_case_table_names = 1 #实现MySQL不区分大小. (Open demand-recommended)

Slave-skip-errors = 1032,1062 #从库可以跳过的错误数字值 (mysql error with digital code feedback, full MySQL error code Daquan, will be published to the blog later).

Replicate-ignore-db=mysql #在做主从的情况下, set up libraries that do not need to be synchronized.

Server-id = 1 #表示本机的序列号为1, if the master from, or multiple instances, ServerID must not be the same.

Myisam_sort_buffer_size = 128M #当需要对于执行REPAIR, OPTIMIZE, when the ALTER statement rebuilds the index, MySQL allocates the cache, and the load DATA infile is loaded into a new table, It will be carefully allocated according to the maximum configuration of each thread.

Myisam_max_sort_file_size = 10G #当重新建索引 (repair,alter,table, or load,data,tnfile), MySQL is allowed to use the maximum value of the temporary file.

Myisam_repair_threads = 1 #如果一个表拥有超过一个索引, MyISAM can use more than one thread to fix them by using parallel sorting.

Myisam_recover #自动检查和修复没有适当关闭的 MyISAM table.

Innodb_additional_mem_pool_size = 4M #用来设置InnoDB存储的数据目录信息和其他内部数据结构的内存池大小. The more tables you have in your application, the more memory you need to allocate in this area. For a relatively stable application, the size of this parameter is relatively stable, and there is no need to reserve very large values. If InnoDB uses the memory in this pool, InnoDB starts allocating memory from the operating system and writes a warning message to the MySQL error log. The default is 1MB, and it should be appropriate to increase the size of the parameter when it is found to have an associated warning message in the error log.

Innodb_buffer_pool_size = 64M #InnoDB使用一个缓冲池来保存索引和原始数据, the larger the setting, the less disk I/O is required to access the data in the table. It is strongly recommended not to arbitrarily configure the InnoDB buffer pool value as the 50%~80% of physical memory, depending on the environment.

Innodb_data_file_path = Ibdata1:128m:autoextend #设置配置一个可扩展大小的尺寸为128MB的单独文件, named Ibdata1. The location of the file is not given, so the default is within the MySQL data directory.

Innodb_file_io_threads = 4 #InnoDB中的文件I/o thread. Typically set to 4 if Windows can set a larger value to increase disk I/O

Innodb_thread_concurrency = 8 #你的服务器有几个CPU就设置为几, it is recommended to use the default setting, which is generally set to 8.

Innodb_flush_log_at_trx_commit = 1 #设置为0就等于innodb_log_buffer_size队列满后在统一存储, which defaults to 1 and is the safest setting.

Innodb_log_buffer_size = 2M #默认为1MB, usually set to 8~16MB is sufficient.

Innodb_log_file_size = 32M #确定日志文件的大小, larger settings can improve performance, but also increase the time to restore the database.

Innodb_log_files_in_group = 3 #为提高性能, MySQL can write log files to multiple files in a circular manner. The recommended setting is 3.

innodb_max_dirty_pages_pct = #InnoDB主线程刷新缓存池中的数据.

Innodb_lock_wait_timeout = #InnoDB事务被回滚之前可以等待一个锁定的超时秒数. InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB uses the Locak tables statement to notice the locking settings. The default value is 50 seconds.

innodb_file_per_table = 0 #InnoDB为独立表空间模式, each table in each database generates a data space. 0 off, 1 open.

Independent Table Space Benefits:

1, each table has its own independent table space.

2. The data and indexes of each table will exist in its own tablespace.

3, you can implement a single table in a different database to move.

4, the space can be recycled (except the drop table operation, the table empty can not be recycled.) ）

[Mysqldump]

Quick

Max_allowed_packet = 2M #设定在网络传输中一次消息传输量的最大值. The system defaults to 1MB and the maximum value is 1GB and must be set to a multiple of 1024. The unit is in bytes.

[Mysqld_safe]

Worth noting:

• It is strongly recommended not to arbitrarily configure the InnoDB buffer pool value as the 50%~80% of physical memory, depending on the environment.

• If the key_reads is too large, then the my.cnf in the key_buffer_size should be larger, keep key_reads/key_read_re-quests at least 1/100, the smaller the better.

• If the qcache_lowmem_prunes is large, increase the value of query_cache_size.

MySQL Configuration optimization