Innodb 文件结构分析 (三) row format 以及字符集

从两个参数入手:

mysql> show variables like '%row_format%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)

mysql> show variables like '%file_format%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+
3 rows in set (0.00 sec)

mysql> 

这里是5.7 的版本,在 8 的版本里 innodb_file_format 已经废弃,这样举例是把所有的格式都做下总结;首先看文件的格式,在mysql前后出现过两种文件格式:

antelope: 是早期的Mysql文件格式,它支持两种方式的行记录格式compact 和redundant ;

barracude:是后期新的文件格式,它支持两种行记录格式分别是compress和dynamic;其中compress 参数只能用于在创建表时;而不能指定为系统的行格式,因为系统表空间并不支持压缩存储;

从数量上说总共有4种 row 格式,实际是dynamic和compact的区别非常小,所以也可以看做是三种格式,首先来看看 redundant格式:

  • Redundant

row1

如图所示一个完整的行记录,主要包含如下部分:

offset array:此部分为变长,其作用是指出从 origin 处开始的,列的偏移量;每个列的偏移量由1byte或者2byte表示;此数组列表中的每个元素的具体长度由 6-byte bit field 中的1byte_offs_flag 值决定;

6-byte bit field,又称为REC_N_OLD_EXTRA_BYTES,是固定长度 6byte,其主要记录列的相关信息,每个bit位的具体意思如下:

row2

hidden field 实际由三个部分组成:primary key/DB_ROW_ID,如果是后者为定长 6bytes;DB_TRX_ID 事务id,定长6byte,DB_ROLL_PTR 回滚段地址,定长7bytes;

其余为具体的表定的其它列,这里要说明的是如果表没有加主键,那么系统会自动生成 6byte的 DB_ROW_ID作为主键,那么Column1 就是行的第一列,如果表在创建时添加了主键,那么此处Column1就是行记录的第二列;

  • compact/dynamic

compact 和dynamic 格式的行记录基本是相同的,只是在不同的数据类型存储上,dynamic有更优秀之处,其主要组成部分为:

row4

offset array:此处为变长部分,记录具体列的长度,数值的每个元素长度为1byte或者2byte,具体是由列的类型定义长度与实际存储长度决定的,详细可以参考:http://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html

Null bit field:每个bit代表一个null 列,所以null 列的个数决定了此部分的长度;null 列不占用空间;

5byte bit field:也就是REC_N_NEW_EXTRA_BYTES 

row5

其余部分与前者同;

dynamic 与compact的两种格式的区别在于:blob 的存储上,dynamic 只存储前20byte 在row 中,而compact 存储前768 byte; 这样dynamic格式更灵活;对于多列也有很多好处;

字符集实际就是 字符能够被计算机软硬件识别的组织方式;不同的字符集的组织形式是不一样的,比如ASCII,是每个字符一个byte,用其中的7bit进行表示,以0–127 代表一个字符,而Unicode 是用整数,两byte –16bit代表一个字符的,所以了解字符集对于数据的解析是非常有好处的;由于不同的字符集导致字符占用的实际存储长度也不同,所以在进行数据的解析,迁移等等操作时,需要更好的考虑字符集带来的影响;

Innodb 文件结构分析 (二) – 数据存储类型

在Mysql当中,数据类型可以分为三大类:数值类型、日期和时间类型、字符类型;每个类型又可以详细划分为数个详细的子类,

需要注意的是有些类型是底层的数据类型的别名,举个例子:

mysql> create table dtype (id bool);
Query OK, 0 rows affected (0.03 sec)
mysql> show create table dtype \G
*************************** 1. row ***************************
       Table: dtype
Create Table: CREATE TABLE `dtype` (
  `id` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 

从这里可以看到底层的存储仍然是 tinyint 的;下面详细看看各个具体的分类与存储方式:

数值

dt1

在数值类型当中需要注意的是float 类型,其长度是4byte 或者8byte,如果是Float(x)x>24则为8byte否则为4byte,如果是float(x,y) 则为4byte;这里把bit放入到数值类型当中,是因为bit的底层存储是二进制的,就是0和1组成的数字,所以我放到这里,是自己的一个习惯性做法不通用;知道了每个数值类型的长度那么在进行数据文件的的行解析时,就能判断每个类型的长度,这样就比较容易直接转换数据;那么数值在底层是如果转换存储的呢? 以tiny int 为例子,其长度是1byte,而1byte 是8bit,bit从存储是二进制的,也就说1一个byte 可以存储的最大的值为 1111 11111,转换为10进制就是 255 ,也就是0–255 ,如果按照这种计算方式,存储的全部为正数这种情况是mysql unsigned方式,如果有负数呢,如果是有正负数的情况,此时会使用 第一个 bit 作为正负的标志位,正数为1,负数为0,所以此时有7个bit位是作为数值的存储的,也就是 +-127;

数值的存储方式大多如此,那么数值类型当中有特殊情况的是:decimal类型,在之前的版本中decimal实际是以字符串的形式进行存储的在大当前版本中是binary方式,同时需要注意如何计算decimal的长度;decimal 的整数部分每9个数字占用4个字节,小数部分也是如此,不足9个数字的按照下面的方式进行计算:

dt2

因为decimal 是由定义长度决定存储长度的,所以在文件解析时需要特别对待;其它的类型基本都是固定长度;

日期时间:

dt4

日期与时间类似是固定长度的,但是存储长度还是有一定的变化的,如上图图所示:

字符类型包括:

dt5

对于字符类型来说需要,明确binary strings 和character strings 的区别;

在myql当中还包含其他的类别的数据类型,这里不一一介绍,主要介绍的是常用的类型;

从上面的分析可以看出,知道数据类型的定义后,就可以清楚的判断出row 在存储时,每个列的长度,这样对于数据的解析有很好的帮助,如果不知道类的类型,那么有可能只能去猜测,这样就会存在各种误差;

Mysql 中Dtrace的应用

Dtrace是一个非常好的动态跟踪工具,并且在Mysql当中就是支持Dtrace的;尤其是performance_schema中信息的采集;有关Dtrace的基本知识,语法等可以参考dtrace的官方文档:http://dtrace.org/guide/chp-user.html 初次使用dtrace可能会感觉无从下手,这里分享下自己经验;

注:测试环境为:Mysql 8,MAC os 10.10.5 ;

在mac 中有一个命令 dtruss ,实际它的最终来源是dtrace,需要高权限才能执行;所以对dtrace 熟悉后可以直接使用dtrace;

[HuSi@HuoSi]mysql8 $ sudo dtrace -l -m mysqld 
sudo: cannot get working directory
Password:
   ID   PROVIDER            MODULE                          FUNCTION NAME
dtrace: failed to match :mysqld::: No probe matches description

这里想看看有关Mysql的模块信息,但是你会发现此时什么都没有,下面可以启动mysql 后再检查:

[HuSi@HuoSi]mysql8 $ pgrep -x mysqld 
[HuSi@HuoSi]mysql8 $ ./mysqlstart start
Starting MySQL
 SUCCESS! 
[HuSi@HuoSi]mysql8 $ pgrep -x mysqld
16279
[close_connection(THD*, unsigned int, bool, bool)] connection-done
 5458 mysql16279            mysqld _Z22thd_prepare_connectionP3THD [ha_myisammrg::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)] index-read-row-done
 5479 mysql16279            mysqld _ZN9ha_myisam15index_next_sameEPhPKhj 

----省略N行-----

[Sql_cmd_delete_multi::execute(THD*)] multi-delete-start
 9772 mysql16279            mysqld _ZN14Sql_cmd_update26execute_multi_table_updateEP3THD [Sql_cmd_update::execute_multi_table_update(THD*)] multi-update-done
 9773 mysql16279            mysqld _ZN14Sql_cmd_update26execute_multi_table_updateEP3THD [execute_sqlcom_select(THD*, TABLE_LIST*)] select-start
 9949 mysql16279            mysqld _ZN14Sql_cmd_update23try_single_table_updateEP3THDPb [Sql_cmd_update::try_single_table_update(THD*, bool*)] update-done
 9950 mysql16279            mysqld _ZN7handler13ha_update_rowEPKhPh [handler::ha_update_row(unsigned char const*, unsigned char*)] update-row-done
 9951 mysql16279            mysqld _ZN7handler13ha_update_rowEPKhPh [handler::ha_update_row(unsigned char const*, unsigned char*)] update-row-start
 9952 mysql16279            mysqld _ZN14Sql_cmd_update23try_single_table_updateEP3THDPb [Sql_cmd_update::try_single_table_update(THD*, bool*)] update-start

当Mysql启动后可看到dtrace 可以跟踪的具体函数;实际上在Mysql的官方文档里面归类介绍了可以进行Dtrace跟踪的函数;目前5.7 的文档中是有的,但是8 的文档中还没加入Dtrace的有关内容,但是不影响具体的功能;官方介绍的比较笼统,如果是初学者可能会感到困惑,那怎么具体使用Dtrace呢?下面来看看;比如我想查看跟 parse 有关的函数:

[HuSi@HuoSi]mysql8 $ sudo dtrace -l -m mysqld  | grep parse 
 9784 mysql16279            mysqld _Z11mysql_parseP3THDP12Parser_state [mysql_parse(THD*, Parser_state*)] query-exec-done
 9788 mysql16279            mysqld _Z11mysql_parseP3THDP12Parser_state [mysql_parse(THD*, Parser_state*)] query-exec-start
 9791 mysql16279            mysqld _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx [parse_sql(THD*, Parser_state*, Object_creation_ctx*)] query-parse-done
 9792 mysql16279            mysqld _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx [parse_sql(THD*, Parser_state*, Object_creation_ctx*)] query-parse-start

这里选取一条来说明下:9792 mysql16279 mysqld _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx [parse_sql(THD*, Parser_state*, Object_creation_ctx*)] query-parse-start

