我建议您阅读 Vladimir Sitnikov 2016 年初报告的文字记录“PostgreSQL 和 JDBC 正在榨干所有的汁液”
下午好我叫弗拉基米尔·西特尼科夫。 我已经在 NetCracker 工作了 10 年。 我主要关注的是生产力。 一切与Java有关的东西，一切与SQL有关的东西都是我所热爱的。
而今天我就来说说我们在公司开始使用PostgreSQL作为数据库服务器时遇到的事情。 我们主要使用 Java。 但我今天要告诉您的不仅仅是 Java。 实践表明，这种情况也发生在其他语言中。
我们将讨论：
- 关于数据采样。
- 关于保存数据。
- 还有关于性能。
- 还有埋在那里的水下耙子。
让我们从一个简单的问题开始。 我们根据主键从表中选择一行。
数据库位于同一主机上。 而所有这些耕种过程只需要 20 毫秒。
这20毫秒很多。 如果你有 100 个这样的请求，那么你每秒都要花时间滚动浏览这些请求，也就是说，我们在浪费时间。
我们不喜欢这样做，看看基地为此提供了什么。 数据库为我们提供了两种执行查询的选项。
第一个选项是一个简单的请求。 它有什么好处呢？ 事实上，我们接收并发送它，仅此而已。
该数据库还有一个高级查询，它更棘手，但功能更强大。 可以单独发送解析、执行、变量绑定等请求。
超级扩展查询是我们在当前报告中不会讨论的内容。 也许我们想要从数据库中得到一些东西，并且有一个以某种形式形成的愿望清单，即这就是我们想要的，但现在和明年都是不可能的。 所以我们只是把它录下来，然后我们就会去摇动主要人物。
而我们能做的就是简单查询和扩展查询。
每种方法有什么特别之处？
一个简单的查询适合一次性执行。 一旦完成又忘记。 问题是它不支持二进制数据格式，即不适合某些高性能系统。
扩展查询 - 允许您节省解析时间。 这就是我们所做的并开始使用的。 这真的非常帮助我们。 不仅可以节省解析时间。 可以节省数据传输费用。 以二进制格式传输数据效率更高。
我们继续练习吧。 这就是典型应用程序的样子。 可能是Java等。
我们创建了声明。 执行了命令。 创建关闭。 这里的错误在哪里呢？ 问题是什么？ 没问题。 所有书上都是这么说的。 应该这样写。 如果你想要最大的性能，就这样写。
但实践证明这是行不通的。 为什么？ 因为我们有一个“close”方法。 当我们这样做时，从数据库的角度来看，它就像一个吸烟者使用数据库一样。 我们说“解析执行解除分配”。
为什么要额外创建和卸载语句？ 没有人需要它们。 但在PreparedStatements 中通常发生的情况是，当我们关闭它们时，它们会关闭数据库上的所有内容。 这不是我们想要的。
我们希望像健康人一样与基地合作。 我们接受并准备了一次声明，然后多次执行。 事实上，很多次——这在应用程序的整个生命周期中只有一次——它们已经被解析了。 我们在不同的 REST 上使用相同的语句 id。 这是我们的目标。
我们怎样才能做到这一点？
非常简单——不需要关闭语句。 我们这样写：“准备”“执行”。
如果我们推出这样的东西，那么很明显有些东西会在某个地方溢出。 如果不清楚的话，你可以尝试一下。 让我们编写一个使用这个简单方法的基准测试。 创建一个声明。 我们在某些版本的驱动程序上启动它，发现它很快就会崩溃，并丢失所有内存。
显然，此类错误很容易纠正。 我不会谈论他们。 但我要说的是，新版本的运行速度要快得多。 虽然方法很蠢，但是还是可以的。
如何正确工作？ 为此我们需要做什么？
实际上，应用程序总是关闭语句。 在所有书籍中他们都说要关闭它，否则内存会泄漏。
而且 PostgreSQL 不知道如何缓存查询。 每个会话有必要为自己创建此缓存。
我们也不想在解析上浪费时间。
和往常一样，我们有两个选择。
第一个选择是我们接受它并说让我们将所有内容包装在 PgSQL 中。 那里有一个缓存。 它缓存所有内容。 结果会很棒。 我们看到了这个。 我们有 100500 个请求。 不起作用。 我们不同意手动将请求转化为程序。 不，不。
我们还有第二个选择——自己拿去剪。 我们打开来源并开始剪辑。 我们看到了又看到了。 事实证明，做起来并没有那么难。
这出现在 2015 年 XNUMX 月。 现在有一个更现代的版本。 一切都很棒。 它运行得非常好，我们无需更改应用程序中的任何内容。 我们甚至不再考虑 PgSQL 的方向，也就是说，这足以让我们将所有管理成本降低到几乎为零。
因此，服务器准备的语句在第五次执行时被激活，以避免在每个一次性请求上浪费数据库内存。
你可能会问——数字在哪里？ 你得到什么？ 这里我不会给出数字，因为每个请求都有自己的请求。
我们的查询花费了大约 20 毫秒来解析 OLTP 查询。 执行时间为 0,5 毫秒，解析时间为 20 毫秒。 请求 – 10 KiB 文本，170 行计划。 这是一个 OLTP 请求。 它需要 1、5、10 行，有时甚至更多。
但我们根本不想浪费 20 毫秒。 我们将其减少到 0。 一切都很棒。
你能从这里带走什么？ 如果您有 Java，那么您可以使用现代版本的驱动程序并感到高兴。
如果您说不同的语言，那么想一想 - 也许您也需要这个？ 因为从最终语言的角度来看，例如，如果 PL 8 或者您有 LibPQ，那么您并不清楚您是否将时间花在执行、解析上，这是值得检查的。 如何？ 一切都是免费的。
除了存在错误和一些特殊性之外。 我们现在就讨论它们。 其中大部分将是关于工业考古学，关于我们发现了什么，我们遇到了什么。
如果请求是动态生成的。 它发生了。 有人将字符串粘合在一起，从而生成 SQL 查询。
他为什么不好？ 这很糟糕，因为每次我们都会得到不同的字符串。
并且需要再次读取这个不同字符串的hashCode。 这实际上是一项 CPU 任务 - 即使在现有哈希中查找长请求文本也不是那么容易。 因此，结论很简单——不生成请求。 将它们存储在一个变量中。 并欢喜。
下一个问题。 数据类型很重要。 有些 ORM 说，不管有哪种 NULL，只要有某种类型就行。 如果是 Int，那么我们说 setInt。 如果为 NULL，则让它始终为 VARCHAR。 那么 NULL 到底有什么区别呢？ 数据库本身会理解一切。 而且这张图不行。
实际上，数据库根本不关心。 如果你第一次说这是一个数字，第二次你说这是一个VARCHAR，那么就不可能重用服务器准备的语句。 在这种情况下，我们必须重新创建我们的语句。
如果您正在执行相同的查询，请确保列中的数据类型没有混淆。 您需要注意 NULL。 这是我们开始使用PreparedStatements后经常遇到的错误
好的，已开机。 也许他们带走了司机。 生产力下降了。 事情变得很糟糕。
这是怎么发生的？ 这是一个错误还是一个功能？ 不幸的是，无法理解这是一个错误还是一个功能。 但有一个非常简单的场景可以重现这个问题。 她完全出乎意料地伏击了我们。 它包括从一张表中逐字抽样。 当然，我们还有更多这样的要求。 一般来说，他们包括两个或三个表，但有这样的回放场景。 从数据库中获取任何版本并播放。
关键是我们有两列，每一列都有索引。 一个 NULL 列中有一百万行。 第二列仅包含 20 行。 当我们在没有绑定变量的情况下执行时，一切正常。
如果我们开始使用绑定变量执行，即我们执行“？” 或者我们的请求为“1 美元”，我们最终会得到什么？
第一次执行符合预期。 第二个稍微快一点。 有些东西被缓存了。 第三、第四、第五。 然后砰的一声——诸如此类的事情。 最糟糕的是，这种情况发生在第六次处决时。 谁知道必须执行六次才能了解实际的执行计划？
谁有罪？ 发生了什么？ 数据库包含优化。 它似乎针对一般情况进行了优化。 因此，从某个时刻开始，她转而采用通用计划，不幸的是，该计划可能会有所不同。 结果可能是相同的，也可能是不同的。 并且有某种阈值会导致这种行为。
你能为这个做什么？ 当然，在这里假设任何事情都更加困难。 我们使用一个简单的解决方案。 这是+0，OFFSET 0。你肯定知道这样的解决方案。 我们只需接受它并在请求中添加“+0”，一切就都很好了。 稍后我会告诉你。
还有另一种选择——更仔细地审视计划。 开发人员不仅要写一个请求，还要说6次“解释分析”。 如果是5就不行了。
还有第三种选择 - 给 pgsql-hackers 写一封信。 我写道，但是，目前还不清楚这是一个错误还是一个功能。
当我们思考这是一个错误还是一个功能时，让我们修复它。 让我们接受我们的请求并添加“+0”。 一切安好。 两个符号，你甚至不必考虑它是什么或它是什么。 很简单。 我们只是禁止数据库在该列上使用索引。 我们在“+0”列上没有索引，就是这样，数据库不使用索引，一切都很好。
这是6的规则解释。 现在，在当前版本中，如果您有绑定变量，则必须执行 6 次。 如果您没有绑定变量，这就是我们所做的。 而最终也正是这个要求失败了。 这不是什么棘手的事情。
看起来，有多少可能？ 这里有一个错误，那里有一个错误。 事实上，这个 bug 无处不在。
让我们仔细看看。 例如，我们有两个模式。 方案A与表S以及图B与表S。 查询——从表中选择数据。 在这种情况下我们会得到什么？ 我们将会遇到一个错误。 我们将拥有以上所有内容。 规则是——错误无处不在，我们将拥有以上所有内容。
现在的问题是：“为什么？” 似乎有文档表明，如果我们有一个模式，那么就有一个“search_path”变量告诉我们在哪里查找表。 看来还是有变数的。
问题是什么？ 问题是服务器准备的语句不怀疑 search_path 可以被某人更改。 该值对于数据库来说保持不变。 有些部分可能不会获得新的含义。
当然，这取决于您正在测试的版本。 取决于您的表差异的严重程度。 9.1 版本将简单地执行旧的查询。 新版本可能会捕获该错误并告诉您有错误。
该如何治疗呢？ 有一个简单的秘诀——不要这样做。 应用程序运行时无需更改 search_path。 如果发生更改，最好创建新连接。
你可以讨论，即打开、讨论、添加。 也许我们可以说服数据库开发人员，当有人更改值时，数据库应该告诉客户端：“看，你的值已在这里更新。 也许您需要重置语句并重新创建它们？” 现在，数据库的行为是秘密的，不会以任何方式报告语句在内部某处发生了更改。
我要再次强调 - 这对于 Java 来说是不典型的。 我们将在 PL/pgSQL 中一对一地看到同样的事情。 但它会在那里被复制。
让我们尝试更多的数据选择。 我们选择再选择。 我们有一个包含一百万行的表。 每行为一千字节。 大约一千兆字节的数据。 我们的Java机器有128兆字节的工作内存。
正如所有书籍中所建议的那样，我们使用流处理。 也就是说，我们打开resultSet并从那里一点一点地读取数据。 它会起作用吗？ 会从记忆中消失吗？ 你会读一点吗？ 让我们相信数据库，让我们相信 Postgres。 我们不相信。 我们会失去记忆吗？ 谁经历过内存不足？ 后来谁成功修复了它？ 有人设法修复了它。
如果您有一百万行，您就不能只是挑选。 需要偏移/限制。 谁支持这个选项？ 谁赞成使用 autoCommit？
在这里，像往常一样，最意想不到的选择被证明是正确的。 如果你突然关闭 autoCommit，会有帮助。 这是为什么？ 科学对此一无所知。
但默认情况下，所有连接到 Postgres 数据库的客户端都会获取整个数据。 PgJDBC 在这方面也不例外；它选择所有行。
FetchSize 主题有一个变体，即您可以在单独的语句级别上说，请按 10、50 选择数据。但这只有在您关闭 autoCommit 后才会起作用。 关闭自动提交 - 它开始工作。
但是遍历代码并到处设置setFetchSize很不方便。 因此，我们进行了一项设置，表示整个连接的默认值。
这就是我们所说的。 参数已配置。 我们得到了什么？ 如果我们选择少量数据，例如，我们一次选择 10 行，那么我们的开销成本就会非常大。 因此，这个值应该设置为一百左右。
当然，理想情况下，您仍然需要学习如何以字节为单位限制它，但秘诀是：将 defaultRowFetchSize 设置为超过 XNUMX，然后就高兴了。
让我们继续插入数据。 插入更容易，有不同的选择。 例如，插入、值。 这是一个不错的选择。 你可以说“插入选择”。 实际上也是一样的。 性能上没有区别。
书上说你需要执行一个Batch语句，书上说你可以用几个括号执行更复杂的命令。 Postgres 有一个很棒的功能 - 你可以进行 COPY，即做得更快。
如果你测量它，你可以再次得到一些有趣的发现。 我们希望它如何运作？ 我们不想解析也不执行不必要的命令。
实际上，TCP 不允许我们这样做。 如果客户端正忙于发送请求，则数据库不会读取请求以尝试向我们发送响应。 最终的结果是客户端等待数据库读取请求，数据库等待客户端读取响应。
因此客户端被迫定期发送同步数据包。 额外的网络交互，额外的时间浪费。
我们添加的越多，情况就越糟糕。 驱动程序相当悲观，并且经常添加它们，大约每 200 行添加一次，具体取决于行的大小等。
碰巧你只修正了一行，一切都会加速 10 倍。 它发生了。 为什么？ 像往常一样，这样的常量已经在某处使用过。 值“128”表示不使用批处理。
还好这没有包含在正式版本中。 在发布开始之前发现。 我给出的所有含义均基于现代版本。
我们来试试吧。 我们衡量InsertBatch很简单。 我们多次测量 InsertBatch，即同一件事，但有很多值。 棘手的举动。 不是每个人都能做到这一点，但这是一个如此简单的举动，比 COPY 容易得多。
您可以进行复制。
你可以在结构上做到这一点。 声明用户默认类型，将数组和 INSERT 直接传递到表。
如果您打开链接：pgjdbc/ubenchmsrk/InsertBatch.java，则此代码位于 GitHub 上。 您可以具体查看那里生成了哪些请求。 没关系。
我们推出了。 我们首先意识到的是，不使用批处理是根本不可能的。 所有批处理选项均为零，即与一次性执行相比，执行时间实际上为零。
我们插入数据。 这是一张非常简单的桌子。 三列。 我们在这里看到了什么？ 我们看到所有这三个选项大致具有可比性。 当然，COPY 更好。
这是我们插入碎片的时候。 当我们说一个 VALUES 值、两个 VALUES 值、三个 VALUES 值或我们表示其中 10 个值时，用逗号分隔。 现在这只是水平的。 1, 2, 4, 128。 可以看到，用蓝色绘制的Batch Insert，让他的心情好了很多。 也就是说，当您一次插入一个甚至一次插入四个时，它的性能会提高一倍，这仅仅是因为我们在 VALUES 中塞入了更多的内容。 更少的 EXECUTE 操作。
在小批量上使用 COPY 是非常没有希望的。 我什至没有画前两个。 他们去了天堂，就是这些绿色的数字进行COPY。
当您有至少一百行数据时，应使用 COPY。 打开这个连接的开销很大。 而且，说实话，我并没有往这个方向挖掘。 我优化了 Batch，但没有优化 COPY。
接下来我们做什么？ 我们试穿了。 我们知道我们需要使用结构或结合多种含义的巧妙的 bacth。
您应该从今天的报告中得到什么？
- ReadyStatement 是我们的一切。 这对生产力有很大帮助。 美中不足的是，它产生了很大的失败。
- 并且需要执行 EXPLAIN ANALYZE 6 次。
- 我们需要稀释 OFFSET 0 和 +0 之类的技巧，以纠正有问题的查询的剩余百分比。
来源： habr.com