2

What I would like to do/have is:

Some kind of application/driver that creates a RAM drive/partition that is actually assigned a drive letter (eg. D:). This drive letter should be based on/mirroring a specific directory of a physical directory (eg. C:\MyDBData).

On startup of the application/driver, I would expect the application/driver to read all files in the directory into memory and locking them to prevent writes to those files by other applications. All files are then available on the RAM drive. Each read action can then be done from memory, which I guess would be a major performance boost.. Any writes should be done "write-through" to the physical directory, or maybe with buffer of max 1 second. (So in case of power-loss, losses would be minimal.)

Of course this application/driver would need some RAM of its own, and will create some slight overhead on CPU load. Still, I expect a major performance boost.

What I would like to use it for is:

In order to speed up my 5 GB MySQL DB, I would like to move all tables to my 8 GB RAM.

And even though MySQL does support memory-tables, the limitations are keeping me of achieving this. For example, text/blob fields are not allowed in heap tables.

It's still a guess to me whether 8 GB RAM is enough for a 5 GB RAM drive, the overhead of the mentioned application/driver, the mysqld itsself and the rest of the OS. But I can consider upgrading to 12GB or even 16 GB RAM.

My question is

I assume this technique already exists. What is it called, and where can I find a good implementation of it? (Preferably open source, for Windows)

And even though tips on Mysql vs memory are welcome, and maybe this idea I have isn't the best way to approach the Mysql problem, I would still like to explore the question I have of directory-to-RAM.

EDIT: What I just read in a similar question is that this is called Filesystem Caching, and is present in most modern OS'. But if that is the way to go, how do I instruct my OS to increase the Filesystem Cache, and to specifically keep an entire 5GB directory in that cache at all times?

Improve this question
6
  • and when you lose power and lose all your changes?
    – JamesRyan
    Commented Apr 2, 2013 at 14:57
  • @JamesRyan It says so right in the text above: --- Any writes should be done "write-through" to the physical directory, or maybe with buffer of max 1 second. (So in case of power-loss, losses would be minimal.) ---
    – nl-x
    Commented Apr 3, 2013 at 8:27
  • 1
    but then you have to wait for the disk anyway!
    – JamesRyan
    Commented Apr 3, 2013 at 10:34
  • @JamesRyan no. any writes are written in the RAM first. The program that did the write can then continue without waiting for anything. The application/driver that manages the mirrored directory eventually does the "write-through" is of no imporance to the program doing the write action.
    – nl-x
    Commented Apr 3, 2013 at 11:22
  • You mean write-back. Write-through caching is where changes get written both to the cache and the underlying storage. If you are consistantly writing faster than the underlying storage at some point you have to write it out so just delaying the inevitable.
    – JamesRyan
    Commented Apr 3, 2013 at 12:40

1 Answer 1

Reset to default
2

The OS and the database already include features for managing data in memory in the best way to suit most usage without increasing the risk of data loss. Far smarter people than you or I have worked on this problem and there is no magic go faster option that they have just decided to turn off for the fun of it. :)

Unless you have a specific use case and know what you are doing you are not going to make any better improvements than just following best practices for database performance.

Improve this answer
5
  • My specific use case is written above. The OS manages on file level. The OS might want to flush a cached DB table file when the OS sees the file not being accessed in a specific time. I want to prevent that, because I want the entire DB in the RAM at all times! The server is a dedicated DB server, but the OS doesn't know this. So your answer isn't really an answer.
    – nl-x
    Commented Apr 3, 2013 at 11:15
  • 1
    The OS doesn't take things out of memory unless you try to load something else in so by default it will keep the db there already as long as you have enough memory to cover that and other processes you run.
    – JamesRyan
    Commented Apr 3, 2013 at 11:25
  • How sure are you of that? And will the OS also cache all files that could fit regardless of their site? I would expect the OS to not cache an entire 3GB file if the OS has 3,5GB free memory left. I would expect to only cache the file partially if at all. And thus fragment the caching of that file, if cached at all.
    – nl-x
    Commented Apr 3, 2013 at 11:30
  • Don't forget that your database app also needs its own memory to run queries, etc. in as well. This will be doing it's own caching too. Swappiness is the property to control the balance between apps and disk cache, but again unless you know what you are doing messing with this is likely to reduce performance.
    – JamesRyan
    Commented Apr 3, 2013 at 12:38
  • nl-x you are correct, the O/S probably won't cache a 3 GB file if it only has 3.5GB of RAM in total, but it depends on the O/S. Ultimately, if you want more performance, get more RAM. A well written DB application will however, cache the entire DB to RAM if there is enough.
    – Snellgrove
    Commented Apr 3, 2013 at 13:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .