Базовые навыки администрирования PostgreSQL, о которых должен знать каждый Python разработчик

Иван Терещук (mos.ru, Тимлид мобильной разработки) @ Moscow Python Meetup №76
"PostgreSQL — наиболее популярная база данных, которая используется в Python разработке сейчас. Не все разработчики знают, с какими нюансами можно столкнуться при ее использовании. В докладе будет много полезной информации по тому как правильно настроить PostgreSQL под ваш проект чтобы повысить ее эффективность и надежность".

Видео: https://moscowpython.ru/meetup/76/postresql-basics/

Transcript

1. Базовые навыки администрирования Postgresql О которых должен знать каждый Python разработчик Иван Терещук
2. Почему это важно
3. Потому
4. Завел базу - Сделай бэкапы! • pg_dump • pg_basebackup • rsync, tar, cp
6. FATAL: sorry, too many clients already Проблема множественных соединений к БД
8. # vim postgresql.conf max_connections = 300 Как добавить соединения прямо сейчас
9. pg_stat_activity SELECT pid, usename, now() - query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - query_start) > interval '5 minutes';
10. pg_stat_activity
11. Pgbouncer • Session pooling • Transaction pooling • Statement pooling
13. 13 No space left on device Проблема свободного места
14. 14 Забыл про мониторинг свободного места • Prometheus • Grafana • New Relic • Zabbix • etc…
15. $ ls -lah /var/lib/pgsql/11/data/log/ total 18G drwx------ 2 postgres postgres 4.0K Feb 21 00:00 . drwx------ 21 postgres postgres 4.0K Feb 21 00:00 .. -rw------- 1 postgres postgres 18G Feb 21 14:46 postgresql-Fri.log -rw------- 1 postgres postgres 9.3K Feb 20 22:52 postgresql-Thu.log -rw------- 1 postgres postgres 3.3K Feb 19 22:36 postgresql-Wed.log Где освободить пространство прямо сейчас
16. $ tail-100 postgresql-Fri.log > /tmp/log_temp.log $ cat /dev/null> /var/lib/pgsql/11/data/log/postgresql-Fri.log Очистка логов
17. Вывести топ 10 самых больших таблиц SELECT table_name, pg_relation_size(quote_ident(table_name)) AS size FROM information_schema.tables WHERE table_schema = 'public' ORDER BY size DESC LIMIT 10;
18. VACUUM и autovacuum VACUUM [ FULL ] [ имя_таблицы [ (имя_столбца [, ...] ) ] ] autovacuum = on
19. Партиционирование таблиц
20. Параметры настроек postgresql.conf • shared_buffers • wal_buffers • effective_cache_size • work_mem • temp_buffers • maintenance_work_mem • synchronous_commit Узнать подробнее - https://habr.com/ru/post/458952/
21. https://pgtune.leopard.in.ua/
22. Что делать, если запросы тормозят
23. Как найти медленные запросы log_min_duration_statement = 10000
24. Как найти медленные запросы SELECT now() - query_start AS duration, query FROM pg_stat_activity ORDER BY duration DESC LIMIT 10;
25. Как найти медленные запросы while psql -qt -c "select query, now() - query_start as duration from pg_stat_activity" >> query_stats.txt; do sleep 1; done sort query_stats.txt | less
26. Почему запрос выполняется медленно EXPLAIN [ ANALYZE ] [ VERBOSE ] оператор
27. Почему запрос выполняется медленно EXPLAIN ANALYZE SELECT * FROM users WHERE first_name='Людвиг';
28. Почему запрос выполняется медленно EXPLAIN ANALYZE SELECT * FROM users WHERE id = '4858565f-1ed8-47f1-bdd6- 9f37b943beed';
30. Взаимные блокировки (Deadlocks)
31. Взаимные блокировки (Deadlocks) ERROR: deadlock detected DETAIL: Process 18293 waits for ShareLock on transaction 639; blocked by process 18254. Process 18254 waits for ShareLock on transaction 640; blocked by process 18293. HINT: See server log for query details. CONTEXT: while updating tuple (0,9) in relation "users"
32. Репликация
33. Потоковая Master-Slave репликация
34. Multi-Master репликация
35. Итого • Сразу настройте резервное копирование (бэкапы); • Планируйте сколько коннектов К БД вам нужно, настройте PgBouncer; • Следите за местом на жестком диске и объемами таблиц, настройте avtovacuum; • Сделайте первичные настройки параметров в конфигурационном файле БД; • Оптимизируйте долгие запросы; • Следите за взаимными блокировками (deadlocks) • Знайте, как работать с Master-Slave архитектурой
37. Полезные ссылки и источники • https://postgrespro.ru/docs/postgresql/ • https://www.youtube.com/watch?v=HjLnY0aPQZo • Очистка пространства - https://severalnines.com/database-blog/my-postgresql-database- out-disk-space • Оптимизация параметров - https://habr.com/ru/post/458952/ • Обнаружение дедлоков - https://habr.com/ru/company/wargaming/blog/323354/ • PgTune - https://pgtune.leopard.in.ua/ • Саймон Ригс. Ханну Кросинг. Администрирование Postgresql 9. Книга рецептов
38. Спасибо! Иван Терещук tg: ivanteresh tereschukia@mos.ru