作为医疗保健行业公司构建消息传递平台的高级软件工程师，我负责我们应用程序的性能，包括其他职责。 我们使用Ruby on Rails应用程序开发了非常标准的Web服务，用于业务逻辑和API，React + Redux用于用户面对的单页应用程序，因为数据库使用PostgreSQL。 在类似堆栈中出现性能问题的常见原因是对数据库的查询过多，我想讲一个故事，我们如何应用非标准但相当简单的优化来提高性能。
我们的业务在美国运营，因此我们必须遵守HIPAA，并遵守某些安全政策，因此我们始终准备进行安全审核。 为了降低风险和成本，我们依靠特殊的云提供商来运行我们的应用程序和数据库，这与Heroku的做法非常相似。 一方面，它使我们能够专注于构建平台，但另一方面，它对我们的基础结构增加了额外的限制。 简短地说-我们不能无限扩大规模。 作为成功的启动，我们每隔几个月就会增加用户数量，有一天我们的监控告诉我们，我们超出了数据库服务器上的磁盘IO配额。 底层的AWS开始节流，这导致严重的性能下降。 Ruby应用程序无法处理所有传入流量，因为Unicorn员工花费太多时间等待数据库的响应，客户不满意。
标准解决方案
在本文的开头，我提到了“非标准优化”一词，因为所有低调的成果都已被采摘：
- 我们删除了所有N + 1个查询。 Ruby gem Bullet是主要工具
- 多亏了pg_stat_statements ，添加了数据库上所有需要的索引，删除了所有不需要的索引
- 重写了一些具有多个联接的查询，以提高效率
- 我们将查询分开以从装饰查询中获取分页集合。 例如，最初我们通过联接表来为每个对话框添加消息计数器，但是它被替换为附加查询以增强结果。 下一个查询执行“仅索引扫描”并且非常便宜：
SELECT COUNT(1), dialog_id FROM messages WHERE dialog_id IN (1, 2, 3, 4) GROUP BY dialog_id;
- 添加了一些缓存。 实际上，这并不奏效，因为作为消息传递应用程序，我们进行了许多更新
所有这些技巧在几个月内都表现出色，直到我们再次遇到相同的性能问题-更多的用户，更高的负载。 我们在寻找其他东西。
先进的解决方案
我们不想使用重型火炮并实施非规范化和分区，因为这些解决方案需要数据库方面的深厚知识，需要将团队的重点从实现功能转移到维护，最后我们要避免应用程序的复杂性。 最后，我们使用了PostgreSQL 9.3，其中分区基于所有成本的触发器。 KISS原则付诸实践。
定制解决方案
压缩资料
我们决定专注于主要症状-磁盘IO。 随着我们存储的数据越来越少，所需的IO容量也越来越少，这就是主要思想。 我们开始寻找压缩数据的机会，第一个候选对象是ActiveRecord提供的多态关联提供的诸如user_type列。 在应用程序中，我们经常使用模块，这导致我们需要长字符串，例如用于多态关联的Module::SubModule::ModelName 。 我们所做的-将所有这些列的类型从varchar转换为ENUM。 Rails迁移看起来像这样：
class AddUserEnumType < ActiveRecord::Migration[5.2] disable_ddl_transaction! def up ActiveRecord::Base.connection.execute <<~SQL CREATE TYPE user_type_enum AS ENUM ( 'Module::Submodule::UserModel1', 'Module::Submodule::UserModel2', 'Module::Submodule::UserModel3' ); SQL add_column :messages, :sender_type_temp, :user_type_enum Message .in_batches(of: 10000) .update_all('sender_type_temp = sender_type::user_type_enum') safety_assured do rename_column :messages, :sender_type, :sender_type_old rename_column :messages, :sender_type_temp, :sender_type end end end
对于不熟悉Rails的人们的一些迁移说明：
- disable_ddl_transaction！ 禁用事务迁移。 这样做非常冒险，但我们希望避免长时间交易。 请确保不要在不需要迁移时禁用事务。
- 第一步，我们在PostgreSQL上创建一个新的ENUM数据类型。 ENUM的最佳功能是体积小，与varchar相比确实很小。 ENUM在添加新值时遇到一些困难，但是通常我们不经常添加新用户类型。
- 使用user_type_enum添加新列sender_type_temp
- 将值填充到新列in_batches中，以避免长时间锁定表消息
- 最后一步将旧列替换为新列。 这是最危险的步骤，因为如果将列sender_type设置为sender_type_old，但sender_type_temp未能成为sender_type，我们将会遇到很多麻烦。
- safety_assured来自gem_strongmigration ，它有助于避免在编写迁移时出错。 重命名列是不安全的操作，因此我们必须确认我们了解自己在做什么。 实际上，存在更安全但更长的方法，包括多个部署。
不用说，我们在最低活动周期内通过适当的测试运行所有类似的迁移。
我们将所有多态列转换为ENUM，在监视几天后删除了旧列，最后运行VACUUM以减少碎片。 这为我们节省了大约10％的总磁盘空间，但是
一些带有几列的表被压缩了两次！ 更重要的是-某些表开始由PostgreSQL缓存在内存中（请记住，我们不能轻易添加更多的RAM），这大大减少了所需的磁盘IO。
不要相信您的服务提供商
在文章“ 如何通过单个PostgreSQL配置更改将慢查询性能提高50倍”中发现了另一件事-我们的PostgreSQL提供程序根据请求的RAM，磁盘和CPU数量对服务器进行了自动配置，但无论出于何种原因，他们都将参数random_page_cost留给了默认值是为HDD优化的4。 他们要求我们在SSD上运行数据库，但没有正确配置PostgreSQL。 与他们联系后，我们获得了更好的执行计划：
EXPLAIN ANALYSE SELECT COUNT(*) AS count_dialog_id, dialog_id as dialog_id FROM messages WHERE sender_type = 'Module::Submodule::UserModel1' AND sender_id = 1234 GROUP BY dialog_id; db=# SET random_page_cost = 4; QUERY PLAN
移走数据
我们将一个巨大的表移至另一个数据库。 我们必须依法对系统中的每个更改进行审核，并且该要求通过gem PaperTrail实现。 该库在生产数据库中创建一个表，其中保存了受监视对象的所有更改。 我们使用库multiverse将另一个数据库实例集成到我们的Rails应用程序中。 顺便说一句-这将成为Rails 6的标准功能。有一些配置：
在文件config/database.yml描述连接
external_default: &external_default url: "<%= ENV['AUDIT_DATABASE_URL'] %>" external_development: <<: *external_default
来自另一个数据库的ActiveRecord模型的基类：
class ExternalRecord < ActiveRecord::Base self.abstract_class = true establish_connection :"external_
实现PaperTrail版本的模型：
class ExternalVersion < ExternalRecord include PaperTrail::VersionConcern end
审核模型中的用例：
class Message < ActiveRecord::Base has_paper_trail class_name: "ExternalVersion" end
总结
最终，我们在PostgreSQL实例中添加了更多的RAM，目前仅消耗了10％的可用磁盘IO。 我们幸存下来直到这一点，因为我们应用了一些技巧-生产数据库中的压缩数据，更正的配置以及不相关的数据被删除。 也许这些对您的特定情况没有帮助，但我希望本文能够提供有关自定义和简单优化的一些想法。 当然，不要忘了浏览开头列出的标准问题清单。
PS：强烈建议为psql使用出色的DBA插件 。