Diamond Notes

I have one project going on that is using a MySQL Cluster.  We have some tables that are going to grow extremely rapidly, so I am going to convert them to storing (non-indexed) columns on disk.  Before I started working on this, I had the naive thought that this was a fairly simple thing.  Maybe a table alter command of some type.  Nope.  Not the case.  As detailed here: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data-objects.html there are several steps required to create a table that is stored on disk.

1. Create a log file group.  This is used to create undo logs for the table.

2. Create a tablespace and assign the log file group to the tablespace and also assign at least one data file.

3.  Create a “Disk Data Table” that uses the tablespace on disk for storage of table data.

This doesn’t really help my particular situation out as I already have the table created.  While there isn’t a huge amount of data in it currently, I didn’t want to dump the data out of the table(s), rebuild them with this method and then  re-import the data.  I know, I am lazy :)  So, I asked on the cluster mailing list what I could do and Stewart Smith replied:

“It’s not an online operation, but you can ALTER TABLE foo TABLESPACE ts1 STORAGE DISK ENGINE=NDB; to convert table foo to be a disk data table.  It’s a copying alter, so you should be in single user mode and the table will be locked.”

So, I can create the log files and table space, shut my cluster down to single-user mode, and then just alter the table.  Simple(r) even for the lazy among us.  If you are using a MySQL Cluster you absolutely should be subscribed to the cluster mailing list.  It (and the other MySQL mailing lists) can be subscribed to at http://lists.mysql.com.

Thanks Stewart!!