今天遇到了D-SMART产品本身的性能问题。准备用D-SMART巡查一组Oracle数据库，发现任务异常，因为一条SQL超时。通过日志发现是分析某指标的SQL。执行时间高达229秒。检查报告中设置的SQL超时时间为180秒。如果检查时间间隔超过一个半月，则这条SQL的执行时间在170秒到250秒之间。它经常超时。D-SMART的后台数据库是PG，这张表是TIMESCALEDB表。还会在表上创建适当的索引。根据explain分析，执行计划也正常，通过这个分区索引进行范围扫描，然后进行聚合（Timescaledb会自动根据时间戳对数据进行分区）。通过D-SMART的PG数据库等待事件分析工具，可以发现数据文件读取在前。一开始，我没有仔细分析。通过EXPAIN发现sortbuffer使用量接近20M，明显超过了WORK_MEM参数。于是我调整了WORK_MEM参数，重新执行了这条SQL。结果发现原来需要200多秒的SQL，不到50毫秒就完成了。不过我还是留了个心眼，因为在D-SMART分析工具中，可以看到文件读取占据了比较高的位置。于是我重启了PG数据库，再次执行了这条SQL。比以前慢了一点，大约80毫秒。不过比起200多秒，也提升不少。于是跟同事说，这个SQL的性能问题已经解决了，增加WORK_MEM参数就行了。老储在PG方面还是有丰富的实践经验的。他提醒我，验证PG的问题，重启数据库也没用。文件缓冲会影响SQL的性能。我从事Oracle20多年，一直用Oracle的思维来思考现在的数据库问题。这次我又犯了类似的错误。所以我重新测试，关闭数据库，然后使用echo3>drop_caches命令清除操作系统缓冲区，然后再次测试。遗憾的是，SQL的性能又回到了过去。看起来增加WORK_MEM并不能有效提高SQL性能。回头想想也是，排序缓冲区就算稍微超出一点，硬盘排序搞定，也不可能有20秒的性能影响。公司的PG14.4环境安装在虚拟机上，磁盘是SATA磁盘，性能确实不行。对于像PG这样使用DOUBLECACHE的数据库，文件缓冲确实可以显着提高SQL性能。而这个特性也会使得PG数据库中同一条SQL语句的执行性能在OS的不同状态下波动较大。让我们用一个例子来验证它。在做这个测试之前，我们需要安装一个插件——pgfincore，对这个插件感兴趣的朋友可以去https://github.com/klando/pgfincore下载。Pgfincore是一个用于操作系统缓冲区分析和PG数据库操作的插件。一般被用户用来分析OS缓存中的数据库表或索引，也有一些用户用来预热数据，让一些热数据一直缓存在FILECACHE中，让OSCACHE更好的发挥作用。Pgfincore非常强大。首先，它可用于检查操作系统缓冲区中表或索引的状态。例如：我查看的timescaledb的一个索引分区共有157000页，其中139000页在OSbuffer中。第二个功能是将某个表或索引的数据预热到OSCache中。这里需要注意的是，如果表是分区表，必须直接预热分区，不能使用表名。pgfincore不支持表分区的自动识别。Timescaledb的表分区。本来这张表并没有完全缓冲到内存中。通过调用pgfadvise_willneed函数，将这张表的所有数据调入OS缓冲区。第三个功能是在特定场景下备份和恢复OSCACHE。这对于预热一些非常关键的系统非常有价值。例如，系统的某些热数据对系统性能至关重要。当系统重启（尤其是服务器重启）一定时间后，数据还没有预热，系统性能就会受到很大的影响。如果我们在重启前备份OSCache中一些热表的缓冲状态，并在系统重启后立即预热这部分数据，可以保证系统重启后立即恢复重启前的性能。首先在系统重启前将pgfincore数据保存在pgfincore_snapshot表中，系统重启后使用pgfadvise_loader重新加载缓冲数据。有了以上的基础知识，下面我们来做个实验。首先彻底清理操作系统缓冲区。然后启动PG数据库。执行刚才出问题的SQL语句。我只截取了部分执行计划，因为对于每个分区，扫描的方式都是一样的，先扫描索引，再回表。此SQL执行时间超过34秒。接下来我们按照上面的流程再次关闭数据库，清空缓冲区，然后对所有的索引分区进行预热，看看效果。可以看到现在所有索引的OSbufferitems都为0，说明没有缓存索引数据。接下来预热，然后再次执行这条SQL。可以看到预热之后，这些索引分区都在OSbuffer中，所有表的数据也都预热了。我们执行一下刚才的SQL语句：可以看到，执行时间从34秒变成了31毫秒。其实这样的问题对于使用双缓存的数据库来说是很常见的。这类数据库产品的同一条SQL在不同时间的执行性能可能相差数百倍，但其执行计划是完全一样的。这是因为双缓存。Pgfincore插件为此类问题提供了很好的解决方案。使用快照/恢复的方式，如果做一些适当的细粒度管理，对于稳定关键业务SQL的执行效率可以起到很好的作用。希望我的性能失败案例能给大家一些启发。
