We're really needing help now: zabbix DB is becoming way too big!

[just2blue4u]

Since Start of monitoring with zabbix 1.4.2, our DB is growing constantly (see here: http://www.heiwu.de/myfiles/drbddisc.jpg

We really need help now since we only have about 20GB Disk space left...

First my System:
zabbix server V1.4.2
running with
- Fedora Core 6 Linux
- mysql-server-5.0.27-1.fc6 / mysql-5.0.27-1.fc6 / php-mysql-5.1.6-3.6.fc6
- httpd-2.2.4-2.1.fc6 / php-5.1.6-3.6.fc6
on a DELL PE 860 with
- Intel Pentium D 925, 3 GHz, 800MHz FSB
- 2 x 1024 GB DDR SD 667MHz

We monitor:

Number of hosts (monitored/not monitored/templates/deleted)	71(45/0/26/0)
Number of items (monitored/disabled/not supported)[trapper]	4286(2406/1722/158)[0]
Number of triggers (enabled/disabled)[true/unknown/false]	1206(1068/138)[3/48/1017]
Number of events	436866
Number of alerts	17638

The Hardware is used for Zabbix and as a standby gateway, nothing else.

All DB data is stored in one file called ibdata1, so i think the DB Engine System is MySQL InnoDB.
PhpMyAdmin says, the Zabbix DB (scheme) within MySQL would be MyISAM and the Zabbix DB's single Tables would be InnoDB again. Seems very strange to me...

Mysql Administrator says, the Zabbix DB would only contain 9GB of Data and 10 GB Indexes.

Every night we backup the full zabbix DB into one sql file, which is now 634MB large.

The value cpu user time (in %) is also very strange: http://www.heiwu.de/myfiles/cpuuserzabbix.jpg
As i said: only Zabbix is on this server...

First i thought, just waiting for the Time when the DB reaches its final Size (or begins growing slower), would be ok, but now i get really scared. There MUST be something wrong.
So i copied the sql-backup onto another System, installed Zabbix Server v1.4.4 and put die data from the .sql file into its Mysql-DB (InnoDB).
The Data was ok (zabbix could use it) and the ibdata1 file was only 1,8GB large.

So- Why do i have a file larger than 100GB for 19GB of Data and indexes?
Is this a zabbix or a mysql problem?

Pleas help!
You may also contact me via ICQ, AIM or email!

best regards,
Heiner

[vaccinus]

Hi! Try "http://gentoo-wiki.com/HOWTO_Zabbix#MySQL"..

Hope, it helps

[Palmertree]

Use innodb_file_per_table to create seperate files for each table and run mysqlcheck ounce a week during a maintenance window with the optimize switch. Using this setup will allow your tables to shrink when records are deleted. Using a flat innodb file without file_per_table, the only way to shrink the database is to backup the database and restore everytime which is too time consuming.

[just2blue4u]

@ vaccinus:
What are you trying to tell me? Should i use BerkleyDB Engine within MySQL?

@ Palmer:
I'm testing your suggestion right now. Though i don't expect the problem to disappear, i think i we can get nearer to its source...

@ both:
Thanks for your help so far, i'll keep you informed.

@ all:
As this Problem still isn't solved: Whoever has another idea how to solve it please tell me!

[qix]

Maybe you need to see if your housekeeping is still working.
If not, your database will grow and grow....

[just2blue4u]

OK, guys, so here's my update:

I put the innodb_file_per_table into my.cfg and restarted mysqld.
Next, i copied the whole zabbix-db into a new one calles zabbix-neu:

mysqldump --single-transaction -v -q -ptesttest zabbix|mysql -ptesttest zabbix-neu

as far as i understood the man-pages, "single-transaction" is to get a better performance on reading innodb.
When the copy was done, i reconfigured my zabbix-server to work with the new DB, and finally restarted it. That was all on friday.

Today, my first action (after getting myself a hot cup of coffee ) was, to look at the Partition's size. Here it is: http://www.heiwu.de/myfiles/drbddisc-2.jpg
After some hours, the Filesize of the new zabbix DB began to rise again.

The good news is that my CPU Usage is now constantly about 10%, the up- and downs are gone. I think this is because of the really smaller filesize of the single DB-Files: http://www.heiwu.de/myfiles/cpuuserzabbix-2.jpg

@qix:
Thanks for the hint! But my housekeeping is turned on:
Housekeeper:
Do not keep actions older than (in days) 365
Do not keep events older than (in days) 365

The "keep history" value is 7, "keep trends" is 365 for all hosts.

This is the top 10 of DB-Table-Filesizes (in KB):

[root@zabbixserver zabbix-neu]# du -s *|sort -nr|head
1082404 history_uint.ibd
770804  history.ibd
291104  trends.ibd
200904  items.ibd
65604   events.ibd
27680   alerts.ibd
13332   history_str.ibd
10256   items_applications.ibd
804     triggers.ibd
564     functions.ibd

And here is some more Zabbix stats:


Please help me!
I'm now trying to shrink the old ibdata1 file, but that won't solve the problem of my constantly growing zabbix-db...
Question: I'm using v1.4.2. Is this a Problem that's fixed in v1.4.3 or .4?


Best regards (and thanks 4 reading),
Heiner

[Aly]

May be i'm wrong, but ibdata1 file contains all info about executed sql's which affected DB's. So generally you can backup all DB, delete everything (including ibdata1 file) and than restore DB.

may be, you can just delete ibdata1 file, without deleting and restoring.. I would try that.

P.S. For start just rename ibdata1 to something else if all goes OK than delete it.

[just2blue4u]

well, afaik the mysql-internal-tables are also stored in there...
i'm afraid of getting trouble because of that...

[Aly]

We hadn't

[just2blue4u]

this is what the docs say:

To decrease the size of your tablespace, use this procedure:
1. Use mysqldump to dump all your InnoDB tables.
2. Stop the server.
3. Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
4. Remove any .frm files for InnoDB tables.
5. Configure a new tablespace.
6. Restart the server.
7. Import the dump files.

(http://dev.mysql.com/doc/refman/5.0/...-removing.html)

I'm curious about starting the DB without any mysql tables...
Well- in a few munites we'll know...

[Tenzer]

Well- in a few munites we'll know...

Any news, or are you busy restoring a database?

[just2blue4u]

Thank you for being interested in this!

As expected, this didn't work that easy.
After creating a backup for the mysql-internal DB (and stopping the DB), i moved the whole DB-Folder and created an empty new one.
Then i copied the zabbix-db-folder into the new DB folder (archive mode).
I started the DB again and restored the db "mysql".
MySQL found the zabbix-DB, also its tables, but it said, the tables would not exist. Very weird.

After trying a lot more things, i resigned, and did the following:
- deinstall mysql-server software
- install it again (same version)
- restore the backed up db "mysql"
- restore zabbix-db (backup from today, 4.00AM, still restoring)

I'll let you all know if it worked.
I think if that works i can delete my old mysql-dir (including an ibdata1 with 111GB size)

Thanks so far...
Best regards,
Heiner

[Aly]

By deleting everything I meant only DB's. Better to just drop db's and manually delete ibdata1 file. Than restore. As I remember that's what we did in that situation.

[just2blue4u]

as posted before, this is what the docs say:

Remove all the existing tablespace files, including the ibdata and ib_log files

But nevertheless- i'm already playing the dumps back in, so i'll just wait for them to be finished and then hope all works great again!

[just2blue4u]

update:
The dumps are in, zabbix is started again and seems to work.

I don't think the growing of the database is stopped now, so i'll post here tomorrow, when i know more...