3 Оптимізація БД і додатки

  1. 3.1 Підтримка бази в порядку
  2. 3.1.1 Команда VACUUM
  3. 3.1.2 Команда ANALYZE
  4. 3.1.3 pg_autovacuum
  5. 3.1.4 Команда REINDEX
  6. 3.2 Використання індексів
  7. 3.2.1 Команда EXPLAIN [ANALYZE]
  8. 3.2.2 Використання зібраної статистики
  9. 3.2.3 Можливості індексів в PostgreSQL
  10. 3.2.3.2 Часткові індекси (partial indexes)
  11. 3.2.3.3 Повнотекстовий пошук
  12. 3.4 Оптимізація конкретних запитів
  13. 3.4.1 SELECT max (...) / min (...) FROM <величезна таблиця>
  14. проблема
  15. Рішення
  16. 3.4.2 SELECT count (*) FROM <величезна таблиця>
  17. проблема
  18. Рішення
  19. 3.4.3 SELECT ... WHERE ... IN (SELECT ...)
  20. проблема
  21. Рішення
  22. Footnotes

Next: 4 Висновок Up: PostgreSQL: настройка продуктивності Previous: 2 Налаштування сервера
Subsections

Для швидкої роботи кожного запиту в вашій базі в основному зверніть увагу на таке:

  1. Відсутність в базі сміття, що заважає дістатися до актуальних даних. Можна сформулювати дві підзадачі:
    • Грамотне проектування бази. Висвітлення цього питання виходить далеко за рамки цієї статті.
    • Прибирання сміття, що виникає при роботі СУБД.
  2. Наявність швидких шляхів доступу до даних - індексів.
  3. Можливість використання оптимізатором цих швидких шляхів.
  4. Обхід відомих проблем.


3.1 Підтримка бази в порядку

У цьому розділі містяться дії, які повинні періодично виконуватися для кожної бази. Від розробника потрібно тільки налаштувати їх автоматичне виконання (за допомогою cron) і дослідним шляхом підібрати його оптимальну частоту.


3.1.1 Команда VACUUM

Використовується для «збірки сміття» в базі даних. Починаючи з версії 7.2, існує в двох варіантах:

  • VACUUM FULL (VACUUM до 7.2) намагається видалити всі старі версії записів і, відповідно, зменшити розмір файлу, що містить таблицю. Цей варіант команди повністю блокує оброблювану таблицю.
  • VACUUM (починаючи з 7.2) позначає місце, займане старими версіями записів, як вільне (див. Також пункт 2.3 ). Використання цього варіанту команди, як правило, не зменшує розмір файлу, що містить таблицю, але дозволяє не дати йому безконтрольно рости, зафіксувавши на деякому прийнятному рівні. При роботі VACUUM можливий паралельний доступ до оброблюваної таблиці.

При використанні в формі VACUUM [FULL] ANALYZE, після складання сміття буде оновлена ​​статистика по цій таблиці, яка використовується оптимізатором. В абсолютній більшості випадків має сенс використовувати саме цю форму.

Рекомендується досить часте - в [ 7 ] І [ 8 ], Наприклад, раз на кілька хвилин (!) - виконання VACUUM ANALYZE для часто оновлюваних баз (або окремих таблиць). У звичайних випадках досить щоденного 8 виконання цієї команди. При цьому зверніть увагу: якщо «пляшкове горлечко» вашого сервера знаходиться в районі дискової підсистеми, то виконання VACUUM паралельно зі звичайною роботою може вкрай негативно позначитися на продуктивності.

Команду VACUUM FULL варто запускати досить рідко, не частіше разу на тиждень. Її також можна буде запускати вручну для конкретної таблиці після видалення або оновлення великої частини записів в ній.


3.1.2 Команда ANALYZE

Служить для оновлення інформації про розподіл даних в таблиці. Ця інформація використовується оптимізатором для вибору найбільш швидкого плану виконання запиту.

Зазвичай команда використовується в зв'язці VACUUM ANALYZE. Якщо в базі є таблиці, дані в яких не змінюються і не видаляються, а лише додаються, то для таких таблиць можна використовувати окрему команду ANALYZE. Також варто використовувати цю команду для окремої таблиці після додавання в неї великої кількості записів.

3.1.3 pg_autovacuum

Починаючи з версії 7.4, в дистрибутиві PostgreSQL поставляється програма pg_autovacuum, яка відстежує зміни в таблицях і автоматично запускає команди VACUUM і / або ANALYZE для цих таблиць по досягненні певної межі.

Використання цієї програми дозволяє відмовитися від настройки періодичного виконання команд VACUUM і ANALYZE. Більш того, в разі використання pg_autovacuum ресурси не витрачаються даремно на обробку таблиць, які практично не піддавалися змінам.

Для роботи pg_autovacuum повинен бути включений складальник статистики (див. Пункт 2.4.2 ) І включений параметр stats_row_level.


3.1.4 Команда REINDEX

Команда REINDEX використовується для перебудови існуючих індексів. Використовувати її можна буде в разі

  • псування індексу;
  • постійного збільшення його розміру.

Другий випадок вимагає пояснень. Індекс, як і таблиця, містить блоки зі старими версіями записів. PostgreSQL не завжди може заново використовувати ці блоки 9 , І тому файл з індексом поступово збільшується в розмірах. Якщо дані в таблиці часто змінюються, то рости він може досить швидко.

Якщо ви помітили подібну поведінку якогось індексу, то варто налаштувати для нього періодичне виконання команди REINDEX. Врахуйте: команда REINDEX, як і VACUUM FULL, повністю блокує таблицю, тому виконувати її треба тоді, коли завантаження сервера мінімальна.


3.2 Використання індексів

Досвід показує, що найбільш значні проблеми з продуктивністю викликаються відсутністю потрібних індексів. Тому зіткнувшись з повільним запитом, в першу чергу перевірте, чи існують індекси, які він може використовувати. Якщо немає - побудуйте їх.

Надлишок індексів, втім, теж загрожує проблемами:

  • Команди, які змінюють дані в таблиці, повинні змінити також і індекси. Очевидно, чим більше індексів побудовано для таблиці, тим повільніше це буде відбуватися.
  • Оптимізатор перебирає можливі шляхи виконання запитів. Якщо побудовано багато непотрібних індексів, то цей перебір буде йти довше.

Єдине, що можна сказати з великим ступенем визначеності - поля, що є зовн ключами, і поля, за якими об'єднуються таблиці, індексувати треба обов'язково.

3.2.1 Команда EXPLAIN [ANALYZE]

Команда EXPLAIN [запит] показує, яким чином PostgreSQL збирається виконувати ваш запит. Команда EXPLAIN ANALYZE [запит] виконує запит 10 і показує як початковий план, так і реальний процес його виконання.

Читання виведення цих команд - мистецтво, яке приходить з досвідом. Для початку звертайте увагу на наступне:

Слід зазначити, що повний перегляд таблиці далеко не завжди повільніше перегляду за індексом. Якщо, наприклад, в таблиці-довіднику кілька сотень записів, що вміщується в одному-двох блоках на диску, то використання індексу призведе лише до того, що доведеться читати ще й пару зайвих блоків індексу. Якщо в запиті доведеться вибрати 80% записів з великої таблиці, то повний перегляд знову ж вийде швидше.

При тестуванні запитів з використанням EXPLAIN ANALYZE можна скористатися налаштуваннями, які забороняють оптимізатора використовувати певні плани виконання. наприклад,

SET enable_seqscan = false; заборонить використання повного перегляду таблиці, і ви зможете з'ясувати, чи правий був оптимізатор, відмовляючись від використання індексу. Ні в якому разі не слід прописувати подібні команди в postgresql.conf! Це може прискорити виконання декількох запитів, але сильно уповільнить всі інші!


3.2.2 Використання зібраної статистики

Результати роботи збирача статистики (див. Пункт 2.4.2 ) Доступні через спеціальні системні уявлення. Найцікавіші для наших цілей наступні:

pg_stat_user_tables

містить - для кожної користувальницької таблиці в поточній базі даних - загальна кількість повних переглядів і переглядів з використанням індексів, загальні кількості записів, які були повернуті в результаті обох типів перегляду, а також загальну кількість вставлених, змінених і віддалених записів. pg_stat_user_indexes містить - для кожного користувача індексу в поточній базі даних - загальна кількість переглядів, які використовували цей індекс, кількість прочитаних записів, кількість успішно прочитаних записів в таблиці (може бути менше попереднього значення, якщо в індексі є записи, що вказують на застарілі записи в таблиці). pg_statio_user_tables містить - для кожної користувальницької таблиці в поточній базі даних - загальна кількість блоків, прочитаних з таблиці, кількість блоків, які опинилися при цьому в буфері (див. пункт 2.1.1 ), А також аналогічну статистику для всіх індексів по таблиці і, можливо, по пов'язаної з нею таблицею TOAST. З цих уявлень можна дізнатися, зокрема

  • Для яких таблиць варто створити нові індекси (індикатором служить велика кількість повних переглядів і велика кількість прочитаних блоків).
  • Які індекси взагалі не використовуються в запитах. Їх має сенс видалити, якщо, звичайно, мова не йде про індекси, які забезпечують виконання обмежень PRIMARY KEY і UNIQUE.
  • Чи достатній обсяг буфера сервера.

Також можливий «дедуктивний» підхід, при якому спочатку створюється велика кількість індексів, а потім невикористовувані індекси видаляються.

3.2.3 Можливості індексів в PostgreSQL

3.2.3.1 Функціональні індекси

Ви можете побудувати індекс не тільки по полю / декільком полям таблиці, але і за висловом, залежному від полів. Нехай, наприклад, у вашій таблиці foo є поле foo_name, і вибірки часто робляться за умовою «перша буква foo_name = 'буква', в будь-якому регістрі». Ви можете створити індекс

CREATE INDEX foo_name_first_idx

ON foo ((lower (substr (foo_name, 1, 1))));

і запит виду SELECT * FROM foo

WHERE lower (substr (foo_name, 1, 1)) = 'и';

буде його використовувати.

Слід зазначити, що можливості завдання подібних індексів були значно розширені в версії 7.4, і наведений приклад може зажадати доопрацювання, щоб бути запущеним на більш старої версії.

3.2.3.2 Часткові індекси (partial indexes)

Під частковим індексом розуміється індекс з предикатом WHERE. Нехай, наприклад, у вас є в базі таблиця scheta з параметром uplocheno типу boolean. Записів, де uplocheno = false менше, ніж записів з uplocheno = true, а запити по ним виконуються значно частіше. Ви можете створити індекс

CREATE INDEX scheta_neuplocheno ON scheta (id)

WHERE NOT uplocheno;

який буде використовуватися запитом виду SELECT * FROM scheta WHERE NOT uplocheno AND ...; Гідність підходу в тому, що записи, які не відповідають умові WHERE, просто не потраплять в індекс.

3.2.3.3 Повнотекстовий пошук

Звичайні індекси не можуть бути використані в запитах, які шукають, наприклад, входження підрядка в рядок. Для цього потрібні спеціальні засоби повнотекстового пошуку.

Найбільш просунутим з наявних коштів є tsearch2 http://www.sai.msu.su/ megera / postgres / gist / tsearch / V2 / Він поставляється в дистрибутиві PostgreSQL версії 7.4 в каталозі contrib / tsearch2, варіант для версії 7.3 можна завантажити на зазначеному сайті.

За повним описом можливостей tsearch2 зверніться до продукції, що поставляється з ним документації.

Цей пункт очевидний для досвідчених користувачів PostrgeSQL і призначений для тих, хто використовує або переносить на PostgreSQL додатки, написані спочатку для більш примітивних СУБД.

Реалізація частини логіки на стороні сервера через збережені процедури, тригери, правила 11 часто дозволяє прискорити роботу програми. Дійсно, якщо кілька запитів об'єднані в процедуру, то не потрібно

Крім того, збережені процедури спрощують процес розробки та підтримки: зміни треба вносити тільки на стороні сервера, а не міняти запити у всіх додатках.


3.4 Оптимізація конкретних запитів

У цьому розділі описуються запити, для яких з різних причин не може бути примушений оптимізатор використовувати індекси, і які будуть завжди викликати повний перегляд таблиці. Таким чином, якщо вам потрібно використовувати ці запити в вимогливому до швидкодії додатку, то доведеться їх змінити.

3.4.1 SELECT max (...) / min (...) FROM <величезна таблиця>

Все агрегатні функції в PostgreSQL реалізовані однаково: спочатку вибираються всі записи, що задовольняють умові, а потім до отриманого набору записів застосовується агрегатна функція. У такого підходу є переваги - ви можете легко написати власну агрегатную функцію - але є і недолік, який полягає в тому, що для роботи функцій типу min () / max () весь набір записів абсолютно не потрібен.

Для їх роботи раціональніше було б скористатися індексом по полю, для якого шукається максимум (мінімум), але для цього доведеться зробити реалізацію цих агрегатних функцій відмінною від усіх інших.

проблема

запит виду

SELECT max (field) FROM foo; не використовуватиме существущую індекс по полю field, а буде робити повний перегляд таблиці. Якщо записів в таблиці багато, то це може зайняти чимало часу.

Рішення

запит виду

SELECT field FROM foo ORDER BY field DESC LIMIT 1; поверне те ж саме значення 12 , Але при цьому зможе використовувати індекс по field, якщо такий існує.

3.4.2 SELECT count (*) FROM <величезна таблиця>

До функції count () відноситься все вищесказане з приводу реалізації агрегатних функцій в PostgreSQL. Крім того, інформація про видимість записи для поточної транзакції (а конкурентним транзакціях може бути мабуть різну кількість записів в таблиці!) Не зберігається в індексі. Таким чином, навіть якщо використовувати для виконання запиту індекс первинного ключа таблиці, все одно буде потрібно читання записів власне з файлу таблиці.

проблема

запит виду

SELECT count (*) FROM foo; здійснює повний перегляд таблиці foo, що вельми довго для таблиць з великою кількістю записів.

Рішення

Простого вирішення проблеми, на жаль, немає. Можливі такі підходи:

  1. Якщо точне число записів не має значення, а важливий порядок 13 , То можна використовувати інформацію про кількість записів в таблиці, зібрану під час виконання команди ANALYZE: SELECT reltuples FROM pg_class WHERE relname = 'foo';
  2. Якщо подібні вибірки виконуються часто, а зміни в таблиці досить рідкісні, то можна завести допоміжну таблицю, що зберігає число записів в основний. На основну ж таблицю повісити тригер, який буде зменшувати це число в разі видалення запису і збільшувати в разі вставки. Таким чином, для отримання кількості записів буде потрібно лише вибрати одну запис з допоміжної таблиці.
  3. Варіант попереднього підходу, але дані в допоміжній таблиці оновлюються через певні проміжки часу (cron).

3.4.3 SELECT ... WHERE ... IN (SELECT ...)

Відразу відзначимо, що у версії 7.4 в обробку підзапитів з IN / NOT IN були внесені зміни, і тепер вони працюють (як мінімум) не повільні, ніж підзапити з EXISTS / NOT EXISTS. Якщо ви з якихось причин не можете оновити версію сервера до 7.4, то читайте далі.

проблема

При використанні підзапиту виду

SELECT ...

FROM foo

WHERE foo_field IN (

SELECT bar_field

FROM bar

...

);

оптимізатор не може використовувати індекс по таблиці bar, і тому запит відпрацьовує вкрай повільно.

Рішення

Перепишіть підзапит з використанням конструкції EXISTS:

SELECT ...

FROM foo

WHERE EXISTS (

SELECT bar_field

FROM bar

WHERE bar.bar_field = foo.foo_field

...

);

Аналогічно можна переписати підзапит з NOT IN, використовуючи конструкцію NOT EXISTS.

Footnotes

... 8точніше щоночі, тому щоночами навантаження на базу менше... 9у версії 7.4 були зроблені суттєві поліпшення... 10і тому EXPLAIN ANALYZE DELETE ... - не надто гарна ідея... 11RULE - реалізоване в PostgreSQL розширення стандарту SQL, що дозволяє, зокрема, створювати оновлювані подання... 12насправді майже те ж саме: відмінність буде в разі, якщо в таблиці немає записів... 13«На нашому форумі більше 10000 зареєстрованих користувачів, які залишили більше 50000 повідомлень!»
Next: 4 ВисновокUp: PostgreSQL: настройка продуктивностіPrevious: 2 Налаштування сервера2003-12-10

Спонсори:

Хостинг:



Для EXPLAIN ANALYZE: чи немає великих відмінностей в передбачувану кількість записів і реально обраному?

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

Или позвоните нам по телефонам: (048) 823-25-64

Организация (обязательно) *

Адрес доставки

Объем

Как с вами связаться:

Имя

Телефон (обязательно) *

Мобильный телефон

Ваш E-Mail

Дополнительная информация: