反馈优化结果,单机单表 20 亿数据走索引查询也可以在 100ms 左右返回结果.

[cxh116]

原帖: https://v2ex.com/t/828186

最新结果截图:

基于 hash 字段的 hash 索引第一次查询大概在 100ms 左右,第二次缓存查询只要 0.5 ms 左右.
基于 block_number 的 btree 索引查询,基本在 80ms 左右.

之前之所以单表 20 亿表数据这么慢,不是 PostgreSQL 的问题.

原因如下:

1. Kimsufi 的洋垃圾服务器有个磁盘有坏道,导致慢.走工单沟通了快一周才换的硬盘.
2. 场景错误的使用 TimescaleDB . transactions 基于 hash 字段查询时,时序性并不强.
3. 使用了错误的索引类型, transactions 表的 hash from_address 字段更加适合用 hash 索引.

数据库文件大小在 1.5 TB 左右,精简掉不需要的 Text 字段的话,估计某些性能还会有提升.

洋垃圾主机配置如下:

1. E3-1245 V2 CPU.
2. DH67BL 桌面主板.
3. 三块 HGST HUS726020ALA610 的 16 年的 2TB 机械硬盘. 数据盘组成 raid 0, 读写速度在 250MB/s 左右.
4. 2 根 Kingston DDR3 1333 MHz 8G 桌面内存.

不差钱的话,还是买更好的独服.
之所以选 Kimsufi 洋垃圾独服,主要还是和其它的独服对比,便宜. 23 USD 一个月,能有这配置,对没赚到钱的 Idea 来说,不寒碜.

碰到一个新问题, transactions 表的 from_address 添加 Hash 索引已经 24 小时过去了还没有加完,但 hash 字段 4 小时就添加完了.
不晓得是不是因为 from_address 重复字段太多,导致 hash 重复了. 但 hash 字段因为没重复,所以建的很快.
目前有个 postgres 进程是处于 100% CPU 状态,但通过 iotop 查询,硬盘写又只有 100KB/s .

后面计划:

1. 精简 transactions 与 blocks 表,删除掉 text 字段,只保留自己需要的字段.
2. 尝试 ClickHouse 列数据库.

瞎折腾,没有大数据,创建大数据来玩.

最后,感谢 V 友们的回复.

[sagaxu]

btree 首次 80ms 正常，重复查询 80ms 偏慢了

[displayabc]

所以原评论里边说的，MySQL 亿级数据百毫秒出结果完全可以

[winglight2016]

@haython 然而 mysql 不是不建议单表 2000w 以上的记录数吗？

[littlewing]

@winglight2016 100ms 对于 TP 场景来说，已经太慢太慢太慢了

[sagaxu]

@winglight2016 单表 2000w 的依据是？

内存大小差 10 倍，磁盘随机 IO 性能差 100 倍的两台机器，阈值都是 2000w ？

[est]

早年间 mysql 有个神引擎 tokudb ，你那个 select count() 也可以 8 秒返回。20 亿。

[dayeye2006199]

OP 有心了，还有 follow-up 。原帖上也给 OP 回复了优化方案，看到提升了很多性能，为你感到高兴。

[displayabc]

@littlewing 你要知道 2000W 是怎么出来的，才能知道该不该
有并发的情况肯定不能有这么多数据，一天只访问一次的情况下就无所谓了

[tiiis]

ClickHouse 我们也用了，速度挺不错

[encro]

1 ，关键是索引太长，没必要，只对前面 16 或者 32 位建立索引可能性能更好。
2 ，这个级别，至少要分区了。