MySQL之日志管理(二)

时间:2022-09-22 08:24:06


承接上文 http://11107124.blog.51cto.com/11097124/1897457 


Sync(同步):在内存中完成操作的同时,立马将数据写入磁盘中;但是磁盘的I/O性能和内存的I/O性能相差好几个数量级,所以sync同步写入会降低MySQL的服务性能。


ASync(异步写入):写将数据写入内存中,再过一段时间(3,5秒)一并同步到磁盘上


所以引入异步的方式将内存中的二进制日志写入磁盘比较好;可以为异步写入提供一个内存缓存空间,先将日志缓存再缓存空间中,当缓存满后再一并写入磁盘


mysql> show global variables like '%log%';

binlog_cache_size                        32768   

二级制日志缓存区大小

binlog_stmt_cache_size                    32768  

binlog_format                          STATEMENT  

二进制日志格式statement(基于语句),还可以定义为row(基于行)和mixed(混合方式)   

log_bin                              ON

是否启用二级制日志存储文件,默认为数据目录下的mysql-bin.00001...可以自己指定存储位置

sql_log_bin                            ON  

控制二进制日志信息是否记录进入职文件 

slow_query_log                          OFF  

是否启用记录慢查询日志                         |

slow_query_log_file                       /mydata/data/node1-slow.log  

慢查询日志保存位置                          |

sql_log_off                             OFF  

查询语言(sql)日志是否开启


sync_binlog=# 

设定多久一次同步日志到磁盘,0表示不同步;任何正值都表示对二进制日志每隔多久往磁盘上同步一次;当autocommit值为1时,每条语句执行都会同步到磁盘上,否则每个事物提交的时候进行同步 


中继日志:从主服务器上复制的二进制日志,并保存在从服务器上可以在从服务器上再执行一遍进行数据备份


并发访问时,多个MySQL线程在执行时可能引起数据库变化的操作时,都要同时写入同一个磁盘的二进制文件中,这样就导致写入日志的速度太慢,大大降低服务器并发的性能;为解决这个问题,可以用多块SSD做成一个raid0,大大加速mysql数据库的日志写入速度,从而提高数据库服务器性能


现在的许多数据库服务器都使用FIO(FUSION-IO)机制,来讲SSD盘通过PCI-IN接口直接连接到北桥上,时磁盘与内存的交互能力只差一个数量级


IOPS:服务器每秒钟可以执行的IO操作数(IO-Peer-Seconds)


一般台式机的IOPS为100左右,SICI硬盘位200左右,而SSD盘可以达到500、1000、2000等


所以可以将二进制日志文件保存在SSD上


事务日志:用来保证数据的可靠性(ACID)


二进制日志:当数据崩溃后,用来进行数据的即时点还原


注:事务日志不能进行还原,只能撤销;数据的还原必须依靠二进制日志


事务只要一提交就不能撤销,而在事务提交之后数据才写入磁盘,在事务提交之前都可以撤销或者重做;如果事务提交后数据还未写入磁盘数据就崩溃了,则会在下次启动时,根据二进制日志记录进行数据修复,叫已经提交的事务数据写入到磁盘上,而将未提交的事务进行撤销(这就是事务重做和事务撤销)



所以为了保证数据的安全,可以设定提交事务后每隔一定时间,将数据一并同步到磁盘(ASync),这就避免了一个事务因为持续时间太长而未来得及将数据写入磁盘,而server就崩溃


Innodb事务有关的参数:

innodb_flush_log_at_trx_commit          | 1 

0:表示每一秒钟同步一次,并执行磁盘的flush操作

1:表示有事务提交或者每隔一秒就同步一次,并且执行磁盘flush操作

2:表示事物提交才同步,但不执行磁盘的flush操作,而是由操作系统决定何时对磁盘进行flush操作


[root@node1 ~]# cat /mydata/data/master-bin.index 

二进制日志的索引文件,记录了有哪些二进制日志

./master-bin.000001

./master-bin.000002

./master-bin.000003

./master-bin.000004

......


log_buffer_size 内存的缓存大小默认为8M

log_file_size  日志文件大小,默认为5M


注:MySQL为用户空间的进程,每次进行IO操作都要先交给内核,再由内核发起系统调用,让内核与磁盘进行交互


存储引擎:


[root@node1 ~]# mysql -e 'show engines'

+--------------------+---------+----------------------------------------------------------------+

| Engine             | Support | Comment        | Transactions | XA   | Savepoints |

+--------------------+---------+----------------------------------------------------------------+

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables          

| CSV                | YES     | CSV storage engine                                       

| MyISAM             | YES     | MyISAM storage engine                                    

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) |

| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables    

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                           

| ARCHIVE            | YES     | Archive storage engine                                         

| FEDERATED          | NO      | Federated MySQL storage engine                               

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     

+--------------------+---------+-----------------------------------------------------------



MyISam:不支持事务,只支持表锁,但由于所粒度太大,导致并发能力不强;但MyISam对读多写少的场景比较适应,支持B数索引FullText引擎,空间索引,表压缩(将表压缩后存储,节省空间、但加大开销)


Innodb:支持事务,支持行级锁(并行写入能力强),支持B数索引、聚簇索引、自适应HASH索引


MRG_MyISam:工作在MyISam上的存储引擎,能够将两张表结构一样的表合并为一张表进行查看


Memory:内存存储引擎,将表数据存在内存中,性能好,但数据不安全,容易丢失


CSV:利用文本文件存储表(类似/etc/passwd),可以方便的将数据从一个数据库导入到另一个数据库


注:MySQL的存储引擎为插件式的,所以你完全可以自己编译一个第三方的存储引擎进行使用;但不建议混合使用存储引擎(如既用MyISam又用Innodb),这样会导致许多操作不能执行(如两个存储引擎不一样的表不能添加外键)


MyISam:.frm(表结构文件).MYD(数据存储文件).MYI(索引文件)


Innodb:.frm(表结构文件).ibd(数据存储文件和索引文件)


innodb_file_per_table            | ON  

打开Innodb的每表一个空间,可进行许多高级操作;负责所有表都使用同一个表空间













             

      

本文出自 “11097124” 博客,请务必保留此出处http://11107124.blog.51cto.com/11097124/1897568