How to tell how much main memory an app uses

Question

Hi all,

I need to choose a database management system (DBMS) that uses the least amount of main memory since we are severely constrained. Since a DBMS will use more and more memory to hold the index in main memory, how exactly do I tell which DBMS has the smallest memory footprint?

Right now I just have a memory monitor program open while I perform a series of queries we'll call X. Then I run the same set of queries X on a different DBMS and see how much memory is used in its lifetime and compare with the other memory footprints.

Is this a not-dumb way of going about it? Is there a better way?

Thanks, Jbu

Answer 1

Ultimately, this kind of optimization is probably answering the wrong question.

Most likely the answers you gather through this sort of testing are going to be misleading, because the DBMS will react differently under "live" circumstances than during your testing. Futhermore, you're locking yourself in to a particular architecture. It's difficult to change DBMS down the road, once you've got code written against it. You'd be far better served finding which DBMS will fill your needs and simplify your development process, and then make sure you're optimizing your SQL queries and indices to fit the needs of your application.

Answer 2

What you can do in the application is manage how you fetch data. If you fetch all rows from a given query, it may try to build a Collection in your application, which can consume memory very quickly if you're not careful. This is probably the most likely cause of memory exhaustion.

To solve this, open a cursor to a query and fetch the rows one by one, discarding the row objects as you iterate through the result set. That way you only store one row at a time, and you can predict the "high-water mark" more easily.

Depending on the JDBC driver (i.e. the brand of database you're connecting to), it may be tricky to convince the JDBC driver not to do a fetchall. For instance, some drivers fetch the whole result set to allow you to scroll through it backwards as well as forwards. Even though JDBC is a standard interface, configuring it to do row-at-a-time instead of fetchall may involve proprietary options.

On the database server side, you should be able to manage the amount of memory it allocates to index cache and so on, but the specific things you can configure are different in each brand of database. There's no shortcut for educating yourself about how to tune each server.

Answer 3

Just use SQLite. In a single process. With C++, preferably.