mysql

Dive into MySQL Replication

Dive into MySQL replication protocol

Preface

Let’s consider following scenario, we store huge data in MySQL and want to know data changes immediately(data inserted, deleted or updated), then do something for these changes like updating associated cache data.

Using MySQL trigger + UDF may be a feasible way, but I have not used it and would not recommend this. If we have many tables, maintaining so many triggers is horrible. At the same time, UDF may fail so that we will lost some changes, and even worse, a bad UDF implementation may block service or even crash MySQL.

We need a better solution, and this is binlog. MySQL records every changes into binlog, so if we can sync the binlog, we will know the data changes immediately, then do something.

Using rsync may be a good way, but I prefer another: acting as a slave and using MySQL replication protocol to sync.

MySQL Packet

First, we must know how to send or receive data with MySQL, and this is packet:

Split the data into packets of size 16MB.
Prepend to each chunk a packet header.

A packet header has 3 bytes for following payload data length and 1 byte for sequence ID.

The sequence ID is incremented with each packet between client and server communication, it starts at 0 and is reset to 0 when a new command begins, e.g, we send a packet, the sequence ID is 0, and the response packet sequence ID must be 1, if not, some error happens between the communication.

So a packet looks like below:

3              payload length
1              Sequence ID
string[len]    payload

As you see, sending data with packet is very easy, I have implemented a base library in go-mysql packet pkg.

Connection phase

When we first connect to MySQL server, we must do a handshake to let server authorizing us to communicate with it later, this is called connection phase.

Let’s consider a popular common connection phase.

  • Client connects server using socket connect API.
  • Server sends a initial handshake packet which contains server’s capabilities and a 20 bytes random salt.
  • Client answers with a handshake response packet, telling server its capabilities and a 20 bytes scrambled password.
  • Server response ok packet or err packet.

Although MySQL supports many authentication method, I only use username + password, you can see codes in go-mysql client and server pkg.

Now we know how to send/receive data, how to establish the connection, and it’s time to move on for syncing binlog. :-)

Register a Slave

When we want to sync binlog, we must register a slave at master first, that is acting as a pseudo slave.

We only need to send COM_REGISTER_SLAVE command, the payload is:

1              COM_REGISTER_SLAVE
4              server-id
1              slaves hostname length
string[$len]   slaves hostname
1              slaves user len
string[$len]   slaves user
1              slaves password len
string[$len]   slaves password
2              slaves mysql-port
4              replication rank
4              master-id

We must use an unique server id for our pseudo slave, I prefer using a ID bigger than 1000 which is different from our real MySQL servers.

Dump BinLog from master

After register, we should send COM_BINLOG_DUMP command, it is very easy too, payload is:

1              COM_BINLOG_DUMP
4              binlog-pos
2              flags
4              server-id
string[EOF]    binlog-filename

If the binlog filename is empty, server will use the first known binlog.

You can set flags to 1 to tell server to reply a EOF packet instead of blocking connection if there is no more binlog event. But I prefer blocking so that if there is any new event, server can send to us immediately.

Although MySQL supports COM_BINLOG_DUMP_GTID commands, I still prefer using command binlog filename + position, because it is very easy and for our pseudo slave, we only need to sync binlog, save it in a place and let other applications like MHA use it.

After we send COM_BINLOG_DUMP command, server will response a binlog network stream which contains many binlog events.

BinLog Event

A MySQL binlog event has many versions, but we only care version 4(MySQL 5.0+) and don’t support earlier versions.

A binlog event contains three parts, header, post header and payload, but I like parsing post header and payload at same time.

A event header looks below:

4              timestamp
1              event type
4              server-id
4              event-size
4              log pos
2              flags

You can see all binlog event types here. The log pos is position of the next event, we can save this position for resuming syncing later.

The first binlog event in the binlog file is FORMAT_DESCRIPTION_EVENT, this event is very important, we use it to determine table ID size for row based event, and check the last 5 bytes to see whether CRC32 is enabled or not for the following events, etc…

Another event we should care is ROTATE_EVENT, it tells us a new binlog coming.

Parsing a binlog event is very easy, we only need to refer the document and parse it one by one, except row based replication event.

Row Based Replication

Parsing row based replication event is very hard if we want to know the real data changes for a column in one row.

First, we must parse TABLE_MAP_EVENT, payload is:

post-header:
    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
    }
  2              flags

payload:
  1              schema name length
  string         schema name
  1              [00]
  1              table name length
  string         table name
  1              [00]
  lenenc-int     column-count
  string.var_len [length=$column-count] column-def
  lenenc-str     column-meta-def
  n              NULL-bitmask, length: (column-count + 8) / 7

The post_header_len is saved in FORMAT_DESCRIPTION_EVENT, column count and def tells us how many columns in one row and their data types, like tiny int, short int, float, set, etc. column meta def is tricky, but we must use it to parse following ROWS_EVENT.

A ROWS_EVENT contains WRITE_ROWS_EVENT(insert), DELETE_ROWS_EVENT(delete) and UPDATE_ROWS_EVENT(update), every event contains v0, v1 and v2 three versions, most of time, we only need to care v1 and v2.

