1. Паспорт учебного занятия и целеполагание
Дисциплина: ОП.08 Основы проектирования баз данных
Специальность: 09.02.07 Информационные системы и программирование
Тема программы: Тема 4. Проектирование структур баз данных
Номер и наименование: Практическое занятие №10: исполняемый файл проекта, этап 1 (Занятие №27, §2.2 с.7–8 рабочей программы)
Вид занятия: Практическое занятие
Основной регламент: 80 минут (2 академических часа)
Сокращенный регламент: 60 минут (интенсивный трек)
Форма работы: индивидуальная инженерно-практическая деятельность за ПК
Оборудование: ПК с ОС Windows/Linux, установленный сервер PostgreSQL/MySQL, среда разработки (VS Code / PyCharm / IDEA), терминальный клиент psql, веб-браузер для интерактивного листа zanyatie.html.
Ключевые образовательные цели и формируемые результаты
- Дидактическая (обучающая): освоить архитектурные основы сборки исполняемого приложения для интеграции с СУБД; изучить синтаксис и структуру параметров строки подключения (Connection String: драйвер, протокол, хост, порт, имя БД, учетные данные, параметры SSL, таймауты); понять физические механизмы установления TCP-сессии и рукопожатия СУБД (Handshake); сформировать компетенции по конфигурации высокопроизводительного пула соединений (Connection Pool: min/max connections, idle_timeout, max_lifetime); реализовать первичное тестирование сетевой связности (SELECT 1 / Ping) и структурированное логирование сетевых исключений.
- Развивающая: сформировать системное инженерное понимание накладных расходов при сетевом межпроцессном взаимодействии; развить способность диагностировать и устранять сетевые сбои на стыке уровней L4/L7 (connection refused, timeout, handshake failure); развить навыки оптимизации серверных ресурсов СУБД.
- Воспитательная: привить студентам культуру безопасной разработки (Security by Design, 12-Factor App) с безусловным запретом хардкода учетных данных в репозиториях; воспитать ответственность за отказоустойчивость разрабатываемых информационных систем.
2. Поминутный тайминг учебного занятия (80 и 60 минут)
Организационная структура занятия адаптирована под два регламента проведения с сохранением полного дидактического цикла.
| Этап занятия | 80 минут | 60 минут | Содержание деятельности преподавателя и студентов |
|---|---|---|---|
| 1. Организационный момент и проблемная мотивация | 5 минут | 3 минуты | Приветствие, фиксация явки. Разбор реального производственного кейса: «Черная пятница — падение сервера БД под нагрузкой из-за создания 1500 прямых соединений без пула (исчерпание RAM и процессов СУБД)». Постановка задачи занятия. |
| 2. Теоретический базис и разбор архитектуры | 15 минут | 12 минут | Демонстрация презентации (слайды 2–11). Разбор структуры Connection String, физики Connection Handshake, устройства Connection Pool (HikariCP, pgBouncer), режимов SSL (sslmode=verify-full) и паттернов Health Check. |
| 3. Сквозная живая демонстрация (Live Coding) | 10 минут | 7 минут | Преподаватель на проекторе демонстрирует: сборку файла конфигурации (.env / config.json), инициализацию пула на 5 соединений, выполнение пинга БД (SELECT 1), искусственный сбой (остановка службы СУБД) и обработку исключения с записью в системный лог. |
| 4. Самостоятельный инженерный практикум | 40 минут | 32 минуты | Студенты в интерактивном листе zanyatie.html выполняют задания по 4 вариантам: расчет параметров пула, оформление конфигураций в JSON/YAML/.env, написание проверочного кода соединения и обработчика сетевых исключений. Индивидуальный инструктаж преподавателя. |
| 5. Контроль, экспертный разбор и рефлексия | 10 минут | 6 минут | Демонстрация слайда 15 (мем «Little Bobby Tables» и культура безопасности). Анализ 3 типичных ошибок студентов (утечка соединений, хардкод паролей, таймауты). Оценивание по 4-балльной рубрике. Инструктаж по домашнему заданию. |
3. Архитектура взаимодействия исполняемого файла с СУБД
3.1. Параметры строки подключения (Connection String)
Строка подключения представляет собой структурированную спецификацию, передаваемую клиентскому драйверу СУБД (PostgreSQL libpq, JDBC, Npgsql, asyncpg, psycopg2). Существует два базовых стандарта записи:
postgresql://db_user:s3cr3t_p@ss@db.prod.company.local:5432/warehouse_db?sslmode=verify-full&sslrootcert=/etc/ssl/ca.crt&connect_timeout=10&application_name=WarehouseApp_v1host=db.prod.company.local port=5432 dbname=warehouse_db user=db_user password=s3cr3t_p@ss sslmode=verify-full sslrootcert=/etc/ssl/ca.crt connect_timeout=10| Параметр | Назначение | Рекомендуемые значения / Риски |
|---|---|---|
driver / scheme |
Протокол сетевого драйвера СУБД | postgresql://, mysql://, oracle:thin: |
host & port |
Сетевой адрес узла СУБД и порт прослушивания сокета | localhost:5432, 192.168.1.100:5432. При недоступности возникает Connection Refused. |
dbname |
Целевой каталог базы данных на сервере | При ошибке имени СУБД возвращает database does not exist (код 3D000). |
user & password |
Учетные данные для аутентификации роли | Никогда не коммитить в Git! Использовать внешние секреты (.env, HashiCorp Vault). |
sslmode |
Политика защиты сетевого канала | disable (запрещено в проде!), require, verify-ca, verify-full (защита от Man-in-the-Middle). |
connect_timeout |
Максимальное время ожидания TCP-рукопожатия | 2–10 секунд. Значение 0 (бесконечность) блокирует рабочие потоки приложения при падении маршрута. |
3.2. Архитектура и физика пула соединений (Connection Pool)
Создание каждого прямого соединения с СУБД сопряжено со значительными накладными расходами операционной системы:
- Тройное TCP-рукопожатие (SYN, SYN-ACK, ACK) — ~10–30 мс.
- TLS-рукопожатие (обмен сертификатами, согласование шифров Diffie-Hellman) — ~30–70 мс.
- Аутентификация СУБД (SCRAM-SHA-256) и запуск серверного процесса
postgres backend— ~40–100 мс и выделение до 10 МБ оперативной памяти на клиенто-процесс.
Суммарная задержка: 100–200 мс на каждое обращение! Пул соединений держит сокеты открытыми и передает их потокам приложения за 0.2–0.5 мс.
$$ ext{Pool Size} = ( ext{CPU Cores} imes 2) + ext{Disk Spindle Count}$$
Для стандартного 4-ядерного сервера с SSD оптимальный размер пула составляет: $4 imes 2 + 1 = 9 \dots 10$ активных соединений. Попытка установить размер пула в 500 или 1000 соединений приводит к катастрофической деградации производительности из-за конкуренции за процессорное время (context switching) и дисковый ввод-вывод.
min_connections (min_idle): гарантированное число сокетов, готовых к мгновенному приему запроса (обычно 2–5).max_connections (max_pool_size): жесткий верхний предел открытых сокетов приложения (например, 10–20).idle_timeout: время (в сек/мс), по истечении которого простаивающие сокеты сверх min_connections закрываются.connection_timeout: максимальное время ожидания свободного коннекта из пула до выброса исключения (2000–5000 мс).
Если приложение арендует соединение из пула (метод getConnection() или pool.acquire()) и не возвращает его в блоке finally (или через контекстный менеджер with / try-with-resources), пул исчерпывается за несколько запросов.
Все последующие потоки блокируются и падают по таймауту, выводя систему из строя.
3.3. Первичная инициализация, Health Check и логирование
При старте исполняемого файла проект обязан выполнить процедуру самодиагностики связности с БД перед запуском бизнес-логики (Fail-Fast принцип):
-- Простейший проверочный запрос (Health Check / Ping):
SELECT 1;- Connection Refused (ECONNREFUSED / SQLState 08001): сервер СУБД остановлен, либо слушает другой порт/интерфейс (проверить
listen_addressesв postgresql.conf). - Connection Timeout: фаервол дропает пакеты, маршрут недоступен, либо исчерпан пул соединений.
- SSL Handshake Error: не совпадает CN/SAN в сертификате, истек срок действия сертификата или указан неверный Root CA.
- Too Many Connections (SQLState 53300): СУБД достигла лимита
max_connections. Требуется внедрение промежуточного пулера pgBouncer.
4. Типовые студенческие ошибки и методы их устранения
| Типовая ошибка | Причина и последствия | Методическое исправление |
|---|---|---|
| Хардкод учетных данных в коде | Студент прописывает логин и пароль БД прямо в исходном коде main.py или Program.cs. При пуше в GitHub пароли утекают ботам за секунды. |
Требовать вынос параметров в .env или config.json, добавление файла в .gitignore и предоставление файла-образца .env.example. |
| Бесконечный таймаут (connect_timeout=0) | При обрыве сети или зависании сервера приложение «намертво» висит, забивая поток выполнения и не сообщая пользователю об ошибке. | Объяснить принцип Fail-Fast. Установить жесткий лимит connect_timeout = 3..5 секунд с выводом внятного интерфейсного уведомления. |
| Открытие нового соединения на каждый SQL-запрос | Студент вызывает create_connection() внутри цикла или функции выборки данных без закрытия сокета. Сервер быстро падает с ошибкой исчерпания соединений. |
Объяснить архитектуру пула соединений. Однократная инициализация пула при старте модуля и аренда сокетов через контекстный менеджер. |
| Утечка соединения (Connection Leak) | При возникновении ошибки в SQL-запросе поток прерывается до вызова conn.close(), сокет навсегда остается занятым в пуле. |
Обучить использованию конструкции try ... finally { conn.close(); } либо синтаксиса with pool.connection() as conn:. |
| Игнорирование проверки сертификата (sslmode=disable) | Отключение шифрования в корпоративных сетях приводит к перехвату логинов и персональных данных через сниффинг трафика (Wireshark/ARP-spoofing). | Показать настройку sslmode=verify-full с явным указанием пути к доверенному корневому сертификату sslrootcert. |
5. Педагогические приемы и критерии оценивания
После того как студент успешно настроил подключение и получил статус «Подключение успешно», преподаватель просит изменить порт в конфигурации на несуществующий (5433 вместо 5432) или временно остановить локальную службу PostgreSQL. Студент обязан продемонстрировать, что его приложение не упало с неотловленным аварийным завершением (Crash / Unhandled Exception), а корректно вывело информативное сообщение об ошибке в консоль/интерфейс и зафиксировало инцидент в лог-файле с временной меткой.
- Параметры Connection String (2 балла): корректное выделение всех ключевых параметров (хост, порт, БД, логин, пароль, SSL, таймаут) в отдельный файл конфигурации (.env, JSON, YAML) без хардкода секретов в коде, соблюдение синтаксиса.
- Архитектура пула соединений (1 балл): обоснованный расчет минимального и максимального размера пула для заданной предметной области, настройка времени удержания
idle_timeout, предотвращение утечек сокетов. - Обработка ошибок и самопроверка (1 балл): реализация первичной проверки доступности (SELECT 1 / ping) и перехват исключений сбоя подключения (Connection Refused / Timeout) с выводом в системный журнал.