MySQL ibdata1 growing and growing and growing and...

jnojr · 07-20-2009, 04:29 PM

RHEL5, mysql-5.0.45-7.el5, and I know jack about MySQL.

/var/lib/mysql/ibdata1 just keeps growing bigger and bigger. I believe it may be logging each transaction. I really need to stop this... it's on a VM with limited disk space, and I really don't care if transactions cannot be recovered.

1) How do I shrink the file? Right now, my best guess is to dump the databases, stop mysqld, delete /var/lib/mysql/ibdata1, start mysqld, hope it works, and restore the databases. Yes/no?

2) How do I tell mysqld to not keep collecting gigabytes of information? If there's an easy way to have it keep the last X days worth, great. If not, how do I tell it to not do any logging at all, if that's what it's doing?

chrism01 · 07-20-2009, 07:11 PM

You need to read this part of the manual: http://dev.mysql.com/doc/refman/5.0/...iguration.html and maybe look at Per-Table Tablespaces http://dev.mysql.com/doc/refman/5.0/...blespaces.html

However, the key thing in the first link is:

Quote:

Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory. To get good performance, you should explicitly provide InnoDB parameters as discussed in the following examples. Naturally, you should edit the settings to suit your hardware and requirements.

IOW, if its ibdata1 that's growing, that's real data, not logging... you need to investigate where all the rows are coming from.