A ROWS_EVENT looks below:

header:
  if post_header_len == 6 {
4                    table id
  } else {
6                    table id
  }
2                    flags
  if version == 2 {
2                    extra-data-length
string.var_len       extra-data
  }

body:
lenenc_int           number of columns
string.var_len       columns-present-bitmap1, length: (num of columns+7)/8
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       columns-present-bitmap2, length: (num of columns+7)/8
  }

rows:
string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap1'+7)/8
string.var_len       value of each field as defined in table-map
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap2'+7)/8
string.var_len       value of each field as defined in table-map
  }
  ... repeat rows until event-end

I promise that if you don’t dive into the MySQL source, you can not understand how to parse it at all.

First, let’s see columns-present-bitmap, for a column, it will not be saved in the row data if the associated bit in columns-present-bitmap is 0, and we will skip this column when parsing.

For every row data, first we should calculate the null-bitmap, a big pitfall here, we calculate columns-present-bitmap using (num of columns+7)/8, but we must use (bits set in ‘columns-present-bitmap’+7)/8 for null-bitmap. (You should google bits set if you don’t understand it).

From MySQL 5.6, it supports another two binlog row images: minimal and noblob. For minimal row image update, if we have 16 columns and only the first column data changed, if we use (num of columns+7)/8, we should use 2 bytes to store null bitmap, but if we use (bits set in ‘columns-present-bitmap’+7)/8, we will only use 1 bytes to store null bitmap, saving 1 byte(is it really necessary?). By the way, I sent a pull request for python-mysql-replication to handle minimal and noblob row image paring.

Now we get column-present-bitmap and null-bitmap, for a column, if it’s not set in column-present-bitmap or set in null-bitmap, we will know that this column is null for the current row data.

Then we will parse the rest of none null columns. For some special columns, like MYSQL_TYPE_LONG or MYSQL_TYPE_FLOAT, we can know the data length directly, e.g, MYSQL_TYPE_LONG is 4 bytes and MYSQL_TYPE_TINY_INT is 1 byte.

But for other columns, we should use column meta in TABLE_MAP_EVENT to help us determine the data length. For example, a MYSQL_TYPE_BLOB column, if meta is 1, the data is tiny blob and the first 1 byte in data is the length for payload, if meta is 2, the data is short blob and the first 2 bytes is the lenght for payload.

Paring the real data is very hard and I can not illustrate here fully and clearly. I hope you can see the source in MySQL or go-mysql replication pkg if you have some interest.

Semi Sync Replication

At first, I didn’t support semi sync replication in go-mysql, but after I develop go-mysql-elasticsearch, I realize that if I want to sync MySQL changes into elasticsearch more quickly and immediately, supporting semi sync replication is a good choice and it’s easy to do like below:

  • Check whether master supports semi sync or not, using “SHOW VARIABLES LIKE ‘rpl_semi_sync_master_enabled’”.
  • Tell master we support semi sync using “SET @rpl_semi_sync_slave = 1”.

If all is ok, server will prepend two byte before every binlog event. The first byte is 0xef indicating semi sync and the second byte is semi sync ACK flag, if 1, we must reply a semi sync ACK packet.

It now seems that this is a wise decision. In facebook, they even develop a semi sync binlog, you can see more here. I develop a similar go-mysqlbinlog supporting semi sync too, but it still needs improvement for production environment.

Summary

Learning mysql protocol is a hard but happy journey for me, and I have done some interesting things too, like mixer, a MySQL proxy which the modified version(cm) has been used in production in wandoujia company. go-mysql-elasticsearch, a tool to sync MySQL data into elasticsearch immediately.

Now I have been developing go-mysql, a powerful MySQL toolset. I would be very glad if some people find it can help them for their own MySQL development too.

Later I will also try to use go-mysqlbinlog + MHA to build our own MySQL HA solution, I don’t know perl, but luckily, I can understand MHA code.

Below is my contact, any advice and feedback is very welcome.

  • Email: siddontang@gmail.com

  • Skype: live:siddontang_1

深入解析MySQL replication协议

Why

最开始的时候,go-mysql只是简单的抽象mixer的代码,提供一个基本的mysql driver以及proxy framework,但做到后面,笔者突然觉得,既然研究了这么久mysql client/server protocol,干脆顺带把replication protocol也给弄明白算了。现在想想,幸好当初决定实现了replication的支持,不然后续go-mysql-elasticsearch这个自动同步MySQL到Elasticsearch的工具就不可能在短时间完成。

其实MySQL replication protocol很简单,client向server发送一个MySQL binlog dump的命令,server就会源源不断的给client发送一个接一个的binlog event了。

Register

首先,我们需要伪造一个slave,向master注册,这样master才会发送binlog event。注册很简单,就是向master发送COM_REGISTER_SLAVE命令,带上slave相关信息。这里需要注意,因为在MySQL的replication topology中,都需要使用一个唯一的server id来区别标示不同的server实例,所以这里我们伪造的slave也需要一个唯一的server id。

Binlog dump

