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

Migrating a 40TB SQL Server Database

tarynpivots.com

Post reply on HN
Print view View original

31–40 of 126 posts

Re: Migrating a 40TB SQL Server Database

#31

Post by hogFeast »

posix_me_less wrote: post #16
abhishekjha wrote: post #12

Earlier quoted context omitted.

>storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it I get this question asked a lot of times in the interview. Why is it a bad idea to store logs in a SQL database? It has the ACID properties baked in and it is pretty reliable. What is the better alternative? I guess I have never worked on anything that would require keeping track of 40TB of data because of which the drawbac…

Because everything gets substantially slower and more difficult, see 11 months of busy work motivated by queries being slow, described in TFA. SQL is great for querying data, but you don't need to store the whole history in a single database. ACID is a red herring. These are traffic logs, not some precious global state that has to handle ACID transactions. The better alternative is to dump the data into many big file…

Then use partitioning (which the OP appears to be doing)?

If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? With SQL, your data is presumably going to be non-contiguous so you are going to get slower seeks on disk...but (I believe, although I am not 100% familiar) this can happen with noSQL if you add an index (which are often BTree...so the same as SQL under the hood) and you have things like SSTables/compaction potentially slowing performance too (although maybe not an issue in this application...I don't know).

If you are doing some kind of full table scan though, there is no weird magic in noSQL that allows you to perform this faster afaik. You are reading from disk, that is your limit. The point, afaik, with noSQL for logging is that writes are faster so the throughput is larger...which can be required for this use case but probably isn't relevant for everyone.

Re: Migrating a 40TB SQL Server Database

#32

Post by polygotdomain »

I think some of the posts here miss a little bit of the context as to why things like this happen in the first place. It's only in the last handful of years that a stack for logging has really become mainstream. Chances are a lot of these types of logging solutions predate that and used whatever persistence technology was readily available. Writing to files on web servers can be a pain, and these logs will have to be queried at some point, so storing it in a database is not a bad idea, especially when better options have only recently become available.

The problem is that relational SQL is bad for logs, but by the time it gets to the scale where it's problematic that there's too much volume in the logs to make anything "easy". Simultaneously there's a lot of business value in that log data that you don't want to lose.

Yes, SQL's a poor fit for logs, but it's a better fit then a lot of other things, including not logging at all. Better solutions exist, but they don't exist in a bubble, and there's a cost to integrating them and migrating to them. A lot of these comments seem to be judging a technology decision based solely on hindsight without realizing that there are legitimate reasons for logging to SQL.

Re: Migrating a 40TB SQL Server Database

#33

Post by adfinoinio »

BrentOzar wrote: post #25
yknx4 wrote: post #24

I'm baffled with the fact that they were toying with the production database in the production machine. That sounds incredibly dangerous

> 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.

Re: Migrating a 40TB SQL Server Database

#34

Post by BrentOzar »

jto1218 wrote: post #27

> There were lots of reasons this needed to be done, one being tech debt. We realized that the original daily table structure wasn’t ideal. If we needed to query something over several days or months, it was terrible — lots of UNION ALLs or loops to crawl through days or even months at a time was slow. Maybe i'm missing it, but there doesn't seem to be any discussion of the result. _How_ much faster was it after the…

> Maybe i'm missing it, but there doesn't seem to be any discussion of the result.

Sometimes you have to stop writing after 9,000 words. ;-)

Re: Migrating a 40TB SQL Server Database

#35

Post by dsr_ »

rrdharan wrote: post #2

It seems concerning that they would be hanging onto traffic logs for that long. Seems like a massive PII / data breach liability.

Right here at the beginning:

"Our HAProxy1 logs aka traffic logs, are currently stored on two SQL Servers, one in New York, and one in Colorado. While we store a minimal summary of the traffic data, we have a lot of it. At the beginning of 2019, we had about 4.5 years of data, totaling about 38TB. "

and I asked myself, why the hell would you want this data?

Logs serve two purposes:

- find a fault and fix it - audit what someone (or many someones) did

You never need years of data to find evidence of a fault: either it's available in the last few weeks or you didn't log the data in the first place (unless, of course, it's a bug that only shows up on leapdays, or leapseconds, or is similarly rare...)

So that leaves tracking your users in a way which isn't covered by your user's own history. Again, a month or two might be helpful, sometimes... but why years?

Re: Migrating a 40TB SQL Server Database

#36

Post by thisisbrians »

I'm in the process of migrating a ~5.4TB time series database. This is also slow/painful. I wonder if there are any general-purpose/open source tools that could help with these sorts of tasks (I've had to roll my own job queue/ETL worker script infrastructure to manage this). In my case, it looks like the full migration will take around 10 calendar days assuming no issues come up.

Re: Migrating a 40TB SQL Server Database

#38

Post by gbrayut »

slg wrote: post #26
BrentOzar wrote: post #17

Earlier quoted context omitted.

> Anybody can explain why SQL database makes sense for large time series data? Let's say, just to say, that you have effectively free licensing for Microsoft SQL Server (because you already own the licenses in question), and you have staff who know that platform well. Sometimes it's easier to use the tools you already have rather than go acquire a new platform to achieve a goal. As with anytime you choose a persisten…

>and you have staff who know that platform well. People often ignore how important this is, especially for a small team like Stack Overflow appears to have. A new platform requires training and/or a lack of productivity as employees learn the new system. It requires developer time to convert over any work designed to use the old system. It makes hiring more difficult because now you will either want someone with the…

Case in point, one of my favorite system architecture/design blog posts:

Providence: Failure Is Always An Option

https://jasonpunyon.com/blog/2015/02/12/providence-failure-i...

Which has one of my favorite quotes about scope creep:

Kevin and I have essentially become allergic to big projects. We attempt to practice “What can get done by Friday?” driven development. Keeping things small precludes a whole class of errors like “We need a new datastore”, ‘cause that ain’t gettin’ done by Friday. It’s hard to sink a week on something you weren’t supposed to be working on when all you have is a week.

Re: Migrating a 40TB SQL Server Database

#39

Post by thisisbrians »

dswalter wrote: post #37

According to their pricing page, a 50TB SSD from Nimbus costs $12,500. That would have been worth the expense in DBA time alone, right? Edit: added the link. https://nimbusdata.com/products/exadrive/pricing/

Yes. Or even stripe some cheaper $/TB drives together in a RAID to cover the needs.

Re: Migrating a 40TB SQL Server Database

#40

Post by amichal »

polygotdomain wrote: post #32

I think some of the posts here miss a little bit of the context as to why things like this happen in the first place. It's only in the last handful of years that a stack for logging has really become mainstream. Chances are a lot of these types of logging solutions predate that and used whatever persistence technology was readily available. Writing to files on web servers can be a pain, and these logs will have to be…

Totally agree. I would be curious if anyone who is saying why didn't you use XYZ has actually migrated 500 billion rows of queryable log data from one tech to the other. For everyone complaining about PII. The article shows a 6 year log retention which fits with many regulatory requirements for data retention. We forget that a lot changes in 6 years on what makes sense to do. ELK stack according to elastics history page became a real thing in 2015.
Post reply on HN