Appearance
数据库性能和存储测试
是不是该起一个更符合流量要求的名字，比如“上云就是谎言！开发板装上 postgres 都比 4 万的云服务器还强”，第二段就写“压缩炸弹，4G 压缩包解压出 1T 数据”
注：
- 根据后面的测试结果，pg 的读写 qps 大概有 10000 左右。按照网上同类数据库的宣称性能和服务器规格作对比，8c16g，硬盘 2T 的腾讯云 mysql 需要 4W2 一年
- 根据测试的结果来看，似乎标题党也没有太大的意义。可能还得是简明且吸引人好。这也正合我意
目前有一个 RK3588 的开发板准备替代树莓派做家庭内部的控制节点，由于性能和 IO 达到可用水平，因此正好对现有环境做改造和测试。由于新节点上跑的程序种类很多，因此采用 Docker 进行环境隔离
- 硬件：CPU 是A76@2.3GHz(cpuz 单核分数 220)，内存是 32G LPDDR4 4224MHz，硬盘是 2TB P41(实际速率 3.0x4)，4K IOPs 750W
- 软件：Armbian，内核为瑞芯微的 5.10，数据库通过 Docker 启动，使用卷映射做持久化管理
目前准备选取两个数据库做测试 PostgreSQL(简称 pg) 和 Mysql(简称 mysql)，其他的数据库不是很满足官方发版+ARM+Docker 的需求。剩下的找一些 olap 的数据库测试列压缩
预计主要的测试场景如下
- 爬虫结果和网页数据存储(1000W 行，每行 5 个页面，HTML 存储至行中)
- 大数据量的论坛数据采集(单表过亿)
- 作为传感器采集数据的后端(单表百万)
- 大数据量数据做关联分析(单表 16 亿)
单表 5 亿数据的测试
随机生成了 5 亿的用户昵称和电话，在不挂载 volume 和进行优化的情况下做一个基础的定量测试。模拟把一个大数据量的分析文件导入到数据库后进行简单 olap 操作
未做优化的情况下的测试
模拟数据库新手，在不优化默认配置文件时做的操作
- 插入：通过 Navicat 的图形化界面进行导入
- 查询：通过前端进行查询
- 数量统计:
SELECT count(*) FROM tmp - 唯一查询:
SELECT * FROM tmp WHERE phone='xxx' - 范围查询:
SELECT * FROM tmp WHERE phone BETWEEN 'xxx' AND 'xxx'范围为 百万 - 模糊查询:
SELECT * FROM tmp WHERE phone LIKE 'xxx%'%匹配 百万 - 全量分组:
SELECT phone, count(uid) FROM tmp GROUP BY phone似乎太大了 - 简单分组:
SELECT phone, count(uid) FROM tmp WHERE phone BETWEEN 'xxx' AND 'xxx' GROUP BY phone范围为百万 - 简单排序:
SELECT phone FROM tmp ORDER BY phone DESC limit 1000
- 数量统计:
在直接的插入项目上，pg 只用了一小时就完成了 5 亿数据的插入，但 mysql 用了一小时 33 分钟。原始大小 11G 的 txt 插入后的大小均为 39G
超过 5 分钟的都是 N/A，等不了了
| 项目 | Mysql | Postgre | 备注 |
|---|---|---|---|
| 插入 | 89778 | 138441 | |
| 数量统计 | 44 | 26 | |
| 唯一查询 | N/A | 23.9 | |
| 模糊查询 | N/A | 23.9 |
简单新建索引
测一半我就知道 mysql 性能有问题了，没做索引的时候完全查不到。新建索引时 pg 的速度也快不少以下测试结果为在 phone 列新建索引之后，索引均为 BTREE，其余默认
在使用默认索引的情况下，pg 的性能诡异的被 mysql 反超。我优化了 pg 的索引类型，把 text 类型换成了 varchar 还是不行
| 项目 | Mysql | Postgres | 备注 |
|---|---|---|---|
| 数量统计 | 43.564 | 17.683 | |
| 唯一查询 | 0.007 | 0.004 | |
| 范围查询 | 0.005 | 0.128 | |
| 模糊查询 | 0.095 | 21.741 | ??? |
| 全量分组 | N/A | N/A | 太大了 |
| 简单分组 | 0.148 | 3.439 | |
| 简单排序 | 0.008 | 0.021 |
但是这个似乎和返回结果的大小也有关系，添加 limit=1000 后的结果如下。虽然 pg 在模糊查询上还是打不过 mysql，但是性能确实很不错了
尤其是 pg 能比较稳定的查出来，我尝试对一个号段(一亿范围)进行聚合，然后选择了对应用户 id 数量最多的前 1000 名。pg 用了 70 秒就返回来结果，mysql 则超时了。后面优化了之后对全量数据进行索引后 722 秒能返回结果，不过看资源应该不优化也行
| 项目 | Mysql | Postgres | 备注 |
|---|---|---|---|
| 范围查询 | 0.015 | 0.006 | |
| 模糊查询 | 0.013 | 0.395 | pg 100 是 0.1 |
| 简单分组 | 0.22 | 0.014 |
优化后的性能测试
需要做 docker 层面和数据库层面的两部分优化，具体如下
docker 由于不想做过多优化，目前只配置了代理，因此先不改共享内存大小了
bash
# pg由于是多进程模型，通讯要用到 share_mem 共享内存，但是docker默认的共享内存大小为64Mmysql 抄了一个配置就完事了
bash
innodb_buffer_pool_size = 8G
tmp_table_size = 16M
max_heap_table_size = 16M
max_allowed_packet = 16M
table_open_cache = 2048
innodb_thread_concurrency = 4
bulk_insert_buffer_size = 16Mpg 我直接用 pgtune 生成了一个配置
bash
max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 16MB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2sysbench 性能测试
sysbench 是一个非常通用的性能测试软件，支持对 mysql 和 pg 做测试。目前自带了如下的数据库测试类型，准备做个简单的基准测试
bash
oltp_read_only 只读测试
oltp_read_write 读写测试
oltp_insert 简单插入测试
bulk_insert 批量插入测试
oltp_delete delete删除测试
oltp_update_index 带索引的更新测试
oltp_update_non_index 不带索引的更新测试
oltp_point_select 等值查询测试
select_random_points 随机等值查询测试
select_random_ranges 随机范围查询测试考虑到预计的数据库大小规模和实际场景，考虑使用另一台 5900x 的机器上用 16 线程(WEB 服务器是 8 核的，进程数是 2N)对数据库进行压力测试
- 数据量：原定计划单表 2500W，由于单线程写入性能实在是太慢了。改为默认
- 测试场景
- 批量导入：bulk_insert 批量插入测试
- 数据爬取：oltp_insert 简单插入测试
- 正常负载：oltp_read_write 读写测试
- 数据更新：oltp_update_index 带索引的更新测试
- 已知索引查询：oltp_point_select 等值查询测试
- 范围查询：select_random_ranges 随机范围查询测试
通用脚本内容大致如下，还需要补充数据库链接认证和测试脚本专用的参数。测试分为 prepare，run，clearnup 三步进行
bash
sysbench --threads=16 --time=300 --report-interval=3 /usr/share/sysbench/测试脚本名称 操作tps 单位是秒，由于除了 bulk_insert 和 oltp_read_write 外其他项目都的 tps 和 qps 一样，因此省略。p95 是延迟，单位是毫秒
可以看到 pg 的性能全方面超过了 mysql，基本上最少也是 30% 的性能差距。插入和更新上更是达到了三倍之多。当然，这个性能差距确实是比不过合理的索引和优化的，即使是 mysql 的性能其实也能撑起一个日活百万的项目或者是充当 千台规模 hadoop 集群的元数据库了。pg 更多的是强在未经索引优化的前提下能够稳定的查询出数据
| 项目 | mysql-tps | mysql-p95 | pg-tps | pg-p95 | 备注 |
|---|---|---|---|---|---|
| bulk_insert | 335886.53 | 0 | 776847.14 | 0 | |
| oltp_insert | 1558 | 20.37 | 4335.97 | 5.00 | |
| oltp_read_write | 457.89 | 65.65 | 635.63 | 32.53 | |
| oltp_update_index | 1284.66 | 24.83 | 4771.73 | 6.43 | |
| oltp_point_select | 23829.64 | 0.72 | 27911.99 | 0.67 | |
| select_random_ranges | 17552.90 | 1.30 | 22566.60 | 0.83 |
实际场景测试
实际场景中有数据库下有两张表，分别是 100W 级别的用户信息(account)和 500W 级别的数据记录(record)
查询性能测试
select status, count(*) as c from record WHERE update_time BETWEEN '2024-01-01 00:00:00' and '2025-01-01 00:00:00' GROUP BY status order by c desc实际测试挺难绷的，本地 pg 和 mysql 的运行时间分别是 0.1s 和 0.75s，在服务器上的时间是 45s
云服务器是腾讯云的轻量，4c8g 180g ssd。cpu 是至强可拓展二代，ssd 看官方的手册 IOPs 只有 6000，吞吐量只有 150MB/s，和本地 750w IOPs，3200 MiB/s 的吞吐量比差了几个数量级
准备根据以下的实际业务请求编写一些简单的测试条目，在开发机上进行查询测试
- OLTP：单线程请求，数据量为 10W，超时时间为 60s
- 点查用户：精确查询用户昵称
select nickname from account where nickname = '$1$' - 匹配用户：模糊查询用户昵称和历史昵称
select nickname from account where nickname like '%$1$%' union select nickname from account where former_name is not null and former_name like '%$1$%' - 点查记录：根据记录 id 点查询数据记录
select id from record where id='$1$' - 列出记录：根据用户 id 或用户昵称 查询所有的数据记录
select nickname from record where nickname='$1$' order by update_time desc limit 1
- 点查用户：精确查询用户昵称
- OLAP：在 navicat 控制台请求并记录时间
- 数据量：查询一段时间内的数据记录数量
select count(*) from record where update_time between '2023-01-01 00:00:00' and '2024-01-01 00:00:00' - 用户聚合：根据用户特征进行统计（同名用户）
select nickname, count(distinct rockstar_id) as c from record where update_time between '2023-01-01 00:00:00' and '2024-01-01 00:00:00' group by nickname order by c desc - 数据大小：查询一段时间内的数据大小
select sum(bit_length(raw))/8/1024/1024/1024 as c from record where update_time between '2024-01-01 00:00:00' and '2025-01-01 00:00:00' - 状态统计：聚合一段时间内的数据记录，查询各个状态码的数量
select status, count(*) as c from record WHERE update_time BETWEEN '2023-01-01 00:00:00' and '2024-01-01 00:00:00' GROUP BY status order by c desc
- 数据量：查询一段时间内的数据记录数量
pg 基本只有两种情况，小赢和赢麻了。不过我也没想到匹配用户和列出记录的这两个比较常见的操作居然 mysql 性能居然这么低，匹配用户一般不会被触发，mysql 列出记录的性能这么低，可能是索引同步的时候存在问题。生产数据库的是有建复合索引的，实际速度比这个高不少
| 项目(tps) | mysql | postgres |
|---|---|---|
| 点查用户 | 3204.1 | 4100.1 |
| 匹配用户 | 2.92 | 8.8917 |
| 点查记录 | 3734.2 | 4320.1 |
| 列出记录 | 0.95873 | 2347.6 |
| 项目(s) | mysql | postgres |
|---|---|---|
| 数据量 | 2.8596 | 0.18104 |
| 用户聚合 | 3.584 | 3.1951 |
| 数据大小 | 102.46 | 0.11761 |
| 状态统计 | 1.9464 | 0.35143 |
存储和运维能力分析
由于数据实在是太多了，现在单 mysql 表就达到了 97G 的大小，pg 更是高达 121g。这个大小对本地服务器来说完全不值一提，但是对于云服务器来说这个大小真的是要了亲命了(能装得下的SSD云盘每年 1500)。所以需要考虑一下如何使用更高压缩率的方案，理论上这种大量重复的文本的数据压缩率能做到 99% 以上
做了个简单测试，zlib 压缩前原始数据的大小为 16754MB，压缩后大小为 1300MB。压缩率约为 92.2%。zlib 压缩算法经过测试最高等级压缩相比默认等级提升幅度不到 1%。因此不做压缩等级提升方面的考虑
mysql 复合压缩方案：查询程序将多个网页数据打包为一个 json 后使用 python 的 zlib 库进行数据压缩，上传时使用 base64 将二进制压缩数据编码成字符串，然后插入到 mysql 的 blob 字段中
把 html 数据转移到元数据表里其实是一个过于凑合的解决办法，尤其是 base64 把体积凭空增加了 33%，blob 字段使用的也没有意义。只能说当时的考虑不够到位
可能需要考虑优化查询文件的数据清洗方案，原有方案没考虑到后期更新，文件大小居然从 100k 变成了 600k，等于现在存储了原定 1200W 的数据，怪不得我觉得原本预计能撑三年的方案怎么这么快就不行了
目前服务器续费到了 2025 年 8 月，年底或者明年 618 的时候需要关注下新购服务器了，现在的服务器续费打 4 折都得 1700，实在是吃不消。新服务器的硬盘肯定是没有现在 180G 这么大，可能也就是 70G，预算拉满也就 90G。把剩余空间极致压缩也就是 50/70G 的 blob 存储空间，这点空间怎么存下最少 700W 的 blob 文件将是一个巨大的挑战
运维方案
经过检查，pg 存在如下问题。好在也有定期运维机制，可以自动化清理
- 由于 mvcc 机制，pg 的表在删改后会留下死元祖。这就会导致频繁增删改查的表实际占用的空间会增大不少（表膨胀），必须执行 vacuum full 锁表（无法读写）进行数据回收。好在查询程序基本没有删改
- 由于 pg 的事务设计，需要定期执行 vacuum 来重置 XID，不然超过 42 亿后就存在问题。这个问题不大，请求量没那么大，也不需要 vacuum full
hydra、timescaledb、clickhouse 基本都存在更新数据困难，删除数据后需要手动清理回收表大小的问题。而且最麻烦的是这些对于最低 2c4g 的云服务器来说太重了，何况还需要再加上一个 mysql/pg 等数据库用于队列查询
mysql 也存在类似的问题，尤其是 mysql 的收缩表是采用新建一个表的方法来收缩，这就需要两倍的查询程序的存储空间，这个非常的吓人
存储方案
由于上面 mysql 的测试结果实在是太过于稀烂了，因此将原定的测试 mysql 存储大小的方案完全放弃，只作为数据库整库压缩的存储基准参考
这里简单介绍下存储方案的不同
- 行存储：传统数据库的存储方案，按照行来进行存储。优点是查询速度指定行时非常快，缺点是行压缩的效率低，大数据量聚合困难。mysql、pg 默认就是这样
- 列存储：把所有数据按列分开存储和查询。优点是压缩时整行压缩效率高，支持的存储容量大，缺点是查询速度一条数据也要找完一列数据，慢不少。duckdb、hydra，tsdb 基本属于这一类
- 对象存储：这个是个额外对比对象，本质是把文件存储到一个类似云盘的地方。优点是可以使用便宜的对象存储或者自建，缺点增加了复杂度，存取也要额外的流量
按数据库维度来说有这些方案可以选择，其中 minio 和本地存储方式一致、pg 的插件部署困难且不如 clickhouse 压缩率高，因此跳过测试
- mysql
- mysql 复合压缩：行压缩 + zlib 压缩数据
- pg
- pg 默认：自带 toast 压缩，似乎就是默认结果，效果不是很好
- pg 列压缩：tsdb，需要安装插件，而且不是纯列数据库，效率不太行
- pg 列压缩：hydra，需要安装插件，文件删除很麻烦
- 杂项数据库
- duckdb：专用压缩列方案，无法压缩 html
- clickhouse：zstd，数据更新有延迟
- 其他存储
- minio：本地对象存储数据库
- 本地文件：纯本地存储
数据压缩测试
在调试数据库的同时我也在研究如何优化新数据的存储，如果抛弃掉无损压缩，那么可以把单个文件的体积压缩到原有的 30%。同时可以考虑通过钞能力把数据库的存储空间从 25G 提升到 50G。这个就是盘外优化
除了完全不考虑实用的合并手段外，实际上压缩率最高的算法也就比原方法多存储一倍的数据。但是通过盘外优化能提升 6.5 倍以上的存储量
测试数据时从生产数据库中导出的一万行的压缩后的二进制文件，期望的目标是能在数据膨胀到千万级别的时候仍然能将数据存储到 50G SSD 的云服务器中，实际数据库的可用大小大概在 30G 左右，二进制数据的存储空间大概是 25G。需要以此为依据评估存储方案
一般来说除非是列存储，不然其余的存储方案基本上都是使用吞吐量而非高压优化压缩算法，因此先对数据做预压缩就非常重要了。文件和数据库的压缩算法我都测试了最高压缩版本，但是基本只会带来性能大幅下降，存储空间基本保持不变
经过测试，相比 zlib 压缩并使用 base64 编码的原方案，更换为纯二进制的 xz 压缩方案是一个非常无痛且高效的临时解决方案。尽管压缩的性能降低了不少，但是存储密度仍然有可观(70%-78%)的提升。xz 的 lzma 库的算法已经是极致压缩的版本了，无法在压缩效率上有进一步的提升了。同时妄图二次压缩进行叠叠乐的想法也是失败的，合并二压的提升更多的是来源于重复数据的增加
一般来说，重复文件的增加能够很好的提升文件压缩效率，而且列数据库的列存储模式也非常适合大量相同数据的合并压缩。可是也不是没有例外的，比如说 duckdb 使用了高吞吐量优化的简单算法，对 html 这种数据的压缩效果堪称悲剧。虽然 Clickhouse 的压缩效率和算法在数据库中已经接近极限了(列压缩+zstd)，但因为原始文本集合已经够大了，所以压缩率相比单文件压缩方式里最优的 lzma(6) 方案只有小幅提升，完全赶不上本地大量文本文件合并压缩的效果
lzma(3)应该是最优的通用压缩方案了，我对 lzma1-6 都做了测试，1-2 压缩速度最多提升到 105，效率提升太低，4-5 效率沿腰斩的同时提升为负。6-9 等级的效率直接沿脚踝斩断了，提升幅度也非常小
ch 是 click house 的缩写，pg 是 postgresql 的缩写
| 压缩方案名称 | 占用空间(MiB) | 压缩率 | 千万行大小(GiB) | 可存储行数(W) | 优化后行数(W) |
|---|---|---|---|---|---|
| 文本 | 5715 | 0.00% | 5581.05 | 4.48 | 30 |
| zlib(6)-base64 | 434 | 92.41% | 423.83 | 58.99 | 393 |
| zlib(6) | 326 | 94.30% | 318.36 | 78.53 | 524 |
| zlib(6)-lzma(6) | 326 | 94.30% | 318.36 | 78.53 | 524 |
| lzma(3) 90tps | 255 | 95.54% | 249.02 | 100.39 | 66 |
| lzma(6) 15tps | 244 | 95.73% | 238.28 | 104.92 | 699 |
| DuckDB 文本 | 25000+ | -337.45% | 24411.06 | 1.02 | 7 |
| pg zlib-lz4 | 343 | 94.00% | 334.96 | 76.64 | 498 |
| ch 文本-ZSTD(1) | 356 | 93.76% | 348.02 | 71.84 | 479 |
| ch 文本-ZSTD(16) | 212 | 96.29% | 207.15 | 120.69 | 805 |
| ch xz(9)-ZSTD(16) | 239 | 95.82% | 233.40 | 107.11 | 714 |
合并二压和原文件合并压缩更多的是图一乐的探索性行为，尽管将多份文件进行复合压缩的效率惊人的高，甚至可以说已经非常接近期望的了。但是带来的百倍甚至是万倍的 IO 惩罚(因为要一起读写)和文件管理使得无法用于在线应用。可惜这个方案已经无限逼近理想化方案，存储效率极其惊人(换算下来 25G 存储了 5.54T 的文本，部分有损压缩后是 18.6T)，也许以后有冷数据存储需求可以使用这个方案
| 压缩方案名称 | 占用空间(MiB) | 压缩率 | 千万行大小(GiB) | 可存储行数(W) | 优化后行数(W) |
|---|---|---|---|---|---|
| zib(6)-lzma-合并二压 | 181 | 96.83% | 176.76 | 141.44 | 943 |
| xz(9)-文本合并压缩 | 29.5 | 99.48% | 28.81 | 867.80 | 5785 |
| 理想化方案 | 25.5 | 99.55% | 24.90 | 1003.92 | 6693 |
实际方案评估
以同样原始方案下存储方案进行比对。mysql 是默认行压缩，pg 是所有字符串的表都配置了 lz4 压缩。由于数据删除和存储组织形式的不同，pg 和 mysql 的某些数据是没有的。非物理大小统计基本上都是使用 length 函数
在删除冗余的 record 数据后，pg 的 toast 从 113G 下降到了 96G，少了 17G。统计查询结果列的大小直接飞速下降到 6G。删除掉整个查询结果后表大小就只有 6.9G 了，侧面说明了奖章的占用空间确实很大
整体来看，pg 和 mysql 差距最大的二进制列的大小差了大约四分之一，类似做了一次 base64 编码一样，这个数据非常的奇怪。pg 的效率被打了个巨大的折扣
理论上二进制文件的大小为 91G。但是 mysql 的大小不知道为什么有大幅缩压，pg 反而有小幅增加，难不成是因为压缩算法不同所以可以叠叠乐？pg 解除 base64 编码后的大小也就是 76G
| 方案 | mysql | postgres |
|---|---|---|
| 原始表大小 | 97G | 123G |
| toast 大小 | N/A | 113G |
| 查询结果列大小 | 76G | 48G |
| 二进制列大小 | 91G | 91G |
| 删除冗余查询记录表大小 | 82G | 104G |
| 删除 raw 文件后表大小 | 8G | 7G |
| 删除 record 文件后表大小 | 381M | 446M |
| 存储大小 | mysql | postgres |
|---|---|---|
| 总大小 | 97G | 123G |
| 查询结果列 | 23G | 25G |
| 冗余结果列 | 15G | 19G |
| 二进制列 | 74G | 97G |
| 剩余数据 | 381M | 446M |
经过数天的重新压缩和打包测试。即使是使用了原定的最终方案并且删除了冗余的查询记录数据，整个数据库的大小也接近了 44G。折合压缩幅度 35.7%。和 mysql 原始的 97G 的大小比，实际上的压缩幅度也就 45%，突出一个费劲。但是 mysql 数据库的大小也有 40G，折合压缩幅度也就 41%。达不到理论上格式提升带来的两倍体积缩小再乘上三倍的有损压缩带来的 6 倍压缩率提升(16.7%)
对于纯最新版本的数据而言，mysql 的大小为 35G，pg 的大小为 33G。在 50G 的存储空间下的可存储行数非常接近，270-280W 行。考虑到性能得到满足且出于运维省事的考虑，其实使用 mysql 也不是不行
尤其是目前新服务器的最优方案里，要么是续费现有的服务器、要么是花大价钱买 350G 的大盘鸡，存储压力反而没那么大了。目前还是先考虑以续费为主，能扣一点是一点
这段时间也在研究新服务器的选型，目前看有续费和新购两种选项
- 新购首单优惠服务器 180G 硬盘，三年价格 2150，年化 1T 价格为 4077
- 周年庆续费现有服务器 180G 硬盘，三年价格 1323，年化 1T 价格为 2509
- 新购新老同享服务器 90G 硬盘，三年价格 1404，年化 1T 价格为 6846
- 新购存储型服务器服务器 350G 硬盘，三年价格 3213，年化 1T 价格为 3133
评估
出于性能、和方便管理的考虑，把 mysql 换成 pg 或许是个好主意。甚至如果更考虑文件压缩率的话，mysql 都不用换，优化后的 sql 足够满足查询程序用户拓展 10 倍，查询量增加两倍。原有的数据库+压缩文件/对象存储的方案选项证明是没有问题的
如果处于统一也是最正规的开发技术栈考虑，把小文件存储到 minio 然后 pg 中存地址才是正规且可拓展的方式。但是这样又会增加文件同步的复杂度并降低数据一致性，也是蛮头疼的。尤其是目前机器还有一年左右的时间，不是很想折腾太多