最开始的时候,MySQL只支持一种binlog dump方式,也就是指定binlog filename + position,向master发送COM_BINLOG_DUMP命令。在发送dump命令的时候,我们可以指定flag为BINLOG_DUMP_NON_BLOCK,这样master在没有可发送的binlog event之后,就会返回一个EOF package。不过通常对于slave来说,一直把连接挂着可能更好,这样能更及时收到新产生的binlog event。

在MySQL 5.6之后,支持了另一种dump方式,也就是GTID dump,通过发送COM_BINLOG_DUMP_GTID命令实现,需要带上的是相应的GTID信息,不过笔者觉得,如果只是单纯的实现一个能同步binlog的工具,使用最原始的binlog filename + position就够了,毕竟我们不是MySQL,解析GTID还是稍显麻烦的。这里,顺带吐槽一下MySQL internal文档,里面关于GTID encode的格式说明竟然是错误的,文档格式如下:

4                n_sids
  for n_sids {
string[16]       SID
8                n_intervals
    for n_intervals {
8                start (signed)
8                end (signed)
    }

但实际坑爹的是n_sids的长度是8个字节。这个错误可以算是血的教训,笔者当时debug了很久都没发现为啥GTID dump一直出错,直到笔者查看了MySQL的源码。

MariaDB虽然也引入了GTID,但是并没有提供一个类似MySQL的GTID dump命令,仍是使用的COM_BINLOG_DUMP命令,不过稍微需要额外设置一些session variable,譬如要设置slave_connect_state为当前已经完成的GTID,这样master就能知道下一个event从哪里发送了。

Binlog Event

对于一个binlog event来说,它分为三个部分,header,post-header以及payload。但实际笔者在处理event的时候,把post-header和payload当成了一个整体body。

MySQL的binlog event有很多版本,但这里笔者只关心version 4的,也就是从MySQL 5.1.x之后支持的版本。而且笔者也只支持这个版本的event解析,首先是不想写过多的兼容代码,另一个更主要的原因就在于现在几乎都没有人使用低版本的MySQL了。

Binlog event的header格式如下:

4              timestamp
1              event type
4              server-id
4              event-size
4              log pos
2              flags

header的长度固定为19,event type用来标识这个event的类型,event size则是该event包括header的整体长度,而log pos则是下一个event所在的位置。

在v4版本的binlog文件中,第一个event就是FORMAT_DESCRIPTION_EVENT,格式为:

2                binlog-version
string[50]       mysql-server version
4                create timestamp
1                event header length
string[p]        event type header lengths

我们需要关注的就是event type header length这个字段,它保存了不同event的post-header长度,通常我们都不需要关注这个值,但是在解析后面非常重要的ROWS_EVENT的时候,就需要它来判断TableID的长度了。这个后续在说明。

而binlog文件的结尾,通常(只要master不当机)就是ROTATE_EVENT或者STOP_EVENT。这里我们重点关注ROTATE_EVENT,格式如下:

Post-header
8              position
Payload
string[p]      name of the next binlog

它里面其实就是标明下一个event所在的binlog filename和position。这里需要注意,当slave发送binlog dump之后,master首先会发送一个ROTATE_EVENT,用来告知slave下一个event所在位置,然后才跟着FORMAT_DESCRIPTION_EVENT。

其实我们可以看到,binlog event的格式很简单,文档都有着详细的说明。通常来说,我们仅仅需要关注几种特定类型的event,所以只需要写出这几种event的解析代码就可以了,剩下的完全可以跳过。

Row Based Replication

如果真要说处理binlog event有啥复杂的,那铁定属于row based replication相关的ROWS_EVENT了,对于一个ROWS_EVENT来说,它记录了每一行数据的变化情况,而对于外部来说,是需要准确的知道这一行数据到底如何变化的,所以我们需要获取到该行每一列的值。而如何解析相关的数据,是非常复杂的。笔者也是看了很久MySQL,MariaDB源码,以及mysql-python-replication的实现,才最终搞定了这个个人觉得最困难的部分。

在详细说明ROWS_EVENT之前,我们先来看看TABLE_MAP_EVENT,该event记录的是某个table一些相关信息,格式如下:

post-header:
    if post_header_len == 6 {
  4              table id
    } else {
  6              table id
    }
  2              flags

payload:
  1              schema name length
  string         schema name
  1              [00]
  1              table name length
  string         table name
  1              [00]
  lenenc-int     column-count
  string.var_len [length=$column-count] column-def
  lenenc-str     column-meta-def
  n              NULL-bitmask, length: (column-count + 8) / 7

table id需要根据post_header_len来判断字节长度,而post_header_len就是存放到FORMAT_DESCRIPTION_EVENT里面的。这里需要注意,虽然我们可以用table id来代表一个特定的table,但是因为alter table或者rotate binlog event等原因,master会改变某个table的table id,所以我们在外部不能使用这个table id来索引某个table。

TABLE_MAP_EVENT最需要关注的就是里面的column meta信息,后续我们解析ROWS_EVENT的时候会根据这个来处理不同数据类型的数据。column def则定义了每个列的类型。

ROWS_EVENT包含了insert,update以及delete三种event,并且有v0,v1以及v2三个版本。

ROWS_EVENT的格式很复杂,如下:

header:
  if post_header_len == 6 {
4                    table id
  } else {
6                    table id
  }
2                    flags
  if version == 2 {
2                    extra-data-length
string.var_len       extra-data
  }

body:
lenenc_int           number of columns
string.var_len       columns-present-bitmap1, length: (num of columns+7)/8
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       columns-present-bitmap2, length: (num of columns+7)/8
  }

