mysql 极限优化配置_MySQL性能调优配置

方法

使用如下配置文件参数启动数据库,默认配置文件路径为/etc/my.cnf, 其中MySQL软件安装及数据存放路径根据实际情况修改。

[mysqld_safe]

log-error=/data/mysql/log/mariadb.log

pid-file=/data/mysql/run/mysqld.pid

[client]

socket=/data/mysql/run/mysql.sock

default-character-set=utf8

[mysqld]

basedir=/usr/local/mysql

tmpdir=/data/mysql/tmp

datadir=/data/mysql/data

socket=/data/mysql/run/mysql.sock

port=3306

user=root

default_authentication_plugin=mysql_native_password

ssl=0 #关闭ssl

max_connections=2000 #设置最大连接数

back_log=2048 #设置会话请求缓存个数

performance_schema=OFF #关闭性能模式

max_prepared_stmt_count=128000

#file

innodb_file_per_table #设置每个表一个文件

innodb_log_file_size=1500M #设置logfile大小

innodb_log_files_in_group=32 #设置logfile组个数

innodb_open_files=4000 #设置最大打开表个数

#buffers

innodb_buffer_pool_size=230G #设置buffer pool size,一般为服务器内存60%

innodb_buffer_pool_instances=16 #设置buffer pool instance个数,提高并发能力

innodb_log_buffer_size=64M #设置log buffer size大小

#tune

sync_binlog=1 #设置每次sync_binlog事务提交刷盘

innodb_flush_log_at_trx_commit=1 #每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去

innodb_use_native_aio=1 #开启异步IO

innodb_spin_wait_delay=180 #设置spin_wait_delay 参数,防止进入系统自旋

innodb_sync_spin_loops=25 #设置spin_loops 循环次数,防止进入系统自旋

innodb_flush_method=O_DIRECT #设置innodb数据文件及redo log的打开、刷写模式

innodb_io_capacity=20000 # 设置innodb 后台线程每秒最大iops上限

innodb_io_capacity_max=40000 #设置压力下innodb 后台线程每秒最大iops上限

innodb_lru_scan_depth=9000 #设置page cleaner线程每次刷脏页的数量

innodb_page_cleaners=16 #设置将脏数据写入到磁盘的线程数

#perf special

innodb_flush_neighbors=0 #检测该页所在区(extent)的所有页,如果是脏页,那么一起进行刷新,SSD关闭该功能

innodb_write_io_threads=16 #设置写线程数

innodb_read_io_threads=16 #设置读线程数

innodb_purge_threads=32 #设置回收已经使用并分配的undo页线程数

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES

参数名称参数含义优化建议

innodb_thread_concurrency

InnoDB使用操作系统线程来处理用户的事务请求。

建议取默认值为0,它表示默认情况下不限制线程并发执行的数量。

innodb_read_io_threads

执行请求队列中的读请求操作的线程数。

根据CPU核数及读写比例进一步更改来提高性能。

innodb_write_io_threads

执行请求队列中的写请求操作的线程数。

根据CPU核数及读写比例进一步更改来提高性能。

query_cache_size

设置query_cache_size大小。

Query Cache(查询缓存)是一个众所周知的瓶颈位,即使在并发量不高的时候也会出现。最好的选择是从一开始就禁用它。通过设置 query_cache_size = 0,建议禁用查询缓存。

innodb_buffer_pool_instances

开启多个内存缓冲池,把需要缓冲的数据hash到不同的缓冲池中,这样可以并行的内存读写。

建议设置8~16。

innodb_open_files

在innodb_file_per_table模式下,限制Innodb能打开的文件数量。

建议此值调大一些,尤其是表特别多的情况。

innodb_buffer_pool_size

缓存数据和索引的地方。

通常建议内存的70%左右。

innodb_log_buffer_size

缓存重做日志。

默认值是64M,建议通过查看innodb_log_wait,调整innodb_log_buffer_size大小。

innodb_io_capacity

innodb 后台线程每秒最大iops上限。

建议为IO QPS总能力的75%。

innodb_log_files_in_group

重做日志组的个数。

-

innodb_log_file_size

重做日志文件大小。

如果存在大量写操作,建议增加日志文件大小,但日志文件过大,会影响数据恢复时间。

如果是非生产环境,测试极限性能时,尽量调大日志文件。

如果是商用场景,需要考虑数据恢复时间,综合折中后设置日志文件大小。

innodb_flush_method

Log和数据刷新磁盘的方法:

datasync模式:写数据时,write这一步并不需要真正写到磁盘才算完成(可能写入到操作系统buffer中就会返回完成),真正完成是flush操作,buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。

O_DSYNC模式:写日志操作是在write这步完成,而数据文件的写入是在flush这步通过fsync完成。

O_DIRECT模式:数据文件的写入操作是直接从mysql innodb buffer到磁盘的,并不用通过操作系统的缓冲,而真正的完成也是在flush这步,日志还是要经过OS缓冲。

建议O_DIRECT模式。

innodb_spin_wait_delay

控制轮询的间隔。

根据真实场景调试,直到看不到splin_lock热点函数等。

innodb_sync_spin_loops

控制轮询的虚幻次数。

根据真实场景调试,直到看不到splin_lock热点函数等。

innodb_lru_scan_depth

LRU列表的可用页数量。

默认值是1024,非生产环境,测试极限性能可以适当调大,减少checkpoint次数。

innodb_page_cleaners

刷新脏数据的线程数。

建议与innodb_buffer_pool_instances相等。

innodb_purge_threads

回收undo的线程数。

-

innodb_flush_log_at_trx_commit

0:不管有没有提交,每秒钟都写到binlog日志里.

1:每次提交事务,都会把log buffer的内容写到磁盘里去,对日志文件做到磁盘刷新,安全性最好。

2:每次提交事务,都写到操作系统缓存,由OS刷新到磁盘,性能最好。

非生产环境,测试极限性能,可以设置为0。

innodb_doublewrite

是否开启二次写。

非生产环境,测试极限性能,可以设置为0,关闭二次写。

ssl

是否开启安全连接。

安全连接对性能影响较大,非生产环境,测试极限性能,可以设置为0;商用场景,根据客户需求调整。

skip_log_bin

是否开启binlog。

非生产环境,测试极限性能在参数文件中增加此参数,关闭binlog选项。

innodb_checksum_algorithm

数据完整性校验。

非生产环境,测试极限性能设置成none,不启用算法校验。

binlog_checksum

Binlog完整性校验。

非生产环境,测试极限性能设置成none,不启用算法校验。

innodb_log_checksums

Log完整性校验。

非生产环境,测试极限性能设置成0,关闭log checksum。

foreign_key_checks

外键校验。

非生产环境,测试极限性能设置成0,关闭外键校验。

performance_schema

是否开启性能模式。

非生产环境,测试极限性能设置为OFF,关闭性能模式。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值