V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
cxh116
V2EX  ›  PostgreSQL

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

  •  
  •   cxh116 · 2022-03-03 16:47:15 +08:00 · 3337 次点击
    这是一个创建于 979 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    10 条回复    2022-03-05 20:10:04 +08:00
    sagaxu
        1
    sagaxu  
       2022-03-03 17:39:49 +08:00 via Android
    btree 首次 80ms 正常,重复查询 80ms 偏慢了
    haython
        2
    haython  
       2022-03-03 19:52:59 +08:00
    所以原评论里边说的,MySQL 亿级数据百毫秒出结果完全可以
    winglight2016
        3
    winglight2016  
       2022-03-03 20:19:34 +08:00
    @haython 然而 mysql 不是不建议单表 2000w 以上的记录数吗?
    littlewing
        4
    littlewing  
       2022-03-03 20:46:19 +08:00
    @winglight2016 100ms 对于 TP 场景来说,已经太慢太慢太慢了
    sagaxu
        5
    sagaxu  
       2022-03-03 20:51:31 +08:00 via Android
    @winglight2016 单表 2000w 的依据是?

    内存大小差 10 倍,磁盘随机 IO 性能差 100 倍的两台机器,阈值都是 2000w ?
    est
        6
    est  
       2022-03-03 21:21:18 +08:00
    早年间 mysql 有个神引擎 tokudb ,你那个 select count() 也可以 8 秒返回。20 亿。
    dayeye2006199
        7
    dayeye2006199  
       2022-03-04 02:58:44 +08:00 via Android
    OP 有心了,还有 follow-up 。原帖上也给 OP 回复了优化方案,看到提升了很多性能,为你感到高兴。
    haython
        8
    haython  
       2022-03-04 10:31:21 +08:00
    @littlewing 你要知道 2000W 是怎么出来的,才能知道该不该
    有并发的情况肯定不能有这么多数据,一天只访问一次的情况下就无所谓了
    tiiis
        9
    tiiis  
       2022-03-04 12:47:33 +08:00 via iPhone
    ClickHouse 我们也用了,速度挺不错
    encro
        10
    encro  
       2022-03-05 20:10:04 +08:00   ❤️ 1
    1 ,关键是索引太长,没必要,只对前面 16 或者 32 位建立索引可能性能更好。
    2 ,这个级别,至少要分区了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1335 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 17:45 · PVG 01:45 · LAX 09:45 · JFK 12:45
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.