需要关注的是:parse_sql(THD*, Parser_state*, Object_creation_ctx*),parse_sql 是函数名称,括号里是相关的参数,比如像看看这个函数具体的做什么:

[HuSi@HuoSi]mysql8 $ sudo dtrace -n 'mysql*:::query-parse-start {printf ("%Y   %s\n ",walltimestamp,copyinstr(arg0));}'
dtrace: description 'mysql*:::query-parse-start ' matched 1 probe

此时数据库没有执行任何的解析所以是没有结果的,下面执行几个简单的sql:

[HuSi@HuoSi]mysql8 $ bin/mysql -uroot -proot 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test;
+------+---------------------------------------+
| id   | name                                  |
+------+---------------------------------------+
|    1 | jjjjjj                                |
|   12 | jjjj                                  |
|   19 | uuuuuu                                |
|   19 | uuuuuu-------                         |
..................
.....................
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | uuuuuu-------==========oooooljljl     |
|   21 | rectest1                              |
|   21 | rectest1                              |
|   21 | rectest1                              |
|   21 | rectest1                              |
+------+---------------------------------------+
42 rows in set (0.00 sec)

mysql> 

此时dtrace 显示的数据为: 

[HuSi@HuoSi]mysql8 $ sudo dtrace -n 'mysql*:::query-parse-start {printf ("%Y   %s\n ",walltimestamp,copyinstr(arg0));}'
dtrace: description 'mysql*:::query-parse-start ' matched 1 probe
CPU     ID                    FUNCTION:NAME
  0   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   SELECT DATABASE()
 
  2   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   show databases
 
  1   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   show databases
 
  1   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   show tables
 
  0   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:19   show tables
 
  0   9792 _Z9parse_sqlP3THDP12Parser_stateP19Object_creation_ctx:query-parse-start 2016 Dec  2 11:31:45   select * from test
 

在使用Dtrace 时需要注意的一个问题是:copyinstr(arg0) 这里,这个argxxx 这个好理解就是第几个参数,第一个参数是arg0,然后依次的即可,但是如果使用copyinstr,可能会显示乱码等问题,这里可以参考官方文档的第33章节有比较详细的描述;有了上面的例子,其它的mysql的相关函数,基本就这么来就可以,这样就可以能看到你想要的内容了;

关于Dtrace 确实很有用,但是这个不是什么独孤九剑,懂一定有好处,不懂也无害,毕竟使用的地方较少,不能当做一个炫耀的资本,感觉有点类似 oracle bbed似得,只不过 dtrace 使用的范围更广;与源码的结合可以更快的更清晰的掌握相关内容;配合使用Dtrace后,也可以减少学习的枯燥程度;

那么大家可以思考如下的场景:

Mysql监控,常用监控项 OS 层面设置就可以了,如果是db层面的,实际是查询db中的各种信息得来的,如直接使用Dtrace呢,比如想做审计,审计所有解析的 sql,以及解析时间,执行时间等等,用Dtrace 写个脚本然后直接发送给监控系统;还有DB 竞争分析等等;

最后推荐一篇文章:

http://dtrace.org/blogs/brendan/2011/06/23/mysql-performance-schema-and-dtrace/

 

Mysql GTID

从5.6 mysql引入了gtid的概念,但是到8的版本,默认gtid是关闭,如果要打开gtid需要做如下操作:

GTID_MODE = ON

同时 设置:ENFORCE_GTID_CONSISTENCY = ON,

否则会报错:2016-11-29T07:39:35.265167Z 0 [ERROR] GTID_MODE = ON requires ENFORCE_GTID_CONSISTENCY = ON. 从而导致数据库无法启动;在官方文档当中gtid的组成结构为:

GTID = source_id:transaction_id

其中 source_id 是mysql server id,实质它的值和 server_uuid 是一样的;做个简单的测试:

[HuSi@HuoSi]mysql8 $ bin/mysql -uroot -proot -e "show variables like '%uuid%'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 300e39aa-7e2c-11e6-a991-22aaeca26514 |
+---------------+--------------------------------------+

在来看看binlog 中记录的值:

#161129 15:52:48 server id 8  end_log_pos 219 CRC32 0x186fb5bf 	GTID	last_committed=0	sequence_number=1
SET @@SESSION.GTID_NEXT= '300e39aa-7e2c-11e6-a991-22aaeca26514:1'/*!*/;

可以看到 source_id 和 uuid 是相同的,需要注意uuid 本身的值是可以更改的;

这里有这样一个场景假如:1、此时uuid为 a,2、修改为b,3、再次修改为a,会响应数据库吗?

证券交易所行情数据架构的简单实践

公司引进了上海证券交易所和深圳证券交易所行情数据,行情数据分为两个解绑level-1,和level-2 ,我们用的是level-1,在level-1中 上交所数据的更新频率是 5s(据说要升级到3s),深交所的更新频率是 3s;截止到2016 最新的上市公司情况为:

