[PostgreSQL] Ускоряем загрузку больших объёмов в PostgreSQL, используя COPY from STDIN binary
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
ПредисловиеЯ изучаю PostgreSQL дома и очень люблю обрабатывать большое количество данных. Пишу на ЯП C/C++ на Qt фреймворке. К сожалению Qt драйвер для постреса не поддерживает функционал, необходимый для быстрой загрузки. Поэтому я написал свою библиотеку на С++ для этого, а теперь хочу с Вами поделиться этим прекрасным методом добавления и самой библиотекой.Привет, $username ! Сегодня пойдёт речь о быстрой загрузке данных в СУБД PostgreSQL ( далее `постик` ). Делать мы это будем через механизм COPY с передачей данных по сети в бинарном формате.Первым делом рассмотрим плюсы данной методики добавления:
- Очень большая скорость добавленияВсё обуславливается тем, что мы на минимум снимаем потребность в обработке данных ( разные преобразования ), постику остаётся только проверять, правильный ли мы используем формат.
- Не теряем данные, в отличии от текстового формата.Например, как это может произойти с double числом. Нам не нужно будет в этом методе выяснять, сколько знаков до и после запятой. Данные передаются `как есть`.
В данном посте я не буду раскрывать все подробности, которые описаны в документации. Мы просто напишем лёгкий метод добавления, т.е. без специфик и прочего. Все функции, которые будут вызываться в коде — это функции из библиотеки “libpq-fe.h”. Так-же весь код будет писаться на С/С++. Алгоритм создания бинарного буфераСтруктура буфера:[шапка начала буфера]{строка данных}{строка данных}{строка данных}. . .{строка данных}[шапка конца буфера]Шапка начала буфераШапка начала буфера состоит из следующей последовательности байт:
- Сигнатура COPY-буфера
'P','G','C','O','P','Y','\n','\377','\r','\n','\0'
- Поле флагов
'\0','\0','\0','\0'
В случае, если мы включаем в данные OID – выставляем 16-й бит в 1
- Длина области расширения заголовка
'\0','\0','\0','\0'
В настоящее время заголовок не используется, а длинна их должна быть равна нулю. Сделано это для того, чтобы в будущем не было проблем с совместимостью буферов.
- Расширение заголовкаПоскольку в текущих версиях этого расширения нет ( на данный момент 13.1 последняя версия ), то ничего и не пишем.
Строка данныхСтрока данных состоит из:
- Длинна записи
Это int16_t число, указывающее, сколько столбцов будет добавляется в текущем столбце. На данное время это число постоянно одно и то же, в будущем, возможно, будут изменения.
- После этого следует указанное количество данных о столбцах:1) Длинна данныхУказываем, сколько байт занимают данные, которые нужно добавить в текущий столбец2) Непосредственно сами данные
Хотелось бы тут заметить, что разные типы данных могут добавляются совсем по другому:Например массивы. Чтобы добавить массив int64_t нужно вставлять не просто данные, а их сигнатуру. ( там идёт своя шапка данных и описание массива ). К сожалению, описание добавления для каждого типа не описано в док-ции постреса. Поэтому, чтобы узнать сигнатуру массива надо или смотреть в исходники или создавать дамп через COPY TOи уже оттуда смотреть, что и как лежит.Шапка конца буфера
0xff, 0xff
Для дополнительной синхронизации передачи буфер необходимо закрыть. Таким образом, когда постик будет просматривать новую длину строки данных — увидит, что достигнут конец буфера (ну данные ведь не могут быть длинной -1).Алгоритм добавления данных
- Для того, чтобы подключиться к серверу — создадим подключение
string conninfo =
"host=127.0.0.1 port=5432 dbname=postgres user=postgres password=postgres connect_timeout=10";
PGconn *conn = PQconnectdb(conninfo.c_str());
// conninfo - это срока параметрами подключения ( connect_timeout измеряется в секундах )
- Подготовим COPY-запрос
pg_result *res = PQexec(conn, cquery);
в качестве cquery у нас выступает COPY запрос. В моём примере это COPY testtable5 ( col1, col2, col3, col4 ) FROM STDIN (format binary);Добавим буфер
PQputCopyData(conn, buf, currentSize);
, где buf – указатель на буфер, currentSize — длинна буфера в байтах.Очень важно то, что буфер можно передавать частями. Это очень удобно. Я лично использую буферы по 2-128 Мб.
- Скажем серверу, что это конец данных
PQputCopyEnd(conn, NULL);
Очень важно! Все данные должны передаваться в сетевом формате. Что это значит? Например, int16_t tmp = 2; На самом деле в оперативке данные будут лежать так: 0x02, 0x00 А не так как мы привыкли 0x00, 0x02. Это связано с архитектурой процессора. Процессоры архитектуры SPARC хранят данные уже в сетевом формате. Поэтому, если у вас не SPARC-архитектура, нужно все байты вставлять в буфер задом на перёд ( за исключением строк ) Немного графиковЯ сделал вторую программу для добавления в БД строк данных.
Написано это было на Qt:
db.open();
QSqlQuery query(db);
query.prepare("insert into testtable5 ( col1, col2, col3, col4 ) values (?,?,?,?);");
for(int i=0; i<20000000; i++)
{
query.addBindValue("column1");
query.addBindValue(double(12983712987.4383453947384734853872837));
query.addBindValue(int(12345678));
query.addBindValue(float(123.4567));
query.exec();
}
Нижеприведённые графики будут показывать время добавления ( в мс ) следующих 10.000 данных - по оси Y, кол-во добавленных данных - по оси X.Сравнительный график скорости работы COPY и INSERT запросов.Красным - INSERT-вставка в постоянную таблицу.Зелёный - INSERT-вставка во временную таблицу.Синий- COPY-вставка в постоянную таблицу.Жёлтый - COPY-вставка во временную таблицу.
Сравнительный график скорости работы INSERT запросов.Жёлтый - INSERT-вставка в постоянную таблицу.Синий- INSERT-вставка во временную таблицу.
Сравнительный график скорости работы COPY запросов.Синий- COPY-вставка в постоянную таблицу.Жёлтый - COPY-вставка во временную таблицу.
Немного расчётовДавайте посмотрим на графики и попробуем найти средние показатели добавления.Вот что у меня получается:Чтобы добавить порцию из 10.000 данных ( строк ), мне требуется:12.620 мс на добавление в постоянную таблицу при помощи INSERT12.050 мс на добавление во временную таблицу при помощи INSERT150 мс на добавление в постоянную таблицу при помощи COPY 120 мс на добавление во временную таблицу при помощи COPY Тут хотелось бы остановиться сразу и сказать... Что я не смог замерить время коммита запроса COPY. Думаю, оно не сильно будет играть роль.Исходный кодПоскольку библиотеку я писал в основном под свои нужды — она имеет ограниченный функционал. В будущем, надеюсь, что мне удастся довести библиотеку до того уровня, чтобы каждый мог использовать её в своих продуктах.Ссылка на проект на GitHubСсылка на документацию по использованию COPYP.S.: это мой первый пост, прошу меня простить за кривизну моих рук. Хотелось бы услышать хороших комментариев и конструктивной критики.
===========
Источник:
habr.com
===========
Похожие новости:
- [Ненормальное программирование, JavaScript, Google Chrome, PDF] Пугающие эксперименты с PDF: запускаем «Арканоид» в документе (перевод)
- [PostgreSQL] Postgresso 27
- [PostgreSQL, DevOps] Заряжай Patroni. Тестируем Patroni + Zookeeper кластер (Часть вторая)
- [PostgreSQL, DevOps] Заряжай Patroni. Тестируем Patroni + Zookeeper кластер (Часть первая)
- [Информационная безопасность, Совершенный код, Управление продуктом, Софт] Строим безопасную разработку в ритейлере. Итоги одного большого проекта
- [PostgreSQL] «Ростелеком» покупает отечественную СУБД Postgres Pro на 257 миллионов
- [PostgreSQL] PostgreSQL 14: Часть 3 или «ноябрьское затишье» (Коммитфест 2020-11)
- [Oracle, PostgreSQL, Java, Microsoft SQL Server, Администрирование баз данных] varchar2 и Unicode для тех, кто ничего не понимает в базах данных Oracle или ORA-12899: value too large for column
- [Oracle, PostgreSQL, Microsoft SQL Server, Администрирование баз данных] «Росатом» массово меняет СУБД Oracle и Microsoft на российскую Postgres Pro
- [Информационная безопасность, Криптография, Open source, Законодательство в IT] Немецкий суд заставил почтового провайдера Tutanota установить бэкдор
Теги для поиска: #_postgresql, #_post, #_uskorenie_zagruzki (ускорение загрузки), #_libpq, #_postgresql
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 23-Ноя 04:46
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
ПредисловиеЯ изучаю PostgreSQL дома и очень люблю обрабатывать большое количество данных. Пишу на ЯП C/C++ на Qt фреймворке. К сожалению Qt драйвер для постреса не поддерживает функционал, необходимый для быстрой загрузки. Поэтому я написал свою библиотеку на С++ для этого, а теперь хочу с Вами поделиться этим прекрасным методом добавления и самой библиотекой.Привет, $username ! Сегодня пойдёт речь о быстрой загрузке данных в СУБД PostgreSQL ( далее `постик` ). Делать мы это будем через механизм COPY с передачей данных по сети в бинарном формате.Первым делом рассмотрим плюсы данной методики добавления:
'P','G','C','O','P','Y','\n','\377','\r','\n','\0'
'\0','\0','\0','\0'
'\0','\0','\0','\0'
0xff, 0xff
string conninfo =
"host=127.0.0.1 port=5432 dbname=postgres user=postgres password=postgres connect_timeout=10"; PGconn *conn = PQconnectdb(conninfo.c_str()); // conninfo - это срока параметрами подключения ( connect_timeout измеряется в секундах )
pg_result *res = PQexec(conn, cquery);
PQputCopyData(conn, buf, currentSize);
PQputCopyEnd(conn, NULL);
Написано это было на Qt: db.open();
QSqlQuery query(db); query.prepare("insert into testtable5 ( col1, col2, col3, col4 ) values (?,?,?,?);"); for(int i=0; i<20000000; i++) { query.addBindValue("column1"); query.addBindValue(double(12983712987.4383453947384734853872837)); query.addBindValue(int(12345678)); query.addBindValue(float(123.4567)); query.exec(); }  Сравнительный график скорости работы INSERT запросов.Жёлтый - INSERT-вставка в постоянную таблицу.Синий- INSERT-вставка во временную таблицу.  Сравнительный график скорости работы COPY запросов.Синий- COPY-вставка в постоянную таблицу.Жёлтый - COPY-вставка во временную таблицу.  Немного расчётовДавайте посмотрим на графики и попробуем найти средние показатели добавления.Вот что у меня получается:Чтобы добавить порцию из 10.000 данных ( строк ), мне требуется:12.620 мс на добавление в постоянную таблицу при помощи INSERT12.050 мс на добавление во временную таблицу при помощи INSERT150 мс на добавление в постоянную таблицу при помощи COPY 120 мс на добавление во временную таблицу при помощи COPY Тут хотелось бы остановиться сразу и сказать... Что я не смог замерить время коммита запроса COPY. Думаю, оно не сильно будет играть роль.Исходный кодПоскольку библиотеку я писал в основном под свои нужды — она имеет ограниченный функционал. В будущем, надеюсь, что мне удастся довести библиотеку до того уровня, чтобы каждый мог использовать её в своих продуктах.Ссылка на проект на GitHubСсылка на документацию по использованию COPYP.S.: это мой первый пост, прошу меня простить за кривизну моих рук. Хотелось бы услышать хороших комментариев и конструктивной критики. =========== Источник: habr.com =========== Похожие новости:
|
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 23-Ноя 04:46
Часовой пояс: UTC + 5