rows:
string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap1'+7)/8
string.var_len       value of each field as defined in table-map
  if UPDATE_ROWS_EVENTv1 or v2 {
string.var_len       nul-bitmap, length (bits set in 'columns-present-bitmap2'+7)/8
string.var_len       value of each field as defined in table-map
  }
  ... repeat rows until event-end

ROWS_EVENT的table id跟TABLE_MAP_EVENT一样,虽然table id可能变化,但是ROWS_EVENT和TABLE_MAP_EVENT的table id是能保证一致的,所以我们也是通过这个来找到对应的TABLE_MAP_EVENT。

为了节省空间,ROWS_EVENT里面对于各列状态都是采用bitmap的方式来处理的。

首先我们需要得到columns present bitmap的数据,这个值用来表示当前列的一些状态,如果没有设置,也就是某列对应的bit为0,表明该ROWS_EVENT里面没有该列的数据,外部直接使用null代替就成了。

然后就是null bitmap,这个用来表明一行实际的数据里面有哪些列是null的,这里最坑爹的是null bitmap的计算方式并不是(num of columns+7)/8,也就是MySQL计算bitmap最通用的方式,而是通过columns present bitmap的bits set个数来计算的,这个坑真的很大,为啥要这么设计,最主要的原因就在于MySQL 5.6之后binlog row image的格式增加了minimal和noblob,尤其是minimal,update的时候只会记录相应更改字段的数据,譬如我一行有16列,那么用2个byte就能搞定null bitmap了,但是如果这时候只有第一列更新了数据,其实我们只需要使用1个byte就能记录了,因为后面的铁定全为0,就不需要额外空间存放了,不过话说真有必要这么省空间吗?

null bitmap的计算需要通过columns present bitmap的bits set计算,bits set其实也很好理解,就是一个byte按照二进制展示的时候1的个数,譬如1的bits set就是1,而3的bits set就是2,而255的bits set就是8了。

好了,得到了present bitmap以及null bitmap之后,我们就能实际解析这行对应的列数据了,对于每一列,首先判断是否present bitmap标记了,如果为0,则跳过用null表示,然后在看是否在null bitmap里面标记了,如果为1,表明值为null,最后我们就开始解析真有有数据的列了。

但是,因为我们得到的是一行数据的二进制流,我们怎么知道一列数据如何解析?这里,就要靠TABLE_MAP_EVENT里面的column def以及meta了。

column def定义了该列的数据类型,对于一些特定的类型,譬如MYSQL_TYPE_LONG, MYSQL_TYPE_TINY等,长度都是固定的,所以我们可以直接读取对应的长度数据得到实际的值。但是对于一些类型,则没有这么简单了。这时候就需要通过meta来辅助计算了。

譬如对于MYSQL_TYPE_BLOB类型,meta为1表明是tiny blob,第一个字节就是blob的长度,2表明的是short blob,前两个字节为blob的长度等,而对于MYSQL_TYPE_VARCHAR类型,meta则存储的是string长度。这里,笔者并没有列出MYSQL_TYPE_NEWDECIMAL,MYSQL_TYPE_TIME2等,因为它们的实现实在是过于复杂,笔者几乎对照着MySQL的源码实现的。

搞定了这些,我们终于可以完整的解析一个ROWS_EVENT了,顺带说一下,python-mysql-replication里面minimal/noblob row image的支持,也是笔者提交的pull request,貌似是笔者第一次给其他开源项目做贡献。

总结

实现MySQL replication protocol的解析真心是一件很有挑战的事情,虽然辛苦,但是让笔者更加深入的学习了MySQL的源码,为后续笔者改进LedisDB的replication以及更深入的了解MySQL的replication打下了坚实的基础。

话说,现在成果已经显现,不然go-mysql-elasticsearch不可能如此快速实现,后续笔者准备基于此做一个更新cache的服务,这样我们的代码里面就不会到处出现更新cache的代码了。

MySQL问题两则

这段时间处理了两个比较有意思的MySQL问题,一个死锁的,一个优化的,陡然发现其实自己对MySQL的理解还不深入,很多运行机制也是知其然但不知其所以然,后续还需要好好恶补一下底层知识。

一次不可思议的死锁

假设有如下表结构:

mysql> show create table tt \G;
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) NOT NULL DEFAULT '0',
  `fileid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `fileid` (`fileid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

启动三个shell,连接MySQL,然后begin开启一个事务,各个shell分别执行对应的更新语句,

shell 1:

shell 1> update tt set id = 2 where fileid = 1;

shell 2:

shell 2> update tt set id = 3 where fileid = 1;

shell 3:

shell 3> update tt set id = 4 where fileid = 1;

假设shell 1先执行,这时候2和3会block,然后shell 1 commit提交,我们发现shell 2执行成功,但是3出现死锁错误,通过show engine innodb status我们得到如下死锁信息:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-01-23 14:24:16 10ceed000
*** (1) TRANSACTION:
TRANSACTION 24897, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 8, OS thread handle 0x10cea5000, query id 138 127.0.0.1 root updating
update tt set id = 4 where fileid = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24897 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 24896, ACTIVE 8 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 7, OS thread handle 0x10ceed000, query id 136 127.0.0.1 root updating
update tt set id = 3 where fileid = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24896 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 4 n bits 72 index `fileid` of table `test`.`tt` trx id 24896 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------

刚开始碰到这个死锁问题,真心觉得很奇怪,每个事务一条语句,通过一个唯一索引去更新同一条记录,正常来说完全不可能发生死锁,但确确实实发生了。笔者百思不得其解,幸好有google,然后搜到了这篇,一个最不可思议的MySQL死锁分析,虽然触发情况不一样,但是死锁原理都应该类似的,后续如果有精力,笔者将好好深入研究一下。

顺带再说一下,MySQL 加锁处理分析这篇文章也是干活满满,这两篇加起来深入理解了,对MySQL的deadlock就会有一个很全面的认识了。

一次坑爹的优化

我们需要在一张表里面删除某种类型的数据,大概的表结构类似这样:

CREATE TABLE t (
    id INT,
    tp ENUM ("t1", "t2"),
    PRIMARY KEY(id)
) ENGINE=INNODB;

假设我们需要删除类型为t2的数据,语句可能是这样delete from t where tp = "t2",这样没啥问题,但我们这张表有5亿数据,好吧,真的是5亿,所以以后别再跟我说MySQL表存储百万级别数据就要分表了,百万太小case了。

这事情我交给了一个小盆友去帮我搞定,他最开始写出了如下的语句delete from t where tp = "t2" limit 1000,使用limit来限制一次删除的个数,可以了,不过这有个很严重的问题,就是越往后,随着t2类型的减少,我们几乎都是全表遍历来删除,所以总的应该是O(n*n)的开销。

于是我让他考虑主键,每次操作的时候,记录当前最大的主键,这样下次就可以从这个主键之后开始删除了,首先 select id from t where id > last_max_select_id and tp = "t2" limit 1000,然后delete from t where id in (ids),虽然这次优化采用了两条语句,但是通过主键,我们只需要遍历一次表就可以了,总的来说,性能要快的。

但是,实际测试的时候,我们却发现,select这条语句耗时将近30s,太慢了。虽然我们使用了主键,但是MySQL仍然需要不停的读取数据判断条件,加之t2类型的数据在表里面比较少量,所以为了limit 1000这个条件,MySQL需要持续的进行IO读取操作,结果自然是太慢了。

想清楚了这个,其实就好优化了,我们只需要让条件判断在应用层做,MySQL只查询数据返回,语句就是 select id, tp from t where id > last_max_select_id limit 1000,得到结果集之后,自行判断需要删除的id,然后delete。看似我们需要额外处理逻辑,并且网络开销也增大了,但MySQL只是简单的IO读取,非常快,总的来说,性能提升很显著。当然笔者后续还需要更深入的分析。

最后执行,很happy的是,非常快速的就删完了相关数据,而select的查询时间消耗几乎忽略不计。

MySQL高可用浅析

对于多数应用来说,MySQL都是作为最关键的数据存储中心的,所以,如何让MySQL提供HA服务,是我们不得不面对的一个问题。当master当机的时候,我们如何保证数据尽可能的不丢失,如何保证快速的获知master当机并进行相应的故障转移处理,都是需要我们好好思考的。这里,笔者将结合这段时间做的MySQL proxy以及toolsets相关工作,说说我们现阶段以及后续会在项目中采用的MySQL HA方案。

Replication

要保证MySQL数据不丢失,replication是一个很好的解决方案,而MySQL也提供了一套强大的replication机制。只是我们需要知道,为了性能考量,replication是采用的asynchronous模式,也就是写入的数据并不会同步更新到slave上面,如果这时候master当机,我们仍然可能会面临数据丢失的风险。

为了解决这个问题,我们可以使用semi-synchronous replication,semi-synchronous replication的原理很简单,当master处理完一个事务,它会等待至少一个支持semi-synchronous的slave确认收到了该事件并将其写入relay-log之后,才会返回。这样即使master当机,最少也有一个slave获取到了完整的数据。

但是,semi-synchronous并不是100%的保证数据不会丢失,如果master在完成事务并将其发送给slave的时候崩溃,仍然可能造成数据丢失。只是相比于传统的异步复制,semi-synchronous replication能极大地提升数据安全。更为重要的是,它并不慢,MHA的作者都说他们在facebook的生产环境中使用了semi-synchronous(这里),所以我觉得真心没必要担心它的性能问题,除非你的业务量级已经完全超越了facebook或者google。

如果真的想完全保证数据不会丢失,现阶段一个比较好的办法就是使用gelera,一个MySQL集群解决方案,它通过同时写三份的策略来保证数据不会丢失。笔者没有任何使用gelera的经验,只是知道业界已经有公司将其用于生产环境中,性能应该也不是问题。但gelera对MySQL代码侵入性较强,可能对某些有代码洁癖的同学来说不合适了:-)

