Database on Memory

I am trying to make a server database.

Lets say I have a database with 2 million records. Each record has 22 columns and all the information inside the database is numeric. Only numbers.

The database is 300mb. I have 20 thousand records per table.

I heard that you can load databases on memory.

I put this database on a server. If I load it on the memory how many transactions can I have per second? Will the hard drive still play an important role? And what role will the processor play? Lets say I want the database to handle 1 000 read / write per second?

What hard drive do I need? VelociRaptor?

Can PostgreSQL handle it or will I need MySQL?
ChoklanderAsked:
Who is Participating?
 
dportasConnect With a Mentor Commented:
Your database is a small one and any of the leading DBMSs will handle it without much difficulty. All of them will automatically cache frequently accessed data in RAM.

Read / Write transactional workloads are usually I/O bound so the disk subsystem usually determines performance. A 7200 rpm disk can easily exceed 50MB / sec transfer speeds. On that basis I suggest you focus on application and database design. Poorly designed applications and databases are the cause of most performance problems, not hardware.

0
 
jmoss111Commented:
RAMDrives have been around for a long time and while they are considerably faster 30 - 60X than the electromechanical drives, like memory when the power drops so does your database stored on the ramdrive. So you have to copy the db from a hard drive to memory and before power drops you have to have the db back on a HDD.

Actually the HDD speed doesn't really matter except when interfacing the ramdrive to it but I do like Raptors.

Any database should handle it.
0
 
dportasCommented:
jmoss111, I guess you are referring to virtual drives in on-board RAM, rather than solid state drives. Solid state drives use non-volatile flash RAM. Data isn't lost when you lose power so they don't require spinning disk drives at all. But at the throughput that Choklander is talking about solid state drives would probably be a waste at today's prices. It would be better to invest in other hardware.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
jmoss111Commented:
dportas, Thats exactly what I was talking about because that is what the question is about.  I understand solid state drives, one of my clients builds them. I agree that SSD's aren't cost effective and I don't think that speed is their key selling point but rather ability to withstand shock and lower electrical consumption.

I wouldn't recommend using ramdrives... too many points of possible failure
0
 
ChoklanderAuthor Commented:
Well I read about SSD drives. What i do not like is the write speed. As I understand the smaller the transaction the slower it writes it. It needs larger pieces of data in order for the write speed to be average.

My confusion comes from the fact that I am new to this.

I was asking about physical (or virtual?) memory? But as I gather databases store certain parts of them on the memory anyhow and when needed access them?

I sow a comparison between Raptor, VelociRaptor and 32GB MemoRight GT SSD.
<a href="http://www.xlr8yourmac.com/IDE/SSD_vs_VelociRaptor_vs_Raptor/SSD_vs_VelociRaptor_Raptor.html">32GB MemorRight GT SSD vs VelociRaptor vs Raptor</a>

Now from:
QuickBench: Random Writes
QuickBench: Random Reads

We can clearly see that the Raptor is actually outperforming both on small portions of data (4 - 8kb).

As my database is small and the fact that each record can be no more than 60 bytes I think that I can safely say I just need 1 Raptor.

WD Raptor 74 GB Internal hard drive - 150 MBps - 10000 rpm.

That hard drive can even hold some 246 databases like mine.

Can't find a smaller.

And one last thing if someone of you can tell me. What is the deal with MySQL? I read that it was under the GPL. This means that if I want to use it for free I need to provide my source code of the application.

<a href="http://blog.marcocantu.com/blog/mysql_interbase_firebird.html">MySQL GPL license project></a>
0
 
ChoklanderAuthor Commented:
Sorry for the html tags. I inserted one too many ">".
0
 
jmoss111Connect With a Mentor Commented:
Choklander,

And my answer was pointed toward physical, virtual, memory or ram drives, whatever you want to call them. RAMDrive has been around since DOS days.

I don't know if WD quit making the 36GB drive but there wasn't much difference in price.

I don't think you really have to provide your source code; that really speaks to modifying the database engine itself.

Regards,

Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.