CODEWITHSUNDEEP
mysqlubuntu-12.04innodb
lars.schwarz
lars.schwarz

Reputation: 1274

MySQL InnoDB: how to completely flush all remaining transactional changes

I had to kill an alter table (copy to tmp table) process, because my server was running low on disk space. My ibdata file is now 40 GB big and there are just 8 GB disk space left (no, I can't add disk space atm and I can't use a ram disk, since the server only has 8 GB ram).

From my understanding even I killed the alter table process MySQL will try to complete it as soon as there's enough disk space available.

According to "show status like '%tmp%';" there are 0 tmp tables and 5 tmp files.

According to "SHOW ENGINE INNODB STATUS;" there is just one "transaction 0, not started" entry (and a few file I/O ones).

According to the information schema my table is approx. 20 GB and my ibdata file is 40 GB (I just have a single InnoDB table on this database).

Is there a way to flush all pending changes and/or delete all tmp tables (even show status does not list one)?

I also wondering why my innodb_buffer_pool_size is set to 8 GB (on a 8 GB ram server). I did not alter any InnoDB settings and there are actually none in my my.cnf file, so this seems to be some default value?

Thanks: Lars

Upvotes: 2

Views: 2113

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562330

You're right, even if you kill the ALTER TABLE, it continues to try to create a new copy of the table, and this operation is "killed" only after it has finished that step. There's no way to interrupt it, short of kill -9 on the mysqld process. Note that it'll generate errors if it run out of disk space. I think it'll abort the ALTER TABLE at that time, clean up the temp table, and then finish.

As for your question about flushing changes, there's no command to flush the work being done by ALTER TABLE. For other types of changes, if you set global innodb_fast_shutdown=0 and then shut down mysqld, this will flush all dirty pages in the buffer pool, purge any garbage in the rollback segment, and merge any pending index changes in the change buffer. But this doesn't do anything for ALTER TABLE. I believe the result will be that the shutdown will wait until the ALTER TABLE is done.

As for your question about the buffer pool, the default innodb_buffer_pool_size is 128MB, if you don't specify another value. You must either have it set to 8GB in a config file. Note that Ubuntu supports a directory /etc/mysql/conf.d where multiple config files reside, and this can be set in any of these config files.

You may have a big ibdata1 file, with nothing taking up the extra space.

Temporary tables are cleaned up when MySQL is done with them, but it may have increased the size of the ibdata1 file while it was in use. MySQL does not shrink the ibdata1 file, but it will reuse the allocated space for later data.

Yes, the only way to shrink ibdata1 is to dump all your InnoDB data, shut down mysqld, physical rm the ibdata1 file, then start mysqld, then reimport your dumped data. This has been a major inconvenience for MySQL users for many years.

Before you import your data, it's recommended to enable innodb_file_per_table so that you don't face this difficulty in the future. MySQL still needs an ibdata1 for global InnoDB data, but it should stay smaller. And anytime you drop or alter any InnoDB table (temporary or not), it'll reclaim some disk space. This is in fact enabled by default in MySQL 5.6.

mysqld looks for a my.cnf in several places:

  1. /var/lib/mysql/my.cnf (the datadir)
  2. /usr/my.cnf (the basedir)
  3. /etc/my.cnf

So check those locations for another my.cnf that has the mystery setting for innodb_buffer_pool_size=8G.

For lots of details on how MySQL finds the my.cnf, see http://dev.mysql.com/doc/refman/5.6/en/option-files.html

Upvotes: 1

Related Questions