Большая транзакция SQL: заканчивается память на PostgreSQL, но работает на SQL Server
Я решил переместить мое приложение-демон C # (используя dotConnect в качестве поставщика ADO.NET) с SQL Server 2008 R2 на PostgreSQL 9.0.4 x64 (на Windows Server 2008 R2). Поэтому я немного изменил все запросы, чтобы они соответствовали синтаксису PostgreSQL, и ... застрял в поведении, которого никогда не было с теми же запросами на SQL Server (даже в простой версии Express).
Допустим, база данных содержит 2 очень простых таблицы без какое-либо отношение друг к другу. Они выглядят примерно так: ID, Name, Model, ScanDate, Notes. У меня есть процесс преобразования, который считывает данные через TCP / IP, обрабатывает их, запускает транзакцию и помещает результаты в вышеупомянутые 2 таблицы с использованием ванильных INSERT. Таблицы изначально пусты; нет столбцов BLOB. В плохой день происходит около 500000 INSERT, все они заключены в одну транзакцию (и не могут быть разделены на несколько транзакций, кстати). Никакие операции SELECT, UPDATE или DELETE никогда не выполняются. Пример INSERT (ID - 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 () не имеет значения. Никаких одновременных соединений никогда не устанавливается: демон использует исключительно базу данных.
Может показаться, что PostgreSQL не может справиться с умопомрачительно простым INSERT-fest, в то время как SQL Server может это сделать. Может быть, разница в изоляции блокировок PostgreSQL и SQL Server? Для меня это факт: обычный SQL Server работает, в то время как ни ванильный, ни измененный PostgreSQL не работают.
Что я могу сделать, чтобы потребление памяти PostgreSQL оставалось неизменным (как, по-видимому, в случае с SQL Server), пока выполняется транзакция на основе INSERT ?
РЕДАКТИРОВАТЬ: Я создал искусственный тестовый сценарий:
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, память не увеличивается! По-видимому, подготовка нескольких PgSqlCommand с ИДЕНТИЧНЫМ SQL, но с разными значениями параметров не приводит к , а не к единому плану запроса внутри PostgreSQL, как это происходит в SQL Server.
Проблема остается: если используется Active Record dp Фаулера для вставки нескольких новых объектов подготовленное совместное использование экземпляра PgSqlCommand не изящно.
Есть ли способ / вариант облегчить повторное использование плана запроса с несколькими запросами, имеющими одинаковую структуру, но с разными значениями аргументов?
ОБНОВЛЕНИЕ
Я решил Рассмотрим простейший возможный случай - когда пакет SQL запускается непосредственно в СУБД, без ADO.NET (предложено Джордани). Удивительно, но PostgreSQL не сравнивает входящие запросы SQL и не использует повторно внутренние скомпилированные планы - даже если входящий запрос имеет одинаковые аргументы! Например, следующий пакет:
PostgreSQL (через pgAdmin -> Выполнить запрос) - поглощает память
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 -> Выполнить) - сохраняет использование памяти на одном уровне
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 (и, таким образом, в блаженном неведении о «невозможности повторного использования- Identity-sql ') не будет эффективно работать в PostgreSQL без серьезного рефакторинга. И рефакторинг более 130 устаревших классов ActiveRecord для повторного использования подготовленных объектов SqlCommand в беспорядочном многопоточном промежуточном программном обеспечении не является делом типа «просто замените dbo-with-public».
К сожалению, для моей сверхурочной работы, ответ Ивара правильный :)
Спасибо всем, кто участвовал!