• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to find the approximate memory size of the Database?

 
prabhu pandurangan
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Team,

Below is my table column values:


Assume that i have 1000 rows similar to the above table structure. Here the String Key column keys can be same (repeated).

What would be the approximate memory size for constructing this database.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not sure what you would do with this information. But you can get an idea by calculating the space of the data and then adding a bunch (for indexes and various other database components.)

It's not huge though hence wondering what you want to do with the number.
 
prabhu pandurangan
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Jeanne,

Thanks for your reply.

its for my self assessment. i just want to know how the database memory management works and for this particular scenario what would be the best possible least memory consumption way of DB design.

Regards,
PRabhu
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not sure, but I believe different databases manage memory differently.
They all have their own queries to find how much approximate space is occupied with existing data.
One way to design table to have least memory usage is...the column you believe would have maximum NULLs, put it as the last column.

I guess there should be different strategies also.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also disk space is often not simply a case of how much data is in the table.
Many DBs leave "gaps" so they can assign data blocks easily to a table...these gaps don't have any data in them, they're there for performance reasons.

For example, this is a description of Oracle's structure (it's 10g, but it hasn't change significantly).
 
prabhu pandurangan
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Team,

Thanks everyone for your replies.

What would be the best/least expensive data structure to load the below example data to the DB?

Regards,
Prabhu
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am absolutely unsure about your question.
To save data in a database, tables are needed, not data structures.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The size of a table will depend on lots of factors - the data-types (numbers and dates take less space than characters, use VARCHAR, etc), the data (sparse data, distribution of values etc), indexes, partitioning, standard overhead per row, the storage block size in your database, replication, rollback requirements etc - and many of these things will vary depending on the database (Oracle or MySQL etc). If you just want a rough idea, Jeanne's approach is as good as any. If you want a more accurate estimate, you need to look at the recommendations on how to estimate table space for your specific database - talk to your DBA.

Also, you seem to be confusing memory requirements and disk requirements. Unless you are using an in-memory database, your database will store data on disk, so you do not need to worry about how much memory a table needs, unless you plan to keep running a full query on it i.e. SELECT * FROM my_table; to read all the data. In general it is your queries that will determine how much data you fetch into RAM, not your table size. However, your database itself uses plenty of memory, so you or your DBA would need to consider this when deciding how to ensure your database has enough resources.

Prabhu wrote:What would be the best/least expensive data structure to load the below example data to the DB?

Don't try to optimise your database by hand. The database will store the data efficiently according to its own internal rules. One of the many good reasons to use a database is that as a developer you don't have to worry about the internal physical storage mechanisms the database uses, because you can work with the abstractions provided by the relational data model and SQL (e.g. you work with tables, not files). Concentrate on getting your data model right for your application, and make sure your queries are optimised to use indexes properly, and let the database get on with doing its job.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic