Вставка большого объема записей в таблицу с помощью COPY / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Вставка большого объема записей в таблицу с помощью COPY

17 сообщений из 17, страница 1 из 1

Вставка большого объема записей в таблицу с помощью COPY

#39001091

Emily_rose

Гость

Добрый день!

Работаю с PostgreSQL в PgAdmin3.

Помогите пожалуйста разобраться с двумя вопросами:
1) Как ускорить запись в таблицу из файла? Мне необходимо протестировать свою программу для этого поставлена задача добавить в одну таблицу порядка 1 600 000 произвольных записей.

2) Одно из полей в таблице с которой я работаю имеет тип serial и необходимо делать его инкремент. Так вот вопрос, как прописать команду COPY, чтобы значение в это поле вставлялось само. Так не работает COPY name_tab FROM 'C:/task1/testbd.txt' WITH DELIMITER ','

Относительно первого вопроса, попробовала настроить файл конфигураций, а именно увеличила sharred_buffers до 1024 Mb, разницы не почувствовала (до этого стояло 128Mb).

ИНдексов нет, так как таблицы в БД не связаны между собой.

...

Рейтинг:

0 / 0

07.07.2015, 10:02

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001104

Emily_rose

Гость

Emily_rose,

Также дополнительно добавила следующие настройки (оперативная память 3 ГБ):

maintenance_work_mem = 128MB;
checkpoint_completion_target = 0.7
effective_cache_size = 1536MB
work_mem = 4MB
wal_buffers = 4MB
checkpoint_segments = 8
shared_buffers = 512MB
max_connections = 500

...

Рейтинг:

0 / 0

07.07.2015, 10:23

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001105

grufos

Участник

Откуда: Москва

Сообщения: 94

Рейтинг: 0 / 0

Emily_rose,

Emily_rose2) Одно из полей в таблице с которой я работаю имеет тип serial и необходимо делать его инкремент.
команда COPY позволяет указывать имена полей для обработки
COPY name_tab(filed1, filed2, filed3, ...) FROM 'C:/task1/testbd.txt' WITH DELIMITER ',';

...

Рейтинг:

0 / 0

07.07.2015, 10:25

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001108

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Emily_roseДобрый день!

Работаю с PostgreSQL в PgAdmin3. интимные подробности можете оставить при себе

Emily_roseПомогите пожалуйста разобраться с двумя вопросами:
1) Как ускорить запись в таблицу из файла? Мне необходимо протестировать свою программу для этого поставлена задача добавить в одну таблицу порядка 1 600 000 произвольных записей. снести индексы, триггера, чеки.
ускорить дисковую.

Emily_rose2) Одно из полей в таблице с которой я работаю имеет тип serial и необходимо делать его инкремент. Так вот вопрос, как прописать команду COPY, чтобы значение в это поле вставлялось само. Так не работает COPY name_tab FROM 'C:/task1/testbd.txt' WITH DELIMITER ','

а RTFM за вас пупкин будет читать
http://www.postgresql.org/docs/current/static/sql-copy.html

автор

Код: sql

1.
2.
3.
4.
5.
6.
7.

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

...

Рейтинг:

0 / 0

07.07.2015, 10:28

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001112

Emily_rose

Гость

qwwq,

Спасибо за ответ

...

Рейтинг:

0 / 0

07.07.2015, 10:30

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001122

Emily_rose

Гость

Спасибо огромное за ответы! Действительно указание полей для заполнения решило вопрос. НЕ внимательно документацию прочитала.

Единственное, как бы еще ускорить вставку записей =)

...

Рейтинг:

0 / 0

07.07.2015, 10:36

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001145

PgSQLAnonymous

Гость

Emily_roseЕдинственное, как бы еще ускорить вставку записей =)

Это уже читали?

...

Рейтинг:

0 / 0

07.07.2015, 11:02

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001163

Emily_rose

Гость

PgSQLAnonymous, Да, насколько мне это позволяет мой не оч хороший английский =)

...

Рейтинг:

0 / 0

07.07.2015, 11:17

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001188

p2.

Участник

Сообщения: 495

Рейтинг: 0 / 0

Emily_rose,

зачем произвольные данные вставлять из файла. не проще ли их нагенерить непосредственно в sql.

...

Рейтинг:

0 / 0

07.07.2015, 11:30

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39001428

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Emily_roseСпасибо огромное за ответы! Действительно указание полей для заполнения решило вопрос. НЕ внимательно документацию прочитала.

Единственное, как бы еще ускорить вставку записей =)

Чтобы ускорять процесс надо понять во что оно упирается.
Варианта два
1)в дисковую подсистему (тогда надо или диски побыстрее и побольше и/или сильно поднять checkpoint_segments/checkpoint_timeout)
или
2)в процессор (тогда убрать индексы с таблицы внести в нее данные создать индексы или разбить copy на N кусков и заливать в N потоков чтобы много ядер использовать)

Для анализа во что упираетесь полезно использовать top и iostat.

--
Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

07.07.2015, 13:53

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39006700

Emily_rose

Гость

Maxim Boguk, спасибо за ответ. А Вы еще не уточните следующее: поднимая checkpoint_segments насколько нужно поднимать checkpoint_timeout? Какая примерно должна быть соблюдена пропорция? Сейчас стоит 8 и 5 мин.

...

Рейтинг:

0 / 0

14.07.2015, 10:59

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39006947

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Emily_roseMaxim Boguk, спасибо за ответ. А Вы еще не уточните следующее: поднимая checkpoint_segments насколько нужно поднимать checkpoint_timeout? Какая примерно должна быть соблюдена пропорция? Сейчас стоит 8 и 5 мин.

Нуууу.... если не жалко 10 минут потратить в случае если сервер с базой упадет (пока она будет накатываться) то можно и 1024/60min поставить.
Вообще идеально так чтобы checkpoints шли раз в час по времени.
Исходя из этого настраивать сегменты.

--
Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

14.07.2015, 13:57

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39007040

Emily_rose

Гость

Maxim Boguk ок, спсаибо!

Единственное, что раз в час, это все же на мой взгляд достаточно редко, можно много информации потерять на мой взгляд.

А можете подсказать, как часто необходимо делать бэкапы? ИЛи это вс еиндивидуально и зависит от проекта?

Также не подскажите как лучше организовать систему, чтобы по минимуму исключить потерю данных?

...

Рейтинг:

0 / 0

14.07.2015, 15:21

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39007062

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Emily_roseMaxim Boguk ок, спсаибо!

Единственное, что раз в час, это все же на мой взгляд достаточно редко, можно много информации потерять на мой взгляд.

А можете подсказать, как часто необходимо делать бэкапы? ИЛи это вс еиндивидуально и зависит от проекта?

Также не подскажите как лучше организовать систему, чтобы по минимуму исключить потерю данных?

>>можно много информации потерять на мой взгляд.
А при чем тут checkpoints к потере данных?
Данные записаны сразу после commit и дальше пока файловая система не побилась они уже никуда не денутся.

>>backups по потребностям проекта делается.

>>Также не подскажите как лучше организовать систему, чтобы по минимуму исключить потерю данных?
Синхронная реплика + (basebackup+wal archiving) + регулярные pg_dump для архива + естественно не выключать fsync/syncronous_commit.

--
Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

14.07.2015, 15:36

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39007083

Emily_rose

Гость

checkpoint_segments – указывает количество транзакций, после которых происходит физический сброс данных на диск, при интенсивной записи параметр можно увеличить. На эту настройку стоит обратить внимание, если у Вас происходит немалое количество записей в БД (для высоконагруженных систем это нормальная ситуация). Postgres записывает данные в базу данных порциями (WALL сегменты) — каждая размером в 16Mb. После записи определенного количества таких порций (определяется параметром checkpoint_segments) происходит чекпойнт. Чекпойнт — это набор операций, которые выполняет postgres для гарантии того, что все изменения были записаны в файлы данных ( следовательно при сбое, восстановление происходит по последнему чекпойнту ). Выполнение чекпоинтов каждые 16Мб может быть весьма ресурсоемким, поэтому это значение следует увеличить хотя бы до 10. Для случаев с большим количеством записей, стоит увеличивать это значение в рамках от 32 до 256.

...

Рейтинг:

0 / 0

14.07.2015, 16:02

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39007137

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Emily_rosecheckpoint_segments – указывает количество транзакций, после которых происходит физический сброс данных на диск, при интенсивной записи параметр можно увеличить. На эту настройку стоит обратить внимание, если у Вас происходит немалое количество записей в БД (для высоконагруженных систем это нормальная ситуация). Postgres записывает данные в базу данных порциями (WALL сегменты) — каждая размером в 16Mb. После записи определенного количества таких порций (определяется параметром checkpoint_segments) происходит чекпойнт. Чекпойнт — это набор операций, которые выполняет postgres для гарантии того, что все изменения были записаны в файлы данных ( следовательно при сбое, восстановление происходит по последнему чекпойнту ). Выполнение чекпоинтов каждые 16Мб может быть весьма ресурсоемким, поэтому это значение следует увеличить хотя бы до 10. Для случаев с большим количеством записей, стоит увеличивать это значение в рамках от 32 до 256.

Не читайте русские переводы.
Фраза "следовательно при сбое, восстановление происходит по последнему чекпойнту" значит в данном случае что после сбоя восстановление идет от последнего checkpoint и до самой последней записанной в wal транзакции.
Поэтому если wal segments много и все упало оно может восстанавливаться заметное время.
Основная задача база (любой нормальной) обеспечить гарантию того при при исправном оборудовании нельзя потерять закомиченную транзакцию как сервер не выключай по питанию.

--
Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

14.07.2015, 16:41

| Ответить | Цитировать | Написать

Вставка большого объема записей в таблицу с помощью COPY

#39007527

Emily_rose

Гость

Maxim Boguk, ясно, спасибо! =)

Да, приходится к сожалению читать на русском, т.к анг. не самый хороший у меня =)

...

Рейтинг:

0 / 0

15.07.2015, 09:16

| Ответить | Цитировать | Написать

17 сообщений из 17, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Вставка большого объема записей в таблицу с помощью COPY

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=39001105&tid=1997882]:	0ms
get settings:	7ms
get forum list:	15ms
check forum access:	3ms
check topic access:	3ms
track hit:	148ms
get topic data:	10ms
get forum data:	2ms
get page messages:	39ms
get tp. blocked users:	1ms
others:	210ms

total:	438ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы