Upgrade 1.8-2.0 c partitioning

[sersad]

Использую забикс 1.8.13 с партицированием
Как я описал в статье http://habrahabr.ru/post/120955/

Кратко партицирование идет по этим таблицам

Вот эти таблицы с делением по дням:

history
history_log
history_str
history_text
history_uint


и с делением по месяцам:

acknowledges
alerts
auditlog
events
service_alarms
trends
trends_unit

При попытке применить скрипт для апгреда БД вылетают ошибки про FOREIGN KEY
Вот пример:

mysql> ALTER TABLE auditlog ADD CONSTRAINT c_auditlog_1 FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE;
ERROR 1506 (HY000): Foreign key clause is not yet supported in conjunction with partitioning

Пока упоминание FOREIGN KEY в таблицах с патицированием закоментировал.
Как бы грамотнее сделать переезд на версию 2.0?

mysql5.5

[dotneft]

Закомментить все FOREIGN keys, которые ссылаются/используются на партиционированных таблицах. MySQL не поддерживает их в связки с партиционированием! http://dev.mysql.com/doc/refman/5.1/...mitations.html

[sersad]

dotneft, а как скажется отсутствие ключей FOREIGN на работе бд?

[dotneft]

никак... все равно используется партиционирование. В Zabbix mysql ключи настроены таким образом, что если удаляется родитель, то удалется и подчиненный. У тебя эту функцию будет выполнять партиционирование. Так что don't worry

[sersad]

Переехал на 2.0
с таблиц
acknowledges
alerts
auditlog
events
service_alarms
Убрал партицирование. В остальнов все работает нормально.

[dotneft]

Ну и зря... проще было не заводить foreign ключи на этих таблицах

[AndreyHammer]

sersad, а могли бы вы написать краткую инструкцию по апргейду с партичированием дял чайников в mysql? Буду очень благодарен

[sersad]

Ну и зря... проще было не заводить foreign ключи на этих таблицах

эти таблицы всего ничего, учитывая что оперативы 10G и 2 проца по 2 ядра на 64 битной оси.

acknowledges 1,599 	InnoDB 	utf8_bin 	464.0 KiB
alerts 	2,658 	InnoDB 	utf8_bin 	2.1 MiB
auditlog 	6,400 	InnoDB 	utf8_bin 	1.9 MiB
events 	~1,877,670 	InnoDB 	utf8_bin 	236.9 MiB 	
service_alarms 	7,583 	InnoDB 	utf8_bin 	880.0 KiB

Единственно это таблица events но её можно и сделать опать я партициями, делается на раз-два.

Нагрузка даже уменьшилась после обновления

top - 08:43:44 up 223 days, 20:45,  3 users,  load average: 0.33, 0.43, 0.44
Tasks: 266 total,   2 running, 264 sleeping,   0 stopped,   0 zombie
Cpu(s):  6.2%us,  1.4%sy,  0.0%ni, 92.0%id,  0.0%wa,  0.0%hi,  0.5%si,  0.0%st
Mem:  10261624k total,  9610564k used,   651060k free,   561748k buffers
Swap:  5855652k total,    99184k used,  5756468k free,  5293832k cached

Количество узлов сети (под наблюдением/без наблюдения/шаблоны)	740	637 / 21 / 82
Количество элементов данных (активных/деактивированых/не поддерживаются)	245415	119233 / 124604 / 1578
Количество триггеров (активированных/деактивированных)[проблема/неизвестно/ок]	12667	12176 / 491  [74 / 0 / 12102]
Количество пользователей (в сети)	38	6
Требуемое быстродействие сервера, новые значения в секунду	136.84	-

[sersad]

sersad, а могли бы вы написать краткую инструкцию по апргейду с партичированием дял чайников в mysql? Буду очень благодарен

Легко
Делаю копию рабочей БД версии 1.8

sudo mysql -e"create database zabbix2;"
sudo mysql -e"grant all privileges on zabbix2.* to zabbix;"
mysqldump -u root -p --single-transaction zabbix  > zabbix_23.05.2012.sql

заливаю туда дамп актуальной БД

Делаю отдельно копии некоторых таблиц

