Как автоматически чистить базу данных MariaDB/MySQL раз в неделю

Привет! Решил скрипт сделать для чисти базы данных. Ну если у вас анализ данных за годы тогда стоит чистить раз в несколько лет. но мне пока хватит и недели. Если у тебя IoT-система на Raspberry Pi пишет данные от датчиков в MySQL или MariaDB, то со временем база начинает раздуваться — особенно если ты просто вставляешь новые записи, а не обновляешь старые. У меня, например, за пару недель накопилось почти 900 тысяч строк и 70 МБ в одной таблице. Так то это совсем не навпрягает, но если данных будет за год или два может подтормаживать слабые процессоры, особенно без индексов.

Как ускорить MySQL / MariaDB: полезные команды и советы

Если таблица растёт (например, до 70 МБ и больше), а запросы тормозят — скорее всего, не хватает индексов или не оптимизированы настройки. Вот основные шаги:

1. Добавьте индексы на поля, используемые в WHERE, JOIN, ORDER BY

CREATE INDEX idx_column_name ON table_name (column_name);

-- Для составного условия
CREATE INDEX idx_status_date ON orders (status, created_at);

2. Проверьте, использует ли запрос индекс

EXPLAIN SELECT * FROM products WHERE category = 'electronics';

Если в колонке key — NULL, индекс не используется.

3. Посмотрите текущие индексы таблицы

SHOW INDEX FROM your_table_name;

4. Оптимизируйте таблицу (особенно после массовых DELETE/UPDATE)

OPTIMIZE TABLE your_table_name;

Это дефрагментирует данные и обновляет статистику для оптимизатора запросов.

5. Увеличьте буфер InnoDB (в my.cnf или my.ini)

# Для сервера с 2 ГБ RAM и одной основной БД
innodb_buffer_pool_size = 1G

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

6. Включите slow query log для поиска медленных запросов

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

После перезагрузки MySQL лог будет записывать все запросы дольше 1 секунды.

7. Избегайте SELECT * — запрашивайте только нужные поля

-- Плохо
SELECT * FROM logs WHERE user_id = 123;

-- Лучше
SELECT id, timestamp, action FROM logs WHERE user_id = 123;

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

Чистка базы данных от старых записей

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

Шаг 1. Смотрим, что именно занимает место

Сначала зайди в консоль MariaDB:

mysql iot_db

И выполни запрос:

SELECT
  table_name AS `Таблица`,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Размер (МБ)`,
  table_rows AS `Строк`
FROM information_schema.tables
WHERE table_schema = 'iot_db'
ORDER BY (data_length + index_length) DESC;

У меня, например, выяснилось, что почти всё место съедает таблица sensor_data. Остальные — доли мегабайта.

Шаг 2. Создаём файл конфигурации для безопасного доступа

Чтобы не хранить пароль в скрипте, используем файл ~/.my.cnf. Создаём его без sudo — от своего пользователя:

nano ~/.my.cnf

Содержимое файла:

[client]
user = твой_пользователь_бд
password = твой_пароль
host = localhost

Важно: установи правильные права, иначе MariaDB проигнорирует файл:

chmod 600 ~/.my.cnf

Теперь команды вроде mysql iot_db будут работать без запроса пароля.

Шаг 3. Пишем скрипт очистки

Создаём файл, например /home/ваш_пользователь/clean_iot_db.sh:

#!/bin/bash

# Очищаем только те таблицы, которые накапливают данные
mysql iot_db -e "
  TRUNCATE TABLE sensor_data;
  TRUNCATE TABLE log_stats;
  TRUNCATE TABLE rpi_monitor;
  TRUNCATE TABLE daily_resets;
"

echo "$(date): Очистка базы iot_db завершена." >> /var/log/clean_iot_db.log

Не трогай таблицы вроде sensor_info — в них хранится справочная информация о датчиках (названия, комнаты, иконки). Если её почистить — админка перестанет показывать датчики.

Сделай скрипт исполняемым:

chmod +x /home/ваш_пользователь/clean_iot_db.sh

Шаг 4. Запускаем по расписанию через cron

Открываем редактор cron:

crontab -e

Добавляем строку для еженедельной очистки (например, каждое воскресенье в 3 ночи):

0 3 * * 0 /home/ваш_пользователь/clean_iot_db.sh

Готово! Теперь база не будет расти бесконтрольно.

Что происходит после очистки?

После запуска скрипта таблицы sensor_data и логов становятся пустыми. В админке на пару минут может не отображаться текущие значения — это нормально. Как только датчики (ESP32, Arduino и т.д.) пришлют новые данные — всё снова заработает.

Объём базы падает с десятков мегабайт до пары сотен килобайт. SD-карта благодарит.

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

Если переписать логику записи в базу так, чтобы обновлять последнее значение по уникальному ключу (например, по topic), то таблица sensor_data всегда будет содержать по одной строке на датчик. Тогда чистка вообще не понадобится.

Но если не хочется переписывать — текущий скрипт решает задачу "здесь и сейчас".

Как чистить не всю таблицу, а только данные старше недели

Иногда хочется сохранить свежие данные в админке, но избавиться от "хвостов" прошлых недель. Для этого вместо TRUNCATE используй DELETE с условием по времени.

Предположим, в таблице sensor_data есть поле timestamp (обычно DATETIME или TIMESTAMP). Тогда команда для удаления всего, что старше 7 дней, выглядит так:

DELETE FROM sensor_data WHERE timestamp < NOW() - INTERVAL 7 DAY;

Аналогично можно почистить и другие таблицы:

DELETE FROM log_stats WHERE timestamp < NOW() - INTERVAL 7 DAY;
DELETE FROM rpi_monitor WHERE timestamp < NOW() - INTERVAL 7 DAY;

Обрати внимание: такой подход работает только если в таблице есть колонка с датой/временем. Если у тебя в sensor_data нет timestamp — добавь её:

ALTER TABLE sensor_data ADD COLUMN timestamp DATETIME DEFAULT CURRENT_TIMESTAMP;

После этого все новые записи будут автоматически помечаться временем вставки.

Обновлённый скрипт с "умной" очисткой

Вот как будет выглядеть скрипт, если оставить только свежие данные:

#!/bin/bash

mysql iot_db -e "
  DELETE FROM sensor_data WHERE timestamp < NOW() - INTERVAL 7 DAY;
  DELETE FROM log_stats WHERE timestamp < NOW() - INTERVAL 7 DAY;
  DELETE FROM rpi_monitor WHERE timestamp < NOW() - INTERVAL 1 DAY;
  TRUNCATE TABLE daily_resets;
"

echo "$(date): Удалены данные старше срока." >> /var/log/clean_iot_db.log

Плюсы такого подхода:

• Админка всегда показывает актуальные значения (не нужно ждать, пока датчики пришлют новые данные)
• История за последнюю неделю остаётся — удобно для анализа
• База не раздувается до 70 МБ

Минус: DELETE работает медленнее, чем TRUNCATE, особенно на больших таблицах. Но на Raspberry Pi с сотнями тысяч строк — разница в секунды, так что это не критично.

Если после массового DELETE хочешь физически уменьшить файл базы, можно добавить:

OPTIMIZE TABLE sensor_data;

Но учти: во время оптимизации таблица будет заблокирована для записи. Делай это в "тихий час", если система работает 24/7.

Заключение

Автоматическая очистка — простой и надёжный способ держать IoT-базу в форме. Скрипт можно адаптировать под любые таблицы: просто замени список в секции TRUNCATE.

Если у тебя есть свои фишки по оптимизации — делитесь в комментариях! А я, пожалуй, пойду проверю, не накопились ли снова логи…

Посетителей сегодня: 0

кто я | книга | контакты без контактов