Little rookie MySQL Optimization solution

According to the personal habits of small rookie, his own set of MySQL optimization program, feel or a bit of food, hope everyone understanding, shortcomings, please the great god interaction!

#mysql优化解决方案

#公共参数默认值:

Max_connections = 151

#同事处理多大连接数, we recommend setting the maximum number of connections is about 80% of the maximum number of connections

Sort_buffer_size = 2M

#查询排序时缓冲区大小, only the order by and group by function can increase this value to 16M

Open_files_limit = 1024

#打开文件数限制 If the show global status like ' Open_files ' values equals or is greater than Open_files_limit value

#程序会无法连接数据库或卡死

MyISAM parameter Default value:

Key_buffer_size = 16M

#索引缓存区大小, general setup of physical memory 30-40%

Read_buffer_size = 128k

#读操作缓存区大小, recommended settings 16M or 32M

Query_cache_type = On

#打开查询缓存功能

Query_cache_limit = 1M

#查询缓存限制, only 1 m the following query results will be cached to avoid overwriting the cache pool with large result data

Query_cache_size = 16M

#查看缓存区大小, for caching Select query results, the next time the same select query will return results directly from the cache pool, you can multiply this value appropriately

InnoDB parameter Default value:

Innodb_buffer_pool_size = 128M

#索引和数据缓冲区大小, general setup of physical memory 60%-70%

Innodb_buffer_pool_instances = 1

#缓冲池实例个数, recommended setting of 4 or 8

Innodb_flush_log_at_trx_commit = 1

#关键参数, 0 represents about every second written to the log and synchronizes to disk, and database failures can lose about 1 seconds of transactional data. 1 writes to the log after each SQL execution and synchronizes to disk, I/O overhead, SQL to wait for the log to read and write, inefficient. 2 means that only the log is written to the system buffer, and then synchronized to disk per second, the efficiency is very high, if the server fails, the transaction data will be lost. The data security requirements are not very high recommended settings 2, high performance, modified after the effect is obvious.

Innodb_file_per_table = OFF

#默认是共享表空间, shared tablespace idbdata files are increasing, affecting certain I/O performance. It is recommended to turn on the standalone tablespace mode, where each table's index and data exist in its own table space, enabling a single table to move in different databases.

Innodb_log_buffer_size = 8M

#日志缓冲区大小, because the log is refreshed every second, it is generally not more than 16M

#系统内核优化

Net.ipv4.tcp_fin_timeout = 30

#TIME_WAIT超时时间, the default is 60s

Net.ipv4.tcp_tw_reuse = 1

#1表示开启复用, allow time_wait sockets to be re-used for new TCP connections, 0 for shutdown

Net.ipv4.tcp_tw_recycle = 1

#1表示开启TIME_WAIT socket Fast Recovery, 0 to close

Net.ipv4.tcp_max_tw_buckets = 4096

#系统保持TIME_WAIT the maximum number of sockets, if this number is exceeded, the system will randomly clear some time_wait and print a warning message

Net.ipv4.tcp_max_syn_backlog = 4096

#进入SYN队列最大长度, increase the queue length to accommodate more waiting connections

#在linux系统中, if the number of file handles opened by the process exceeds the system default of 1024, the "Too many files open" message is prompted, so you want to adjust the open file handle limit.

# vi/etc/security/limits.conf #加入以下配置, * on behalf of all users, can also specify the user, restart the system to take effect

* Soft Nofile 65535

* Hard Nofile 65535

# Ulimit-shn 65535 #立刻生效

This article is from the "Little Rookie" blog, please be sure to keep this source http://baishuchao.blog.51cto.com/12918589/1927583

Little rookie MySQL Optimization solution