Пропоную ознайомитися з розшифровкою доповіді початку 2016 року Володимира Ситникова "PostgreSQL та JDBC вичавлюємо всі соки"
Добридень! Мене звуть Володимир Ситніков. Я працюю 10 років у компанії NetCracker. І переважно я займаюся продуктивністю. Все, що пов'язане з Java, все, що пов'язане з SQL - те, що я люблю.
І сьогодні я розповім про те, з чим ми зіткнулися в компанії, коли почали використовувати PostgreSQL як сервер баз даних. І ми здебільшого працюємо з Java. Але те, що я сьогодні розповім, пов'язане не лише з Java. Як показала практика, це виникає і в інших мовах.
Ми будемо говорити:
- про вибірку даних.
- Для збереження даних.
- А також про продуктивність.
- І про підводні граблі, які там закопані.
Почнемо з простого питання. Ми вибираємо один рядок із таблиці за первинним ключем.
База знаходиться на тому ж хості. І все це господарство займає 20 мілісекунд.
Ось ці 20 мілісекунд – дуже багато. Якщо у вас таких 100 запитів, то ви витрачаєте час на секунду на те, щоб ці запити прокрутити, тобто в пусту витрачаємо час.
Ми це не любимо робити і дивимось, що нам пропонує база для цього. База пропонує нам два варіанти виконання запитів.
Перший варіант – це найпростіший запит. Чим він добрий? Тим, що ми його беремо та посилаємо, і нічого більше.
У бази є ще й розширений запит, який хитріший, але функціональніший. Можна окремо надсилати запит на парсинг, виконання, зв'язування змінних тощо.
Super extended query – це те, що ми не покриватимемо в поточній доповіді. Ми, можливо, щось хочемо від бази даних і є такий список хотівок, який у якомусь вигляді сформований, тобто це те, що ми хочемо, але неможливо зараз і найближчого року. Тому просто записали і ходитимемо, трястимемо основних людей.
А те, що ми можемо зробити, так це simple query і extended query.
У чому особливість кожного підходу?
Простий запит добре використовуватиме одноразове виконання. Раз виконали та забули. І проблема в тому, що він не підтримує бінарний формат даних, тобто для якихось високопродуктивних систем він не підходить.
Extended query – дозволяє заощаджувати час на парсингу. Це те, що ми зробили та почали використовувати. Нам це дуже допомогло. Там є не лише економія на парсингу. Є економія передачі даних. Передавати дані у бінарному форматі набагато ефективніше.
Перейдемо до практики. Ось так виглядає типовий додаток. Це може бути Java і т.д.
Ми створили стан. Виконали команду. Створили close. Де тут помилка? В чому проблема? Немає проблем. Так у всіх книгах написано. Так треба писати. Якщо ви бажаєте максимальної продуктивності, пишіть так.
Але практика засвідчила, що це не працює. Чому? Тому що ми маємо метод «close». І коли ми так робимо, то з погляду бази даних виходить – це як робота курця з базою даних. Ми сказали "PARSE EXECUTE DEALLOCATE".
Навіщо ці зайві створення та вивантаження statements? Вони нікому не потрібні. Але зазвичай у PreparedStatement так і виходить, коли ми їх закриваємо, вони закривають все на базі даних. Це не те, що ми хочемо.
Ми хочемо, як здорові люди працювати з базою. Одного разу взяли та підготували наш statement, потім його виконуємо багато разів. Насправді багато разів – це один раз за все життя програми запарсили. І на різних REST використовуємо той самий statement id. Оце наша мета.
Як нам цього досягти?
Дуже просто – не треба закривати statements. Пишемо так: «prepare» «execute».
Якщо ми таке запустимо, то зрозуміло, що десь у нас щось переповниться. Якщо не зрозуміло, можна поміряти. Візьмемо та напишемо бенчмарк, у якому такий простий метод. Створюємо стан. Запускаємо на якійсь версії драйвера і отримуємо, що він досить швидко валиться зі втратою всієї пам'яті, яка збула.
Зрозуміло, такі помилки легко виправляються. Я не про них говоритиму. Але я скажу, що у новій версії працює набагато швидше. Метод безглуздий, проте.
Як правильно працювати? Що нам для цього треба робити?
Насправді програми завжди закривають стани. У всіх книжках пишуть, щоб зачиняли, інакше пам'ять втече.
І PostgreSQL не вміє кешувати запити. Треба щоб кожна сесія сама для себе створювала цей кеш.
І час витрачати на парсинг ми також не хочемо.
І як завжди, у нас є два варіанти.
Перший варіант - ми беремо і кажемо, що давайте все загорнемо в PgSQL. Там є кеш. Він усе кешує. Вийде чудово. Ми таке подивилися. У нас є 100500 запитів. Не працює. Ми не згодні – ручками на процедури перетворювати запити. Ні ні.
У нас є другий варіант - взяти і самим запиляти. Відкриваємо вихідники, починаємо пиляти. Пилим-пиляємо. Виявилося, що це не так складно зробити.
З'явилося це у серпні 2015 року. Нині вже більш сучасна версія. І все чудово. Працює настільки добре, що ми нічого не змінюємо у додатку. І ми навіть перестали думати у бік PgSQL, тобто нам цього цілком вистачило, щоби всі накладні витрати знизити практично до нуля.
Відповідно, Server-prepared statements активується на 5-му виконанні для того, щоб не витрачати пам'ять у базі даних на кожен одноразовий запит.
Можна спитати – де цифри? Що ви отримуєте? І тут я цифри не дам, бо кожен запит має свої.
У нас запити були такі, що ми на OLTP-запитах витрачали десь 20 мілісекунд на парсинг. Там було 0,5 мілісекунд на виконання, 20 мілісекунд на парсинг. Запит – 10 КіБ тексту, 170 рядків плану. Це запит OLTP. Він запитує 1, 5, 10 рядків, іноді більше.
Але нам зовсім не хотілося витрачати 20 мілісекунд. Ми в 0 звели. Все здорово.
Що ви можете звідси винести? Якщо у вас Java, ви берете сучасну версію драйвера і радієте.
Якщо у вас якась інша мова, то ви подумайте – можливо вам це теж треба? Тому що з точки зору кінцевої мови, наприклад, якщо PL 8 або у вас є LibPQ, то вам не очевидно, що ви витрачаєте час не на виконання, на парсинг і це варто перевірити. Як? Все безкоштовно.
За винятком того, що є помилки, якісь особливості. І про них якраз зараз говоритимемо. Більшість буде про промислову археологію, про те, що ми знайшли, на що натрапили.
Якщо запит генерується динамічно. Таке буває. Хтось рядки склеює, виходить SQL-запит.
Чим він поганий? Він поганий тим, що щоразу у нас у результаті виходить різний рядок.
І у цього різного рядка потрібно наново вважати hashCode. Це дійсно CPU завдання – знайти довгий текст запиту навіть у наявній hash'е не так просто. Тому висновок простий – не генеруйте запити. Зберігайте їх у якійсь одній змінній. І радійте.
Наступна проблема. Типи даних є важливими. Бувають ORM, які кажуть, що не важливо який NULL, хай буде якийсь. Якщо Int, то ми говоримо setInt. А якщо NULL, то нехай буде VARCHAR завжди. І яка різниця врешті-решт який там NULL? База даних сама все зрозуміє. І така картинка не працює.
На практиці базі даних зовсім не байдуже. Якщо ви вперше сказали, що це у вас число, а вдруге сказали, що це VARCHAR, неможливо перевикористовувати Server-prepared statements. І в такому випадку доводиться заново перестворювати наш стан.
Якщо ви виконуєте один і той же запит, слідкуйте за тим, щоб типи даних у колонці у вас не плуталися. Потрібно стежити за NULL. Це часта помилка, яка у нас була після того, як ми почали використовувати PreparedStatements
Добре, увімкнули. Взяли, можливо, драйвер. І продуктивність упала. Все стало погано.
Як це буває? Баг це чи фіча? На жаль, не вдалося зрозуміти – баг це чи фіча. Але є простий сценарій відтворення цієї проблеми. Вона несподівано підстерегла нас. І полягає у вибірці буквально з однієї таблиці. У нас, звісно, таких запитів було більше. Вони, як правило, дві-три таблиці включали, але є такий сценарій відтворення. Берете на вашій базі будь-якої версії та відтворюєте.
Сенс у тому, що у нас дві колонки, кожна з яких проіндексована. У одній колонці за значенням NULL лежить мільйон рядків. А у другій колонці лежить лише 20 рядків. Коли ми виконуємо без зв'язкових змінних, все добре працює.
Якщо ми почнемо виконувати зі зв'язаними змінними, тобто ми виконуємо знак "?" або «$1» для нашого запиту, що ми в результаті отримуємо?
Перше виконання – як належить. Друге – трохи швидше. Щось прокешувалося. Третє-четверте-п'яте. Потім хлоп – і якось так. І найгірше, що це відбувається на шостому виконанні. Хто знав, що треба робити саме шість виконань для того, щоб зрозуміти, який реальний план виконання?
Хто винен? Що сталося? База даних містить оптимізацію. І вона ніби оптимізована під generic випадок. І, відповідно, з якогось разу вона переходить на generic план, який, на жаль, може виявитися іншим. Він може виявитися таким самим, а може й іншим. І там є якесь граничне значення, яке призводить до такої поведінки.
Що із цим можна робити? Тут, звичайно, складніше щось припускати. Є просте рішення, яке ми використовуємо. Це +0, OFFSET 0. Напевно, ви знаєте такі рішення. Просто беремо і в запит додаємо +0 і все добре. Покажу пізніше.
І є ще варіант – уважніші плани дивитися. Розробник повинен не тільки запит написати, а й 6 разів сказати "explain analyze". Якщо 5, то не підійде.
І є ще третій варіант – це написати в pgsql-hackers листа. Я написав, щоправда, поки що не зрозуміло – баг це чи фіча.
Поки ми думаємо – баг це чи фіча, давайте полагодимо. Візьмемо наш запит та додамо «+0». Все добре. Два символи і навіть не треба думати, як там і чого там. Дуже просто. Ми просто базі даних заборонили використовувати індекс цієї колонки. Немає у нас індексу по колонці "+0" і все, база даних не використовує індекс, все добре.
Ось це правило 6-ти explain'ів. Зараз у поточних версіях треба робити 6 разів, якщо у вас пов'язані змінні. Якщо у вас немає пов'язаних змінних, то ми так робимо. І в нас, зрештою, саме цей запит падає. Справа не хитра.
Здавалося б, скільки можна? Тут баг, там баг. Реально баг скрізь.
Давайте ще побачимо. Наприклад, ми маємо дві схеми. Схема А з таблицею Ы і схема Б з таблицею Ы. Запит - вибрати дані з таблиці. Що в нас буде при цьому? У нас буде помилка. У нас буде все вище перераховане. Правило таке – баг скрізь, у нас буде все перераховане вище.
Тепер питання: «Чому?». Здавалося б, є документація, що якщо ми маємо схему, тобто змінну «search_path», яка говорить про те, де потрібно шукати таблицю. Здавалося б, змінна є.
В чому проблема? Проблема в тому, що server-prepared statements не підозрюють, що search_path може хтось змінювати. Ось це значення залишається ніби константним для бази даних. І якісь частини можуть не підхопити нові значення.
Звичайно, це залежить від версії, де ви тестуєте. Залежить від того, як серйозно у вас таблиці різняться. І версія 9.1 просто виконає старі запити. Нові версії можуть виявити каверзу і сказати, що у вас помилка.
Як це лікувати? Є простий рецепт – не робіть так. Не потрібно змінювати search_path у роботі програми. Якщо ви змінюєте, краще створити нове з'єднання.
Можна поговорити, тобто відкрити, поговорити, дописати. Можливо, і переконаємо розробників бази даних, що у випадку, коли хтось змінює значення, база даних має про це клієнту говорити: «Дивіться, у вас тут значення поновилося. Можливо, вам треба statements скинути, перестворити?». Сьогодні база даних потай себе веде і не повідомляє ніяк про те, що десь усередині statements змінилися.
І я знову підкреслю – це те, що не є типовим для Java. Ми те саме побачимо в PL/ pgSQL один до одного. Але там відтвориться.
Спробуємо ще вибрати дані. Вибираємо-вибираємо. Ми маємо таблицю в мільйон рядків. Кожен рядок по кілобайту. Приблизно гігабайт даних. І у нас є робоча пам'ять у Java-машині 128 мегабайт.
Ми, як рекомендовано у всіх книгах, користуємося потоковою обробкою. Т. е. ми відкриваємо результат Set і читаємо звідти дані потроху. Чи це спрацює? Чи не впаде у пам'ять? Буде потроху читати? Давайте повіримо в базу, у Postgres повіримо. Не віримо. Впадемо OutOFMemory? У кого падав OutOfMemory? А хто зумів полагодити після цього? Хтось зумів відремонтувати.
Якщо у вас мільйон рядків, то не можна просто вибирати. Потрібно обов'язково OFFSET/LIMIT. Хто такий варіант? І хто за варіант, що треба autoCommit'ом грати?
Тут, як завжди, найнесподіваніший варіант виявляється правильним. І якщо ви раптом вимкнете autoCommit, воно допоможе. Чому так? Науці про це невідомо.
Але за замовчуванням усі клієнти, які з'єднуються з базою даних Postgres, вибирають дані цілком. PgJDBC у цьому плані не виняток, вибирає всі рядки.
Є варіація на тему FetchSize, тобто можна на рівні окремого стану сказати, що тут, будь ласка, вибирай дані по 10, 50. Але це не працює доти, доки ви не вимкнете autoCommit. Вимкнули autoCommit – починає працювати.
Але ходити за кодом і скрізь ставити setFetchSize – це незручно. Тому ми зробили таке налаштування, яке для всього з'єднання буде говорити значення за промовчанням.
Ось ми це сказали. Налаштували параметр. І що в нас вийшло? Якщо ми вибираємо по малому, якщо, наприклад, по 10 рядків вибираємо, то у нас дуже великі накладні витрати. Тому треба це значення ставити близько сотні.
В ідеалі, звичайно, в байтах ще навчитися обмежувати, але рецепт такий: ставимо defaultRowFetchSize більше ста і радіємо.
Давайте перейдемо до вставки даних. Вставка – простіша, там є різні варіанти. Наприклад, INSERT, VALUES. Це хороший варіант. Можна говорити "INSERT SELECT". На практиці це те саме. Жодної відмінності немає за продуктивністю.
Книги кажуть, що треба виконувати Batch statement, книги кажуть, що можна виконувати складніші команди з кількома дужками. І у Postgres є чудова функція – можна COPY робити, тобто робити це швидше.
Якщо поміряти, можна знову кілька цікавих відкриттів зробити. Як ми хочемо, щоби це працювало? Хочемо не парсити та зайвих команд не виконувати.
На практиці TCP нам так не дає. Якщо клієнт зайнятий надсиланням запиту, то база даних у спробах надіслати нам відповіді, запити не читає. У результаті клієнт чекає на базу даних, поки вона прочитає запит, а база даних чекає на клієнта, поки він прочитає відповідь.
І тому клієнт змушений періодично відправляти пакет синхронізації. Зайві мережеві взаємодії, зайва втрата часу.
І що більше ми їх додаємо, то гірше стає. Драйвер дуже песимістичний і додає їх досить часто, приблизно раз на 200 рядків, залежно від розміру рядків і т.д.
Буває, що рядок виправиш одну всього і в 10 разів все прискориться. Таке буває. Чому? Як завжди, константа десь така вже була використана. І значення "128" означало - не використовувати batching.
Добре, що це не потрапило до офіційної версії. Виявили до того, як почали випускати реліз. Усі значення, які я називаю, ґрунтуються на сучасних версіях.
Давайте поміряємо. Ми міряємо InsertBatch простий. Ми міряємо InsertBatch багаторазовий, тобто те ж саме, але дуже багато. Хитрий перебіг. Не всі так вміють, але це такий простий хід набагато простіше, ніж COPY.
Можна робити COPY.
І це можна робити на структурах. Оголосити User default type, передавати масив та INSERT безпосередньо до таблиці.
Якщо ви відкриєте посилання: pgjdbc/ubenchmsrk/InsertBatch.java, цей код є на GitHub. Можна подивитись конкретно, які запити там генеруються. Не має значення.
Ми запустили. І перше, що ми зрозуміли, що не використовувати batch це просто не можна. Усі варіанти batching дорівнюють нулю, т. е. час виконання практично дорівнює нулю проти одноразовим виконанням.
Ми вставляємо дані. Дуже там проста таблиця. Три колонки. І що ми тут бачимо? Ми бачимо, що всі ці три варіанти приблизно можна порівняти. І COPY, звичайно, краще.
Це коли ми шматочками вставляємо. Коли ми говорили, що одне значення VALUES, два значення VALUES, три значення VALUES або ми їх там 10 через кому вказали. Це саме зараз по горизонталі. 1, 2, 4, 128. Видно, що Batch Insert, який синеньким намальований, йому від цього дуже полегшує. Т. е. коли ви вставляєте по одному або навіть коли ви вставляєте по чотири, то стає вдвічі краще, просто від того, що ми в VALUES трохи більше запхали. Менше операцій EXECUTE.
Використовувати COPY на невеликих обсягах – це дуже неперспективно. Я на перших двох навіть не намалював. Вони в небеса йдуть, тобто ці зелені цифри для COPY.
COPY треба використовувати, коли у вас обсяг даних хоча б більше ста рядків. Накладні витрати на відкриття цього з'єднання є великими. І, чесно скажу, у цю бік не копав. Batch я оптимізував, COPY – ні.
Що ми робимо далі? Поміряли. Розуміємо, що треба використовувати чи структури, чи хитромудрий bacth, що поєднує кілька значень.
Що потрібно винести із сьогоднішньої доповіді?
- PreparedStatement – це наше все. Це дуже багато дає для продуктивності. Воно дає велику бочку дьогтю.
- І треба робити EXPLAIN ANALYZE 6 разів.
- І треба розбавляти OFFSET 0, і трюками типу +0 для того, щоб правити відсоток, що залишився там, від наших проблемних запитів.
Джерело: habr.com