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 的速度。 - 对于机械硬盘一般每秒一百多次。
网友评论