上面这张图总结了innodb mysql的逻辑和物理架构组成,锁与事务,备份策略以及调优点,以下就每个点进行总结说明。
在线程处理方面,Mysql是多线程的架构,由一个master线程,一个锁监控线程,一个错误监控线程,和多个IO线程组成。并且对一个连接会开启一个线程进行服务。
io线程又分为节省随机IO的insert buffer,用于事务控制的类似于oracle的redo log,以及多个write,多个read的硬盘和内存交换的IO线程。
在内存分配方面,包括innodb buffer pool ,以及log buffer。其中innodb buffer pool包括insert buffer、datapage、index page、数据字典、自适应hash。
在逻辑数据结构方面,innodb包括表空间、段、区、页/块,行。索引结构是B- tree结构,包括二级索引和主键索引,二级索引的叶子节点是主键PK,根据主键索引的叶子节点指向存储的数据块。
在物理存储文件方面,包括frm表定义文件,每个表一个的ibd文件(需要进行设置),ibdata文件(系统表空间MySQL数据库文件,存储InnoDB系统信息和用户数据库表数据和索引,数据字典,undo,所有表共用),
ib_logfile日志文件,binlog二进制日志文件。
为了保证一致性,对于锁的分类,一般分为共享锁和排他锁;mysql主要有这么几个锁,行锁,页锁,表锁,innodb存储引擎是行级锁。
共享锁(S--只允许当前或其他线程读,不允许任何线程修改该记录)和排他锁(X---只允许当前线程update或者delete一行记录,不允许其他线程操作(包括查询该记录)
在做select查询的时,会产生一个共享锁,当然类似select for update会产生一个排他锁。
使用锁的时候,要主要死锁的产生,一般是交叉进行锁相应的资源,常见的场景是,
一个线程根据主键更新索引值(先给数据行加X锁,再给索引块中的记录加X锁),另外一个是根据索引更新相关字段(先给索引块中的记录加X锁,再给数据行加X锁)。
为了保证数据之间的完整性,Mysql innodb支持事务操作,
并且考虑到一致性和并发性的不同要求,事务之间隔离级别有
read uncommited(脏读、不可重复读、幻读(隔离级别最低,并发性能高))、
read commited(会出现不可重复读、幻读问题(锁定正在读取的行))、
repeated read(mysql默认)(会出幻读(锁定所读取的所有行))、
serialize read(证所有的情况不会发生(锁表))。
在备份方面,分为冷备份(物理文件copy,数据文件、undo、插入缓冲等-------停机),温备份(dump,import----要加锁),热备份(xtrabackup)。
可以进行有全量的备份,增量的备份(通过binlog或者lsn(xtrabackup)),或者实时性的备份(replication复制)
在性能调优方面,可以从以下几个角度来考虑,
a、操作系统级别,内核以及socket的优化,网络优化bond
b、server级别(连接管理、网络管理、table管理、日志)
c、应用级别(比如索引的考虑,schema的优化适当冗余;优化sql查询导致的CPU问题和内存问题)
d、IO存储级别,
innodb主要用在OLTP类应用,一般都是IO密集型的应用,在提高IO能力的基础上,充分利用cache机制。需要考虑的内容有,
1、在保证系统可用内存的基础上,尽可能的扩大innodb buffer pool,一般设置为物理内存的3/4
2、日志和数据的存储,需要分开,日志是顺序的写,需要做raid1+0,并且用buffer-IO;数据是离散的读写,走direct IO即可,
避免走文件系统cache带来的开销。
存储能力,SAS盘raid操作(raid卡缓存,关闭读cache,关闭磁盘cache,关闭预读,只用writeback buffer,不过需要考虑充放电的问题),
当然如果数据规模不大,数据的存储可以用高速的设备,Fusion IO、SSD。
对于数据的写入,控制脏页刷新的频率,对于数据的读取,控制cache hit率;因此而估算系统需要的IOPS,评估需要的硬盘数量
(fusion io上到IOPS 在10000以上,普通的硬盘150)
4、文件系统的使用,只在记录事务日志的时候用文件系统的cache;尽量避免mysql用到swap
(可以将vm.swappiness=0,内存紧张时,释放文件 系统cache)
5、IO调度优化,减少减少磁头移动
以下是相关参数参考:
假设 Mysql(物理内存32G,CPU物理40core)
扩展表空间带来的抖动问题,需要预先分配表空间
innodb_file_per_table(true)---分散IO
innodb_flush_log_at_trx_commit(设置成1,<2<0)2是文件系统,1是直接flush到存储上
sync_binlog(设置成1<0)0是文件系统
innodb_flush_method(默认值为fdatasync,O_DSYNC和O_DIRECT,这里设置为O_DIRECT)
binlog_cache_size(默认32k)
innodb_buffer_pool_size(设置成24G)
innodb_max_dirty_pages_pct(默认90%--->75%)
innodb_read_io_threads/innodb_write_io_threads(默认都是4个,对于高速的存储设备么可以设置大一些)
innodb_adaptive_flushing(ON,根据断重做日志产生速度确定需要刷新脏页的最合适数目)
innodb_thread_concurrency(cpu core的两倍,80)
innodb_io_capacity(默认值为200---刷新脏页和插入缓冲)
innodb_doublewrite
innodb_purge_threads
max_connections(默认100)
max_user_connections(默认30,改为100)
innodb_page_size(8K,如果全表扫描16K,如果ssd,4k随机性能好)