Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

From MySQL 5.1, the data doesn't need to be entirely in memory anymore.

I've read that the indexed columns (I think the entire index structure) must still be in memory (MySQL High Availability, 2010, pg. 533, "MySQL Cluster keeps all indexed columns in main memory").

In light of this, what happens if there's NOT enough memory (ie, a huge database (> 100GB, or 1TB), running on servers with low memory configs (2 data nodes, each with 1GB of RAM, for instance))?

share|improve this question
    
I've asked this because I got curious about what would happen if a data node doesn't have enough main memory. Won't MySQL Cluster keep part of the index in secondary memory at all? What if I don't have lots of RAM and my database size is huge. Then MySQL Cluster isn't an option for me? –  gsb Dec 19 '11 at 18:13
add comment

4 Answers 4

up vote 4 down vote accepted
+50

MySQL Cluster supports storing non-indexed columns on disk-only with an LRU cache of recently accessed data. However indexed columns are always held in-memory.

MySQL Cluster preallocates all memory, according to the DataMemory and IndexMemory parameters. It will not ask the underlying OS for more memory dynamically.

This means that you need to have configured enough memory across your cluster to hold all indexed columns in memory. If your dataset is large enough that the indexed columns are larger than the available cluster memory, then you cannot load that dataset into the cluster. At some point you will run out of space, and your inserting transactions will be aborted.

When configuring DataMemory and IndexMemory, it is best to limit yourself to somewhat less than the physical memory in each system. Some physical memory should be reserved for the OS and other processes.

Theoretically MySQL Cluster can be configured so that it uses virtual memory via a swap device (e.g. more than physical memory), but as the other answers state, this is not a designed-for use case. Having in-memory structures swapped to disk is usually sub-optimal as in-memory random access patterns result in random access to the disk, resulting in swap thrashing and slowdown across the system. With MySQL Cluster, the most likely outcome is heartbeat failure and cluster failure due to a swapping data node not responding to signals quickly enough.

To efficiently support larger-than-aggregate-memory indexes, MySQL Cluster would need to support on-disk index formats (perhaps a B tree etc) with caching and access patterns aligned with disk access properties.

share|improve this answer
add comment

MySQL cluster is built around an index structure that is optimized for memory fit; a T-tree. This is different from your regular storage engines in MySQL which use a B-tree or B+tree structure, which can survive quite well out of memory fit assuming that you have some hot-spots / non uniform access (this is normally a safe assumption).

If you want to build some sort of proof-of-concept, there's nothing stopping you from using a large amount of swap to compensate for your lack-of-ram. Just be aware that this will not perform well, and you are using a product for a use case it is not designed for.

share|improve this answer
    
Your answer and Frazer's are quite good. But since he has answered first, I'll give him the credits. Hope you don't mind. :) –  gsb Jan 12 '12 at 11:44
    
Nope, don't mind ;) Frazer works on MySQL cluster. –  Morgan Tocker Jan 13 '12 at 2:31
add comment

In light of this, what happens if there's NOT enough memory

Probably what you'd expect if there's not enough memory. Files will be made on your drives and you will be running as fast as your drives allow.

Be sure to limit MySQL properly so that it doesn't consume all your memory needed for other system processes.

share|improve this answer
add comment

Basically, a chain is only as strong as its weakest link.

When private virtual servers are setup (using VMWare to setup ESX Clusters) all bare-metal servers in the Cluster are supposed to have the same amount of RAM.

MySQL Cluster should be treated with the same kind of kid gloves. If any servers (even just 1) have less memory that others in a Cluster, that would be like a limiting factor. It would not surprise me if a server with 2GB RAM causes other servers in the MySQL Cluster (even if those other servers each have 8GB RAM) to behave as if they had 2GB at most.

At the very least, you should

As a side note, please keep in mind that MySQL Cluster also has the capability of storing data on disk for each server running as a storage node

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.