-2

At my old job when a report was needed I could sit down with someone and pull up results and get immediate feedback, and then refine my queries and ultimately have the data we needed, in the format we needed within 30-90 minutes.

I just started working for a new company with a database containing millions of records and I spent my whole 8 hours making a report that I feel I could have made in less than 2 hours if it were not for the massive amount of data the queries are working with, and the fact that I couldn't ask the person needing the data to sit down with me and give me feedback as I pulled up results as I am used to.

So I am trying to think of how we can make the server faster... much faster, so that I can have the same level of productivity I'm used to. One thought that just came to mind is that memory is so cheap these days, and by my calculations I could buy 10 8gig ram sticks for 1000 bucks. What I have never heard of though is a device that would let me combine these into a huge ram drive.

So I'd like to know if any such device exists, and if not what is the largest ram drive I could realistically make and how would I go about doing so?

EDIT: To you guys who are saying the database shema needs to be analyzed... you can't make a query such as "Select f1, f2, f3, etc from SomeTable" run any faster by normalizing or indexing the table. What I'm talking about IS ABSOLUTELY a need for improved performance at the hardware level.

I am used to having results come back to me in a few seconds, not a few minutes or much less a half an hour. Maybe that's what you guys are used to who have 100 billion record tables and you feel like that's fast, but I'm looking for results back from tables with about 10 million records to come back to me withing less than half a minute TOPS.

Improve this question
4
  • "[M]assive amount of data the queries are working with"--are full table scans occurring?
    – Mark Wagner
    Mar 22, 2012 at 6:04
  • 6
    To make a huge RAM drive, first you must obtain the keys to the RAM. Then you get in, put the key in the ignition and turn it to Start. Then you should be able to make the RAM drive.
    – Tom O'Connor
    Mar 22, 2012 at 9:53
  • @MarkWagner Pretty much. For example I was asked to include results for one report where a particular field was >= 5. Well, that was about 96% of the records in the table.
    – Brandon Moore
    Mar 23, 2012 at 4:48
  • @TomO'Connor Thanks. That was at least as helpful as a couple of other answers I've gotten :)
    – Brandon Moore
    Mar 23, 2012 at 4:52

6 Answers 6

Reset to default
4

Checkout Fusion-IO's ioMemory Platform. They provide flash-based hardware with high capacity and great performance.

Improve this answer
3
  • Thanks, I actually found that right after I posted this question and given the performance gain I believe it will offer versus the amount of knowledge and expertise I would have to have to otherwise optimize and micro-optimize to math that... it seems like a no brainer to me. Definitely going to look into getting one of these.
    – Brandon Moore
    Mar 23, 2012 at 4:29
  • I realize this solutions annoys the heck out of people who have all that knowledge and expertise though... which is probably why my question got downvoted.
    – Brandon Moore
    Mar 23, 2012 at 4:30
  • Didn't deserve that. Hope you can figure it out! :)
    – pkhamre
    Mar 23, 2012 at 7:32
6

One thought that just came to mind is that memory is so cheap these days, and by my calculations I could buy 10 8gig ram sticks for 1000 bucks. What I have never heard of though is a device that would let me combine these into a huge ram drive.

This is one of those ideas that look good if you do not know what you do and fall apart when you think that through.

See, the problem is - you talk about making an 80gb RAM drive, but why not plug the RAM into the server and have SQL Server caching use the memory? 80GB is nothing these days for a server.

There is absolutely no need to use a RAM drive as SQL Server just needs enough RAM to cache all needed data.

Otherwise a nice SSD based disc system also helps - very fast and quite cost effective.

Btw:

a database containing millions of records

That is tiny. Come back when you say hundreds of billions these days.

Improve this answer
21
  • 1
    Tom, that's like saying the sun is tiny because there are much larger stars out there; compared to the rock we live on every day it's pretty large though just as a million records is compared to the average db. Did you answer this question because you wanted to be helpful or because you wanted to be arrogant? I found this: fusionio.com/platforms/iodrive that would totally be worth the cost considering that I could be multiple times more productive with this kind of speed and it's a pretty small fraction of what they're paying me.
    – Brandon Moore
    Mar 23, 2012 at 4:16
  • And can you get results back from a 100 billion record table in a few seconds? Because that's the kind of speed I'm after because I believe it's possible with just a million records. I don't think your solution will provide that.
    – Brandon Moore
    Mar 23, 2012 at 4:16
  • Yes, you surely can. You can get servers with 512gb ram these days and then put a high end storage solution behind. The database serve rI am working at the moment delivers me 5 ggiabyte per second IO performance for random IO and has a 21000gb storage subsystem based on 3 storage nodes. We have atables that grow by 60 million entries - per day.
    – TomTom
    Mar 23, 2012 at 5:00
  • Ok TomTom, run this query: "Select * From AnyTable", where 'AnyTable' has a minimum of 20 fields and tell me how fast the query results are returned. That is, not how fast you see results start to come up in SMSS, but how long before the entire query has been processed.
    – Brandon Moore
    Mar 23, 2012 at 5:05
  • I.e. Not trying to be argumentitive there... I actually would like to know.
    – Brandon Moore
    Mar 23, 2012 at 5:06
6

As TomTom already said, a RAM drive won't help a bit, but more RAM and/or faster drives for the database might help.

But other than throwing more hardware at this problem, it might be that your problem is really located elsewhere (and more hardware will have a lot less benefit than you expect):

  • Are you sure your indices and general DB design are good? They can have a huge performance impact.
  • The same is true for your queries. Can they be optimized for your specific data structures? Use your databases analysis tools to help you with both issues.
  • It appears that you work with a trial-and-error approach when creating the report queries, which I think is kind of odd. Is this because you don't get good requirements from the persons needing the report or because you don't fully understand the data structures and/or the query language? Both problems should be fixed, but if that's not possible, maybe you can work with a small subset of your production data to formulate the queries and only run them against the full dataset when your are satisfied with them.
Improve this answer
5
  • You say that "a RAM drive won't help a bit", and then say "but ... faster drives for the database might help." You realize that a RAM drive is a much faster drive... right? What am I missing here? Because those comments seem to be totally conflicting to me.
    – Brandon Moore
    Mar 23, 2012 at 4:26
  • To answer your questions: 1) The indexes pretty much suck on this db and I'm working on that, but read the edit and other comments I've made to understand why this isn't relevant for my question. 2) No, the queries can't be further optimized. Just trust me on this one... they definitely aren't that complex to begin with. 3) Like I said it's a new job (only been there a week) and I got to spend almost no time with the previous employee so yes... I'm working on learning the schema and also this is a new industry for me. However...
    – Brandon Moore
    Mar 23, 2012 at 4:57
  • those impediments are minute compared to the slowness of the db. If I could have gotten results back as fast as I am used to then I could have finished in a fraction of the time. Instead I spent more time creating small subsets of the data to work with to formulate the queries with than I did actually creating the queries.
    – Brandon Moore
    Mar 23, 2012 at 4:59
  • @BrandonMoore: Re. the RAM drive: Having more actual RAM available for the database's caching usually will help more than have an ultra-fast disk, at least once the data is fully cached. The caching strategies are optimized for the case of faster RAM and slower disks. You can help the the overall performance even more if you increase the speed of the disks (e.g. with SSDs), but not if you sacrifice RAM for a RAM disk.
    – Sven
    Mar 23, 2012 at 10:57
  • Aaaah, I see where the miscommunication is now. The hope was to somehow run the db on some kind of ram drive, but NOT with the intention that the ram for this drive would be coming from the ram the server itself is running on. I.e. FusionIO is exactly the answer I was looking for, because I previously didn't know if something like it existed or not.
    – Brandon Moore
    Mar 23, 2012 at 22:51
3

It sounds like the database is designed pretty badly or the server hardware is seriously underpowered if it is taking such a long time to run queries on a database of that size. As the poster above said, millions of records is not so large these days.

From your original question about adding hardware to ease the issue I would guess that you are either in charge of the server(s) or have some influence over it. Is normalizing the DB an option? What hardware is it running on?

If you really wanted to use a RAM disk an SSD is probably a more reliable and effective means of achieving this these days. However I really would suggest analysing the database schema and whether the current hardware is up to scratch first and then making a decision.

We run databases (SQL Server, MySQL and PostGreSQL) that have millions of records on fairly lowly hardware (think Pentium-D, 7200RPM drives in RAID-10) and have very fast database responses and this is due to the efficient normalization of the DB schemas.

Improve this answer
3
  • Maybe I should have provided more detail when I wrote the question... running a "select field1, field2, field3, etc from sometable" can take a few minutes for a million records. Not much an index or normalization can do to help that query. It's on a virtual machine with 12 gigs of ram. Like I said... just started at the job and no I'm not a db or networking admin, but I know they are fixing to move to a clustered environment and my understanding is that this only improves availability and not really performance. So I just want to give what input I can to make sure their efforts are fruitful.
    – Brandon Moore
    Mar 23, 2012 at 4:34
  • See. Crappy hardware. A VM with 12gb ram is laughabl as high end server. The system IW was talking of has 96gbRAM and 24 real cores+ hyperthreading. AND uses multiple such machiens at the same time.
    – TomTom
    Mar 24, 2012 at 5:38
  • Ha, see what? You were emphasizing the need to analyze the db and 'I' was pointing out it was a hardware issue (though I don't know the best solution). But at least we're in agreement :)
    – Brandon Moore
    Mar 24, 2012 at 7:33
1

Well, both sql server and oracle support to run in RAM although db still sits physically on hard drive. Also other RDBMS providers support this functionality. In these days bladecenters and other modular server system are in touch, but when looking for something really special, I always look at TYAN motherboards which support huge amount of installed RAM.

But still I think milions of records is not something what couldn't be handled in short time, I would rather first take a look at schema, especially at indexes and relation collumns between table.

I also would like to propose take a look at Fusion-IO platform, but when thinking about big storage it could became really expensive. When rally trying to configure big IO throughput you could think about combine 2 cheap raid array using external raid card to RAID 100 which is really unbeatable. Even there is no array controller which directly supports RAID 100, you could build 2 disk arrays, each in RAID 10 and then connect both array together using RAID 0 using external additional array controller, so you have mirrored storage stripped on 2 levels, the IO performance boost in real is about 150-180 % and this solution could be cheap. In case of you don't like to lost half of storage, you could think about RAID 600 in that case, which is still stable, but uses just 2 disc for parity. This could be solution is better against RAID 100 if startup RAID 6 is configured using at minimum of 5 hard drivers.

Improve this answer
1
  • +1. Thanks, I agree that FusionIO seems pretty expensive... but if the results come back fast enough that I can run ad-hoc queries in front of the people needing reports so they can give me immediate feedback, allowing me to refine the reports and give them what they need in 2 hours instead of 8, then I think it's well worth the cost.
    – Brandon Moore
    Mar 23, 2012 at 4:39
1

Oracle will in fact load the most frequently used data in RAM. And if your database fits in RAM it will place it in RAM for you. It does need to be configured correctly to do it.

I assume that the data you access is sitting along with other data competing for server resources. You could copy the data that you need into a separate database, it could even be oracle. And that is in fact what a lot of people do.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .