PG类数据库优化之稳定关键SQL执行速度

白鳝的洞穴 2023-05-10

658

大家用Oracle数据库的时候，根本不需要过多的去考虑数据库物理结构以及OS物理结构方面的优化。因为Oracle把一切都帮我们做好了，只需要交给Oracle足够容量，足够能力的物理资源就一切OK了。不过作为三十年前就开始使用Oracle数据库的我们这一代DBA，还是经历过很多这样的优化案例的。在那个年代，曾经通过底层存储的重新条带化设计，让一套IO负载极高的数据库系统的批处理作业性能提高数倍。

现在我们如果不使用Oracle数据库了，我们要把应用从Oracle迁移到国产数据库上了，那么这些老手艺可能又有些作用了。不过与那个时代不同的是，现在硬件的能力已经百倍的提升了，存储系统的全局IO打散能力也成为了标配，因此再也不需要像我在二十多年前那样为了一点点的IOPS去精打细算了。

今天这个话题虽然我已经把讨论范围缩小到了物理结构优化上，不过还是太大，我不大可能在一个一千多字的文章里把所有的问题都讲清楚，因此我们今天还是缩小到一个比较小的话题上吧，那就是如何通过底层物理结构的优化让关键业务系统在PG类数据库上平稳运行。这里讲的是PG类数据，除了社区版的PG外，还有大量基于PG的开源、国产数据库产品，包括华为的GAUSSDB系列，瀚高、金仓、神通、优炫、亚信ANTDB、中国移动磐维等，都是PG类数据库。甚至今天我讨论的部分内容对于MYSQL、达梦、SQL SERVER等没有使用DIO的数据库也是有一定作用的。

最近我经常听说某些客户把数据库从Oracle迁移到国产数据库上后，一些关键业务忽快忽慢，检查执行计划也没啥问题，操作系统层面也没有明显的瓶颈或者慢的因素。实际上这与数据库没有使用DIO有很大的关系。对于没有使用DIO的系统，当数据没有在DB CACHE中命中的时候，会产生物理读。不过这个物理读并不一定真正的从物理存储介质上读取，如果数据在LINUX的OS CACHE里，那么可以直接从CACHE中获取，这种物理读是很快的，如果OS CACHE没有命中，那么就只能真正的从物理存储介质中去读取了，相对于直接从内存中读取，这种物理读是较慢的。因为OS CACHE没办法按照我们的数据库访问需求去缓冲数据，因此OS CACHE的目的性并不强。这种忽快忽慢大多数集中在读操作上的，不过有时候写也会出现抖动，那是因为OS层面的的后台进程、检查点以及WAL或者文件写的FSYNC都会让OS产生一个抖动，从而对事务提交或者索引更新产生性能影响。在并发量较大、存在热点写数据的应用中，就很可能会产生一定的影响了。

我们无法去改变上面所说的DOUBLE BUFFERING的问题，这只能通过国产数据库厂商的努力在自己的数据库产品里全面引入DIO来解决了。实际上目前为止只有Oracle能够比较好的把控DIO，除此之外，哪怕是SQL SERVER这种老牌的商用数据库，虽然可以支持DIO，但是也不建议启用DIO。

可能有些朋友觉得偶尔出现几条SQL执行变慢没啥所谓，这可能和你看到的应用场景有关。如果是股票交易或者银行交易出现了某个时段几百笔交易延时异常，那么对于这个企业来说就是运维事故了。如果一条核心交易的SQL，平时执行只需要5毫秒，抖动时就可能变成三四十毫秒，现在网联对金融机构的交易超时监控十分严格，一次这样的抖动足以产生一次告警了。

要想避免此类抖动，必须在物理结构上做好优化。我今天提出几点优化的建议。首先是对底层存储的优化，如果不是使用集中式存储，那么还需要考虑512E和4KN等现代磁盘扇区大小的问题以及相关的对齐问题。今天篇幅有限，就不展开讨论了，有兴趣的朋友可以参考我以前写过的一篇文章《原来ADVANCED FORMAT HDD已经普及了》（https://mp.weixin.qq.com/s?__biz=MzA5MzQxNjk1NQ==&mid=2647848163&idx=1&sn=0f27f69e5d337a3873d6f3cdaa841a24&chksm=88786877bf0fe161178bed2cc05c2db84265ae3356ade0847e81b2e331e299d580c89dc31c9f&token=1445168299&lang=zh_CN#rd）。

除此之外，怕抖动的核心交易系统尽可能使用比较快的存储介质，比如NVME SSD，尽可能降低平均IO延时是从根本上降低IO抖动带来的问题的最便宜的方法，不要觉得SSD很贵，实际上在这里多花点钱可以在其他地方少花更多的钱。

第二个需要关注的就是存储的规划，通过使用独立的表空间和文件系统，让相互影响，可能导致运行毛刺的应用之间的冲突尽可能地减少。如果系统不重要，或者抖动无所谓，那么不需要做这方面的优化，而如果这个系统是十分关键的核心系统，那么在这方面做再多的工作都是值得的。在二十多年前，搞Oracle 7的时候，这些手段我也都用过。

对于写入十分重的系统，WAL尽可能独立存储于高性能的独立磁盘上，避免与其他读写IO冲突。小型热表也可以存储于独立的表空间里。相对静态，但是会大规模扫描的冷数据独立存储，访问十分频繁的表的索引与数据分开等都是可以采取的IO隔离手段。具体要根据业务的特点去做规划。前面讨论的都是从本地磁盘的角度去考虑的，实际上目前大多数系统使用集中式存储，很多IO延时稳定性的问题可能要考虑后端存储的规划与优化，大家要注意这个问题。后端存储分过来的盘和实际的物理盘是不同的。

第三个需要关注的问题是OS层面针对性的优化，这一点大家可以参考我的另外一篇文章《Postgresql数据库优化上该考虑些什么》（https://mp.weixin.qq.com/s?__biz=MzA5MzQxNjk1NQ==&mid=2647849429&idx=1&sn=2776253a04b780d090bfe7a03784345c&chksm=88786541bf0fec573182c704e41c0adddc9d96ee0af694cea263d685ce51fb3f9b4aa7700f52&token=1445168299&lang=zh_CN#rd），这里我就不重复介绍了。

第四个要考虑的是SHARED_BUFFERS的设置，如果我们在运行一个十分关键的核心交易系统，那么建议还是配置大一点的物理内存，并将SHARED BUFFFERS设置的足够大，尽可能地让热点数据都能够长期缓冲在数据库缓冲区里，从而稳定关键业务地运行效率。但是业务系统十分复杂，是不是也会产生一些对大型冷数据的扫描操作。这些操作尽可能地放到只读备机上去做，而不要影响核心业务系统的数据库缓冲。一种更好的设计是将这些可能对核心交易产生影响的数据从核心数据库中剥离出去，另外建个库，那样的话对核心交易的影响就小多了。

sql 数据库大数据 sql优化物理

文章转载自白鳝的洞穴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PG类数据库优化之稳定关键SQL执行速度

评论