MySQL weirdness...

[r0k5t4r]

Hi,

we are running zabbix and MySQL on one server. The performance is very good but disk is filling up very quickly. I have looked at many posts on the forum but never really found a solution for the rapidly growing mysql database.

What I have done so far is changed to innodb_file_per_table.

When I first dumped the whole mysql databases, the dump was about 27gb. After I restored the dump the directory grew immediately to almost 50gb!?

I thought that when dumping and restoring the db the space in the database is freed up? I have set housekeeper to run once every 24 hours and set:

Do not keep actions older than (in days) 7
Do not keep events older than (in days) 7

The database is growing and growing and growing....

What am I missing here?

[heaje]

The size increase could be because of multiple things.

1. What intervals are you collecting items at? The smaller the interval, the more data that is collected. This results in a size increase.

2. When you say the disk is filling up, are you just referring to the actual DB files or are you referring to the entire mysql install? I ask because you could easily have binary logging enabled. That would easily cause a constant increase in disk usage with Zabbix because changes are constantly made to the database. This would ESPECIALLY be the case if you imported a dump. The binary logs are updated for every change the dump file causes. One day of binary logs for our installation equals 52.2GB of binary logs (198nvps and 431 hosts).

3. How many hosts are you monitoring? The more hosts you monitor, the larger the database will be.


Due to the way that MySQL works, your change to innodb_file_per_table may not have done what you think it did.

1. By default, MySQL uses ibdata1 as the tablespace for all INNODB tables. That file will never decrease in size, even if you drop tables/databases. I assume this is the file that was the largest before you changed to innodb_file_per_table.

2. After changing to innodb_file_per_table, you must rebuild the database (or alter the tables in some way) to take advantage of the change. Based on what you said, it sounds like you just re-created the database from a dump. That caused the DB to be recreated with the new setting, however, this does not affect the ibdata1 file that I mentioned above. That means that you now have a large ibdata file AND the large files from the innodb_file_per_table change.

3. To fix this problem, you would need to take a new database dump, drop the current zabbix DB, stop MySQL, delete ibdata1 and all ib_logfile* files, and then start MySQL back up.

4. Doing the steps in #3 will re-create the ibdata1 tablespace to the size you have specified in your config. In this way you "forced" the ibdata file to decrease in size.

5. Re-import your DB dump. Now the only files taking up a large amount of space should be the Zabbix DB files themselves.

6. Remember that innodb table spaces can only grow, they CANNOT shrink.

[lagos.mc]

db2_fetch error on dashboard.

My dashboard on zabbix 2.0 display the followinb error :

db2_fetch_assoc() [<a href='function.db2-fetch-assoc'>function.db2-fetch-assoc</a>]: Fetch Failure [include/db.inc.php:584]


anyone may you help me?

[r0k5t4r]

Hi,

thanks for the response. I think I actually have rebuild the whole mysql thing after setting innodb_file_per_table.

Looking at the mysql folder the ibdata file is just about 51Mbytes and only the zabbix folder holds large files:

56756500        zabbix
51204   ibdata1
11232   mysql
5120    ib_logfile1
5120    ib_logfile0

Binary logs are not a problem as well. I guess there should be huge files under the /var/lib/mysql dir?

BTW, this is my my.cnf config:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]


user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
bind-address            = ########
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M

log_error                = /var/log/mysql/error.log

expire_logs_days        = 10
max_binlog_size         = 100M
innodb_buffer_pool_size = 4G
innodb_file_per_table = 1

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

Yesterday I tried to run an optimize on the whole mysql db but this just filled up my disk again. I have now attached a slow USB 2.0 disk and currently I copy across the /var/lib/mysql folder. Once this is done I will run the optimize again. Shouldn't the optimize shrink the database?

Regards,
Oliver

[r0k5t4r]

The optimize crashed after running for more than 24 hours and the database didn't shrink at all...

[Zaniwoop]

OPTIMIZE may crash due to a deadlock with the housekeeper, if it's holding the history or trends tables open too long.

Also, since OPTIMIZE on innodb tables recreates the table by doing an sqldump to a temp file. If you do not have enough disk space it will crash.

[r0k5t4r]

I copied it across to a usb disk and there was plenty of space available. Also I stopped zabbix and apche during the mysqlcheck... I might give it another try today if it is the only option to shrink the table.

[r0k5t4r]

I don't get it. Housekeeper is set to run once a day and actions and events are kept for 1 day. I reduced the history for almost all my items to 7 days and still the db is 55Gbyte....

Either housekeeper is not running or the mysqlcheck command was not freeing up the space in the db...

[Colttt]

do you monitor log-files or somthing like this?

its not normal that you database is growing so fast.. i think you must recheck you items (how often check) an dos on..

[r0k5t4r]

Hi,

it's not really growing fast. It just sits at 55GB no matter what I do... I will check a few templates today, to see if there is a problem.

e.g. I monitor a Cisco Stack (6 Switches) and I guess there are a lot of unneeded items...