又懒又穷该选用什么 DB 呢？
近期因为 [Arc](https://t.me/arc_null) 先生推荐了 **[zitadel](https://github.com/zitadel/zitadel)**，然后我因为他说到 pgsql 支持有问题，突然想起来这个问题。
当然对于标题的问题，结论肯定是很明显的 pqsql，我们有没有一个合适的数据来证明这一点呢。
考虑到穷，所以没有太多核的机器，考虑到懒，所以一定要即开即用不做任何多余的配置，这就是下面的测试原则。
## 太长不看
先看下面方法论，确保理解场景。
越大越好：
```
MySQL 8.0.32 QPS 8979.31
MariaDB 10.5.22 QPS 25788.07 (287% mysql)
PostgreSQL 15.3 QPS 35383.99 (394% mysql)
```
越小越好（此数字由人工智能采样估测）：
| DB | usr time% | sys time% | io time% | ram(incl. buffer) |
| --- | --- | --- | --- | --- |
| MySQL | 12 | 5 | 30 | 970M |
| MariaDB | 27 | 14 | 18 | 920M |
| Postgre | 30 | 25 | 15 | 710M |
如何理解呢？如果不能理解的话不如加入 pgsql 夸夸人。
## 方法论
参考 [这篇 MariaDB 声称自己吊打 MySQL 的文章](https://mariadb.com/resources/blog/benchmark-mariadb-vs-mysql-on-commodity-cloud-hardware/)，但我选择了默认参数配置的 sysbench oltp_read_write 脚本，单表 2000000 条，4 线程，20 分钟测试。
具体 workload 为，每 transaction 包含下面的内容（使用的 between 区间为 100）：
- 10 个单点 SELECT `SELECT c FROM t WHERE id=?`
- 1 个区间 SELECT `SELECT c FROM t WHERE id BETWEEN ? AND ?`
- 1 个区间求和 `SELECT SUM(k) FROM t WHERE id BETWEEN ? AND ?`
- 1 个区间选出并排序 `SELECT c FROM t WHERE id BETWEEN ? AND ? ORDER BY c`
- 1 个区间选出并去重 `SELECT DISTINCT c FROM t WHERE id BETWEEN ? AND ? ORDER BY c`
- 1 个索引字段更新 `UPDATE t SET k=k+1 WHERE id=?`
- 1 个非索引字段更新 `UPDATE t SET c=? WHERE id=?`
- 1 个删除 `DELETE FROM t WHERE id=?`
- 1 个插入 `INSERT INTO t (id, k, c, pad) VALUES (?, ?, ?, ?)`
很明显，这和 MariaDB 在上面的文章中使用的 9R1W workload 看起来并不是很一致，因为他设置了只保留 9 个单点 SELECT 和 1 个更新。
至于看这个 workload 他能体现正确的使用环境吗，我的意见是，说不定呢，奇奇怪怪的系统还不是满天飞。
为了使用 `mariadb-devel` 编译 sysbench，你可能需要应用[这个 PR](https://github.com/akopytov/sysbench/pull/506/files#diff-6b6db70bc494d49f8745ce5385bb37f1d9c55efa64be8acbfeb094ef41eb011c)。
## 测试环境
为了控制测试环境，减少 VM 问题，本测试也选择跑在 EC2 上，具体配置如下：
```
model: m7a.xlarge (4x 9R14 vcores, 16GB ram)
region: us-west-2
storage: 50GB gp3 EBS (16000iops)
system: AlmaLinux 9.3 (ami-024c6ca40c04ee646)
kernel: 5.14.0-362.8.1.el9_3.x86_64
```
- MySQL 根据 [这篇教程](https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/9/html/configuring_and_using_database_servers/index) 配置。
- MariaDB 根据 [这篇教程](https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/9/html/configuring_and_using_database_servers/index) 配置。
- PostgreSQL 根据 [这篇教程](https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/9/html/configuring_and_using_database_servers/index) 配置。
测试中使用三个 EC2 并行，并且运行了 YABS 保证其性能基本一致：
```
================= vm1 =================
fio Disk Speed Tests (Mixed R/W 50/50) (Partition /dev/nvme0n1p4):
---------------------------------
Block Size | 4k (IOPS) | 64k (IOPS)
------ | --- ---- | ---- ----
Read | 33.03 MB/s (8.2k) | 290.99 MB/s (4.5k)
Write | 33.09 MB/s (8.2k) | 292.52 MB/s (4.5k)
Total | 66.12 MB/s (16.5k) | 583.51 MB/s (9.1k)
| |
Block Size | 512k (IOPS) | 1m (IOPS)
------ | --- ---- | ---- ----
Read | 258.72 MB/s (505) | 257.16 MB/s (251)
Write | 272.47 MB/s (532) | 274.29 MB/s (267)
Total | 531.19 MB/s (1.0k) | 531.46 MB/s (518)
Geekbench 6 Benchmark Test:
---------------------------------
Test | Value
|
Single Core | 2083
Multi Core | 6710
Full Test | https://browser.geekbench.com/v6/cpu/3919365
================= vm2 =================
fio Disk Speed Tests (Mixed R/W 50/50) (Partition /dev/nvme0n1p4):
---------------------------------
Block Size | 4k (IOPS) | 64k (IOPS)
------ | --- ---- | ---- ----
Read | 33.03 MB/s (8.2k) | 290.99 MB/s (4.5k)
Write | 33.08 MB/s (8.2k) | 292.52 MB/s (4.5k)
Total | 66.12 MB/s (16.5k) | 583.51 MB/s (9.1k)
| |
Block Size | 512k (IOPS) | 1m (IOPS)
------ | --- ---- | ---- ----
Read | 258.55 MB/s (504) | 257.07 MB/s (251)
Write | 272.29 MB/s (531) | 274.19 MB/s (267)
Total | 530.85 MB/s (1.0k) | 531.26 MB/s (518)
Geekbench 6 Benchmark Test:
---------------------------------
Test | Value
|
Single Core | 2103
Multi Core | 6822
Full Test | https://browser.geekbench.com/v6/cpu/3919595
================= vm3 =================
fio Disk Speed Tests (Mixed R/W 50/50) (Partition /dev/nvme0n1p4):
---------------------------------
Block Size | 4k (IOPS) | 64k (IOPS)
------ | --- ---- | ---- ----
Read | 33.03 MB/s (8.2k) | 290.95 MB/s (4.5k)
Write | 33.08 MB/s (8.2k) | 292.48 MB/s (4.5k)
Total | 66.12 MB/s (16.5k) | 583.43 MB/s (9.1k)
| |
Block Size | 512k (IOPS) | 1m (IOPS)
------ | --- ---- | ---- ----
Read | 258.75 MB/s (505) | 257.26 MB/s (251)
Write | 272.50 MB/s (532) | 274.39 MB/s (267)
Total | 531.26 MB/s (1.0k) | 531.66 MB/s (518)
Geekbench 6 Benchmark Test:
---------------------------------
Test | Value
|
Single Core | 2088
Multi Core | 6726
Full Test | https://browser.geekbench.com/v6/cpu/3919612
```
## 没有人会看的原始数据
### MySQL
```
mysql> status
--------------
mysql Ver 8.0.32 for Linux on x86_64 (Source distribution)
Connection id: 36
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.32 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 41 min 0 sec
Threads: 2 Questions: 13237920 Slow queries: 0 Opens: 228 Flush tables: 3 Open tables: 135 Queries per second avg: 5381.268
--------------
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 4
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 7542682
write: 2155052
other: 1077526
total: 10775260
transactions: 538763 (448.97 per sec.)
queries: 10775260 (8979.31 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 448.9654
time elapsed: 1200.0100s
total number of events: 538763
Latency (ms):
min: 2.62
avg: 8.91
max: 218.70
95th percentile: 17.63
sum: 4799440.76
Threads fairness:
events (avg/stddev): 134690.7500/75.59
execution time (avg/stddev): 1199.8602/0.01
```
### MariaDB
```
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.5.22-MariaDB, for Linux (x86_64) using EditLine wrapper
Connection id: 63
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.22-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 35 min 49 sec
Threads: 4 Questions: 33003264 Slow queries: 0 Opens: 44 Open tables: 32 Queries per second avg: 15357.498
MariaDB [bench]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_unicode_ci |
+--------------------------+----------------------+
1 row in set (0.000 sec)
[root@ip-172-31-2-103 sysbench-master]# sysbench --time=1200 --db-driver=mysql --table_size=2000000 --threads=4 --mysql-user=super --mysql-password=pw --mysql_storage_engine=innodb --mysql-host=127.0.0.1 --mysql-db=bench oltp_read_write run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 4
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 21662018
write: 6189148
other: 3094574
total: 30945740
transactions: 1547287 (1289.40 per sec.)
queries: 30945740 (25788.07 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 1289.4037
time elapsed: 1200.0020s
total number of events: 1547287
Latency (ms):
min: 1.30
avg: 3.10
max: 164.06
95th percentile: 7.98
sum: 4798288.37
Threads fairness:
events (avg/stddev): 386821.7500/214.04
execution time (avg/stddev): 1199.5721/0.01
```
### PostgreSQL
```
postgres=# SELECT version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 15.3 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-4), 64-bit
postgres=# show server_encoding;
server_encoding
-----------------
UTF8
[postgres@ip-172-31-14-83 ~]$ sysbench --time=1200 --db-driver=pgsql --table_size=2000000 --threads=4 --pgsql-host=/var/run/postgresql --pgsql-user=postgres --pgsql-db=benchr oltp_read_write run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 4
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 29722672
write: 8492181
other: 4246101
total: 42460954
transactions: 2123045 (1769.20 per sec.)
queries: 42460954 (35383.99 per sec.)
ignored errors: 3 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 1769.1973
time elapsed: 1200.0046s
total number of events: 2123045
Latency (ms):
min: 1.07
avg: 2.26
max: 79.08
95th percentile: 2.97
sum: 4797950.37
Threads fairness:
events (avg/stddev): 530761.2500/419.16
execution time (avg/stddev): 1199.4876/0.01
```
评论
发表评论