TiDB 迁移实战:Discourse 从 PostgreSQL 到 MySQL 到 TiDB 丨 AskTUG 论坛背后的故事

TiDB_Robot for PingCAP · 2021年06月25日 · 最后由 songjiayang 回复于 2021年06月28日 · 74 次阅读

AskTUG.com 技术问答网站相信大家都不陌生,但除了日常熟知的前端页面外,背后支撑其运行的数据库还有一个不为人知的故事。本文由 AskTUG.com 的作者之一王兴宗老师分享,揭秘诞生于 Discourse 的 AskTUG.com ,从 PostgreSQL 迁移到 MySQL 最后稳定运行在 TiDB 的奇妙故事。

一个广告:

AskTUG.com 是 TiDB User、Contributor、合作伙伴的聚集地,在这里你可以找到所有 TiDB 相关问题的答案。欢迎大家注册体验~

链接:https://asktug.com/

背景

“通过一个平台,一定能找到 TiDB 所有问题的满意答案。”

因为这样的愿望,TiDB 生态中的用户、Contributor、合作伙伴一起建立了 AskTUG.com 技术问答网站,并于 2019 年 8 月正式公开上线。作为 TUG 成员学习、分享的 “聚集地”,TiDB 用户可以在这里提出、解答问题,相互交流探讨,这里汇集 TiDB 用户的集体智慧。自上线以来,AskTUG.com 逐渐吸引了越来越多用户的关注,截止 2021 年 6 月底,AskTUG.com 已有 7000+ 注册用户,沉淀了 1.6w+ 问题和 300+ 技术文章。

很多小伙伴都已经发现,AskTUG.com 的后端程序是一个 Discourse 程序。关于 Discourse 是 Stack Overflow 的联合创始人 Jeff Atwood 推出的一个新的开源论坛项目,其目的是为了改变十年未变的论坛软件。在 AskTUG.com 建立之初,从以下几个角度确定使用 Discourse:

  • 功能强大:Discourse 特性丰富,可定制性强,是论坛界的 WordPress。与其他传统的论坛相比, Discourse 简化了传统论坛的分类,取而代之是热贴,这点有点像问答,它可以避免用户进入传统论坛后找不到方向的迷茫,这个特性从 AskTUG.com 的页面便可见一斑:

图片

图片

  • 易用性好:Discourse 的帖子是气泡形式展现,全部 Ajax 加载,有电脑和移动版,论坛采用了瀑布流的设计,自动加载下一页,无需手动翻页,简单来说这是一个很赞的系统。

为什么要迁移

到现在为止,Everything is Good,除了一点:Discourse 官方只支持 PostgreSQL 这一种数据库。

作为一家开源数据库厂商,我们有极大的热情和充分的理由让 AskTUG.com 跑在自己的数据库 TiDB 上,最初有这个想法时,当然是找有没有已经将 Discourse port 到 MySQL 的方案,结果是问的人多,行动的没有。

于是我们决定自己来做 Discourse 数据库改造这件事情。原因有二:

  • 吃自己的狗粮,验证 TiDB 的兼容性。

  • Discourse 是一个典型的 HTAP 型应用,它的管理后台有很复杂的报表查询,随着论坛数据量增加,单机 PostgreSQL、MySQL 都很容易出现性能瓶颈。TiDB 5.0 引入的 TiFlash MPP 计算模型正好满足了这种应用场景需求,通过引入 TiFlash 节点,对一些复杂的统计分析类查询做并行处理,达到加速的效果。并且不需要改动 SQL 和复杂的 ETL 流程。

迁移实践

前面我们讲述了做 AskTUG & Discourse 数据库改造项目的原因始末,接来下,则会细致地讲下从 PostgreSQL 迁移到 MySQL / TiDB 踩过的 “坑”,如果有从 PG 迁移到 MySQL 的朋友可以拿来参考。

TiDB 同时兼容 MySQL 协议和生态,迁移便捷,运维成本极低。因此,Discourse 从 PG 迁移到 TiDB 大致分为两步

第一步:将 Discourse 迁移到 MySQL;

第二步:适配 TiDB。

Migrate to MySQL 5.7

🌟mini_sql

minisql 是一个轻量级的 sql wraper,方便做一些 ORM 不擅长的查询,并且可以防止 SQL 注入。之前只支持 PG 和 sqlite。Discourse 的代码依赖 minisql 的地方非常多,重写的话工作量巨大,patch mini_sql 来支持 MySQL 是能够迁移完成的一个重要步骤:https://github.com/discourse/mini_sql/pull/5

🌟 schema migration

Rails 的 schema migration 用来维护 DDL,反映的是数据库 schema 的变化过程,对于迁移来说,其实增加了工作量,解决办法是,先生成一份最终的 schema.rb 文件,在最终结果上做修改,生成一份新的 migration 文件。中间过程产生的 migration 文件删掉就可以了。

🌟 character set utf8mb4

database.yml

development:
  prepared_statements: false
   encoding: utf8mb4
   socket: /tmp/mysql.sock
   adapter: mysql2

/etc/mysql/my.cnf

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

🌟 MySQL can index only the first N chars of a BLOB or TEXT column

PG 的所有类型都是可以索引的,MySQL 不能索引 text 类型,解决办法是,索引的时候指定长度:

t.index ["error"], name: "index_incoming_emails_on_error", length: 100

但对于组合索引的情况更复杂,只能忽略带 text 类型的,好在索引不影响功能。

🌟 data migration

pg2mysql 可以把 pgdump 出来的 insert 语句转换成兼容 MySQL 语法的形式,但只限于简单的形式,一些带有 array 和 json 的格式会乱掉,但这部分用 Ruby 处理起来是正确的,分成两部分处理,首先 pg2mysql 来处理排除一些转换出错的表,比如 user_options、site_settings 等:

PGPASSWORD=yourpass pg_dump discourse_development -h localhost 
  --quote-all-identifiers 
  --quote-all-identifiers 
  --inserts 
  --disable-dollar-quoting 
  --column-inserts 
  --exclude-table-data user_options 
  --exclude-table-data user_api_keys 
  --exclude-table-data  reviewable_histories 
  --exclude-table-data  reviewables 
  --exclude-table-data  notifications 
  --exclude-table-data site_settings 
  --exclude-table-data  reviewables  
  --no-acl 
  --no-owner 
  --format p 
  --data-only -f pgfile.sql

剩下一部分数据使用 seed_dump 来迁移:

bundle exec rake db:seed:dump 
  MODELS=UserApiKey,UserOption,ReviewableHistory,
    Reviewable,Notification,SiteSetting 
EXCLUDE=[] IMPORT=true

🌟 distinct on

PG 有一个 distinct on 的用法,等价于 MySQLONLY_FULL_GROUP_BY 参数关闭时的效果,但从 MySQL 5.7 开始,这个参数默认已经开启了。所以解决办法一个是关掉 ONLY_FULL_GROUP_BY 参数,另一个是用 GROUP 和聚合函数模拟:

# postgresql
SELECT DISTINCT ON (pr.user_id) pr.user_id, pr.post_id, pr.created_at granted_at
  FROM post_revisions pr
  JOIN badge_posts p on p.id = pr.post_id
  WHERE p.wiki
      AND NOT pr.hidden
      AND (:backfill OR p.id IN (:post_ids))
# mysql
SELECT pr.user_id, MIN(pr.post_id) AS post_id, MIN(pr.created_at) AS granted_at
  FROM post_revisions pr
  JOIN badge_posts p on p.id = pr.post_id
  WHERE p.wiki
      AND NOT pr.hidden
      AND (:backfill OR p.id IN (:post_ids))
  GROUP BY pr.user_id

🌟 returning

PG 的 UPDATE、DELETE、INSERT 语句都可以带一个 returning 关键词,用来返回修改/插入之后的结果。对于 UPDATE 和 DELETE 语句,MySQL 改起来比较容易,只需要拆成两步,先查出主键,再更新或删除:

update users set updated_at = now() where id = 801 returning id,updated_at ;
 id  |        updated_at
-----+---------------------------
 801 | 2019-12-30 15:43:35.81969

MySQL 版本:

update users set updated_at = now() where id = 801;
select id, updated_at from users where id = 801;
+-----+---------------------+
| id  | updated_at          |
+-----+---------------------+
| 801 | 2019-12-30 15:45:46 |
+-----+---------------------+

对于单条 INSERT 情况,需要使用 last_insert_id() 函数:

PG 版本

insert into category_users(user_id, category_id, notification_level) values(100,100,1) returning id, user_id, category_id;
 id | user_id | category_id
----+---------+-------------
 59 |     100 |         100

改成 MySQL 版本:

insert into category_users(user_id, category_id, notification_level) values(100,100,1);
select id, category_id, user_id from category_users where id = last_insert_id();
+----+-------------+---------+
| id | category_id | user_id |
+----+-------------+---------+
| 48 |         100 |     100 |
+----+-------------+---------+

对于批量插入,需要改成单条 INSERT,再使用 last_insert_id() 函数,因为 MySQL 没有提供 last_insert_id() 函数:

ub_ids = records.map do |ub|
  DB.exec(
    "INSERT IGNORE INTO user_badges(badge_id, user_id, granted_at, granted_by_id, post_id) 
     VALUES (:badge_id, :user_id, :granted_at, :granted_by_id, :post_id)",
     badge_id: badge.id,
     user_id: ub.user_id,
     granted_at: ub.granted_at,
     granted_by_id: -1,
     post_id: ub.post_id
  )
  DB.raw_connection.last_id
end

DB.query("SELECT id, user_id, granted_at FROM user_badges WHERE id IN (:ub_ids)", ub_ids: ub_ids)

🌟 insert into on conflict do nothing

PG 9.5 开始支持 upsert,MySQL 也有同样的功能,只是写法不一致:

# postgresql
DB.exec(<<~SQL, args)
  INSERT INTO post_timings (topic_id, user_id, post_number, msecs)
  SELECT :topic_id, :user_id, :post_number, :msecs
  ON CONFLICT DO NOTHING
SQL
# MySQL
DB.exec(<<~SQL, args)
  INSERT IGNORE INTO post_timings (topic_id, user_id, post_number, msecs)
  SELECT :topic_id, :user_id, :post_number, :msecs
SQL

🌟 select without from

PG 里允许这样的语法:select 1 where 1=2;

但在 MySQL 里这是不合法的,因为没有 FROM 子句,解决办法很 trick,手动建一个只有一条数据的表,专门用来兼容这个语法。

execute("create table one_row_table (id int)")
execute("insert into one_row_table values (1)")

MySQL 使用:

# MySQL
select 1 from one_row_table where 1=2;

🌟 full outer join

MySQL 不支持 full outer join,需要使用 LEFT JOIN + RIGHT JOIN + UNION 来模拟:

# MySQL
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

🌟 recursive cte

MySQL 8.0 之前不支持 CTE/Recursive CTE,结构简单的 CTE 可以直接改成子查询,除了可读性差以外,功能上没任何影响。recursive CTE 可以用 User-defined variables 来模拟。Discourse 里有一个嵌套回复的查询:

WITH RECURSIVE breadcrumb(id, level) AS (
    SELECT 8543, 0
    UNION
    SELECT reply_id, level + 1
    FROM post_replies AS r
      JOIN breadcrumb AS b ON (r.post_id = b.id)
    WHERE r.post_id <> r.reply_id
          AND b.level < 1000
  ), breadcrumb_with_count AS (
      SELECT
        id,
        level,
        COUNT(*) AS count
      FROM post_replies AS r
        JOIN breadcrumb AS b ON (r.reply_id = b.id)
      WHERE r.reply_id <> r.post_id
      GROUP BY id, level
  )
  SELECT id, level
  FROM breadcrumb_with_count
  ORDER BY id

使用 MySQL 5.7 来兼容:

# MySQL
SELECT id, level FROM (
  SELECT id, level, count(*) as count FROM (
    SELECT reply_id AS id, length(@pv) - length((replace(@pv, ',', '')))  AS level
      FROM (
             SELECT * FROM post_replies ORDER BY post_id, reply_id) pr,
             (SELECT @pv := 8543) init
     WHERE find_in_set(post_id, @pv)
           AND length(@pv := concat(@pv, ',', reply_id))
  ) tmp GROUP BY id, level
) tmp1
WHERE (count = 1)
ORDER BY id

PG 的 cte 是可以嵌套的,比如 Discourse 里这段查询,注 WITH period_actions 是嵌套在 flag_count 里面的:

WITH mods AS (
 SELECT
 id AS user_id,
 username_lower AS username,
 uploaded_avatar_id
 FROM users u
 WHERE u.moderator = 'true'
 AND u.id > 0
 ),
 time_read AS (
 SELECT SUM(uv.time_read) AS time_read,
 uv.user_id
 FROM mods m
 JOIN user_visits uv
 ON m.user_id = uv.user_id
 WHERE uv.visited_at >= '#{report.start_date}'
 AND uv.visited_at <= '#{report.end_date}'
 GROUP BY uv.user_id
 ),
 flag_count AS (
     WITH period_actions AS (
     SELECT agreed_by_id,
     disagreed_by_id
     FROM post_actions
     WHERE post_action_type_id IN (#{PostActionType.flag_types_without_custom.values.join(',')})
     AND created_at >= '#{report.start_date}'
     AND created_at <= '#{report.end_date}'
     ),
     agreed_flags AS (
     SELECT pa.agreed_by_id AS user_id,
     COUNT(*) AS flag_count
     FROM mods m
     JOIN period_actions pa
     ON pa.agreed_by_id = m.user_id
     GROUP BY agreed_by_id
     ),
     disagreed_flags AS (
     SELECT pa.disagreed_by_id AS user_id,
     COUNT(*) AS flag_count
     FROM mods m
     JOIN period_actions pa
     ON pa.disagreed_by_id = m.user_id
     GROUP BY disagreed_by_id
  )

这种用子查询模拟起来就非常复杂,可以使用临时表来兼容,查询部分不需要任何修改,只需要按依赖顺序把 WITH 部分换成临时表:

DB.exec(<<~SQL)
    CREATE TEMPORARY TABLE IF NOT EXISTS mods AS (
      SELECT
      id AS user_id,
      username_lower AS username,
      uploaded_avatar_id
      FROM users u
      WHERE u.moderator = true
      AND u.id > 0
    )
  SQL

  DB.exec(<<~SQL)
    CREATE TEMPORARY TABLE IF NOT EXISTS time_read AS (
      SELECT SUM(uv.time_read) AS time_read,
      uv.user_id
      FROM mods m
      JOIN user_visits uv
      ON m.user_id = uv.user_id
      WHERE uv.visited_at >= '#{report.start_date.to_s(:db)}'
      AND uv.visited_at <= '#{report.end_date.to_s(:db)}'
      GROUP BY uv.user_id
    )
  SQL

🌟 delete & update

PG 和 MySQL 的 update/delete 语句写法是不一样的,使用 ORM 会自动处理,但 Discourse 里大量代码是使用 mini_sql 手写的 SQL,需要逐个替换。

PG 的 update 语句写法:

# postgresql
UPDATE employees
SET department_name = departments.name
FROM departments
WHERE employees.department_id = departments.id

MySQL 的 update 语句写法:

# MySQL
UPDATE employees
LEFT JOIN departments ON employees.department_id = departments.id
SET department_name = departments.name

delete 语句也类似。

🌟 You can't specify target table xx for update in FROM clause

从 PG 迁到 MySQL 之后,很多语句会报这样一个错误:You can't specify target table 'users' for update in FROM clause。

# MySQL
update users set updated_at = now() where id in (
  select id from users where id < 10
);
# You can't specify target table 'users' for update in FROM clause

解决办法是子查询里再用 derived table:

# MySQL
update users set updated_at = now() where id in (
  select id from (select * from users) u where id < 10
);

🌟 MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

还拿上面的查询举例,子查询如果带 LIMIT:

# MySQL
update users set updated_at = now() where id in (
  select id from (select * from users) u where id < 10 limit 10
);
# MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

最简单的解决办法是再 derived 一次:

# MySQL
update users set updated_at = now() where id in (
  select id from (
    select id from (select * from users) u where id < 10 limit 10
  ) u1
);

🌟 window function

MySQL 8.0 之前没有窗口函数,可以使用 User-Defined Variables 替代:

# postgresql
WITH ranked_requests AS (
  SELECT row_number() OVER (ORDER BY count DESC) as row_number, id
    FROM web_crawler_requests
   WHERE date = '#{1.day.ago.strftime("%Y-%m-%d")}'
)
DELETE FROM web_crawler_requests
WHERE id IN (
  SELECT ranked_requests.id
    FROM ranked_requests
   WHERE row_number > 10
)
# MySQL
DELETE FROM web_crawler_requests
WHERE id IN (
  SELECT ranked_requests.id
    FROM (
          SELECT @r := @r + 1 as row_number, id
            FROM web_crawler_requests, (SELECT @r := 0) t
           WHERE date = '#{1.day.ago.strftime("%Y-%m-%d")}'
        ORDER BY count DESC
    ) ranked_requests
   WHERE row_number > 10
)

🌟 swap columns

MySQL 和 PG 在处理 update 语句时,column 的引用行为是不一致的,PG 引用的是原始值,而 MySQL 引用的是更新后的值,举个例子:

# postgresql
create table tmp (id integer primary key, c1 varchar(10), c2 varchar(10));

insert into tmp values (1,2,3);
insert into tmp values (2,4,5);

select * from tmp;
 id | c1 | c2
----+----+----
  1 | 3  | 2
  2 | 5  | 4

update tmp set c1=c2,c2=c1;

select * from tmp;
 id | c1 | c2
----+----+----
  1 | 3  | 2
  2 | 5  | 4
# MySQL

create table tmp (id integer primary key, c1 varchar(10), c2 varchar(10));

insert into tmp values (1,2,3);
insert into tmp values (2,4,5);

select * from tmp;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | 2    | 3    |
|  2 | 4    | 5    |
+----+------+------+

update tmp set c1=c2,c2=c1;

select * from tmp;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | 3    | 3    |
|  2 | 5    | 5    |
+----+------+------+

🌟 function

PG 和 MySQL 的一些内置函数名称和行为会有一些不一致:

  • regexp_replace -> replace

  • pg_sleep -> sleep

  • ilike -> lower + like

  • ~* -> regexp

  • || -> concat

  • set local statementtimeout -> set session statementtimeout

  • offset a limit b -> limit a offset b

  • @ -> ABS

  • interval -> date_add 或者 datediff

  • extract epoch from -> unix_timestimp

  • unnest -> union all

  • json 语法:json->> 'username' to json ->> '$.username' 

  • position in -> locate

  • generate_series -> union

  • greatest & least -> greatest/least + coalesce

🌟 type & casting

MySQL 使用 cast 函数,PG 也支持同样的语法,不过更常用的是四个点::,比如 SELECT 1::varchar,MySQL 的转换类型只能是下面 5 种:CHAR[(N)]、 DATE、DATETIME、DECIMAL、SIGNED、TIME。

select cast('1' as signed);

Rails 里 string 类型,PG 映射成 varchar,MySQL 映射成 varchar(255),而 PG 的 varchar 其实是可以存储超过 255 的,Discourse 里一些使用 string 类型的数据会超过 255 ,转成 MySQL 以后会被截断,解决办法是对这部分列使用 text 类型。

🌟 keywords

MySQL 和 PG 的 keywords 列表并不完全一致,比如 read 在 MySQL 里是关键字,在 PG 里并不是。对于 ORM 生产的 SQL 已经处理好了,一些手写的 SQL 需要自己去 quote,PG 使用"",MySQL 使用``。

🌟 expression index

PG 支持表达式索引:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

Discourse 里面一些功能会在表达式索引上面加唯一约束,MySQL 没有直接的对应,但是可以使用 Stored Generated Column 来模拟,先冗余一个 Stored Generated Column,再在上面加唯一约束,达到了同样的效果。

Rails 也支持:

t.virtual "virtual_parent_category_id", type: :string, as: "COALESCE(parent_category_id, '-1')", stored: true 
t.index "virtual_parent_category_id, name", name: "unique_index_categories_on_name", unique: true

🌟 array && json

PG 支持 array 和 json 类型,MySQL 5.7 已结有了 JSON,Discourse 里,ARRAY 和 JSON 的使用场景比较单一,都是用来存储,没有高级检索需求,直接使用 JSON 可以替代 PG 的 array 和 json。但 MySQL 的 JSON 和 text 都不支持 default value,只能在应用层设置,可以使用:https://github.com/FooBarWidget/default_value_for

适配 TiDB

TiDB 支持 MySQL 传输协议及其绝大多数的语法,但是一些特性由于在分布式环境下没法很好地实现,所以在部分特性的表现仍然与 MySQL 有一些差异,详见文档https://pingcap.com/docs-cn/stable/reference/mysql-compatibility/,接下来我们主要看一下本次迁移中涉及到的一些小问题。

🌟 TiDB 保留关键字

TiDB 在新版本 (本次迁移使用 v3.0.7) 中支持了 Window Function ,引入了 grouprankrow_number 等函数,但比较特殊的是上述函数名都会被 TiDB 当做关键词处理,所以我们在开启窗口函数的时候需要修改命名与窗口函数名类似的 SQL,将相关的关键字用反引号包住。

TiDB 保留关键字:https://pingcap.com/docs-cn/stable/reference/sql/language-structure/keywords-and-reserved-words/

TiDB 窗口函数: https://pingcap.com/docs-cn/stable/reference/sql/functions-and-operators/window-functions/

🌟 Insert into select 语法不兼容

TiDB 暂时不支持该语法,可以使用 insert into select from dual 绕过:

invalid: insert into t1 (i) select 1;
valid: insert into t1 (i) select 1 from dual;

🌟 嵌套事务 & savepoint

TiDB 不支持嵌套事务,同样也不支持 savepoint。但是 Rails ActiveRecord 在数据库是 MySQL 或者 PostgreSQL 时,使用 savepoint 来模拟嵌套事务,并使用 requires_new选项来控制,文档:https://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html

所以在数据库迁移到 TiDB 后,我们需要调整业务代码,将原有涉及到 嵌套事务 的逻辑,调整为单层事务,遇到异常统一回滚,同时在 discourse 中取消使用 requires_new 选项。

TiDB 强大的兼容性

TiDB 100% 兼容 MySQL 5.7 协议。除此之外,还支持了 MySQL 5.7 常用的功能及语法。MySQL 5.7 生态中的系统工具(PHPMyAdmin、Navicat、MySQL Workbench、mysqldump、Mydumper/Myloader)、客户端等均适用于 TiDB。同时,TiDB 5.0 之后,很多新的特性也将陆续发布,比如表达式索引、CTE、临时表等,新版本的 TiDB 兼容性越来越好,从 MySQL 或 PostgreSQL 迁移到 TiDB 也会变得越来越容易。

总结

该项目已经 100% 完成,且目前 AskTUG 网站(https://asktug.com)平稳地运行在 TiDB(当前版本:tidb-v5.0.x)上已一年有余。是的,在没改变体验的情况下,谁也没有发现数据库已经悄悄改变了~证明了跑在 PG 上的业务迁移到 TiDB 的可行性。

项目的地址是:https://github.com/tidb-incubator/discourse/tree/my-2.3.3,可以通过 fork & 提 PR 的方式来参与改进并关注该项目的进展,也非常欢迎 Ruby 社区,Ruby On Rails 社区,Discourse 社区的小伙伴来感受下来自 TiDB 社区的善意。

更多原创文章干货分享,请关注公众号
  • 加微信实战群请加微信(注明:实战群):gocnio

圈子真小,居然是炮哥(hooopo)的文章。

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册