Zabbix database grows too large

Asked by Claude Durocher on 2015-09-30

After running Fuel 6.1 with Zabbix for 3 months, the mysql database grows very large (around 100 GB). I have found that house keeping options can be changed via the Zabbix GUI but i still need to do a cleanup manually of the database.

I tried to stop the zabbix service with "crm resource stop p_zabbix-server" but strangely enough, this also kills the mysql service!

I have 2 questions:

1-how do I stop the zabbix server so I can connect to mysql and clean manually the db?
2-I don't think that purging the rows in the db will actually shrink the space used in mysql: how can I shrink the zabbix DB?

Question information

Language:
English Edit question
Status:
Solved
For:
Fuel for OpenStack Edit question
Assignee:
No assignee Edit question
Solved by:
Claude Durocher
Solved:
2015-10-01
Last query:
2015-10-01
Last reply:
Claude Durocher (claude-d) said : #1

I will reply to myself here.

There was a bug in pacemaker for the zabbix configuration. I had to change "colocation vip_management-with-zabbix-server inf: vip__management p_zabbix-server" to "colocation zabbix-server-with-vip_management inf: p_zabbix-server vip__management" (by doing a "crm configure edit"). After that change, stopping and starting the zabbix service with "crm resource..." works fine.

Now, for the cleanup of zabbix tables, it was a so big (around 100 Gb in size) that I decided to remove the whole zabbix history by issuing the following commands :

crm resource stop p_zabbix-server
mysql --host 192.168.0.2 -u zabbix -p # password in /etc/zabbix/zabbix_server.conf

USE zabbix;
# create new empty zabbix tables
CREATE TABLE history_new LIKE history;
CREATE TABLE history_uint_new LIKE history_uint;
CREATE TABLE history_str_new LIKE history_str;
CREATE TABLE history_log_new LIKE history_log;
CREATE TABLE history_text_new LIKE history_text;
CREATE TABLE alerts_new LIKE alerts;
CREATE TABLE trends_new LIKE trends;
CREATE TABLE trends_uint_new LIKE trends_uint;
# drop old zabbix tables (this frees disk space)
DROP TABLE history;
DROP TABLE history_uint;
DROP TABLE history_str;
DROP TABLE history_log;
DROP TABLE history_text;
DROP TABLE alerts;
DROP TABLE trends;
DROP TABLE trends_uint;
# rename new tables to old name
ALTER TABLE history_new RENAME history;
ALTER TABLE history_uint_new RENAME history_uint;
ALTER TABLE history_str_new RENAME history_str;
ALTER TABLE history_log_new RENAME history_log;
ALTER TABLE history_text_new RENAME history_text;
ALTER TABLE alerts_new RENAME alerts;
ALTER TABLE trends_new RENAME trends;
ALTER TABLE trends_uint_new RENAME trends_ui;
#
EXIT

crm resource start p_zabbix-server

I also changed housekeeping parameters son less history will be kept in zabbix (http://your_zabbix_server/zabbix/adm.housekeeper.php).

Claude Durocher (claude-d) said : #2

Note.

ALTER TABLE trends_uint_new RENAME trends_ui;

should be

ALTER TABLE trends_uint_new RENAME trends_uint;