Migrating a 40TB SQL Server Database • Hacker News Board

Re: Migrating a 40TB SQL Server Database

#101

Post by nickpeterson » Fri, Jul 31, 2020, 1:39 AM UTC

BrentOzar wrote: post #17

posix_me_less wrote: post #4

> "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." Seems to me rather storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it. Anybody can explain why SQL database makes sense for large time series d…

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

I debate this a lot with coworkers. Our industry really loves novelty and new things, but very little expertise is available for new things. I’d rather have an expert at VB6 than a completely new Julia developer.

Re: Migrating a 40TB SQL Server Database

#102

Post by oneplane » Fri, Jul 31, 2020, 2:04 AM UTC

user5994461 wrote: post #82

oneplane wrote: post #54

Earlier quoted context omitted.

Unless the developers are HAProxy itself and made a module for HAProxy to log in to SQL databases (which it doesn't do by default) this is not even remotely related to the developers.

Plot twist: it does support SQL out of the box! HAProxy logs syslog messages, in a configurable format and to a destination of your choosing. All you have to do is to setup your favorite logging agent between fluentd/logstash/syslog-ng to listen on syslog and forward messages to a SQL database.

I know ;-) Hence my lawyer-ish choice of wording (default, module). HAProxy has some real nice features, especially the 1.8+ versions. We used to have configuration management tooling reconfigure and reload it, later on it get hot-reloads which was nice and then it got consul support so we could just feed consul and it all would work like magic. Love it.

My point was more about logging something straight in to a RDBMS wasn't much of a default at any point in time. Earlier on it was too expensive, and later on better systems came along. Some systems have built in text analysis and time series data specifics, but why would you worry about those when you have ElasticSearch and Kibana for most generic setups. Even before that you have syslog and a lot of log parsing libraries that worked great (well, unless you used windows event logs which has pros and a lot of cons).

Re: Migrating a 40TB SQL Server Database

#103

Post by oneplane » Fri, Jul 31, 2020, 2:08 AM UTC

Shog9 wrote: post #68

oneplane wrote: post #55

Earlier quoted context omitted.

It's not that the system itself isn't functional at all, but think about cost, flexibility (i.e. the migration as posted) and support. If you have to move that much data at once because your RDBMS doesn't work in any other way you're going to be in trouble, even if you get the 50k costing SSDs. All of the things SQL server does can be done with not-SQL-server things, but you suddenly gain the capability to take shard…

I'm sure there are better options. OTOH... I was at SO for about 9 years, and the system described existed for all of it - predating GraphQL, predating even ElasticSearch. There's stuff built on top of it that was never envisioned when it was created, and stuff that wouldn't have been possible without it. At this point, moving to something else would likely be a far more costly investment just in terms of requirement…

One important facet that isn't much technology related at all is the knowledge pool you can work with. Every time a generic component is implemented non-standard you essentially end up with unusable knowledge for both existing people leaving for another job and new people coming in with the common knowledge.

While most companies that are old enough to predate the iPhone usually have a lot of in-house technology that predates conventions and generic implementations, there is plenty of movement towards commonalty instead of keeping that special snowflake stuff in; at least at the places where I have/had influence.

When you freeze an in-house tool in time you essentially rob your own people of progress. The solution isn't rip-and-replace, but a process of continuous improvement is good for everyone.

Re: Migrating a 40TB SQL Server Database

#104

Post by oneplane » Fri, Jul 31, 2020, 2:14 AM UTC

nickpeterson wrote: post #67

jeffbee wrote: post #65

Earlier quoted context omitted.

There isn't a universal one. If you don't have a concrete one in mind, you shouldn't produce the log at all.

I appreciate the zen-like nature of this advice, but I think you also know how unreasonable it is most of the time, unless by 'concrete' you allow something as vague as, "troubleshoot production issues".

To be fair, a lot of problems came and come from 'let us store this stuff just in cases'. It also helps in some unforeseen cases, but in both scenarios you essentially end up in an unstructured unknown situation which is generally not what you want in IT, Business or as a person.