2016年10月18日中国股市上市公司数量、总市值
股市总况 深圳股市 上海股市 沪深合计
上市公司(家) 1,824 1,140 2,964
上市证券(只) 4,225 8,664 12,889
总 市 值(亿元) 227,321.16 275,085.14 502,406
流通市值(亿元) 155,204.35 233,509.40 388,714

那么一天的交易时间按照 4小时计算,单是股票行情逐笔数据的数据量大概为:2964*(60/3)*4*60 条,同时需要汇总生成 1分钟,5分钟,15分钟,1小时等行情数据,每个交易日大概为1600w左右,如果再加上其它指数等数据数据量还会增加不少,而这些数据要全部存储下来,行情的历史数据还是比较值钱的;如果把连续几年的逐笔数据存储下来然后进行相关的研究对金融方面的理解还是比较有益的;整体来说这种数据每天的量不是很大,但是一般这种数据都是供行情应用使用的,所以对稳定性要求高一些;下面简单介绍下,笔者使用架构,很普通,够用就好;

level1

一般接收证交所的行情数据都是使用专线的,这个没有什么特别的,一般的IDC都提供专线接入;证交所的数据一般需要落地到一台windows上,因为其提供的基本就是这种win版的数据同步客户端,这里称之为sessinfo node;我们使用的Mysql作为短期存储,开始使用的基本架构是:

34a1587b-b8e3-411e-9539-a2082e11597f

在sess-info-net node上面同时运行了自行开发的转码工具,将证交所发送过来的数据快照,转码为数据库数据,数据库使用传统的M-S架构,这种架构存在的缺点是master 会成为单点,Replication 功能可能会停止,这都会造成前端数据的不正常,而影响整体的功能;那么我们发展的一个方向是改造成,多主结构,即数据会同时写入多个节点,但是多写的数据一致性是需要保证的,完全自己单独开发一个中间件似乎也不太现实,这里我们选取了cobar–简单、轻量、易用,还有源码;

level2

需要注意的是 cobar如何完成多多个表的同时写,注意这里不是分表;经过一段的测试并没有网上所说的有关cobar的那些问题;那么此时的问题是cobar 成为单点,对cobar做一个高可用,似乎不妥,因为cobar节点的切换也是需要时间的,那么怎么办呢?于是又做了些改进:

level3

这里自行开发了 快照传输程序,即将行情快照,同时向一个节点将快照发送出来,然后在reciver node上进行转码然后存储到一个备份mysql当中,如果cobar有问题,或者几个主同时宕机了也不会丢数据;对外提供数据时,同时走代理将负载分发到不同的节点上,这里的代理的使用可以参考:confd+haproxy+etcd;

上面就是整个架构的简单描述,细节上需要注意的是自己开发的快照转码产生的数据量,以及快照传输问题,还有就是代理的设置问题,比如代理节点的修改,无缝切换(haproxy 无缝切换)等问题;

Innodb 文件结构分析 (一) –系统隐藏表

这里是Innodb 文件结构分析的预备知识,主要介绍数据字典表;在mysql 8中.frm 的数据字典文件已经消失,所有的数据文件都已经存储Innodb 的数据表中;在 information_schema 中存在数个表面看是数据字典表(实际是视图),

Mysql-HuoSi->show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLLATIONS                            |
| COLUMN_PRIVILEGES                     |
| COLUMNS                               |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| INNODB_BUFFER_PAGE                    |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_CACHED_INDEXES                 |
| INNODB_CMP                            |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_CMP_RESET                      |
| INNODB_CMPMEM                         |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_BEING_DELETED               |
| INNODB_FT_CONFIG                      |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_DELETED                     |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_LOCK_WAITS                     |
| INNODB_LOCKS                          |
| INNODB_METRICS                        |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_DATAFILES                  |
| INNODB_SYS_FIELDS                     |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_TABLES                     |
| INNODB_SYS_TABLESPACES                |
| INNODB_SYS_TABLESTATS                 |
| INNODB_SYS_VIRTUAL                    |
| INNODB_TEMP_TABLE_INFO                |
| INNODB_TRX                            |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMA_PRIVILEGES                     |
| SCHEMATA                              |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| SHOW_STATISTICS                       |
| SHOW_STATISTICS_DYNAMIC               |
| STATISTICS                            |
| STATISTICS_BASE                       |
| STATISTICS_DYNAMIC                    |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TABLES                                |
| TABLES_DYNAMIC                        |
| TABLESPACES                           |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
67 rows in set (0.00 sec)

在这里可以看起定义:

