Postgresql для чего нужна эта программа

Содержание

Postgresql для чего нужна эта программа

В работе любой программист или системный администратор встречается со страшной проблемой — тормозит база. Сервер работает медленно, происходит нечто непонятное, пользователи жалуются, клиент ругается. Я не зря написал, что проблема является страшной, в первую очередь потому, что решение здесь будет неочевидное и лежит оно не на поверхности. На производительность базы может влиять множество параметров.

Читайте также:

Зум конференция что за программа

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

Por que PostgreSQL?

Ниже перечислю этапы, по которым вам следует пройтись, чтобы понять причины проблемы.

1. Смотрим, что было сделано раньше.

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

И желательно, чтобы основной файл конфигурации БД postgresql.conf также был в гите. Тогда вы сможете в том числе контролировать и изменение настроек базы.

Конечно, вы должны взять за правило — не делать никаких изменений в базе напрямую, а всё проводить через гит и миграции.

Особенно если речь идёт об изменениях структуры базы данных, добавлении обработчиков, пользовательских функций, процедур. Эти работы должны делать скрипты, которые, в свою очередь, должны версионироваться.

2. Смотрим, что происходит в операционной системе.

Если изменений в гите не было, а проблема возникла неожиданно, то следующее, на что стоит обратить внимание — что происходило в операционной системе.

Надеюсь, база данных у вас установлена на Linux-машине. Если нет, то советую подумать о переезде на Linux.

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

3. Проверяем оборудование.

Опять же, если в операционной системе вы ничего не меняли, у вас уже стоит Linux, стоит обратить внимание на оборудование. Сегодня некоторые производители железа грешат качеством и нельзя надеяться на то, что память или жёсткий диск не выйдут из строя в самый неподходящий момент. Поэтому, первое на что советую обратить внимание:

Если вы видите, что процессор загружен на 70% и более и при этом загружены все ядра, то подумайте об увеличении мощности. Процессоры сейчас дешёвые, а если вы используете виртуализацию, то добавьте вашей базе дополнительное количество ядер. Скорее всего, вашей базе перестало хватать мощностей из-за возросшей нагрузки и стоит подумать о модернизации сервера.

SQL и POSTGRESQL | Урок #4. Почему PostgreSQL?

Обратите внимание на последнюю колонку. Она вам даст представление о загрузке дисковой подсистемы. Если показатель приближается к 100%, то нужно подумать об увеличении памяти. Скорее всего, у вас очень большой размер базы данных, а оперативки на сервере для обработки данных слишком мало.

Помните, что расходы по оптимизации запросов или кода приложения не окупятся, если у вас присутствуют проблемы с оборудованием.

4. Установите систему мониторинга.

Попробуйте установить систему мониторинга Percona https://www.percona.com/software/database-tools/percona-monitoring-and-management. Её интерфейс показан на картинке выше. Она бесплатная и позволит вам быстро оценить динамику нагрузки на сервер и покажет все главные показатели работы сервера.

5. Проанализируйте файловое хранилище.

Посмотрите на колонки с постфиксов _await. Они говорят вам о показателе f/s latency. Если показатель больше 50 мс без нагрузки, то стоит задуматься. Если более 100 — то стоить предпринимать срочные меры.

Причиной высоких цифр может быть неверная настройка гипервизора или работа базы через NAS. Последнего подхода в проектировании нужно избегать.

6. Анализируем pg_stat_activity.

Итак, мы проверили сервера, оборудование, ничего необычного не нашли. Переходим непосредственно к тюнингу базы.

Первое, что приходит на ум — представление pg_stat_activity.

Сначала проверьте в настройках базы параметр track_activity_query_size, по умолчанию он выставлен на 1024. Увеличьте его как минимум в 2-3 раза, в большинстве случаев его не хватает.

Теперь ищем, какая активность у нас происходит в базе. Может всё гораздо проще и кто-то запустил сложный запрос и вам стоит остановить этот скрипт. Смотрим активные запросы длительностью более 5 секунд:

SELECT now() — query_start as «runtime», usename, datname, wait_event, state, query FROM pg_stat_activity WHERE now() — query_start > ‘5 seconds’::interval and state=’active’ ORDER BY runtime DESC;

Также могут вызвать подозрения запросы с состоянием «idle». А ещё хуже с состоянием «idle in transaction».

Такие запросы можно остановить следующими командами:

Первый остановит активные запросы, второй с типом idle.

Теперь стоит посмотреть, есть ли у вас зависшие трансакции. Выполняем запрос:

SELECT pid, xact_start, now() — xact_start AS duration FROM pg_stat_activity WHERE state LIKE ‘%transaction%’ ORDER BY 3 DESC;

Помните, трансакции должны выполняться моментально. Из ответа смотрите на duration. Если трансакция висит несколько минут, а тем более часов, значит, приложение повело себя некорректно, оставив трансакцию незавершённой. А это влияет на репликацию, на работу VACUUM, WAL.

7. Анализируем pg_stat_statements.

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

Затем в текущем сеансе работы с базой, запускаем команду:

create extension pg_stat_statements;

Если вы планируете заниматься производительностью базы, то оно вам нужно обязательно. Без него вы не сможете сделать многих вещей. Оно, конечно, займёт немного места на диске, но по сравнению с преимуществами это будет не страшно.

С помощью него вы можете получить статистику по уже выполненным запросам.

Например, мы можем получить запросы с максимальной загрузкой процессора такой командой:

SELECT substring(query, 1, 50) AS short_query, round(total_time::numeric, 2) AS

total_time, calls, rows, round(total_time::numeric / calls, 2) AS avg_time, round((100 *

total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu FROM

pg_stat_statements ORDER BY total_time DESC LIMIT 20;

А максимально долгие запросы — вот такой:

SELECT substring(query, 1, 100) AS short_query, round(total_time::numeric, 2) AS

total_time, calls, rows, round(total_time::numeric / calls, 2) AS avg_time, round((100 *

total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu FROM

pg_stat_statements ORDER BY avg_time DESC LIMIT 20;

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

8. Работаем с представлением pg_stat_user_tables

Таблица pg_stat_user_tables — ключевое конкурентное преимущество по сравнению с другими БД. С помощью неё мы, например, получить информацию о последовательном чтении с диска:

SELECT schemaname, relname, seq_scan, seq_tup_read, seq_tup_read / seq_scan AS avg, idx_scan

WHERE seq_scan > 0

ORDER BY seq_tup_read DESC

В верхней части таблиц будут как раз те самые операции, которые читали данные с диска. Если у таблицы больше 2000 записей, то такие операции должны производиться по индексу, а не с диска. Т.е. вы получите в первую очередь те запросы, которые вам потребуется оптимизировать.

Также вам следует посмотреть кеширование этих таблиц по представлению pg_statio_user_tables.

В этом вам помогут колонки heap_blks. и idx_blks.

9. Настраиваем память для PostgreSQL.

В зависимости от настроек сервера, настройки у вас будут примерно следующими:

effective_cache_size — 2/3 RAM

Но я рекомендую пользоваться специальными конфигураторами:

https://pgtune.leopard.in.ua/ — онлайн версия классического конфигуратора pgtune.

Эти инструменты помогут вам поставить нужные настройки за вас.

10. Настраиваем дисковую подсистему.

Если вам ничего больше ничего не помогло, то в крайнем случае вы можете выставить следующие настройки:

Но в этом случае вы потеряете в надёжности хранения данных. Но если у вас PostgreSQL не является единственной системой по обработке данных и база асинхронно копируется в аналитические системы, то с такими настройками можно жить. Так как они снижают нагрузку на диск. Немного поясню по данным параметрам:

Postgresql для чего нужна эта программа

МЕРОПРИЯТИЯ

Сбер Java Meetup

15 декабря Онлайн Бесплатно

F*ckup Meetup 2022

14 декабря Онлайн Бесплатно

Alfa Digital Open

13 декабря Онлайн Бесплатно

5 сайтов для оттачивания навыков написания SQL-запросов

Одним из ключевых навыков при работе с базами данных является умение писать sql-запросы. Мы отобрали 5 лучших сайтов для оттачивания этого навыка.

Подборка материалов для изучения баз данных и SQL

Подборка книг, видеокурсов и онлайн-ресурсов для изучения баз данных, основ реляционной теории и языка SQL.

SQL за 20 минут

Предлагаем вашему вниманию статью с кричащим названием «SQL за 20 минут». Конечно, весь SQL за 20 минут вы не освоите, но хороший старт получите.

Источник: proglib.io

Postgresql для чего нужна эта программа

Я достаточно долго думал над вопросом, какую систему управления базами данных (СУБД) выбрать для своих статей и решил остановиться на PostgreSQL.
Выбор обусловлен несколькими причинам:

Бесплатная СУБД
Простота установки.
Поддержка основных операционных систем
Удобная програма pgAdmin для работы с базами
Это современная СУБД с хорошими возможностями

В принципе в JDK есть встроенная база данных — Derby. Но пользоваться ей, на мой взгляд, очень неудобно. MySQL в общем тоже неплохо, но в нем достаточно неудобная утилита для ввода команд. Остальные базы либо платные, либо малоизвестные. Само собой, после прочтения моих статей вам никто не мешает попробовать поработать с этими базами данных самостоятельно.

Загрузить нужную версию PostgreSQL можно с этой страницы: Download PostgreSQL.
На данный момент я использовал версию 9.5.2. Какая версия будет на момент чтения статьи вами — не знаю. Но надеюсь, что в ближайшие годы что-то кардинально не поменяется.
Будьте внимательны — загружайте версию для вашей операционной системы. Дальше запускаете установку. По экранам она выглядит вот так.

На втором экране вам надо выбрать директорию для установки. Я не рекомендую устанавливать в каталог “Program Files” по умолчанию, т.к. на Windows серверных платформ это бывает чревато. На домашних системах скорее всего проблем не будет, но как говорится, “обэегшись на молоке, дуешь на воду”. Посему я обычно ставлю директорию “C:PostgreSQL”

Далее вам предложат дирекотрию для хранения файлов для баз данных — оставляйте как есть.

На следующем экране вам надо ввести парль для пользователя “postgres”. Для разработки я выбираю такой же пароль: “postgres”.

На следующем экране запрашивается порт, на котором будет “висеть” PostgreSQL. Если у вас не установлен PostgreSQL, то можно оставить по умолчанию “5432”.

Локализацию можно оставить как есть. Кому интересно, может выбрать что-нибудь конкретное. Я в принципе проблем не имел при выборе по-умолчанию.

и ждем, пока она закончится.

По окончанию вам предложат установить дополнительную утилиты Stack Builder — я ее обычно не ставлю, так что “галочку” можно снять и нажать “Finish”.

В общем установка закончилась. Теперь в списке сервисов Windows можно увидеть PostgreSQL

Я нередко устанавливаю ручной запуск сервиса, но это уже как вам будет удобно.

Осталось только узнать, что в комплекте PostgreSQL устанавливается весьма удобная и легко понятная программа для управления СУБД — pgAdmin III. Ее можно найти в стартовом меню Windows (если вы работаете под другой ОС — поищите, наверняка найдете).
Запускайте и дальше все достаточно просто.

Кликаем дважды на PostgreSQL 9.5 слева — вас могу попросить ввести пароль.
Дальше вы увидите слева струткуру вашей СУБД.

Открываем слева раздел “Базы данных” и видим уже заранее созданную базу “postgres”.
Щелкните правой кнопкой мыши на пункте “Базы данных” и в выпадающем меню выберите “Новая база данных…”. Появится форма для ввода — для начала достаточно ввести имя базы данных — я назвал ее “contactdb”

Выделите мышкой вновь созданную базу данных и получите возможность делать с ней, что хотите.

Для запуска команд вам надо открыть SQL-редактор. Проще всего — нажать кнопку на верхней панели.

В открытом окне можно набирать команды SQL.

В общем все готово. Мы можем запускать SQL-скрипт, который создаст нужную нам таблицу для контактов и вставит туда тестовые данные.
Для создания необходимых нам данных SQL-скрипт выглядит вот так:

News

Настройка приложений баз данных , Б.А.Новиков, Г.Р.Домбровская

Отличный учебник, в котором основы изложены четко и понятно;

Mastering PostgreSQL 12 , Hans-Jürgen Schönig

Хорошая книга про администрированию БД. Помимо перечисления новых фич PostgreSQL 12, книга рассматривает логическую репликацию, кластеризацию, мониторинг, оптимизацию производительности, конфигурацию системы и оптимизацию запросов и представляет собой комплексный справочник по работе с СУБД в целом. Кроме того, он дает представление о процессе перехода с Oracle на PostgreSQL;

Troubleshooting PostgreSQL , Hans-Jürgen Schönig

Несмотря на то, что книга не самая свежая – 2015 года издания – это отличное компактное практическое руководство по пониманию методов решения проблем, с которыми вы можете столкнуться при работе с PostgreSQL.

Для тех, у кого уже есть опыт работы с БД:

Фундаментальная классика в изучении БД. Его книги будут интересны тем, у кого уже есть опыт работы с базами данных и не хватает более широкой картины и системных знаний. В них есть достаточно глубокое погружение в computer science, разбор реляционной теории и т.д. Для новичка они могут быть избыточны, так как в повседневной работе с БД такие знания не обязательны. Если вам все же интересна теория, но не хотелось бы этому посвящать слишком много времени, то Настройка приложений баз данных Б.А.Новикова, Г.Р.Домбровской будет прекрасным компромиссом;

Классика в изучении БД на русском, которая точно пригодится на старте работы с любыми БД.

Книги, которые помогут углубить ваши знания о работе СУБД:

Transactional Information Systems: Theory, Algorithms, and the Practice of Concurrency Control and Recovery (The Morgan Kaufmann Series in Data Management Systems) G.Weikum, G.Vossen

Здесь приведены как фундаментальные знания по теории транзакций, так и практические примеры, которыми можно воспользоваться в работе с СУБД;

Principles of Distributed Database Systems , M.T.Özsu, P.Valduriez

Отличное объяснение про распределенные базы данных.

Развивающимся разработчикам, копающим глубже, пригодятся эти пособия:

SQL Performance Explained , Markus Winand

Книга фокусируется на прикладных методах обеспечения высокой доступности и производительности БД. Полезна как для разработчиков, так и для начинающих ДБА;

The Art of PostgreSQL , Dimitri Fontaine

Достаточно обширный труд, который поможет в разработке сложных решений для БД. Книга содержит интервью с лидирующими в комьюнити разработчиками и предлагает решения для разработчиков, релевантные вне зависимости от языка – будь то PHP, Java или Python.

Для DBA, желающих изучить тему глубже, рекомендуем:

The Internals of PostgreSQL for database administrators and system developers , Hironobu SUZUKI

Книга детально разбирает внутренние механизмы работы БД, такие как кластеризация и таблицы, работа с запросами, FDW, VACUUM, WAL, логическая репликация и прочее. Не все пригодится на практике, но к ней можно прибегнуть для поиска специфических решений. Отметим отдельно, если вам нужно понять какие-то конкретные внутренние механизмы работы PostgreSQL, не бойтесь заглянуть в исходники, ведь там вы найдете много полезной информации в readme файлах и в комментариях, и при этом не обязательно читать и разбираться в коде;

Systems Performance-Enterprise and the Cloud , Gregg Brendan

Хорошая книга для системных инженеров и DBA по траблшутингу;

Компьютерные сети , В.Г. Олифер, Н.А. Олифер

Книга для тех, кто хочет получить базовые знания о принципах построения компьютерных сетей, способах создания и управления ими. Ее стоит использовать скорее как справочник, чем читать от корки до корки. Актуальное издание можно найти здесь ;

Ядро Linux , Д. Бовет, М. Чезати

К сожалению, это единственная книга про ядро Linux и местами она достаточно устарела. При этом более актуального пособия пока нет, потому она остается хорошей отправной точкой для тех, кто хочет понять, как работает Linux. Для того, чтобы получить более полноценную и актуальную картину, советуем дополнительно почитать ресурс lwn.net .

Конечно, мы не могли пропустить одну из главных книг о языке C, на котором, помимо PostgreSQL, написаны и другие серьезные БД:

Язык программирования C , Керниган Брайан У., Ритчи Деннис М.

Конференции.

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

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

Наши ДБА регулярно выступают в качестве спикеров в России и за рубежом. Записи их докладов можно найти на нашем YouTube-канале .

Изучить типичные ошибки разработчиков при работе с PostgreSQL, например, можно по этим выступлениям:

PostgreSQL Worst Practices , Илья Космодемьянский (SCALE18x 2020 на английском , HighLoad++ 2017 на русском ).
Топ ошибок со стороны разработки при работе с PostgreSQL , Алексей Лесовский (HighLoad++ 2018).

Множество хороших тем рассматривается докладчиками на таких конференциях, как HighLoad++ , PGConf.Russia , PGDay Russia , PGConf.EU , а если хотите пообщаться с разработчиками и core team PostgreSQL, рекомендуем PGCon .

Online-митапы, подкасты.

Среди множества онлайн-митапов наши DBA выделяют встречи RuPostgres , которые проходят в прямом эфире по вторникам. Ведущие – Николай Самохвалов (Postgres.ai) и Илья Космодемьянский (Data Egret) – вместе с гостями обсуждают вопросы, связанные с разработкой PostgreSQL, существующими фичами, обновлениями, тематическими мероприятиями. Зрители могут задать вопрос в прямом эфире или предложить тему для будущих встреч .

Кроме того, существует пара подкастов, которые регулярно приглашают профессионалов сообщества PostgreSQL: Цинковый прод и SDCast .

Вот несколько примеров с участием наших DBA:

Андрей Сальников в гостях у подкаста Цинковый Прод на SaintHighload ‘20
SDCast #92: в гостях Илья Космодемьянский ;
SDCast #94 HL Edition: Алексей Лесовский и Виктор Егоров ;
Цинковый Прод #040 Алексей Лесовский как влюбить в себя Postgres .

Форумы, блоги, чаты.

Случается, перелопатишь кучу источников, но ответа на свой вопрос не находишь. К счастью, мы работаем с open-source и сообщество PostgreSQL очень открытое, а его участники готовы помочь советом в сложных вопросах.

Пообщаться с другими пользователями PostgreSQL, поделиться опытом можно на форумах, в чатах и блогах специалистов, к примеру:

https://use-the-index-luke.com (англ.) – регулярно обновляемый ресурс от Маркуса Винанда,

Также существуют тематические мейлинг листы сообщества , в которых можно задать любой вопрос, связанный с функционалом СУБД, и зачастую получить фидбек от самого Тома Лейна или других членов Core Team PostgreSQL.

Помимо этого, есть ряд блогов опытных разработчиков и DBA, в которых они делятся опытом, кейсами, обсуждают фичи и обновления СУБД:

Planet postgresql (англ.) – официальный агрегатор блогов по PostgreSQL.

Утилиты.

Существует ряд утилит для работы с PostgreSQL, которые могут значительно облегчить вашу жизнь. Среди них те, которыми пользуются наши DBA:

pgCenter – инструмент администрирования командной строки для работы с PostgreSQL статистикой ;
PgBouncer – программа, управляющая пулом соединений PostgreSQL.
pgBackRest – инструмент для резервного копирования и восстановления для базы данных PostgreSQL;
WAL-G – еще один инструмент для создания резервных копий базы и восстановления из них;
Patroni – шаблон для создания собственного индивидуального решения с высокой доступностью с использованием Python и распределенного хранилища конфигураций;
pgcompacttable – инструмент для устранения bloat в таблицах и индексах с целью освобождения места на диске без долгих блокировок;
pg_repack – еще один инструмент для устранения bloat с минимальными блокировками, работающий несколько другим способом;
pg-utils – набор полезных утилит для работы DBA с PostgreSQL.

Стоит отметить, что даже самый полноценный список книг, форумов и утилит не заменит практического применения знаний. Поэтому найдите или придумайте задачу: поставьте базу и начните играть с настройками, а затем использовать ее. Уверяем, это станет для вас бесценным опытом.

Разумеется, в этом посте представлен далеко не самый исчерпывающий список источников, полезных при работе с PostgreSQL. Да, не все подойдет в вашем конкретном случае и станет актуальным для вашей работы, но мы надеемся, что среди этих источников вы найдете подходящие и интересные вам ресурсы.

А наши рекомендации позволят вам сэкономить время на поиске полезного материала и сразу перейти к изучению. Познавательного погружения в PostgreSQL!

Источник: dataegret.com

Postgresql для чего нужна эта программа

Postgresql для чего нужна эта программа