Ефект роздування таблиць та індексів (bloat) широко відомий і присутній не тільки в Postgres. Є способи боротьби з ним "з коробки" на кшталт VACUUM FULL або CLUSTER, але вони блокують таблиці під час роботи і тому не можуть бути використані.
У статті буде трохи теорії про те, як виникає bloat, як з ним можна боротися, про deferred constraints та про проблеми, які вони привносять у використання розширення pg_repack.
Ця стаття написана на основі на PgConf.Russia 2020 року.
Чому виникає bloat
В основі Postgres лежить багатоверсійна модель (). Її суть у тому, що кожен рядок у таблиці може мати кілька версій, при цьому транзакції бачать не більше однієї з цих версій, але необов'язково одну й ту саму. Це дозволяє працювати кільком транзакціям одночасно і практично не впливати один на одного.
Очевидно, що всі ці версії потрібно зберігати. Postgres працює з пам'яттю посторінково і сторінка - це мінімальний обсяг даних, який можна рахувати з диска або записати. Давайте розглянемо невеликий приклад, щоб зрозуміти, як це відбувається.
Допустимо, у нас є таблиця, в яку ми додали кілька записів. На першій сторінці файлу, де зберігається таблиця, з'явилися нові дані. Це живі версії рядків, які доступні іншим транзакціям після коміту (для простоти будемо вважати, що рівень ізоляції Read Committed).
Потім ми оновили один із записів і тим самим помітили стару версію як неактуальну.
Крок за кроком, оновлюючи та видаляючи версії рядків, ми отримали сторінку, де приблизно половина даних – це “сміття”. Ці дані не видно жодної транзакції.
У Postgres існує механізм , що вичищає неактуальні версії та звільняє місце для нових даних. Але якщо він налаштований мало агресивно або зайнятий роботою в інших таблицях, то "сміттєві дані" залишаються, і нам доводиться використовувати додаткові сторінки для нових даних.
Так, у нашому прикладі в якийсь момент часу таблиця складатиметься з чотирьох сторінок, але живих даних у ній буде лише половина. В результаті, при зверненні до таблиці ми вичитуватимемо набагато більше даних, ніж потрібно.
Навіть якщо зараз VACUUM видалить усі неактуальні версії рядків, ситуація не покращиться кардинально. У нас з'явиться вільне місце у сторінках або навіть цілі сторінки для нових рядків, але ми, як і раніше, будемо вичитувати більше даних, ніж потрібно.
До речі, якби повністю порожня сторінка (друга в нашому прикладі) опинилася наприкінці файлу, VACUUM зміг би її обрізати. Але зараз вона знаходиться посередині, тож зробити з нею нічого не вийде.
Коли кількість таких порожніх або сильно розряджених сторінок стає більшою, що називається bloat, це починає позначатися на продуктивності.
Все описане вище – механіка виникнення bloat у таблицях. В індексах це відбувається приблизно так само.
А чи маю bloat?
Є кілька способів визначити, чи є у вас bloat. Ідея першого - використання внутрішньої статистики Postgres, в якій міститься приблизна інформація про кількість рядків у таблицях, кількість "живих" рядків та ін. В інтернеті можна знайти безліч варіацій вже готових скриптів. Ми за основу взяли від PostgreSQL Experts, який може оцінити bloat таблиць разом з toast і bloat btree-індексів. На наш досвід його похибка становить 10-20%.
Інший спосіб - використовувати розширення , яке дозволятиме заглянути всередину сторінок і отримати як оцінне, так і точне значення bloat. Але у другому випадку доведеться просканувати всю таблицю.
Невелике значення bloat, до 20%, ми вважаємо за прийнятне. Його можна вважати як аналог fillfactor для и . При 50% і більше можуть початися проблеми з продуктивністю.
Способи боротьби з bloat
У Postgres є кілька способів боротьби з bloat "з коробки", проте вони далеко не завжди і не всім можуть підійти.
Налаштувати AUTOVACUUM, щоб bloat не виникало. А якщо точніше, щоб він тримався на прийнятному для вас рівні. Здається, що це “капітанська” порада, але насправді цього не завжди легко досягти. Наприклад, у вас триває активна розробка з регулярною зміною схеми даних або відбувається якась міграція даних. Як наслідок, ваш профіль навантаження може часто змінюватись і, як правило, він буває різним для різних таблиць. Значить, вам потрібно постійно працювати трохи на випередження і підлаштовувати AUTOVACUUM під профіль кожної таблиці, що змінюється. Але очевидно, що це зробити непросто.
Інша поширена причина того, що AUTOVACUUM не встигає обробляти таблиці, є наявність тривалих транзакцій, які не дозволяють йому вичищати дані через те, що вони доступні цим транзакціям. Рекомендація тут також очевидна – позбутися “високих” транзакцій та мінімізувати час активних транзакцій. Але якщо навантаження на вашу програму – це гібрид OLAP і OLTP, то у вас одночасно може бути як безліч частих оновлень і коротких запитів, так і тривалі операції – наприклад, побудова будь-якого звіту. У такій ситуації варто задуматися про рознесення навантаження на різні бази, що дозволить провести більш тонке налаштування кожної з них.
Ще один приклад - навіть якщо профіль однорідний, але БД знаходиться під дуже високим навантаженням, то навіть максимально агресивний AUTOVACUUM може не справлятися, і bloat виникатиме. Масштабування (вертикальне чи горизонтальне) – єдине рішення.
Як бути в ситуації, коли AUTOVACUUM ви налаштували, але bloat продовжує рости.
Команда VACUUM FULL перебудовує вміст таблиць та індексів та залишає в них лише актуальні дані. Для усунення bloat вона працює ідеально, але під час її виконання захоплюється ексклюзивне блокування на таблицю (AccessExclusiveLock), яка не дозволить виконувати запити до цієї таблиці навіть select-и. Якщо ви можете дозволити собі зупинку вашого сервісу або його частини на якийсь час (від десятків хвилин до кількох годин залежно від розміру БД та заліза), то цей варіант – найкращий. Ми, на жаль, не встигаємо вигнати VACUUM FULL за час запланованого maintenance, тому такий спосіб нам не підходить.
Команда Кластер так само перебудовує вміст таблиць, як і VACUUM FULL, при цьому дозволяє вказати індекс, згідно з яким дані будуть фізично впорядковані на диску (але в майбутньому для нових рядків порядок не гарантується). У певних ситуаціях це непогана оптимізація ряду запитів – з читанням кількох записів за індексом. Недолік команди той же, що у VACUUM FULL – вона блокує таблицю під час роботи.
Команда ПОВТОРНИЙ ІНДЕКС схожа на дві попередні, але виконує перебудову конкретного індексу чи всіх індексів таблиці. Блокування трохи слабше: ShareLock на таблицю (заважає модифікаціям, але дозволяє виконувати select) і AccessExclusiveLock на індекс, що перебудовується (блокує запити з використанням цього індексу). Однак у 12-й версії Postgres з'явився параметр , що дозволяє перебудовувати індекс, не блокуючи паралельне додавання, зміну або видалення записів.
У ранніх версіях Postgres можна досягти результату, схожого з REINDEX CONCURRENTLY, за допомогою . Він дозволяє створити індекс без суворого блокування (ShareUpdateExclusiveLock, який не заважає паралельним запитам), потім підмінити старий індекс на новий та видалити старий індекс. Це дозволяє усунути bloat індексів, не заважаючи роботі вашої програми. Важливо врахувати, що з перебудові індексів буде додаткове навантаження на дискову підсистему.
Таким чином, якщо для індексів є способи усунення bloat "на гарячу", то для таблиць їх немає. Тут у справу вступають різні зовнішні розширення: (раніше pg_reorg), , та інші. У рамках цієї статті я не порівнюватиму їх і розповім тільки про pg_repack, яке після деякої доопрацювання ми використовуємо у себе.
Як працює pg_repack
Припустимо, у нас є цілком собі звичайна таблиця – з індексами, обмеженнями та, на жаль, із bloat. Першим кроком pg_repack створює лог-таблицю, щоб зберігати дані про всі зміни під час роботи. Тригер буде реплікувати ці зміни на кожен insert, update та delete. Потім створюється таблиця, аналогічна вихідної структурою, але не мають індексів і обмежень, ніж уповільнювати процес вставки даних.
Далі pg_repack переносить у нову таблицю дані зі старої, автоматично фільтруючи усі неактуальні рядки, а потім створює індекси для нової таблиці. За час виконання всіх цих операцій у лог-таблиці накопичуються зміни.
Наступний крок – перенести зміни до нової таблиці. Перенесення виконується в кілька ітерацій, і коли в лог-таблиці залишається менше 20 записів, pg_repack захоплює жорстке блокування, переносить останні дані і замінює стару таблицю на нову в системних таблицях Postgres. Це єдиний і дуже короткий час, коли ви не зможете працювати з таблицею. Після цього стара таблиця та таблиця з логами видаляються і у файловій системі звільняється місце. Процес завершено.
Теоретично все виглядає добре, що ж практично? Ми протестували pg_repack без навантаження та під навантаженням, перевірили його роботу у разі передчасної зупинки (простіше кажучи, за Ctrl+C). Усі тести були позитивними.
Ми вирушили на прод - і тут все пішло не так, як ми очікували.
Перший млинець
На першому кластері ми отримали помилку про порушення унікального обмеження:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.
Це обмеження мало автозгенеровану назву index_16508 – його створив pg_repack. За атрибутами, що входять до його складу, ми визначили “наше” обмеження, яке відповідає. Проблема виявилося в тому, що це не зовсім нормальне обмеження, а відкладене (), тобто. його перевірка виконується пізніше, ніж SQL-команда, що призводить до несподіваних наслідків.
Deferred constraints: навіщо потрібні та як працюють
Трохи теорії про deferred-обмеження.
Розглянемо простий приклад: у нас є таблиця-довідник автомобілів із двома атрибутами – найменуванням та порядком автомобіля у довіднику.
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique
);
Припустимо, нам знадобилося поміняти перший та другий автомобілі місцями. Рішення "в лоб" - оновити перше значення на друге, а друге на перше:
begin;
update cars set ord = 2 where name = 'audi';
update cars set ord = 1 where name = 'bmw';
commit;
Але при виконанні цього коду ми очікуємо отримаємо порушення обмеження, тому що порядок значень таблиці унікальний:
[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.
Як зробити інакше? Варіант перший: додати додаткову заміну значення на порядок, якого гарантовано немає у таблиці, наприклад “-1”. У програмуванні це називається "обміном значень двох змінних через третю". Єдиний недолік цього методу – додатковий update.
Варіант другий: перепроектувати таблицю, щоб використовувати значення даних з плаваючою точкою замість цілих чисел. Тоді при оновленні значення з 1, наприклад, на 2.5 перший запис автоматично "встане" між другою та третьою. Це рішення працююче, але є два обмеження. По-перше, воно не підійде вам, якщо значення використовується десь в інтерфейсі. По-друге, залежно від точності типу даних ви матимете обмежену кількість можливих вставок до проведення перерахунку значень усіх записів.
Варіант третій: зробити обмеження відкладеним, щоб воно перевірялося лише на момент коміту:
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique deferrable initially deferred
);Оскільки логіка нашого початкового запиту гарантує, що на момент комміту всі значення унікальні, він виконається успішно.
Розглянутий вище приклад, звісно, дуже синтетичний, але ідею розкриває. У нашому додатку ми використовуємо відкладені обмеження для реалізації логіки, яка відповідає за вирішення конфліктів за одночасної роботи користувачів із загальними об'єктами-віджетами на дошці. Використання таких обмежень дозволяє зробити прикладний код трохи простіше.
Загалом, залежно від типу обмеження у Postgres існує три рівні гранулярності їх перевірки: рівень рядка, транзакції та виразу.
Джерело:
CHECK і NOT NULL завжди перевіряються лише на рівні рядка, інших обмежень, як видно з таблиці, є різні варіанти. Детальніше можна почитати .
Якщо коротко підсумувати, то відкладені обмеження у низці ситуацій дають зрозуміліший для читання код і менше команд. Однак за це доводиться платити ускладненням процесу дебагу, оскільки момент виникнення помилки та момент, коли ви про неї дізнаєтесь, рознесені у часі. Ще одна можлива проблема пов'язана з тим, що планувальник не завжди може побудувати оптимальний план, якщо у запиті бере участь відкладене обмеження.
Доробка pg_repack
Ми розібралися з тим, що таке відкладені обмеження, але як вони пов'язані з проблемою? Згадаймо помилку, яку ми раніше отримали:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.Вона виникає у момент копіювання даних із лог-таблиці у нову таблицю. Це дивно, т.к. дані в лог-таблиці комітуються разом із даними вихідної таблиці. Якщо вони задовольняють обмеженням вихідної таблиці, то як вони можуть порушувати ті самі обмеження в новій?
Як виявилося, корінь проблеми приховується у попередньому кроці роботи pg_repack, на якому створюються лише індекси, але не обмеження: у старій таблиці було унікальне обмеження, а в новій замість нього створився унікальний індекс.
Тут важливо відзначити, що й обмеження просте, а чи не відкладене, то створений замість нього унікальний індекс рівносильний цьому обмеження, т.к. Унікальні обмеження в Postgres реалізуються за допомогою створення унікального індексу. Але у випадку відкладеного обмеження поведінка не однакова, тому що індекс не може бути відкладеним і завжди перевіряється в момент виконання sql-команди.
Таким чином, суть проблеми полягає у “відкладеності” перевірки: у вихідній таблиці вона відбувається у момент комміту, а в новій – у момент виконання sql-команди. Значить, нам потрібно зробити так, щоб перевірки виконувались однаково в обох випадках: або завжди відкладено, або завжди відразу.
Отже, які ідеї ми мали.
Створити індекс, аналогічний deferred
Перша ідея – виконувати обидві перевірки у режимі immediate. Це може породити кілька false positive спрацьовувань обмеження, але якщо їх буде небагато, то це не позначиться на роботі користувачів, оскільки для них такі конфлікти – нормальна ситуація. Вони відбуваються, наприклад, коли два користувача починають одночасно редагувати той самий віджет, і клієнт другого користувача не встигає отримати інформацію про те, що віджет вже заблокований на редагування першим користувачем. У такій ситуації сервер відповідає другому користувачеві відмовою, а його клієнт відкочує зміни та блокує віджет. Трохи згодом, коли перший користувач завершить редагування, другий отримає інформацію, що віджет більше не заблокований, і зможе повторити свою дію.
Щоб перевірки були завжди у невідкладному режимі, ми створили новий індекс, аналогічний до оригінального відкладеного обмеження:
CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;На тестовому оточенні ми отримали лише кілька очікуваних помилок. Успіх! Знову запустили pg_repack на проді та отримали 5 помилок на першому кластері за годину роботи. Це прийнятний результат. Проте вже на другому кластері кількість помилок збільшилась у рази і нам довелося зупинити pg_repack.
Чому так сталося? Імовірність виникнення помилки залежить від того, як багато користувачів працюють одночасно з одними і тими ж віджетами. Зважаючи на все, у той момент з даними, що зберігаються на першому кластері, було набагато менше конкурентних змін, ніж на інших, тобто. нам просто "пощастило".
Ідея не спрацювала. На той момент ми бачили два інші варіанти рішення: переписати наш прикладний код, щоб відмовитися від відкладених обмежень, або “навчити” pg_repack працювати з ними. Ми обрали другий.
Замінити індекси у новій таблиці на відстрочені обмеження з вихідної таблиці
Мета доопрацювання була очевидною – якщо вихідна таблиця має відкладене обмеження, то для нової потрібно створювати таке обмеження, а не індекс.
Щоб перевіряти наші зміни, ми написали простий тест:
- таблиця з відкладеним обмеженням та одним записом;
- вставляємо в циклі дані, які конфліктують із наявним записом;
- робимо update – дані вже не конфліктують;
- комітім зміни.
create table test_table
(
id serial,
val int,
constraint uk_test_table__val unique (val) deferrable initially deferred
);
INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
UPDATE test_table set val = i where id = v_id;
COMMIT;
END;
END LOOP;Вихідна версія pg_repack завжди падала на першому insert, доопрацьована версія працювала без помилок. Чудово.
Ідемо на прод і знову отримуємо помилку на тій же фазі копіювання даних з лог-таблиці в нову:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.Класична ситуація: на тестових оточеннях все працює, а на проді – ні?!
APPLY_COUNT та стик двох батчів
Ми почали аналізувати код буквально рядково і виявили важливий момент: переливання даних з лог-таблиці в нову відбувається батчами, константа APPLY_COUNT вказувала на розмір батча:
for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);
if (num > MIN_TUPLES_BEFORE_SWITCH)
continue; /* there might be still some tuples, repeat. */
...
}Проблема в тому, що дані вихідної транзакції, в якій кілька операцій можуть потенційно порушити обмеження, при перенесенні можуть потрапити на стик двох батчів - половина команд буде закомітчина в першому батчі, а друга половина - у другому. І тут як пощастить: якщо команди у першому батчі нічого не порушують, то все гаразд, а якщо порушують — відбувається помилка.
APPLY_COUNT дорівнює 1000 записів, що пояснює, чому наші тести проходили успішно – вони не покривали випадку “стику батчів”. Ми використовували дві команди - insert і update, тому рівно 500 транзакцій по дві команди завжди поміщалися в батч і ми не мали проблем. Після додавання другого update наша редагування перестала працювати:
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
UPDATE test_table set val = i where id = v_id;
UPDATE test_table set val = i where id = v_id; -- one more update
COMMIT;
END;
END LOOP;Отже, наступне завдання – зробити те щоб дані з вихідної таблиці, які змінювалися у однієї транзакції, потрапляли у нову таблицю також усередині однієї транзакції.
Відмова від батчингу
І в нас знову було два варіанти рішення. Перший: давайте взагалі відмовимося від розбиття на батчі і робитимемо перенесення даних однією транзакцією. На користь цього рішення була його простота – необхідні зміни коду мінімальні (до речі, у старіших версіях тоді pg_reorg працював саме так). Але є проблема — ми створюємо тривалу транзакцію, а це, як було сказано раніше, — загроза для виникнення нового bloat.
Друге рішення — складніше, але, мабуть, правильніше: створити в лог-таблиці стовпець з ідентифікатором транзакції, яка додала дані в таблицю. Тоді при копіюванні даних ми зможемо групувати їх за цим атрибутом і гарантувати, що пов'язані зміни переносяться спільно. Батч буде формуватися з декількох транзакцій (або однієї великої) і його розмір змінюватиметься в залежності від того, як багато даних змінили в цих транзакціях. Важливо відзначити, що оскільки дані різних транзакцій потрапляють до лог-таблиці у випадковому порядку, то вже не вийде читати її послідовно, як це було раніше. seqscan при кожному запиті з фільтрацією по tx_id – це занадто дорого, потрібен індекс, але й він уповільнить роботу методу через накладні витрати на його оновлення. Загалом, як завжди, потрібно чимось жертвувати.
Отже, ми вирішили почати з першого варіанта як простішого. Для початку необхідно було зрозуміти, чи буде тривала транзакція реальною проблемою. Оскільки основне перенесення даних зі старої таблиці в нову відбувається також в одній тривалій транзакції, то питання трансформувалося в "наскільки ми збільшимо цю транзакцію?" Тривалість першої транзакції залежить переважно від розміру таблиці. Тривалість нової – оскільки багато змін накопичиться у таблиці під час переливання даних, тобто. від інтенсивності навантаження. Прогін pg_repack відбувався під час мінімального навантаження на сервіс, і обсяг змін був незрівнянно малий порівняно з вихідним обсягом таблиці. Ми вирішили, що можемо знехтувати часом нової транзакції (порівняно усереднено це 1ч і 2-3 хвилини).
Експерименти були позитивні. Запуск на продажі теж. Для наочності - картинка з розміром однієї з баз після прогону:
Оскільки це рішення нас повністю влаштувало, пробувати реалізувати друге ми не стали, але розглядаємо можливість його обговорення з розробниками розширення. Наші поточні доопрацювання, на жаль, поки що не готові до публікації, оскільки ми вирішили проблему тільки з унікальними відкладеними обмеженнями, а для повноцінного патчу необхідно підтримати й інші типи. Сподіваємось, що вдасться зробити це у майбутньому.
Можливо, у вас виникло питання, навіщо ми взагалі вплуталися в цю історію з доопрацюванням pg_repack, а чи не стали, наприклад, використовувати його аналоги? У якийсь момент ми теж думали про це, але позитивний досвід його використання раніше, на таблицях без відкладених обмежень, мотивував те, щоб спробувати розібратися в суті проблеми та виправити її. До того ж для використання інших рішень також потрібен час на проведення тестів, тому ми вирішили, що спершу спробуємо виправити проблему в ньому, і якщо зрозуміємо, що не зможемо це зробити за розумний час, то тоді почнемо розглядати аналоги.
Висновки
Що ми можемо рекомендувати на основі власного досвіду:
- Монітор ваш bloat. На основі даних моніторингу ви зможете зрозуміти, наскільки добре налаштовано autovacuum.
- Налаштуйте AUTOVACUUM, щоб тримати bloat на допустимому рівні.
- Якщо все ж таки bloat росте і ви не можете його побороти за допомогою засобів "з коробки", не бійтеся використовувати зовнішні розширення. Головне – все добре тестувати.
- Не бійтеся доопрацьовувати зовнішні рішення під свої потреби – іноді це може бути ефективніше і навіть простіше, ніж зміна вашого власного коду.
Джерело: habr.com