MySQL性能需要关注的参数有哪些
发布时间：2021-12-21 23:32:14 所属栏目：MySql教程 来源：互联网
导读：本篇内容介绍了MySQL性能需要关注的参数有哪些的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 1、innodb_flush_log_at_trx_commit设置为2 这参数是指
|
本篇内容介绍了“MySQL性能需要关注的参数有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 1、innodb_flush_log_at_trx_commit设置为2 这参数是指 事务log 怎样从log buffer写进日志文件（ib_logfile0、ib_logfile1） =0 mysql crash 就丢失了，性能最好 buffer pool -> log buffer 每秒 wirte os cache & flush磁盘 =1 不会丢失，效率低 每次commit，buffer pool -> log buffer—> write os cache & flush磁盘 =2 即使mysql崩溃也不会丢数据 每次commit ，buffer pool -> os cache 然后每秒flush 磁盘 注意：由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒 可以根据业务的安全程度和对性能的要求来具体设置该参数，已经下面的sync_binlog 2、sync_binlog 二进制日志（binary log）同步到磁盘的频率。binary log 每写入sync_binlog 次后，刷写到磁盘。 如果 autocommit 开启，每个语句都写一次 binary log，否则每次事务写一次。 默认值是 0，不主动同步，而依赖操作系统本身不定期把文件内容 flush 到磁盘 设为 1 最安全，在每个语句或事务后同步一次 binary log，即使在崩溃时也最多丢失一个语句或事务的日志，但因此也最慢。 sync_binlog = N： 控制的是从binlog buffer中刷新binlog到底层binlog文件（也就是刷新到底层磁盘） N>0 每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上； N=0 不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定； 大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把 sync_binlog 配置成 1，为了追求高并发，提升性能，可以设置为 100 或直接用 0 注意 MySQL 5.6开始引入Group Commit后 sync_binlog的含义就变了，假定设为1000，表示的不是1000个事务后做一次fsync，而是1000个事务组。也就是说，当设置sync_binlog=1，binlog还未落盘，此时系统crash，会丢失对应的最后一个事务组；如果这个事务组内有10个事务，那么这10个事务都会丢失。 如何查看是否属于一个事务组 通过mysqlbinlog可以查看binlog日志中last_committed值，如果值一样，表明是在同一事务组内。 ### INSERT INTO `wukong_test`.`wukong` ### SET ### @1=3 /* INT meta=0 nullable=1 is_null=0 */ ### @2='ccccc' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */ # at 496468 #170527 4:17:35 server id 12001 end_log_pos 496499 CRC32 0xd6e7f69f Xid = 5556 COMMIT/*!*/; # at 496499 #170527 4:17:35 server id 12001 end_log_pos 496564 CRC32 0x28816d5c GTIDlast_committed=1845sequence_number=1846 SET @@SESSION.GTID_NEXT= '0a646c88-36e2-11e7-937d-fa163ed7a7b1:3624'/*!*/; # at 496564 #170527 4:17:35 server id 12001 end_log_pos 496632 CRC32 0x03150d48 Query thread_id=1852 exec_time=0 error_code=0 SET TIMESTAMP=1495873055/*!*/; BEGIN 3、write/read thread 异步IO线程数 innodb_write_io_threads=16 innodb_read_io_threads=16 (该参数需要在配置文件中添加，重启mysql实例起效)脏页写的线程数，加大该参数可以提升写入性能 4、innodb_max_dirty_pages_pct 最大脏页百分数，当系统中脏页所占百分比超过这个值，INNODB就会进行写操作以把页中的已更新数据写入到磁盘文件中。默认75，一般现在流行的SSD硬盘很难达到这个比例。可依据实际情况在75-80之间调节， 这个参数太大，导致实例恢复需要很长时间，太小的话会频繁刷新，增加page_cleaner_thread以及innodb_write_io_threads和cpu的负担，一般就用默认值； 5、innodb_io_capacity=5000 从缓冲区刷新脏页时，一次刷新脏页的数量。根据磁盘IOPS的能力一般建议设置如下： SAS 200 SSD 5000 PCI-E 10000-50000 6、innodb_flush_method=O_DIRECT(该参数需要重启mysql实例起效) 控制innodb 数据文件和redo log的打开、刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。 MySQL性能需要关注的参数有哪些 fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。 O_DSYNC模式：写日志操作是在write这步完成（不通过os buffer)，而数据文件的写入是在flush这步通过fsync完成。 O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲。 MySQL性能需要关注的参数有哪些 通过图可以看出O_DIRECT相比fdatasync的优点是避免了双缓冲，本身innodb buffer pool就是一个缓冲区，不需要再写入到系统的buffer，但是有个缺点是由于是直接写入到磁盘，所以相比fdatasync的顺序读写的效率要低些。所以如果磁盘io压力不大的话，并且如果系统使用了swap空间，可以考虑innodb_flush_method=O_DIRECT; 在大量随机写的环境中O_DIRECT要比fdatasync效率更高些，顺序写多的话，还是默认的fdatasync更高效，因为咱们现在使用了insert buffer cache的使用（转化成了顺序写），个人认为还是默认值比较好， 7、innodb_adaptive_flushing 设置为 ON （使刷新脏页更智能） 影响每秒刷新脏页的数目 规则由原来的“大于innodb_max_dirty_pages_pct时刷新100个脏页到磁盘”变为 “通过buf_flush_get_desired_flush_reate函数判断重做日志产生速度确定需要刷新脏页的最合适数目”，即使脏页比例小于 innodb_max_dirty_pages_pct时也会刷新一定量的脏页。 8.innodb_page_cleaners MySQL 5.7开启并发刷新线程，innodb_page_cleaners控制刷新线程数 mysql> show variables like 'i%cleaners'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_page_cleaners | 1 | +----------------------+-------+ 1 row in set (0.05 sec) 配置文件my.cnf中添加innodb_page_cleaners=num值 默认是1；最大可以是64，也就是会有64个page cleaner线程并发工作清理脏页 9.innodb_flush_ neighbors刷新临近页的参数； 当刷新一个脏页时，Innodb存储引擎会检测该页所在区（extent）的所有的页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势。至于固态硬盘来说，因为它有着超高的IOPS性能，则建议将该参数设置为0，也就是关闭这个特性；个人觉得如果io并不是性能瓶颈，不建议开启这个功能，因为它可能将不怎么脏的页进行刷新，而该页之后又会很快变成脏页； 10、innodb_adaptive_flushing_method 设置为 keep_average 影响checkpoint，更平均的计算调整刷脏页的速度，进行必要的flush.（该变量为mysql衍生版本Percona Server下的一个变量，原生mysql不存在） 11、innodb_stats_on_metadata=OFF 关掉一些访问information_schema库下表而产生的索引统计。 当重启mysql实例后，mysql会随机的io取数据遍历所有的表来取样用于统计数据，这个实际使用中用的不多，建议关闭. 11、innodb_change_buffering=all change buffer可以认为是insert buffer的升级，可以对dml操作---insert、delete、update都进行缓冲，他们分别是：insert buffer、delete buffer、purge buffer； 该参数用来开启各种buffer的选项，可选择的值为：inserts、delete、purges、changes、all、none;其中changes代表启用了inserts和delete，all 表示启用所有，none表示都不启用，默认为all; 当更新/插入的非聚集非唯一索引的数据所对应的页不在内存中时（对非聚集非唯一索引的更新操作通常会带来随机IO），会将其放到一个insert buffer中，当随后页面被读到内存中时，会将这些变化的记录merge到页中。当服务器比较空闲时，后台线程也会做merge操作。 由于主要用到merge的优势来降低io，但对于一些场景并不会对固定的数据进行多次修改，此处则并不需要把更新/插入操作开启change_buffering，如果开启只是多余占用了buffer_pool的空间和处理能力。这个参数要依据实际业务环境来配置。 12、innodb_change_buffer_max_size 从Innodb 1.2.X版本开始，可以通过参数innodb_change_buffer_max_size来控制change buffer最大使用内存的数量；默认值为25，表示最多使用25%的缓冲池内存空间，该参数最大有效值为50%。 如果使用太多，那么当MySQL server crash，会进行很长时间的恢复工作（进行merger操作） 13、innodb_old_blocks_pct初始化默认是37, （innodb体系架构 27页）注意因为新来的page是放到了尾部3/8的位置(也就是属于sublist of old blocks)所以sublist of new blocks只能来自于sublist of old blocks的移动 innodb缓存池有2个区域一个是sublist of old blocks存放不经常被访问到的数据，另外一个是sublist of new blocks存放经常被访问到的数据 innodb_old_blocks_pct参数是控制进入到sublist of old blocks区域的数量，初始化默认是37. innodb_old_blocks_time参数是在访问到sublist of old blocks里面数据的时候控制数据不立即转移到sublist of new blocks区域，而是在多少微秒之后才会真正进入到new区域，这也是防止new区域里面的数据不会立即被踢出。 所以就有2种情况： 1、如果在业务中做了大量的全表扫描，那么你就可以将innodb_old_blocks_pct设置减小，增到innodb_old_blocks_time的时间，不让这些无用的查询数据进入old区域，尽量不让缓存再new区域的有用的数据被立即刷掉。（这也是治标的方法，大量全表扫描就要优化sql和表索引结构了） 2、如果在业务中没有做大量的全表扫描，那么你就可以将innodb_old_blocks_pct增大，减小innodb_old_blocks_time的时间，让有用的查询缓存数据尽量缓存在innodb buffer pool中，减小磁盘io，提高性能。 21、binlog_cache_size 二进制日志缓冲大小：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘。 设置太大的话，会比较消耗内存资源（Cache本质就是内存），更加需要注意的是：binlog_cache是不是全局的，是按SESSION为单位独享分配的，也就是说当一个线程开始一个事务的时候，Mysql就会为这个SESSION分配一个binlog_cache 怎么判断我们当前的binlog_cache_size设置的没问题呢？ mysql> show status like 'binlog_%'; +-----------------------+-----------+| Variable_name | Value | Binlog_cache_disk_use | 1425 | | Binlog_cache_use | 126945718 | 2 rows in set (0.00 sec) mysql> select @@binlog_cache_size; +-----------------------+-----------+| @@binlog_cache_size 1048576 1 row in set (0.00 sec) 运行情况Binlog_cache_use 表示binlog_cache内存方式被用上了多少次，Binlog_cache_disk_use表示binlog_cache临时文件方式被用上了多少次,当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值 22.innodb_file_per_table两个取值: 1：开启独立表空间； 0：不开启，也就使用共享表空间； 优点： 1)每个表的数据和索引都会存在自已的表空间中, 2)可以实现单表在不同的数据库中移动 3)空间可以回收（除drop table操作） 4)删除大量数据后可以通过:alter table TableName engine=innodb; 回缩不用的空间 使用turncate table也会使空间收缩 5）对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能 缺点： 1）单表增加过大，如超过100个G，使用共享表空间可能会更好！ 结论：共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整一 下：innodb_open_files，因为每个表对应一个文件，需要打开的文件个数比共享表空间要多，所以需要适当调大innodb_open_files的参数，并且调高linux内核参数 open files的限制1 想要将共享表空间转化为独立表空间有两种方法： 1.先逻辑备份，然后修改配置文件my.cnf中的参数innodb_file_per_table参数为1，重启服务后将逻辑备份导入即可。 2.修改配置文件my.cnf中的参数innodb_file_per_table参数为1，重启服务后将需要修改的所有innodb表都执行一遍：alter table table_name engine=innodb; 使用第二种方式修改后，原来库中的表中的数据会继续存放于ibdata1中，新添加的数据才会使用独立表空间 23.sync_relay_log： sync_relay_log：默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制，为N就是n次次sync_relay_log事件会刷新到磁盘； If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk (using fdatasync()) after every sync_relay_log events are written to the relay log. Setting this variable takes effect for all replication channels immediately, including running channels 当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O。 当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是10000，可动态修改； 24.然后介绍参数sync_binlog： sync_binlog = N： 控制的是从binlog buffer中刷新binlog到底层binlog文件（也就是刷新到底层磁盘） N>0 每向二进制日志文件写入N条SQL或N个事务后（组提交的时候，实际上是n个组事务），则把二进制日志文件的数据刷新到磁盘上； N=0 不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定； 25.增加本地端口，以应对大量连接 1 echo ‘1024 65000′ > /proc/sys/net/ipv4/ip_local_port_range 该参数指定端口的分配范围，该端口是向外访问的限制。mysql默认监听的3306端口即使有多个请求链接，也不会有影响。但是由于mysql是属于高内存、高cpu、高io应用，不建议把多个应用于mysql混搭在同一台机器上。即使业务量不大，也可以通过降低单台机器的配置，多台机器共存来实现更好。 26.增加队列的链接数 1 echo ‘1048576' > /proc/sys/net/ipv4/tcp_max_syn_backlog 建立链接的队列的数越大越好，但是从另一个角度想，实际环境中应该使用连接池更合适，避免重复建立链接造成的性能消耗。使用连接池，链接数会从应用层面更可控些。 27.设置链接超时时间 1 echo '10'> /proc/sys/net/ipv4/tcp_fin_timeout 该参数主要为了降低TIME_WAIT占用的资源时长。尤其针对http短链接的服务端或者mysql不采用连接池效果比较明显。 28.linux内核信号量 来linux内核信号量默认设置太小，造成大量等待， 默认值如下： # cat /proc/sys/kernel/sem 250 32000 32 128 说明： 第一列，表示每个信号集中的最大信号量数目。 第二列，表示系统范围内的最大信号量总数目。 第三列，表示每个信号发生时的最大系统操作数目。 第四列，表示系统范围内的最大信号集总数目。 将第三列调大一点，参考网上的数据 echo "kernel.sem=250 32000 100 128″>>/etc/sysctl.conf 然后sysctl -p 重启mysql 如果设置过小，当大量并发的时候，会在错误日志中报错：InnoDB: Warning: a long semaphore wait！！！！ “MySQL性能需要关注的参数有哪些”的内容就介绍到这里了，感谢大家的阅读。 （编辑：鞍山站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |