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

[just2blue4u]

here the latest information:

First: The DB hasn't grown by now.
But that still doesn't say much, because of my second problem, which still keeps me frustrated:

As seen in my first post in this thread, the System Load always goes up and down (posted cpu usage graph). LoadAV(1 min) is above 3 when high and below 1 when low.
If the load is high, the Zabbix server begins sending alerts because he doesn't get back data from the agents (no data for 120s).

Because of this, i am forced to turn of monitoring for more than half of my clients.

here my memory stats:

             total       used       free     shared    buffers     cached
Mem:          2013       1901        112          0        201       1357
-/+ buffers/cache:        342       1671
Swap:         2047         36       2010

Guys, i still need some help here...
What can i do to find the source of that performance problems? I can't belive Zabbix or MySQL is it...

Best regards,
Heiner

[noxis]

here the latest information:

First: The DB hasn't grown by now.
But that still doesn't say much, because of my second problem, which still keeps me frustrated:

As seen in my first post in this thread, the System Load always goes up and down (posted cpu usage graph). LoadAV(1 min) is above 3 when high and below 1 when low.
If the load is high, the Zabbix server begins sending alerts because he doesn't get back data from the agents (no data for 120s).

Because of this, i am forced to turn of monitoring for more than half of my clients.

here my memory stats:

             total       used       free     shared    buffers     cached
Mem:          2013       1901        112          0        201       1357
-/+ buffers/cache:        342       1671
Swap:         2047         36       2010

Guys, i still need some help here...
What can i do to find the source of that performance problems? I can't belive Zabbix or MySQL is it...

Best regards,
Heiner

If i had to guess I would say those load spikes are the house keeper kicking off. In the server configuration you can set this period (we do it once every 24 hours).

How many pre-forked instances of pollers do you have? If things start to slow down polling will take longer and you can run out of them.

In regards to the mysql, are you now running with innodb file per table? For increased performance I would also looking into "innodb_flush_log_at_trx_commit" for your my.cnf. It can really increase the performance and for Zabbix where it doesn't matter if the odd bit of data isn't committed it is a good trade off.

[just2blue4u]

this is my my.cnf:

# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

default-storage-engine=innodb
# Jede Tabelle bekonnt ihre eigene Datei:
innodb_file_per_table

#innodb_buffer_pool_size=1800000k
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

i'm thinking about using the example config "my-innodb-heavy-4G.cnf", but as i only have 2GB RAM, i must modify it a bit, i think.

Thanks for your hints.
Housekeeping is commented out (set to default -> every hour (1)). if i set it higher, wouldn't affect that, that the ups and downs are only longer and less frequently? i think it doesn't shrink my heavy load.

Yes, i do file per table now (see config).

What i try next (means: "tomorrow") is setting innodb_flush_log_at_trx_commit to 0 or 2 (don't now right now what's better), thanks for this tip!

I'll post here as soon as i have new information. If someone has a tip how to config MySQL InnoDB on my System (Intel Pentium D 925, 3 GHz, 800MHz FSB, 2 x 1024 GB DDR SD 667MHz), don't be shy!

[noxis]

Hmm I wonder if commenting it out stops house keeping from running? I am sure I read somewhere you could stop house keeping.... it would certainly explain your continuing problem of running out of space.

[just2blue4u]

well- it was commented out by default (i compiled from sources), an as i wrote- i have the load peaks with that setting, so i don't think it is deactivated that way.

I'm now trying to modify the mysql-innodb-huge a bit and then load it. maybe the peaks will shrink then ...

[rreilly]

We had a similar issue, but was due to zabbix_server being moved from bin to sbin and the old version housekeeping was broken so even though we upgraded to (i think 1.4.2) it was still starting the old version because we did not replace the startup scripts...
Rob

[just2blue4u]

OK.
So now my System Load is really fine again.
What keeps frustrating me is my growing DB: 500MB/12h.

I am now testing if "mysqlcheck -Aoqp" solves that issue...

[skullone]

How big are your ib_logfile files getting?
If they are large, thats your problem, set the max log file size to something sane.

[Palmertree]

Make sure you have your innodb tables stored as files and not one flat file or it will not shrink when you run mysqlcheck with the optimize switch.

[globifrosch]

mysqldump with option --delete-master-logs

"filesystem growing very fast with zabbix" reminds me of a problem i had with MySQL.

Problem was the binlog of mysql. Since i start the mysqldump backup with the option "--delete-master-logs", everything is OK.

Don't know if this is related with your problem.

[just2blue4u]

Hi guys!
Here's my mysql-directory:

[root@server mysql-DBs]# du -hs *
35M     ibdata1
257M    ib_logfile0
257M    ib_logfile1
257M    ib_logfile2
24K     intra2dmz-2-slow.log
716K    mysql
4,0K    mysql-bin.000001
1,1G    mysql-bin.000002
1,1G    mysql-bin.000003
1,1G    mysql-bin.000004
1,1G    mysql-bin.000005
1,1G    mysql-bin.000006
1,1G    mysql-bin.000007
1,1G    mysql-bin.000008
189M    mysql-bin.000009
4,0K    mysql-bin.index
0       mysql.sock
4,0K    test
1,6G    zabbix

Yes, my binlogs are damn huge!

Thanks to your hint, i changed my cronjob to the following:

nice mysqldump --flush-logs --delete-master-logs --all-databases -pgeheim > /root/mysql-db-dump-imv.sql && echo "DB-Backup erstellt"|| echo "FEHLER BEIM ERSTELLEN DES DB-DUMP AUF INTRA2DMZ-2"

In /root/ the file is backuped to tape by amanda at 3 AM, so the backup is safe then.

I'll post here tomorrow...

best regards,
Heiner

[just2blue4u]

U got it

"filesystem growing very fast with zabbix" reminds me of a problem i had with MySQL.

Problem was the binlog of mysql. Since i start the mysqldump backup with the option "--delete-master-logs", everything is OK.

Don't know if this is related with your problem.

[root@intra2dmz-2 mysql-DBs]# du -hs *
35M ibdata1
257M ib_logfile0
257M ib_logfile1
257M ib_logfile2
28K intra2dmz-2-slow.log
716K mysql
4,0K mysql-bin.000001
510M mysql-bin.000002
4,0K mysql-bin.index
0 mysql.sock
4,0K test
1,7G zabbix
[root@intra2dmz-2 mysql-DBs]#

Baby, you got it!
I Think this is what made the fast growth!
Tomorrow i'll confirm if the daily growth (1GB/day) is really caused by the logs, but i can't belive it isn't!

So- big thanks for all who made me wiser and helped me here!!! Thanks alot! *kiss*

PS:
I'm still a little bit worrieg about my mysql-config (took the one called 4GB but only have 2GB RAM). someone who got some experience related to this situation may tell me!

[noxis]

If you don't need binlogs why not just turned them off?

[just2blue4u]

i didn't know i could do that so easily. Thought they'd be essential!

[globifrosch]

i didn't know i could do that so easily. Thought they'd be essential!

they're essential if you like to do "point in time" restores. With binlogs you can redo changes from the last backup until a specific time (specific time maybe: just before a accidental "DELETE FROM xyz")