行锁 tps 测试

2020-08-19 本文已影响0人蓝笔头

MySQL

1. 环境准备

mysql 版本为 8.0.21。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+

创建用来测试表 demo。

drop table IF EXISTS demo;
CREATE TABLE demo (
  id bigint NOT null AUTO_INCREMENT PRIMARY KEY,
  value int
);

-- 插入 update 命令需要的数据
insert into demo(value) VALUES(0);

创建用来执行 update 语句的存储过程。

DELIMITER $$

DROP PROCEDURE IF EXISTS update_demo;
CREATE PROCEDURE update_demo(IN count INTEGER)
BEGIN
    declare var int;
    set var=0;  
    while var<count do  
        UPDATE demo set value=value+1 where id=1;
        set var=var+1;  
    end while;  
END; $$

DELIMITER ;

执行完上述命令后验证一下数据。

mysql> show create procedure update_demo;
+-------------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure   | sql_mode                                                                                                              | Create Procedure                                                                                                                                                                                                 | character_set_client | collation_connection | Database Collation |
+-------------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| update_demo | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `update_demo`(IN count INTEGER)
BEGIN
declare var int;
set var=0;
while var<count do
UPDATE demo set value=value+1 where id=1;
set var=var+1;
end while;
END | latin1               | latin1_swedish_ci    | utf8mb4_0900_ai_ci |
+-------------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> show create table demo;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| demo  | CREATE TABLE `demo` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `value` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from demo;
+----+-------+
| id | value |
+----+-------+
|  1 |     0 |
+----+-------+
1 row in set (0.00 sec)

2. tps 测试

调用存储过程触发 update 执行。

-- 执行 20 次 update
mysql> call update_demo(20);
Query OK, 1 row affected (2.04 sec)

mysql> select * from demo;
+----+-------+
| id | value |
+----+-------+
|  1 |    20 |
+----+-------+
1 row in set (0.03 sec)

发现执行 20 次 update，耗时 2s，单行 tps 为 10。

PostgreSQL

1. 环境准备

postgresql 版本为 12.4。

athena=> select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

-- 查询自动提交配置（发现默认是关闭的）
athena=> \echo :AUTOCOMMIT
off

-- 设置为自动提交模式
athena=> \set AUTOCOMMIT on
athena=> \echo :AUTOCOMMIT
on

创建用来测试表 demo。

drop table if exists demo;

-- 创建表之前要先提权，切换为有权限的角色。
set role athena;

CREATE TABLE demo (
  id bigserial NOT NULL PRIMARY KEY,
  value int
);


-- 插入 update 命令需要的数据
insert into demo(id, value) VALUES(1, 0);

创建用来执行 update 语句的存储过程。

create or replace procedure update_demo(count INTEGER)
language plpgsql
as $$
    declare var integer;
    begin
        var := 0;
        while var<count loop  
            UPDATE demo set value=value+1 where id=1;
            var := var+1;
            commit;  --提交事务
            insert into demo(value) VALUES(0);
            rollback; -- 回滚事务     
        end loop;  
    end;
$$;

通过 rollback 语句保证每个 update 操作是独立的事务。（如果不是独立的事务，rollback 语句会回滚 update 操作）。

执行完上述命令后验证一下数据。


athena=>  select proname,prosrc  from pg_proc where proname ='update_demo';
   proname   |                    prosrc
-------------+----------------------------------------------
 update_demo |                                             +
             |     declare var integer;                    +
             |     begin                                   +
             |   var := 0;                                 +
             |   while var<count loop                      +
             |    UPDATE demo set value=value+1 where id=1;+
             |    var := var+1;                            +
             |    commit;  --提交事务                      +
             |    insert into demo(value) VALUES(0);       +
             |    rollback; -- 回滚事务                    +
             |   end loop;                                 +
             |     end;                                    +
             |
(1 row)

athena=> \d demo
                            Table "public.demo"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | bigint  |           | not null | nextval('demo_id_seq'::regclass)
 value  | integer |           |          |
Indexes:
    "demo_pkey" PRIMARY KEY, btree (id)

athena=> select * from demo;
 id | value
----+-------
  1 |     0
(1 row)

Time: 0.618 ms

2. tps 测试

调用存储过程触发 update 执行。

-- 执行 20 次 update
athena=> call update_demo(20);
CALL
Time: 8.376 ms
athena=>  select * from demo;
 id | value
----+-------
  1 |    20
(1 row)

Time: 0.552 ms

-- 执行 1000 次 update
athena=> call update_demo(1000);
CALL
Time: 338.440 ms
athena=>  select * from demo;
 id | value
----+-------
  1 |  1020
(1 row)

Time: 0.575 ms

-- 执行 3000 次 update
athena=> call update_demo(3000);
CALL
Time: 1137.671 ms (00:01.138)
athena=>  select * from demo;
 id | value
----+-------
  1 |  4020
(1 row)

Time: 0.732 ms

发现执行 3000 次 update，耗时 1s，单行 tps 为 3000。

MySQL 和 PostgreSQL 行锁 tps 对比

MySQL：单行 tps 为 10
PostgreSQL：单行 tps 为 3000

后续

1. 咨询大佬

首先咨询了一波 cto，和 cto 的交流如下所示：

初醒 2020-8-19 16:07:21
shulin，请教一个技术问题。

昨天听说了mysql 行锁tps 非常慢，只有10~20。
 
然后我今天测试对比了下 mysql 和 pg 的行锁 tps，发现mysql只有10tps，pg有3000tps。

为什么 pg 的行锁 tps 会这么高呢？

hot potato 2020-8-19 16:53:38
你这个mysql的数据是有些奇怪。我按照你的sql执行了一下，耗时0.05秒，在SSD上。

对于这种简单语句的执行，影响tps的应该主要是commit时磁盘flush的速度。对于机械硬盘一般每秒一百多次。

TPS只有10我觉得怎么样都不太应该的。

初醒 16:55:03
我是用 docker 在 office-cluster-1 跑的mysql

初醒 16:55:34
自动提交是开启的吗？

shulin 16:58:25
开启的
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

hot potato 2020-8-19 16:58:34
office-cluster-1上可能有其他程序干扰
hot potato 2020-8-19 16:58:49
另外office-cluster-1是一个老的pc，机械硬盘

初醒 16:59:15
难怪，可能是机械硬盘的问题

初醒 16:59:32
您是在哪台机器测试的呢？

初醒 17:00:02
我是昨天听一个在有赞支付平台的学长说的，mysql 单行tps 只有20左右。今天就测试了

shulin 17:00:04
我是在自己的pc上跑的

初醒 17:00:15
哦哦

初醒 17:00:21
我也在本地装一个mysql 试试

CTO 的测试结果

查看 Linux 磁盘是否是 SSD 盘


meikai@office-cluster-1:~$ lsblk
NAME   MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda      8:0    0 298.1G  0 disk
├─sda1   8:1    0 290.7G  0 part /
├─sda2   8:2    0     1K  0 part
└─sda5   8:5    0   7.4G  0 part [SWAP]
sr0     11:0    1  1024M  0 rom

# 返回 1：SATA盘
meikai@office-cluster-1:~$ cat /sys/block/sda/queue/rotational
1

2. 进行验证

根据怎么看自己的固态硬盘是哪个盘得知，C 盘是ssd。

本地安装 MySQL 到 C 盘，然后执行之前的测试命令。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

mysql> drop table IF EXISTS demo;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE demo (
    ->   id bigint NOT null AUTO_INCREMENT PRIMARY KEY,
    ->   value int
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into demo(value) VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS update_demo;
    -> CREATE PROCEDURE update_demo(IN count INTEGER)
    -> BEGIN
    -> declare var int;
    -> set var=0;
    -> while var<count do
    -> UPDATE demo set value=value+1 where id=1;
    -> set var=var+1;
    -> end while;
    -> END; $$
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql>  select * from demo;
+----+-------+
| id | value |
+----+-------+
|  1 |     0 |
+----+-------+
1 row in set (0.00 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> call update_demo(20);
Query OK, 1 row affected (0.04 sec)

mysql> call update_demo(100);
Query OK, 1 row affected (0.19 sec)

mysql> call update_demo(500);
Query OK, 1 row affected (0.98 sec)

mysql> call update_demo(600);
Query OK, 1 row affected (1.34 sec)

mysql> call update_demo(500);
Query OK, 1 row affected (1.09 sec)

发现执行 500 次 update，耗时 1s，单行 tps 为 500。

所以之前的测试结果【单行 tps 为 10】应该是机器上其他程序干扰和机械硬盘导致的。

划重点

对于这种简单语句的执行，影响 tps 的应该主要是 commit 时磁盘 flush 的速度。
对于机械硬盘一般每秒一百多次。

行锁 tps 测试

MySQL

1. 环境准备

2. tps 测试

PostgreSQL

1. 环境准备

2. tps 测试

MySQL 和 PostgreSQL 行锁 tps 对比

后续

1. 咨询大佬

2. 进行验证

划重点

参考

猜你喜欢

热点阅读