PostgreSQL后端进程高内存使用问题

Question

我们正在评估使用PostgreSQL来实现多租户数据库，目前我们正在对单个数据库多个模式模型进行一些测试（基本上，所有租户在同一个数据库下拥有自己的模式，这些模式下有相同的数据库对象集合）。应用程序将维护一个连接池，该池将被所有租户/模式共享。

例如，如果数据库有500个租户/模式，每个租户都有200个表/视图，则表/视图的总数将为500 * 200 = 100,000。

由于连接池将被所有租户使用，因此最终每个连接都会访问所有表/视图。

在我们的测试中，当连接访问更多的视图时，我们发现后端进程的内存使用量增长非常快，并且其中大部分是私有内存。这些内存将保持到连接关闭。

我们有一个测试用例，其中一个后端进程使用了30GB以上的内存，并最终出现了内存不足错误。

为了帮助理解问题，我编写了代码来创建简化的测试用例 - MTDB_destroy：用于清除租户模式 - MTDB_Initialize：用于创建多租户数据库 - MTDB_RunTests：简化的测试用例，基本上逐个从所有租户视图中选择。

我所做的测试是在CentOS 5.4上的PostgreSQL 9.0.3上进行的。

为了确保我有一个干净的环境，我重新创建了数据库群集，并将大多数配置留在默认状态下， （唯一必须更改的是增加“max_locks_per_transaction”，因为MTDB_destroy需要删除许多对象。）

这就是我重现问题所做的操作：

1. 创建一个新的数据库
2. 使用附带的代码创建三个函数

3. 连接到新创建的数据库并运行初始化脚本

   -- 初始化

   select MTDB_Initialize('tenant', 100, 100, true);

   -- 不确定vacuum analyze在这里是否有用，我只是运行它

   vacuum analyze;

   -- 检查已创建的表/视图

   select table_schema, table_type, count(*) from information_schema.tables where table_schema like 'tenant%' group by table_schema, table_type order by table_schema, table_type;

4. 打开另一个连接到新创建的数据库并运行测试脚本

   -- 获取当前连接的后端进程ID

   SELECT pg_backend_pid();

   -- 打开Linux控制台并运行ps -p并观察VIRT、RES和SHR

   -- 运行测试

   select MTDB_RunTests('tenant', 1);

观察结果：

1. 在首次创建运行测试的连接时，

   VIRT = 182MB，RES = 6240K，SHR = 4648K。

2. 经过一次测试后（共175秒），

   VIRT = 1661MB，RES = 1.5GB，SHR = 55MB。

3. 再次运行测试（共167秒），

   VIRT = 1661MB，RES = 1.5GB，SHR = 55MB。

4. 重复运行测试（共165秒），

   VIRT = 1661MB，RES = 1.5GB，SHR = 55MB。

随着表数量的增加，测试中的内存使用量也会增加。

有人能帮忙解释一下这里到底发生了什么吗？ 是否有办法控制PostgreSQL后端进程的内存使用情况？

谢谢。

Samuel

-- MTDB_destroy
create or replace function MTDB_destroy (schemaNamePrefix varchar(100))
returns int as $$
declare
   curs1 cursor(prefix varchar) is select schema_name from information_schema.schemata where schema_name like prefix || '%';
   schemaName varchar(100);
   count integer;
begin
   count := 0;
   open curs1(schemaNamePrefix);
   loop
      fetch curs1 into schemaName;
      if not found then exit; end if;           
      count := count + 1;
      execute 'drop schema ' || schemaName || ' cascade;';
   end loop;  
   close curs1;
   return count;
end $$ language plpgsql;

-- MTDB_Initialize
create or replace function MTDB_Initialize (schemaNamePrefix varchar(100), numberOfSchemas integer, numberOfTablesPerSchema integer, createViewForEachTable boolean)
returns integer as $$
declare   
   currentSchemaId integer;
   currentTableId integer;
   currentSchemaName varchar(100);
   currentTableName varchar(100);
   currentViewName varchar(100);
   count integer;
begin
   -- clear
   perform MTDB_Destroy(schemaNamePrefix);

   count := 0;
   currentSchemaId := 1;
   loop
      currentSchemaName := schemaNamePrefix || ltrim(currentSchemaId::varchar(10));
      execute 'create schema ' || currentSchemaName;

      currentTableId := 1;
      loop
         currentTableName := currentSchemaName || '.' || 'table' || ltrim(currentTableId::varchar(10));
         execute 'create table ' || currentTableName || ' (f1 integer, f2 integer, f3 varchar(100), f4 varchar(100), f5 varchar(100), f6 varchar(100), f7 boolean, f8 boolean, f9 integer, f10 integer)';
         if (createViewForEachTable = true) then
            currentViewName := currentSchemaName || '.' || 'view' || ltrim(currentTableId::varchar(10));
            execute 'create view ' || currentViewName || ' as ' ||
                     'select t1.* from ' || currentTableName || ' t1 ' ||
             ' inner join ' || currentTableName || ' t2 on (t1.f1 = t2.f1) ' ||
             ' inner join ' || currentTableName || ' t3 on (t2.f2 = t3.f2) ' ||
             ' inner join ' || currentTableName || ' t4 on (t3.f3 = t4.f3) ' ||
             ' inner join ' || currentTableName || ' t5 on (t4.f4 = t5.f4) ' ||
             ' inner join ' || currentTableName || ' t6 on (t5.f5 = t6.f5) ' ||
             ' inner join ' || currentTableName || ' t7 on (t6.f6 = t7.f6) ' ||
             ' inner join ' || currentTableName || ' t8 on (t7.f7 = t8.f7) ' ||
             ' inner join ' || currentTableName || ' t9 on (t8.f8 = t9.f8) ' ||
             ' inner join ' || currentTableName || ' t10 on (t9.f9 = t10.f9) ';                    
         end if;
         currentTableId := currentTableId + 1;
         count := count + 1;
         if (currentTableId > numberOfTablesPerSchema) then exit; end if;
      end loop;   

      currentSchemaId := currentSchemaId + 1;
      if (currentSchemaId > numberOfSchemas) then exit; end if;     
   end loop;
   return count;
END $$ language plpgsql;

-- MTDB_RunTests
create or replace function MTDB_RunTests(schemaNamePrefix varchar(100), rounds integer)
returns integer as $$
declare
   curs1 cursor(prefix varchar) is select table_schema || '.' || table_name from information_schema.tables where table_schema like prefix || '%' and table_type = 'VIEW';
   currentViewName varchar(100);
   count integer;
begin
   count := 0;
   loop
      rounds := rounds - 1;
      if (rounds < 0) then exit; end if;

      open curs1(schemaNamePrefix);
      loop
         fetch curs1 into currentViewName;
         if not found then exit; end if;
         execute 'select * from ' || currentViewName;
         count := count + 1;
      end loop;
      close curs1;
   end loop;
   return count;  
end $$ language plpgsql;

- Samuel

---

我理解的是否正确，只有在保持连接打开状态时才会增加内存使用量？你不能在每次切换模式时关闭并重新打开连接来避免这种情况吗？ - Frank Farmer

是的，如果我们关闭连接，分配给进程的内存将返回到操作系统。然而，这也不是理想的情况。连接池的想法是减少与数据库服务器的并发连接数，并消除连接/断开连接的成本（如果我们必须为每个请求执行此操作，则成本很容易变得相当大）。 - Samuel

Answer 1

这些连接是处于事务空闲还是仅仅是空闲？听起来像是未完成的事务正在占用内存，或者你可能有内存泄漏之类的问题。

- Scott Marlowe

---

这里的测试用例是在整个测试期间通过同一单个连接发送查询。 - Samuel

这里没有显式的事务，因此它正在运行隐式事务模式，即这里不应该有未关闭的事务问题。 - Samuel

感觉不像是内存泄漏，因为如果我们反复运行测试，内存使用量并没有持续增长。更像是某种缓存，只是我不知道是否有一种方法来控制总内存使用量，以防止它吃掉数据库服务器上的所有内存(物理+虚拟)。 - Samuel

那么，如果连接已打开，如果您从另一个终端psql到数据库中，使用“select * from pg_stat_activity;”会看到什么？有任何空闲事务吗？ - Scott Marlowe

我看到两行代码，一行是 select * from pg_stat_activity，另一行是 select MTDB_RunTests('tenant', 1)。 - Samuel

请注意，函数内的所有内容都作为一个事务运行。只要MTDB_RunTests正在运行，就会打开一个事务。 - Scott Marlowe

Answer 2

对于在搜索中看到此线程的人（就像我一样），我发现了一个在不同上下文中似乎是相同问题。空闲进程缓慢地消耗越来越多的内存，直到OOM killer将它们关闭（导致周期性的数据库崩溃）。

我们追踪问题到长时间运行的PHP脚本，这些脚本保持一个连接打开很长时间。我们能够通过定期关闭连接并重新连接来控制内存。

从我所读的内容来看，Postgres进行了大量的缓存，因此如果您有一个会话访问许多不同的表/查询，则此缓存数据可能会继续增长。

-肯

- Ken Koch