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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dportasCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
jmoss111Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Server Hardware

From novice to tech pro — start learning today.