Руководство по оптимизации PostgreSQL и MySQL: 5 реальных примеров с готовыми скриптами

Введение: когда база данных начинает тормозить

Привет, коллеги! Знаете, что объединяет всех системных администраторов и DBA в 3 часа ночи? Правильно — звонок от разработчика с криком «База умирает!». За 15 лет в профессии я насмотрелся на такое количество неоптимизированных баз данных, что мог бы написать энциклопедию антипаттернов.

Сегодня поговорим о том, как превратить вашу MySQL или PostgreSQL из неповоротливого слона в гоночный болид. И нет, решение не в том, чтобы просто купить сервер с 512 ГБ RAM (хотя это тоже помогает). Речь пойдет о настоящей оптимизации — той, которая дает прирост производительности в 10-100 раз без дополнительных инвестиций в железо.

Анатомия медленной базы данных

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

• Медленные запросы — SELECT, который выполняется 30 секунд вместо 0.01
• Отсутствие индексов — или наоборот, их избыток
• Блокировки таблиц — когда все стоят в очереди за одним ресурсом
• Неправильная конфигурация — дефолтные настройки не подходят для вашего железа
• Проблемы с дисками — HDD вместо SSD, или умирающий диск

Самое смешное (или печальное), что в 80% случаев проблема решается элементарно: добавлением одного индекса или изменением трех строк в конфигурации.

Диагностика: находим узкие места

Для PostgreSQL:

# Включаем логирование медленных запросов в postgresql.conf
log_min_duration_statement = 100
log_line_prefix = '%t [%p]: user=%u,db=%d '
log_checkpoints = on
log_lock_waits = on

sudo systemctl reload postgresql

# Смотрим текущие медленные запросы
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

# Неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Для MySQL:

# В /etc/mysql/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

sudo systemctl restart mysql

# Текущие долгие запросы
SELECT * FROM information_schema.processlist 
WHERE time > 5 
ORDER BY time DESC;

# Таблицы без индексов
SELECT t.table_schema, t.table_name, t.table_rows
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s 
ON t.table_schema = s.table_schema AND t.table_name = s.table_name
WHERE s.index_name IS NULL
AND t.table_schema NOT IN ('mysql', 'information_schema');

Пять практических примеров оптимизации

Пример 1: Оптимизация JOIN-запросов с индексами

Классика: запрос с джойнами трех таблиц выполняется вечность. Проблема — нет индексов на колонках соединения.

-- Проблемный запрос
SELECT u.username, o.order_id, p.product_name
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_products op ON o.order_id = op.order_id
JOIN products p ON op.product_id = p.product_id
WHERE u.created_at > '2025-01-01';

-- Проверяем план выполнения
EXPLAIN ANALYZE [запрос];

-- Создаем индексы (PostgreSQL и MySQL)
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_order_products_order ON order_products(order_id);
CREATE INDEX idx_order_products_product ON order_products(product_id);

-- Обновляем статистику
ANALYZE users, orders, order_products, products;

Результат: время выполнения с 15 секунд упало до 0.05 секунд — в 300 раз быстрее!

Пример 2: Оптимизация PostgreSQL для 16 ГБ RAM

Дефолтные настройки PostgreSQL рассчитаны на картофелину. Исправляем это:

# /etc/postgresql/15/main/postgresql.conf

# Память (для 16 ГБ RAM)
shared_buffers = 4GB                    # 25% от RAM
effective_cache_size = 12GB             # 75% от RAM
maintenance_work_mem = 1GB
work_mem = 64MB

# Параллелизм
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Контрольные точки
checkpoint_completion_target = 0.9
wal_buffers = 16MB
min_wal_size = 2GB
max_wal_size = 8GB

# Для SSD
random_page_cost = 1.1                  # Вместо 4.0 для <a class="wpil_keyword_link" href="https://it-apteka.com/tag/hdd/"   title="hdd" data-wpil-keyword-link="linked"  data-wpil-monitor-id="229">HDD</a>
effective_io_concurrency = 200

# Логирование
shared_preload_libraries = &amp;#039;pg_stat_statements&amp;#039;

sudo systemctl restart postgresql

Скрипт автонастройки:

#!/bin/bash
TOTAL_RAM=$(free -g | awk '/^Mem:/{print $2}')
CPU_CORES=$(nproc)

SHARED_BUFFERS=$((TOTAL_RAM / 4))
EFFECTIVE_CACHE=$((TOTAL_RAM * 3 / 4))

echo "shared_buffers = ${SHARED_BUFFERS}GB"
echo "effective_cache_size = ${EFFECTIVE_CACHE}GB"
echo "max_worker_processes = ${CPU_CORES}"

Пример 3: Оптимизация MySQL/InnoDB

# /etc/mysql/my.cnf
[mysqld]

# Память (для 16 ГБ RAM)
innodb_buffer_pool_size = 10G           # 60-70% от RAM
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Потоки
innodb_read_io_threads = 8
innodb_write_io_threads = 8
max_connections = 500

# Кеш
table_open_cache = 4000
table_definition_cache = 2000

# Буферы
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 2M

# Временные таблицы
tmp_table_size = 64M
max_heap_table_size = 64M

sudo systemctl restart mysql

Проверка настроек:

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# Hit rate кеша (должен быть > 99%)
mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';"

Пример 4: Автоматический анализ производительности

Скрипт для PostgreSQL:

#!/bin/bash
DB_NAME="mydb"
LOG_FILE="/var/log/pg_perf_$(date +%Y%m%d).log"

echo "=== Анализ PostgreSQL ===" > $LOG_FILE

# Топ медленных запросов
psql -U postgres -d $DB_NAME -c "
SELECT 
  substring(query, 1, 80) AS query,
  calls,
  total_exec_time / 1000 AS total_sec,
  mean_exec_time / 1000 AS avg_sec
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;" >> $LOG_FILE

# Таблицы с низким использованием индексов
psql -U postgres -d $DB_NAME -c "
SELECT tablename, seq_scan, idx_scan,
  ROUND(100.0 * idx_scan / NULLIF(seq_scan + idx_scan, 0), 2) AS idx_usage
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 1000
ORDER BY idx_usage ASC
LIMIT 10;" >> $LOG_FILE

# Неиспользуемые индексы
psql -U postgres -d $DB_NAME -c "
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;" >> $LOG_FILE

echo "Отчет: $LOG_FILE"

Скрипт для MySQL:

#!/bin/bash
DB_USER="root"
DB_PASS="password"
LOG_FILE="/var/log/mysql_perf_$(date +%Y%m%d).log"

echo "=== Анализ MySQL ===" > $LOG_FILE

# Slow query анализ (требует percona-toolkit)
if command -v pt-query-digest &> /dev/null; then
  pt-query-digest /var/log/mysql/slow-query.log >> $LOG_FILE
fi

# Таблицы без PRIMARY KEY
mysql -u$DB_USER -p$DB_PASS -e "
SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints tc
  ON t.table_name = tc.table_name
  AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
  AND t.table_schema NOT IN ('mysql', 'sys');" >> $LOG_FILE

# Фрагментированные таблицы
mysql -u$DB_USER -p$DB_PASS -e "
SELECT table_name,
  ROUND(data_free / 1024 / 1024, 2) AS free_mb,
  ROUND(data_free * 100 / (data_length + data_free), 2) AS frag_pct
FROM information_schema.tables
WHERE data_free > 0
ORDER BY frag_pct DESC
LIMIT 10;" >> $LOG_FILE

echo "Отчет: $LOG_FILE"

Пример 5: Партиционирование больших таблиц

Когда таблица растет до сотен миллионов записей, партиционирование — спасение.

PostgreSQL:

-- Создаем партиционированную таблицу
CREATE TABLE logs (
    id BIGSERIAL,
    created_at TIMESTAMP NOT NULL,
    user_id INTEGER,
    action VARCHAR(100)
) PARTITION BY RANGE (created_at);

-- Партиции по месяцам
CREATE TABLE logs_2025_01 PARTITION OF logs
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE logs_2025_02 PARTITION OF logs
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Индексы на партиции
CREATE INDEX idx_logs_2025_01_user ON logs_2025_01(user_id);
CREATE INDEX idx_logs_2025_02_user ON logs_2025_02(user_id);

-- Функция автосоздания партиций
CREATE OR REPLACE FUNCTION create_partition()
RETURNS void AS $$
DECLARE
    start_date DATE;
    end_date DATE;
    part_name TEXT;
BEGIN
    start_date := date_trunc('month', CURRENT_DATE + interval '1 month');
    end_date := start_date + interval '1 month';
    part_name := 'logs_' || to_char(start_date, 'YYYY_MM');
    
    EXECUTE format('CREATE TABLE %I PARTITION OF logs FOR VALUES FROM (%L) TO (%L)',
                   part_name, start_date, end_date);
    RAISE NOTICE 'Создана партиция: %', part_name;
END;
$$ LANGUAGE plpgsql;

MySQL:

-- Партиционированная таблица
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date) * 100 + MONTH(sale_date)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Добавление новой партиции
ALTER TABLE sales 
REORGANIZE PARTITION p_future INTO (
    PARTITION p202504 VALUES LESS THAN (202505),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Удаление старых данных
ALTER TABLE sales DROP PARTITION p202401;

Результаты: запросы с фильтром по дате ускоряются в 10-50 раз, удаление старых данных мгновенное!

Мониторинг и типичные ошибки

Что мониторить постоянно:

• Cache hit ratio (должен быть > 99%)
• Средняя длительность запросов
• Количество блокировок
• Размер таблиц и индексов
• Использование дискового пространства

Типичные ошибки:

• Слишком агрессивная очистка — используйте таймауты минимум 2 секунды при тестировании доступности
• Игнорирование статистики — всегда делайте ANALYZE после массовых изменений
• Избыток индексов — каждый индекс замедляет INSERT/UPDATE
• Недостаток памяти — если shared_buffers или innodb_buffer_pool слишком малы, производительность падает катастрофически

Заключение: путь к идеальной базе

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

Настройте мониторинг, автоматизируйте обслуживание, и ваша база будет работать как часы. А когда коллега спросит «как ты добился такой производительности?», загадочно улыбнитесь и скажите: «Это IQ 150, детка».

Помните: медленная база — это не приговор, это диагноз с известным лечением. Используйте эти практики, адаптируйте под свои условия, и пусть ваши базы данных летают быстрее света!

P.S. И да, делайте бэкапы перед любыми изменениями конфигурации. Я знаю, вы это знаете, но напомнить не помешает.