mysqldump --no-create-info --lock-tables zabbix acknowledges > 23.05.2012_acknowledges.sql
mysqldump --no-create-info --lock-tables zabbix alerts> 23.05.2012_alerts.sql
mysqldump --no-create-info --lock-tables zabbix auditlog > 23.05.2012_auditlog.sql
mysqldump --no-create-info --lock-tables zabbix events > 23.05.2012_events.sql
mysqldump --no-create-info --lock-tables zabbix service_alarms > 23.05.2012_service_alarms.sql

Убиваю таблицы

DROP TABLE `acknowledges`;
DROP TABLE `alerts`;
DROP TABLE `auditlog`;
DROP TABLE `events`;
DROP TABLE `service_alarms`;

Создаю таблицы по образцу версии 1.8

CREATE TABLE acknowledges (
        acknowledgeid           bigint unsigned         DEFAULT '0'     NOT NULL,
        userid          bigint unsigned         DEFAULT '0'     NOT NULL,
        eventid         bigint unsigned         DEFAULT '0'     NOT NULL,
        clock           integer         DEFAULT '0'     NOT NULL,
        message         varchar(255)            DEFAULT ''      NOT NULL,
        PRIMARY KEY (acknowledgeid)
) ENGINE=InnoDB;
CREATE INDEX acknowledges_1 on acknowledges (userid);
CREATE INDEX acknowledges_2 on acknowledges (eventid);
CREATE INDEX acknowledges_3 on acknowledges (clock);

CREATE TABLE alerts (
        alertid         bigint unsigned         DEFAULT '0'     NOT NULL,
        actionid                bigint unsigned         DEFAULT '0'     NOT NULL,
        eventid         bigint unsigned         DEFAULT '0'     NOT NULL,
        userid          bigint unsigned         DEFAULT '0'     NOT NULL,
        clock           integer         DEFAULT '0'     NOT NULL,
        mediatypeid             bigint unsigned         DEFAULT '0'     NOT NULL,
        sendto          varchar(100)            DEFAULT ''      NOT NULL,
        subject         varchar(255)            DEFAULT ''      NOT NULL,
        message         blob                    NOT NULL,
        status          integer         DEFAULT '0'     NOT NULL,
        retries         integer         DEFAULT '0'     NOT NULL,
        error           varchar(128)            DEFAULT ''      NOT NULL,
        nextcheck               integer         DEFAULT '0'     NOT NULL,
        esc_step                integer         DEFAULT '0'     NOT NULL,
        alerttype               integer         DEFAULT '0'     NOT NULL,
        PRIMARY KEY (alertid)
) ENGINE=InnoDB;
CREATE INDEX alerts_1 on alerts (actionid);
CREATE INDEX alerts_2 on alerts (clock);
CREATE INDEX alerts_3 on alerts (eventid);
CREATE INDEX alerts_4 on alerts (status,retries);
CREATE INDEX alerts_5 on alerts (mediatypeid);
CREATE INDEX alerts_6 on alerts (userid);

CREATE TABLE auditlog (
        auditid         bigint unsigned         DEFAULT '0'     NOT NULL,
        userid          bigint unsigned         DEFAULT '0'     NOT NULL,
        clock           integer         DEFAULT '0'     NOT NULL,
        action          integer         DEFAULT '0'     NOT NULL,
        resourcetype            integer         DEFAULT '0'     NOT NULL,
        details         varchar(128)            DEFAULT '0'     NOT NULL,
        ip              varchar(39)             DEFAULT ''      NOT NULL,
        resourceid              bigint unsigned         DEFAULT '0'     NOT NULL,
        resourcename            varchar(255)            DEFAULT ''      NOT NULL,
        PRIMARY KEY (auditid)
) ENGINE=InnoDB;
CREATE INDEX auditlog_1 on auditlog (userid,clock);
CREATE INDEX auditlog_2 on auditlog (clock);

CREATE TABLE events (
        eventid         bigint unsigned         DEFAULT '0'     NOT NULL,
        source          integer         DEFAULT '0'     NOT NULL,
        object          integer         DEFAULT '0'     NOT NULL,
        objectid                bigint unsigned         DEFAULT '0'     NOT NULL,
        clock           integer         DEFAULT '0'     NOT NULL,
        value           integer         DEFAULT '0'     NOT NULL,
        acknowledged            integer         DEFAULT '0'     NOT NULL,
        PRIMARY KEY (eventid)
) ENGINE=InnoDB;
CREATE INDEX events_1 on events (object,objectid,eventid);
CREATE INDEX events_2 on events (clock);

CREATE TABLE service_alarms (
        servicealarmid          bigint unsigned         DEFAULT '0'     NOT NULL,
        serviceid               bigint unsigned         DEFAULT '0'     NOT NULL,
        clock           integer         DEFAULT '0'     NOT NULL,
        value           integer         DEFAULT '0'     NOT NULL,
        PRIMARY KEY (servicealarmid)
) ENGINE=InnoDB;
CREATE INDEX service_alarms_1 on service_alarms (serviceid,clock);
CREATE INDEX service_alarms_2 on service_alarms (clock);

Закидываю инфу в эти таблицы обратно

mysql -u root -p  zabbix2 < 23.05.2012_acknowledges.sql
mysql -u root -p  zabbix2 < 23.05.2012_alerts.sql
mysql -u root -p  zabbix2 < 23.05.2012_auditlog.sql
mysql -u root -p  zabbix2 < 23.05.2012_events.sql
mysql -u root -p  zabbix2 < 23.05.2012_service_alarms.sql

Патчим БД

 mysql -v -v -v -f -u root -p zabbix2 < patch.sql

Если хотим новые картинки открываем

database/mysql/images.sql

правим там imageid на не занятые номера
реальные занятые номера в БД можно посмотреть так

mysql> select imageid,name from images order by imageid;

Заливаем кратинки в БД

mysql -u root -p zabbix2 < images.sql

А далее все по инструкции.
Внимательно! Расположение файлов конфига сменилось на /usr/local/etc/

[AndreyHammer]

Благодарю!

[vvlad]

никак... все равно используется партиционирование. В Zabbix mysql ключи настроены таким образом, что если удаляется родитель, то удалется и подчиненный. У тебя эту функцию будет выполнять партиционирование. Так что don't worry

Это каким таким чудесным механизмом партиционирование будет обеспечивать логическую целостность данных?

Как я понял, в версии 2.x разработчики решили переложить эту задачу на СУБД. Соответственно, без FOREIGN-ключей БД не узнает о взаимосвязях "родитель-подчиненный" в таблице и поддерживать эту самую целостность не будет. И, как результат, удалив, к примеру из системы узел с историей, мы получим кучу висящих записей в таблицах history, history_uint, trends, trends_uint и проч.

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

[dotneft]

1. Логическую целостность партиционирование не обеспечивает это бесспорно.
2. Тут вы абсолютно правы. Но не все таблицы истории получили foreign keys. Например, таблица alerts не получила
3. Мусор будет, но это вынужденная мера. Рассмотрим ситуацию когда имеется наполнение 10-20ГБ в месяц (а если в день?;-) ). Вы будете чистить устаревшие данные из таблицы простым DELETE? Не думаю, поэтому и мусор и ДЕЙСТВИТЕЛЬНО устаревшие данные логичнее удалять с помощью партиционирования.

Вот вам пример huge installation:
-rw-rw---- 1 mysql mysql 29G Июл 11 00:15 history_uint#P#p2012_07_10.ibd
-rw-rw---- 1 mysql mysql 14G Июл 11 11:15 history_uint#P#p2012_07_11.ibd
-rw-rw---- 1 mysql mysql 28K Июл 11 02:00 history_uint#P#p2012_07_12.ibd

В итоге на пару метров мусора мне глубоко пофиг

[vvlad]

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

[sersad]

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

Именно! Удобнее и быстрее по крону килять партицию и добавять новые чем отдавать все это на хаускипер.
Если уж такая параноя на муссор можно написать триггеры которые будут удалять "мусор" вместе с удаленными итемами
Ну или написать запрос который будет подчищать таблицы за удаленными итемами.

[dotneft]

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

это не все элементы данных есть и str, есть и log, есть и попросту таблица history.
Дисковое место дешевле, чем процессорные ресурсы, которые бы были потрачены на DELETE запросы.