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

原帖: 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 友们的回复.