Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Does my data fit in RAM?

yourdatafitsinram.net

Post reply on HN
Print view View original

101–110 of 167 posts

Re: Does my data fit in RAM?

#101

Post by quersive »

ramraj07 wrote: post #2

Legit question: I have a dataset that's a terabyte in size spread over multiple tables, but my queries often involve complex self joins and filters; for various reasons, I'd prefer to be able to write my queries in SQL (or spark code) because it's the most expressive system I've seen. What tool should I use it to load this dataset on RAM and run these queries?

[Disclaimer: I worked on BigQuery a couple lives ago]

I'd give Google BigQuery a shot. Should work fast [seconds] and scale seamlessly to [much] larger datasets and [many] more users. For a 1 TB dataset, I have a hard time imagining crafting a slow query. Maybe something outlandish like 1000[00?] joins. They also have an in-memory "BI Engine" offering, alas limited to 50GB max.

On premise, there is Tableau Data Engine. I don't think they offer a SQL interface, you have to buy into the entire ecosystem.

Long shot: I've been working on "most expressive query system over multiple tables" as an offshoot of some recent NLP work. Your use case piqued my interest. I'd love to help / understand it better. My contact is in my profile.

Re: Does my data fit in RAM?

#102

Post by wtallis »

thedance wrote: post #67
keanzu wrote: post #48

Earlier quoted context omitted.

Samsung claims their 970 Pro NVMe can read 3.5GB/s sequentially. That's about 300 seconds or 5 minutes per TB.

It can't though.

It can, and their fastest enterprise SSD can write at that speed too, or do sequential reads at 7-8GB/s, or random reads at over 4 GB/s.

I just ran `time cp /dev/nvme0n1 /dev/null` on the 1TB 970 Pro. The result:

  real    4m50.724s
  user    0m2.001s
  sys     3m10.282s
So with literally zero optimization effort, we've hit the spec (and saturated a PCIe 3.0 x4 link).

Re: Does my data fit in RAM?

#103

Post by threeseed »

andyjpb wrote: post #96
threeseed wrote: post #77

Earlier quoted context omitted.

I really don't understand comments like this. Yes your company's data may fit in RAM. But does every intermediate data set also fit in RAM ? Because I've also worked at a bank and we had thousands of complex ETLs often needing tens to hundreds of intermediate sets along the way. There is no AWS server that can keep all of that inflight at one time. And what about your Data Analysts/Scientists. Can all of their random…

Buy them a machine each. $100K has always been "cheap" for a "business computer" and today you can get more computer for that money than ever. $100K of hardware (per year or so) is small-fry compared to almost every other R&D industry out there. Just compare with the cost of debuggers, oscilloscopes and EMC labs for electronic engineers.

My company has over 400 Data Scientists and 1000s of Data Analysts.

Buy them a machine each at a cost of $40-60 billion ?

Or would it make more sense to buy one Spark cluster and then share the resources at a fraction of the cost.

Re: Does my data fit in RAM?

#104

Post by rconti »

siffland wrote: post #9

Funny story, about 6 years ago we got a HP DL980 server with 1TB of memory to move from an Itanium HP-UX server. The test database was Oracle and about 600GB in size. We loaded the data and they had some query test they would run and the first time took about 45 minutes (which was several hours faster than the HPUX), They made changes and all the rest of the runs took about 5 minutes for their test to complete. Final…

Those were some poorly built systems. I worked on probably 10 of them and they not-infrequently had .. major issues. HP's support model was to send a tech out with 2 sticks of RAM, and try them in different places to try to trace memory failures... across 4 (or 8?) cassettes, and 64 sticks of ram, and 20+ minute POST times.

We eventually had one server entirely replaced at HP's cost after yelling at them long enough, and that one never worked well enough to ever use in production, either. I'd say we had maybe a 70-80% success rate with those servers. They were beasts, though, with 4TB of RAM as I recall, and 288 cores.

Re: Does my data fit in RAM?

#105

Post by yongjik »

Even more importantly, does your data have to fit in RAM?

There are tons of problems that need to process large data, but touch each item just once (or a few times). You can go a really long way by storing them in disk (or some cloud storage like S3) and writing a script to scan through them.

I know, pretty obvious, but somehow escapes many devs.

Re: Does my data fit in RAM?

#106

Post by yongjik »

dmos62 wrote: post #78
FridgeSeal wrote: post #70

Earlier quoted context omitted.

And reducing it to a single machine means I have an order of magnitude less time spent on setting it up, maintaining it, adapting all my code, debugging, etc. These days I’m firmly of the opinion that if you can make it run on a single machine, you absolutely should, and you don’t get more machines until you can prove you can fully utilise one machine.

Account for parent's concerns for redundancy, backups, scalability.

If you store data in something like AWS S3, "redundancy" and "backup" is handled by AWS. And scalability is a moot point if a single box can handle your load.

When a single box cannot handle your load, you start one more, and then you start worrying about scalability.

(Of course, YMMV, I guess there are cases where "one box" -> "two boxes" requires an enormous quantum jump, like transactional DB. For everything else, there's a box.)

Re: Does my data fit in RAM?

#107

Post by dana321 »

ramraj07 wrote: post #2

Legit question: I have a dataset that's a terabyte in size spread over multiple tables, but my queries often involve complex self joins and filters; for various reasons, I'd prefer to be able to write my queries in SQL (or spark code) because it's the most expressive system I've seen. What tool should I use it to load this dataset on RAM and run these queries?

If you run on MySQL/InnoDB you can set innodb_buffer_pool_size=1000Gb and it should cache your data after the first query.

Re: Does my data fit in RAM?

#108

Post by keanzu »

wtallis wrote: post #102
thedance wrote: post #67

Earlier quoted context omitted.

It can't though.

It can, and their fastest enterprise SSD can write at that speed too, or do sequential reads at 7-8GB/s, or random reads at over 4 GB/s. I just ran `time cp /dev/nvme0n1 /dev/null` on the 1TB 970 Pro. The result: real 4m50.724s user 0m2.001s sys 3m10.282s So with literally zero optimization effort, we've hit the spec (and saturated a PCIe 3.0 x4 link).

Impressive performance for a $345 consumer grade SSD.

https://www.newegg.com/samsung-970-pro-1tb/p/N82E16820147694

Re: Does my data fit in RAM?

#109

Post by mr_toad »

ksec wrote: post #43

The problem is DRAM price hasn't drop one bit. The lowest price floor per GB has been similar for the past decade. Roughly at $2.8/GB in 2012, 2016, and 2019. And all DRAM manufacturers has been enjoying a very profitable period. And yet our Data size continue to grow. We can fit more Data inside memory not because DRAM capacity has increase, but we are simply increasing memory channels.

If it was just a matter of adding more memory people would. A few thousand, or tens of thousands of dollars aren’t much to organisations that have that much data.

The trouble is that there are limits to how much memory you can fit on a motherboard.

Re: Does my data fit in RAM?

#110

Post by Aeolun »

ghc wrote: post #18

Fine print: But it might cost you $300K CapEx or $800K/yr OpEx. Hope you have a budget!

Meh, that’s like half the budget of the databases on our dev environment.

We have way too many fucking copies of our DB.

Post reply on HN