大型SQL事务：在PostgreSQL上运行内存不足,但在SQL Server上运行

发布时间：2020-12-12 16:28:10 所属栏目：MsSql教程来源：网络整理

导读：我决定将我的C#守护程序应用程序(使用dotConnect作为ADO.NET提供程序)从SQL Server 2008 R2移到PostgreSQL 9.0.4 x64(在 Windows Server 2008 R2上).因此,我略微修改了所有查询,以匹配PostgreSQL语法和…被困在SQL Server上甚至没有发生同样的查询的行为(甚至

我决定将我的C#守护程序应用程序(使用dotConnect作为ADO.NET提供程序)从SQL Server 2008 R2移到PostgreSQL 9.0.4 x64(在 Windows Server 2008 R2上).因此,我略微修改了所有查询,以匹配PostgreSQL语法和…被困在SQL Server上甚至没有发生同样的查询的行为(甚至不在低速版本上).

我们假设数据库包含两个非常简单的表,彼此之间没有任何关系.他们看起来像这样：ID,Name,Model,ScanDate,Notes.我有一个转换过程,通过TCP / IP读取数据,处理它,启动一个事务,并使用vanilla INSERT将结果放入上述的2个表中.表最初是空的没有BLOB列.在糟糕的一天有大约500.000个INSERT,全部包装在单个事务中(并且不能被拆分成多个事务btw).没有选择,更新或删除. INSERT的一个例子(ID是bigserial – 自动增量)：

INSERT INTO logs."Incoming" ("Name","Model","ScanDate","Notes")
VALUES('Ford','Focus','2011-06-01 14:12:32',NULL)

SQL Server平静接受负载,同时保持约200 MB的合理工作集.然而,PostgreSQL每秒需要额外的30 MB(！),并迅速耗尽系统RAM.

我已经完成了我的RTFM,并尝试使用postgresql.conf：将“work_mem”设置为最小64 kB(这稍微减慢了RAM占用),将“shared_buffers”/“temp_buffers”减少到最小(无差异),但是无济于事将事务隔离级别降低为Read Uncommitted读取没有帮助.除了ID BIGSERIAL(PK)之外没有索引. SqlCommand.Prepare()没有区别.没有建立并发连接：守护程序专门使用数据库.

似乎PostgreSQL无法应对令人难以置信的简单的INSERT-fest,而SQL Server可以做到这一点.也许这是一个PostgreSQL快照vs-SQL Server锁隔离差异？这对我来说是一个事实：香草SQL Server的工作原理,既不是香草也不是调整PostgreSQL.

当基于INSERT的事务运行时,我可以做什么来使PostgreSQL内存消耗保持平坦(在SQL Server的情况下显然是这样)？

编辑：我创建了一个人造测试用例：

DDL：

CREATE TABLE sometable
(
  "ID" bigserial NOT NULL,"Name" character varying(255) NOT NULL,"Model" character varying(255) NOT NULL,"ScanDate" date NOT NULL,CONSTRAINT "PK" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);

C#(需要Devart.Data.dll& Devart.Data.PostgreSql.dll)

PgSqlConnection conn = new PgSqlConnection("Host=localhost; Port=5432; Database=testdb; UserId=postgres; Password=###########");
conn.Open();
PgSqlTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted);

for (int ii = 0; ii < 300000; ii++)
{
    PgSqlCommand cmd = conn.CreateCommand();
    cmd.Transaction = tx;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "INSERT INTO public."sometable" ("Name","Model","ScanDate") VALUES(@name,@model,@scanDate) RETURNING "ID"";
    PgSqlParameter parm = cmd.CreateParameter();
    parm.ParameterName = "@name";
    parm.Value = "SomeName";
    cmd.Parameters.Add(parm);

    parm = cmd.CreateParameter();
    parm.ParameterName = "@model";
    parm.Value = "SomeModel";
    cmd.Parameters.Add(parm);

    parm = cmd.CreateParameter();
    parm.ParameterName = "@scanDate";
    parm.PgSqlType = PgSqlType.Date;
    parm.Value = new DateTime(2011,6,1,14,12,13);
    cmd.Parameters.Add(parm);

    cmd.Prepare();

    long newID = (long)cmd.ExecuteScalar();
}

tx.Commit();

这会重新创建内存占用.然而,如果创建’cmd’变量,并且.Prepare()d在FOR循环之外,内存不会增加！显然,使用IDENTICAL SQL准备多个PgSqlCommands但不同的参数值不会导致PostgreSQL中的单个查询计划,就像在SQL Server中一样.

问题依然存在：如果使用Fowler的Active Record dp插入多个新对象,则准备好的PgSqlCommand实例共享并不优雅.

有没有方法/选项来促进查询计划重用,具有相同结构但不同参数值的多个查询？

UPDATE

我决定看一下最简单的情况 – 一个SQL批处理直接在DBMS上运行,没有ADO.NET(由Jordani建议).令人惊讶的是,PostgreSQL不会比较传入的SQL查询,也不会重用内部编译的计划 – 即使传入查询具有相同的相同参数！例如,以下批次：

PostgreSQL(通过pgAdmin – >执行查询) – 内存

BEGIN TRANSACTION;

INSERT INTO public."sometable" ("Name","ScanDate") VALUES('somename','somemodel','2011-06-01 14:12:19');
INSERT INTO public."sometable" ("Name",'2011-06-01 14:12:19');
-- the same INSERT is repeated 100.000 times

COMMIT;

SQL Server(通过Management Studio – > Execute) – 保持内存使用率不变

BEGIN TRANSACTION;

INSERT INTO [dbo].sometable ("Name",'2011-06-01 14:12:19');
INSERT INTO [dbo].sometable ("Name",'2011-06-01 14:12:19');
-- the same INSERT is repeated 100.000 times

COMMIT;

和PostgreSQL日志文件(谢谢,Sayap！)包含：

2011-06-05 16:06:29 EEST LOG:  duration: 0.000 ms  statement: set client_encoding to 'UNICODE'
2011-06-05 16:06:43 EEST LOG:  duration: 15039.000 ms  statement: BEGIN TRANSACTION;

INSERT INTO public."sometable" ("Name",'2011-06-01 14:12:19');
-- 99998 lines of the same as above
COMMIT;

显然,即使在将整个查询以原样传输到服务器之后,服务器也无法对其进行优化.

ADO.NET驱动替代

正如Jordani所建议的,我尝试使用NpgSql驱动程序,而不是dotConnect – 具有相同的(缺少)结果.但是,.Prepare()方法的Npgsql源包含如此启发性的行：

planName = m_Connector.NextPlanName();
String portalName = m_Connector.NextPortalName();
parse = new NpgsqlParse(planName,GetParseCommandText(),new Int32[] { });
m_Connector.Parse(parse);

日志文件中的新内容：

2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2011-06-05 15:25:26 EEST LOG:  duration: 1.000 ms  parse npgsqlplan1: INSERT INTO public."sometable" ("Name","ScanDate") VALUES($1::varchar(255),$2::varchar(255),$3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  bind npgsqlplan1: INSERT INTO public."sometable" ("Name",$3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST DETAIL:  parameters: $1 = 'SomeName',$2 = 'SomeModel',$3 = '2011-06-01'
2011-06-05 15:25:26 EEST LOG:  duration: 1.000 ms  execute npgsqlplan1: INSERT INTO public."sometable" ("Name",$3 = '2011-06-01'
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  parse npgsqlplan2: INSERT INTO public."sometable" ("Name",$3::date) RETURNING "ID"
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  bind npgsqlplan2: INSERT INTO public."sometable" ("Name",$3 = '2011-06-01'
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  execute npgsqlplan2: INSERT INTO public."sometable" ("Name",$3 = '2011-06-01'
2011-06-05 15:25:26 EEST LOG:  duration: 0.000 ms  parse npgsqlplan3: INSERT INTO public."sometable" ("Name",$3::date) RETURNING "ID"

在这个日志摘录中效率低下很明显…

结论(如它们)

弗兰克关于WAL的注释是另一个觉醒：还有一件事情要配置SQL Server隐藏在典型的MS开发人员身上.

NHibernate(即使在最简单的用法)正确地重用准备好的SqlCommands …如果只是从一开始就使用…

很明显,SQL Server和PostgreSQL之间存在架构上的差异,而专门为SQL Server构建的代码(从而令人愉快地不了解“不能重复使用相同的sql”可能性)在PostgreSQL上无效,而没有主重构.并重构130个传统的ActiveRecord类,以便在一个凌乱的多线程中间件中重新使用准备好的SqlCommand对象不是一个’只是替换 – 与公开’类型的事情.

不幸的是我的加班,Eevar的答案是正确的:)

感谢所有投入的人！

解决方法

我怀疑你自己弄清楚了.你可能创建了500k种不同的准备语句,查询计划和所有.
其实比这更糟糕准备好的语句生效在事务边界之外,并持续到连接关闭.像这样滥用他们会消耗大量的记忆力.

如果要执行多次查询,但是避免每个执行的计划开销,请创建一个准备好的语句,并重新使用新的参数.

如果您的查询是独一无二的,特殊的,只需使用postgres对绑定变量的常规支持;不需要预备语句的额外开销.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!