Over a million developers have joined DZone.

No MySQL Cluster, the Table Isn't Full!

DZone's Guide to

No MySQL Cluster, the Table Isn't Full!

· Database Zone
Free Resource

Learn how our document data model can map directly to how you program your app, and native database features like secondary indexes, geospatial and text search give you full access to your data. Brought to you in partnership with MongoDB.

  OK, to begin with, I'd really like to try MySQL Cluster as a Key-Value Store. Yes, I have already recognized that MySQL Cluster stores variable length data in fixed length format. And someone should think about how good an idea this really is, in my mind it's just as mindbogglingly efficient, modern and start as storing data on punched cards. Which I assume how the MySQL Cluster sourcecode is managed.

yes, I really dislike the idea of fixed length strings, and more on this later. But think now, I have 2 fields defined as VARCHAR(256) and this is a Web application, then how much space will be allocated on disk for MySQL Clsuter? As many bytes are there are in the string r 256 bytes? Neither actually, this is a web-app, and I know it comes as a surprise to some of you, but Unicode is here to stay, and UTF-8 is way to go. And MySQL assumes that no one will use more than a 3-byte Unicode character (which is an incorrect assummpion, but I can live with it. And 4-byte Unicode charsets are available actually). Anyway, as each character in that case may occupy 3 buyes, MySQL Cluster will allocate 768 bytes for a VARCHAR(256) UTF-8 encoded field. Yes. Yuck. As I have written in many blogs before, disk space isn' much of an issue these days, but writing all that data may well be. So it I put 10 7-bit ASCII characters in a VARCHAR(256) fields, I'm not writing 10 bytes, I'm writing 768 bytes, that makes a difference in performance.

All this aside, I really want to load my data into MySQL Cluster to test it's claims for performance and it's claims as a great Key-Value Store. And now again, it tells me that "The table is full". That MySQL Cluster tablespaces stores data in fixed length format is bad enough, that it insists on storing it in fixed length data files (that takes forever to create) makes it even worse. But I have never been able to store more than about 60.000.000 rows to my MySQL Cluster setup. The error I constantly get is "The table '' is full. Yes, there is no table there, the table name is empty. I have 4 data nodes. I look at my storage and I see this:

mysql> select * from ndbinfo.memoryusage;
| node_id | memory_type  | used      | used_pages | total      | total_pages |
|       2 | Data memory  | 497647616 |      15187 |  524288000 |       16000 |
|       2 | Index memory | 129687552 |      15831 | 1048838144 |      128032 |
|       3 | Data memory  | 498073600 |      15200 |  524288000 |       16000 |
|       3 | Index memory | 129802240 |      15845 | 1048838144 |      128032 |
|       4 | Data memory  | 497745920 |      15190 |  524288000 |       16000 |
|       4 | Index memory | 129777664 |      15842 | 1048838144 |      128032 |
|       5 | Data memory  | 497680384 |      15188 |  524288000 |       16000 |
|       5 | Index memory | 129679360 |      15830 | 1048838144 |      128032 |

OK, what about my data store on disk, maybe I am running out on space there:

mysql> select SUM(FREE_EXTENTS * EXTENT_SIZE) / 1024 / 1024 As "Free MB", SUM(INITIAL_SIZE) / 1024 / 1024 "Size MB", (SUM(INITIAL_SIZE) - SUM(FREE_EXTENTS * EXTENT_SIZE)) / 1024 / 1024 AS "Used MB" FROM information_schema.files WHERE FILE_TYPE = 'DATAFILE';
| Free MB         | Size MB         | Used MB        |
| 327935.00000000 | 381472.00000000 | 53537.00000000 |

Nothing there either, there seems to be plenty of space available. What makes this table "full", please tell me if you know, I'd really like to test this beast!

And then, before I end this post, let me tell you one thing: For the intent and purposes that MySQL Cluster was once designed, Telco-applications, I'm sure it's great. Fact is, I know it is and  few beats it. Secondly, even in Web-space, there are great uses for MySQL Cluster, I know that from first hand experience. But as, say, a substitute for, say, MongoDB as a document store, it's not even close. Not necessarily only in terms of raw performance, but it's just not a good fit for the use case. For one thing, a document is variable in size, for another a simple KVS document store is just that, simple, and if there is one thing that MySQL Cluster is not it's simple. If you want the Ferrari of databases, then MySQL Cluster might be it. Possibly. But if you want the Volvo KVS, then MySQL Cluster isn't it.

And now I'm close to closing, but there is more more thing: No, I'm not giving up. No way! But I just want to know why the table is "full", because MySQL Cluster is wrong, it's not (and when creating the table I told it to hold 150.000.000 rows with and avg-row-size just what it in reality is, so if MySQL Cluster has a problem with this table, it could have said so from the start, when I created the table. Right? If it ignores what I am telling it, why is the syntax there at all?).

Discover when your data grows or your application performance demands increase, MongoDB Atlas allows you to scale out your deployment with an automated sharding process that ensures zero application downtime. Brought to you in partnership with MongoDB.


Published at DZone with permission of Anders Karlsson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}