ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Рост базы при вставке большого количества записей
6 сообщений из 6, страница 1 из 1
Рост базы при вставке большого количества записей
    #39424578
ivanra
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ivanra
Гость
PostgreSQL используется для хранения метаданных в Jackrabbit JCR. Необходимо загрузить большое количество документов в хранилище. При этом документы в базу не загружаются, только метаданные. Вот эта таблица:
Код: sql
1.
2.
3.
4.
5.
CREATE TABLE "public"."default_bundle" (
	"node_id_hi" bigint NOT NULL, 
	"node_id_lo" bigint NOT NULL, 
	"bundle_data" bytea NOT NULL,
	CONSTRAINT "default_bundle_pkey" PRIMARY KEY ("node_id_hi", "node_id_lo")


Несмотря на то, что поле bundle_data - блоб, в среднем его длина небольшая. Это ссылка на документ в дисковом хранилище, плюс немного метаданных. Но почему-то размер таблицы при вставке растет катастрофически. Вот статистика:
 
скрипт
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / x.ct END          AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   default_bundle t  -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, what, nr);

 
результатwhatbytes/ctbytes_prettybytes_per_rowcore_relation_size3971481638 MB355visibility_map00 bytes0free_space_map3276832 kB0table_size_incl_toast1194908876811 GB106958indexes_size56197125488 kB50total_size_incl_toast_and_indexes1195470848011 GB107008live_rows_in_text_representation8164623678 MB730------------------------------row_count111717live_tuples111680dead_tuples3
 
попыка почистить
Код: sql
1.
VACUUM VERBOSE ANALYZE default_bundle;


INFO: 00000: vacuuming "public.default_bundle"
INFO: 00000: scanned index "default_bundle_pkey" to remove 8 row versions
INFO: 00000: "default_bundle": removed 8 row versions in 8 pages
INFO: 00000: index "default_bundle_pkey" now contains 111717 row versions in 686 pages
INFO: 00000: "default_bundle": found 0 removable, 111717 nonremovable row versions in 4848 out of 4848 pages
INFO: 00000: vacuuming "pg_toast.pg_toast_16417"
INFO: 00000: index "pg_toast_16417_index" now contains 1261 row versions in 17930 pages
INFO: 00000: "pg_toast_16417": found 0 removable, 0 nonremovable row versions in 0 out of 1435467 pages
INFO: 00000: analyzing "public.default_bundle"
INFO: 00000: "default_bundle": scanned 4848 of 4848 pages, containing 111717 live rows and 0 dead rows; 30000 rows in sample, 111717 estimated total rows
Command(s) completed successfully.

Это после вставки ~11000 документов, а надо вставить миллион (на документ приходится с среднем 2 записи в таблице метаданных). Можно тут что-то сделать? Или сразу на MySQL переходить
...
Рейтинг: 0 / 0
22.03.2017, 09:38
| Ответить | Цитировать | Написать  
Рост базы при вставке большого количества записей
    #39424704
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
ivanraPostgreSQL используется для хранения метаданных в Jackrabbit JCR. Необходимо загрузить большое количество документов в хранилище. При этом документы в базу не загружаются, только метаданные. Вот эта таблица:
...
Это после вставки ~11000 документов, а надо вставить миллион (на документ приходится с среднем 2 записи в таблице метаданных). Можно тут что-то сделать? Или сразу на MySQL переходить

Чей то очень не похоже что "Несмотря на то, что поле bundle_data - блоб, в среднем его длина небольшая. "
По тому что я вижу - средняя длинна этого bytea порядка мегабайта что наводит меня на мысль что вы что то не так сделали и туда сам документ попадает тоже.
Рекомендую внимательно изучить что в это bundle_data у вас реально лежит.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
22.03.2017, 12:10
| Ответить | Цитировать | Написать  
Рост базы при вставке большого количества записей
    #39424732
ivanra
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ivanra
Гость
Maxim Boguk,
средняя длина записи 335 байт. Вот запрос по конкретной колонке
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
  avg(pg_column_size((bundle_data))) 
  ,min(pg_column_size((bundle_data))) 
  ,max(pg_column_size((bundle_data))) 
  ,sum(pg_column_size((bundle_data))) 
  ,count(*)
from default_bundle

avgminmaxsumcount321.695650617184493037250852035938873111717
Всего длинных немного
Код: sql
1.
where pg_column_size((bundle_data))>1024

avgminmaxsumcount252622.40000000000010692508520252622410
Фактически, там только 1 длинный блоб на 2,5 мб, который был до того. Второй по длине - 5 кб, остальные меньше 2кб
Если интересно, перед проведением теста в таблице уже было порядка 70000 записей, и был сделан VACUUM ALL. На тот момент в таблице данных было порядка 30 мб, и на диске она занимала почти столько же.
После вставки данных стало 38мб, а на диске - 11гб.
Нашел статью про усиление записи, видимо, тот самый случай
...
Рейтинг: 0 / 0
22.03.2017, 12:29
| Ответить | Цитировать | Написать  
Рост базы при вставке большого количества записей
    #39424742
ivanra
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ivanra
Гость
Вот статистика по этой таблице после VACUUM ALL, как видно выше, сбор мусора по только этой таблице не помогает
whatbytes/ctbytes_prettybytes_per_rowcore_relation_size3971481638 MB355visibility_map00 bytes0free_space_map00 bytes0table_size_incl_toast4235264040 MB379indexes_size35553283472 kB31total_size_incl_toast_and_indexes4590796844 MB410live_rows_in_text_representation8164623678 MB730------------------------------row_count111717live_tuples111717dead_tuples0
...
Рейтинг: 0 / 0
22.03.2017, 12:37
| Ответить | Цитировать | Написать  
Рост базы при вставке большого количества записей
    #39424968
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
ivanraMaxim Boguk,
средняя длина записи 335 байт. Вот запрос по конкретной колонке
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
  avg(pg_column_size((bundle_data))) 
  ,min(pg_column_size((bundle_data))) 
  ,max(pg_column_size((bundle_data))) 
  ,sum(pg_column_size((bundle_data))) 
  ,count(*)
from default_bundle

avgminmaxsumcount321.695650617184493037250852035938873111717
Всего длинных немного
Код: sql
1.
where pg_column_size((bundle_data))>1024

avgminmaxsumcount252622.40000000000010692508520252622410
Фактически, там только 1 длинный блоб на 2,5 мб, который был до того. Второй по длине - 5 кб, остальные меньше 2кб
Если интересно, перед проведением теста в таблице уже было порядка 70000 записей, и был сделан VACUUM ALL. На тот момент в таблице данных было порядка 30 мб, и на диске она занимала почти столько же.
После вставки данных стало 38мб, а на диске - 11гб.
Нашел статью про усиление записи, видимо, тот самый случай

Вам база русским английским языком написала
INFO: 00000: "pg_toast_16417": found 0 removable, 0 nonremovable row versions in 0 out of 1435467 pages
у вас TOAST 11Gb и чем то занят.
Ничем кроме данных bundle_data он занят быть не может.
А что есть VACUUM ALL ? Такой команды у базы нет.

Вообще ОЧЕНЬ странные цифры.
INFO: 00000: vacuuming "pg_toast.pg_toast_16417"
INFO: 00000: index "pg_toast_16417_index" now contains 1261 row versions in 17930 pages
INFO: 00000: "pg_toast_16417": found 0 removable, 0 nonremovable row versions in 0 out of 1435467 pages

попробуйте заново провести эксперимент вида - загрузить нужные вам данные в таблицу раз.
Сделать vacuum verbose на нее и показать вывод
сделать vacuum full и показать вывод
сделать vacuum verbose еще раз и опять показать вывод.

Я что то никак не могу сообразить как 1200 строк в toast заняло 11Gb с 0 пустых строк.

А вот еще - попробуйте поставить pgstattuple и сразу после загрузки сделать select * from pgstattuple('pg_toast.pg_toast_16417');
интересно будет посмотреть что там.

PPS: у вас загрузка делается просто через insert или там updates бывают? И это все одной транзакцией или построчно на транзакцию?
Есть у меня одна теория как такое можно получить (но это постараться надо).
...
Рейтинг: 0 / 0
22.03.2017, 15:22
| Ответить | Цитировать | Написать  
Рост базы при вставке большого количества записей
    #39425054
ivanra
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ivanra
Гость
Maxim Boguk,
конечно, vacuum full. Просто этим занимался админ, я больше по ява коду. Состояние до очистки и после я привел, что там было в toast так и осталось загадкой. VACUUM VERBOSE ANALYZE на этой таблице сейчас выдает: INFO: 00000: vacuuming "public.default_bundle"
INFO: 00000: index "default_bundle_pkey" now contains 111717 row versions in 434 pages
INFO: 00000: "default_bundle": found 0 removable, 111717 nonremovable row versions in 4848 out of 4848 pages
INFO: 00000: vacuuming "pg_toast.pg_toast_16417"
INFO: 00000: index "pg_toast_16417_index" now contains 1261 row versions in 6 pages
INFO: 00000: "pg_toast_16417": found 0 removable, 1261 nonremovable row versions in 316 out of 316 pages
INFO: 00000: analyzing "public.default_bundle"
INFO: 00000: "default_bundle": scanned 4848 of 4848 pages, containing 111717 live rows and 0 dead rows; 30000 rows in sample, 111717 estimated total rows
Command(s) completed successfully.

Ну и что конкретно делает сервер jackrabbit - большой вопрос. Это opensource проект, и документация к нему неважная. Ну и баги. Пока писал загрузчик хранилища, уже нашел утечку в клиенте. Не удивлюсь багам и в серверной части.
Документация утверждает, что транзакция коммитится при вызове jcrSession.save(), я это делаю при сохранении каждого документа, и еще на всякий случай - закрываю и открываю сессию заново каждые 100 документов. Не помогает.
Мое предположение - документы в базу все-таки сначала вставляются, а потом заменяются на ссылку на диске. Хоть в документации и говорится, что в базу ничего больше 16 кб не должно попадать (а по другому месту в документации - вообще 100 байт).
Пока решено взять MySQL
...
Рейтинг: 0 / 0
22.03.2017, 16:38
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Рост базы при вставке большого количества записей
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=77&tid=1996643]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 34ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 17ms
total:  142ms
созд. / загр.: 142ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]