PostgreSQL后端处理内存使用率高的问题

Question

我们正在使用PostgreSQL进行评估，以实现多租户数据库，目前我们正在对单个数据库-多模式模型(基本上，所有租户在同一数据库中有相同的数据库对象集)上运行一些测试。应用程序将维护将在所有租户/模式之间共享的连接池。

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

由于连接池将由所有租户使用，最终每个连接都将命中所有表/视图。

在我们的测试中，当连接碰到更多的视图时，我们发现后端进程的内存使用量增加得相当快，而且大多数都是私有内存。这些内存将一直保持到连接关闭为止。

我们有一个测试用例，一个后端进程使用更多的30GB内存，最终得到内存不足的错误。

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

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

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

这就是我要重复的问题：

database

• create

• 创建一个新的，这三个函数使用代码attached

1. connect到新创建的db并运行初始化脚本

-初始化

选择MTDB_Initialize(“租户”，100,100，true)；

--不确定真空分析在这里是否有用，我只是做了一下

真空分析；

-检查创建的表/视图

从table_type;

• open中选择table_schema、table_type、count(*)，其中table_schema像table_schema的'tenant%‘组、table_schema的table_type order、与新创建的db的另一个连接并运行测试脚本

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

选择pg_backend_pid()；

-打开linux控制台，运行ps -p并观看VIRT、RES和SHR

-运行测试

选择MTDB_RunTests(“租户”，1);

意见：

当首次创建用于运行测试的连接时，

1. ，

VIRT =182 175，RES =6240 K，SHR=4648K

• after只运行一次测试(花费175秒)

VIRT =1661 re=1.5GBSHR=55 re

• 重新运行测试(耗时167秒)

VIRT =1661 re=1.5GBSHR=55 re

• 重新运行测试(耗时165秒)

VIRT =1661 SHR = 1.5GB SHR=55 SHR

随着我们扩大表的数量，内存的使用也会在测试中增加。

有人能帮我解释一下这里发生了什么吗？有什么方法可以控制PostgreSQL后端进程的内存使用？

谢谢。

塞缪尔

-- 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;

Answer 1

这些连接在事务中是空闲的还是只是空闲的？听起来好像还没有完成的事务一直保存在内存中，或者可能是内存泄漏什么的。

Answer 2

对于在搜索时看到这个线程的人(如我所做的)，我在不同的上下文中发现了相同的问题。空闲进程慢慢地消耗越来越多的内存，直到OOM杀手取出它们(导致周期性的DB崩溃)。

我们将问题追溯到运行了很长时间的PHP脚本，该脚本使一个连接打开了很长时间。通过定期关闭连接和重新连接，我们能够控制内存。

据我所读，postgres进行了大量的缓存，因此，如果您有一个会话访问了许多不同的表/查询，那么这个缓存数据可以继续增长和增长。

-Ken