Re: Migrating a 40TB SQL Server Database

#105

Post by oneplane » Fri, Jul 31, 2020, 2:18 AM UTC

bob1029 wrote: post #62

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…

Logging to a database is probably a mistake if you haven't thought about how you'd use that data after you write it to disk. If you have good ideas about how you'd want to use that data, then it is probably a fantastic idea. We log to SQL so that we can instantly obtain a full list of log entries that pertain to a specific user action trace id. We can go from a collection of user actions over a larger business proces…

We use similar setups but with ElasticSearch instead of an RDBMS and we use cluster connections to query against disparate environments at the same time. They are not allowed to communicate to each other, but a 'placeholder' or 'empty' cluster that is allowed to talk to the others can give you exactly what you want for analysis and auditing. Another big benefit is that environments and other 'boxing' formats are isolated and cannot influence another. Same with lifecycle management, they do their own hot/cold/archive/deletion.

We usually end up querying based on session signatures (which is something like user agent + JWT), on plain user ids or on trace ids. This gets you a pretty neat timeline with references for full records if you need to drill down on a specific event. Makes everything super fast and when you need more data or want to read PII you can drill down (depending on your access roles).

Re: Migrating a 40TB SQL Server Database

#106

Post by wutwutwutwut » Fri, Jul 31, 2020, 3:14 AM UTC

jeffbee wrote: post #72

nickpeterson wrote: post #67

Earlier quoted context omitted.

I appreciate the zen-like nature of this advice, but I think you also know how unreasonable it is most of the time, unless by 'concrete' you allow something as vague as, "troubleshoot production issues".

Ad-hoc production troubleshooting is a reason to keep, at most, 7 days of logs. Usually you want the most recent minute or hour. Troubleshooting usually does not need collection, aggregation, and indexing because either the problem is isolated to a host or the logs of a single host, pod, or process are representative of what is happening in the rest of the fleet. Even if you want to access all logs, it's still better…

What a bunch of odd and arbitrary statements. Examples: I often use logs older than 7 days for troubleshooting. I rarely troubleshoot only using last minute or hour data. I need aggregation most of the time when troubleshooting. I also treat most runtime environments as cattle so relying on it to keep logs locally would be wrong.

Re: Migrating a 40TB SQL Server Database

#107

Post by AlfeG » Fri, Jul 31, 2020, 4:28 AM UTC

PaywallBuster wrote: post #22

ThePadawan wrote: post #9

Earlier quoted context omitted.

Because you've already paid the licensing cost for the SQL database and have salaried DBAs taking care of it. And building an alternative solution (including HA, backups etc.) would cost more than $0.

> I fully expected the process to take months, and it did — after hitting delays in getting new servers the entire project took about 11 months. This doesn't sound like it cost $0

But the real cost is not 11 month. Db Dev were not 100% working in this project, just monitor from time to time that everything is ok.

Re: Migrating a 40TB SQL Server Database

#108

Post by PaywallBuster » Fri, Jul 31, 2020, 6:04 AM UTC

jeffbee wrote: post #60

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…

I disagree with the premise of your statement. It's typical that a log will be accessed zero times. Collecting, aggregating, and indexing logs is usually a mistake made by people who aren't clear on the use case for the logs.

Indeed, I mostly stop looking at logs once I get the metrics from mtail in prometheus/grafana.

Re: Migrating a 40TB SQL Server Database

#109

Post by tedisaacs » Fri, Jul 31, 2020, 6:18 AM 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…

it’s adorable how proud you are to have worked at FAANG and how angry you get at the idea some other organisation handles equivalent scale

Re: Migrating a 40TB SQL Server Database

#110

Post by nwmcsween » Fri, Jul 31, 2020, 7:50 AM UTC

Someone really needs to make an anomaly filter for logs so data storage becomes miniscule for the purpose of anomaly detection, one Jacky way would be to preload a compression dict with bad strings from the binary, another would be to use something like hyperminhash to spit out low similarity strings.