我们还可以使用drbd来实现MySQL数据复制,MySQL官方文档有一篇文档有详细介绍,但笔者并未采用这套方案,MHA的作者写了一些采用drdb的问题,在这里,仅供参考。

在后续的项目中,笔者会优先使用semi-synchronous replication的解决方案,如果数据真的非常重要,则会考虑使用gelera。

Monitor

前面我们说了使用replication机制来保证master当机之后尽可能的数据不丢失,但是我们不能等到master当了几分钟才知道出现问题了。所以一套好的监控工具是必不可少的。

当master当掉之后,monitor能快速的检测到并做后续处理,譬如邮件通知管理员,或者通知守护程序快速进行failover。

通常,对于一个服务的监控,我们采用keepalived或者heartbeat的方式,这样当master当机之后,我们能很方便的切换到备机上面。但他们仍然不能很即时的检测到服务不可用。笔者的公司现阶段使用的是keepalived的方式,但后续笔者更倾向于使用zookeeper来解决整个MySQL集群的monitor以及failover。

对于任何一个MySQL实例,我们都有一个对应的agent程序,agent跟该MySQL实例放到同一台机器上面,并且定时的对MySQL实例发送ping命令检测其可用性,同时该agent通过ephemeral的方式挂载到zookeeper上面。这样,我们可以就能知道MySQL是否当机,主要有以下几种情况:

  1. 机器当机,这样MySQL以及agent都会当掉,agent与zookeeper连接自然断开
  2. MySQL当掉,agent发现ping不通,主动断开与zookeeper的连接
  3. Agent当掉,但MySQL未当

上面三种情况,我们都可以认为MySQL机器出现了问题,并且zookeeper能够立即感知。agent与zookeeper断开了连接,zookeeper触发相应的children changed事件,监控到该事件的管控服务就可以做相应的处理。譬如如果是上面前两种情况,管控服务就能自动进行failover,但如果是第三种,则可能不做处理,等待机器上面crontab或者supersivord等相关服务自动重启agent。

使用zookeeper的好处在于它能很方便的对整个集群进行监控,并能即时的获取整个集群的变化信息并触发相应的事件通知感兴趣的服务,同时协调多个服务进行相关处理。而这些是keepalived或者heartbeat做不到或者做起来太麻烦的。

使用zookeeper的问题在于部署起来较为复杂,同时如果进行了failover,如何让应用程序获取到最新的数据库地址也是一个比较麻烦的问题。

对于部署问题,我们要保证一个MySQL搭配一个agent,幸好这年头有了docker,所以真心很简单。而对于第二个数据库地址更改的问题,其实并不是使用了zookeeper才会有的,我们可以通知应用动态更新配置信息,或者使用proxy来解决。

虽然zookeeper的好处很多,但如果你的业务不复杂,譬如只有一个master,一个slave,zookeeper可能并不是最好的选择,没准keepalived就够了。

Failover

通过monitor,我们可以很方便的进行MySQL监控,同时在MySQL当机之后通知相应的服务做failover处理,假设现在有这样的一个MySQL集群,a为master,b,c为其slave,当a当掉之后,我们需要做failover,那么我们选择b,c中的哪一个作为新的master呢?

原则很简单,哪一个slave拥有最近最多的原master数据,就选哪一个作为新的master。我们可以通过show slave status这个命令来获知哪一个slave拥有最新的数据。我们只需要比较两个关键字段Master_Log_File以及Read_Master_Log_Pos,这两个值代表了slave读取到master哪一个binlog文件的哪一个位置,binlog的索引值越大,同时pos越大,则那一个slave就是能被提升为master。这里我们不讨论多个slave可能会被提升为master的情况。

在前面的例子中,假设b被提升为master了,我们需要将c重新指向新的master b来开始复制。我们通过CHANGE MASTER TO来重新设置c的master,但是我们怎么知道要从b的binlog的哪一个文件,哪一个position开始复制呢?

GTID

为了解决这一个问题,MySQL 5.6之后引入了GTID的概念,即uuid:gid,uuid为MySQL server的uuid,是全局唯一的,而gid则是一个递增的事务id,通过这两个东西,我们就能唯一标示一个记录到binlog中的事务。使用GTID,我们就能非常方便的进行failover的处理。

仍然是前面的例子,假设b此时读取到的a最后一个GTID为3E11FA47-71CA-11E1-9E33-C80AA9429562:23,而c的为3E11FA47-71CA-11E1-9E33-C80AA9429562:15,当c指向新的master b的时候,我们通过GTID就可以知道,只要在b中的binlog中找到GTID为3E11FA47-71CA-11E1-9E33-C80AA9429562:15这个event,那么c就可以从它的下一个event的位置开始复制了。虽然查找binlog的方式仍然是顺序查找,稍显低效暴力,但比起我们自己去猜测哪一个filename和position,要方便太多了。

google很早也有了一个Global Transaction ID的补丁,不过只是使用的一个递增的整形,LedisDB就借鉴了它的思路来实现failover,只不过google貌似现在也开始逐步迁移到MariaDB上面去了。

MariaDB的GTID实现跟MySQL 5.6是不一样的,这点其实比较麻烦,对于我的MySQL工具集go-mysql来说,意味着要写两套不同的代码来处理GTID的情况了。后续是否支持MariaDB再看情况吧。

Pseudo GTID

GTID虽然是一个好东西,但是仅限于MySQL 5.6+,当前仍然有大部分的业务使用的是5.6之前的版本,笔者的公司就是5.5的,而这些数据库至少长时间也不会升级到5.6的。所以我们仍然需要一套好的机制来选择master binlog的filename以及position。

最初,笔者打算研究MHA的实现,它采用的是首先复制relay log来补足缺失的event的方式,但笔者不怎么信任relay log,同时加之MHA采用的是perl,一个让我完全看不懂的语言,所以放弃了继续研究。

幸运的是,笔者遇到了orchestrator这个项目,这真的是一个非常神奇的项目,它采用了一种Pseudo GTID的方式,核心代码就是这个

create database if not exists meta;

drop event if exists meta.create_pseudo_gtid_view_event;

delimiter ;;
create event if not exists
  meta.create_pseudo_gtid_view_event
  on schedule every 10 second starts current_timestamp
  on completion preserve
  enable
  do
    begin
      set @pseudo_gtid := uuid();
      set @_create_statement := concat('create or replace view meta.pseudo_gtid_view as select \'', @pseudo_gtid, '\' as pseudo_gtid_unique_val from dual');
      PREPARE st FROM @_create_statement;
      EXECUTE st;
      DEALLOCATE PREPARE st;
    end
;;

delimiter ;

set global event_scheduler := 1;

它在MySQL上面创建了一个事件,每隔1s,就将一个uuid写入到一个view里面,而这个是会记录到binlog中的,虽然我们仍然不能像GTID那样直接定位到一个event,但也能定位到一个1s的区间了,这样我们就能在很小的一个区间里面对比两个MySQL的binlog了。

继续上面的例子,假设c最后一次出现uuid的位置为s1,我们在b里面找到该uuid,位置为s2,然后依次对比后续的event,如果不一致,则可能出现了问题,停止复制。当遍历到c最后一个binlog event之后,我们就能得到此时b下一个event对应的filename以及position了,然后让c指向这个位置开始复制。

使用Pseudo GTID需要slave打开log-slave-update的选项,考虑到GTID也必须打开该选项,所以个人感觉完全可以接受。

后续,笔者自己实现的failover工具,将会采用这种Pseudo GTID的方式实现。

在《MySQL High Availability》这本书中,作者使用了另一种GTID的做法,每次commit的时候,需要在一个表里面记录gtid,然后就通过这个gtid来找到对应的位置信息,只是这种方式需要业务MySQL客户端的支持,笔者不很喜欢,就不采用了。

后记

MySQL HA一直是一个水比较深的领域,笔者仅仅列出了一些最近研究的东西,有些相关工具会尽量在go-mysql中实现。

MySQL索引研究

介绍

这段时间在重构server,尤其是重新设计表结构以满足功能需求以及后续的性能需求。因为我们使用的是mysql,所以为了支持更多的并发访问,对大数据量的表设计一些index是必不可少的,而现在看我当时设计的index,又发现了很多不足的地方。同时又因为深入重新在学习了解了一些index知识,觉得有必要记录一下,以免自己再犯同样的错误。

假设有如下的表结构,后续所有的例子都通过该表来说明:

table tbl,
fileid int,
groupid int,
opver int,
name varchar(1024),
entid int,
primary key (fileid),
engine = innodb

聚集索引

因为使用的mysql engine是innodb,所以对于主键使用的是聚簇索引,对于聚簇索引我们知道它是吧数据存放到index里面的,也就是通过主键查询就能直接定位到数据,非常方便。

以前我受到的教育就是,select *是邪恶的,因为会取出所有列的数据。诚然,对于数据的获取,我们是按需索取,这样就能极大的减少网络传输的消耗。正因为以前有这样的认识,我自然认为innodb在server端io数据读取的时候也是按需索取。但其实不是这样。

在innodb里面,数据是按照页来存放的,通常一页为16k,每一页至少存放2条数据,也就是说,每一条数据在最大为8k。这里有童鞋就可能疑惑了,那如果我表结构里面有blob,text这种类型的肿么办?对于这种大数据类型的,innodb会将其存放到一个overflow page里面去,然后数据页只存放对应的指针。同样,innodb也是按照页来读取数据,也就是说一次读取16k。

对于上面我们的表结构,是完全能够存放到同一个page里面的,也就是说,我们即使select的时候没有选择对应的列,在innodb层面也仍然会将该数据读出,也就说无论怎样,io消耗是一样的。具体可以参考这篇

这里在谈谈主键id类型选择的问题,因为innodb采用的是b+tree来进行数据的存储,网上对于是否采用自增id或者guid这种争论满天飞,现在我的想法如下:

  • guid会导致主键过于分散,以至于在数据进行插入,删除,更新的时候会进行b+tree的频繁分裂,同时读取数据的时候因为过于分散会导致io随即读取,性能不高。
  • 自增id虽然能让io进行顺序读取,但是如果服务器数据量太大造成分表了,对于每张表的auto increment的初始值维护也是一件很烦的事情。
  • 鉴于上面那种情况,我们现在考虑的做法就是提供一个id生成器,用来保证主键的顺序递增,同时又保证全局的完全唯一。这个id生成器很容易,使用redis的incr就可以搞定,而且还可以通过incrby进行批量申请,性能妥妥的。

联合索引

一般为了查询方便,我们有可能在多列上面建立联合索引,譬如在(groupid, opver)上面,那么我就可以很方便的使用如下索引:

select * from tbl where groupid in (1,2,3)
select * from tbl where groupid = 1 and opver > 10 and opver < 100
select * from tbl where groupid = 1 and opver > 10 order by opver desc limit 10

在上面那些查询语句立马,index都能很好的工作,然后我就自认为下面这种的也行:

select * from tbl where groupid in (1,2,3) and opver > 10 order by opver desc limit 10

对于这个查询,我自然想到的就是首先mysql会通过索引选出groupid为1,2,3的,然后再在这个结果集里面选出opver满足的进行排序。可真的是这样吗。使用explain之后,才发现,在extra那一栏生生的出现了using filesort。也就是说mysql并没有对opver使用索引进行order by,而是使用了filesort,这里我们的索引在groupid之后就无效了。

其实这种情况可以用通用的情况来归纳,在一个组合索引里面,如果出现了多个范围查询,那么mysql不可能为每一个范围都使用索引,一般碰到第一个范围之后就会停止索引工作了。

这里给了我一个深刻的教训,就是设计好表结构以及index之后,不要自认为就能按照我想的方式工作了,最好需要explain一下,看看mysql到底是怎么工作的,不然出了性能瓶颈都不知道。

覆盖索引

当我们需要查询的数据通过索引就能查到的时候,这种索引就叫做覆盖索引。譬如下面这些:

select groupid, opver from tbl where groupid = 1 and opver > 10;

select fileid, groupid, opver from tbl where groupid = 1 and opver > 10 order by opver limit 10

因为我们在(groupid, opver)上面建立了索引,那么当我们查询的所有列包含在该索引里面的时候,我们就可以通过覆盖索引直接找到。通过explain可以看到extra里面有using index,表明使用了覆盖索引。

对于第二个例子,为什么fileid也可以使用覆盖索引呢,因为对于innodb来说,非聚簇索引保存的是主键id,也就是fileid,所以通过索引也能够直接找到fileid。

使用覆盖索引的好处是很明显的,数据只需要通过索引就能获取,而不需要通过主键在进行随机的io读取。一个很简单的例子。

select entid from t1 order by opver limit 100000, 10;

select entid from t1 inner join (select fileid from t1 order by opver limit 100000, 10) as ac using(fileid)

因为我们需要查询entid,而没有任何一个索引能覆盖entid,这里我们给opver单独建立一个索引。对于第一个查询,mysql因为需要获取entid,所以会通过fileid查到实际的数据并取出,在进行排序,同时因为limit的跨度很大,mysql会丢弃很多数据,所以导致该查询会很慢。

而对于第二个查询,在join立马,我们只是通过覆盖索引找到了fileid,而不需要通过fileid去随机io读取实际数据,取出所有的fileid之后,在取出了entid。虽然该查询有一个join操作,但是因为join的性能很高,所以比第一个查询快很多。

关于索引覆盖,网上有很多关于这个的介绍,譬如这个,总之用好了覆盖索引,是能极大提升效率的,但是也不可能为了覆盖索引去建立覆盖索引,如果索引泛滥了,也是一件很头疼的事情。

filesort

对于先前联合索引遇到的问题,我发现如果不重新设计整个的查询机制,按照现有做法是完全不能避免filesort的。因为我们的业务逻辑就是需要查询一批groupid里面,opver大于某个值的所有数据。既然避免不了,那我们就可以看看到底性能高不高。

mysql对于filesort,有两种处理方式,一种是单路排序,一种是双路排序。单路排序直接就是取出选择的字段,然后在sort buffer中排序。而双路排序则是取出排序字段以及主键,排序完成之后在通过主键获取实际的数据。可以看出,双路排序会有两次io操作,自然性能会差一点。

早期mysql采用的是双路排序,现在采用的是单路,但是如果我们需要排序的数据超过了配置的sort buffer空间,或者需要排序的单行数据长度大于max_length_for_sort_data,mysql仍然会使用单路排序。mysql默认max_length_for_sort_data为1024,这个需要根据实际数据长度进行配置。

不过如果能把mysql的硬盘换成ssd,或者把内存加到100g以上,我觉得这个filesort也还真不成问题了。

后续

这几天对于mysql索引的研究就先记录一下,感觉对mysql的理解还需要加深,后续如果还有什么新的感想也会在这里记录。同时bs一下自己,号称用了mysql这么多年,到现在了还有很多东西没有领会,是时候开始好好深入研究了。