Большая транзакция SQL: заканчивается память на PostgreSQL, но работает на SQL Server
Я решил перенести свое приложение для демона С# (используя dotConnect как поставщик ADO.NET) из SQL Server 2008 R2 в PostgreSQL 9.0.4 x64 (в Windows Server 2008 R2). Поэтому я немного изменил все запросы, чтобы сопоставить синтаксис PostgreSQL и... застрял в поведении, которое никогда не случалось с теми же запросами на SQL Server (даже не с низкой версией Express).
Скажем, база данных содержит две очень простые таблицы без какого-либо отношения друг к другу. Они выглядят примерно так: ID, Name, Model, ScanDate, Notes. У меня есть процесс преобразования, который считывает данные по TCP/IP, обрабатывает его, запускает транзакцию и помещает результаты в вышеупомянутые 2 таблицы с использованием ванильных INSERT. Таблицы изначально пусты; нет столбцов BLOB. В плохой день около 500 000 INSERT, все они завернуты в одну транзакцию (и не могут быть разделены на несколько транзакций, кстати). Нет SELECT, UPDATE или DELETE когда-либо созданных. Пример INSERT (идентификатор - bigserial - автоматически автоинкремент):
INSERT INTO logs."Incoming" ("Name", "Model", "ScanDate", "Notes")
VALUES('Ford', 'Focus', '2011-06-01 14:12:32', NULL)
SQL Server спокойно принимает нагрузку при сохранении разумного рабочего набора ~ 200 МБ. PostgreSQL, однако, занимает дополнительно 30 МБ каждую секунду, когда транзакция выполняется (!) И быстро исчерпывает системную память.
Я сделал свой RTFM и попытался возиться с postgresql.conf: установка "work_mem" до минимума 64 кБ (это немного замедлило работу в режиме RAM), уменьшив "shared_buffers" / "temp_buffers" до минимума (без разницы), но безрезультатно. Снижение уровня изоляции транзакций до Read Uncommitted не помогло. Нет индексов, кроме индекса ID BIGSERIAL (PK). SqlCommand.Prepare() не имеет значения. Никаких параллельных соединений никогда не было установлено: daemon использует базу данных исключительно.
Может показаться, что PostgreSQL не может справиться с умопомрачительно простым INSERT-fest, в то время как SQL Server может это сделать. Может быть, PostgreSQL snapshot-vs-SQL Server блокирует разницу в изоляции? Для меня это факт: ванильный SQL Server работает, в то время как ни ванильный, ни измененный PostgreSQL не делает.
Что я могу сделать, чтобы потребление памяти PostgreSQL оставалось плоским (как это, по-видимому, имеет место с SQL Server), в то время как транзакция на основе INSERT работает?
EDIT: я создал искусственный тестовый файл:
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
);
С# (требуется 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' и не приводят к единому плану запроса внутри PostgreSQL, как в SQL Server.
Проблема остается: если вы используете Fowler Active Record dp для вставки нескольких новых объектов, готовый общий доступ к экземплярам PgSqlCommand не является элегантным.
Есть ли способ/вариант для облегчения повторного использования плана запроса с несколькими запросами, имеющими идентичную структуру, но с разными значениями аргументов?
UPDATE
Я решил посмотреть на самый простой случай - где пакет SQL запускается непосредственно на СУБД, без ADO.NET(предложенный Jordani). Удивительно, но PostgreSQL не сравнивает входящие SQL-запросы и не повторно использует внутренние компилированные планы, даже если входящий запрос имеет одинаковые одинаковые аргументы! Например, следующая партия:
PostgreSQL (через pgAdmin → Выполнять запрос) - память hogs
BEGIN TRANSACTION;
INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '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", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
INSERT INTO [dbo].sometable ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '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", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES('somename', 'somemodel', '2011-06-01 14:12:19');
-- 99998 lines of the same as above
COMMIT;
По-видимому, даже после передачи всего запроса на сервер как есть, сервер не может его оптимизировать.
Альтернативный драйвер ADO.NET
Как предположил Жордани, я попробовал драйвер NpgSql вместо dotConnect - с тем же (отсутствием) результатов. Однако источник Npgsql для метода .Prepare() содержит такие просветляющие строки:
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", "Model", "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", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $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", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $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: 0.000 ms parse npgsqlplan2: INSERT INTO public."sometable" ("Name", "Model", "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 npgsqlplan2: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $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: 0.000 ms execute npgsqlplan2: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $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: 0.000 ms parse npgsqlplan3: INSERT INTO public."sometable" ("Name", "Model", "ScanDate") VALUES($1::varchar(255), $2::varchar(255), $3::date) RETURNING "ID"
Неэффективность совершенно очевидна в этом отрывке журнала...
Выводы (такие как они)
Замечание Фрэнк о WAL - это еще одно пробуждение: что-то еще, чтобы настроить, что SQL Server скрывается от типичного разработчика MS.
NHibernate (даже в своем простом использовании) повторно использует подготовленные SqlCommands правильно... если только он использовался с самого начала...
очевидно, что существует разница в архитектуре между SQL Server и PostgreSQL, а код, специально построенный для SQL Server (и, таким образом, блаженно не осознающий возможность "невозможного повторного использования идентичного-sql" ) не будет эффективно работать PostgreSQL без основного рефакторинга. Рефакторинг 130 + старых классов ActiveRecord для повторного использования подготовленных объектов SqlCommand в беспорядочном многопоточном промежуточном программном обеспечении не является делом "just-replace-dbo-with-public".
К сожалению, для моей сверхурочной работы правильный ответ Eevar:)
Спасибо всем, кто разбил!
Ответы
Ответ 1
Я подозреваю, что вы сами это поняли. Вероятно, вы создаете 500 тыс. Различных подготовленных операторов, планов запросов и всего.
На самом деле, это хуже, чем это; подготовленные заявления живут за пределами границ транзакций и сохраняются до закрытия соединения. Злоупотребление ими, как это, истощает много памяти.
Если вы хотите выполнить запрос несколько раз, но избегайте накладных расходов на планирование для каждого выполнения, создайте один подготовленный оператор и повторно используйте это с новыми параметрами.
Если ваши запросы уникальны и ad-hoc, просто используйте обычную поддержку postgres для переменных привязки; нет необходимости в дополнительных накладных расходах из подготовленных инструкций.
Ответ 2
Сокращение work_mem и shared_buffers - не очень хорошая идея, базы данных (включая PostgreSQL) любят ОЗУ.
Но это может быть не ваша самая большая проблема, как насчет настроек WAL? wal_buffers должен быть достаточно большим, чтобы провести всю транзакцию, все 500K INSERT. Какова текущая настройка? А как насчет контрольных точек?
500k INSERT не должно быть проблемой, PostgreSQL может справиться с этим без проблем с памятью.
http://www.postgresql.org/docs/current/interactive/runtime-config-wal.html
Ответ 3
Почему?? Невозможно ли иметь внешний цикл:
cmd = conn.CreateCommand();
parm1 = cmd.CreateParameter();
parm1.ParameterName = "@name";
parm2 = cmd.CreateParameter();
parm2.ParameterName = "@model";
parm3 = cmd.CreateParameter();
parm3.ParameterName = "@scanDate";
Также я нашел это в msdn:
// NOTE:
// For optimal performance, make sure you always set the parameter
// type and the maximum size - this is especially important for non-fixed
// types such as NVARCHAR or NTEXT;
Если dotConnect не работает как поставщик SQL-сервера, это не хорошо (исправлена последняя версия/ошибка). Можете ли вы использовать другого поставщика?
Вы должны проверить, кто "ел" память - сервер или поставщик баз данных. Вы также можете протестировать PostgreSql, если вы создаете sql script и "psql.exe