PostgreSQL: аналитика для DBA
Многие пользователи СУБД PostgreSQL знают, что сервер во время своей работы собирает разнообразную статистику, но не все знают, что ее полезно анализировать и как ее извлекать для этого. В этом небольшом тулките собраны несколько полезных запросов, дающих некоторое представление о том, как использовать это «скрытое знание», которое постоянно копится. Эти запросы можно использовать для мониторинга состояния PostgreSQL (ручного или с помощью плагинов для систем мониторинга вроде Nagios, Cacti или Zabbix), для поиска узких мест в работе сервера и многих других подобных задач. Помните, что это лишь верхушка айсберга; в документации можно найти описания нескольких десятков системных представлений, которые также могут быть полезны администратору PostgreSQL.
Для корректной работы тулкита необходимо включить опции stats_block_level и stats_row_level в postgresql.conf, а также настроить параметр stats_reset_on_server_start по своему усмотрению. Если при каждом перезапуске сервера PostgreSQL вы меняете какие-то существенные параметры его конфигурации, имеет смысл обнулять статистику, чтобы отслеживать эффект внесенных изменений. Если же вас интересует долгосрочная перспектива и рестарт производится не вследствие изменения конфигурации PostgreSQL, ставьте параметр stats_reset_on_server_start в значение off.
Отношение hit / read
При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет — делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.
SELECT datname, CASE WHEN blks_read = 0 THEN 0 ELSE blks_hit / blks_read END AS ratio FROM pg_stat_database;
Количество модификаций, произошедших в таблице
Список по таблицам: какое количество записей в них было добавлено, изменено и удалено с момента последнего сброса статистики. Администратор БД должен представлять, какие таблицы являются самыми нагруженными в текущей базе данных, а также каково соотношение между различными типами модифицирующих запросов к ним.
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables ORDER BY n_tup_upd DESC;
Статистика seq scan / index scan
Список по таблицам: какое количество запросов к ним было выполнено посредством последовательного просмотра; какое количество запросов было выполнено с использованием индексов; а также отношение этих двух чисел. Позволяет оценить, все ли нужные индексы созданы в данной таблице. Если ваши таблицы содержат более нескольких тысяч рядов, последовательный просмотр будет выполняться медленнее просмотра индекса, поэтому в идеальном случае seqscan-ов в таких таблицах быть не должно. Если у вас они все же есть, анализируйте запросы к таким таблицам и создавайте соответствующие индексы. При этом важно не перестараться: чем больше индексов по колонкам таблицы, тем дороже становятся операции обновления данных.
Также не забывайте, что после создания индекса таблице нужно делать ANALYZE, иначе планировщик запросов не заметит изменений в структуре таблицы.
SELECT relname, seq_scan, idx_scan, CASE WHEN idx_scan = 0 THEN 100 ELSE seq_scan / idx_scan END AS ratio FROM pg_stat_user_tables ORDER BY ratio DESC;
Статистика по индексам
Список по индексам: сколько записей из индекса были возвращены в запросах по этому индексу; сколько рядов при этом все же пришлось просматривать в родительской таблице; отношение этих двух чисел. Суть данной статистики проста: если у вас много fetch-ей, значит индекс устарел и при выполнении запроса PostgreSQL вынужден смотреть непосредственно в таблицу, как в источник самых актуальных данных, что замедляет его работу. В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.
SELECT indexrelname, idx_tup_read, idx_tup_fetch, CASE WHEN idx_tup_fetch = 0 THEN 100 ELSE idx_tup_read / idx_tup_fetch END AS ratio FROM pg_stat_user_indexes ORDER BY ratio DESC;
Выполняющиеся запросы с их продолжительностью
Простой список выполняющихся сервером в настоящий момент запросов. Бывает полезен, когда вы недостаточно хорошо знаете систему или просто не успели ее настроить — с его помощью можно найти и прервать «плохой» запрос, который мешает работе сервера (колонка procpid содержит PID процесса, которому можно сделать kill при необходимости). Помните, однако, что простой периодический просмотр выполняющхся запросов ни в коем случае не заменит замечательный анализатор логов pgFouine. Также не стоит забывать, что процесс, в котором вы выполняете данный запрос, тоже попадает в результирующий список.
SELECT datname, NOW() - query_start AS duration, procpid, current_query FROM pg_stat_activity ORDER BY duration DESC;
Список текущих блокировок
Список текущих блокировок с указанием типа блокировки, таблицы и базы данных, на которой она выставлена и номера транзакции, которая выставила блокировку. Не стоит пугаться, если запрос выдает длинный список lock-ов — не все они являются критическими и блокирующими таблицу от всех возможных изменений и даже чтения. Для анализа списка блокировок обязательно стоит ознакомиться с документацией об их типах в PostgreSQL и о том, когда и какими запросами они выставляются. Один из распространенных случаев, когда список блокировок может пригодиться: выполняя команду ps aux | grep ^postgres вы видите, что в статусе одного из процессов postgres-а написано waiting — это как раз и означает, что данный процесс ждет, когда будет снята нужная ему блокировка. Какая именно — выясняйте, запустив данный запрос.
SELECT l.mode, d.datname, c.relname, l.granted, l.transactionid FROM pg_locks AS l LEFT JOIN pg_database AS d ON l.database= d.oid LEFT JOIN pg_class AS c ON l.relation = c.oid;
Dba postgresql что это
Для создания базы данных сервер PostgreSQL должен быть развёрнут и запущен (см. Раздел 17.3).
База данных создаётся SQL-командой CREATE DATABASE :
CREATE DATABASE имя;
где имя подчиняется правилам именования идентификаторов SQL . Текущий пользователь автоматически назначается владельцем. Владелец может удалить свою базу, что также приведёт к удалению всех её объектов, в том числе, имеющих других владельцев.
Создание баз данных это привилегированная операция. Как предоставить права доступа, описано в Разделе 20.2.
Поскольку для выполнения команды CREATE DATABASE необходимо подключение к серверу базы данных, возникает вопрос как создать самую первую базу данных. Первая база данных всегда создаётся командой initdb при инициализации пространства хранения данных (см. Раздел 17.2.) Эта база данных называется postgres . Далее для создания первой « обычной » базы данных можно подключиться к postgres .
Вторая база данных template1 , также создаётся во время инициализации кластера. При каждом создании новой базы данных в рамках кластера по факту производится клонирование шаблона template1 . При этом любые изменения сделанные в template1 распространяются на все созданные впоследствии базы данных. Следует избегать создания объектов в template1 , за исключением ситуации, когда их необходимо автоматически добавлять в новые базы. Более подробно в Разделе 21.3.
Для удобства, есть утилита командной строки для создания баз данных, createdb .
createdb dbname
Утилита createdb не делает ничего волшебного, она просто подключается к базе данных postgres и выполняет ранее описанную SQL-команду CREATE DATABASE . Подробнее о её вызове можно узнать в createdb . Обратите внимание, что команда createdb без параметров создаст базу данных с именем текущего пользователя.
Примечание
Глава 19 содержит информацию о том, как ограничить права на подключение к заданной базе данных.
Иногда необходимо создать базу данных для другого пользователя и назначить его владельцем, чтобы он мог конфигурировать и управлять ею. Для этого используйте одну из следующих команд:
CREATE DATABASEимя_базыOWNERимя_роли;
из среды SQL, или:
createdb -Oимя_ролиимя_базы
из командной строки ОС. Лишь суперпользователь может создавать базы данных для других (для ролей, членом которых он не является).
| Пред. | Наверх | След. |
| 21.1. Обзор | Начало | 21.3. Шаблоны баз данных |
База данных PostgreSQL: что это такое
PostgreSQL — это одна из самых продвинутых систем управления базами данных (СУБД). Ее относят к классу объектно-реляционных систем. Это значит, что PostgreSQL, в отличие от, например, другой популярной СУБД MySQL, поддерживает пользовательские объекты и их поведение.
Что важно знать о СУБД Postgres:
· поддерживает ссылочную и транзакционную целостность сохраненной информации;
· имеет множество дополнительных функций, которые сохраняют только корректные data-файлы;
· обладает расширенной емкостью за счет внутренней структуры;
· соответствует стандарту ANSI/ISO SQL:2008.
Postgres — гибкая и надежная СУБД, которая может работать со сложными структурами и типами данных: многомерными массивами, json, uuid, битовыми строками, сетевыми адресами.
В PostgreSQL можно пользоваться встроенными типами данных — битовыми строками, двоичными данными, интервалом времени — или создавать свои:
Размер базы, как и количество строк и индексов в таблице, не ограничены. Максимальный размер таблицы может составлять до 32 TB.
Установка PostgreSQL
Установочные файлы Postgres можно найти на официальном сайте продукта. Там же доступна сопроводительная информация о версиях и правках, а также различные руководства.
Установить PostgreSQL в Ubuntu можно с помощью команды apt: программа уже включена в репозиторий, поэтому для инсталляции достаточно заранее проверить наличие обновлений системы и выполнить команду. Помимо основных файлов установится дополнительный пакет с полезными функциями и утилитами.
При установке можно воспользоваться возможностями менеджеров пакетов. Например, для MAC OSподойдет сервис Homebrew или установочный комплект приложений Postgresapp.
Кроме того, нужно организовать место для размещения серверной части. Иногда для этих целей используют хостинги. Если приложения размещаются на отдельном от Postgres сервере, важно разрешить подключения извне в настройках.
Настройка PostgreSQL
Базовые настройки хранятся в файлах:
· postgresql.conf с настройками самой базы;
· pg_hba.conf с настройками доступа.
Редактировать файлы можно в любом текстовом редакторе, а после изменения перезапустить систему.
Встроенная утилита psql открывает доступ к терминалу для работы СУБД, через который можно в том числе уведомлять о необходимости перечитать параметры настроек. Через терминал можно вызвать службу поддержки или справочную информацию и вообще полноценно работать с СУБД, без дополнительных сервисов.
На какие настройки памяти важно обратить внимание:
· максимальное количество подключенных пользователей;
· объем буфера, в который загружаются страницы для считывания и записи;
· объем памяти для каждого подключения, которое может регулироваться в процессе работы.
Для устранения фрагментации памяти и высокого system time можно изменить параметры минимального количества свободной памяти и свопа.
Управление пользователями
PostgreSQL database распределяет доступы не по пользователям, а по ролям. Каждая роль наделяется правами, может владеть в базе своими объектами, разграничивать другие права. Создавать новые роли можно в терминале psql или в рабочей консоли через оператора SQL. Функция «создание пользователей» доступна в режиме суперпользователя.
По тому же принципу можно запросить список ролей, изменить пароли или удалить пользователей. Информация обо всех подобных операциях сохраняется в файлах psql_history: для соблюдения требований безопасности эти файлы лучше удалять, особенно если они связаны с заменой паролей.
Работа с базами данных в PostgreSQL
В Postgres используется понятие кластер базы данных — набор баз, управляемых одним экземпляром работающего сервера. Создавать, просматривать, удалять эти базы можно через консоль или через терминал.
Перед созданием database нужно проверить настройки времени и даты в ОС, так как они будут отображаться в базе. Путь к базе задан по умолчанию. Если под нее выделен отдельный диск, путь задается вручную.
Резервные копии создаются сложной командой, в которой прописываются:
· сервер, на котором хранится БД;
· имя пользователя, который совершает действие;
· формат, в котором содержимое базы сохранится на резервном носителе;
· путь сохранения резервной копии;
· название БД, с которой будет сделана копия.
Все те же параметры указываются в команде восстановления.
Подключение к базе данных
При подключении к database через терминал нужно учитывать параметры входа. Если есть несоответствия в именах базы, пользователя и роли, в командной строке нужно будет указать соответствующий параметр.
Взаимодействовать с базой можно также через оператора SQL или через интуитивно понятное приложение pgAdmin. Подойдут также любые другие сервисы, которые поддерживают ODBC и JDBC— это удобно, если разрабатывается приложение с соответствующим драйвером.
PostgreSQL и облачные платформы
При конфигурации сервера для Постгри SQL обычно используют виртуализацию. Если система устанавливается локально, для нее потребуется отдельный сервер.
Postgres сохраняет данные даже при аварийных отключениях виртуальных серверов. После включения система без проблем загружается и продолжает работу с последнего checkpoint. Это особенно важно, когда речь идет о критических для бизнеса приложениях — именно поэтому PostgreSQL часто становится основой для решений уровня enterprise. Эту чаще всего СУБД выбирают для обработки сложных запросов и поддержки массивных баз данных.
На базе Platform V, облачной платформы для разработки бизнес-приложений, доступно решение Platform V Pangolin. Это продукт, в основе которого лежит версия PostgreSQL, содержащая собственные разработки и инновации Сбера. В отличие от open source версии СУБД, в Platform VPangolin усилены критические для бизнеса функциональные особенности:
Platform V Pangolin подходит для компаний и корпораций, в которых
· необходимо работать с большим объемом структурированными данных;
· приняты высокие стандарты безопасности и надежности;
· создаются приложения с высокой пропускной способностью и доступом к базе.
Platform V Pangolin обеспечивает надежную со сложными структурами данных, скорость и отказоустойчивость — качества, которые сделали Postgres одной из наиболее популярных СУБД в мире. Сегодня Platform V Pangolin используется в 188 системах Сбера. Это полностью независимая база данных, которая стала для банка полноценной заменой решениям иностранных вендоров.
Pangolin SE
Реляционная СУБД уровня enterprise, специальная сборка PostgreSQL
DBA1
Перед началом самостоятельного прохождения курса ознакомьтесь с руководством слушателя. В нем вы найдете инструкцию по настройке рабочего места и указания по выполнению практических заданий.
Темы
| Введение | учебные материалы | видео | |
| Базовый инструментарий | |||
| 1. | Установка и управление сервером | учебные материалы | видео |
| 2. | Использование psql | учебные материалы | видео |
| 3. | Конфигурирование | учебные материалы | видео |
| Архитектура | |||
| 4. | Общее устройство PostgreSQL | учебные материалы | видео |
| 5. | Изоляция и многоверсионность | учебные материалы | видео |
| 6. | Очистка | учебные материалы | видео |
| 7. | Буферный кеш и журнал | учебные материалы | видео |
| Организация данных | |||
| 8. | Базы данных и схемы | учебные материалы | видео |
| 9. | Системный каталог | учебные материалы | видео |
| 10. | Табличные пространства | учебные материалы | видео |
| 11. | Низкий уровень | учебные материалы | видео |
| Задачи администрирования | |||
| 12. | Мониторинг | учебные материалы | видео |
| Управление доступом | |||
| 13. | Роли и атрибуты | учебные материалы | видео |
| 14. | Привилегии | учебные материалы | видео |
| 15. | Политики защиты строк | учебные материалы | видео |
| 16. | Подключение и аутентификация | учебные материалы | видео |
| Резервное копирование | |||
| 17. | Обзор | учебные материалы | видео |
| Репликация | |||
| 18. | Обзор | учебные материалы | видео |
Дополнительно
- учтены нововведения версий PostgreSQL 11, 12 и 13,
- вместо сборки из исходных кодов используется установка из пакета,
- увеличен объем практических заданий,
- тема «Сопровождение» перенесена в модуль «Архитектура» и посвящена исключительно очистке.
Версия курса по PostgreSQL 10
Администрирование PostgreSQL 10. Базовый курс
Основные сведения
Продолжительность: 3 дня
- минимальные сведения о базах данных и SQL,
- знакомство с Unix.
- общие сведения об архитектуре PostgreSQL,
- установка, базовая настройка, управление сервером,
- организация данных на логическом и физическом уровнях,
- базовые задачи администрирования,
- управление пользователями и доступом,
- представление о резервном копировании и репликации.
Руководство слушателя
Перед началом самостоятельного прохождения курса ознакомьтесь с руководством слушателя. В нем вы найдете инструкцию по настройке рабочего места и указания по выполнению практических заданий.
Темы
| Введение | слайды | видео | |||
| Базовый инструментарий |
|||||
| 1. | Установка и управление сервером | слайды | демо | ответы | видео |
| 2. | Использование psql | слайды | демо | ответы | видео |
| 3. | Конфигурирование | слайды | демо | ответы | видео |
| Архитектура | |||||
| 4. | Общее устройство PostgreSQL | слайды | видео | ||
| 5. | Изоляция и многоверсионность | слайды | демо | ответы | видео |
| 6. | Буферный кэш и журнал | слайды | демо | ответы | видео |
| Организация данных | |||||
| 7. | Базы данных и схемы | слайды | демо | ответы | видео |
| 8. | Системный каталог | слайды | демо | ответы | видео |
| 9. | Табличные пространства | слайды | демо | ответы | видео |
| 10. | Низкий уровень | слайды | демо | ответы | видео |
| Задачи администрирования | |||||
| 11. | Мониторинг | слайды | демо | ответы | видео |
| 12. | Сопровождение | слайды | демо | ответы | видео |
| Управление доступом | |||||
| 13. | Роли и атрибуты | слайды | демо | ответы | видео |
| 14. | Привилегии | слайды | демо | ответы | видео |
| 15. | Политики защиты строк | слайды | демо | ответы | видео |
| 16. | Подключение и аутентификация | слайды | демо | ответы | видео |
| Резервное копирование | |||||
| 17. | Обзор | слайды | демо | ответы | видео |
| Репликация | |||||
| 18. | Обзор | слайды | демо | ответы | видео |
Дополнительно
Видеозапись курса проводилась с 27 февраля по 1 марта 2018 года в Нижнем Новгороде.
- учтены нововведения версий PostgreSQL 9.5, 9.6 и 10,
- материал по-новому структурирован и переупорядочен,
- архитектура PostgreSQL рассматривается более подробно,
- подробное изучение резервного копирования заменено на обзор,
- добавлены темы по политикам защиты строк и репликации.