InnoDB : ibdata growing growing and growing !

[n0mad44]

Hi,

I'm using zabbix with mysql 5.0.22 and InnoDB (as recommended). However my ibdata is growing every day (insteed of housekeeper and other regulation processes).

I read that's due to InnoDB : it's not able to reuse freed space (or something like that )

For now the only solution I have is to dump my base, stop mysql, erase ibdata and restore the dump.

Have you any information/trick to make my ibdata smaller ? I don't think I'm the only one with this problem.. how do you do ?

Thanks,
n0mad.

[eli.stair]

Setting InnoDB 'file per table'

You're looking for 'innodb_file_per_table' in my.cnf. You'll find more detailed info on the mysql site when you search for it.

Cheers,

/eli

[Niels]

You're right that the file wont shrink, and even innodb_file_per_table wont help you with that. I still like to use it to keep the file system more manageable, but that's personal taste.

The trick you're looking for is what you've already stated: dump and restore.

You _may_ be able to change some tables from innodb to myisam, but that's a lot of bother with unknown consequences.

My advice is: Buy large discs, dump and restore if needed.

[just2blue4u]

i had a similar issue.
Already fount this thread? http://www.zabbix.com/forum/showthread.php?t=8641

[pierre-hoffmann]

Hi,

However my ibdata is growing every day

Make an

mysql zabbix -u root -p
OPTIMIZE TABLE xxxx

on each table this defrag tablespace but lock table ...

Regards,
Pierre.

[xs-]

http://www.zabbix.com/forum/showthread.php?t=9925

[just2blue4u]

here's what i do:

[root@server ~]# crontab -l
# taeglich: MySQL-Backup aller DBs
0 0 * * * nice mysqldump --flush-logs --delete-master-logs --all-databases -p'geheim' > /root/mysql-db-dump-imv.sql && echo "DB-Backup erstellt"|| echo "FEHLER BEIM ERSTELLEN DES DB-DUMP"

# Montags morgens: MySQL-DBs optimieren
0 5 * * 1 /etc/init.d/zabbix_server stop && mysqlcheck -Aoqp'geheim' && /etc/init.d/zabbix_server start || echo "FEHLER BEIM CHECKEN DER MYSQL-DBs"
[root@server ~]#

[Jason]

Is is possible to run the mysqlcheck -o without it crashing zabbix_server? I really don't want to restart zabbix_server often as it resets all my triggers on stuff such as event logs etc...

I'd ideally like to run it as a nightly cron script, but can't if it means a zabbix_server restart