PostgreSQL后端处理高内存使用问题
我们正在评估使用PostgreSQL实现多租户数据库,
目前,我们正在对单数据库多模式模型进行一些测试 (基本上,所有租户在同一数据库中拥有相同的数据库对象集,然后在自己的模式下). 应用程序将维护一个将在所有租户/模式之间共享的连接池. 例如如果数据库有500个租户/模式,每个租户有200个表/视图, 由于连接池将由所有租户使用,因此最终每个连接都将命中所有表/视图. 在我们的测试中,当连接访问更多视图时,我们发现后端进程的内存使用量增长非常快,并且大多数都是私有内存. 我们有一个测试用例,一个后端进程使用更多的30GB内存,最终得到内存不足错误. 为了帮助理解这个问题,我编写了代码来创建简化的测试用例 我所做的测试是在CentOS 5.4上的PostgreSQL 9.0.3上进行的 为了确保我有一个干净的环境,我重新创建了数据库集群并将大多数配置保留为默认值, 这就是我重现这个问题的方法: >创建一个新数据库 – 初始化 选择MTDB_Initialize(‘tenant’,100,true); – 不确定真空分析在这里是否有用,我只是运行它 真空分析; – 检查创建的表/视图 从information_schema.tables中选择table_schema,table_type,count(*),其中table_schema类似于’tenant%’group by table_schema,table_type order by table_schema,table_type; – 获取当前连接的后端进程ID SELECT pg_backend_pid(); – 打开一个linux控制台并运行ps -p并观看VIRT,RES和SHR – 运行测试 选择MTDB_RunTests(‘tenant’,1); 观察: >首次创建运行测试的连接时, VIRT = 182MB,RES = 6240K,SHR = 4648K VIRT = 1661MB RES = 1.5GB SHR = 55MB VIRT = 1661MB RES = 1.5GB SHR = 55MB VIRT = 1661MB RES = 1.5GB SHR = 55MB 当我们扩大表的数量时,内存使用量也会增加. 谁能帮忙解释一下这里发生了什么? 谢谢. 塞缪尔 -- 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;
这些连接在事务中是空闲还是空闲?听起来像未完成的事务正在持有内存,或者你可能有内存泄漏或其他什么.
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |