主页 / dba / 问题 / 28060
Accepted
lorgartzor
Asked: 2012-11-03 01:51:40 +0800 CST

如何确定 PostgreSQL 中池大小和数据库连接之间的最佳点

  • 772

我们在处理高峰时段到我们的数据库服务器的流量时遇到了麻烦。我们正在考虑改进硬件（请参阅有关该方面的问题），但我们也希望处理池配置和服务器调优。

我们正在开发的应用程序是一款面向智能手机的回合制多人游戏，其中后端由带有独角兽的Rails和PostgreSQL 9.1作为数据库组成。我们目前有 600 000 个注册用户，并且由于游戏状态存储在数据库中，因此每隔几秒钟就会进行数千次写入。我们使用PgBadger分析了来自PostgreSQL的日志文件，在关键时刻我们得到了很多

FATAL: remaining connection slots are reserved for non-replication superuser connections

解决这个问题的简单解决方案是在postgresql.conf中增加max_connections（当前为 100） 。我已阅读http://wiki.postgresql.org/wiki/Number_Of_Database_Connections这表明这可能不是正确的做法。在前面提到的文章中，它提到了找到 max_connections 和pool size之间的最佳点。

为了找到这个甜蜜点可以做些什么？是否有任何好的工具来测量不同max_connections和池大小值的 I/O 性能？

我们当前的设置是 4 个游戏服务器，每个服务器有 16 个独角兽工作者和一个 5 个池大小。

以下是我们使用的非默认 postgres 设置：

version                      | PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu,compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
checkpoint_completion_target | 0.9
checkpoint_segments          | 60
checkpoint_timeout           | 6min
client_encoding              | UTF8
effective_cache_size         | 2GB
lc_collate                   | en_US.UTF-8
lc_ctype                     | en_US.UTF-8
log_destination              | csvlog
log_directory                | pg_log
log_filename                 | postgresql-%Y-%m-%d_%H%M%S.log
log_line_prefix              | %t
log_min_duration_statement   | 200ms
log_rotation_age             | 1d
log_rotation_size            | 10MB
logging_collector            | on
max_connections              | 100
max_stack_depth              | 2MB
server_encoding              | UTF8
shared_buffers               | 1GB
ssl                          | on
TimeZone                     | localtime
wal_buffers                  | 16MB
work_mem                     | 8MB
postgresql

