Работа с PostgreSQL: настройка и
масштабирование
А. Ю. Васильев aka leopard
Creative Commons Attribution-Noncommercial 4.0 International
2017
Оглавление
Оглавление 1
1 Введение 2
1.1 Что такое PostgreSQL? . . . . . . . . . . . . . . . . . . . . . 2
2 Настройка производительности 4
2.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.2 Настройка сервера . . . . . . . . . . . . . . . . . . . . . . . . 7
2.3 Диски и файловые системы . . . . . . . . . . . . . . . . . . . 17
2.4 Утилиты для тюнинга PostgreSQL . . . . . . . . . . . . . . . 18
2.5 Оптимизация БД и приложения . . . . . . . . . . . . . . . . 22
2.6 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
3 Индексы 34
3.1 Типы индексов . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.2 Возможности индексов . . . . . . . . . . . . . . . . . . . . . 42
4 Партиционирование 44
4.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
4.2 Теория . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
4.3 Практика использования . . . . . . . . . . . . . . . . . . . . 46
4.4 Pg_partman . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
4.5 Pgslice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
4.6 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
5 Репликация 63
5.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
5.2 Потоковая репликация (Streaming Replication) . . . . . . . . 65
5.3 PostgreSQL Bi-Directional Replication (BDR) . . . . . . . . . 78
5.4 Pglogical . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
5.5 Slony-I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
1
Оглавление
5.6 Londiste . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
5.7 Bucardo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
5.8 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
6 Шардинг 117
6.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
6.2 PL/Proxy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
6.3 Postgres-X2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
6.4 Postgres-XL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
6.5 Citus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
6.6 Greenplum Database . . . . . . . . . . . . . . . . . . . . . . . 143
6.7 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
7 PgPool-II 158
7.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
7.2 Установка и настройка . . . . . . . . . . . . . . . . . . . . . 159
7.3 Настройка репликации . . . . . . . . . . . . . . . . . . . . . 161
7.4 Параллельное выполнение запросов . . . . . . . . . . . . . . 163
7.5 Master-slave режим . . . . . . . . . . . . . . . . . . . . . . . . 169
7.6 Онлайн восстановление . . . . . . . . . . . . . . . . . . . . . 170
7.7 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
8 Мультиплексоры соединений 174
8.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
8.2 PgBouncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
8.3 PgPool-II vs PgBouncer . . . . . . . . . . . . . . . . . . . . . 176
9 Кэширование в PostgreSQL 177
9.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
9.2 Pgmemcache . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
9.3 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
10 Расширения 185
10.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
10.2 PostGIS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
10.3 pgSphere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
10.4 HStore . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
10.5 PLV8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
10.6 Pg_repack . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
10.7 Pg_prewarm . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
10.8 Smlar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
10.9 Multicorn . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
10.10 Pgaudit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
10.11 Ltree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
10.12 PostPic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
2
Оглавление
10.13 Fuzzystrmatch . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
10.14 Pg_trgm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
10.15 Cstore_fdw . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
10.16 Postgresql-hll . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
10.17 Tsearch2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
10.18 PL/Proxy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
10.19 Texcaller . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
10.20 Pgmemcache . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
10.21 Prefix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
10.22 Dblink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
10.23 Postgres_fdw . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
10.24 Pg_cron . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
10.25 PGStrom . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
10.26 ZomboDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
10.27 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
11 Бэкап и восстановление PostgreSQL 259
11.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
11.2 SQL бэкап . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
11.3 Бэкап уровня файловой системы . . . . . . . . . . . . . . . . 262
11.4 Непрерывное резервное копирование . . . . . . . . . . . . . 263
11.5 Утилиты для непрерывного резервного копирования . . . . 265
11.6 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
12 Стратегии масштабирования для PostgreSQL 281
12.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
12.2 Проблема чтения данных . . . . . . . . . . . . . . . . . . . . 282
12.3 Проблема записи данных . . . . . . . . . . . . . . . . . . . . 283
12.4 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283
13 Утилиты для PostgreSQL 284
13.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
13.2 Pgcli . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
13.3 Pgloader . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
13.4 Postgres.app . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
13.5 pgAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
13.6 PostgREST . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
13.7 Ngx_postgres . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
13.8 Заключение . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
14 Полезные мелочи 287
14.1 Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
14.2 Мелочи . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Литература 298
3
1
Введение
Послушайте — и Вы забудете,
посмотрите — и Вы
запомните, сделайте — и Вы
поймете
Конфуций
Данная книга не дает ответы на все вопросы по работе с PostgreSQL.
Главное её задание — показать возможности PostgreSQL, методики на-
стройки и масштабируемости этой СУБД. В любом случае, выбор метода
решения поставленной задачи остается за разработчиком или администра-
тором СУБД.
1.1 Что такое PostgreSQL?
PostgreSQL (произносится «Пост-Грес-Кью-Эль») — свободная
объектно-реляционная система управления базами данных (СУБД).
PostgreSQL ведёт свою «родословную» от некоммерческой СУБД
Postgres, разработанной, как и многие open-source проекты, в Калифор-
нийском университете в Беркли. К разработке Postgres, начавшейся в 1986
году, имел непосредственное отношение Майкл Стоунбрейкер, руководи-
тель более раннего проекта Ingres, на тот момент уже приобретённого ком-
панией Computer Associates. Само название «Postgres» расшифровывалось
как «Post Ingres», соответственно, при создании Postgres были применены
многие уже ранее сделанные наработки.
Стоунбрейкер и его студенты разрабатывали новую СУБД в течение
восьми лет с 1986 по 1994 год. За этот период в синтаксис были введены
процедуры, правила, пользовательские типы и многие другие компоненты.
Работа не прошла даром — в 1995 году разработка снова разделилась: Сто-
унбрейкер использовал полученный опыт в создании коммерческой СУБД
4
1.1. Что такое PostgreSQL?
Illustra, продвигаемой его собственной одноимённой компанией (приобре-
тённой впоследствии компанией Informix), а его студенты разработали но-
вую версию Postgres — Postgres95, в которой язык запросов POSTQUEL —
наследие Ingres — был заменен на SQL.
В этот момент разработка Postgres95 была выведена за пределы уни-
верситета и передана команде энтузиастов. С этого момента СУБД полу-
чила имя, под которым она известна и развивается в текущий момент —
PostgreSQL.
На данный момент, в PostgreSQL имеются следующие ограничения:
Максимальный размер базы данных Нет ограничений
Максимальный размер таблицы 32 Тбайт
Максимальный размер записи 1,6 Тбайт
Максимальный размер поля 1 Гбайт
Максимум записей в таблице Нет ограничений
Максимум полей в записи 250—1600, в зависимости
от типов полей
Максимум индексов в таблице Нет ограничений
Согласно результатам автоматизированного исследования различного
ПО на предмет ошибок, в исходном коде PostgreSQL было найдено 20
проблемных мест на 775000 строк исходного кода (в среднем, одна ошибка
на 39000 строк кода). Для сравнения: MySQL — 97 проблем, одна ошибка
на 4000 строк кода; FreeBSD (целиком) — 306 проблем, одна ошибка на
4000 строк кода; Linux (только ядро) — 950 проблем, одна ошибка на 10
000 строк кода.
5
2
Настройка производительности
Теперь я знаю тысячу
способов, как не нужно
делать лампу накаливания
Томас Алва Эдисон
2.1 Введение
Скорость работы, вообще говоря, не является основной причиной ис-
пользования реляционных СУБД. Более того, первые реляционные базы
работали медленнее своих предшественников. Выбор этой технологии был
вызван скорее:
∙ возможностью возложить поддержку целостности данных на СУБД;
∙ независимостью логической структуры данных от физической;
Эти особенности позволяют сильно упростить написание приложений,
но требуют для своей реализации дополнительных ресурсов.
Таким образом, прежде чем искать ответ на вопрос «как заставить
РСУБД работать быстрее в моей задаче?», следует ответить на вопрос
«нет ли более подходящего средства для решения моей задачи, чем
РСУБД?» Иногда использование другого средства потребует меньше уси-
лий, чем настройка производительности.
Данная глава посвящена возможностям повышения производитель-
ности PostgreSQL. Глава не претендует на исчерпывающее изложение
вопроса, наиболее полным и точным руководством по использованию
PostgreSQL является, конечно, официальная документация и официаль-
ный FAQ. Также существует англоязычный список рассылки postgresql-
performance, посвящённый именно этим вопросам. Глава состоит из двух
разделов, первый из которых ориентирован скорее на администратора,
6
2.1. Введение
второй — на разработчика приложений. Рекомендуется прочесть оба раз-
дела: отнесение многих вопросов к какому-то одному из них весьма услов-
но.
Не используйте настройки по умолчанию
По умолчанию PostgreSQL сконфигурирован таким образом, чтобы он
мог быть запущен практически на любом компьютере и не слишком ме-
шал при этом работе других приложений. Это особенно касается исполь-
зуемой памяти. Настройки по умолчанию подходят только для следующе-
го использования: с ними вы сможете проверить, работает ли установка
PostgreSQL, создать тестовую базу уровня записной книжки и потрени-
роваться писать к ней запросы. Если вы собираетесь разрабатывать (а
тем более запускать в работу) реальные приложения, то настройки при-
дётся радикально изменить. В дистрибутиве PostgreSQL, к сожалению,
не поставляются файлы с «рекомендуемыми» настройками. Вообще го-
воря, такие файлы создать весьма сложно, т.к. оптимальные настройки
конкретной установки PostgreSQL будут определяться:
∙ конфигурацией компьютера;
∙ объёмом и типом данных, хранящихся в базе;
∙ отношением числа запросов на чтение и на запись;
∙ тем, запущены ли другие требовательные к ресурсам процессы (на-
пример, веб-сервер);
Используйте актуальную версию сервера
Если у вас стоит устаревшая версия PostgreSQL, то наибольшего уско-
рения работы вы сможете добиться, обновив её до текущей. Укажем лишь
наиболее значительные из связанных с производительностью изменений.
∙ В версии 7.4 была ускорена работа многих сложных запросов (вклю-
чая печально известные подзапросы IN/NOT IN);
∙ В версии 8.0 были внедрены метки восстановления, улучшение
управления буфером, CHECKPOINT и VACUUM улучшены;
∙ В версии 8.1 был улучшен одновременный доступ к разделяемой па-
мяти, автоматическое использование индексов для MIN() и MAX(),
pg_autovacuum внедрен в сервер (автоматизирован), повышение
производительности для секционированных таблиц;
∙ В версии 8.2 была улучшена скорость множества SQL запросов, усо-
вершенствован сам язык запросов;
∙ В версии 8.3 внедрен полнотекстовый поиск, поддержка SQL/XML
стандарта, параметры конфигурации сервера могут быть установле-
ны на основе отдельных функций;
7
2.1. Введение
∙ В версии 8.4 были внедрены общие табличные выражения, рекур-
сивные запросы, параллельное восстановление, улучшена произво-
дительность для EXISTS/NOT EXISTS запросов;
∙ В версии 9.0 «асинхронная репликация из коробки»,
VACUUM/VACUUM FULL стали быстрее, расширены хранимые
процедуры;
∙ В версии 9.1 «синхронная репликация из коробки», нелогируемые
таблицы (очень быстрые на запись, но при падении БД данные мо-
гут пропасть), новые типы индексов, наследование таблиц в запросах
теперь может вернуться многозначительно отсортированные резуль-
таты, позволяющие оптимизации MIN/MAX;
∙ В версии 9.2 «каскадная репликация из коробки», сканирование по
индексу, JSON тип данных, типы данных на диапазоны, сортировка
в памяти улучшена на 25%, ускорена команда COPY;
∙ В версии 9.3 materialized view, доступные на запись внешние табли-
цы, переход с использования SysV shared memory на POSIX shared
memory и mmap, cокращено время распространения реплик, а так-
же значительно ускорена передача управления от запасного сервера
к первичному, увеличена производительность и улучшена система
блокировок для внешних ключей;
∙ В версии 9.4 появился новый тип поля JSONB (бинарный JSON с
поддержкой индексов), логическое декодирование для репликации,
GIN индекс в 2 раза меньше по размеру и в 3 раза быстрее, неблоки-
руюшие обновление materialized view, поддержка Linux Huge Pages;
∙ В версии 9.5 добавлена поддержка UPSERT, Row Level Security,
CUBE, ROLLUP, GROUPING SETS функции, TABLESAMPLE,
BRIN индекс, ускорена скорость работы индексов для текстовых и
цифровых полей;
∙ В версии 9.6 добавлена поддержка параллелизации некоторых за-
просов, что позволяет использование несколько ядер (CPU core) на
сервере, чтобы возвращать результаты запроса быстрее, полнотек-
стовый поиск поддерживает фразы, новая опция «remote_apply» для
синхронной репликации, которая позволяет дождаться, пока запрос
завершится на слейве;
Следует также отметить, что большая часть изложенного в статье ма-
териала относится к версии сервера не ниже 9.0.
Стоит ли доверять тестам производительности
Перед тем, как заниматься настройкой сервера, вполне естественно
ознакомиться с опубликованными данными по производительности, в том
числе в сравнении с другими СУБД. К сожалению, многие тесты слу-
жат не столько для облегчения вашего выбора, сколько для продвижения
8
2.2. Настройка сервера
конкретных продуктов в качестве «самых быстрых». При изучении опуб-
ликованных тестов в первую очередь обратите внимание, соответствует
ли величина и тип нагрузки, объём данных и сложность запросов в те-
сте тому, что вы собираетесь делать с базой? Пусть, например, обычное
использование вашего приложения подразумевает несколько одновремен-
но работающих запросов на обновление к таблице в миллионы записей. В
этом случае СУБД, которая в несколько раз быстрее всех остальных ищет
запись в таблице в тысячу записей, может оказаться не лучшим выбором.
Ну и наконец, вещи, которые должны сразу насторожить:
∙ Тестирование устаревшей версии СУБД;
∙ Использование настроек по умолчанию (или отсутствие информации
о настройках);
∙ Тестирование в однопользовательском режиме (если, конечно, вы не
предполагаете использовать СУБД именно так);
∙ Использование расширенных возможностей одной СУБД при игно-
рировании расширенных возможностей другой;
∙ Использование заведомо медленно работающих запросов (раздел
«2.5 Оптимизация конкретных запросов»);
2.2 Настройка сервера
В этом разделе описаны рекомендуемые значения параметров, влияю-
щих на производительность СУБД. Эти параметры обычно устанавлива-
ются в конфигурационном файле postgresql.conf и влияют на все базы в
текущей установке.
Используемая память
Общий буфер сервера: shared_buffers
PostgreSQL не читает данные напрямую с диска и не пишет их сразу
на диск. Данные загружаются в общий буфер сервера, находящийся в
разделяемой памяти, серверные процессы читают и пишут блоки в этом
буфере, а затем уже изменения сбрасываются на диск.
Если процессу нужен доступ к таблице, то он сначала ищет нужные
блоки в общем буфере. Если блоки присутствуют, то он может продол-
жать работу, если нет — делается системный вызов для их загрузки. За-
гружаться блоки могут как из файлового кэша ОС, так и с диска, и эта
операция может оказаться весьма «дорогой».
Если объём буфера недостаточен для хранения часто используемых
рабочих данных, то они будут постоянно писаться и читаться из кэша ОС
или с диска, что крайне отрицательно скажется на производительности.
9
2.2. Настройка сервера
В то же время не следует устанавливать это значение слишком боль-
шим: это НЕ вся память, которая нужна для работы PostgreSQL, это толь-
ко размер разделяемой между процессами PostgreSQL памяти, которая
нужна для выполнения активных операций. Она должна занимать мень-
шую часть оперативной памяти вашего компьютера, так как PostgreSQL
полагается на то, что операционная система кэширует файлы, и не стара-
ется дублировать эту работу. Кроме того, чем больше памяти будет отдано
под буфер, тем меньше останется операционной системе и другим прило-
жениям, что может привести к своппингу.
К сожалению, чтобы знать точное число shared_buffers, нужно учесть
количество оперативной памяти компьютера, размер базы данных, число
соединений и сложность запросов, так что лучше воспользуемся несколь-
кими простыми правилами настройки.
На выделенных серверах полезным объемом для shared_buffers будет
значение 1/4 памяти в системе. Если у вас большие активные порции ба-
зы данных, сложные запросы, большое число одновременных соединений,
длительные транзакции, вам доступен большой объем оперативной па-
мяти или большее количество процессоров, то можно подымать это зна-
чение и мониторить результат, чтобы не привести к «деградации» (па-
дению) производительности. Выделив слишком много памяти для базы
данных, мы можем получить ухудшение производительности, посколь-
ку PostgreSQL также использует кэш операционной системы (увеличение
данного параметра более 40% оперативной памяти может давать «нуле-
вой» прирост производительности).
Для тонкой настройки параметра установите для него большое значе-
ние и потестируйте базу при обычной нагрузке. Проверяйте использование
разделяемой памяти при помощи ipcs или других утилит(например, free
или vmstat). Рекомендуемое значение параметра будет примерно в 1,2 –2
раза больше, чем максимум использованной памяти. Обратите внимание,
что память под буфер выделяется при запуске сервера, и её объём при
работе не изменяется. Учтите также, что настройки ядра операционной
системы могут не дать вам выделить большой объём памяти (для версии
PostgreSQL < 9.3). В руководстве администратора PostgreSQL описано,
как можно изменить эти настройки.
Также следует помнить, что на 32 битной системе (Linux) каждый про-
цесс лимитирован в 4 ГБ адресного пространства, где хотя бы 1 ГБ за-
резервирован ядром. Это означает, что не зависимо, сколько на машине
памяти, каждый PostgreSQL инстанс сможет обратиться максимум к 3 ГБ
памяти. А значит максимум для shared_buffers в такой системе — 2–2.5 ГБ.
Хочу обратить внимание, что на Windows, большие значения для
shared_buffers не столь эффективны, как на Linux системах, и в результате
лучшие результаты можно будет получить, если держать это значение от-
носительно небольшое (от 64 МБ до 512 МБ) и использовать кэш системы
вместо него.
10
2.2. Настройка сервера
Память для сортировки результата запроса: work_mem
work_mem параметр определяет максимальное количество оператив-
ной памяти, которое может выделить одна операция сортировки, агрега-
ции и др. Это не разделяемая память, work_mem выделяется отдельно
на каждую операцию (от одного до нескольких раз за один запрос). Ра-
зумное значение параметра определяется следующим образом: количество
доступной оперативной памяти (после того, как из общего объема вычли
память, требуемую для других приложений, и shared_buffers) делится на
максимальное число одновременных запросов умноженное на среднее чис-
ло операций в запросе, которые требуют памяти.
Если объём памяти недостаточен для сортировки некоторого результа-
та, то серверный процесс будет использовать временные файлы. Если же
объём памяти слишком велик, то это может привести к своппингу.
Объём памяти задаётся параметром work_mem в файле postgresql.conf.
Единица измерения параметра — 1 кБ. Значение по умолчанию — 1024. В
качестве начального значения для параметра можете взять 2–4% доступ-
ной памяти. Для веб-приложений обычно устанавливают низкие значения
work_mem, так как запросов обычно много, но они простые, обычно хва-
тает от 512 до 2048 КБ. С другой стороны, приложения для поддержки
принятия решений с сотнями строк в каждом запросе и десятками мил-
лионов столбцов в таблицах фактов часто требуют work_mem порядка 500
МБ. Для баз данных, которые используются и так, и так, этот параметр
можно устанавливать для каждого запроса индивидуально, используя на-
стройки сессии. Например, при памяти 1–4 ГБ рекомендуется устанавли-
вать 32–128 MB.
Максимальное количество клиентов: max_connections
Параметр max_connections устанавливает максимальное количество
клиентов, которые могут подключиться к PostgreSQL. Поскольку для
каждого клиента требуется выделять память (work_mem), то этот пара-
метр предполагает максимально возможное использование памяти для
всех клиентов. Как правило, PostgreSQL может поддерживать несколько
сотен подключений, но создание нового является дорогостоящей операци-
ей. Поэтому, если требуются тысячи подключений, то лучше использовать
пул подключений (отдельная программа или библиотека для продукта,
что использует базу).
Память для работы команды VACUUM: maintenance_work_mem
Этот параметр задаёт объём памяти, используемый командами
VACUUM, ANALYZE, CREATE INDEX, и добавления внешних ключей. Что-
бы операции выполнялись максимально быстро, нужно устанавливать
этот параметр тем выше, чем больше размер таблиц в вашей базе дан-
11
2.2. Настройка сервера
ных. Неплохо бы устанавливать его значение от 50 до 75% размера вашей
самой большой таблицы или индекса или, если точно определить невоз-
можно, от 32 до 256 МБ. Следует устанавливать большее значение, чем для
work_mem. Слишком большие значения приведут к использованию свопа.
Например, при памяти 1–4 ГБ рекомендуется устанавливать 128–512 MB.
Большие страницы: huge_pages
В PostgreSQL, начиная с версии 9.4, появилась поддержка больших
страниц. В ОС Linux работа с памятью основывается на обращении к стра-
ницам размер которых равен 4kB (на самом деле зависит от платформы,
проверить можно через getconf PAGE_SIZE). Так вот, когда объем памя-
ти переваливает за несколько десятков, а то и сотни гигабайт, управлять
ею становится сложнее, увеличиваются накладные расходы на адресацию
памяти и поддержание страничных таблиц. Для облегчения жизни и бы-
ли придуманы большие страницы, размер которых может быть 2MB, а то
и 1GB. За счет использования больших страниц можно получить ощути-
мый прирост скорости работы и увеличение отзывчивости в приложениях
которые активно работают с памятью.
Вообще запустить PostgreSQL с поддержкой больших страниц мож-
но было и раньше, с помощью libhugetlbfs. Однако теперь есть встроен-
ная поддержка. Итак, ниже описание процесса как настроить и запустить
PostgreSQL с поддержкой больших страниц.
Для начала следует убедиться, что ядро поддерживает боль-
шие страницы. Проверяем конфиг ядра на предмет наличия опций
CONFIG_HUGETLBFS и CONFIG_HUGETLB_PAGE.
Листинг 2.1 Проверка конфига ядра на поддержку huge pages
Line 1 $ grep HUGETLB /boot / c onf i g - $ (uname - r )
- CONFIG_CGROUP_HUGETLB=y
- CONFIG_HUGETLBFS=y
- CONFIG_HUGETLB_PAGE=y
В случае отсутствия этих опций, ничего не заработает и ядро следует
пересобрать.
Очевидно, что нам понадобится PostgreSQL версии не ниже 9.4. За
поддержку больших страниц отвечает параметр huge_page, который может
принимать три значения: off — не использовать большие страницы, on —
использовать большие страницы, try — попытаться использовать большие
страницы и в случае недоступности откатиться на использование обычных
страниц. Значение try используется по умолчанию и является безопасным
вариантом. В случае on, PostgreSQL не запустится, если большие страницы
не определены в системе (или их недостаточно).
После перезапуска базы с параметром try потребуется включить под-
держку больших страниц в системе (по умолчанию они не задействованы).
12
2.2. Настройка сервера
Расчет страниц приблизительный и здесь следует опираться на то, сколь-
ко памяти вы готовы выделить под нужды СУБД. Отмечу, что значение
измеряется в страницах размером 2Mb, если вы хотите выделить 16GB,
то это будет 8000 страниц.
Официальная документация предлагает опираться на значение VmPeak
из status файла, который размещен в /proc/PID/ директории, соответ-
ствующей номеру процесса postmaster. VmPeak как следует из названия
это пиковое значение использования виртуальной памяти. Этот вариант
позволяет определить минимальную планку, от которой следует отталки-
ваться, но на мой взгляд такой способ определения тоже носит случайный
характер.
Листинг 2.2 Включаем поддержку huge pages в системе
Line 1 $ head -1 / var / l i b / pg sql / 9. 5 / data / pos tmast er . pid
- 3076
- $ grep ^VmPeak / proc /3076/ s t a t u s
- VmPeak : 4742563 kB
5 $ echo $ ((4742 563 / 2048 + 1) )
- 2316
- $ echo ’vm. nr_hugepages = 2316 ’ >> / et c / s y s c t l . d/30 -
p o s t g r e s q l . con f
- $ s y s c t l -p - - system
В ОС Linux есть также система по менеджменту памяти под названи-
ем «Transparent HugePages», которая включена по умолчанию. Она может
вызывать проблему при работе с huge pages для PostgreSQL, поэтому ре-
комендуется выключать этот механизм:
Листинг 2.3 Отключаем Transparent HugePages
Line 1 $ echo ne ver > / sys / k er n e l /mm/ transparent_hugepage / d ef r ag
- $ echo never > / s y s / k e r n e l /mm/ transparent_hugepage / ena ble d
После этого перезапускаем PostgreSQL и смотрим использование боль-
ших страниц:
Листинг 2.4 Проверяем использование huge pages
Line 1 $ grep ^HugePages / proc /meminfo
- HugePages_Total : 2316
- HugePages_Free : 2301
- HugePages_Rsvd : 128
5 HugePages_Surp : 0
13
2.2. Настройка сервера
Прочие настройки
∙ temp_buffers — буфер под временные объекты, в основном для вре-
менных таблиц. Можно установить порядка 16 МБ;
∙ max_prepared_transactions — количество одновременно подготавлива-
емых транзакций (PREPARE TRANSACTION). Можно оставить по
умолчанию — 5;
∙ vacuum_cost_delay — если у вас большие таблицы, и производит-
ся много одновременных операций записи, вам может пригодиться
функция, которая уменьшает затраты на I/O для VACUUM, растяги-
вая его по времени. Чтобы включить эту функциональность, нужно
поднять значение vacuum_cost_delay выше 0. Используйте разумную
задержку от 50 до 200 мс. Для более тонкой настройки повышайте
vacuum_cost_page_hit и понижайте vacuum_cost_limit. Это ослабит
влияние VACUUM, увеличив время его выполнения. В тестах с па-
раллельными транзакциями Ян Вик (Jan Wieck) получил, что при
значениях delay — 200, page_hit — 6 и limit —100 влияние VACUUM
уменьшилось более чем на 80%, но его длительность увеличилась
втрое;
∙ max_stack_depth — cпециальный стек для сервера, который в идеале
должен совпадать с размером стека, выставленном в ядре ОС. Уста-
новка большего значения, чем в ядре, может привести к ошибкам.
Рекомендуется устанавливать 2–4 MB;
∙ max_files_per_process — максимальное количество файлов, открыва-
емых процессом и его подпроцессами в один момент времени. Умень-
шите данный параметр, если в процессе работы наблюдается сооб-
щение «Too many open files»;
Журнал транзакций и контрольные точки
Для обеспечения отказоустойчивости СУБД PostgreSQL, как и многие
базы данных, использует специальный журнал, в котором ведет историю
изменения данных. Перед тем как записать данные в файлы БД, сервер
PostgreSQL аккумулирует изменения в оперативной памяти и записывает
в последовательный файл журнала, чтобы не потерять их из-за непредви-
денного отключения питания.
Данные в журнал пишутся до того как пользователь базы данных по-
лучит сообщение об успешном применении изменений. Этот журнал на-
зывается журналом упреждающей записи (Write-Ahead Log или просто
WAL), а файлы журнала хранятся в каталоге pg_xlog. Также периоди-
чески PostgreSQL сбрасывает измененные аккумулированные данные из
оперативной памяти на диск. Этот процесс согласования данных назы-
вается контрольной точкой (checkpoint). Контрольная точка выполняется
также при каждом штатном выключении PostgreSQL.
14
2.2. Настройка сервера
В этом случае нет необходимости сбрасывать на диск изменения дан-
ных при каждом успешном завершении транзакции: в случае сбоя БД
может быть восстановлена по записям в журнале. Таким образом, данные
из буферов сбрасываются на диск при проходе контрольной точки: либо
при заполнении нескольких (параметр checkpoint_segments, по умолчанию
3) сегментов журнала транзакций, либо через определённый интервал вре-
мени (параметр checkpoint_timeout, измеряется в секундах, по умолчанию
300).
Изменение этих параметров прямо не повлияет на скорость чтения, но
может принести большую пользу, если данные в базе активно изменяются.
Уменьшение количества контрольных точек: checkpoint_segments
Если в базу заносятся большие объёмы данных, то контрольные точ-
ки могут происходить слишком часто («слишком часто» можно опре-
делить как «чаще раза в минуту». Вы также можете задать параметр
checkpoint_warning (в секундах): в журнал сервера будут писаться преду-
преждения, если контрольные точки происходят чаще заданного). При
этом производительность упадёт из-за постоянного сбрасывания на диск
данных из буфера.
Для увеличения интервала между контрольными точками нужно
увеличить количество сегментов журнала транзакций через параметр
checkpoint_segments. Данный параметр определяет количество сегментов
(каждый по 16 МБ) лога транзакций между контрольными точками. Этот
параметр не имеет особого значения для базы данных, предназначенной
преимущественно для чтения, но для баз данных со множеством тран-
закций увеличение этого параметра может оказаться жизненно необходи-
мым. В зависимости от объема данных установите этот параметр в диа-
пазоне от 12 до 256 сегментов и, если в логе появляются предупреждения
(warning) о том, что контрольные точки происходят слишком часто, по-
степенно увеличивайте его. Место, требуемое на диске, вычисляется по
формуле (checkpoint_segments * (2 + checkpoint_completion_target) + 1) * 16
МБ, так что убедитесь, что у вас достаточно свободного места. Например,
если вы выставите значение 32, вам потребуется больше 1 ГБ дискового
пространства.
Следует также отметить, что чем больше интервал между контроль-
ными точками, тем дольше будут восстанавливаться данные по журналу
транзакций после сбоя.
Начиная с версии 9.5 checkpoint_segments был заменен на параметры
min_wal_size и max_wal_size. Теперь система может автоматически сама
решать сколько checkpoint_segments требуется хранить (вычислять по ра-
нее приведенной формуле от указанного размера). Преимуществом этого
является то, что вы можете установить max_wal_size очень большим, но
система не будет на самом деле потреблять указанное количество места
на жестком диске, если в этом нет никакой необходимости. min_wal_size
15
2.2. Настройка сервера
устанавливает минимальный размер места, который будет использовать-
ся сегментами (можно отключить такую автонастройку, установив для
min_wal_size и max_wal_size одинаковое значение).
fsync, synchronous_commit и стоит ли их трогать
Для увеличения производительности наиболее радикальное из возмож-
ных решений — выставить значение «off» параметру fsync. При этом запи-
си в журнале транзакций не будут принудительно сбрасываться на диск,
что даст большой прирост скорости записи. Учтите: вы жертвуете надёж-
ностью, в случае сбоя целостность базы будет нарушена, и её придётся
восстанавливать из резервной копии! Использовать этот параметр реко-
мендуется лишь в том случае, если вы всецело доверяете своему «железу»
и своему источнику бесперебойного питания. Ну или если данные в базе
не представляют для вас особой ценности.
Параметр synchronous_commit определяет нужно ли ждать WAL запи-
си на диск перед возвратом успешного завершения транзакции для под-
ключенного клиента. По умолчанию и для безопасности данный параметр
установлен в «on» (включен). При выключении данного параметра («off»)
может существовать задержка между моментом, когда клиенту будет со-
общено об успехе транзакции и когда та самая транзакция действительно
гарантированно и безопасно записана на диск (максимальная задержка —
wal_writer_delay * 3). В отличие от fsync, отключение этого параметра не
создает риск краха базы данных: данные могут быть потеряны (послед-
ний набор транзакций), но базу данных не придется восстанавливать после
сбоя из бэкапа. Так что synchronous_commit может быть полезной альтер-
нативой, когда производительность важнее, чем точная уверенность в со-
гласовании данных (данный режим можно назвать «режимом MongoDB»:
изначально все клиенты для MongoDB не проверяли успешность записи
данных в базу и за счет этого достигалась хорошая скорость для бенчмар-
ков).
Прочие настройки
∙ commit_delay (в микросекундах, 0 по умолчанию) и commit_siblings
(5 по умолчанию) определяют задержку между попаданием запи-
си в буфер журнала транзакций и сбросом её на диск. Если при
успешном завершении транзакции активно не менее commit_siblings
транзакций, то запись будет задержана на время commit_delay. Если
за это время завершится другая транзакция, то их изменения будут
сброшены на диск вместе, при помощи одного системного вызова.
Эти параметры позволят ускорить работу, если параллельно выпол-
няется много «мелких» транзакций;
16
2.2. Настройка сервера
∙ wal_sync_method Метод, который используется для принудительной
записи данных на диск. Если fsync=off, то этот параметр не исполь-
зуется. Возможные значения:
– open_datasync — запись данных методом open() с параметром
O_DSYNC;
– fdatasync — вызов метода fdatasync() после каждого commit;
– fsync_writethrough — вызов fsync() после каждого commit, игно-
рируя параллельные процессы;
– fsync — вызов fsync() после каждого commit;
– open_sync — запись данных методом open() с параметром
O_SYNC;
Не все эти методы доступны на разных ОС. По умолчанию устанав-
ливается первый, который доступен для системы;
∙ full_page_writes Установите данный параметр в «off», если fsync=off.
Иначе, когда этот параметр «on», PostgreSQL записывает содержи-
мое каждой записи в журнал транзакций при первой модификации
таблицы. Это необходимо, поскольку данные могут записаться лишь
частично, если в ходе процесса «упала» ОС. Это приведет к тому, что
на диске окажутся новые данные смешанные со старыми. Строкового
уровня записи в журнал транзакций может быть недостаточно, что-
бы полностью восстановить данные после «падения». full_page_writes
гарантирует корректное восстановление, ценой увеличения записы-
ваемых данных в журнал транзакций (Единственный способ сниже-
ния объема записи в журнал транзакций заключается в увеличении
checkpoint_interval);
∙ wal_buffers Количество памяти, используемое в Shared Memory для
ведения транзакционных логов (буфер находится в разделяемой па-
мяти и является общим для всех процессов). Стоит увеличить буфер
до 256–512 кБ, что позволит лучше работать с большими транзакци-
ями. Например, при доступной памяти 1–4 ГБ рекомендуется уста-
навливать 256–1024 КБ;
Планировщик запросов
Следующие настройки помогают планировщику запросов правильно
оценивать стоимости различных операций и выбирать оптимальный план
выполнения запроса. Существуют 3 настройки планировщика, на которые
стоит обратить внимание:
∙ default_statistics_target — этот параметр задаёт объём статистики, со-
бираемой командой ANALYZE. Увеличение параметра заставит эту
команду работать дольше, но может позволить оптимизатору стро-
ить более быстрые планы, используя полученные дополнительные
данные. Объём статистики для конкретного поля может быть задан
командой ALTER TABLE ... SET STATISTICS;
17
2.2. Настройка сервера
∙ effective_cache_size — этот параметр сообщает PostgreSQL примерный
объём файлового кэша операционной системы, оптимизатор исполь-
зует эту оценку для построения плана запроса (указывает планиров-
щику на размер самого большого объекта в базе данных, который
теоретически может быть закеширован). Пусть в вашем компьютере
1.5 ГБ памяти, параметр shared_buffers установлен в 32 МБ, а па-
раметр effective_cache_size в 800 МБ. Если запросу нужно 700 МБ
данных, то PostgreSQL оценит, что все нужные данные уже есть в
памяти и выберет более агрессивный план с использованием индек-
сов и merge joins. Но если effective_cache_size будет всего 200 МБ, то
оптимизатор вполне может выбрать более эффективный для диско-
вой системы план, включающий полный просмотр таблицы.
На выделенном сервере имеет смысл выставлять effective_cache_size в
2/3 от всей оперативной памяти; на сервере с другими приложениями
сначала нужно вычесть из всего объема RAM размер дискового кэша
ОС и память, занятую остальными процессами;
∙ random_page_cost — переменная, указывающая на условную стои-
мость индексного доступа к страницам данных. На серверах с быст-
рыми дисковыми массивами имеет смысл уменьшать изначальную
настройку до 3.0, 2.5 или даже до 2.0. Если же активная часть вашей
базы данных намного больше размеров оперативной памяти, попро-
буйте поднять значение параметра. Можно подойти к выбору опти-
мального значения и со стороны производительности запросов. Если
планировщик запросов чаще, чем необходимо, предпочитает после-
довательные просмотры (sequential scans) просмотрам с использова-
нием индекса (index scans), понижайте значение. И наоборот, если
планировщик выбирает просмотр по медленному индексу, когда не
должен этого делать, настройку имеет смысл увеличить. После из-
менения тщательно тестируйте результаты на максимально широком
наборе запросов. Никогда не опускайте значение random_page_cost
ниже 2.0; если вам кажется, что random_page_cost нужно еще пони-
жать, разумнее в этом случае менять настройки статистики плани-
ровщика.
Сбор статистики
У PostgreSQL также есть специальная подсистема — сборщик статисти-
ки, — которая в реальном времени собирает данные об активности сервера.
Поскольку сбор статистики создает дополнительные накладные расходы
на базу данных, то система может быть настроена как на сбор, так и не
сбор статистики вообще. Эта система контролируется следующими пара-
метрами, принимающими значения true/ false :
∙ track_counts — включать ли сбор статистики. По умолчанию вклю-
чён, поскольку autovacuum демону требуется сбор статистики. От-
18
2.3. Диски и файловые системы
ключайте, только если статистика вас совершенно не интересует (как
и autovacuum);
∙ track_functions — отслеживание использования определенных поль-
зователем функций;
∙ track_activities — передавать ли сборщику статистики информацию
о текущей выполняемой команде и времени начала её выполнения.
По умолчанию эта возможность включена. Следует отметить, что
эта информация будет доступна только привилегированным пользо-
вателям и пользователям, от лица которых запущены команды, так
что проблем с безопасностью быть не должно;
Данные, полученные сборщиком статистики, доступны через специаль-
ные системные представления. При установках по умолчанию собирается
очень мало информации, рекомендуется включить все возможности: до-
полнительная нагрузка будет невелика, в то время как полученные дан-
ные позволят оптимизировать использование индексов (а также помогут
оптимальной работе autovacuum демону).
2.3 Диски и файловые системы
Очевидно, что от качественной дисковой подсистемы в сервере БД за-
висит немалая часть производительности. Вопросы выбора и тонкой на-
стройки «железа», впрочем, не являются темой данной главы, ограничим-
ся уровнем файловой системы.
Единого мнения насчёт наиболее подходящей для PostgreSQL файло-
вой системы нет, поэтому рекомендуется использовать ту, которая лучше
всего поддерживается вашей операционной системой. При этом учтите,
что современные журналирующие файловые системы не намного медлен-
нее нежурналирующих, а выигрыш — быстрое восстановление после сбо-
ев — от их использования велик.
Вы легко можете получить выигрыш в производительности без побоч-
ных эффектов, если примонтируете файловую систему, содержащую базу
данных, с параметром noatime (но при этом не будет отслеживаться время
последнего доступа к файлу).
Перенос журнала транзакций на отдельный диск
При доступе к диску изрядное время занимает не только собственно
чтение данных, но и перемещение магнитной головки.
Если в вашем сервере есть несколько физических дисков (несколько
логических разделов на одном диске здесь, очевидно, не помогут: головка
всё равно будет одна), то вы можете разнести файлы базы данных и жур-
нал транзакций по разным дискам. Данные в сегменты журнала пишутся
19
2.4. Утилиты для тюнинга PostgreSQL
последовательно, более того, записи в журнале транзакций сразу сбра-
сываются на диск, поэтому в случае нахождения его на отдельном диске
магнитная головка не будет лишний раз двигаться, что позволит ускорить
запись.
Порядок действий:
∙ Остановите сервер (!);
∙ Перенесите каталоги pg_clog и pg_xlog, находящийся в каталоге с
базами данных, на другой диск;
∙ Создайте на старом месте символическую ссылку;
∙ Запустите сервер;
Примерно таким же образом можно перенести и часть файлов, содер-
жащих таблицы и индексы, на другой диск, но здесь потребуется больше
кропотливой ручной работы, а при внесении изменений в схему базы про-
цедуру, возможно, придётся повторить.
CLUSTER
CLUSTER table [ USING index ] — команда для упорядочивания записей
таблицы на диске согласно индексу, что иногда за счет уменьшения досту-
па к диску ускоряет выполнение запроса. Возможно создать только один
физический порядок в таблице, поэтому и таблица может иметь только
один кластерный индекс. При таком условии нужно тщательно выбирать,
какой индекс будет использоваться для кластерного индекса.
Кластеризация по индексу позволяет сократить время поиска по дис-
ку: во время поиска по индексу выборка данных может быть значительно
быстрее, так как последовательность данных в таком же порядке, как и
индекс. Из минусов можно отметить то, что команда CLUSTER требует
«ACCESS EXCLUSIVE» блокировку, что предотвращает любые другие
операции с данными (чтения и записи) пока кластеризация не завершит
выполнение. Также кластеризация индекса в PostgreSQL не утвержда-
ет четкий порядок следования, поэтому требуется повторно выполнять
CLUSTER для поддержания таблицы в порядке.
2.4 Утилиты для тюнинга PostgreSQL
Pgtune
Для оптимизации настроек для PostgreSQL Gregory Smith создал ути-
литу pgtune в расчёте на обеспечение максимальной производительности
для заданной аппаратной конфигурации. Утилита проста в использовании
и во многих Linux системах может идти в составе пакетов. Если же нет,
можно просто скачать архив и распаковать. Для начала:
20
2.4. Утилиты для тюнинга PostgreSQL
Листинг 2.5 Pgtune
Line 1 $ pgtune - i $PGDATA/ p o s t g r e s q l . c on f - o $PGDATA/ p o s t g r e s q l .
conf . pgtune
опцией - i , --input-config указываем текущий файл postgresql.conf, а -o
, --output-config указываем имя файла для нового postgresql.conf.
Есть также дополнительные опции для настройки конфига:
∙ -M, --memory используйте этот параметр, чтобы определить общий
объем системной памяти. Если не указано, pgtune будет пытаться
использовать текущий объем системной памяти;
∙ -T, --type указывает тип базы данных. Опции: DW, OLTP, Web,
Mixed, Desktop;
∙ -c, -- connections указывает максимальное количество соединений. Ес-
ли он не указан, то будет браться в зависимости от типа базы данных;
Существует также онлайн версия pgtune.
Хочется сразу добавить, что pgtune не «серебряная пуля» для опти-
мизации настройки PostgreSQL. Многие настройки зависят не только от
аппаратной конфигурации, но и от размера базы данных, числа соедине-
ний и сложности запросов, так что оптимально настроить базу данных
возможно, только учитывая все эти параметры.
pg_buffercache
Pg_buffercache — расширение для PostgreSQL, которое позволяет по-
лучить представление об использовании общего буфера (shared_buffer) в
базе. Расширение позволяет взглянуть какие из данных кэширует база,
которые активно используются в запросах. Для начала нужно установить
расширение:
Листинг 2.6 pg_buffercache
Line 1 # CREATE EXTENSION pg_ buffercach e ;
Теперь доступно pg_buffercache представление, которое содержит:
∙ bufferid — ID блока в общем буфере;
∙ relfilenode — имя папки, где данные расположены;
∙ reltablespace — Oid таблицы;
∙ reldatabase — Oid базы данных;
∙ relforknumber — номер ответвления;
∙ relblocknumber — номер страницы;
∙ isdirty — грязная страница?;
∙ usagecount — количество LRU страниц;
21
2.4. Утилиты для тюнинга PostgreSQL
ID блока в общем буфере (bufferid) соответствует количеству использу-
емого буфера таблицей, индексом, прочим. Общее количество доступных
буферов определяется двумя вещами:
∙ Размер буферного блока. Этот размер блока определяется опцией --
with-blocksize при конфигурации. Значение по умолчанию — 8 КБ,
что достаточно в большинстве случаев, но его возможно увеличить
или уменьшить в зависимости от ситуации. Для того чтобы изменить
это значение, необходимо будет перекомпилировать PostgreSQL;
∙ Размер общего буфера. Определяется опцией shared_buffers в
PostgreSQL конфиге.
Например, при использовании shared_buffers в 128 МБ с 8 КБ разме-
ра блока получится 16384 буферов. Представление pg_buffercache будет
иметь такое же число строк — 16384. С shared_buffers в 256 МБ и разме-
ром блока в 1 КБ получим 262144 буферов.
Для примера рассмотрим простой запрос показывающий использова-
ние буферов объектами (таблицами, индексами, прочим):
Листинг 2.7 pg_buffercache
Line 1 # SELECT c . relname , count ( * ) AS b u f f e r s
- FROM p g_buffe rca che b INNER JOIN pg_ class c
- ON b . r e l f i l e n o d e = p g_r e la t i on _ f il e nod e ( c . oid ) AND
- b . r e l d a t a b a s e IN ( 0 , (SELECT oi d FROM pg_database WHERE
datname = current_database ( ) ) )
5 GROUP BY c . relname
- ORDER BY 2 DESC
- LIMIT 1 0;
-
- relname | b u f f e r s
10 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - -
- pgbench_accounts | 4082
- pgbench_history | 53
- pg_ a t tribut e | 23
- pg_proc | 14
15 pg_operator | 11
- pg_proc_oid_index | 9
- p g_class | 8
- pg_attribute_relid_attnum_index | 7
- pg_proc_proname_args_nsp_index | 6
20 pg_class_oid_index | 5
- (10 rows )
Этот запрос показывает объекты (таблицы и индексы) в кэше:
22
2.4. Утилиты для тюнинга PostgreSQL
Листинг 2.8 pg_buffercache
Line 1 # SELECT c . relname , count ( * ) AS b u f f e r s , usa gecount
- FROM pg_clas s c
- INNER JOIN pg_bufferca che b
- ON b . r e l f i l e n o d e = c . r e l f i l e n o d e
5 INNER JOIN pg_database d
- ON ( b . r e l d a t a b a s e = d . oid AND d . datname = current_database
( ) )
- GROUP BY c . relname , usa gecount
- ORDER BY c . relname , usagecount ;
-
10 relname | b u f f e r s | usagecount
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - + - - - - - - - - - - - -
- pg_rewrite | 3 | 1
- pg_rewrite_rel_rulename_index | 1 | 1
- pg_rewrite_rel_rulename_index | 1 | 2
15 p g _ s t a t i s t i c | 1 | 1
- p g _ s t a t i s t i c | 1 | 3
- p g _ s t a t i s t i c | 2 | 5
- pg_stat i s t i c _ r e l id_att_inh_inde x | 1 | 1
- pg_stat i s t i c _ r e l id_att_inh_inde x | 3 | 5
20 pgbench_accounts | 4082 | 2
- pgbench_accounts_pkey | 1 | 1
- pgbench_history | 53 | 1
- p g b enc h _ te lle r s | 1 | 1
Это запрос показывает какой процент общего буфера используют
обьекты (таблицы и индексы) и на сколько процентов объекты находятся
в самом кэше (буфере):
Листинг 2.9 pg_buffercache
Line 1 # SELECT
- c . relname ,
- pg_size_pretty ( count ( * ) * 8192) as b u ff er ed ,
- round ( 10 0 .0 * count ( *) /
5 (SELECT s e t t i n g FROM p g _s e t t i n g s WHERE name=’ sh a r e d_ bu ff e rs
’ ) : : i nt e g e r , 1 )
- AS bu ff e rs _ pe r c en t ,
- round ( 10 0 .0 * count ( *) * 8192 / p g_table _size ( c . o i d ) , 1 )
- AS per c e nt_o f _ rela t i on
- FROM pg_class c
10 INNER JOIN pg_bufferca che b
- ON b . r e l f i l e n o d e = c . r e l f i l e n o d e
- INNER JOIN pg_database d
- ON ( b . r e l d a t a b a s e = d . oid AND d . datname = current_database
( ) )
23
2.5. Оптимизация БД и приложения
- GROUP BY c . oid , c . relname
15 ORDER BY 3 DESC
- LIMIT 2 0;
-
- - [ RECORD 1 ] - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- relname | pgbench_accounts
20 b u f f e r ed | 32 MB
- b u ff e rs_ p erc e nt | 24 . 9
- p e r cent _ o f_re l a tion | 9 9 .9
- - [ RECORD 2 ] - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- relname | pgbench_history
25 b u f f e r ed | 424 kB
- b u ff e rs_ p erc e nt | 0 . 3
- p e r cent _ o f_re l a tion | 9 4 .6
- - [ RECORD 3 ] - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- relname | pg_operator
30 b u f f e r ed | 88 kB
- b u ff e rs_ p erc e nt | 0 . 1
- p e r cent _ o f_re l a tion | 6 1 .1
- - [ RECORD 4 ] - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- relname | pg_opclass_oid_index
35 b u f f e r e d | 16 kB
- b u ff e rs_ p erc e nt | 0 . 0
- p e r cent _ o f_re l a tion | 1 0 0 . 0
- - [ RECORD 5 ] - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- relname | p g _ s t a t i stic_relid_att_ i n h _ i n d e x
40 b u f f e r e d | 32 kB
- b u ff e rs_ p erc e nt | 0 . 0
- p e r cent _ o f_re l a tion | 1 0 0 . 0
Используя эти данные можно проанализировать для каких объектов
не хватает памяти или какие из них потребляют основную часть общего
буфера. На основе этого можно более правильно настраивать shared_buffers
параметр для PostgreSQL.
2.5 Оптимизация БД и приложения
Для быстрой работы каждого запроса в вашей базе в основном требу-
ется следующее:
1. Отсутствие в базе мусора, мешающего добраться до актуальных дан-
ных. Можно сформулировать две подзадачи:
a) Грамотное проектирование базы. Освещение этого вопроса вы-
ходит далеко за рамки этой книги;
b) Сборка мусора, возникающего при работе СУБД;
2. Наличие быстрых путей доступа к данным — индексов;
24
2.5. Оптимизация БД и приложения
3. Возможность использования оптимизатором этих быстрых путей;
4. Обход известных проблем;
Поддержание базы в порядке
В данном разделе описаны действия, которые должны периодически
выполняться для каждой базы. От разработчика требуется только настро-
ить их автоматическое выполнение (при помощи cron) и опытным путём
подобрать оптимальную частоту.
Команда ANALYZE
Служит для обновления информации о распределении данных в таб-
лице. Эта информация используется оптимизатором для выбора наиболее
быстрого плана выполнения запроса.
Обычно команда используется в связке с VACUUM ANALYZE. Если в
базе есть таблицы, данные в которых не изменяются и не удаляются, а
лишь добавляются, то для таких таблиц можно использовать отдельную
команду ANALYZE. Также стоит использовать эту команду для отдельной
таблицы после добавления в неё большого количества записей.
Команда REINDEX
Команда REINDEX используется для перестройки существующих ин-
дексов. Использовать её имеет смысл в случае:
∙ порчи индекса;
∙ постоянного увеличения его размера;
Второй случай требует пояснений. Индекс, как и таблица, содержит
блоки со старыми версиями записей. PostgreSQL не всегда может заново
использовать эти блоки, и поэтому файл с индексом постепенно увеличи-
вается в размерах. Если данные в таблице часто меняются, то расти он
может весьма быстро.
Если вы заметили подобное поведение какого-то индекса, то стоит на-
строить для него периодическое выполнение команды REINDEX. Учтите:
команда REINDEX, как и VACUUM FULL, полностью блокирует таблицу,
поэтому выполнять её надо тогда, когда загрузка сервера минимальна.
Использование индексов
Опыт показывает, что наиболее значительные проблемы с производи-
тельностью вызываются отсутствием нужных индексов. Поэтому столк-
нувшись с медленным запросом, в первую очередь проверьте, существуют
ли индексы, которые он может использовать. Если нет — постройте их.
Излишек индексов, впрочем, тоже чреват проблемами:
25
2.5. Оптимизация БД и приложения
∙ Команды, изменяющие данные в таблице, должны изменить также
и индексы. Очевидно, чем больше индексов построено для таблицы,
тем медленнее это будет происходить;
∙ Оптимизатор перебирает возможные пути выполнения запросов. Ес-
ли построено много ненужных индексов, то этот перебор будет идти
дольше;
Единственное, что можно сказать с большой степенью определённо-
сти — поля, являющиеся внешними ключами, и поля, по которым объеди-
няются таблицы, индексировать надо обязательно.
Команда EXPLAIN [ANALYZE]
Команда EXPLAIN запрос[] показывает, каким образом PostgreSQL со-
бирается выполнять ваш запрос. Команда EXPLAIN ANALYZE запрос[] вы-
полняет запрос (и поэтому EXPLAIN ANALYZE DELETE . . . — не слиш-
ком хорошая идея) и показывает как изначальный план, так и реальный
процесс его выполнения.
Чтение вывода этих команд — искусство, которое приходит с опытом.
Для начала обращайте внимание на следующее:
∙ Использование полного просмотра таблицы (seq scan);
∙ Использование наиболее примитивного способа объединения таблиц
(nested loop);
∙ Для EXPLAIN ANALYZE: нет ли больших отличий в предполагае-
мом количестве записей и реально выбранном? Если оптимизатор
использует устаревшую статистику, то он может выбирать не самый
быстрый план выполнения запроса;
Следует отметить, что полный просмотр таблицы далеко не всегда
медленнее просмотра по индексу. Если, например, в таблице–справочнике
несколько сотен записей, умещающихся в одном-двух блоках на диске, то
использование индекса приведёт лишь к тому, что придётся читать ещё
и пару лишних блоков индекса. Если в запросе придётся выбрать 80%
записей из большой таблицы, то полный просмотр опять же получится
быстрее.
При тестировании запросов с использованием EXPLAIN ANALYZE
можно воспользоваться настройками, запрещающими оптимизатору ис-
пользовать определённые планы выполнения. Например,
Листинг 2.10 enable_seqscan
Line 1 SET enable_seq scan=f a l s e ;
26
2.5. Оптимизация БД и приложения
запретит использование полного просмотра таблицы, и вы сможете
выяснить, прав ли был оптимизатор, отказываясь от использования ин-
декса. Ни в коем случае не следует прописывать подобные команды в
postgresql.conf! Это может ускорить выполнение нескольких запросов, но
сильно замедлит все остальные!
Использование собранной статистики
Результаты работы сборщика статистики доступны через специальные
системные представления. Наиболее интересны для наших целей следую-
щие:
∙ pg_stat_user_tables содержит — для каждой пользовательской табли-
цы в текущей базе данных — общее количество полных просмотров
и просмотров с использованием индексов, общие количества запи-
сей, которые были возвращены в результате обоих типов просмотра,
а также общие количества вставленных, изменённых и удалённых
записей;
∙ pg_stat_user_indexes содержит — для каждого пользовательского ин-
декса в текущей базе данных — общее количество просмотров, ис-
пользовавших этот индекс, количество прочитанных записей, коли-
чество успешно прочитанных записей в таблице (может быть меньше
предыдущего значения, если в индексе есть записи, указывающие на
устаревшие записи в таблице);
∙ pg_statio_user_tables содержит — для каждой пользовательской таб-
лицы в текущей базе данных — общее количество блоков, прочитан-
ных из таблицы, количество блоков, оказавшихся при этом в буфере
(см. пункт 2.1.1), а также аналогичную статистику для всех индексов
по таблице и, возможно, по связанной с ней таблицей TOAST;
Из этих представлений можно узнать, в частности:
∙ Для каких таблиц стоит создать новые индексы (индикатором слу-
жит большое количество полных просмотров и большое количество
прочитанных блоков);
∙ Какие индексы вообще не используются в запросах. Их имеет смысл
удалить, если, конечно, речь не идёт об индексах, обеспечивающих
выполнение ограничений PRIMARY KEY и UNIQUE;
∙ Достаточен ли объём буфера сервера;
Также возможен «дедуктивный» подход, при котором сначала созда-
ётся большое количество индексов, а затем неиспользуемые индексы уда-
ляются.
27
2.5. Оптимизация БД и приложения
Перенос логики на сторону сервера
Этот пункт очевиден для опытных пользователей PostrgeSQL и предна-
значен для тех, кто использует или переносит на PostgreSQL приложения,
написанные изначально для более примитивных СУБД.
Реализация части логики на стороне сервера через хранимые проце-
дуры, триггеры, правила
1
часто позволяет ускорить работу приложения.
Действительно, если несколько запросов объединены в процедуру, то не
требуется
∙ пересылка промежуточных запросов на сервер;
∙ получение промежуточных результатов на клиент и их обработка;
Кроме того, хранимые процедуры упрощают процесс разработки и под-
держки: изменения надо вносить только на стороне сервера, а не менять
запросы во всех приложениях.
Оптимизация конкретных запросов
В этом разделе описываются запросы, для которых по разным причи-
нам нельзя заставить оптимизатор использовать индексы, и которые бу-
дут всегда вызывать полный просмотр таблицы. Таким образом, если вам
требуется использовать эти запросы в требовательном к быстродействию
приложении, то придётся их изменить.
SELECT count(*) FROM <огромная таблица>
Функция count() работает очень просто: сначала выбираются все за-
писи, удовлетворяющие условию, а потом к полученному набору запи-
сей применяется агрегатная функция — считается количество выбранных
строк. Информация о видимости записи для текущей транзакции (а кон-
курентным транзакциям может быть видимо разное количество записей
в таблице!) не хранится в индексе, поэтому, даже если использовать для
выполнения запроса индекс первичного ключа таблицы, всё равно потре-
буется чтение записей собственно из файла таблицы.
Проблема Запрос вида
Листинг 2.11 SQL
Line 1 SELECT count ( *) FROM f oo ;
осуществляет полный просмотр таблицы foo, что весьма долго для таб-
лиц с большим количеством записей.
Решение Простого решения проблемы, к сожалению, нет. Возможны
следующие подходы:
1
RULE — реализованное в PostgreSQL расширение стандарта SQL, позволяющее, в
частности, создавать обновляемые представления
28
2.5. Оптимизация БД и приложения
1. Если точное число записей не важно, а важен порядок
1
, то можно ис-
пользовать информацию о количестве записей в таблице, собранную
при выполнении команды ANALYZE:
Листинг 2.12 SQL
Line 1 SELECT r e l t u p l e s FROM pg _class WHERE relname = ’ fo o ’ ;
2. Если подобные выборки выполняются часто, а изменения в табли-
це достаточно редки, то можно завести вспомогательную таблицу,
хранящую число записей в основной. На основную же таблицу пове-
сить триггер, который будет уменьшать это число в случае удаления
записи и увеличивать в случае вставки. Таким образом, для полу-
чения количества записей потребуется лишь выбрать одну запись из
вспомогательной таблицы;
3. Вариант предыдущего подхода, но данные во вспомогательной таб-
лице обновляются через определённые промежутки времени (cron);
Медленный DISTINCT
Текущая реализация DISTINCT для больших таблиц очень медлен-
на. Но возможно использовать GROUP BY взамен DISTINCT. GROUP BY
может использовать агрегирующий хэш, что значительно быстрее, чем
DISTINCT (актуально до версии 8.4 и ниже).
Листинг 2.13 DISTINCT
Line 1 po st gr e s=# s e l e c t count ( *) from ( s e l e c t d i s t i n c t i from g ) a
;
- count
- - - - - - - -
- 19125
5 (1 row )
-
- Time : 580 ,553 ms
-
-
10 p o s t g r e s=# s e l e c t count ( * ) from ( s e l e c t d i s t i n c t i from g ) a
;
- count
- - - - - - - -
- 19125
- (1 row )
15
- Time : 36 ,281 ms
1
«на нашем форуме более 10000 зарегистрированных пользователей, оставивших
более 50000 сообщений!»
29
2.5. Оптимизация БД и приложения
Листинг 2.14 GROUP BY
Line 1 p os tg re s=# s e l e c t count ( * ) from ( s e l e c t i from g group by i )
a ;
- count
- - - - - - - -
- 19125
5 (1 row )
-
- Time : 26 ,562 ms
-
-
10 p o s t g r e s=# s e l e c t count ( * ) from ( s e l e c t i from g group by i )
a ;
- count
- - - - - - - -
- 19125
- (1 row )
15
- Time : 25 ,270 ms
Утилиты для оптимизации запросов
pgFouine
pgFouine — это анализатор log-файлов для PostgreSQL, используемый
для генерации детальных отчетов из log-файлов PostgreSQL. pgFouine по-
может определить, какие запросы следует оптимизировать в первую оче-
редь. pgFouine написан на языке программирования PHP с использовани-
ем объектно-ориентированных технологий и легко расширяется для под-
держки специализированных отчетов, является свободным программным
обеспечением и распространяется на условиях GNU General Public License.
Утилита спроектирована таким образом, чтобы обработка очень больших
log-файлов не требовала много ресурсов.
Для работы с pgFouine сначала нужно сконфигурировать PostgreSQL
для создания нужного формата log-файлов:
∙ Чтобы включить протоколирование в syslog
Листинг 2.15 pgFouine
Line 1 l o g_ d es t in a ti o n = ’ s y s l o g ’
- r e d i r e c t _ s t d e r r = o f f
- silent_mode = on
-
∙ Для записи запросов, длящихся дольше n миллисекунд:
30
2.5. Оптимизация БД и приложения
Листинг 2.16 pgFouine
Line 1 log_min_duration_statement = n
- log_ dura tion = o f f
- log_statement = ’ none ’
-
Для записи каждого обработанного запроса установите
log_min_duration_statement на 0. Чтобы отключить запись запросов,
установите этот параметр на -1.
pgFouine — простой в использовании инструмент командной строки.
Следующая команда создаёт HTML-отчёт со стандартными параметрами:
Листинг 2.17 pgFouine
Line 1 pgf o u in e . php - f i l e your / l og / f i l e . l og > your - r e po rt . html
С помощью этой строки можно отобразить текстовый отчёт с 10 запро-
сами на каждый экран на стандартном выводе:
Листинг 2.18 pgFouine
Line 1 pgf o u in e . php - f i l e your / l og / f i l e . l og - top 10 - format t e xt
Более подробно о возможностях, а также много полез-
ных примеров, можно найти на официальном сайте проекта
pgfouine.projects.pgfoundry.org.
pgBadger
pgBadger — аналогичная утилита, что и pgFouine, но написанная на
Perl. Еще одно большое преимущество проекта в том, что он более активно
сейчас разрабатывается (на момент написания этого текста последний ре-
лиз pgFouine был в 24.02.2010, а последняя версия pgBadger — 24.01.2017).
Установка pgBadger проста:
Листинг 2.19 Установка pgBadger
Line 1 $ t ar x zf pgbadger - 2 . x . t ar . gz
- $ cd pgbadger - 2 . x/
- $ p e r l M a k ef il e . PL
- $ make && sudo make i n s t a l l
Как и в случае с pgFouine нужно настроить PostgreSQL логи:
Листинг 2.20 Настройка логов PostgreSQL
Line 1 l o g g i n g _ c o l l e c t o r = on
31
2.5. Оптимизация БД и приложения
- log_min_messages = debug1
- log_min_error_statement = debug1
- log_min_duration_statement = 0
5 l o g_ l in e _p r ef i x = ’%t [%p ] : [% l - 1 ] u s e r=%u , db=%d ’
- l o g _ checkpoints = on
- l o g _ c o n n e c t i o n s = on
- lo g _d i sco n ne c tio n s = on
- log_lock_waits = on
10 log _tem p_fi les = 0
Парсим логи PostgreSQL через pgBadger:
Листинг 2.21 Запуск pgBadger
Line 1 $ . / pgbadger ~/ p g sq l / master /pg_log/ p o s t g re sq l -2 0 12 -08 -30 _132
*
- [========================>] Parsed 10485768 bytes o f
10485768 (100.00%)
- [========================>] Parsed 10485828 bytes o f
10485828 (100.00%)
- [========================>] Parsed 10485851 bytes o f
10485851 (100.00%)
5 [========================>] Parsed 10485848 bytes o f
10485848 (100.00%)
- [========================>] Parsed 10485839 bytes o f
10485839 (100.00%)
- [========================>] Parsed 982536 bytes o f 982536
(100.00%)
В результате получится HTML файлы, которые содержат статистику
по запросам к PostgreSQL. Более подробно о возможностях можно найти
на официальном сайте проекта http://dalibo.github.io/pgbadger/.
pg_stat_statements
Pg_stat_statements — расширение для сбора статистики выполнения
запросов в рамках всего сервера. Преимущество данного расширения в
том, что ему не требуется собирать и парсить логи PostgreSQL, как это
делает pgFouine и pgBadger. Для начала установим и настроим его:
Листинг 2.22 Настройка pg_stat_statements в postgresql.conf
Line 1 s h ar e d_ p re l oa d _l i br a ri e s = ’ pg_stat_statements ’
- c u s t o m _ v a r i a b l e _ c lasses = ’ pg_stat_statements ’ # данная
настройка нужна для PostgreSQL 9 . 1 и ниже
-
- pg_stat_statements . max = 10000
5 pg_stat_statements . t r ac k = a l l
32
2.5. Оптимизация БД и приложения
После внесения этих параметров PostgreSQL потребуется перегрузить.
Параметры конфигурации pg_stat_statements:
1. pg_stat_statements.max (integer)» — максимальное количество sql за-
просов, которое будет храниться расширением (удаляются записи с
наименьшим количеством вызовов);
2. pg_stat_statements.track (enum)» — какие SQL запросы требуется за-
писывать. Возможные параметры: top (только запросы от приложе-
ния/клиента), all (все запросы, например в функциях) и none (от-
ключить сбор статистики);
3. pg_stat_statements.save (boolean)» — следует ли сохранять собранную
статистику после остановки PostgreSQL. По умолчанию включено;
Далее активируем расширение:
Листинг 2.23 Активация pg_stat_statements
Line 1 # CREATE EXTENSION pg_stat_statements ;
Пример собранной статистики:
Листинг 2.24 pg_stat_statements статистика
Line 1 # SELECT query , c a l l s , total_time , rows , 1 0 0 . 0 *
shared_blks_hit /
- n u l l i f ( shared_blks_hit + shared_blks_read , 0)
AS hi t _ percent
- FROM pg_stat_statements ORDER BY total _time DESC
LIMIT 1 0;
- - [ RECORD 1 ] - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
5 query | SELECT query , c a l l s , total_time , rows , ? *
shared_blks_hit /
- | n u l l i f ( shared_blks_hit +
shared_blks_read , ?) AS h i t _ p ercent
- | FROM pg_stat_statements ORDER BY
tot al_ti me DESC LIMIT ? ;
- c a l l s | 3
- tota l_tim e | 0.994
10 rows | 7
- h it_percen t | 100. 00000 00000000 000
- - [ RECORD 2 ] - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- query | i n s e r t i n t o x ( i ) s e l e c t g e n e r a t e _ s e r i e s ( ? , ? ) ;
- c a l l s | 2
33
2.5. Оптимизация БД и приложения
15 tota l_tim e | 0.591
- rows | 110
- h it_percen t | 100. 00000 00000000 000
- - [ RECORD 3 ] - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- query | s e l e c t * from x where i = ? ;
20 c a l l s | 2
- tota l_tim e | 0.157
- rows | 6
- h it_percen t | 100. 00000 00000000 000
- - [ RECORD 4 ] - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
25 query | SELECT pg_stat_statements_reset ( ) ;
- c a l l s | 1
- tota l_tim e | 0.102
- rows | 1
- h it_percen t |
Для сброса статистики есть команда pg_stat_statements_reset:
Листинг 2.25 Сброс статистики
Line 1 # SELECT pg_stat_statements_reset ( ) ;
- - [ RECORD 1 ] - - - - - - - - - - - - + -
- pg_stat_statements_reset |
-
5 # SELECT query , c a l l s , total_time , rows , 1 0 0 . 0 *
shared_blks_hit /
- n u l l i f ( shared_blks_hit + shared_blks_read , 0)
AS hi t _ percent
- FROM pg_stat_statements ORDER BY total _time DESC
LIMIT 1 0;
- - [ RECORD 1 ] - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- query | SELECT pg_stat_statements_reset ( ) ;
10 c a l l s | 1
- tota l_tim e | 0.175
- rows | 1
- h it_percen t |
Хочется сразу отметить, что расширение только с версии PostgreSQL
9.2 contrib нормализирует SQL запросы. В версиях 9.1 и ниже SQL запросы
сохраняются как есть, а значит «select * from table where id = 3» и «select
* from table where id = 21» буду разными записями, что почти бесполезно
для сбора полезной статистики.
34
2.6. Заключение
2.6 Заключение
К счастью, PostgreSQL не требует особо сложной настройки. В боль-
шинстве случаев вполне достаточно будет увеличить объём выделенной
памяти, настроить периодическое поддержание базы в порядке и прове-
рить наличие необходимых индексов. Более сложные вопросы можно об-
судить в специализированном списке рассылки.
35
3
Индексы
«Ну у вас и запросики»
сказала база данных и зависла
интернет
Что такое таблица в реляционной СУБД? Это такой список из корте-
жей (tuple). Каждый кортеж состоит из ячеек (row). Количество ячеек в
кортеже и их тип совпадают со схемой колонки, нескольких колонок. Этот
список имеет сквозную нумерацию RowId — порядковый номер. Таким об-
разом, таблицы можно осознавать как список пар (RowId, Кортеж).
Индексы — это обратные отношения (Кортеж, RowId). Кортеж обя-
зан содержать хотя бы одну ячейку (т. е. быть построенным минимум по
одной колонке). Для индексов, которые индексируют более одной колон-
ки — они ещё называются составными, и участвуют в отношениях вида
«многие-ко-многим» — всё написанное верно в равной степени. Очевидно,
если кортеж — не уникален (в колонке существует два одинаковых кор-
тежа), то эти отношения выглядят как (Кортеж, Список RowId) — т. е.
кортежу сопоставляется список RowId.
Индексы могут использоваться для таких операций в базе данных:
∙ Поиск данных — абсолютно все индексы поддерживают поиск зна-
чений по равенству. А B-Tree — по произвольным диапазонам;
∙ Like — B-Tree и Bitmap индексы можно использовать для ускорения
префиксных Like-предикатов (вида abc%);
∙ Оптимизатор — B-Tree и R-Tree индексы представляют из себя ги-
стограмму произвольной точности;
∙ Join — индексы могут быть использованы для Merge, Index алгорит-
мов;
∙ Relation — индексы могут быть использованы для операций
except/intersect;
∙ Aggregations — индексы позволяют эффективно вычислять неко-
торые агрегатные функции — COUNT, MIN, MAX, а также их
DISTINCT версии;
36
3.1. Типы индексов
∙ Grouping — индексы позволяют эффективно вычислять группировки
и произвольные агрегатные функции (sort-group алгоритм);
3.1 Типы индексов
В зависимости от структуры, используемой в реализации индексов, су-
щественно различаются поддерживаемые операции, их стоимости, а также
свойства читаемых данных. Давайте рассмотрим какие существуют типы
индексов в PostgreSQL.
B-Tree
B-Tree (Boeing/Bayer/Balanced/Broad/Bushy-Tree) называют упорядо-
ченное блочное дерево. Узлы в дереве представляют из себя блоки фикси-
рованного размера. У каждого узла фиксированное число детей. Струк-
тура B-Tree представлена на рисунке 3.1.
Рис. 3.1: B-Tree индекс
B-Tree для индексов отличается от представленной на Википедии —
есть дублированные данных в промежуточных блоках. Для i-ой записи в
блоке сохраняется не значение, которое больше максимума i-го поддерева,
и меньше минимума (i+1) поддерева, а максимум i-го поддерева. Различия
проистекают из того, что википедия приводит пример B-Tree для множе-
ства, а нам нужен ассоциативный массив.
В индексном B-Tree значения и RowId размещаются совместно на ниж-
нем слое дерева. Каждый узел дерева представляет из себя одну страницу
(page) в некотором формате. В начале страницы всегда идёт некоторый за-
головок. Для корневого и промежуточного узла в страницах хранятся па-
ры (Значение, Номер страницы). Для листовых — пары (Значение ,RowId)
либо (Значение, Список RowId) (в зависимости от свойств значения — уни-
кально или нет). B-Tree деревья имеют крайне маленькую высоту — по-
37
3.1. Типы индексов
рядка 𝐻 = log
𝑚
𝑁, где m — количество записей в блоке, N — количество
элементов. B-Tree деревья являются упорядоченными — все элементы в
любой странице (блоке) дерева лежат последовательно. Предыдущие два
свойства позволяют крайне эффективно производить поиск — начиная с
первой страницы, половинным делением (binary search) выделяются де-
ти, в которых лежат границы поиска. Таким образом, прочитав всего H,
2H страниц мы находим искомый диапазон. Важным нюансом является
также факт, что страницы в листьях связаны в односвязный либо дву-
связный список - это означает, что, выполнив поиск, мы можем дальше
просто последовательно читать страницы, и эффективность чтения боль-
шего объёма данных (длинного диапазона) сравнима с эффективностью
чтения данных из таблицы.
Сильные стороны B-Tree индексов:
∙ сохраняют сортированность данных;
∙ поддерживают поиск по унарным и бинарным предикатам (<a; = b
; >c and <d; <e and >f) за O(log
𝑚
𝑁), где m — количество записей в
блоке, N — количество элементов;
∙ позволяют не сканируя последовательность данных целиком оценить
cardinality (количество записей) для всего индекса (а следовательно
таблицы), диапазона, причём с произвольной точностью. Посмотре-
ли корневую страницу — получили одну точность. Посмотрели сле-
дующий уровень дерева — получили точность получше. Просмотрели
дерево до корня — получили точное число записей;
∙ самобалансируемый, для внесения изменения не требуется полного
перестроения, происходит не более O(log
𝑚
𝑁) действий, где m — ко-
личество записей в блоке, N — количество элементов;
Слабые стороны B-Tree индексов:
∙ занимают много места на диске. Индекс по уникальным Integer-ам,
к примеру, весит в два раза больше аналогичной колонки (т.к. хра-
нятся ещё и RowId);
∙ при постоянной записи дерево начинает хранить данные разреженно
(сразу после построения они могут лежать очень плотно), и время
доступа увеличивается за счёт увеличения объёма дисковой инфор-
мации. Поэтому B-Tree индексы требуют присмотра и периодическо-
го перепостроения (REBUILD);
R-Tree
R-Tree (Rectangle-Tree) предназначен для хранения пар (X, Y) значе-
ний числового типа (например, координат). По способу организации R-
Tree очень похоже на B-Tree. Единственное отличие — это информация,
записываемая в промежуточные страницы в дереве. Для i-го значения в
38
3.1. Типы индексов
узле в B-Tree мы пишем максимум из i-го поддерева, а в R-Tree — мини-
мальный прямоугольник, покрывающий все прямоугольники из ребёнка.
Подробней можно увидеть на рисунке 3.2.
Рис. 3.2: R-Tree индекс
Сильные стороны:
∙ поиск произвольных регионов, точек за O(log
𝑚
𝑁), где m — количе-
ство записей в блоке, N — количество элементов;
∙ позволяет оценить количество точек в некотором регионе без полного
сканирования данных;
Слабые стороны:
∙ существенная избыточность в хранении данных;
∙ медленное обновление данных;
В целом, плюсы-минусы очень напоминают B-Tree.
39
3.1. Типы индексов
Hash индекс
Hash индекс по сути является ассоциативным хеш-контейнером. Хеш-
контейнер — это массив из разряженных значений. Адресуются отдель-
ные элементы этого массива некоторой хеш-функцией которая отобража-
ет каждое значение в некоторое целое число. Т. е. результат хеш-функции
является порядковым номером элемента в массиве. Элементы массива в
хеш-контейнере называются бакетами (bucket). Обычно один бакет — од-
на страница. Хеш-функция отображает более мощное множество в менее
мощное, возникают так называемые коллизии — ситуация, когда одно-
му значению хеш-функции соответствует несколько разных значений. В
бакете хранятся значения, образующие коллизию. Разрешение коллизий
происходит посредством поиска среди значений, сохранённых в бакете.
Рис. 3.3: Hash индекс
Сильные стороны:
∙ очень быстрый поиск O(1);
∙ стабильность — индекс не нужно перестраивать;
Слабые стороны:
∙ хеш очень чувствителен к коллизиям хеш-функции. В случае «пло-
хого» распределения данных, большинство записей будет сосредото-
чено в нескольких бакетах, и фактически поиск будет происходить
путем разрешения коллизий;
40
3.1. Типы индексов
∙ из-за нелинейности хэш-функций данный индекс нельзя сортировать
по значению, что приводит к невозможности использования в срав-
нениях больше/меньше и «IS NULL»;
∙ данный индекс в PostgreSQL транзакционно небезопасен, нужно
перестраивать после краха и не реплицируется через потоковую
(streaming) репликацию (разработчики обещают это исправить к 10
версии);
Битовый индекс (bitmap index)
Битовый индекс (bitmap index) — метод битовых индексов заключа-
ется в создании отдельных битовых карт (последовательность 0 и 1) для
каждого возможного значения столбца, где каждому биту соответствует
строка с индексируемым значением, а его значение равное 1 означает, что
запись, соответствующая позиции бита содержит индексируемое значение
для данного столбца или свойства (алгоритм Хаффмана).
Рис. 3.4: Битовый индекс
Сильные стороны:
∙ компактность представления (занимает мало места);
∙ быстрое чтение и поиск по предикату «равно»;
Слабые стороны:
∙ невозможность изменить способ кодирования значений в процессе
обновления данных;
41
3.1. Типы индексов
У PostgreSQL нет возможности создать постоянный битовый индекс,
но база может на лету создавать данные индексы для объединения разных
индексов. Чтобы объединить несколько индексов, база сканирует каждый
необходимый индекс и готовит битовую карту в памяти с расположением
строк таблицы. Битовые карты затем обрабатываются AND/OR опера-
цией по мере требования запроса и после этого выбираются колонки с
данными.
GiST индекс
GiST (Generalized Search Tree) — обобщение B-Tree, R-Tree дерево по-
иска по произвольному предикату. Структура дерева не меняется, по-
прежнему в каждом нелистовом узле хранятся пары (Значения, Номер
страницы), а количество детей совпадает с количеством пар в узле. Суще-
ственное отличие состоит в организации ключа. B-Tree деревья заточены
под поиск диапазонов и хранят максимумы поддерева-ребёнка. R-Tree —
региона на координатной плоскости. GiST предлагает в качестве значе-
ний в нелистовых узлах хранить ту информацию, которую мы считаем
существенной, и которая позволит определить, есть ли интересующие нас
значения (удовлетворяющие предикату) в поддереве-ребёнке. Конкретный
вид хранимой информации зависит от вида поиска, который мы желаем
проводить. Таким образом параметризовав R-Tree и B-Tree дерево преди-
катами и значениями мы автоматически получаем специализированный
под задачу индекс (PostGiST, pg_trgm, hstore, ltree, прочее).
Сильные стороны:
∙ эффективный поиск;
Слабые стороны:
∙ большая избыточность;
∙ необходимость специализированной реализации под каждую группу
запросов;
Остальные плюсы-минусы совпадают с B-Tree и R-Tree индексами.
GIN индекс
GIN (Generalized Inverted Index) — обратный индекс, используемым
полнотекстовым поиском PostgreSQL. Это означает, что в структуре ин-
дексов с каждой лексемой сопоставляется отсортированный список номе-
ров документов, в которых она встречается. Очевидно, что поиск по та-
кой структуре намного эффективнее, чем при использовании GiST, однако
процесс добавления нового документа достаточно длителен.
42
3.1. Типы индексов
Cluster индекс
Не является индексом, поскольку производит кластеризацию табли-
цы по заданному индексу. Более подробно можно почитать в разделе
«2.3 CLUSTER».
BRIN индекс
Версия PostgreSQL 9.5 привнесла с собой новый вид индексов — BRIN
(Block Range Index, или индекс блоковых зон).
Рис. 3.5: BRIN индекс
В отличие от привычного B-Tree, этот индекс намного эффективнее
для очень больших таблиц, и в некоторых ситуациях позволяет заменить
собой партицирование (подробно можно почитать в разделе «4 Партицио-
нирование»). BRIN-индекс имеет смысл применять для таблиц, в которых
часть данных уже по своей природе как-то отсортирована. Например, это
характерно для логов или для истории заказов магазина, которые пишут-
ся последовательно, а потому уже на физическом уровне упорядочены по
дате/номеру, и в то же время таблицы с такими данными обычно разрас-
таются до гигантских размеров.
Под блоковой зоной (Block Range) подразумевается набор страниц, фи-
зически расположенных по соседству в таблице. Для каждой такой зоны
создается некий идентификатор, отвечающий за «место» этой зоны в таб-
лице. Для лога это может быть дата создания записи. Поиск по такому
индексу осуществляется с потерями информации, то есть выбираются все
записи, входящие в блоковые зоны с идентификаторами, соответствующи-
ми запросу, но среди записей в этих зонах могут попадаться такие, которые
43
3.2. Возможности индексов
на следующем этапе надо будет отфильтровать. Размер индекса при этом
очень маленький, и он почти не нагружает базу. Размер индекса обратно
пропорционален параметру pages_per_range, отвечающему за количество
страниц на зону. В то же время, чем меньше размер зоны, тем меньше
«лишних» данных попадёт в результат поиска (надо подходить к этому
параметру с умом).
Индексы BRIN могут иметь один из нескольких встроенных классов
операторов, по которым будет осуществляться разбивка на зоны и при-
своение идентификаторов. Например, int8_minmax_ops применяется для
операций сравнения целых чисел, а date_minmax_ops для сравнения дат.
3.2 Возможности индексов
Функциональный индекс (functional index)
Вы можете построить индекс не только по полю/нескольким полям
таблицы, но и по выражению, зависящему от полей. Пусть, например,
в вашей таблице foo есть поле foo_name, и выборки часто делаются по
условию «первая буква из поля foo_name в любом регистре». Вы можете
создать индекс
Листинг 3.1 Индекс
Line 1 CREATE INDEX foo_name_first_idx ON f o o ( ( lower ( s u bs t r (
foo_name , 1 , 1) ) ) ) ;
и запрос вида
Листинг 3.2 Запрос
Line 1 SELECT * FROM f o o WHERE low e r ( s ub s tr ( foo_name , 1 , 1) ) = ’а ’ ;
будет его использовать.
Частичный индекс (partial index)
Под частичным индексом понимается индекс с предикатом WHERE.
Пусть, например, у вас есть в базе таблица scheta с параметром uplocheno
типа boolean. Записей, где uplocheno = false меньше, чем записей с uplocheno
= true, а запросы по ним выполняются значительно чаще. Вы можете
создать индекс
Листинг 3.3 Индекс
Line 1 CREATE INDEX scheta_neuplocheno ON s c he ta ( i d ) WHERE NOT
uplocheno ;
44
3.2. Возможности индексов
который будет использоваться запросом вида
Листинг 3.4 Запрос
Line 1 SELECT * FROM s c h et a WHERE NOT uplocheno AND . . . ;
Достоинство подхода в том, что записи, не удовлетворяющие условию
WHERE, просто не попадут в индекс.
Уникальный индекс (unique index)
Уникальный индекс гарантирует, что таблица не будет иметь более
чем одну строку с тем же значением. Это удобно по двум причинам: це-
лостность данных и производительность. Поиск данных с использованием
уникального индекса, как правило, очень быстрый.
Индекс нескольких столбцов (multi-column index)
В PostgreSQL возможно создавать индексы на несколько столбцов, но
нам главное нужно понять когда имеет смысл создавать такой индекс,
поскольку планировщик запросов PostgreSQL может комбинировать и ис-
пользовать несколько индексов в запросе путем создания битового индекса
(«3.1 Битовый индекс (bitmap index)»). Можно, конечно, создать индек-
сы, которые охватят все возможные запросы, но за это придется платить
производительностью (индексы нужно перестраивать при запросах на мо-
дификацию данных). Нужно также помнить, что индексы на несколько
столбцов могут использоваться только запросами, которые ссылаются на
эти столбцы в индексе в том же порядке. Индекс по столбцам (a, b) мо-
жет быть использован в запросах, которые содержат a = x and b = y или
a = x, но не будет использоваться в запросе вида b = y. Если это подходит
под запросы вашего приложения, то данный индекс может быть полезен.
В таком случае создание индекса на поле a было бы излишним. Индекс
нескольких столбцов с указанием уникальности (unique) может быть так-
же полезен для сохранения целостности данных (т. е. когда набор данных
в этих стобцах должен быть уникальным).
45
4
Партиционирование
Решая какую-либо проблему,
всегда полезно заранее знать
правильный ответ. При
условии, конечно, что вы
уверены в наличии самой
проблемы
Народная мудрость
4.1 Введение
Партиционирование (partitioning, секционирование) — это разбиение
больших структур баз данных (таблицы, индексы) на меньшие кусочки.
Звучит сложно, но на практике все просто.
Скорее всего у Вас есть несколько огромных таблиц (обычно всю на-
грузку обеспечивают всего несколько таблиц СУБД из всех имеющихся).
Причем чтение в большинстве случаев приходится только на самую по-
следнюю их часть (т. е. активно читаются те данные, которые недавно
появились). Примером тому может служить блог — на первую страницу
(это последние 5. . . 10 постов) приходится 40. . . 50% всей нагрузки, или
новостной портал (суть одна и та же), или системы личных сообщений,
впрочем понятно. Партиционирование таблицы позволяет базе данных де-
лать интеллектуальную выборку — сначала СУБД уточнит, какой парти-
ции соответствует Ваш запрос (если это реально) и только потом сделает
этот запрос, применительно к нужной партиции (или нескольким парти-
циям). Таким образом, в рассмотренном случае, Вы распределите нагруз-
ку на таблицу по ее партициям. Следовательно выборка типа SELECT *
FROM articles ORDER BY id DESC LIMIT 10 будет выполняться только над
последней партицией, которая значительно меньше всей таблицы.
Итак, партиционирование дает ряд преимуществ:
46
4.2. Теория
∙ На определенные виды запросов (которые, в свою очередь, создают
основную нагрузку на СУБД) мы можем улучшить производитель-
ность;
∙ Массовое удаление может быть произведено путем удаления одной
или нескольких партиций (DROP TABLE гораздо быстрее, чем мас-
совый DELETE);
∙ Редко используемые данные могут быть перенесены в другое храни-
лище;
4.2 Теория
На текущий момент PostgreSQL поддерживает два критерия для со-
здания партиций:
∙ Партиционирование по диапазону значений (range) — таблица разби-
вается на «диапазоны» значений по полю или набору полей в табли-
це, без перекрытия диапазонов значений, отнесенных к различным
партициям. Например, диапазоны дат;
∙ Партиционирование по списку значений (list) — таблица разбивается
по спискам ключевых значений для каждой партиции.
Чтобы настроить партиционирование таблицы, достаточно выполнить
следующие действия:
∙ Создается «мастер» таблица, из которой все партиции будут насле-
доваться. Эта таблица не будет содержать данные. Также не нужно
ставить никаких ограничений на таблицу, если конечно они не будут
дублироваться на партиции;
∙ Создайте несколько «дочерних» таблиц, которые наследуют от «ма-
стер» таблицы;
∙ Добавить в «дочерние» таблицы значения, по которым они будут
партициями. Стоить заметить, что значения партиций не должны
пересекаться. Например:
Листинг 4.1 Пример неверного задания значений партиций
Line 1 CHECK ( o u tlet I D BETWEEN 100 AND 200 )
- CHECK ( o u tl etI D BETWEEN 200 AND 300 )
неверно заданы партиции, поскольку непонятно какой партиции при-
надлежит значение 200;
∙ Для каждой партиции создать индекс по ключевому полю (или
нескольким), а также указать любые другие требуемые индексы;
∙ При необходимости, создать триггер или правило для перенаправле-
ния данных с «мастер» таблицы в соответствующую партицию;
47
4.3. Практика использования
∙ Убедиться, что параметр constraint_exclusion не отключен в
postgresql.conf. Если его не включить, то запросы не будут оптими-
зированы при работе с партиционированием;
4.3 Практика использования
Теперь начнем с практического примера. Представим, что в нашей си-
стеме есть таблица, в которую мы собираем данные о посещаемости нашего
ресурса. На любой запрос пользователя наша система логирует действия
в эту таблицу. И, например, в начале каждого месяца (неделю) нам нуж-
но создавать отчет за предыдущий месяц (неделю). При этом логи нужно
хранить в течение 3 лет. Данные в такой таблице накапливаются быстро,
если система активно используется. И вот, когда в таблице уже миллионы,
а то и миллиарды записей, создавать отчеты становится все сложнее (да и
чистка старых записей становится нелегким делом). Работа с такой табли-
цей создает огромную нагрузку на СУБД. Тут нам на помощь и приходит
партиционирование.
Настройка
Для примера, мы имеем следующую таблицу:
Листинг 4.2 «Мастер» таблица
Line 1 CREATE TABLE my_logs (
- id SERIAL PRIMARY KEY,
- user_id INT NOT NULL,
- l o gd a te TIMESTAMP NOT NULL,
5 data TEXT,
- some_state INT
- ) ;
Поскольку нам нужны отчеты каждый месяц, мы будем делить пар-
тиции по месяцам. Это поможет нам быстрее создавать отчеты и чистить
старые данные.
«Мастер» таблица будет my_logs, структуру которой мы указали выше.
Далее создадим «дочерние» таблицы (партиции):
Листинг 4.3 «Дочерние» таблицы
Line 1 CREATE TABLE my_logs2010m10 (
- CHECK ( l og d ate >= DATE ’ 2010 -10 -01 ’ AND