orczhou.com

MySQL

  • 使用PolarDB的并行查询优化大赛SQL

    ·

    admin

    在昨天进行的SQL编程大赛中，所有 MySQL 选手的成绩都没有进入八强的。个人也对这个问题比较感兴趣，经过初步分析，重要的原因在于 MySQL 实现中没有比较好的并行加速的能力。而在 MySQL 的衍生版本中，倒是有几个版本提供了并行执行的能力。包括了 PolarDB 的 Elastic Parallel Query[2]、Amazon Aurora 的 Parallel query[3] 。所以，也打算验证一下，如果加上这些并行能力，是否能够更快。

    结果综述

    PolarDB MySQL 运行了与 MySQL “几乎”（仅添加Hint开启并行）相同的SQL（参考）运行最快为：3.821 s。相比在同一个集群，不开启并行的时间是 6.647s，速度提升了 42.5% 。此外也测试了Aurora的相同的规格，几经调试依旧无法使用其并行能力。

    在相同的SQL实现下，PolarDB MySQL 可能是所有 MySQL 版本中性能最好的。如果，感觉还有什么版本可能有更好的性能，欢迎留言。

    数据与SQL

    这次是尝试使用 MySQL 高性能的完成“第二次SQL编程大赛”的进阶挑战。完整的题目描述可以参考：赛题说明[4]。这里实现的 PolarDB MySQL 版本 SQL 参考：gt.polardb.sql@GitHub[1]（或参考本文结尾部分）。

    PolarDB的规格选择

    这里选择与试题类似的4c8g规格，详细参数如下：

    主要参数：

    • CPU架构：x86
    • 产品版本：企业版
    • 小版本号：8.0.2（与 MySQL 8.0.18 完全兼容）
    • IMCI只读节点个数：0
    • 初始只读节点个数：0
    • 初始读写节点个数：1
    • 节点规格：4 核 8GB（通用）
    • 存储类型：PSL5

    在 PolarDB 上并行执行

    PolarDB 的并行执行可以使用 Hint 较为方便的开启：

    SELECT
  /*+PARALLEL(8)*/
...

    可以通过执行计划观察，实际是否使用了并行：

    | -> Gather (merge sort; slice: 1; workers: 8)  (cost=2024861688.70 rows=1995124000) (actual time=1774.761..2159.495 rows=1000000 loops=1)
    -> Sort: <temporary>.p_id  (cost=1705198767.38 rows=249390500) (actual time=480.641,781.971,239.446..503.108,818.048,250.387 rows=125000,220305,60043 loops=1,1,1)
        -> Stream results (actual time=1.631,2.232,1.396..380.381,630.231,168.213 rows=125000,220305,60043 loops=1,1,1)
            -> Left hash join (t_01.a_s = p_01.a_s), (t_01.d_s = p_01.d_s), extra conditions: ((p_01.seq >= ((t_01.p_seat_to - t_01.seat_count) + 1)) and (p_01.seq <= t_01.p_seat_to))  (cost=25015432.30 rows=249390500) (actual time=1.621,2.220,1.387..200.048,332.885,86.841 rows=125000,220305,60043 loops=1,1,1)
                -> Parallel table scan on p_01, with parallel partitions: 8 (actual time=0.002,0.003,0.002..48.149,74.839,20.489 rows=125000,220305,60043 loops=1,1,1)
                    -> Materialize (shared access, partitions: 8, partition_keys: a_s,) (actual time=0.001,0.002,0.001..24.240,34.098,9.331 rows=125000,220305,60043 loops=1,1,1)
                        -> Gather (slice: 1; workers: 8)  (cost=1146187.18 rows=997560) (actual time=158.204..362.897 rows=1000000 loops=1)
                            -> Window aggregate  (cost=1090064.43 rows=124695) (actual time=160.066,167.128,157.433..296.715,314.531,278.945 rows=125000,149658,102922 loops=1,1,1)
                                -> Repartition (hash keys: passenger.departure_station, passenger.arrival_station; merge sort; slice: 2; workers: 8)  (cost=514229.38 rows=124695) (actual time=160.059,167.121,157.424..223.566,236.036,217.548 rows=125000,149658,102922 loops=1,1,1)
                                    -> Sort: passenger.departure_station, passenger.arrival_station  (cost=12554.66 rows=124695) (actual time=152.998,157.330,149.335..172.035,180.041,168.003 rows=125000,132932,111478 loops=1,1,1)
                                        -> Parallel table scan on passenger, with parallel partitions: 745 (actual time=0.052,0.058,0.046..57.559,65.907,54.511 rows=125000,132932,111478 loops=1,1,1)
                -> Hash
                    -> Table scan on t_01
                        -> Materialize (shared access) (actual time=0.002,0.003,0.001..0.744,0.935,0.666 rows=2000,2000,2000 loops=1,1,1)
                            -> Gather (slice: 1; workers: 8)  (cost=3416.33 rows=2000) (actual time=6.884..7.646 rows=2000 loops=1)
                                -> Window aggregate with buffering  (cost=3293.83 rows=250) (actual time=1.955,2.233,1.724..3.692,3.760,3.633 rows=250,292,196 loops=1,1,1)
                                    -> Repartition (hash keys: t_include_no_seat.d_s, t_include_no_seat.a_s; slice: 2; workers: 1)  (cost=3161.31 rows=250) (actual time=1.930,2.215,1.691..2.759,2.922,2.589 rows=250,292,196 loops=1,1,1)
                                        -> Sort: t_include_no_seat.d_s, t_include_no_seat.a_s, t_include_no_seat.if_no_seat, t_include_no_seat.t_id (actual time=1.383,1.383,1.383..2.070,2.070,2.070 rows=2000,2000,2000 loops=1,1,1)
                                            -> Table scan on t_include_no_seat (actual time=1.381,1.381,1.381..1.685,1.685,1.685 rows=2000,2000,2000 loops=1,1,1)
                                                -> Materialize with deduplication (shared access) (actual time=0.001,0.001,0.001..0.295,0.295,0.295 rows=2000,2000,2000 loops=1,1,1)
                                                    -> Table scan on <union temporary>  (cost=2.50 rows=0) (actual time=0.001..0.290 rows=2000 loops=1)
                                                        -> Union materialize with deduplication  (actual time=2.330..3.003 rows=2000 loops=1)
                                                            -> Table scan on train  (cost=101.25 rows=1000) (actual time=0.050..0.380 rows=1000 loops=1)
                                                            -> Table scan on train  (cost=101.25 rows=1000) (actual time=0.023..0.348 rows=1000 loops=1)
 |

    这里的诸如Gather (merge sort; slice: 1; workers: 8)等内容，显示对应的部分会通过多线程并行执行。

    执行时间统计

    这里运行了该 SQL 三次的结果统计如下：

    real	0m3.863s
user	0m0.379s
sys	0m0.100s

real	0m3.917s
user	0m0.414s
sys	0m0.123s

real	0m3.821s
user	0m0.422s
sys	0m0.128s

    说明：这里，因为PolarDB是运行在云端，故仅需关注这里的 real 部分的时间。

    不开启并行时 PolarDB 的性能

    该组数据可用对比：

    real	0m6.743s
user	0m0.394s
sys	0m0.120s

real	0m6.647s
user	0m0.393s
sys	0m0.120s

real	0m6.665s
user	0m0.407s
sys	0m0.125s

    并行执行的一些状态参数

    mysql> show global status like '%pq_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| PQ_fallback_one_worker              | 0     |
| PQ_local_workers_created            | 297   |
| PQ_migrant_workers_created          | 0     |
| PQ_net_exchange_fail_connect        | 0     |
| PQ_refused_over_computing_resource  | 0     |
| PQ_refused_over_max_queuing_time    | 0     |
| PQ_refused_over_total_workers       | 0     |
| PQ_remote_workers_created           | 0     |
| PQ_running_local_workers            | 0     |
| PQ_running_migrant_workers          | 0     |
| PQ_running_remote_workers           | 0     |
| PQ_sched_adative_resource_dec_count | 0     |
| PQ_sched_adative_resource_inc_count | 0     |
+-------------------------------------+-------+

    使用 Aurora 的并行执行

    这里也尝试使用 Aurora 的并行查询进行优化，但是并没有成功。Aurora 的并行执行并没有 Hint 可以控制，而是优化器根据需要选择使用。在本次测试中，在一个 4c32gb 的Aurora实例上，几经尝试，都未能实现并行。故未成功测试。在非并行时，Aurora的执行时间为 6.921s。

    开启Aurora并行执行

    要使用 Aurora 的并行执行能力，需要先创建最新版本的Aurora，在选择的参数组（parameter group）时，该参数组需要打开 aurora_parallel_query 参数。在实例创建完成后，可以通过如下命令查看并行查询是否打开：

    mysql> show global variables like '%aurora_parallel_query%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| aurora_parallel_query | ON    |
+-----------------------+-------+
    Aurora上的执行时间

    这里记录相关SQL的执行时间如下：

    real	0m6.921s
user	0m1.022s
sys	0m0.076s
[ec2-user@xterm-256color- delete_me]$ time mysql --local-infile=true -hpq-testing.cluster-cjzowaj9vqpd.ap-northeast-1.rds.amazonaws.com -ub_admin -p-f7HNhmp_frX game_ticket < aurora.sql > aurora.ret
mysql: [Warning] Using a password on the command line interface can be insecure.

real	0m6.955s
user	0m1.012s
sys	0m0.076s
[ec2-user@xterm-256color- delete_me]$ time mysql --local-infile=true -hpq-testing.cluster-cjzowaj9vqpd.ap-northeast-1.rds.amazonaws.com -ub_admin -p-f7HNhmp_frX game_ticket < aurora.sql > aurora.ret
mysql: [Warning] Using a password on the command line interface can be insecure.

real	0m7.154s
user	0m0.970s
sys	0m0.138s

    最后

    PolarDB MySQL 在并行执行开启的情况下性能提升了42.5%，最终执行时间为 3.821 s。有可能是所有 MySQL 兼容的发型版本中性能最快的。

    gt.polardb.sql

    -- explain analyze
WITH
  t_no_seat_virtual AS (
    select
      train_id as t_id,
      departure_station as d_s,
      arrival_station as a_s,
      seat_count,
      seat_count*0.1 as seat_count_no_seat
    from train
  ),
  t_include_no_seat AS (
    select t_id,d_s ,a_s ,seat_count, 0 as if_no_seat
    from t_no_seat_virtual
    union
    select t_id,d_s ,a_s ,seat_count_no_seat, 1 as if_no_seat
    from t_no_seat_virtual
  )
SELECT
  /*+PARALLEL(8)*/
  p_01.p_id,         -- output 01
  p_01.d_s,          -- output 02
  p_01.a_s,          -- output 03
  t_01.t_id as t_id, -- output 04
  IF(
      if_no_seat,
      "" ,
      ceil((p_01.seq-t_01.p_seat_to + t_01.seat_count)/100)
  ) as t_carr_id, -- output 05

  CASE IF( !isnull(t_01.t_id) and if_no_seat,-1,ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)%5))
    WHEN 1  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"A")
    WHEN 2  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"B")
    WHEN 3  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"C")
    WHEN 4  THEN CONCAT( ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5) ,"E")
    WHEN 0  THEN CONCAT( IF( (p_01.seq-t_01.p_seat_to + t_01.seat_count)%100 = 0, "20" ,ceil((( p_01.seq-t_01.p_seat_to + t_01.seat_count )%100)/5)) ,"F")
    WHEN -1 THEN "无座"
    ELSE NULL
  END as seat_index   -- output 06
FROM
  (
    select
      /*+PARALLEL(8)*/
      ROW_NUMBER() over(PARTITION BY departure_station,arrival_station) as seq ,
      passenger_id as p_id,
      departure_station as d_s,
      arrival_station as a_s
    from
    passenger
  ) as p_01

  LEFT JOIN

  (
    select
      /*+PARALLEL(8)*/
      seat_count,
      sum(seat_count)
        over (
               PARTITION BY d_s,a_s
               ORDER BY     if_no_seat,t_id
             ) as p_seat_to ,
      t_id,
      d_s ,
      a_s ,
      if_no_seat
    from
    t_include_no_seat
  ) t_01

  ON
        p_01.seq >= p_seat_to-seat_count + 1
    and p_01.seq <= p_seat_to
    and p_01.d_s =  t_01.d_s
    and p_01.a_s =  t_01.a_s
ORDER BY p_01.p_id

    参考链接

  • 使用 MySQL 8.0 双密码功能实现无中断的密码更换

    ·

    admin

    随着企业规模增长，或者，企业原本就在监管非常严格的行业或环境，所面临合规要求也会逐步增多，密码的定期轮换通常也是众多要求中的一个基础项。MySQL 8.0 提供的“双密码”管理机制，可以让大规模数据库密码轮换变得更平滑，而无需任何的停机时间。

    100台MySQL、1000台应用服务

    考虑这样的场景，你是一个 DBA ，生产环境有 100 台 MySQL 实例，运行在这些数据库上约有 1000 台应用服务器。如果需要对，这些 MySQL 的应用账号密码统一做一次更新，如何实现不停机的迁移。

    在 MySQL 之前（8.0.14版本之前）的单密码机制下，更换密码通常会遇到以下问题：无论是先更新数据库服务器上的密码，还是新调整应用服务器上的密码，这个先后顺序都会带来一定的服务不可用时间。通常，为了降低这个服务不可用时间，应用变更人员和 DBA 则需要非常密切的配合。

    双密码机制（dual password）

    从 MySQL 8.0.14 起，开始支持了双密码策略。在密码更换时，可以保留旧密码依旧可用：

    ALTER USER 'appuser1'@'host1.example.com'
  IDENTIFIED BY 'password_b'
  RETAIN CURRENT PASSWORD;

    在一段时间后，可以删除旧密码：

    ALTER USER 'appuser1'@'host1.example.com'
  DISCARD OLD PASSWORD;

    构建批量密码轮换流程

    有了该功能，就可以使用上述的命令，更加平滑的实现大规模数据库场景下的密码更换。

    1. 给变更数据库服务器上账号的密码，同时将原密码作为备用密码保留（使用“RETAIN CURRENT PASSWORD”）
    2. 变更应用服务器上的访问数据库的密码
    3. 检查并观察一段时间，删除数据库中的原密码（使用“DISCARD OLD PASSWORD ”）

    这是一个简化的过程，实际的操作还需要考虑：

    • 严格遵循企业内部的变更规范
    • 灰度的进行发布，而不是一次性操作所有的实例/服务器
    • 做好检查，需要确保诸如密码已经变更完成、应用服务器密码均已经更新
    • 做好回滚方案，避免过程中操作失误导致的问题
    • 在业务低峰时间进行操作

    参考阅读

  • 关闭 InnoDB 的 redo log

    ·

    admin

    在 MySQL 实例恢复时（尤其是逻辑备份的恢复），为了获得更快的恢复速度，通常会关闭二进制日志（Binary Log），并且将 InnoDB 的日志持久化级别调整到最低。从 MySQL 8.0.21起[1]，更进一步的，可以彻底的关闭 InnoDB redo 从而获得更好导入速度。后续的 8.4 / 9.0 / 9.1 可以使用该特性。

    在本文的测试中，可以看到关闭 InnoDB redo log 导入速度可以提升约 26%。

    使用场景

    最为常见的就是在进行大量数据导入时，希望能够加速数据导入的过程。

    管理命令

    可以使用如下的命令关闭/或打开 InnoDB redo log：

     ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG

    关闭 InnoDB redo log

    mysql>  ALTER INSTANCE DISABLE INNODB REDO_LOG;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Innodb_redo_log_enabled%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+
1 row in set (0.02 sec)

    打开 InnoDB redo log

    mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
Query OK, 0 rows affected (1.02 sec)

mysql> SHOW STATUS LIKE '%Innodb_redo_log_enabled%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

    执行该命令的权限

    因为该命令对数据库影响巨大，所以也引入独立的权限 INNODB_REDO_LOG_ENABLE来管理该命令的执行权限。具体参考：

    mysql> GRANT INNODB_REDO_LOG_ENABLE ON *.* to 'data_load_admin';

    性能对比

    这里做应该简单的性能对比，看看关闭 InnoDB Redo Log 导入速度会提升多少。

    # mysql -uroot test -e "show status like 'Innodb_redo_log_enabled'"
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+
#  mysql -uroot test -e "truncate table passenger"
# time mysql -uroot test < passenger.1000.sql > /dev/null

real	0m3.109s
user	0m0.017s
sys	0m0.013s
# mysql -uroot test -e "truncate table passenger"
# mysql -uroot test -e "ALTER INSTANCE DISABLE INNODB REDO_LOG"
# time mysql -uroot test < passenger.1000.sql > /dev/null

real	0m2.286s
user	0m0.022s
sys	0m0.009s

    在这个初步测试中，可以观察到，在关闭 InnoDB Redo 之后，到如时间从 3.109s 降低到了 2.286s，在该导入中，节省时间约 26%的时间。

    参考文档

  • Oracle Cloud上的MySQL 9.0的性能

    ·

    admin

    Oracle Cloud 是所有云平台最先支持 9.0 版本的。这里，我们来看看该版本的“标准性能”表现如何。

    测试实例与环境说明

    这里使用的实例类型是：MySQL.4，单个节点为4 ecpu 32gb，测试区域选择的是“东京”（ap-tokyo-1），多可用区（FAULT DOMAIN）的版本，测试实例存储空间大小为 100 gb。即：

    instance_type=MySQL.4
vcpu_per_node=4
memory_size_per_node=32
region=tokyo
availability=multi-az
storage_size=100
db_version=8.0.39/8.4.2/9.0.1

    性能对比

    本次测试分别测试了 8.0.39/8.4.2/9.0.1 这三个版本。详细的性能对比如下：

    threads/qpsMySQL80MySQL84MySQL90
    4355136063360
    8593653785256
    16805481867287
    32831780297817
    48813082047911
    64783879818060
    96850484308172
    128819882868000
    192804380538112
    256790780347536
    384820980558151
    512838680307872

    性能概述

    从该“标准”测试来看，9.0.1的性能较为稳定。从上述数据中来看，似乎略微低于 8.0和8.4 版本，但经过调查，主要原因是由于云平台 CPU 资源多少所导致的，而并不是数据库本身的问题。

    此外，在今年5月份观察到的8.4性能退化问题（参考），目前也已经解决。

  • 云数据库RDS MySQL性能测试与对比@2024年09月

    ·

    admin

    这是一个系列文章的一部分，该系列较为完整的对各个云厂商的RDS MySQL进行了测试，包括了阿里云、腾讯云、华为云、百度云、AWS、Azure、GCP、Oracle Cloud等，更多参考：云数据库RDS MySQL的性能。

    在不同的云厂商，购买相同规格的MySQL实例(如4vCPU-16GB)，获得的性能相同吗？

    threads/qpsaliyun_202409_hangzhou->stdbenchtencent_202409_beijing_exclusive->stdbenchhwcloud_202409_beijing_x86->stdbenchbaiducloud_202409_beijing->stdbenchaws_202409_tokyo_m6i->stdbenchazure_202409_east_asia_4c16g->stdbenchgcp_202409_tokyo_80_enterprise->stdbenchoci_202409_tokyo_8039->mysql_on_4_ecpu
    47102559225572206163920257233551
    897029936467441013313365413415936
    161466016141822972986427654825028054
    3222155223361352012022121571036348578317
    4827905247701784916448165161197367458130
    6432704264952011418187181181276180717838
    9636846290772088321007207821330096758504
    128396972991820128210292244613388106208198
    192389993061020521220912259013478115078043
    256383563105221187216652232312985118727907
    384396793122421729211672190212904121318209
    512403333180522647216272159112930121068386
    have_sslDISABLEDDISABLEDDISABLEDDISABLEDYESYESYESYES
    innodb_buffer_pool_size9.75GB12GB9GB12GB11GB12GB11GB17GB
    innodb_doublewriteONONONONOFFOFFONON
    innodb_flush_log_at_trx_commit11111111
    innodb_flush_methodO_DIRECTO_DIRECTO_DIRECTfsyncO_DIRECTfsyncO_DIRECTO_DIRECT
    innodb_io_capacity200002000012000200020020050001250
    innodb_read_io_threads44484NA42
    innodb_write_io_threads44484NA44
    log_binONONONONOFFONONON
    performance_schemaOFFOFFOFFOFFOFFONONON
    rpl_semi_sync_master_enabledONONONONNANANANA
    rpl_semi_sync_master_timeout1000100001000010000NANANANA
    sync_binlog11110001111
    thread_pool_size84NANANA4NA16
    version8.0.368.0.30-txsql8.0.28-2310038.0.32-2.0.0.28.0.358.0.37-azure8.0.31-google8.0.39-cloud
    cpu_capacity80.493.3163.673.9110.956.349.9114.7

    测试结果概述

    在本次测试中：阿里云RDS MySQL性能表现最好，极限的QPS达到了4万；其次是腾讯云，达到了3.2万；第二梯队是华为云、百度云和AWS，极限的QPS约2.2万；之后是Azure、Google云，极限QPS约1.2万；最后是Oracle云，极限QPS约8500。详细的数据和趋势图，可以参考以上的图、表，这里不再详述。

    (more…)

  • 云数据库RDS MySQL性能测试与对比@2024年05月

    ·

    admin

    本问是一个系列文章的一部分，该系列较为完整的对各个云厂商的RDS MySQL进行了测试，包括了阿里云、腾讯云、华为云、百度云、AWS、Azure、GCP、Oracle Cloud等，更多参考：云数据库RDS MySQL的性能。

    在不同的云厂商，购买相同规格的MySQL实例(如4vCPU-16GB)，获得的性能相同吗？

    为了回答上面的疑问，于是就开启了我的数据库性能测试之旅。这是第二次测试，上一次是在2023年12月（参考：云数据库(RDS MySQL)性能深度测评与对比）。

    性能测试结果与概述

    (more…)