Mysql-HuoSi->show create table COLUMNS \G
*************************** 1. row ***************************
                View: COLUMNS
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `COLUMNS` AS select (`cat`.`name` collate utf8_tolower_ci) AS `TABLE_CATALOG`,(`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,(`col`.`name` collate utf8_tolower_ci) AS `COLUMN_NAME`,`col`.`ordinal_position` AS `ORDINAL_POSITION`,`col`.`default_value_utf8` AS `COLUMN_DEFAULT`,if((`col`.`is_nullable` = 1),'YES','NO') AS `IS_NULLABLE`,substring_index(substring_index(`col`.`column_type_utf8`,'(',1),' ',1) AS `DATA_TYPE`,internal_dd_char_length(`col`.`type`,`col`.`char_length`,`coll`.`name`,0) AS `CHARACTER_MAXIMUM_LENGTH`,internal_dd_char_length(`col`.`type`,`col`.`char_length`,`coll`.`name`,1) AS `CHARACTER_OCTET_LENGTH`,if((`col`.`numeric_precision` = 0),NULL,`col`.`numeric_precision`) AS `NUMERIC_PRECISION`,if(((`col`.`numeric_scale` = 0) and (`col`.`numeric_precision` = 0)),NULL,`col`.`numeric_scale`) AS `NUMERIC_SCALE`,`col`.`datetime_precision` AS `DATETIME_PRECISION`,(case `col`.`type` when 'MYSQL_TYPE_STRING' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_SET' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) else NULL end) AS `CHARACTER_SET_NAME`,(case `col`.`type` when 'MYSQL_TYPE_STRING' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_SET' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) else NULL end) AS `COLLATION_NAME`,`col`.`column_type_utf8` AS `COLUMN_TYPE`,`col`.`column_key` AS `COLUMN_KEY`,if((ifnull(`col`.`generation_expression_utf8`,'IS_NOT_GC') = 'IS_NOT_GC'),if((`col`.`is_auto_increment` = TRUE),concat(ifnull(concat('on update ',`col`.`update_option`,' '),''),'auto_increment'),ifnull(concat('on update ',`col`.`update_option`),'')),convert(if(`col`.`is_virtual`,'VIRTUAL GENERATED','STORED GENERATED') using utf8)) AS `EXTRA`,get_dd_column_privileges(`sch`.`name`,`tbl`.`name`,`col`.`name`) AS `PRIVILEGES`,ifnull(`col`.`comment`,'') AS `COLUMN_COMMENT`,ifnull(`col`.`generation_expression_utf8`,'') AS `GENERATION_EXPRESSION` from (((((`mysql`.`columns` `col` join `mysql`.`tables` `tbl` on((`col`.`table_id` = `tbl`.`id`))) join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `coll` on((`col`.`collation_id` = `coll`.`id`))) join `mysql`.`character_sets` `cs` on((`coll`.`character_set_id` = `cs`.`id`))) where (internal_get_view_warning_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`) and can_access_column(`sch`.`name`,`tbl`.`name`,`col`.`name`) and (not(`tbl`.`hidden`)))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

从这里可以看到这个视图的源表为 mysql.xxxx,那么我们直接去访问下,相关的表看看:

Mysql-HuoSi->use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Mysql-HuoSi->show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| procs_priv                |
| proxies_priv              |
| role_edges                |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
32 rows in set (0.00 sec)

这里可以看到,并没有想要看的表,如果直接查询呢:

Mysql-HuoSi->select * from mysql.columns;
ERROR 3554 (HY000): Access to system table 'mysql.columns' is rejected.

这里会报错,那么这些表到底存不存在呢,答案是肯定的:

[HuSi@HuoSi]mysql $ pwd
/Volumes/ssd/hadoop/soft/mysql-8/mysql8/data/mysql
[HuSi@HuoSi]mysql $ ls
catalogs.ibd				help_relation.ibd			slow_log.CSM
character_sets.ibd			help_topic.ibd				slow_log.CSV
collations.ibd				index_column_usage.ibd			slow_log_103.SDI
column_stats.ibd			index_partitions.ibd			st_spatial_reference_systems.ibd
column_type_elements.ibd		index_stats.ibd				table_partition_values.ibd
columns.ibd				indexes.ibd				table_partitions.ibd
columns_priv.ibd			innodb_index_stats.ibd			table_stats.ibd
component.ibd				innodb_table_stats.ibd			tables.ibd
db.ibd					parameter_type_elements.ibd		tables_priv.ibd
default_roles.ibd			parameters.ibd				tablespace_files.ibd
engine_cost.ibd				plugin.ibd				tablespaces.ibd
events.ibd				procs_priv.ibd				time_zone.ibd
foreign_key_column_usage.ibd		proxies_priv.ibd			time_zone_leap_second.ibd
foreign_keys.ibd			role_edges.ibd				time_zone_name.ibd
func.ibd				routines.ibd				time_zone_transition.ibd
general_log.CSM				schemata.ibd				time_zone_transition_type.ibd
general_log.CSV				server_cost.ibd				triggers.ibd
general_log_102.SDI			servers.ibd				user.ibd
gtid_executed.ibd			slave_master_info.ibd			version.ibd
help_category.ibd			slave_relay_log_info.ibd		view_routine_usage.ibd
help_keyword.ibd			slave_worker_info.ibd			view_table_usage.ibd
[HuSi@HuoSi]mysql $ 