1 个回答

  1. Best Answer

    这里的简短回答是“由监控和性能指标引导的反复试验”。

    有一些一般的经验法则可以帮助您找到应该开始的模糊区域，但它们非常笼统。广泛的指导方针“CPU 数量加上独立磁盘数量”经常被引用，但这只是一个非常粗略的起点。

    您真正需要做的是为您的应用程序制定可靠的性能指标。开始记录统计数据。

    为此，集成工具的方式并不多。有诸如 nagioscheck_postgres脚本、Cacti 系统性能计数器日志记录、PostgreSQL 统计信息收集器之类的东西……但没有太多东西可以将它们放在一起。可悲的是，你必须自己做那一点。对于 PostgreSQL 方面，请参阅PostgreSQL 手册中的监控。存在一些第三方选项，例如EnterpriseDB 的 Postgres Enterprise Monitor。

    对于此处提到的应用程序级指标，您需要将它们记录在共享数据结构或外部非持久数据库（如 Redis）中，并在记录它们时或在将它们写入 PostgreSQL 数据库之前聚合它们。尝试直接记录到 Pg 会因记录测量结果所产生的开销而扭曲您的测量结果，并使问题变得更糟。

    最简单的选项可能是您用于记录应用程序统计信息的每个应用程序服务器中的单例。您可能希望不断更新 min、max、n、total 和 mean；这样您就不必存储每个统计点，只需存储聚合。这个单例可以每 x 分钟将其聚合统计信息写入 Pg，这个速度足够低，对性能的影响最小。

    从...开始：

    • 请求延迟是多少？换句话说，应用程序从收到客户端的请求到响应客户端需要多长时间。在一段时间内汇总记录，而不是作为单独的记录。按请求类型分组；说，按页。

    • 应用程序执行的每个查询或查询类型的数据库访问延迟是多少？从向数据库询问信息/存储信息到完成并可以继续下一个任务需要多长时间？同样，在应用程序中聚合这些统计信息，并且只将聚合信息写入数据库。

    • 你的吞吐量怎么样？在任何给定的 x 分钟内，您的应用程序执行的每个主要类的查询有多少由数据库提供服务？

    • 在 x 分钟的相同时间范围内，有多少客户请求？

    • 每隔几秒采样一次并在数据库中的相同 x 分钟窗口上进行聚合，有多少数据库连接？其中有多少是闲置的？有多少是活跃的？在插入？更新？选择？删除？在此期间有多少交易？请参阅统计收集器文档

    • 在相同的时间间隔内再次采样和聚合，主机系统的性能指标是什么样的？每秒多少读取和写入磁盘 IO？每秒兆字节的磁盘读写？CPU利用率？平均负载？内存使用？

    您现在可以通过关联数据、绘制图表等方式开始了解您的应用程序的性能。您将开始看到模式，开始发现瓶颈。

    您可能会了解到您的系统存在瓶颈INSERT并且UPDATE事务率很高，尽管磁盘 I/O 非常低（以兆字节/秒为单位）。这暗示您需要使用电池支持的回写缓存 RAID 控制器或一些高质量的电源保护 SSD 来提高磁盘刷新性能。synchronous_commit = off如果可以在 server crash 和/或 a 上丢失一些事务，您也可以使用来减轻commit_delay一些同步负载。

    当您根据并发连接数绘制每秒事务并纠正应用程序看到的不同请求率时，您将能够更好地了解您的吞吐量最佳点在哪里。

    如果您没有快速刷新存储（BBU RAID 或快速耐用的 SSD），您只需要相当少量的主动写入连接，最多可能是您拥有的磁盘数量的 2 倍，可能更少，具体取决于 RAID 安排，磁盘性能等。在这种情况下，甚至不值得反复试验；只需将您的存储子系统升级到具有快速磁盘刷新功能的存储子系统。

    请参阅pg_test_fsync帮助您确定这是否对您来说是个问题的工具。大多数 PostgreSQL 软件包都将此工具作为 contrib 的一部分安装，因此您不需要编译它。如果您获得的操作数少于每秒几千次，则pg_test_fsync您迫切需要升级您的存储系统。我配备 SSD 的笔记本电脑获得 5000-7000。我的工作站使用 7200rpm SATA 磁盘的 4 磁盘 RAID 10 阵列和直写（非写入缓存）在 80 操作/秒f_datasync，下降到 20 操作/秒fsync()；它慢了数百倍。比较：带有 ssd 的笔记本电脑与带有直写（非写入缓存）RAID 10 的工作站. 这台笔记本电脑的 SSD 很便宜，我不一定相信它会在断电时刷新其写入缓存；我保留了良好的备份，不会将其用于我关心的数据。优质 SSD 的性能即使不是更好也一样好，并且是耐写的。

    对于您的申请，我强烈建议您调查：

    • 具有快速刷新的良好存储子系统。我不能强调这一点。优质的电源故障安全 SSD 和/或具有电源保护回写缓存的 RAID 控制器。
    • 使用UNLOGGED表格存储您可以承受丢失的数据。定期将其聚合到记录表中。例如，将正在进行的游戏保存在未记录的表中，并将分数写入普通的持久表。
    • 使用commit_delay（对快速刷新存储不太有用 - 提示）
    • 关闭synchronous_commit您可以承受损失的事务（对快速刷新存储不太有用 - 提示提示）
    • 分区表，尤其是数据“过期”并被清理的表。与其从分区表中删除，不如删除一个分区。
    • 部分索引
    • 减少您创建的索引数量。每个索引都有写入成本。
    • 将工作批量处理成更大的事务
    • 使用只读热备副本减轻主数据库的读取负载
    • 使用像 memcached 或 redis 这样的缓存层来存储不经常更改或可以承受陈旧的数据。您可以使用LISTEN和NOTIFY使用 PostgreSQL 表上的触发器执行缓存失效。

    如果有疑问：http ://www.postgresql.org/support/professional_support/

    • 16

相关问题