Как сжать / очистить файл ibdata1 в mysql

Введение

В своей инструкции по установке и настройке zabbix я вообще не затрагиваю вопрос базы данных mysql или производительности сервера в целом. Я просто беру дефолтные настройки mariadb, которые идут с установкой и использую их. Когда у вас не очень большая инфраструктура на мониторинге этого вполне достаточно, чтобы нормально пользоваться системой.

Если вы активно используете zabbix и внедряете его повсеместно во все используемые системы (а я рекомендую так делать), то вы рано или поздно столкнетесь с вопросом производительности системы мониторинга и размера базы данных zabbix.

Тема производительности zabbix очень индивидуальная. Она напрямую зависит от того, как вы его используете, а схемы мониторинга могут быть очень разные. Одно дело мониторить несколько серверов, а другое дело нагруженные свичи на 48 портов со съемом метрик с каждого порта раз в 30 секунд.

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

Очистка и уменьшение mysql базы zabbix

Начнем с очистки базы данных zabbix от ненужных данных. Рассмотрим по пунктам в той последовательности, в которой это нужно делать.

Первым делом надо внимательно просмотреть все используемые шаблоны и отключить там все, что вам не нужно. Например, если вам не нужен мониторинг сетевых соединений windows, обязательно отключите автообнаружение сетевых интерфейсов. Оно само по себе находит десятки виртуальных соединений, которые возвращают нули при опросе и не представляют никакой ценности. Тем не менее, все эти данные собираются и хранятся, создавая лишнюю нагрузку. Если же вам нужен мониторинг сети в windows, зайдите в каждый хост и отключите руками лишние адаптеры, которые будут найдены. Этим вы существенно уменьшите нагрузку. По моему опыту, в стандартных шаблонах windows мониторинг всех сетевых интерфейсов дает примерно 2/3 всей нагрузки этих шаблонов.
Отредактируйте в используемых стандартных шаблонах время опроса и хранения данных. Возможно вам не нужна та частота опроса и время хранения, которые заданы. Там достаточно большие интервалы хранения. Чаще всего их можно уменьшить. В целом, не забывайте в своих шаблонах ставить адекватные реальной необходимости параметры. Не нужно хранить годами информацию, которая теряет актуальность уже через неделю.
После отключения лишних элементов в шаблонах, нужно очистить базу данных от значений неактивных итемов. По-умолчанию, они там остаются. Можно их очистить через web интерфейс, но это плохая идея, так как неудобно и очень долго. Чаще всего попытки очистить 50-100 элементов за раз будут сопровождаться ошибками таймаута или зависанием web интерфейса. Далее расскажу, как это сделать эффективнее.

Для очистки базы данных zabbix от значений неактивных итемов, можно воспользоваться следующими запросами. Запускать их можно как в консоли mysql сервера (максимально быстрый вариант), так и в phpmyadmin, кому как удобнее.

Данными запросами можно прикинуть, сколько данных будет очищено:

SELECT count(itemid) AS history FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS history_uint FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS history_str FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS history_text FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS history_log FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS trends FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
SELECT count(itemid) AS trends_uint FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

Если запросы нормально отрабатывают и возвращают счетчик данных, которые будут удалены, дальше можете их удалять из базы следующими sql запросами.

DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');
DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items WHERE status='0');

Данными запросами вы очистите базу данных zabbix от значений неактивных элементов данных. Но реально размер базы данных у вас не уменьшится, потому что в дефолтной настройке базы данных используется формат innodb. Все данные хранятся в файле ibdata1, который автоматически не очищается после удаления данных из базы.

Администрировать такую базу неудобно. Нужно как минимум настроить хранение каждой таблицы в отдельном файле. Этим мы далее и займемся, а заодно обновим сервер базы данных до свежей версии mariadb и реально очистим базу, уменьшив ее размер.

Информация

Поскольку вы, возможно, ищете решение для своей проблемы, вы, вероятно, заметили, как трудно найти что-то, что работает, в зависимости от вашего случая, некоторые вещи могут не подойти вам. Вот почему мы также перечислим больше статей, которые могут быть полезны для вас, и дадут вам представление о том, как решить вашу проблему (в случае, если это действительно возможно, в противном случае резервное копирование будет вашим единственным спасением):

Восстановление поврежденной таблицы InnoDB.
Как восстановить InnoDB MySQL файлы с помощью MAMP на Mac.
Как восстановить данные из файлов InnoDB IDB и FRM.
Восстановить таблицу из файлов frm и idb в InnoDB.

Счастливого выздоровления!

2) Обработка базы при помощи pgtoolkit:

pgtoolkit — инструмент для уменьшения раздувания таблиц и индексов без тяжелых блокировок и полной перестройки таблицы (https://github.com/grayhemp/pgtoolkit)

Непосредственно запуск:

time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -y 10 --reindex

#Или по очереди:

time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -t alerts -y 10 --reindex
time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -t acknowledges -y 10 --reindex
time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -t events -y 10 --reindex
time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -t history -y 10 --reindex
time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -t history_uint -y 10 --reindex
time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -t history_str -y 10 --reindex
time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -t history_text -y 10 --reindex
time sudo -u postgres /opt/pgtoolkit/bin/pgcompact -v info -d zabbix -t history_log -y 10 --reindex

Иногда может возникнуть необходимость проверить и удалить временные индексы, оставшиеся от работы pgcompact:

SELECT * FROM pg_indexes WHERE indexname LIKE '%pgcompact%';
 
 
 schemaname |    tablename    |       indexname       | tablespace |                                           indexdef
------------+-----------------+-----------------------+------------+-----------------------------------------------------------------------------------------------
 public     | hostmacro       | pgcompact_index_12057 |            | CREATE UNIQUE INDEX pgcompact_index_12057 ON hostmacro USING btree (hostid, macro)
 public     | graph_discovery | pgcompact_index_29490 |            | CREATE UNIQUE INDEX pgcompact_index_29490 ON graph_discovery USING btree (graphid)
 public     | hosts_templates | pgcompact_index_4305  |            | CREATE UNIQUE INDEX pgcompact_index_4305 ON hosts_templates USING btree (hostid, templateid)
 public     | hosts_templates | pgcompact_index_21016 |            | CREATE UNIQUE INDEX pgcompact_index_21016 ON hosts_templates USING btree (hostid, templateid)
 
DROP INDEX pgcompact_index_12057;
DROP INDEX pgcompact_index_29490;
DROP INDEX pgcompact_index_4305;
DROP INDEX pgcompact_index_21016;

Результат работы можно увидеть на графиках.

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

Чтобы не пропускать переполнение диска, рекомендуется добавить задание в cron:

sudo -u postgres /usr/bin/flock -w 0 /var/run/postgresql/backup_zabbix_psql.lock /usr/bin/psql -A -R ' : ' -P 'footer=off' zabbix

Если у вас mysql:

При использовании движка базы данных InnoDB, все таблицы и индексы хранятся в системном табличном пространстве (в одном файле /var/lib/mysql/ibdata).

Для того, чтобы хранить каждую таблицу InnoDB и связанные индексы в отдельных файлах — нужно активировать опцию innodb_file_per_table.

Дамп базы данных

Используя командную строку, переключитесь на каталог двоичных файлов MySQL при установке Xampp, обычно :

И запустите консоль mysql с правами root или любого другого пользователя, которого вы использовали при установке Xampp:

Заметка

Важно войти в систему как root, чтобы вы могли видеть все базы данных, доступные на движке. Это откроет консоль mysql, это означает, что вы можете запускать запросы, в частности тот, который перечисляет все базы данных, доступные на движке ():

Это откроет консоль mysql, это означает, что вы можете запускать запросы, в частности тот, который перечисляет все базы данных, доступные на движке ():

Наконец, выйдите из процесса mysql в командном процессе и начните с экспорта ваших баз данных с помощью :

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

Сделайте копию содержимого MySQL / данных вашего сервера

Первый шаг, очевидно, имеет копия папки данных MySQL, которую вы хотите восстановить в XAMPP (например, / var / lib / mysql в Linux). Мы предполагаем, что у вас есть все содержимое папки данных mysql, которая не работает на вашем сервере, и мы постараемся восстановить ее.

Эти данные имеют формат папок с именем каждой из ваших баз данных, внутри этих папок вы найдете файлы IDB и FRM, которые теоретически содержат данные ваших баз данных, и вы сможете восстановить их, если они не повреждены. :

Заметка

Также создайте резервную копию исходных данных в папке данных Xampp, на случай, если вы хотите, чтобы все работало, прежде чем начинать работу с этим руководством.

Получив эти данные, перейдите к следующему шагу.

Добавить контент в локальную папку mysql / data

Следующим шагом вам нужно создать резервную копию вашей папки mysql / data в xampp на случай, если что-то не получится, поэтому вам нужно будет использовать только старое содержимое mysql / data, и все снова будет нормально. В локальной папке mysql / data XAMPP вам нужно будет удалить весь исходный контент, кроме следующих каталогов (они не могут измениться, поэтому обязательно удалите эти каталоги из данных вашего сервера):

MySQL (каталог)
mysql_upgrade_info (файл)
performance_schema (каталог)

Затем добавьте содержимое с вашего сервера (шаг 1), не заменяя ранее упомянутые файлы и каталоги.

1 ответ

Лучший ответ

Кажется, проблема была исправлена, все еще не уверен, ПОЧЕМУ это произошло.

Чтобы исправить проблему, я сначала экспортировал все необходимые базы данных, затем я удалил все файлы из папки MySQL\Data \, за исключением стандартных/стандартных файлов, которые поставляются с новой установкой WAMP.

(файлы по умолчанию: auto.cnf, performance_schema (папка) и mysql (папка))

Затем я только что повторно открыл phpmyadmin и заново создал все базы данных и импортировал все файлы базы данных (структура и данные).

Делая все это, я избавился от ALOT файлов ib_logfiles и mysql-bin (таких файлов, как эти http://imgur.com/HA3VOFb), и проблема теперь решена.

Мои знания о MySQL/ib_logfiles несколько ограничены, поэтому я не знаю, почему все эти файлы были там, и почему они вызвали проблему, которую они сделали, но я рад, что это исправлено.

28 нояб. 2014, в 08:22
Поделиться

Ещё вопросы

Реализация нейронных сетей с использованием C против C ++?
1При загрузке через YouTube API с PHP видеофайл застрял на 0%
PHP определяет числа между двумя числами, а затем запрашивает
2Сертификаты WCF не устанавливаются для пользовательских учетных данных
Заменить часть значения параметра объекта значением из якоря?
Как выполнить метод JavaScript при нажатии на якорь?
Назначение возвращенного указателя другому возвращенному указателю?
Получить значение API автозаполнения Google при загрузке страницы
Несколько абстрактных взглядов — Ионные
1Hibernate выбирает коллекцию в виде столбца в одном запросе
1Как скачать файл с FTP с помощью Java?
Как это колесо меню получает название
2SmtpClient отправляет электронную почту на спам
AngularJS Ng-route — переключение на определенный вид страницы при наборе ввода: текст с другого URL
Возникли проблемы с выполнением SQL-запросов
1Как изолировать привязки JNA, специфичные для платформы?
1Python ORM для массивного набора данных
1Проверка наличия любой переменной в объекте null / undefined / emptyString (»)
1Android: Как изменить ImageButton в TabActivity на основе выбора вкладки
1Использование gae-сессий между доменами
MySQL Cluster 7.1 в Windows 2008 с несколькими узлами управления
Как получить доступ к переменной области видимости во встроенном javascript внутри html-страницы в тимилиф и использовать ее для изменения атрибута css
1Как изменить элементы адаптера извне?
1Apple / Android Сторонние Торговые Услуги
Угловая директива не вызывается из сервиса?
Отображение: таблица + отображение: ячейка таблицы выполняет выравнивание по вертикали, но элементы div имеют бессмысленную ширину
Visual C ++ реализует std :: unordered_map только с одним std :: list?
1Как выделить часть текста, используя метод выбора?
1Выскабливание отзывов пользователей с imdb в python
Вывод квадратной формы C ++ с использованием ‘*’
1Я не могу получить доступ к phpMyAdmin
2Превратите N-арный B-сплайн в последовательность квадратичных или кубических B-сплайнов
1Как удалить конкретный файл из папки в JAVA, не удаляя саму папку?
1как подключиться к удаленному mssql с помощью php
1Конкатенация строк в Python
HTML-тег <img>, ссылающийся на изображение в каталогах, не работает
2Как мне преобразовать символ в код ключа в .Net?
1Реализация OnClickListener в проекте Android с помощью Ormlite
JQuery-код не отвечает
Проблема PHP и MySQL
Bootstrap 3 Навигация многоуровневый выпадающий примерно 5 шаг
1Проблемы с типом выборки данных в спящем режиме
На второй вкладке плагин jPages не работает пагинация
2Полоса прокрутки в <asp: CheckBoxList>?
1Regex для номера автомобиля в Сингапуре
1Этот код хорошо работает для проверки загрузки и расширения, но не когда файл уже существует
1Пустые строки в списке массивов при использовании getTextContent ()
Невозможно установить отрицательное поле в методе jQuery css
1Могу ли я изменить CSS значка маркера на событии (сделать его больше) с помощью листовки
Выполнять функцию jQuery только на определенной ширине окна (даже с изменением размера)

1) Запросы для чистки базы:

-- keep 1 week of history and 3 months of trends
\SET history_interval 7
\SET trends_interval 90
 
DELETE FROM alerts WHERE age(to_timestamp(alerts.clock)) > (:history_interval * INTERVAL '1 day');
 
DELETE FROM acknowledges WHERE age(to_timestamp(acknowledges.clock)) > (:history_interval * INTERVAL '1 day');
 
DELETE FROM events WHERE age(to_timestamp(events.clock)) > (:history_interval * INTERVAL '1 day');
 
DELETE FROM history WHERE age(to_timestamp(history.clock)) > (:history_interval * INTERVAL '1 day');
DELETE FROM history_uint WHERE age(to_timestamp(history_uint.clock)) > (:history_interval * INTERVAL '1 day') ;
DELETE FROM history_str WHERE age(to_timestamp(history_str.clock)) > (:history_interval * INTERVAL '1 day') ;
DELETE FROM history_text WHERE age(to_timestamp(history_text.clock)) > (:history_interval * INTERVAL '1 day') ;
DELETE FROM history_log WHERE age(to_timestamp(history_log.clock)) > (:history_interval * INTERVAL '1 day') ;
 
DELETE FROM trends WHERE age(to_timestamp(trends.clock)) > (:trends_interval * INTERVAL '1 day');
DELETE FROM trends_uint WHERE age(to_timestamp(trends_uint.clock)) > (:trends_interval * INTERVAL '1 day') ;

Непосредственно запуск:

time sudo -u postgres psql -A -R ' : ' -P 'footer=off' zabbix < delete-old-data.pg.sql

Как спланировать нагрузку на Zabbix

Под небольшой структурой, упомянутой в начале, я подразумеваю 50-100 узлов (не сетевое оборудование с десятками портов) сети на мониторинге и примерно 2000-4000 активных элементов данных, которые записывают 20-40 новых значений в секунду. Под такую сеть вам будет достаточно небольшой виртуальной машины с 2 ядрами и 4 гб памяти. База данных на преимущественно стандартных шаблонах будет расти примерно на 2-4 Гб в год. Дальше еще меньше, так как будет автоматически очищаться.

Для мониторинга такой сети можно вообще не выполнять никаких дополнительных настроек. Мониторинг будет вполне нормально работать. Если же нагрузка начнет расти, то первое, с чем вы столкнетесь — это с размером и производительностью базы данных. База zabbix будет расти пропорционально подключению к ней хостов. И с этим придется что-то делать.

Для решения вопроса производительности нужно будет двигаться в двух направлениях: