I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that one has a server, with this kind of memory? I should mention that the queries towards the table are mostly ad-hoc queries.

One issue I believe I have found that would arise is that queries towards the MEMORY table could cause temporary tables to get created. Temporary tables have a hard limit of utilizing up to 4 GB of RAM before they get converted to a MyISAM table. This would of course kill performance completely. The memory limit of temporary tables could be worked around by setting the tempdir to a ramdisk.

What other kinds of problems do you foresee?

  • 1
    Potential alternate question: What is the maximum in-memory storage engine size. – jcolebrand Sep 26 '11 at 14:20
  • 2
    @David I attempted to roll your answer into the question without detracting from the original question. Feel free to edit the wording, but it seems more appropriate in the question since it provides info on how you're using the table. – Derek Downey Sep 28 '11 at 17:31
  • @DTest I really did not understand why that text could not be an answer, but now you solved it, so it could be a part of the question. Thanks a lot for doing that! – David Sep 28 '11 at 18:20

The MEMORY storage engine can either be a blessing or a curse depending on

  • what you intend to store
  • how often you plan to perform DML
  • how much RAM you leaving for the
    • Database caches (MyISAM Key Cache, InnoDB Buffer Pool)
    • OS caches
    • OS operation

The MEMORY storage engine

  • uses full table locking for INSERTs, UPDATEs, and DELETEs
  • Cannot perform concurrent INSERTs
  • uses the hash indexes instead of BTREE indexes by default
  • can use BTREEs indexes, but must be explicitly specified at CREATE TABLE time
  • has no transaction support
  • Single row queries are just great against MEMORY tables, especially using HASH indexes -- Ranged queries and sequential access are just horrific unless you use BTREE explicitly (more memory consumption required)

Even though you have data in RAM, mysqld will always hit the .frm file to check for the table existing as a reference point, thus always incurring a little disk I/O. Proportionally, heavy access to a MEMORY storage engine table will have noticeable disk I/O.

  • 3
    +1 presumably these points limit it's usefulness increasingly as the database grows - hence it's probably not going to work well with 500GB+ data? – Jack Douglas Sep 26 '11 at 15:16
  • 1
    @Jack - I guess this is why MySQL Cluster and memcached are out there. For many, in-memory databases should be managed in a distributdd way. Without those alternatives, most just want memory-based access to data and are willing to throw the OS "under the bus" to attain it. – RolandoMySQLDBA Sep 26 '11 at 17:38
  • 2
    FWIW I'll throw the OS under the bus when all I need the machine to do is return data. I favor the return of big iron in that regard. Strip everything that isn't necessary (hello MinWin) – jcolebrand Sep 26 '11 at 17:41

I'm putting a second answer instead of revising my first because I think this one has merits on it's own.

Why not create a VM with a ~600GB ramdisk for it's hard drive, and load the 512GB datastore into the ramdisk and then you can treat it like a regular HDD and it'll never hit the physical medium?

If I had a machine with 2TB RAM I would help you figure out the proper semantics on this, but I don't. I do, however, have friends in academia who are working on these sorts of problems daily. I just don't have that sort of hardware at my disposal ;)

But this would solve all your problems of speed

  • 1
    Your answer, in itself, is quite reasonable (+1). I also agree with your statement on hardware availability. All you need is a good VMWare environment that uses enough servers as RAM allocation from VMWare servers. Public clouds would croak otherwise. – RolandoMySQLDBA Sep 28 '11 at 17:23
  • Your answer seems more of a Sarcasm to impossibility ;). – Rahul Nov 1 at 11:38
  • Hi Rahul. This answer is indeed 8 years old, and I've grown a bit as a person, as I hope we all have. How would you suggest I improve this answer so it sounds less sarcastic? Would you like to have a go at the edit button above and guide me, as I always enjoy learning a better way to fish, so to speak. (... is the metaphor "teach me to fish" well known?) – jcolebrand Nov 1 at 15:40

I would think that contemporary process size limitations would be the primary limiting factor in this situation. The question then becomes not "can the app do it" but rather "can the OS do it" as well as "does the OS support it".

Since you didn't specify an OS, I'm going to assume that you're using a 64 bit variant (you are discussing 1TB RAM servers in this category) of a "normal" OS.

According to this MSDN http://msdn.microsoft.com/en-us/library/aa366778%28VS.85%29.aspx article for Windows, there is the potential for a 500GB in-memory store, but it would appear unlikely from my basic and rather limited reading that the guys at MySQL would have optimized for that particular use-case. Databases are generally disk-oriented, after all. (see also https://stackoverflow.com/questions/2399162/process-memory-limit-of-64-bit-process)

My googlefu this morning is a little weak, but everyone that I can seem to find referencing the subject seems to be weakly referencing 2^48 without strong proof to back that up as the upper limit for available per-process memory limits in 64bit Linux, which is several terabytes of data. Maybe someone can come up with a better maximum per-process limit for Linux 64 bit kernels.

  • 1
    You are correct about 48 bits but it is a CPU limit deliberately. See en.wikipedia.org/wiki/64-bit#Limitations and en.wikipedia.org/wiki/X86-64#Virtual_address_space_details – gbn Sep 26 '11 at 16:55
  • Although your answer is not MySQL-specific, I can relate to your bird's eye view of the OS, so +1 for that. Just to keep things in perspective, please remember the "guys at MySQL" are Oracle. – RolandoMySQLDBA Sep 26 '11 at 16:55
  • @gbn I was only pointing out that that's the limit everyone refers to for Linux, and I don't think that's accurate, but I can't speak to the Linux internals. But you're correct, everyone refers to the hardware limits. – jcolebrand Sep 26 '11 at 17:04
  • 1
    @RolandoMySQLDBA true, but without an OS to guide us, the answer has to be about the OS. And while it's true today that "the guys at MySQL are at Oracle", that doesn't mean that those guys have the experience per-se of the long-standing Oracle systems-dev gurus, nor does it mean that the team for MySQL has any desire to provide this construct (whereas the Oracle team may have the desire to provide the construct as asked above) .. tl;dr: meh, two different teams. – jcolebrand Sep 26 '11 at 17:06
  • @jcolebrand : I believe Oracle has the expertise (they exercise it within Oracle10g/11g). However, it's the desire part towards MySQL that strikes the biggest chord. +1 on your last comment. – RolandoMySQLDBA Sep 26 '11 at 17:22

The problem I forsee is the server crashing and you loose 500 gigs of data. I'll only use in memory engines for temporary things that aren't really production critical or queues that can be ultimately rebuilt in the event of a crash.

It would be more prudent to go with innodb and make sure to set your key buffers accordingly and leave the rest for disk cache. It doesn't sound like money is the problem for you so invest in an SSD raid solution for your disks.

  • This is generally a good point, but uptime is not as critical as performance. I might end up with just having 256 gb of data and this only requires a commodity server. – David Sep 27 '11 at 6:40
  • 1
    ahem screw SSD raid. Go get some of them thar PCIe ramdrives instead of SSD (no but really, they offer still faster performance over SSD, at the sake of not quite as large. But some of the PCIe SSD drives give better performance still, even beyond a nominally standard SSD.) – jcolebrand Sep 27 '11 at 14:48

Your Answer

