MySQL 第三方引擎应用场景分析

Infobright是开源的MySQL数据仓库解决方案,引入了列存储方案,高强度的数据压缩优化的统计计算(类似sum/avg/group by之类),列式存储 对单列处理效率非常高 如果涉及到多列相对较慢。

Infobright的默认存储引擎是brighthouse,但是Infobright还可以支持其他的存储引擎,比如MyISAM,MRG_MyISAM, Memory,CSV

企业版增加了DML支持,同时有一些额外的工具集

社区版不支持DML,没有在线热备工具

Infobright优点

  1. 高压缩比率,平均压缩比可达10:1,甚至可以达到40:1。

  2. 列存储,及时数据量十分巨大,查询速度也很快。用于数据仓库,处理海量数据没一套可不行。

  3. 不需要建索引,就避免了维护索引及索引随着数据膨胀的问题。把每列数据分块压缩存放,每块有知识网格节点记录块内的统计信息,代替索引,加速搜索。

  4. 单一台服务器可以高效的读写30T数据。具有可扩展性,这里是指对于同样的查询,当数据量是10T时,它耗费的时间不应该比1T数据时慢太多,基本是一个数量级内。

缺点:

  1. 不支持DML

  2. 不支持多核

  3. 不支持分布式

image.png

loader与unloader是infobright的数据导入导出模块,也即处理SQL语句里LOAD DATA INFILE ··· 与 SELECT “` INTO FILE任务,由于infobright面向的是海量数据环境

所以这个数据导入导出模块是一个独立的服务,并非直接使用muysql的模块

逻辑层的infobright优化器包再mysql查询优化器的外面,因为它的存储层有一些特殊结构,所以查询优化方式也跟mysql有很大差异

存储层最底层是一个个的Data Pack(数据块)。每一个pack装着某一列的64K个元素,所有数据按照这样的形式打包存储,每一个数据块进行类型相关的压缩(即根据不同类型采用不同的压缩算法),压缩比很高。它上层的压缩器与解压缩器就做了这个事情。

Knowledge Grid(知识网格)中包含两类节点:

  • 每个Data Pack Node(数据包节点)对应一个Data Pack,存储改Data Pack的一些统计信息,如min,max,avg,null个数,单元总数count,sum总数等,甚至不同值的量等等;

  • KN(Knowledge Node,知识节点)则存储了一些更高级的统计信息以及与其他表的连接信息,这里面的信息有些事是数据载入时已经算好的,有些事随着查询进行而计算的,所以说是具备一定的“智能”的

KN里面存储着指向DP之间或者列之间关系的一些元数据集合,比如值发生的范围(Min_Max),列数据之间的关联。大部分的KN数据是装载数据的时候产生的,另外一些是查询的时候产生。

image.png

Histogram用来提高数字类型(比如data,time,decimal)的查询性能。Histogram是装载数据的时候就产生的。

DPN中有mix,max,Histogram中把Min-Max分成1024段,如果Min_Max范围小于1024的话,每一段就是一个单独的值。

这个时候KN就是一个数值是否在当前段的二进制表示。

image.png

CMAP是针对于文本类型的查询,也是装载数据的时候就产生的。CMAP是统计当前DP内,ASCII在1-64位置出现的情况。如下图所示

image.png

比如说上面的图说明了A再文本的第二个,第三个,第四个位置从来没有出现过。0表示没有出现,1表示出现过。

查询中文本的比较归根究底还是按照字节进行比较,所以根据CMAP能狗很好的提高文本查询的性能。

Pack-To-Pack(P-2-P)是join操作的时候产生的,它是表示join的两个DP中操作的两个列之间关系的位图,也就是二进制表示的矩阵。

image.png

这个sql在innodb里跑了2.96秒

infobright vs innodb

0.29s vs 2.96s

MySQL InnoDB内存结构

  • innodb_buffer_pool

  • innodb_additional_mem_pool_size

  • innodb_log_buffer_size

这3个内存参数是可见的,可控的

还有一些是看不见,不可控的靠mysql内部管理的:

  • adaptive index hash

  • system dictionary hash

  • locking system

  • sync_array

  • os_events

Innodb内存结构

image.png

innodb在5.6以前undo 是放在共享表空间里面的

在高并发的时候,可能会导致ibdata1文件持续增长

ibdata1这个共享表空间又是不能自动释放的 

如果在高并发一直没有提交有大量事务等待回滚的时候会把大量脏数据放在undo里面

所以会导致ibdata1特别庞大 特别是32位的系统下

需要我们及时提交事务 不要把大量事务堆积在innodb redo

或者在初始化的时候把ibdata1初始化大一些 最好在1G以上

5.6可以有独立undo 但是很麻烦 不建议使用

5.7可以设置undo大小和路径、

1.innodb_buffer_pool

    • Innodb高速缓冲(简称IBP),是Innodb最重要的组成部分

    • InnoDB不依赖OS,而自己缓存了所有数据,把索引和数据(包括索引数据,行数据,等等)放在内存里面。这点跟MyISAM有差别MyISAM只是把索引放在key buffer里面,数据还是用OS的page cache进行缓存

    • 应该把innodb_buffer_pool_size设置得大一些,建议设置为可用RAM的50%~80%

        • 如果前端使用连接池 那么设置能70%会相对安全一点,因为前端要有连接池 就不会有特别大的连接连接进来,PGA分配的内存相对就没那么大,所以我们可以留更多的内存给SGA

        • 但是前端如果有大量连接并发的时候,我们建议innodb_buffer_pool_size设置小一点 50%

    • 查询或更新的时候会对IBP加锁,影响并发,innodb会把内存分成多分,来缓解并发带来的影响

    • IBP有一块buffer用于插入缓冲,在插入的时候,先写入内存,之后再合并后顺序写入磁盘。在合并到磁盘上的时候,会引发较大的IO操作,对实时操作造成影响(看上去是抖动,tps变低)

    • show global status like 'innodb_buffer_pool_%' 查看IBP状态们单位是page(16kb)

    • Innodb_buffer_pool_wait_free 如果较大,需要加大IBP设置

2. innodb_buffer_pool_instances 

        如果内存不超过8G,就没有必要分多个instance

        分多个instance时,最好每个instance至少2G以上8G以下

        innodb_buffer_pool_instances也不要太多,一般不超过8

image.png

image.png

innodb_buffer_pool 现在默认值是128M(老版本默认8M,坑很多)

[MySQL FAQ]系列 — 数据不算大,备份却非常慢

http://imysql.com/2009/09/18/mysql-faq-why-backup-is-so-slow.html

3.Adaptive Hash Index

自适应哈希索引,用来管理buffer pool的哈希索引

  • adaotive index hash, size= innodb_buffer_pool / 64, 随着buffer的频繁更新,会随之上升

  • system dictionary hash, size=innodb_buffer_size / 256,基本固定

  • memory for sync_array, which is used for syncronization primitives, size=OS_THREADS(当前线程数) * 152

  • memory for os_events, which are also used for syncronization primitives, size=OS_THREADS * 216

  • memory for locking system, size = 5*4*NBLOCKS(NBLOCK, innodb buffer pool的block数量),随着并发、行锁增加,会随之上升

概念:

  • OS_THREADS=如果innodb_buffer_pool_size >= 1000Mb, 则为:50000, 否则如果innodb_buffer_pool_size >= 8Mb,则为:10000, 否则为:1000(*nixes平台下通用)

  • NBLOCKS = innodb_buffer_pool_size/8192

image.png

image.png

如果innodb_buffer_pool > 1000MB, OS_THREADS*368=17.5MB

否则如果innodb_buffer_pool > 8MB , 3,5m

image.png

我们只能设置innodb_buffer_pool_size为一个合适值 因为其他几个我们没有办法控制

4.innodb_additional_mem_pool_size

数据字典以及内部数据结构缓存,表数据量越多,相应的内容需要越大。

默认8M,通常设置为8~32M足够,一般建议设置为16M,如果确实不够,那么会从系统中请求增加分配内存,并且错误日志中会提醒,目前至少还未发生过。

5.innodb_log_buffer_size

show global status查看Innodb_log_waits是否大于0, 是的话,就需要提高innodb_buffer_pool_size,否则维持原样。

show global status查看30~60秒钟Innodb_os_log_written 的间隔差异值,即可计算出innodb_buffer_pool_size设置多大合适。