这里可以看到对应的 数据文件是存在的,那么之所以隐藏数据字典表的目的应该是防止直接对这些表的修改,从而导致的数据库错误;那么这些表可以查看吗,答案是可以,但是要将启动方式改为debug的方式,下面来看一一看:

[HuSi@HuoSi]mysql8 $ bin/mysqld-debug --defaults-file=./my.cnf &
[1] 4270

然后登陆数据库:

[HuSi@HuoSi]mysql8 $ bin/mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.0-dmr-debug-log MySQL Community Server - Debug (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET SESSION debug='+d,skip_dd_table_access_check';
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table columns;
+---------+----------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------+----------------------------------------------------------------------------------------------------------+
| columns | CREATE TABLE `columns` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_id` bigint(20) unsigned NOT NULL,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_tolower_ci NOT NULL,
  `ordinal_position` int(10) unsigned NOT NULL,
  `type` enum('MYSQL_TYPE_DECIMAL','MYSQL_TYPE_TINY','MYSQL_TYPE_SHORT','MYSQL_TYPE_LONG','MYSQL_TYPE_FLOAT','MYSQL_TYPE_DOUBLE','MYSQL_TYPE_NULL','MYSQL_TYPE_TIMESTAMP','MYSQL_TYPE_LONGLONG','MYSQL_TYPE_INT24','MYSQL_TYPE_DATE','MYSQL_TYPE_TIME','MYSQL_TYPE_DATETIME','MYSQL_TYPE_YEAR','MYSQL_TYPE_NEWDATE','MYSQL_TYPE_VARCHAR','MYSQL_TYPE_BIT','MYSQL_TYPE_TIMESTAMP2','MYSQL_TYPE_DATETIME2','MYSQL_TYPE_TIME2','MYSQL_TYPE_NEWDECIMAL','MYSQL_TYPE_ENUM','MYSQL_TYPE_SET','MYSQL_TYPE_TINY_BLOB','MYSQL_TYPE_MEDIUM_BLOB','MYSQL_TYPE_LONG_BLOB','MYSQL_TYPE_BLOB','MYSQL_TYPE_VAR_STRING','MYSQL_TYPE_STRING','MYSQL_TYPE_GEOMETRY','MYSQL_TYPE_JSON') COLLATE utf8_bin NOT NULL,
  `is_nullable` tinyint(1) NOT NULL,
  `is_zerofill` tinyint(1) DEFAULT NULL,
  `is_unsigned` tinyint(1) DEFAULT NULL,
  `char_length` int(10) unsigned DEFAULT NULL,
  `numeric_precision` int(10) unsigned DEFAULT NULL,
  `numeric_scale` int(10) unsigned DEFAULT NULL,
  `datetime_precision` int(10) unsigned DEFAULT NULL,
  `collation_id` bigint(20) unsigned DEFAULT NULL,
  `has_no_default` tinyint(1) DEFAULT NULL,
  `default_value` blob,
  `default_value_utf8` text COLLATE utf8_bin,
  `default_option` blob,
  `update_option` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `is_auto_increment` tinyint(1) DEFAULT NULL,
  `is_virtual` tinyint(1) DEFAULT NULL,
  `generation_expression` longblob,
  `generation_expression_utf8` longtext COLLATE utf8_bin,
  `comment` varchar(2048) COLLATE utf8_bin NOT NULL,
  `hidden` tinyint(1) NOT NULL,
  `options` mediumtext COLLATE utf8_bin,
  `se_private_data` mediumtext COLLATE utf8_bin,
  `column_key` enum('','PRI','UNI','MUL') COLLATE utf8_bin NOT NULL,
  `column_type_utf8` mediumtext COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `table_id` (`table_id`,`name`),
  UNIQUE KEY `table_id_2` (`table_id`,`ordinal_position`),
  KEY `collation_id` (`collation_id`),
  CONSTRAINT `columns_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`),
  CONSTRAINT `columns_ibfk_2` FOREIGN KEY (`collation_id`) REFERENCES `collations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3464 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC |
+---------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


这里就可以看到具体的表了;那么在解析数据字典表的定义对解析 innodb的数据文件有什么帮助呢?这里做下说明,假如没有数据字典的定义,当拿到一个表的数据文件时,其具体的record 记录的存储格式为: variable length–null Column—extr byte—rowid–transaction id–roll back segment pointer—fields ;如果所有的列都是定长且不为空,那么fields 这里可能会造成无法判断其具体的长度,从而无法解析具体的数据;所以知道数据字典表的具体定义后可以,可以将对应表的定义找到,从而方便解析数据;数据库隐藏的表共有:

catalogs
character_sets
collations
columns
foreign_key_column_usage
foreign_keys
index_column_usage
indexes
index_stats
schemata
tables
table_stats

其实当一个表发生损坏时,一般不影响查看表的定义,这里可以作为一种预备知识,当数据库损坏比较严重时,以备不时之需,实际用处很少,只是一个兴趣方面的研究罢了;

mysql read ahead

read ahead的功能出现于mysql 5.0 的版本,此前只有 sequence read ahead和 random read ahead两种,其目的是为了提高相应场景的读性能,其功能是由单独的后台线程完成的;在5.6 的版本当中又引进了新的方式:logical read ahead;需要注意一下:在mysql 5.5 的官方手册中,预读的两种方式是 linear read ahead 和 random read ahead,而在5.7中是 sequence read ahead 和 random read ahead,但是根据定义 linear 和 sequence应该是相同的,这里我们统称为liear read ahead;

  • linear read ahead

linearra

此种预读是指在读取数据时,如果会顺序的对一个extent的page进行读取时,其读取的page数量超过或者等于参数()值时,后台线程会将与此extent相邻的下一个extent 全部异步读取到bp中;当用户线程试图读取bp中的一个page时,此时就会触发此种预读,mysql会首先判断此page是否是此extent的边界,如果是会接着统计其所在的区有多少个page位于bp以及对它们的读取方式,如果位于bp的page 数量超过参数()值,此时就会执行异步预读;

  • random read ahead

randra

此种方式的预读是指在读取bp中的每个page时,都会观察其所在extent 有多少位于bp当中,并且如果当前extent中的位于bp的page 当中有超过一定数量的page 全部被访问,此时就会对当前extent 其余的page 进行预读;也就说随机预读针对的是当前的extent;

  • logical read ahead

logicalra

当表的碎片化比较严重时,即使是执行全表扫描,速度也会下降,此时如果执行 linear 预读 ,其效果也会打折,因为读取整个extent未必能提升读性能,因为extent中可能都是无效的page,此时逻辑预读就可以发挥作用了;逻辑预读的工作方式是:

1、读取 主键的INODE page

2、然后收集 叶子的page number

3、按照page number 的排序读取page,按照主键的排序读取行数据

这样可以尽可能多的保障每个读取的page 都是有效的数据页

 

自建cdn节点的应用 rtmp 视频流

视频直播系统一个最大的成本是视频带宽,这也是压死很多平台的一个死节;视频带宽的成本有多大呢,可以算一笔账,假如一个客户端占用100k,10w在线,cdn的价格参差不齐,以50/m计算,那么每个月的成本的50w左右,一年600w,钱就这么被cdn厂商赚走了,那么算法可以自己建节点降低成本呢,当然可以;首先熟悉下cdn的原理(网络来源):

1434705391814057528

从这里可以看出cdn的基本原理;那么自建cdn节点怎么做呢;首先需要选定一个区域性的节点,并计算某区域内胆流量,租借当地的带宽,如果一次租借1g以上,那么价格会变的很低,这样就可以减低带宽成本了;剩下的就是自己搭建cdn节点了;

cdn1

这里对dns调度就不在多叙,首先rtmp源可以推送到第三方的cdn上面,然后自建cdn节点会获取视频流,并在内部转发;此时有用户请求时,会被调度到当地自建的机房获取视频流,用户越多自建机房承担的流量就越多,而第三方的cdn带宽占用只是一个视频流;但是毕竟自建带宽有限,如果遭遇攻击,会占满带宽,那么怎么办呢?可以使用让LB将请求发送给第三方cdn,这样就可以不影响正常的使用;如果lb挂了,没关系dns可以直接掉到到第三方cdn上面;这里并不是抛弃第三方cdn,毕竟多数公司不可能建成像网宿、阿里等那样的cdn网络,所以第三方还是需要的;

基于spark stream 实现mysql错误日志的监控

说明:很多听起来高大上的东西实际并不一定有多难,下面演示下基于spark stream的数据库监控功能,用到的环境为mysql 8,spark 1.6 ,flume 1.7 ;通过这个简单功能的扩展,就可以实现一个定制的监控系统;

1.mysql 配置

mysql错误日志的配置:

log_output=FILE 

log_error=/Volumes/ssd/hadoop/soft/mysql-8/mysql8/log/HuoSi.err    

log_error_verbosity= 3                         

在配置完成以上的参数后,基本可以记录到想要的错误信息了,下面看看具体的错误日志的格式:

error

     这里的的数字部分分布代表:

1、时间

2、同一个错误在同一个实例生命周期出现的次数,但是不完全准确(需要再深入确定)

3、错误级别,有三种:note ,warning,error

4、错误具体内容

2、Flume配置

flume 版本 1.7,下载地址:http://flume.apache.org/download.html,下载编译好的版本,可以直接运行

flume 的配置文件:

a1.sources = r1

a1.sinks = k1

a1.channels = c1

a1.sources.r1.type = exec

a1.sources.r1.command=tail -F /Volumes/ssd/hadoop/soft/mysql-8/mysql8/log/HuoSi.err

a1.sources.r1.channels=c1

a1.sinks.k1.type = avro

a1.sinks.k1.hostname = localhost

a1.sinks.k1.port = 3333

a1.channels.c1.type = memory

a1.channels.c1.capacity = 1000

a1.channels.c1.transactionCapacity = 100

a1.sources.r1.channels = c1

a1.sinks.k1.channel = c1

运行使用的命令:

bin/flume-ng agent –conf conf –conf-file conf/flume.push.conf –name a1 -Dflume.root.logger=INFO,console

3、spark 设置

如果只是开发测试,那么只要在工程项目里添加了 spark的相关jar文件,即可进行测试,如果是要用来spark 任务提交的方式进行,那么就需要搭建spark运行环境,关于spark的搭建,可以参看apache的官方文档,这里就不在细说,我这里是代码样例测试,是在开发环境里直接测试的,开发工具为:IDEA 15,为何要用这个工具呢,在以后的项目架构中详述;

error3

下面看看测试代码,实际非常简单:

package HuoSi;

/**
 * Created by yc on 10/20/16.
 */

import org.apache.spark.SparkConf;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.streaming.*;
import org.apache.spark.streaming.api.java.*;
import org.apache.spark.streaming.flume.FlumeUtils;
import org.apache.spark.streaming.flume.SparkFlumeEvent;
public class MySpark {

    public static void main(String[] args) throws Exception {

        String host ="localhost";
        int port = 3333;
        Duration batchInterval = new Duration(5000);
        SparkConf sparkConf = new SparkConf().setAppName("JavaFlumeEventCount").setMaster("local[2]");
       // JavaSparkContext sc = new JavaSparkContext(sparkConf);
       
JavaStreamingContext ssc = new JavaStreamingContext(sparkConf, batchInterval);
        JavaReceiverInputDStream<SparkFlumeEvent> flumeStream =
                FlumeUtils.createStream(ssc, host, port);
        flumeStream.count();
        flumeStream.count().map(new Function<Long, String>() {
            // @Override
           
public String call(Long in) {
                return "获思测试==收到问题日志信息: " + in + " from mysql error.";
            }
        }).print();
        ssc.start();
        ssc.awaitTermination();
    }
}

 

运行:

/Library/Java/JavaVirtualMachines/jdk1.8.0_45.jdk/Contents/Home/bin/java -Didea.launcher.port=7543 "-Didea.launcher.bin.path=/Applications/IntelliJ IDEA

partitions

………………中间省略N行

——————————————-

Time: 1476940510000 ms

——————————————-

获思测试==收到问题日志信息: 0 from mysql error.

出现上述信息,说明,可以正常运行;

首先运行spark 流的代码,然后启动 flume,然后刻意产生mysql的错误日志信息,spark流中就可以显示监控的信息;

error4

至此测试完成,通过测试可以实现用spark stream 对mysql 错误日志的监控,扩展一下将收到的错误信息进行分类等操作,给维护者发送比如短信告警等通知,和具体的错误信息,就可以实现基本的监控系统,最主要的是方便实际的定制;

Mysql Dictionary Cache

mysql的dictionary cache 又可以程为 table definition cahe 或者 per-table cache;有趣的是在当前的mysql中表的定义是放在 .frm 文件当中的,当需要打开表时会读取此文件,如果此文件损坏,那么表将无法读取:

mysql> select * from huo;
ERROR 1033 (HY000): Incorrect information in file: './test/huo.frm'
mysql> desc huo;
ERROR 1033 (HY000): Incorrect information in file: './test/huo.frm'
mysql> 

遇到这个错误时,可以有如下几种做法:

如果是在之前打开表,并且数据库没有重启,在最新的mysql,可以查询:

SELECT * FROM information_schema.COLUMNS where table_name='xxxxx'

这样就可以查询到了表的定义;

如果是备份那么也可以从备份中找到表的定义;如果没有备份,没有打开过表,彻底找不到表的定义了,那么可以会直接将将数据从数据文件中提取为可读状态,然后进行反推表定义; 所以做好备份是王道;从这里也可以看出在当前的版本中mysql并没有单独存储数据字典的表;这里推荐个小工具 dbsake

上面只是mysql 数据字典的一个方面,传统的数据字典,可以用下图来表示:

mysqldict

在开头描述问题只是,当前数据字典的一个问题,其还存在很多不足,所以在最新的mysql 规划中,开发团队已经开始重新规划新到数据字典的存储方式:

mysqldict2

详见:http://mysqlserverteam.com/a-preview-on-lab-release-with-new-data-dictionary-in-mysql/

数据字典的含义不仅仅是表定义,它包括的内容是比较多的,比如表的统计信息,但是在mysql表的统计信息是在打开表时进行的,如果表太大,打开速度就会缓慢,所以把一些相关信息放入表中持久存储也是一个好的选择:innodb_stats_persistent;

那么在当前的数据字典,还需要关注的是数据字典缓存也使用了 lru机制;在目前放出的8 的版本当中,数据字典文件已经默认消失,全部放到数据库中存储,那么问题来了,以后的mysql 在启动方面是不是也会越来越像Oracle呢?让我们拭目以待;