Template change vs. database size (MySQL)

[pol]

Good morning,

The question will be probably lame, but as a newbie I consider this as my last resort

I've been practicing with zabbix (zabbix 1.8.1, MySQL database, all Debian) to monitor 10 production servers. I have started with built in template for Linux (Template_Linux) for some time, but then I've tried to make my own template basing on the previous one, so I quite radically changed the values of history (everything down to 7 days), reduced number of items by 25% etc, and set housekeeping for 7 days instead of 365.

I expected by database to get smaller, but it did not happen. sum(data_length) sum(index_length) are still growing up. Size of ibdata1 file is is still growing.

The question is: Why is not database getting 'thinner' after template was switched to a 'thinner' one? How can I achieve that?

Thank you in advance for any advice and best regards

Pawel

[MrKen]

Sadly the database wont get 'thinner' just because you reduced what is going into the database now. The previously used tablespace doesn't get reclaimed.
The only way to make your db smaller is to dump it and re-import it.

MrKen

[pol]

Hi MrKen and thank you for so quick reply.

Could you give me rough idea (or the source where I could get some more knowledge) how this works

Does it mean that dumping and reimporting database will delete unused (or unnecessary, orphaned, whatever...) records?

Best regards

Pawel

[MrKen]

Hi,

My understanding is that you can optimize tables and this will reduce the size/tidy up the table(s). The only way to reduce the size of the ibdata file is to do a mysqldump and reload the db.

You can get more info about mysqldump or optimizing tables here
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
http://dev.mysql.com/doc/refman/5.1/...ize-table.html

This is the syntax that I use for dumping:

/usr/bin/mysqldump -u admin -p admin --single-transaction --quick --all-databases > dbdump.sql

and to feed it back in:

mysql -u admin -p admin < dbdump.sql &

If you need more info, you will find plenty of forum threads about mysql and database size issues.

MrKen

[pol]

Thanks a lot!

Pawel