通过预热来优化PG数据库的SQL性能

前些年和搞PG的朋友交流的时候，他说PG数据库是比较简单的数据库，但是用好PG数据库并简单。和Oracle数据库比起来，Oracle数据库很复杂，想管好，优化好技术就要很好，掌握管理Oracle的技术需要下大功夫，一旦掌握了，反而管理起来很简单，很顺手了。而PG数据库相对简单，想管好，优化好，在技术上并不复杂，掌握技术难度不大，但是你需要更为精细的去管理它，才能用好它。我觉得他说的挺有道理，PG可以支撑超大型数据库系统，不过你需要在运维与优化上精耕细作，才能用好。

PG数据库提供了相当多的小功能，也有大量的第三方插件，实际上都是在生产环境中遇到了问题而给出的一些解决方案。最近我们就用上了一个数据表预热的插件fincore。对于Oracle这样的数据库来说，数据预热在大多数场景下是不需要的，不过偶尔我们还是会使用预热来加速某些晚上的定时统计任务。

PG数据库采用DOUBLE CACHE的模式，因此预热的应用场景会更丰富一些。在使用PG数据库的时候，我们经常会发现某条SQL执行效率不稳定，有时候秒出，有时候需要十多秒，查看执行计划，还没啥变化，数据量也变化不大。如果遇到这种情况，那么你可能就遇到了DOUBLE CACHE的问题了。当数据都在内存里（包括文件缓冲），SQL的执行效率会非常高，而如果数据在缓冲中的比例不高，那么执行效率就会严重下降。

前阵子我们的D-SMART就遇到了这样一个场景，因为这个用户比较大，D-SMART纳管了500多套Oracle数据库。这就导致PG数据库的共享缓冲区中存储的主要都是指标数据了。当用户想分析TOP SQL的时候，就会觉得很慢，特别是第一次查询，需要7-8秒钟才出结果。我们分析了执行计划，索引使用啥的都是正常的，就是因为表中数据量太大，并且TOPSQL表使用频率并不高，数据比较冷。

刚开始我觉得这条SQL也没法优化，也没必要优化，分析TOP SQL本身就不是十分常用的操作。不过用户并不认同我的观点，他们认为如果日常运维遇到了必须分析TOP SQL的时候往往就是遇到了十分严重的性能问题，对于他们这种金融服务企业，这个时候定位问题解决问题的时间是十分关键的，这时候就需要每个操作都有十分快的响应。

要想优化这个SQL，实际上也没有太好的办法，并行查询原先就已经启用，而且并发度提高也没啥用了。最后我们想到了预热，如果对最近2天的TOP SQL表做预热，那么这个TOP SQL查询的执行时间不超过50毫秒。不过因为相对于指标数据，TOP SQL表太冷了，预热3-5分钟后，这些CACHE就会被重新驱逐了。于是我们做了一个定时任务，每隔五分钟预热一次数据，用户对这个模块的体验基本上满意了。

在这个场景中，因为服务器内存不是很大，预热TOP SQL表会对其他业务产生一定的负面影响，比如查找指标会稍微慢一点，不过用户是能够接受的，10毫秒和15毫秒的响应时间差别，UI用户在操作上是无感的。我们用牺牲特别快的查找指标的性能来提升相对较慢的TOP SQL查询，这笔账是划算的。

一般来说，预热需求往往是对经常被查询或者要重点保障查询性能的大型数据表的，如果所有需要查询的数据都需要从硬盘中读取，会导致查询速度变慢。有很多种需要预热数据的场景。首选，数据表中包含大量数据，而且这些数据经常被查询。数据表中的数据经常被修改，例如经常进行插入、更新或删除操作。这种情况下，数据预热可以加速查询的同时，减少I/O操作的次数，从而提高系统的稳定性。

需要注意的是，如果数据表的大小比较小，或者该数据表的查询不频繁，或者反过来说，某些特别热的小表，其数据大部分都在共享缓冲区中，那么进行数据预热的效果可能不太明显，反而会浪费系统资源。因此，在决定是否对数据表进行预热时，需要仔细分析数据表的特性和使用情况。

还有一个数据预热十分有效的场景就是每天的定期数据统计前。此时OLTP业务负载很小，内存可以腾出来给BATCH类应用。此时如果我们在批处理统计开始之前，先把统计需要使用的主要数据都先预热一下，那么统计分析任务的执行时间可以大幅度提升。因为顺序扫描文件的预热操作是顺序读，用提前的大量顺序读来替换SQL执行时大量的随机读，对于大多数应用场景来说，都是十分划算的。在我们以往的优化案例中，执行效率提升十倍以上是十分常见的。

在做数据预热的时候，我们也需要做一些事先分析。首先要考虑数据库服务器的内存情况。如果内存十分紧张，那么对大表的预热效果可能不好。其次是磁盘IO能力是否能够支撑预热操作。如果本身磁盘IO性能就很差，负担就过重，那么预热可能会给糟糕的磁盘IO带来十分负面的影响，很可能达不到通过预热减轻磁盘IO的作用。磁盘性能好，还可以加速预热的时间，并且让一些定期的预热操作不至于对系统整体性能造成影响。最后就是场景，预热操作的设计需要对系统十分了解，做精细化的分析后才能确定好方案的。如果对你运维的系统一无所知，盲目的去设计预热方案，可能事倍功半，甚至可能引发严重的运维故障。

最后就说说如何预热了。我今天早上就这个问题和CHATGPT聊了聊，根据我的引导，它给出了一个PYTHON脚本。我看了一下，大体上是靠谱的，根据这个思路，我们可以自己写一个预热工具。

import os

import psycopg2

# 连接数据库

conn = psycopg2.connect(database='mydb', user='myuser', password='mypassword', host='localhost', port='5432')

def execute_sql(sql, args=None):

    with conn:

        with conn.cursor() as curs:

            curs.execute(sql, args)

            return curs.fetchall()

def preload_table(table_name):

    # 获取表所在的目录

    base_path = os.path.join(os.environ['PGDATA'], 'base')

    table_oid = execute_sql("SELECT oid FROM pg_class WHERE relname = %s", (table_name,))[0][0]

    table_dir = os.path.join(base_path, str(table_oid // 10000), str(table_oid))

    # 获取所有数据文件并将其预热进入文件缓冲区

    for filename in os.listdir(table_dir):

        if filename.startswith(str(table_oid) + '.') and not filename.endswith('.fsm'):

            filepath = os.path.join(table_dir, filename)

            with open(filepath, 'rb') as f:

                f.read()

if __name__ == '__main__':

    table_name = 'mytable'  # 替换成需要预热的表名

    preload_table(table_name)

如果不想自己写程序的朋友，PG数据库的fincore插件可以研究一下，使用fincore你也可以十分方便的实现你所需要的各种预热操作。