Колись у далекому майбутньому автоматичне видалення непотрібних даних буде одним із важливих завдань СУБД [1]. Поки що нам самим потрібно дбати про видалення або переміщення непотрібних даних на менш дорогі системи зберігання. Допустимо, ви вирішили видалити кілька мільйонів рядків. Досить просте завдання, особливо якщо відомо умова і є відповідний індекс. "DELETE FROM table1 WHERE col1 = :value" - що може бути простіше, так?
Відео:
-
Я у програмному комітеті Highload з першого року, тобто з 2007-го.
-
І з Postgres я з 2005 року. Використовував його у багатьох проектах.
-
Гурт із RuPostges теж з 2007-го року.
-
Ми на Meetup доросли до 2100+ учасників. Це друге місце у світі після Нью-Йорка, обігнали Сан-Франциско вже давно.
-
Декілька років я живу в Каліфорнії. Займаюся більше американськими компаніями, зокрема великими. Вони активні користувачі Postgres. І там виникають усілякі цікаві штуки.
– це моя компанія. Ми займаємося тим, що автоматизуємо завдання, які усувають уповільнення розробки.
Якщо ви щось робите, то іноді навколо Postgres виникають якісь затики. Припустимо, вам потрібно почекати, поки адмін підніме тестовий стенд, або вам потрібно почекати, поки DBA на вас відреагує. І ми знаходимо такі вузькі місця в процесі розробки, тестування та адміністрування та намагаємось їх усунути за допомогою автоматизації та нових підходів.
Я був нещодавно на VLDB у Лос-Анджелесі. Це найбільша конференція з баз даних. І там була доповідь про те, що в майбутньому СУБД не лише зберігатимуть, а й автоматично видалятимуть дані. Це нова тема.
Даних все більше у світі зетабайт – це 1 петабайт. І зараз уже оцінюється, що у нас більше 000 зетабайт даних у світі зберігається. І їх стає дедалі більше.
І що із цим робити? Зрозуміло, що треба видаляти. Ось посилання на цю цікаву доповідь. Але поки що у СУБД це не реалізовано.
Ті, хто вміють рахувати гроші, хочуть двох речей. Вони хочуть, щоб ми видаляли, тому технічно ми маємо вміти це робити.
Те, що далі я розповідатиму, це деяка абстрактна ситуація, яка включає в себе купу реальних ситуацій, тобто якась компеляція того, що відбувалося насправді зі мною та навколишніми базами даних багато разів, багато років. Граблі скрізь і на них постійно наступають.
Припустимо, у нас є база або кілька баз, які ростуть. І деякі записи – це очевидне сміття. Наприклад, користувач щось там почав робити, не закінчив. І через якийсь час ми знаємо, що це незакінчене можна не зберігати. Т. е. якісь сміттєві речі ми хотіли б почистити, щоб заощадити місце, покращити продуктивність тощо.
Загалом ставиться завдання автоматизувати видалення конкретних речей, конкретних рядків у якійсь таблиці.
І у нас є такий запит, про який ми сьогодні говоритимемо, тобто про видалення сміття.
Попросили досвідченого розробника це зробити. Він узяв цей запит, перевірив у себе все працює. Протестував на staging – все гаразд. Викотили – все працює. Раз на добу ми запускаємо це все добре.
БД росте та росте. Щодобовий DELETE трохи повільніше працювати починає.
Потім ми розуміємо, що у нас зараз маркетингова компанія і трафік буде в кілька разів більшим, тому вирішуємо зайві речі тимчасово на паузу поставити. І забуваємо повернути.
За кілька місяців згадали. А той розробник звільнився чи зайнятий чимось іншим, доручили іншому повернути.
Він перевірив на dev, на staging – все Ок. Звичайно, треба ще почистити те, що накопичилося. Він перевірив, чи все працює.
Що відбувається далі? Далі у нас все кидається. Роняється так, що в нас у якийсь момент все лягає. Все шоковане, ніхто не розуміє, що відбувається. І потім з'ясовується, що річ у цьому DELETE була.
Що пішло негаразд? Ось тут подано список того, що могло піти не так. Що з цього найважливіше?
-
Наприклад, не було review, тобто DBA-експерт не подивився. Він би досвідченим поглядом відразу знайшов би проблему, до того ж він має доступ до prod, де накопичилося кілька мільйонів рядків.
-
Можливо, перевіряли якось не так.
-
Можливо, залізо застаріло і потрібно апгрейд для цієї бази робити.
-
Або щось із сомою базою даних не так, і нам із Postgres на MySQL треба переїхати.
-
Або, можливо, з операцією щось не так.
-
Можливо, які помилки в організації роботи і потрібно когось звільнити, а найняти найкращих людей?
Не було перевірки DBA. Якби DBA був, він побачив би ці кілька мільйонів рядків і навіть без жодних експериментів сказав би: «Так не роблять». Припустимо, якби цей код був у GitLab, GitHub і був би процес code review і не було такого, що без затвердження DBA ця операція пройде на prod, то, очевидно, DBA сказав би: «Так не можна робити».
І він би сказав, що у вас з disk IO будуть проблеми і всі процеси розлютуються, можуть бути локи, а також ви заблокуєте автовакуум на купу хвилин, тому це не добре.
Друга помилка – перевіряли не там. Ми постфактум побачили, що сміттєвих даних накопичилося на prod багато, а у розробника не було в цій базі накопичених даних, та й на staging особливо ніхто це сміття не створював. Відповідно, там було 1 рядків, які швидко відпрацювали.
Ми розуміємо, що наші тести слабкі, тобто процес, який побудований, не ловить проблеми. Не проводили адекватного БД-експерименту.
Ідеальний експеримент бажано проводити на такому ж обладнанні. На такому самому обладнанні не завжди виходить це зробити, але дуже важливо, щоб це була повнорозмірна копія бази даних. Це те, що я проповідую кілька років. І рік тому я про це говорив, можете на YouTube це все подивитися.
Можливо, у нас обладнання погане? Якщо подивитися, то latency підскочило. Ми побачили, що утилізація 100%. Звичайно, якщо б це були сучасні NVMe диски, то, напевно, нам було б набагато легше. І, можливо, ми б не лягли від цього.
Якщо у вас хмари, то апгрейд там легко робиться. Здійняли нові репліки на новому залозі. Switchover. І все гаразд. Досить легко.
А чи можна якось менше диски чіпати? І тут якраз за допомогою DBA ми пірнаємо у певну тему, яка називається checkpoint tuning. З'ясовується, що у нас не було проведено checkpoint tuning.
Що таке checkpoint? Це є у будь-якій СУБД. Коли у вас дані в пам'яті змінюються, вони не одразу записуються на диски. Інформація про те, що дані змінилися, спочатку записується в випереджаючий журнал, write-ahead log. І в якийсь момент СУБД вирішує, що час вже реальні сторінки на диск скинути, щоб, якщо у нас буде збій, менше робити REDO. Це як у іграшці. Якщо нас уб'ють, ми почнемо гру з останнього checkpoint. І всі СУБД це реалізують.
Налаштування Postgres відстають. Вони розраховані на 10-15-річну давність обсяги даних та операцій. І checkpoint – не виняток.
Ось ця інформація з нашого звіту з Postgres check-up, тобто автоматична перевірка здоров'я. І ось якась база у кілька терабайт. І добре видно, що примусові checkpoints майже в 90% випадків.
Це що означає? Там є два налаштування. Checkpoint може за часомнаступити, наприклад, в 10 хвилин. Або він може настати, коли наповнилося чимало даних.
І за умовчанням max_wal_saze виставлений на 1 гігабайт. За фактом, це реально трапляється у Postgres через 300-400 мегабайт. Ви змінили стільки даних і у вас checkpoint відбувається.
І якщо це ніхто не тюнував, а сервіс виріс, і компанія заробляє купу грошей, у неї багато транзакцій, то checkpoint настає раз на хвилину, іноді і раз на 30 секунд, а іноді навіть накладаються один на одного. Це вже зовсім погано.
І нам потрібно зробити так, щоб він наступав рідше. Т. е. ми можемо підняти max_wal_size. І він наступатиме рідше.
Але ми розробили в себе цілу методологію, як це зробити правильніше, тобто як приймати рішення про вибір налаштувань, чітко спираючись на конкретні дані.
Відповідно, ми робимо дві серії експериментів над базами даних.
Перша серія – ми змінюємо max_wal_size. І проводимо масову операцію. Спочатку робимо її на дефолтному налаштуванні в 1 гігабайт. І робимо масовий DELETE багатьох мільйонів рядків.
Видно, як нам тяжко. Дивимося, що disk IO дуже поганий. Дивимося, скільки WAL ми згенерували, тому що це дуже важливо. Дивимося, скільки разів checkpoint стався. І бачимо, що недобре.
Далі ми збільшуємо max_wal_size. Повторюємо. Збільшуємо, повторюємо. І так багато разів. У принципі, 10 точок – це добре, де 1, 2, 4, 8 гігабайт. І дивимося на поведінку конкретної системи. Зрозуміло, що тут обладнання має бути як на prod. У вас повинні бути ті ж диски, скільки пам'яті і налаштування Postgres такі ж.
І таким чином ми обміняємо нашу систему, і знаємо, як поводитиметься СУБД при поганому масовому DELETE, як буде вона checkpoint'итися.
Checkpoint російською – це контрольні точки.
Приклад: DELETE кілька млн рядків за індексом, рядки розкидані по сторінках.
Ось приклад. Це — деяка база. І при дефолтному налаштуванні 1 гігабайт для max_wal_size дуже добре видно, що у нас диски на запис йдуть у полицю. Ось така картинка це типовий симптом дуже хворого пацієнта, тобто йому реально було погано. І тут була одна єдина операція, тут був якраз DELETE кілька мільйонів рядків.
Якщо таку операцію пускати в prod, то ми ляжемо, бо видно, що один DELETE вбиває нас у полицю.
Далі де 16 гігабайт видно, що вже зубчики пішли. Зубчики - це вже краще, тобто ми стукаємося об стелю, але вже не так погано. Невелика свобода там з'явилася. Справа – це запис. І кількість операцій – другий графік. І видно, що ми вже трохи легше дихали, коли 16 гігабайт.
А де 64 гігабайти видно, що зовсім краще стало. Вже зубчики яскраво виражені, з'являється більше можливостей вижити іншим операціям і щось зробити з диском.
Чому так?
Я трошки занурюватимуся в подробиці, але ця тема, як проводити checkpoint tuning, може вилитися в цілу доповідь, тому я не сильно вантажитиму, але трішки позначу, які там складності є.
Якщо checkpoint занадто часто трапляється, і ми оновлюємо наші рядки не послідовно, а знаходимо за індексом, що добре, тому що ми не всю таблицю видаляє, то може статися так, що спочатку ми першу сторінку помацали, потім тисячну, а потім повернулися до першої . І якщо між цими заходами в першу сторінку checkpoint її вже на диск зберіг, то він ще раз її зберігатиме, тому що ми її вдруге забруднили.
І ми будемо змушувати checkpoint її багато разів зберігати. Як би виникають надлишкові операції для нього.
Але це ще не все. У Postgres сторінки важать 8 кілобайт, а Linux 4 кілобайт. І є налаштування full_page_writes. За замовчуванням вона увімкнена. І це правильно, тому що, якщо ми її виключимо, то є небезпека, що при збої тільки половина сторінки збережеться.
Поведінка запису в WAL випереджаючого журналу така, що, коли у нас checkpoint трапився і ми сторінку вперше змінюємо, то в випереджаючий журнал потрапляє вся сторінка цілком, тобто всі 8 кілобайт, хоча ми змінювали тільки рядок, який важить 100 байт . І ми змушені повністю сторінку записати.
У наступних змінах будуть лише конкретний кортеж, але вперше всю записуємо.
І, відповідно, якщо checkpoint ще раз стався, то ми повинні знову з нуля все починати і всю сторінку запихати. При частих checkpoints, коли ми гуляємо по одних і тих же сторінках, full_page_writes = on буде більше, ніж могло б бути, тобто ми більше WAL генеруємо. Більше вирушає на репліки, архів, диск.
І, відповідно, дві надмірності у нас виникають.
Якщо ми збільшуємо max_wal_size, виходить, що ми полегшуємо роботу і checkpoint, і wal writer. І це класно.
Давайте поставимо терабайт і з цим житимемо. Що в цьому поганого? Це погано, тому що у разі збою ми підніматимемося годинами, тому що checkpoint був давно і вже багато що змінилося. І нам все це REDO треба зробити. І тому ми робимо другу серію експериментів.
Ми робимо операцію та дивимось, коли checkpoint близький до того, щоб завершиться, ми робимо kill -9 Postgres спеціально.
І після цього стартуємо його заново, і дивимося, як довго він підніматиметься на цьому устаткуванні, тобто скільки він робитиме REDO у цій поганій ситуації.
Двічі зазначу, що ситуація погана. По-перше, ми впали прямо перед завершенням checkpoint, відповідно нам програвати багато треба. І, по-друге, ми мали масивну операцію. І якби checkpoints були за тайм-аутом, то, швидше за все, менше WAL згенерувалося б з моменту останнього checkpoint. Т. е. це двічі невдаха.
Ми заміряємо таку ситуацію для різного розміру max_wal_size і розуміємо, що якщо max_wal_size 64 гігабайти, то в подвійній гіршій ситуації ми будемо підніматися 10 хвилин. І думаємо – влаштовує нас це чи ні. Це бізнес-питання. Ми повинні показати цю картину тим, хто відповідає за бізнес-рішення та запитати: «Скільки ми можемо пролежати максимум у разі проблеми? Чи можемо ми полежати у найгіршій ситуації 3-5 хвилин?». І ухвалюєте рішення.
І тут є цікавий момент. У нас на конференції є кілька доповідей про Patroni. І, можливо, ви його використовуєте. Це autofailover для Postgres. GitLab та Data Egret про це розповідали.
І якщо у вас є autofailover, який настане за 30 секунд, то може бути ми і 10 хвилин можемо пролежати? Тому що ми на цей момент перейдемо на репліку, і все буде добре. Це питання спірне. Я не знаю чіткої відповіді. Я тільки відчуваю, що не тільки довкола відновлення після збою ця тема.
Якщо у нас довга відбудова після збою, то нам буде незручно в багатьох інших ситуаціях. Наприклад, у тих же експериментах, коли ми щось робимо та змушені іноді чекати по 10 хвилин.
Я б таки не ходив занадто далеко, навіть якщо у нас є autofailover. Як правило, такі значення, як 64, 100 гігабайт – це добрі значення. Іноді навіть варто менше вибрати. Загалом це тонка наука.
Вам, щоб робити ітерації, наприклад, max_wal_size =1, 8, потрібно повторювати масову операцію багато разів. Ви її зробили. І на тій же базі хочете її ще раз зробити, але ж ви вже все видалили. Що робити?
Я пізніше розповім про наше рішення, що ми робимо для того, щоб ітерувати в таких ситуаціях. І це найправильніший підхід.
Але в цьому випадку нам пощастило. Якщо, як написано «BEGIN, DELETE, ROLLBACK», ми можемо DELETE повторювати. Т. е. якщо ми його скасували самі, ми можемо його повторювати. І фізично у вас дані будуть там лежати. У вас навіть bloat ніякого не утвориться. Ви можете ітерувати на такі DELETE.
Такий DELETE c ROLLBACK є ідеальним для checkpoint tuning, навіть якщо у вас немає нормально розгорнутої database labs.
Ми зробили табличку із однією колонкою «i». Postgres має службові колонки. Вони невидимі, якщо їх спеціально не попросити. Це: ctid, xmid, xmax.
Ctid – це фізична адреса. Нульова сторінка, перший кортеж у сторінці.
Видно, що після ROOLBACK кортеж залишився на тому самому місці. Т. е. ми можемо ще раз спробувати, воно буде поводитися так само. Це головне.
Xmax – це час смерті кортежу. Він проставився, але Postgres знає, що ця транзакція була відкачена, тому що 0, що відкачена транзакція – не має значення. Це говорить про те, що DELETE можна ітерувати і перевіряти масові операції поведінки системи. Можна зробити database labs для бідних.
Це вже про програмістів. Про DBA теж вони завжди за це програмістів лають: «Навіщо ви робите такі довгі і важкі операції?». Це зовсім інша перпендикулярна тема. Раніше було адміністрування, а зараз буде розробка.
Очевидно, що ми не розбили частини. Це зрозуміло. Не можна такий DELETE для купи мільйонів рядків не розбивати на частини. Він робитиметься 20 хвилин, і все лежатиме. Але, на жаль, помилки роблять навіть досвідчені розробники, навіть у дуже великих компаніях.
Чому важливо розбивати?
-
Якщо ми бачимо, що диску важко, то сповільнимо. І якщо у нас розбито, то ми можемо додати паузи, можемо уповільнити throttling.
-
І ми інших не блокуватимемо надовго. У деяких випадках це неважливо, якщо ви видаляєте реальне сміття, з яким ніхто не працює, то, швидше за все, ви нікого не заблокуєте, окрім роботи autovacuum, тому що воно чекатиме, коли транзакція завершиться. Але якщо ви видаляєте те, що хтось може ще запитати, вони блокуватимуться, піде якась ланцюгова реакція. На сайтах та в мобільних додатках потрібно уникати довгих транзакцій.
Це цікаво. Я часто зустрічаю, що розробники запитують: Який розмір пачки вибрати?.
Зрозуміло, що більше розмір пачки, то менше transaction overhead, т. е. додаткові накладні витрати від транзакцій. Але при цьому час збільшується у цієї транзакції.
У мене є дуже просте правило: візьміть якнайбільше, але не перевищуйте виконання в секунду.
Чому мить? Пояснення дуже просте та зрозуміле всім, навіть не технічним людям. Ми бачимо реакцію. Візьмемо 50 мілісекунд. Якщо щось змінилося, то око наше зреагує. Якщо менше, то важче. Якщо щось відповідає через 100 мілісекунд, наприклад, ви натиснули мишкою, і воно вам через 100 мілісекунд відповіло, ви вже відчуваєте цю невелику затримку. Секунда вже сприймається як гальма.
Відповідно, якщо ми наші масові операції розіб'ємо на 10-ти секундні пачки, то у нас є ризик, що ми когось заблукаємо. І він працюватиме кілька секунд, і це люди вже помітять. Тому я волію більше секунди не робити. Але в той же час і зовсім дрібно не розбивати, бо transaction overhead буде помітним. База буде важчою, можуть виникнути ще інші різні проблеми.
Ми вибираємо розмір пачки. У кожному разі, можемо по-різному це робити. Можна автоматизувати. І переконуємось у ефективності роботи обробки однієї пачки. Т. е. ми робимо DELETE однієї пачки або UPDATE.
До речі, все, що я розповідаю, це не лише про DELETE. Як ви здогадалися, це будь-які масові операції над даними.
І ми дивимося, що план чудовий. Видно index scan, ще краще index only scan. І у нас невелика кількість даних задіяна. І дедалі менше секунди відпрацьовує. Супер.
І ми ще маємо переконатися, що деградації немає. Буває, що перші пачки швидко відпрацьовують, а потім все гірше-гірше та гірше. Процес такий, що треба багато тестувати. Для цього якраз і потрібна database labs.
І ми ще маємо підготувати щось, щоб це нам дозволило у production за цим правильно стежити. Наприклад, ми можемо у лозі писати час, можемо писати, де ми зараз і кого ми зараз видалили. І це нам дозволить потім розуміти, що відбувається. І якщо щось піде не так, швидко знайти цю проблему.
Якщо нам потрібно перевірити ефективність запитів і нам потрібно ітерувати багато разів, то є така штука, як товариш бот. Він уже готовий. Він використовується десятками розробників щодня. І він вміє величезну терабайтну базу дати за запитом за 30 секунд вашу власну копію. І ви можете щось там видалити та сказати RESET, і ще раз видалити. Ви можете експериментувати з ним таким чином. Я бачу за цією штукою майбутнє. І ми це вже робимо.
Які є стратегії розбиття? Я бачу три різні стратегії розбиття, які використовують розробники на пачці.
Перша дуже проста. У нас є айдішник числовий. І давайте ми розіб'ємо на різні інтервали, і працюватимемо з цим. Мінус зрозумілий. У першому відрізку у нас реального сміття може потрапити 100 рядків, у другому 5 рядків або взагалі не потрапити, або всі 1 000 рядків виявляться сміттям. Дуже нерівномірна робота, зате розбивати легко. Взяли максимальний ID та розбили. Це наївний підхід.
Друга стратегія – це збалансований підхід. Він використовується в Gitlab. Взяли та просканували таблицю. Виявили межі пачок ID так, щоб кожна пачка була рівно по 10 000 записів. І засунули в якусь чергу. І далі опрацьовуємо. Можна це робити у кілька потоків.
У першій стратегії теж, до речі, можна це робити кілька потоків. Це не складно.
Але є більш класний та оптимальний підхід. Це третя стратегія. І коли це можливо, краще її вибирати. Ми на основі спеціального індексу це робимо. У цьому випадку це буде, швидше за все, індекс за нашою умовою сміття та ID. Ми включимо ID, щоб це був index only scan, щоб ми в heap не ходили.
Як правило, index only scan – це швидше ніж index scan.
І ми швидко знаходимо наші айдішники, які хочемо видалити. BATCH_SIZE ми вибираємо заздалегідь. І ми їх не тільки отримуємо, ми їх отримуємо спеціальним чином і одразу ж лочимо. Але так лочимо, що якщо вони вже залочені, ми їх не лочимо, а їдемо далі і беремо наступні. Це for update skip locked. Ця суперфіч Postgres нам дозволяє в кілька потоків працювати, якщо ми хочемо. Можна в один потік. І тут є CTE – це один запит. І у нас на другому поверсі цього CTE відбувається реальне вилучення. returning *. Можна returning id, але краще *якщо у вас даних трохи в кожному рядку.
Навіщо нам це потрібне? Цим нам потрібно для того, щоб відзвітувати. Ми зараз видалили стільки рядків за фактом. І у нас кордони з ID або created_at ось такі. Можна min, max зробити. Ще щось можна зробити. Тут можна багато запхати. І це для моніторингу дуже зручно.
Щодо індексу є ще одне зауваження. Якщо ми вирішили, що нам саме для цього завдання потрібен спеціальний індекс, потрібно переконатися, що він не зіпсує heap only tuples updates. Т. е. у Postgres є така статистика. Це можна переглянути в pg_stat_user_tables для вашої таблиці. Ви можете подивитися – використовується hot updates чи ні.
Бувають ситуації, коли ваш новий індекс може просто обрубати їх. І у вас всі інші updates, які вже працюють, сповільняться. Не просто тому що індекс з'явився (кожен індекс трохи сповільнює updates, але трохи), а тут він ще зіпсує. І спеціальну оптимізацію зробити для цієї таблиці неможливо. Таке іноді буває. Це така тонкість, про яку мало хто пам'ятає. І ці граблі легко наступити. Іноді буває, що потрібно підхід з іншого боку знайти і все-таки обійтися без цього нового індексу, або зробити інший індекс, або ще якось, наприклад, можна використовувати другий метод.
Але це найоптимальніша стратегія, як розбивати на batches і одним запитом стріляти по пачках, видаляти потрохи і т.д.
Довгі транзакції
Blocked autovacuum
Blocking issue -
Помилка №5 велика. Микола з Okmeter розповідав про моніторинг Postgres. Ідеального моніторингу Postgres, на жаль, немає. Хтось ближчий, хтось далі. Okmeter досить близький до того, щоб бути ідеальним, але багато чого не вистачає і потрібно додавати. До цього потрібно бути готовим.
Наприклад, dead tuples краще моніторити. Якщо у вас багато мертвини в таблиці, то тут щось не так. Краще реагувати зараз, бо там може бути деградація, і ми можемо лягти. Таке буває.
Якщо велике IO, то відомо, що це недобре.
Довгі транзакції також. На OLTP довгі транзакції не слід пускати. І ось тут посилання на сніпет, яка дозволяє взяти цей сніпет і вже зробити деяке стеження за довгими транзакціями.
Чому довгі транзакції – це погано? Тому що всі локи відпустяться лише наприкінці. І ми лочемо всіх. Плюс ми блокуємо роботу autovacuum для всіх таблиць. Це взагалі не добре. Навіть якщо на репліці у вас hot standby увімкнено, це все одно погано. Загалом ніде краще не допускати довгих транзакцій.
Якщо у нас багато таблиць не вакуумуються, то потрібно мати alert. Тут можлива така ситуація. Ми можемо опосередковано вплинути на роботу autovacuum. Це сніпет від Avito, який я трошки покращив. І вийшов цікавий інструмент, щоб подивитися, що ми маємо з autovacuum. Наприклад, там чекають якісь таблиці та не дочекаються своєї черги. Також треба засунути в моніторинг і мати alert.
І блоки існують. Ліс дерева блокування. Я люблю у когось щось взяти та покращити. Тут Data Egret взяв класний рекурсивний CTE, який показує ліс дерев блокувань. Це чудова штука для діагностики. І на її основі також можна зробити моніторинг. Але це треба обережно робити. Потрібно самому собі statement_timeout зробити. І lock_timeout бажано.
Іноді всі ці помилки зустрічаються у сумі.
На мою думку, найголовніша помилка тут – це організаційна. Вона організаційна, бо техніка не тягне. Це номер 2 – перевіряли не там.
Ми перевіряли не там, тому що ми не мали клон production, на якому легко перевірити. У розробника взагалі може бути доступу до production.
І ми перевіряли не там. Якби перевіряли там, там ми самі б це побачили. Розробник і без DBA все це побачив, якщо він перевіряв це в хорошому оточенні, де даних стільки ж і ідентичне розташування. Він би всю цю деградацію побачив і йому соромно було б.
Ще про автовакуум. Після того, як ми зробили масивну зачистку кількох мільйонів рядків, ще потрібно REPACK зробити. Особливо для індексів це важливо. Їм буде погано після того, як ми всі там почистили.
І якщо ви хочете повернути щоденну роботу із зачистки, то я б запропонував це робити частіше, але дрібніше. Можна раз на хвилину або ще частіше потроху. І потрібно налагодити моніторинг двох речей: що помилок немає у цієї штучки і що вона не відстає. Той трюк, котрий я показував тільки дозволить це вирішити.
Те, що ми робимо, це Open Source. Це викладено на GitLab. І ми робимо так, щоби люди могли перевіряти навіть без DBA. Ми робимо database lab, тобто ми так називаємо базовий компонент, на якому зараз працює Joe. І ви можете взяти копію production. Зараз є реалізація Joe для slack, ви можете там сказати: «explain такий запит» і відразу отримати результат для вашої копії бази. Ви можете там навіть DELETE зробити і ніхто цього не помітить.
Допустимо, у вас 10 терабайт, ми робимо database lab теж 10 терабайт. І з одночасними 10 терабайтними базами можуть працювати одночасно 10 розробників. Кожен може робити те, що хоче. Може видаляти, дропати і т.д. Ось така фантастика. Про це ми завтра говоритимемо.
Це називається thin provisioning. Це тонкий провіжинг. Це така деяка фантастика, яка сильно прибирає затримки у розробці, у тестуванні і робить світ кращим у цьому плані. Т. е. саме дозволяє вам уникати проблем з масовими операціями.
Приклад: база даних 5 терабайт, отримання копії менше, ніж за 30 секунд. І від розміру це навіть не залежить, тобто не має значення, скільки терабайт.
Вже сьогодні ви можете зайти на та покопатися у наших інструментах. Ви можете зареєструватись, подивитися, що там є. Можете собі цього бота. Він безкоштовний. Пишіть.
Питання
Дуже часто в реальних ситуаціях виходить так, що дані, які повинні залишитися в таблиці, набагато менше, ніж потрібно видалити. Т. е. в такій ситуації часто легше здійснити такий підхід, коли простіше створити новий об'єкт, скопіювати туди тільки потрібні дані і затранкейти стару таблицю. Зрозуміло, що потрібен програмний підхід для цього моменту, доки у вас відбуватиметься перемикання. Як такий підхід?
Це дуже хороший підхід та дуже гарне завдання. Вона дуже схожа на те, що робить pg_repack, вона дуже схожа на те, що вам доводиться робити, коли ви айдишники зробили 4 байтними. Багато фреймворків це робили кілька років тому, і якраз таблички підросли, і їх потрібно конвертувати на 8 байт.
Це завдання досить важке. Ми це робили. І вам потрібно бути дуже обережними. Там локи тощо. Але це робиться. Т. е. стандартний підхід похід на pg_repack. Ви повідомляєте таку табличку. І перш ніж у неї почати заливати дані снапшотом, ви ще оголошує одну табличку, яка всі зміни відстежує. Там є хитрість, що деякі зміни ви можете навіть не відстежувати. Є тонкощі. І потім ви перемикаєтеся, накотивши зміни. Там буде недовга пауза, коли ми всіх залочимо, але загалом це робиться.
Якщо ви pg_repack на GitHub подивіться, то там, коли була задача конвертувати айдишник з int 4 на int 8, то була ідея сама використовувати pg_repack. Це теж можливо, але це трохи хакерський метод, але він також для цього підійде. Ви можете втрутитися в тригер, який використовує pg_repack і там сказати: "Нам ці дані не потрібні", тобто ми переливаємо тільки те, що нам потрібно. І потім він просто перейде і все.
При такому підході ми отримуємо другу копію таблиці, в якій дані вже індексовані і покладені дуже рівно з красивими індексами.
Bloat ні, це добрий підхід. Але я знаю, що є спроби розробити автоматизацію для цього, тобто зробити універсальне рішення. Я можу звести вас з цією автоматизацією. Вона написана на Python, гарна штука.
Я просто трохи зі світу MySQL, тому я прийшов послухати. І ми маємо такий підхід.
Але він лише якщо у нас 90 %. Якщо ми маємо 5 %, то не дуже добре його застосовувати.
Дякую за доповідь! Якщо немає ресурсів зробити повну копію prod, чи є якийсь алгоритм чи формула для того, щоб прорахувати навантаження чи розмір?
Гарне питання. Поки що нам виходить багатотерабайтні бази знайти. Нехай навіть там залізо не таке ж буде, наприклад, менше пам'яті, менше процесора і диски не зовсім такі ж, але все-таки ми робимо це. Якщо зовсім ніде, треба подумати. Давайте я завтра подумаю, ви приходили, ми поспілкуємося, це гарне питання.
Дякую за доповідь! Ви спочатку почали про те, що є крутий Postgres, у якого ось такі обмеження, але він розвивається. А це все милиця за великим рахунком. Чи не йдеться це все в протиріччя з розвитком самого Postgres, в якому якийсь DELETE deferent з'явиться або ще щось, що має підтримувати на низькому рівні те, що ми намагаємося тут обмазати якимись своїми дивними засобами?
Якщо ми в SQL сказали видалити або проапдейтіть багато записів в одній транзакції, як Postgres може це розподілити? Ми фізично обмежені в операціях. Ми все одно робитимемо це довго. І ми будемо лочити в цей час і т.д.
Із індексами ж зробили.
Я можу припустити, що той самий checkpoint tuning можна було автоматизувати. Колись це, можливо, буде. Але тоді питання не дуже розумію.
Питання в тому, що немає такого вектора розвитку, який йде ось там ось, а тут йде паралельно ваше? Тобто. там поки що про це не гадають?
Я розповів про принципи, які можна використати зараз. Є інший бот За допомогою цього можна зробити автоматизований checkpoint tuning. Чи це буде колись у Postgres? Не знаю, це поки що навіть не обговорюється. Ми поки що далекі від цього. Але є вчені, які виробляють нові системи. І вони нас пхають в автоматичні індекси. Є розробки. Наприклад, auto tuning можете подивитися. Він автоматично підбирає параметри. Але він вам checkpoint tuning поки що не зробить. Т. е. він підбере для performance, shell buffer і т.д.
А для checkpoint tuning можна зробити таку штуку: якщо у вас тисяча кластерів та різні залізниці, різні віртуальні машини у cloud, ви можете за допомогою нашого бота автоматизацію зробити. І буде підбиратися max_wal_size за вашими цільовими установками автоматично. Але поки що цього в ядрі навіть близько немає, на жаль.
Добридень! Ви говорили про шкоду тривалих транзакцій. Ви казали, що блокуються autovacuum у разі видалення. Чим ще нам це шкодить? Тому що ми більше говоримо про звільнення місця та можливість його використовувати. Що ми ще втрачаємо?
Autovacuum – це, можливо, не найбільша проблема тут. А те, що довга транзакція може закласти інші транзакції, ця можливість небезпечніша. Вона може зустрітись, а може й не зустрітися. Якщо вона зустрілася, то дуже погано можливо. І з autovacuum – це також проблема. Тут дві проблеми з довгими транзакціями в OLTP: локи та autovacuum. І якщо у вас hot standby feedback включений на репліці, то вам ще й блокування autovacuum прилетить на майстер, воно прилетить з репліки. Але принаймні там локів не буде. А тут будуть локи. Ми говоримо про зміни даних, тому локи – це важливий момент. І якщо це все довго-довго, то все більше транзакцій тягнеться. Вони можуть лочити інші. І з'являються дерева локів. Я наводив посилання на сніпет. І ця проблема швидше стає помітнішою, ніж проблема з autovacuum, яка може тільки накопичуватися.
Дякую за доповідь! Ви розпочали свою доповідь з того, що неправильно тестували. Продовжили свою ідею, що потрібно взяти обладнання однакове, з базою так само. Допустимо, ми дали розробнику базу. І він виконав запит. І в нього начебто все добре. Але ж він не перевіряє на live, а на live, наприклад, у нас навантаженні на 60-70 %. І навіть якщо ми використовуємо цей тюнінг, виходить не дуже
Мати в команді експерта і користуватися експертами DBA, які можуть прогноз зробити, що буде за реального фонового навантаження, — це важливо. Коли ми просто чисті зміни прогнали, ми бачимо картину. Але більш розвинений підхід, коли ми ще раз те ж саме зробили, але ще з симульованим з production навантаженням. Це дуже класно. До цього треба ще зрости. Це по-дорослому. Ми й чисто подивилися, що є та ще й подивилися – чи вистачає нам ресурсів. Це гарне питання.
Коли ми вже робимо garbage select і ми маємо, наприклад, deleted flag
Це те, що autovacuum робить автоматично в Postgres.
А він це робить?
Autovacuum це є garbage collector.
Спасибо!
Дякую за доповідь! Чи є варіант одразу проектувати базу даних з партикуванням таким, щоб все сміття відпаковувалося від основної таблиці кудись убік?
Звичайно є.
Чи можна тоді убезпечити себе, якщо ми заклали таблицю, яка не повинна використовуватись?
Звичайно є. Але це питання як про курку та яйце. Якщо ми всі знаємо, що буде в майбутньому, то, звичайно, ми зробимо все класно. Але бізнес змінюється, там з'являються нові шпальти, нові запити. А потім – опа, ми хочемо видалити це. Але ця ідеальна ситуація, у житті вона зустрічається, але не завжди. Але загалом це хороша ідея. Просто truncate та все.
Джерело: habr.com