行鎖 tps 測(cè)試

MySQL

1. 環(huán)境準(zhǔn)備

  • mysql 版本為 8.0.21。
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
  • 創(chuàng)建用來(lái)測(cè)試表 demo。
drop table IF EXISTS demo;
CREATE TABLE demo (
  id bigint NOT null AUTO_INCREMENT PRIMARY KEY,
  value int
);

-- 插入 update 命令需要的數(shù)據(jù)
insert into demo(value) VALUES(0);
  • 創(chuàng)建用來(lái)執(zhí)行 update 語(yǔ)句的存儲(chǔ)過(guò)程。
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 ;

執(zhí)行完上述命令后驗(yàn)證一下數(shù)據(jù)。

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 測(cè)試

調(diào)用存儲(chǔ)過(guò)程觸發(fā) update 執(zhí)行。

-- 執(zhí)行 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)

發(fā)現(xiàn)執(zhí)行 20 次 update，耗時(shí) 2s，單行 tps 為 10。

PostgreSQL

1. 環(huán)境準(zhǔn)備

  • 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)

-- 查詢自動(dòng)提交配置（發(fā)現(xiàn)默認(rèn)是關(guān)閉的）
athena=> \echo :AUTOCOMMIT
off

-- 設(shè)置為自動(dòng)提交模式
athena=> \set AUTOCOMMIT on
athena=> \echo :AUTOCOMMIT
on
  • 創(chuàng)建用來(lái)測(cè)試表 demo。
drop table if exists demo;

-- 創(chuàng)建表之前要先提權(quán)，切換為有權(quán)限的角色。
set role athena;

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


-- 插入 update 命令需要的數(shù)據(jù)
insert into demo(id, value) VALUES(1, 0);
  • 創(chuàng)建用來(lái)執(zhí)行 update 語(yǔ)句的存儲(chǔ)過(guò)程。
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;  --提交事務(wù)
            insert into demo(value) VALUES(0);
            rollback; -- 回滾事務(wù)     
        end loop;  
    end;
$$;

通過(guò) rollback 語(yǔ)句保證每個(gè) update 操作是獨(dú)立的事務(wù)。（如果不是獨(dú)立的事務(wù)，rollback 語(yǔ)句會(huì)回滾 update 操作）。

執(zhí)行完上述命令后驗(yàn)證一下數(shù)據(jù)。


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;  --提交事務(wù)                      +
             |    insert into demo(value) VALUES(0);       +
             |    rollback; -- 回滾事務(wù)                    +
             |   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 測(cè)試

調(diào)用存儲(chǔ)過(guò)程觸發(fā) update 執(zhí)行。

-- 執(zhí)行 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

-- 執(zhí)行 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

-- 執(zhí)行 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

發(fā)現(xiàn)執(zhí)行 3000 次 update，耗時(shí) 1s，單行 tps 為 3000。

MySQL 和 PostgreSQL 行鎖 tps 對(duì)比

  • MySQL：單行 tps 為 10
  • PostgreSQL：單行 tps 為 3000

后續(xù)

1. 咨詢大佬

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

初醒 2020-8-19 16:07:21
shulin，請(qǐng)教一個(gè)技術(shù)問(wèn)題。

昨天聽(tīng)說(shuō)了mysql 行鎖tps 非常慢，只有10~20。
 
然后我今天測(cè)試對(duì)比了下 mysql 和 pg 的行鎖 tps，發(fā)現(xiàn)mysql只有10tps，pg有3000tps。

為什么 pg 的行鎖 tps 會(huì)這么高呢？

hot potato 2020-8-19 16:53:38
你這個(gè)mysql的數(shù)據(jù)是有些奇怪。我按照你的sql執(zhí)行了一下，耗時(shí)0.05秒，在SSD上。

對(duì)于這種簡(jiǎn)單語(yǔ)句的執(zhí)行，影響tps的應(yīng)該主要是commit時(shí)磁盤flush的速度。對(duì)于機(jī)械硬盤一般每秒一百多次。

TPS只有10我覺(jué)得怎么樣都不太應(yīng)該的。

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

初醒 16:55:34
自動(dòng)提交是開(kāi)啟的嗎？

shulin 16:58:25
開(kāi)啟的
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是一個(gè)老的pc，機(jī)械硬盤

初醒 16:59:15
難怪，可能是機(jī)械硬盤的問(wèn)題

初醒 16:59:32
您是在哪臺(tái)機(jī)器測(cè)試的呢？

初醒 17:00:02
我是昨天聽(tīng)一個(gè)在有贊支付平臺(tái)的學(xué)長(zhǎng)說(shuō)的，mysql 單行tps 只有20左右。今天就測(cè)試了

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

初醒 17:00:15
哦哦

初醒 17:00:21
我也在本地裝一個(gè)mysql 試試
CTO 的測(cè)試結(jié)果

查看 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. 進(jìn)行驗(yàn)證

根據(jù) 怎么看自己的固態(tài)硬盤是哪個(gè)盤 得知，C 盤是ssd。

本地安裝 MySQL 到 C 盤，然后執(zhí)行之前的測(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)

發(fā)現(xiàn)執(zhí)行 500 次 update，耗時(shí) 1s，單行 tps 為 500。

所以之前的測(cè)試結(jié)果【單行 tps 為 10】應(yīng)該是機(jī)器上其他程序干擾和機(jī)械硬盤導(dǎo)致的。

劃重點(diǎn)

  • 對(duì)于這種簡(jiǎn)單語(yǔ)句的執(zhí)行，影響 tps 的應(yīng)該主要是 commit 時(shí)磁盤 flush 的速度。
  • 對(duì)于機(jī)械硬盤一般每秒一百多次。

參考

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容