I have a very small MySQL Database where the main table is about 300 records and I do add more occasionally. The table stores locations to files and some meta data for video assets that my users have access to. (movie title, path, keyframe name, movie name, file name, etc, etc)
I wanted to move this table to an in-memory solution, but I am confused now-a-days as to that options there are and what is really the fastest, lightest weight, best bang for the buck.
Redis? MongoDB? Something else? Can MySQL run in memory?
I am planning on adding some other databases for different projects a well.
The box this runs on is CentOS 5.6, 16gb RAM and I get about 100 visitors to my site daily.
The first thing that you need to ask yourself is,
why do you want to switch to a NoSQL Database?
Answers could be: You need more speed, you want
a replicated and distributed database system,
you need more scalability for used storage
or replication. Or you want to benefit probably
from easier development.
But from 300 records i dont think any, but of the
last would help you.
And i don't think that you really want an "in-memory"
solution. In memory means every data only got written
to ram, and if you close your database, then all your
data is lost. If you really want that then MySQL
have an in-memory Storage engine.
Otherwise all database will cache as lot in-memory
as it can. And 300 records will likely be completly
in your ram also under MySQL. You probably don't get
any speed benefits from switching to a NoSQL database.
If you in general plan to switch to a NoSQL database,
i would prefer MongoDB. It is a mix from NoSQL and
and a RDBMS, because it give you a query language
powerful nearly as SQL. And you can scall it better
than RDBMS. And for me, programming will be a lot
easier against a schema-free database. You can
scale MongoDB up to 1000 servers.
But other NoSQL databases have a lot more scalability.
If you need more Servers, then other databases would
probablby better. But you lose some way to query your
data.
If you generally plan to switch to a NoSQL database
i think MongoDB could handle this. Other NoSQL databases
are probably better for some specific cases and not
for general usage.
The easiest way is to use MySQL MEMORY table engine. It stores all data in memory and its being deleted when the MySQL server stops working.
Easiest thing is just to configure mysql so it keeps everything in memory (which you have plenty).
See the answers about buffer pool size here
Is it possible to load a database in the RAM?
With such a small table, even with the default config it probably already is in RAM for all practical purposes.
With 300 records you're not going to see any measurable benefit moving to a nosql system.
Yse, MySQL can use memory as a substrate for storing tables - but if your entire database is smaller than the amount of free memory on your system, then it's really not worth the effort - most modern OS (including Linux) will very rarely have to read from the disk - and you don't have the hassle of maintaining a copy of the db on disk to survive reboots.
Your database is tiny. "I get about 100 visitors to my site daily" is a pretty meaningless metric but does imply very low levels of traffic.
16gb RAM is a lot of memory - it might be what you need for your application if you're handling a lot of media files though.
It depends on how do you want to access/query your records in the database.
MongoDB is a document store, it does the most of what Mysql can do. But I won't say MongoDB is a light weight solution.
Redis is a fast in-memory key/value database. But you can only access you records by a specific key.
