Как уменьшить базу логов Squid в MySQL
В связке Squid + MySQL + SAMS статистика трафика пишется в таблицу cache, которая со временем разрастается до гигабайтов и может повредиться. Разбираем, как восстановить таблицу, удалить старые записи и не допустить повторения.
Откуда берётся проблема
Система учёта трафика SAMS (Squid Account Management System) складывает каждую запись из журнала Squid в базу MySQL, по умолчанию — в таблицу cache базы squidlog. При сотне пользователей это миллионы строк в месяц. Если базу никто не обслуживает, получается несколько неприятных эффектов:
- веб-интерфейс SAMS начинает тормозить на отчётах;
- раздел с базой заканчивается;
- таблица (в старых установках это почти всегда MyISAM) повреждается при аварийной остановке или нехватке места.
В последнем случае в журнале MySQL и SAMS появляется сообщение вроде:
Table './squidlog/cache' is marked as crashed and last (automatic?) repair failed
Удалить записи через интерфейс SAMS в такой ситуации уже не получится — нужно работать с базой напрямую.
Сначала — резервная копия и место на диске
Перед любыми операциями проверьте свободное место: ремонт и оптимизация таблицы создают временную копию, которой нужно примерно столько же места, сколько занимает сама таблица.
df -h /var/lib/mysql
du -sh /var/lib/mysql/squidlog
Если статистика нужна, сохраните её до чистки (на другой раздел или сервер):
mysqldump -u root -p squidlog cache | gzip > /backup/squidlog-cache.sql.gz
Ремонт таблицы
Подключаемся к MySQL и пробуем восстановить таблицу:
-- mysql -u root -p
USE squidlog;
CHECK TABLE cache;
REPAIR TABLE cache;
Если REPAIR TABLE не справился, можно восстановить MyISAM-таблицу утилитой myisamchk. Делать это нужно при остановленном MySQL, иначе таблица повредится ещё сильнее:
systemctl stop mysql
myisamchk -r /var/lib/mysql/squidlog/cache.MYI
# если не помогло — более медленный и тщательный режим
myisamchk -o /var/lib/mysql/squidlog/cache.MYI
systemctl start mysql
Удаление старых записей
После восстановления удаляем статистику старше нужной даты. Название столбца с датой зависит от версии SAMS — посмотрите структуру таблицы:
DESCRIBE cache;
Затем удаляем, например, всё до 1 сентября:
DELETE FROM cache WHERE date < '2013-09-01';
На очень больших таблицах одна большая операция DELETE может идти часами и держать блокировку. Удобнее удалять порциями:
DELETE FROM cache WHERE date < '2013-09-01' LIMIT 100000;
Запрос повторяют, пока он не вернёт 0 rows affected.
Если статистика не нужна совсем, таблицу можно очистить целиком — это самый быстрый способ, но данные пропадут безвозвратно:
TRUNCATE TABLE cache;
Освобождение места
DELETE не уменьшает файл таблицы на диске: строки лишь помечаются удалёнными. Чтобы вернуть место и дефрагментировать индексы, выполните:
OPTIMIZE TABLE cache;
Для InnoDB эта команда пересоздаёт таблицу. Место вернётся операционной системе, только если включён параметр innodb_file_per_table (в современных версиях MySQL и MariaDB он включён по умолчанию); иначе данные живут в общем файле ibdata1, который не сжимается.
Автоматическая очистка
Чтобы не доводить базу до аварии, удаляйте старые записи по расписанию. Создайте отдельного пользователя MySQL с правами только на эту базу, а учётные данные положите в файл ~/.my.cnf с правами 600:
[client]
user=squidclean
password=СложныйПароль
Задание в crontab, которое раз в месяц удаляет записи старше полугода:
0 4 1 * * /usr/bin/mysql squidlog -e "DELETE FROM cache WHERE date < DATE_SUB(CURDATE(), INTERVAL 6 MONTH); OPTIMIZE TABLE cache;"
Подробнее о синтаксисе расписаний — в статье о настройке cron в Linux.
Актуальное положение дел
SAMS2 давно не развивается, а последние выпуски рассчитаны на PHP 5, который не поддерживается современными дистрибутивами. Сам Squid жив и обновляется, но для учёта трафика сегодня чаще используют анализаторы журналов, например SARG или LightSquid, либо выгрузку журналов в системы вроде Graylog или ELK. Если база учёта всё-таки нужна, переведите её таблицы на InnoDB — она гораздо устойчивее к сбоям, чем MyISAM.
Как была устроена сама связка Squid и SAMS2, описано в статье о настройке прокси-сервера Squid + SAMS2 на Ubuntu.
Частые вопросы
Что значит ошибка «is marked as crashed and last (automatic?) repair failed»?
Таблица MyISAM повреждена, а автоматическое восстановление не сработало, часто из-за нехватки места на диске. Освободите место и выполните REPAIR TABLE или myisamchk при остановленном MySQL.
Почему после DELETE файл базы не уменьшился?
DELETE только помечает строки удалёнными. Чтобы вернуть место на диск, выполните OPTIMIZE TABLE. Для InnoDB без innodb_file_per_table место в общем файле ibdata1 не освобождается вовсе.
Чем TRUNCATE отличается от DELETE без условия?
TRUNCATE пересоздаёт таблицу целиком: это быстро и сразу освобождает место, но удаляет всю статистику и не может быть отменено. DELETE удаляет строки по одной и позволяет задать условие по дате.
Как часто чистить статистику?
Зависит от числа пользователей и требований к отчётам. Обычно хранят 3–12 месяцев, а старые данные удаляют ежемесячно по расписанию.