2 MBG

Как к ситуации автора топика ignitorТаблицы по 3-12млн. активно обновляются в течение дня, идет удаление 18-50 тысяч записей и затем через COPY заливаются обратно.можно применить ваш способ MBGЛучше данные записать во временную таблицу, там обработать и переложить в большую таблицу.Объясните пожалуйста подробнее.

PS: "Перепроектирование приложения" - это, мне кажется, более широкое понятие, чем добавление в узких местах временных таблицы. Например, изменить структуру таблиц, при этом возможно даже изменив функционал базы, и как следствие приложения. Это может привести к кардинальному ускорению работы с бд. Но требует гораздо больших усилий со стороны программистов, чем routine vacuum reindex. :-)

ignitorПосле некоторых экспериментов пришел к выводу, что самое быстрое - делать DROP TABLE, затем CREATE TABLE и COPY из файла... А вот таблица accanl как раз таки пересоздается каждый раз, поэтому в чем тормоза - непонятно. Хотя после включения в код VACUUM FULL ANALYSE accanl (выполняется 10,5 секунд) все вроде бы устаканилось.Мы делаем так DROP TABLE, COPY FROM, CREATE INDEX, VACUUM ANALYZE. Ключик FULL в команде VACUUM для только что созданной таблицы лишний.

MBGЕсли требуется проводить какие-то операции update, то только во временной таблице.Не понятно, чем здесь сможет помочь временная таблица. Например, есть таблица (id,name,info,shows,clicks) с N записями. Необходимо для N/10 записей обновить по ключу id поля shows и clicks, по данным приходящим извне. Временная таблица?

MBGЕсли просто удаление/добавление данных, то разделенные таблицы.Зачем? Чтобы операция массированного удаления/добавления затронула одну или несколько из разделенных таблиц? Но не всегда удастся разделить таблицы таким образом.

MBG LeXa NalBat MBGЕсли требуется проводить какие-то операции update, то только во временной таблице.Не понятно, чем здесь сможет помочь временная таблица. Например, есть таблица (id,name,info,shows,clicks) с N записями. Необходимо для N/10 записей обновить по ключу id поля shows и clicks, по данным приходящим извне. Временная таблица?Если несколько запросов _одновременно_ обновляют данные, используя транзакции, постгрес создает снимки таблицы для каждой транзакции и на них выполняет обновление, что очень неэффективно на больших таблицах. Даже при однопользовательском доступе можно напороться на грабли с тем, что одна транзакция еще не завершилась, а следующая началась. Например, база в 10 мегабайт может при интенсивном обновлении (раз в несколько минут) небольшим количеством данных вырасти до десяти гигабайт за сутки. Сборщик мусора в таких условиях не может эффектиивно работать - незафиксированные снимки таблицы не чистятся, индексы пухнут.И? В этом случае сможет помочь временная таблица? Объясните пожалуйста подробнее.

MBGРазница будет в _скорости выборки после_ этих операций (к вопросу о количестве дисковых операций). Разделять нужно, ориентируясь на селекты, а не на обновления.Неужели действительно скорость селектов по целой и партиционной таблице будет отличаться например более чем в два раза? Надо попробовать поэкспериментировать...

MBGЗаливаем данные во временную таблицу, выполняем их обработку, копируем в постоянную таблицу, временная таблица (temp table) автоматически удаляется. Запуск сборщика мусора после этого не нужен - файлы временных таблиц удаляются целиком, чистить нечего.Теперь кажется понятно, что под фразой "если требуется проводить какие-то операции update, то только во временной таблице" вы имели в виду, что надо проводить все предварительные расчеты используя временные таблицы.

Я же предполагал, что все предварительные расчеты проведены клиентом, а под "операциями update" вы имели в виду именно обновление постоянной таблицы, а не добавление (как вы написали выше "копируем в постоянную таблицу"); при обновлении мусор (в постоянной таблице) образуется. :-(

Мне кажется, что описание автора топика "таблицы по 3-12млн. активно обновляются в течение дня, идет удаление 18-50 тысяч записей и затем через COPY заливаются обратно" описывает алгоритм "delete returning" -> приложение (пересчет данных) -> "copy to", то есть временные таблицы здесь бесполезны.

MBGЕсли нужно заливать данные в один поток (например, когда разные клиенты могут заливать одни и те же данные, а мы хотим защититься от дубликатов и сообщить клиентам, какая часть их данных уже присутствует) создается постоянная таблица, которая блокируется на время заливки/обработки/удаления данных.Блокировать можно и саму постоянную таблицу с помощью команды lock table, но конкурентность приложения от этого может сильно ухудшиться. :-(

MBGПример: есть разделенная по месяцам таблица с данными за два года (24 части). Построение отчета за месяц выполняется как минимум на порядок быстрее.Интересно, за счет чего такая разница. Кажется, что безусловный seq scan по партиционной таблице против bitmap index scan по полной таблице конечно должен выигрывать, но не на порядок. Если не сложно, вы могли бы привести explain analyze этих двух запросов.

MBGНапример, имеем 24 таблицы по месяцам, при построении отчета за полугодие потребуется просмотреть лишь 6 таблиц, а не все данные.Бред. Что по парционным таблицам, что по полной таблице, нужно просмотреть одинаковое количество tuples. При этом количество страниц, на которых они располагаются, может оказаться сильно разным, а может и примерно одинаковым. "Все данные" в большой таблице просматривать не обязательно, ведь кроме seq scan придумали index scan и bitmap index scan. :-)

MBGПлюс к тому, операционка будет кэшировать файлы часто используемых таблиц, в то время как на файл одной огромной таблицы не хватит памяти.Бред. Операционка кэширует не файлы, а страницы! Ей фиолетово, являются ли эти страницы кусочками большого файла или маленьких. :-)

MBGЧто касается индексирования, партишионирования и кластеризации - это вещи must have, которыми должен пользоваться каждый разработчик.К сожалению, да. В идеале хотелось бы, чтобы СУБД сама об этом заботилась, а разработчик отдыхал. :-)

MBGЕсли архитектура СУБД разрабатывается с учетом этого, производительность получается на высоком уровне.Однако не в каждой БД это необходимо делать. Мы, например, пока обходимся без партиционирования и кластеризации.

MBGесли обновления данных проводить в огромной таблице, кластеризация бесполезна, все равно будет непрерывная сегментация данных вследствии обновленийВозможно, я опять неправильно понял вашу мысль, точнее сказать, почти ничего не понял. Уверен, что кластеризация огромной таблицы может оказаться очень полезной.

MBGв то время как обновление одной или двух подтаблиц разделенной таблицы пользуется всеми выгодами кластеризацииНе согласен. Кластеризация одинаково полезна, как для частичных "больших" таблиц, так и для одной "огромной" таблицы.

MBGесть разница в приоритетах кэширования изменяемых и постоянных данных операционной системойВ любой операционной системе? Киньте пожалуйста ссылки.

MBG"Кластеризация бесполезна" подразумевает, что в тех случаях, когда в таблице много изменяющихся данных, кластеризация будет только время отнимать.Теперь понятно. Полностю с этим согласен.

Просто из вашей фразы "если обновления данных проводить в огромной таблице, кластеризация бесполезна" не было понятно, в этой огромной таблице обновляется много данных или мало. В первом случае кластеризация менее эффективна, возможно и бесполезна, а во втором может оказаться очень эффективной и полезной.

MBGПо поводу кэширования ссылок не приведу, замечу лишь, что кэшируются _часто_ используемые данные. Те данные, которые не меняются, имеют большее число обращений в течении своей "жизни" (просто в силу большей ее продолжительности), чем быстро меняющиеся, что приводит к лучшему кэшированию именно постоянных данных.Я не спец по осям. Имхо, предположение "кэшируются часто используемые данные" абсолютно верное, а вот вывод "приводит к лучшему кэшированию именно постоянных данных" вы сделали неправильный.

В памяти создается новая (измененная или добавленная) версия данных, происходит commit, записывается на диск. Ведь затем эти данные из памяти не очищаются, они остаются закэшированными. Хотя это суть обновленные данные, а не постоянные.

MBG'commit' - это на уровне базы данных, а не операционкиДа, но именно при commit-е происходит flush на диск. (Зависит от wal_sync_method.)

MBGВ самой базе в первую очередь кэшируются часто используемые данные, а не последние записанные - скорее, последние прочитанные.Откуда такая информация? Что вообще есть кэш самой базы данных? Какими параметрами постгреса настраивается?

MBGА операционка как закэшировала страницы с предыдущей версией данных, так и будет их держать в кэшеВерно. А наряду с ними в кэше будет держать страницы с новыми версиями данных.

MBGоткуда ОСь знает, что это старая версияПравильно, не знает. Поэтому ОС будет какое-то время держать эти уже ненужные данные в кэше. До тех пор пока не пройдет время N, в течение которого к этим данным никто не обращался, ОС посчитает их неиспольуемыми и сможет заменить их в кэше другими данными.

MBGКажется, мы немного о разном. Я рассматриваю случай, когда оперативная память составляет порядка несколько процентов от объема базы. Тут уже нет смысла говорить о кэшировании "и старых и новых страниц", просто негде их кэшировать.Мы говорим об одном и том же. Разница количественная (объем оперативной памяти), но не качественная (алгоритм кэширования). То есть просто время N устаревания неиспользуемой информации в кэше, о котором я говорил, при меньшем доступном объеме оперативной памяти будет меньшим.

MBGОбычно записывается сразу пакет изменений.Как именно это можно сделать?