MySQL补充内容
Mysql
MySQL补充内容
Mysql

MySQL补充内容

MySQL主从同步优化

sync_binlog参数

1、sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。https://www.cndba.cn/hbhe0316/article/22619

2、sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响，就是对写入性能影响太大，binlog虽然是顺序IO，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100、1000 或者是0。这样牺牲一定的一致性，可以获得更高的并发和吞吐量！

Mysql innodb_flush_log_at_trx_commit参数

innodb_flush_log_at_trx_commit：是 InnoDB 引擎特有的，ib_logfile的刷新方式( ib_logfile：记录的是redo log和undo log的信息)
取值:0/1/2
innodb_flush_log_at_trx_commit=0，表示每隔一秒把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区，也就是内存上，如果机器宕掉，可能丢失1秒的事务数据。
innodb_flush_log_at_trx_commit=1，表示在每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。
innodb_flush_log_at_trx_commit=2，表示在每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。

控制提交操作的严格ACID遵从性和在重新安排和批量执行与提交相关的I/O操作时可能实现的更高性能之间的平衡。您可以通过更改默认值来获得更好的性能，但这样可能会在崩溃时丢失事务。
为了完全符合ACID，需要将默认设置设置为1。在每次事务提交时将日志写入并刷新到磁盘。
如果设置为0，则每秒将日志写入磁盘并将其刷新一次。未刷新日志的事务可能会在崩溃中丢失。
如果设置为2，则在每次事务提交后写入日志，并每秒将日志刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。
对于设置0和2，不能100%保证每秒刷新一次。刷新可能由于DDL更改和其他InnoDB内部活动而更频繁地发生，这些活动导致日志被独立于innodb_flush_log_at_trx_commit设置而刷新，有时由于调度问题而不那么频繁地刷新。如果每秒钟刷新一次日志，那么在崩溃中可能会丢失一秒以内的事务。如果日志刷新的频率高于或低于每秒一次，那么可能丢失的事务数量也会相应变化。
日志刷新频率由innodb_flush_log_at_timeout控制，它允许您将日志刷新频率设置为N秒(其中N为1…2700，默认值为1)。然而，任何意外的mysqld进程退出都可能删除最多N秒的事务。
DDL变化和其他InnoDB内部活动会独立于innodb_flush_log_at_trx_commit设置刷新日志。
InnoDB崩溃恢复工作与innodb_flush_log_at_trx_commit设置无关。事务要么被完全应用，要么被完全删除。
在使用InnoDB和事务的复制设置中，为了持久性和一致性:
如果启用了二进制日志，设置sync_binlog=1。
总是设置innodb_flush_log_at_trx_comm=1

大型事务查询

--查看事务运行了多长时间
SELECT
	now(),(
		UNIX_TIMESTAMP(
		now()) - UNIX_TIMESTAMP( a.trx_started )) diff_sec,
	b.id,
	b.USER,
	b.HOST,
	b.db,
	d.SQL_TEXT 
FROM
	information_schema.innodb_trx a
	INNER JOIN information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id 
	AND b.command = 'Sleep'
	INNER JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
	INNER JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

--查看对应的事物
SELECT
	ps.id 'PROCESS ID',
	ps.USER,
	ps.HOST,
	esh.EVENT_ID,
	trx.trx_started,
	esh.event_name 'EVENT NAME',
	esh.sql_text 'SQL',
	ps.TIME 
FROM
	PERFORMANCE_SCHEMA.events_statements_history esh
	JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
	JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
	LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id 
WHERE
	trx.trx_id IS NOT NULL 
	AND ps.USER != 'SYSTEM_USER' 
ORDER BY
	esh.EVENT_ID;

--查找大事务，超找出来的是大事务的大小 大事务/1024/1024 MB
mysqlbinlog mysql-bin.000002 | grep "GTID$(printf '\t')last_committed" -B 1 \
| grep -E '^# at' | awk '{print $3}' \
| awk 'NR==1 {tmp=$1} NR>1 {print ($1-tmp);tmp=$1}' \
| sort -n -r | head -n 10
Print Friendly, PDF & Email