MySQL cluster and all dump 1000

So, I had written a while back: “We currently have a DataMemory of 4GB and IndexMemory of 2GB. Based on the crude methods we have to monitor it, I think we are at about 40% capacity.” Boy, I was wrong.

After that post, I started looking at this more in detail because we were considering buying more RAM “just in case”. I figured out how to use the super secret command “all dump 1000”. The command is not documented in the MySQL documentation that I could find. I did find it in the NDB API documentation before writing this post however. Not sure why I could not find it before.

For those that still don’t know how to use it, simply type “all dump 1000” from your management console. Then check your cluster log files on the management server. You will see something like this:

2007-07-20 17:56:47 [MgmSrvr] INFO -- Node 2: Data usage is 46%(90672 32K pages of total 196608)
2007-07-20 17:56:47 [MgmSrvr] INFO -- Node 2: Index usage is 1%(2006 8K pages of total 131104)
2007-07-20 17:56:47 [MgmSrvr] INFO -- Node 3: Data usage is 46%(90724 32K pages of total 196608)
2007-07-20 17:56:47 [MgmSrvr] INFO -- Node 3: Index usage is 1%(2039 8K pages of total 131104)
2007-07-20 17:56:48 [MgmSrvr] INFO -- Node 4: Data usage is 43%(86153 32K pages of total 196608)
2007-07-20 17:56:48 [MgmSrvr] INFO -- Node 4: Index usage is 1%(2016 8K pages of total 131104)
2007-07-20 17:56:48 [MgmSrvr] INFO -- Node 5: Data usage is 46%(90672 32K pages of total 196608)
2007-07-20 17:56:48 [MgmSrvr] INFO -- Node 5: Index usage is 1%(2007 8K pages of total 131104)

Anyhow, I ran that and low and behold I saw that we were at about 93% capacity. As you can see above, we have made some changes. This got me to really digging as to what the difference could have been. As far as I can tell, our use of TEXT fields in our data was causing the issue. We have several fields in our data structure that hold data larger than 256 bytes. However, they hardly ever are more than 600 bytes. Based on what I read, it seems that the data after the first 256 bytes would be stored in 2k chunks. So, our litte ~300 byte extras were being stuck into 2k chunks. That caused a huge amount of wasted space.

So, the first thing I realized was that we could use 6GB DataMemory instead of 4GB. The machines have 8GB of RAM in them. We were using only about 200MB of IndexMemory. That leaves well over 1GB of the system.

The second thing I did was carefully analyze our data. I went back to school and started counting bytes. Its easy to get lazy as a developer these days. We just use a data type we know will work and it really is no big deal. But, not in this case. I changed int to mediumint and some to even small and tiny. I realized that there were two TEXT fields I could eliminate altogether. I looked at the varchar fields and made them only as long as they need to be. In the end, we ended up at the numbers you see above, about 46% capacity or 2.76GB. We were at 3.72GB before. The removal of the two TEXT fields may have had more to do with the improvement than anything.

This was all with MySQL 5.0. 5.1 will bring better storage of the large data. For example, all data over 256 bytes, for a row, will be stored together in 2k chunks rather than one 2k chunk per column. That will likely save us 1GB of DataMemory. The other feature of 5.1 is Disk Data Tables. We are currently testing those and I will blog about them when I have more information. Early numbers look good though. We have just set our largest table (the one with the TEXT fields) as a disk data table and our DataMemory is down to just 280MB. Yeah, that is an MB. Our COO is a bit concerned with the performance hit we may see with data on disk.

Oh, one more pointer about all dump 1000. For a quick way to grab my current usage, I use this command from my prompt (on a Mac):
ssh user@mgmserver "ndb_mgm -e 'all dump 1000' && tail -n 1000 /var/lib/mysql-cluster/ndb_1_cluster.log | fgrep usage | tail -n 8";;
Use at your own risk. It may make your server catch fire. =)


3 Responses to MySQL cluster and all dump 1000

  1. Ben says:

    awesome information, thanks!

  2. Paul M says:

    Nice tip with regards to “all dump 1000” I will have to go read the NDB API as well.

    The 5.1 data on disk would be perfect for your text columns, reducing the size of the cluster database. However I see you are going down the MASTER-MASTER replication route. Make sure to be careful with AUTO_INCREMENT use AUTO_INCREMENT_OFFSET.

    Have Fun


  3. doughboy says:

    auto_increment is not an issue for us. All data in our cluster (and now replication see my latest post) is populated from our main database where all the ids are created.

%d bloggers like this: