Liunx CentOS7.0安装MySQL5.6 my.cnf优化配置

07/19 项目实施 阅读 430 views 次 人气 0
摘要:

Liunx CentOS7.0安装MySQL5.6 my.cnf优化配置。

配置文件:/etc/my.cnf

[client]
port=3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8


[mysqld]
bind-address=0.0.0.0
port=3306
socket=/var/lib/mysql/mysql.sock

datadir=/var/lib/mysql 
character-set-server=utf8

default_storage_engine=InnoDB
innodb_buffer_pool_size=3072M
innodb_additional_mem_pool_size=32M

innodb_log_file_size=256M
innodb_log_files_in_group=3
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1

innodb_file_per_table=1
innodb_file_io_threads=4
innodb_open_files=500
#innodb_data_file_path=ibdata:10M:autoextend

innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=90
innodb_thread_concurrency=16

max_connections=2000
query_cache_size=16M


symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


一、内存利用相关

1、innodb_buffer_pool_size

Innodb优化首要参数。专用的数据库服务器下需要设置成物理内存的80%大小。不要设置太大,以免因此与操作系统进行分页竞争。注意,在32位系统中,每个进程被限制在2-3.5G,因此不要设置太高。

2、innodb_additional_mem_pool_size 

用来存放Innodb的内部目录,这个值不用分配太大,系统可以自动调。通常设置16M够用了,如果表比较多,可以适当的增大。


二、日志相关

1、innodb_log_file_size

在一个日志组中,每个log的大小。结合innodb_buffer_pool_size设置其大小,25%-100%。避免不需要的刷新。注意:这个值分配的大小和数据库的写入速度,事务大小,异常重启后的恢复有很大的关系。一般取256M可以兼顾性能和recovery的速度。

2、innodb_log_files_in_group

在一个日志组中,每个log的大小。结合innodb_buffer_pool_size设置其大小,25%-100%。避免不需要的刷新。注意:这个值分配的大小和数据库的写入速度,事务大小,异常重启后的恢复有很大的关系。

MySQL默认配置文件my-innodb-heavy-4G中设置的是innodb_log_files_in_group=3。

3、innodb_log_buffer_size

日志缓冲区的大小, 默认设置即可,具有大量事务的可以考虑设置为16M。

MySQL默认配置文件my-innodb-heavy-4G(中设置的是innodb_log_buffer_size=8M)

4、innodb_flush_log_at_trx_commit

控制log的刷新到磁盘的方式,这个参数只有3个值(0,1,2)默认为1,性能更高的可以设置为,0或是2,这样可以适当的减少磁盘IO,游戏库的MySQL建议设置为0。

0:log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer到log file的刷新或者文件系统到磁盘的刷新操作;

1:默认为1,在每次事务提交的时候将logbuffer中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;

2:事务提交会触发log buffer到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。

MySQL默认配置文件my-innodb-heavy-4G(中设置的是innodb_flush_log_at_trx_commit=1)


三、文件IO和空间占用相关

1、innodb_file_per_table

使每个Innodb的表,有自已独立的表空间。如删除表后可以回收那部分空间。默认是关闭的,建议打开(innodb_file_per_table=1)。

MySQL默认配置文件my-innodb-heavy-4G(无相关设置)。

2、innodb_file_io_threads

文件读写IO数,这个参数只在Windows上起作用。在Linux上只会等于4,默认即可。

MySQL默认配置文件my-innodb-heavy-4G(无相关设置)

3、innodb_open_files

限制Innodb能打开的表的数据,默认为300,数据库里的表特别多的情况,可以适当增大为1000。innodb_open_files的大小对InnoDB效率的影响比较小。但是在InnoDBcrash的情况下,innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。

MySQL默认配置文件my-innodb-heavy-4G(无相关设置)

4、innodb_data_file_path

指定表数据和索引存储的空间,可以是一个或者多个文件。最后一个数据文件必须是自动扩充的,也只有最后一个文件允许自动扩充。这样,当空间用完后,自动扩充数据文件就会自动增长(以8MB为单位)以容纳额外的数据。

例如: innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件放在不同的磁盘上。数据首先放在ibdata1 中,当达到900M以后,数据就放在ibdata2中。

MySQL默认配置文件my-innodb-heavy-4G(中设置的是innodb_data_file_path=ibdata:10M:autoextend)

5、innodb_data_home_dir

放置表空间数据的目录,默认在mysql的数据目录,设置到和MySQL安装文件不同的分区可以提高性能。

MySQL默认配置文件my-innodb-heavy-4G(中设置的是#innodb_data_home_dir = <directory>)


四.其他参数相关

1、innodb_flush_method

确定日志文件何时write/flush,innodb_flush_method则确定日志及数据文件如何write、flush。有三个值,分别是fdatasync,O_DSYNC和O_DIRECT,其中fdatasync是默认值。它们控制了InnoDB刷新日志和数据的模式。O_DIRECT跳过了操作系统的文件系统Disk Cache,直接读写磁盘了。避免双重缓冲,innodb用了innodb_buffer_pool,操作系统层面还有一个缓冲。这样就双重缓冲了,没有必要,反而加大了系统的负载,速度也慢了。可以配置innodb_flush_method=O_DIRECT跳过操作系统的缓冲,直接写入磁盘。建议设置为O_DIRECT。

2、innodb_max_dirty_pages_pct

在buffer pool中,允许脏页的百分比,默认为90,建议保持默认。

MySQL默认配置文件my-innodb-heavy-4G(中设置的是innodb_max_dirty_pages_pct=90)

3、innodb_thread_concurrency

同时在Innodb内核中处理的线程数量。建议默认值。

MySQL默认配置文件my-innodb-heavy-4G(中设置的是innodb_thread_concurrency=16)


五、公共参数调优

1、skip-external-locking

MyISAM存储引擎会使用这个参数,MySQL4.0之后,这个值默认是开启的。作用是避免MySQL的外部锁定(老版本的MySQL此参数叫做skip-locking),减少出错几率增强稳定性。建议默认值。

MySQL默认配置文件my-large.cnf(中设置的是skip-external-locking)

2、skip-name-resolve

禁止MySQL对外部连接进行DNS解析(默认是关闭此项设置的,即默认解析DNS),使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!如果需要,可以设置此项。

MySQL默认配置文件my-large.cnf(无此项设置)

2、max_connections

设置最大连接(用户)数,每个连接MySQL的用户均算作一个连接,max_connections的默认值为100。此值需要根据具体的连接数峰值设定。

MySQL默认配置文件my-large.cnf(无此项设置)

3、query_cache_size

查询缓存大小,如果表的改动非常频繁,或者每次查询都不同,查询缓存的结果会减慢系统性能。可以设置为0。

MySQL默认配置文件my-large.cnf(query_cache_size=16M)

4、sort_buffer_size

connection级的参数,排序缓存大小。一般设置为2-4MB即可。

MySQL默认配置文件my-large.cnf(sort_buffer_size=1M)

5、read_buffer_size

connection级的参数。一般设置为2-4MB即可。

MySQL默认配置文件my-large.cnf(read_buffer_size=1M)

6、max_allowed_packet

网络包的大小,为避免出现较大的网络包错误,建议设置为16M

MySQL默认配置文件my-large.cnf(max_allowed_packet=1M)

7、table_cache

当某一连接访问一个表时,MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开,则会直接访问缓存中的表,以加快查询速度;如果该表未被缓存,则会将当前的表添加进缓存并进行查询。(5.1.3之后这个值叫做table_open_cache),通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果发现open_tables等于table_cache,并且opened_tables在不断增长,那么就需要增加table_cache的值。设置为512即可满足需求。

MySQL默认配置文件my-large.cnf(table_open_cache=256)

8、myisam_sort_buffer_size

实际上这个myisam_sort_buffer_size参数意义不大,这是个字面上蒙人的参数,它用于ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 等命令时需要的内存。默认值即可。

MySQL默认配置文件my-large.cnf(myisam_sort_buffer_size=64M)

9、thread_cache_size

线程缓存,如果一个客户端断开连接,这个线程就会被放到thread_cache_size中(缓冲池未满),SHOW STATUS LIKE  'threads%';如果 Threads_created 不断增大,那么当前值设置要改大,改到 Threads_connected 值左右。(通常情况下,这个值改善性能不大),默认8即可

MySQL默认配置文件my-large.cnf(thread_cache_size=8)

10、thread_concurrency

线程并发数,建议设置为CPU内核数*2

MySQL默认配置文件my-large.cnf(thread_concurrency=8)

11、key_buffer_size

仅作用于 MyISAM存储引擎,用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存,这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引,以尽可能提高性能。不要设置超过可用内存的30%。即使不用MyISAM表,也要设置该值8-64M,用于临时表。

MySQL默认配置文件my-large.cnf(key_buffer_size = 256M)


评论

该文章不支持评论!

分享到: