3

In general, how much RAM does an in-memory database require? I would assume it requires enough RAM for:

  • Operating system and general system services, plus
  • DBMS, plus
  • the entire database (same amount as stored on disk), plus
  • temporary database tables
  • some slack

Considering that the largest component of this would normally be the size of the entire database, then it would require the normal size of RAM required to run a regular on-disk database (e.g. 16 GB) plus the on-disk size of the database (let's say 50 GB for a fairly large DB); in this example it would be at least 76 GB plus room for gradual growth of the database.

I know that the answer would be different for different DBMSs (e.g. SAP Hana or Oracle TimesTen), but I am asking about the general components of the calculation.

Is this analysis accurate, or am I missing some important components of the calculation?

closed as too broad by Mark Storey-Smith, Paul White, Max Vernon, RLF, swasheck Sep 16 '14 at 16:14

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What RDBMS are you referring to SQL Server, Sybase, Oracle ..? – Kin Shah Sep 4 '14 at 19:30
  • 2
    Is this question related to In-memory technology introduced in SQL Server 2014 or its about general how much RAM would SQL Server use – Shanky Sep 4 '14 at 19:53
  • Check the total resident memory used by all processes by summing the RSS column from ps. You can also use free to check RAM in use. Also don't forget to leave some spare RAM for filesystem buffers. – Bill Karwin Sep 4 '14 at 20:42
  • I edited the question to clarify that I am asking for any in-memory database in principle. I am not even so much looking for a definite number as I am looking for a formula for calculating the number, since the number will obviously vary widely based on implementation. – Ochado Sep 5 '14 at 1:21
3

Check out these links - the first is for Michael Stonebraker's VoltDB. He is one of the main drivers behind what he terms NewSQL - he believes that OldSQL (i.e. traditional RDBMS systems, your Oracles, MySQLs and SQL Servers) are doing far to much work buffering and latching &c. In his VoltDB (Java based) system, transactions run to completion and don't compete with other transactions for system resources.

The key thing to bear in mind is that it is a shared-nothing in-memory architecture. He believes that this is good for OLTP, but not for OLAP for which he advocates columnar based stores such as the one he founded - Vertica, since bought by HP.

Stonebraker also believes that NoSQL has thrown the baby out with the bathwater in sacrificing SQL and ACID for performance.

Microsoft's Hekaton is based on very similar ideas (1, 2) which is hardly surprising since there was a Microsoft guy as coauthor of some of Stronebraker's work (can't find reference at the minute). The main difference AFAICS is that Hekaton's stored procedures are compiled to native code.

The references I have given are kind of vague and general, but the first Microsoft link says that your memory should be twice your data. AIUI, this rule is not cast in stone, but will depend on your app's functionality, but it's a good place to start :-). This is a fascinating area and worth reading up on (did an essay on it last year) and there's a lot of stuff out there.

Check out these also (1, 2, 3).

  • Thanks for the links. The [voltdb.com/docs/PlanningGuide/HwCapacity.php VoltDB calculations] were particularly helpful. I could summarize the response to be something like: (((disk size of DB) + (server process RAM = approx. 2 GB)) * 1.3 ) * 2). The *1.3 is to give 30% slack for the server requirement, and the *2 is to operate at 50% RAM capacity. Thanks! – Ochado Sep 5 '14 at 15:52
3

This will be different for each in-memory database, I can speak only for VoltDB which stores 100% of the data in RAM. We wrote a blog that answers this question: In-memory database sizing - Throw out the conventional wisdom

The first step is to estimate the size of the data in RAM, which depends on the data types and field sizes. We provide an online worksheet based on the actual field datatypes and sizes from the schema. In my experience, if you try to skip this step and estimate based on the size on disk from another database, or the size of CSV files, it can be way off. The final estimate is only as good as the inputs, and this is the primary input.

Once you have an estimated size from the worksheet, add 25% as a safety/overhead margin. Multiply by 2 if you need high availability fault tolerance for up to 1 server, or by 3 if you need up to 2 servers fault tolerance.

Once you have that total size of RAM for a cluster, you can divide by the number of servers you intend to deploy to get the RAM/server, and you should keep at least 2 GB RAM for java heap on each server.

A note about java heap on VoltDB (VoltDB is not affected by garbage collection issues): Impact of Java Garbage Collection on in-memory databases

There is a more detailed explanation of the estimation process and formulas for each datatype in the VoltDB Planning Guide.

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