Migrating a 40TB SQL Server Database • Hacker News Board

Re: Migrating a 40TB SQL Server Database

#91

Post by jeffbee » Thu, Jul 30, 2020, 11:05 PM UTC

FridgeSeal wrote: post #89

jeffbee wrote: post #84

Earlier quoted context omitted.

It's adorable when people think scientific computing has the same scale as a Google or Microsoft.

Sorry what was that? https://www.spie.org/news/photonics-focus/mayjun-2020/square...

Ignoring the fantasy b.s. in the second half of the article, the stuff at the top is exactly what I mean.

A mighty 400 GB/s: i.e. much less than the > 50 PB/day of logs the other person mentioned;

1600 hours of SD video per second: i.e. about 1-2 million concurrent HD streams, or much less than the amount actually served by YouTube.

IBM Summit "world's most powerful supercomputer": < 5000 nodes, i.e. much below the median cell size described in the 2015 Borg paper. Summit is a respectable computer but it would get lost in a corner of a FAANG datacenter.

Re: Migrating a 40TB SQL Server Database

#92

Post by greggyb » Thu, Jul 30, 2020, 11:13 PM UTC

hansitomani wrote: post #19

Wow. I really wana know if they even compress the log data in there database. 40 TB of logs in a postgresql, like that can't be efficient? I would probably just have a scaling blob store and would store it away. That has to be much faster, simpler and cheaper.

They mentioned in the article that they are using SQL Servers clustered columnstore index. This is a poor name, as it's more than just an index. CCI in SQL Server is an on-disk storage format that is a highly compressed columnstore.

Re: Migrating a 40TB SQL Server Database

#93

Post by techntoke » Thu, Jul 30, 2020, 11:22 PM UTC

dkdk8283 wrote: post #90

techntoke wrote: post #59

Earlier quoted context omitted.

So you're paying roughly 72,000 per year? Or are you using SSDs?

gp2. master and multiple read slaves. we we pay millions per year for AWS and support.

Can I submit a proposal?

Re: Migrating a 40TB SQL Server Database

#94

Post by idlewords » Thu, Jul 30, 2020, 11:22 PM UTC

I'm sitting in a hotel waiting for 22 TB of data to copy over, so I could not have read this piece with more interest. Moving data in these quantities is like pouring tar, and it's always great to hear war stories from people who have done it. It is always more painful than message board theory would suggest.

Re: Migrating a 40TB SQL Server Database

#95

Post by lukeschlather » Thu, Jul 30, 2020, 11:24 PM UTC

jeffbee wrote: post #91

FridgeSeal wrote: post #89

Earlier quoted context omitted.

Sorry what was that? https://www.spie.org/news/photonics-focus/mayjun-2020/square...

Ignoring the fantasy b.s. in the second half of the article, the stuff at the top is exactly what I mean. A mighty 400 GB/s: i.e. much less than the > 50 PB/day of logs the other person mentioned; 1600 hours of SD video per second: i.e. about 1-2 million concurrent HD streams, or much less than the amount actually served by YouTube. IBM Summit "world's most powerful supercomputer": < 5000 nodes, i.e. much below the m…

CERN is a correct example. The LHC reportedly generates 1PB per second: https://home.cern/news/news/computing/cern-data-centre-passe...

Re: Migrating a 40TB SQL Server Database

#96

Post by jeffbee » Thu, Jul 30, 2020, 11:29 PM UTC

lukeschlather wrote: post #95

jeffbee wrote: post #91

Earlier quoted context omitted.

Ignoring the fantasy b.s. in the second half of the article, the stuff at the top is exactly what I mean. A mighty 400 GB/s: i.e. much less than the > 50 PB/day of logs the other person mentioned; 1600 hours of SD video per second: i.e. about 1-2 million concurrent HD streams, or much less than the amount actually served by YouTube. IBM Summit "world's most powerful supercomputer": < 5000 nodes, i.e. much below the m…

CERN is a correct example. The LHC reportedly generates 1PB per second: https://home.cern/news/news/computing/cern-data-centre-passe...

If you define “generates” to mean “discards” then yes.

Re: Migrating a 40TB SQL Server Database

#97

Post by idlewords » Thu, Jul 30, 2020, 11:32 PM UTC

blyat wrote: post #75

polskibus wrote: post #64

Earlier quoted context omitted.

How would you 'do it right'?

I've only done a few 30+TB moves, largest being 50TB which had similar table structure - one table per machine holding calibration readings that were constantly streamed in from a MUCH beefier RabbitMQ instance. Old CPU, not a ton of cores, spinning disks, etc. Same base problem. I think the best thing to do here would be change the order of some things around. 1. Buy the hardware first with blazing drives and roarin…

Having step 1 of your solution be "eliminate the hardware constraints that made this job painful" is kind of a cheat. We can all do a better job on any problem if we lift those constraints.

Re: Migrating a 40TB SQL Server Database

#98

Post by m3047 » Thu, Jul 30, 2020, 11:45 PM UTC

bryan_w wrote: post #69

tutfbhuf wrote: post #6

Earlier quoted context omitted.

Agreed, it's questionable why a company needs to save raw traffic logs for years.

You say that but people were pretty upset that Google couldn't confirm if anybody exploited the G+ security bug past the last two weeks that it was discovered.

+1 for them. Pretty cynical, I know. But imagine the request for legal discovery...

Re: Migrating a 40TB SQL Server Database

#99

Post by pstrateman » Thu, Jul 30, 2020, 11:52 PM UTC

jeffbee wrote: post #91

FridgeSeal wrote: post #89

Earlier quoted context omitted.

Sorry what was that? https://www.spie.org/news/photonics-focus/mayjun-2020/square...

Ignoring the fantasy b.s. in the second half of the article, the stuff at the top is exactly what I mean. A mighty 400 GB/s: i.e. much less than the > 50 PB/day of logs the other person mentioned; 1600 hours of SD video per second: i.e. about 1-2 million concurrent HD streams, or much less than the amount actually served by YouTube. IBM Summit "world's most powerful supercomputer": < 5000 nodes, i.e. much below the m…

400GB/s is about 35 PB/day

not quite as big a difference

Re: Migrating a 40TB SQL Server Database

#100

Post by Shog9 » Fri, Jul 31, 2020, 12:20 AM UTC

adfinoinio wrote: post #33

BrentOzar wrote: post #25

Earlier quoted context omitted.

> I'm baffled with the fact that they were toying with the production database in the production machine. That sounds incredibly dangerous As Taryn stated repeatedly in the post, they simply didn't have enough infrastructure to have development spare copies of this. They didn't even have enough infrastructure to take a backup.

Which is obviously nuts. If your developers are ever spending months of their time to work around infrastructure limitations, that means you need more hardware. You could build a new server to handle this job easily for much less than the cost of this person's time.

Unfortunately, the exchange rate for converting slivers of dev time into hardware is... Unfavorable. And up until two days ago[0], Stack Overflow was running on fumes - shedding staff, perks, equipment to keep the ship afloat. I know of folks who spent months scrambling to get new servers before the old ones died, and... Didn't quite make it.

Given the situation, investing time into making do probably seemed like a much better bet than hoping for a windfall with which to buy equipment.

[0]: https://news.ycombinator.com/item?id=23976881