|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Есть таблица с парой индексов. В нее идет интенсивная вставка в среднем по 20 записей за раз (в ХП передается массив и потом Insert из select from unnset(array)). Если индексы убрать, то 2млн записей вставляются за 25сек (на рабочей машине, не сервер). С индексами - 40сек. Разница ощутимая, но индексы нужны. Вопрос. Как можно ускорить? Есть ли в Postgre IOT? (нашел только CLUSTER, но так понимаю, что в БД будет все равно два объекта индекс и таблица и в общем скорость вставки в CLUSTER-ed table получается такая же, как и в варианте таблица + индексы). Есть ли в Postgre хинты? (что-нибудь типа append) ... |
|||
:
Нравится:
Не нравится:
|
|||
29.02.2020, 18:02 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS Есть таблица с парой индексов. В нее идет интенсивная вставка в среднем по 20 записей за раз (в ХП передается массив и потом Insert из select from unnset(array)). Если индексы убрать, то 2млн записей вставляются за 25сек (на рабочей машине, не сервер). С индексами - 40сек. Разница ощутимая, но индексы нужны. Вопрос. Как можно ускорить? Есть ли в Postgre IOT? (нашел только CLUSTER, но так понимаю, что в БД будет все равно два объекта индекс и таблица и в общем скорость вставки в CLUSTER-ed table получается такая же, как и в варианте таблица + индексы). Есть ли в Postgre хинты? (что-нибудь типа append) Это нормальный overhead на работу с индексами. Ничего с этим сделать не получится. А вот как ускорить уже вопрос интереснее. Попробуйте замерять время вставки тех же 2М записей через COPY FROM FILE предназначеном для батч операций (без хранимок, insert и прочего). Тогда будет понянее предельная скорость достижимая на вашей конкретной рабочей машине и на конкретной настройке базы. После чего можно будет думать о параметрах железа, настройке базы и тд (там тоже легко 2-3 раза скорости выйграть можно). ... |
|||
:
Нравится:
Не нравится:
|
|||
29.02.2020, 20:19 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Maxim Boguk , сравнил. Insert 2млн записей Наличие индексовInsert по 20 записей (сек)Insert по 200 записей (сек)Copy from fileС индексами552525Без индексов301817 В итоге получается, если программно вставлять не по 20, а по 200 записей, то получается чуть ли не быстрее чем copy from file (тк в программном варианте еще идет вставка в мастер-таблицу, генерация сиквенса и case-ы в каждом поле таблицы деталей, куда заливаем 2млн записей, а время то же самое). То есть сам механизм работает так же по скорости, если брать пакеты большего размера. Но засада в том, что вставлять надо все же по 20 записей, а чаще еще меньше - например по 10. Может есть возможность распараллелить? То есть разбить таблицу на патиции по хешу например или другие варианты и грузить параллельно. В постгре это шардинг называется? Можно ли при шардинге таблицу держать в одной БД, а ее патиции разнести на разные диски? Или шардинг это когда таблицу кладут в разные ноды кластера? При шардинге не поддерживаются глобальные уник индексы? Надеюсь, что просто не умею готовить, но пока грустно со скоростями. Попробую еще на машине с SSD. ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 00:48 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Попробовал еще так 1. делаем копию таблицы с 2млн записей (кстати ctas - 7сек вот это нормальная скорость :) 2. truncate исх таблицы 3. insert select в исх таблицу с индексами 30сек, без индексов - те же 18сек ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 01:04 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS Но засада в том, что вставлять надо все же по 20 записей, а чаще еще меньше - например по 10. по 10 записей 200000 раз надо вставить? лучше буферизуйте их тогда ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 01:31 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS, А если использовать такой вариант: создать копию таблицы без индексов, вставлять в нее данные, как только счетчик доходит допустим до 200, делать инсерт из нее в основную таблицу, truncate и сброс секвенса в 0. ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 09:05 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Swa111 , суть как предложил Алексей, но так будет дольше (инсерт в таблицу без индексов уже время то же самое, потом еще + переливка в таблицу с индексами). Алексей Роза , да, хороший вариант по-моему. Но возникают другие моменты (особенно 3): 1. Сиквенс придется генерить на стороне приложения (это конечно решаемо, не вопрос) 2. Если приложение падает, то накрываются все данные в буфере, но по бизнесу этим можно пожертвовать. 3. Сейчас в процедуру передается массив целых чисел (ID-шников) и кучка параметров, на основе которых генерятся значения для других полей в таблице через case-ы в самом запросе. Если сначала буферизовать данные, для набора пакета большего объема, то придется передавать не просто массив целых чисел, а уже желательно массив записей, то есть объектов. Поэтому вопрос, есть ли возможность в Postgres передавать из Java CallableStatement набор объектов (так-то в CallableStatement есть метод setObject, это поищу что за зверь). Либо передавать массив строк с json, а в постгре json_to_recordset, но не охота парсить ибо тоже затраты времени. ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 13:15 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS В итоге получается, если программно вставлять не по 20, а по 200 записей, то получается чуть ли не быстрее чем copy from file (тк в программном варианте еще идет вставка в мастер-таблицу, генерация сиквенса и case-ы в каждом поле таблицы деталей, куда заливаем 2млн записей, а время то же самое). То есть сам механизм работает так же по скорости, если брать пакеты большего размера. Но засада в том, что вставлять надо все же по 20 записей, а чаще еще меньше - например по 10. Тут я сильно подозреваю что вы в свое железо и настройки базы упираетесь. Что у вас стоит в synchronous_commit max_wal_size checkpoint_timeout shared_buffers возможно загрузка у вас в fsync данных на диск упирается (и тогда понятно почему большие батчи идут быстрее). ps: а зачем вам партиции и шардирование для паралельной загрузки то? пишите спокойно в таблицу в 10 потоков и будет вам быстрее (насколько - зависит от вашего оборудования). ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 13:41 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Maxim Boguk , итак пишу через тредпул в 15 потоков, это конечно дает ощутимый прирост, но диск-то все равно один. Памяти на машине 8GB, параметры: synchronous_commit - on max_wal_size - 1GB checkpoint_timeout - 5min shared_buffers - 128MB ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 13:57 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS Maxim Boguk , итак пишу через тредпул в 15 потоков, это конечно дает ощутимый прирост, но диск-то все равно один. Памяти на машине 8GB, параметры: synchronous_commit - on max_wal_size - 1GB checkpoint_timeout - 5min shared_buffers - 128MB Протестируйте с synchronous_commit =off max_wal_size=16GB checkpoint_timeout=60min shared_buffers=2GB только рестарт базы не забудьте сделать после изменений (скорее всего влиять будет первый и последний параметр в основном). ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 14:07 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Maxim Boguk , попробовал. Да получается побыстрее. Немного нелинейная зависимость получается, но и зависимость от размера пакета сохраняется, то есть вариант с буферизацией на стороне приложения пока актуален. Да и изменение параметров возможно влечет другие риски в частности настораживает synchronous_commit =off. Вставка 2млн записей Наличие индексовПакетами по 20 записей (сек)Пакетами по 200 записей (сек)Без индексов2620 С индексами3630 При этом в варианте без индексов по 200 записей время загрузки стало сильно нестабильным и колеблется от 11 до 35 секунд. ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 14:46 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS Swa111 , суть как предложил Алексей, но так будет дольше (инсерт в таблицу без индексов уже время то же самое, потом еще + переливка в таблицу с индексами). Алексей Роза , да, хороший вариант по-моему. Но возникают другие моменты (особенно 3): 1. Сиквенс придется генерить на стороне приложения (это конечно решаемо, не вопрос) 2. Если приложение падает, то накрываются все данные в буфере, но по бизнесу этим можно пожертвовать. 3. Сейчас в процедуру передается массив целых чисел (ID-шников) и кучка параметров, на основе которых генерятся значения для других полей в таблице через case-ы в самом запросе. Если сначала буферизовать данные, для набора пакета большего объема, то придется передавать не просто массив целых чисел, а уже желательно массив записей, то есть объектов. Поэтому вопрос, есть ли возможность в Postgres передавать из Java CallableStatement набор объектов (так-то в CallableStatement есть метод setObject, это поищу что за зверь). Либо передавать массив строк с json, а в постгре json_to_recordset, но не охота парсить ибо тоже затраты времени. под буферизацией имеется ввиду складывание строк в файл под будущий COPY а когда их набралось 2 млн (ну или пачками по 50000 грузите хотя бы - от этого тоже скорость зависит), то разом их все загрузили в БД и никаких потерь нет. ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 15:46 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS Maxim Boguk , попробовал. Да получается побыстрее. Немного нелинейная зависимость получается, но и зависимость от размера пакета сохраняется, то есть вариант с буферизацией на стороне приложения пока актуален. Да и изменение параметров возможно влечет другие риски в частности настораживает synchronous_commit =off. Вставка 2млн записей Наличие индексовПакетами по 20 записей (сек)Пакетами по 200 записей (сек)Без индексов2620 С индексами3630 При этом в варианте без индексов по 200 записей время загрузки стало сильно нестабильным и колеблется от 11 до 35 секунд.\ значит у вас упирается все в скорость fsync на вашем диске... на механике вообще больше 200-300 коммитов в секунду делать не получается по честному... поэтому вот так и работает. Будут быстрые ssd - разница между synchronous_commit =off и on будет не такой ощутимой (ну или нормальный рейд с кешом и батарейкой исправной на механических дисках). ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 15:51 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Алексей Роза , тоже так думал, но во-первых, буде не сильно быстрее (в файл же тоже надо записать), во-вторых, самое главное, к этим данным могут идти селекты, а из файла не поселектишь особо ), поэтому придется все равно на время пока данные не легли в БД держать их в памяти, то есть буфер на 200+- записей Maxim Boguk , ок, попробую на SSD еще. И еще все же интересно что там с шардингом ) ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 16:20 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Хотя селектить данные, которые еще не легли в БД тоже негуд, т.к. юзер заселектил из буфера приложения, дальше приложение вдруг упало и после восстановления пользователь уже не увидит данные, которые он видел до падения ибо они не записались в базу из буфера. ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 16:32 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS Алексей Роза , тоже так думал, но во-первых, буде не сильно быстрее (в файл же тоже надо записать), во-вторых, самое главное, к этим данным могут идти селекты, а из файла не поселектишь особо ), поэтому придется все равно на время пока данные не легли в БД держать их в памяти, то есть буфер на 200+- записей Maxim Boguk , ок, попробую на SSD еще. И еще все же интересно что там с шардингом ) у вас на том оборудовании что есть упирается не в запись в датафайлы а в запись в wal лог (fsync точнее) а он один и пишется последовательно (судя по вашим результатам) и всегда на одно устройство так что от шардинга ему ни холодно не жалко зато есть польза от выноса wal лога на другое/отдельное физическое устройство. В общем это больше к администрированию базы а не к архитектуре приложения вопрос. >>Может есть возможность распараллелить? >>То есть разбить таблицу на патиции по хешу например или другие варианты и грузить параллельно. >>В постгре это шардинг называется? это именно партиционирование называется... шардинг - когда на много независимых серверов с базами данных раскидывается нагрузка только пользы от него тут вам будет мало (не там узкое место). Можно ли при шардинге таблицу держать в одной БД, а ее патиции разнести на разные диски? Да Или шардинг это когда таблицу кладут в разные ноды кластера? Да. При шардинге не поддерживаются глобальные уник индексы? В зависимости от версии... в старых версиях вообще нет... в новых "Unique constraints on partitioned tables must include all the partition key columns. This limitation exists because PostgreSQL can only enforce uniqueness in each partition individually." что по сути тоже нет )). ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 18:26 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Maxim Boguk , спасибо за пояснения. Попутно еще возник вопрос по деградации производительности с ростом объема. То есть. 2млн записей вставляются в среднем за 25сек (условно 80тыс. записей/сек) А 30млн записей - за 22.5 минуты (примерно 22тыс записей/сек) То есть с ростом объема скорость падает практически в 4 раза в данном случае, что довольно ощутимо. Как боремся с этим? И все-таки шардинг и патиционирование - разные вещи значит? То есть таблица может быть и патиционирована и шардирована одновременно. В общем надо еще курить документацию, хотя бы основы, то тут все немного по-другому похоже ) ... |
|||
:
Нравится:
Не нравится:
|
|||
01.03.2020, 19:36 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS Maxim Boguk , спасибо за пояснения. Попутно еще возник вопрос по деградации производительности с ростом объема. То есть. 2млн записей вставляются в среднем за 25сек (условно 80тыс. записей/сек) А 30млн записей - за 22.5 минуты (примерно 22тыс записей/сек) То есть с ростом объема скорость падает практически в 4 раза в данном случае, что довольно ощутимо. Как боремся с этим? Скорее всего данные перестают помещаться в кеш OS и shared buffer базы и начинают с диска читаться - и борятся с этим более быстрыми дисками (в пределе intel optane) или наращиванием памяти на сервере. Ну и checkpoints начинаются которые уже нагруженный диск - совсем ушатывают. Вообще бессмысленно проводить тесты производительности не на том оборудовании на котором проект планируется к работе. И все-таки шардинг и патиционирование - разные вещи значит? То есть таблица может быть и патиционирована и шардирована одновременно. Да может быть и так... но только шардирование это уже задача приложения а не базы (т.е. штатных методов шардирования в postgresql нет это всегда что то самописное). ... |
|||
:
Нравится:
Не нравится:
|
|||
02.03.2020, 01:15 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Maxim Boguk, спасибо, теперь с шардингом еще понятнее, думал, это в коробке) Продолжаю эксперименты. Пока самый быстрый вариант это передача массива и select from unnest пакетами в районе 200 значений в массиве. Но ввиду обьединения многих вызовов по 10-20 записей в один через буфер на 200 записей, нужен многомерный массив или обьекты типа pojo(это не пробовал, не знаю есть ли это в постгре). Поэтому. Пробовал еще просто батч инсерты - unnest быстрее. Пробовал засунуть все 200 записей в json и потом select from json_to_recordset - unnest быстрее. Пробовал передавать в процедуру в каждый из параметров отдельный массив и массив индексов массива - мало того, что криво но unnest из одного массива все равно ощутимо быстрее. Можно было бы попробовать передавать массив строк с разделителями и потом их парсить, но думаю, это будет примерно как с json_torecordset. Есть другая мысль. Передаются только числа. То есть можно пофиксить формат, выделив для каждого параметра определенное кол-во байт. И тогда парсить можно будет быстрее, сразу задавая смещение. Поэтому вопрос есть ли в постгре возможность работы с бинарными данными? То есть передавать одномерный массив, элементы которого двоичные данные. Хотя потом их все равно придется приводить к тому же bigint например. ... |
|||
:
Нравится:
Не нравится:
|
|||
03.03.2020, 11:29 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Код: plsql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.
Попробовал просто массив строк с фиксированным форматом. По скорости так же как вставка пакетами по 20 строк. То есть медленне чем json_to_recordset. Но может криво написано с типами и преобразованием. ... |
|||
:
Нравится:
Не нравится:
|
|||
03.03.2020, 15:26 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Сделал таблицу с со всеми чаровскими полями, убрал из функции преобразование типов в запросе - вставка 2млн записей без индексов - 13сек, с индексами - 1min. Какая-то огромная разница, будто индексы по строкам строятся сильно дольше, чем по числам. Вопрос с аналогичным тестом, но переложенным в бинарный формат пока актуален. Так же как и вариант передачи массива обьектов без сериализации и последующего парсинга) ... |
|||
:
Нравится:
Не нравится:
|
|||
03.03.2020, 15:42 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
JDS Сделал таблицу с со всеми чаровскими полями, убрал из функции преобразование типов в запросе - вставка 2млн записей без индексов - 13сек, с индексами - 1min. Какая-то огромная разница, будто индексы по строкам строятся сильно дольше, чем по числам. Вопрос с аналогичным тестом, но переложенным в бинарный формат пока актуален. Так же как и вариант передачи массива обьектов без сериализации и последующего парсинга) >>будто индексы по строкам строятся сильно дольше, чем по числам. так и есть... разница до порядка (а в тяжелых случаях - и заметно больше)... сравнение utf8 с учетом локализации - штука ОЧЕНЬ дорогая (по сравнению с сравнением 2 int4 просто по значению). ... |
|||
:
Нравится:
Не нравится:
|
|||
03.03.2020, 18:16 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
utf8 это wide string в C/C++ от простого string в тестах regexp, например, в разы отличаются ну а цифра - это самое удобное/быстрое для компьютера (так то строки это тоже цифры = номер в таблице) и когда есть возможность, надо всегда юзать их ... |
|||
:
Нравится:
Не нравится:
|
|||
03.03.2020, 21:09 |
|
Оптимизация INSERT-ов
|
|||
---|---|---|---|
#18+
Прошу пардона, на самом деле пара запусков была по минуте, позже пробовал еще раз - 35-40сек, то есть разница не такая большая с индексами по чарам. Ну и кому-то может пригодится товарищ тоже шуршал в этом направлении . ... |
|||
:
Нравится:
Не нравится:
|
|||
03.03.2020, 22:17 |
|
|
start [/forum/topic.php?fid=53&msg=39933730&tid=1994775]: |
0ms |
get settings: |
12ms |
get forum list: |
14ms |
check forum access: |
3ms |
check topic access: |
3ms |
track hit: |
39ms |
get topic data: |
11ms |
get forum data: |
3ms |
get page messages: |
52ms |
get tp. blocked users: |
1ms |
others: | 15ms |
total: | 153ms |
0 / 0 |