News for hackers. Stuff that matters.
Re:Migrating a 40TB SQL Server Database by polygotdomain on Thursday July 30, 2020 @01:03PM (#24000490)
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 by amichal on Thursday July 30, 2020 @01:17PM (#24000651)
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.
Re:Migrating a 40TB SQL Server Database by user5994461 on Thursday July 30, 2020 @01:49PM (#24001029)
I've done with a hundred billion rows. It's not too difficult but it takes a lot of time. Note that elasticsearch scales really easy and really well, I have an article on a small setup many years ago for 12 TB in a small company https://thehftguy.com/2016/09/12/250-gbday-of-logs-with-gray...

The challenge is, a migration would require a lot of hardware resources (storage mainly) to setup a new cluster and the poster couldn't get one damn hard drive to work with, so any migration would have been a death march from the start.

Knowing that and that the original system was out of disk and had no backup of any sort. I would personally question the effort to try to keep all these old logs.

Re:Migrating a 40TB SQL Server Database by jeffbee on Thursday July 30, 2020 @03:26PM (#24001967)
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.
Re:Migrating a 40TB SQL Server Database by jrbancel on Thursday July 30, 2020 @04:05PM (#24002320)
Absolutely, the vast majority (95%+) of logs are never read by a human. Therefore, processing it is enormously wasteful. A good architecture will write once and not touch anything until it is needed.

I spent years working on system handling 50+PB/day of logs. No database or ELK can handle that, and even if it did it would be prohibitively expensive.

Re:Migrating a 40TB SQL Server Database by Keyframe on Thursday July 30, 2020 @05:15PM (#24002899)
Where did you work? CERN?
Re:Migrating a 40TB SQL Server Database by jeffbee on Thursday July 30, 2020 @06:23PM (#24003501)
It's adorable when people think scientific computing has the same scale as a Google or Microsoft.
Re:Migrating a 40TB SQL Server Database by FridgeSeal on Thursday July 30, 2020 @06:53PM (#24003702)
Re:Migrating a 40TB SQL Server Database by jeffbee on Thursday July 30, 2020 @07:05PM (#24003785)
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 by lukeschlather on Thursday July 30, 2020 @07:24PM (#24003905)
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 by jeffbee on Thursday July 30, 2020 @07:29PM (#24003945)
If you define “generates” to mean “discards” then yes.
Re:Migrating a 40TB SQL Server Database by welterde on Friday July 31, 2020 @04:05AM (#24006689)
It still gets processed though and only all non-interesting events get discarded..

Otherwise the tape alone to store it on would exceed their total operating budget in a day, so they have to be a bit clever about it.

Re:Migrating a 40TB SQL Server Database by tutfbhuf on Saturday August 01, 2020 @02:01PM (#24022548)
I think that even Google can not save 1PB/s in 2020.
Re:Migrating a 40TB SQL Server Database by welterde on Friday July 31, 2020 @04:35AM (#24006841)
The numbers are not fantasy at all - this will be a huge radio telescope - one square kilometer of pure collecting area and thousands of receiving antennas (For reference: Arecibo has around 0.073 km^2). We are talking data input to the correlator on the terabit/s scale. And technology-demonstration with ASKAP are well under way. ALMA is working quite well by now as well (> 600 Gb/s with just an 50 antenna array).
Re:Migrating a 40TB SQL Server Database by tedisaacs on Friday July 31, 2020 @02:18AM (#24006190)
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 by andialo on Friday July 31, 2020 @09:57AM (#24008616)
touche
Re:Migrating a 40TB SQL Server Database by pstrateman on Thursday July 30, 2020 @07:52PM (#24004135)
400GB/s is about 35 PB/day

not quite as big a difference

Re:Migrating a 40TB SQL Server Database by Keyframe on Friday July 31, 2020 @09:44AM (#24008481)
So, Youtube puts whole streams into their logs? Interesting.
Re:Migrating a 40TB SQL Server Database by PaywallBuster on Friday July 31, 2020 @02:04AM (#24006131)
Indeed, I mostly stop looking at logs once I get the metrics from mtail in prometheus/grafana.
Re:Migrating a 40TB SQL Server Database by j88439h84 on Thursday July 30, 2020 @03:44PM (#24002149)
What is the use case for logs?
Re:Migrating a 40TB SQL Server Database by jeffbee on Thursday July 30, 2020 @04:02PM (#24002300)
There isn't a universal one. If you don't have a concrete one in mind, you shouldn't produce the log at all.
Re:Migrating a 40TB SQL Server Database by nickpeterson on Thursday July 30, 2020 @04:09PM (#24002348)
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".
Re:Migrating a 40TB SQL Server Database by jeffbee on Thursday July 30, 2020 @04:14PM (#24002397)
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 to leave them where they were produced and push a predicate out to every host; your log-producing fleet has far, far more compute resources than your poor little central database, no matter how big that DB is.
Re:Migrating a 40TB SQL Server Database by wutwutwutwut on Thursday July 30, 2020 @11:14PM (#24005289)
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 by closeparen on Friday July 31, 2020 @10:25PM (#24016827)
All but trivially reproducible bug reports require, or benefit immensely from, logs about the transaction in question. The pipeline from support to product to engineering to an actual investigation is usually much more than 7 days.
Re:Migrating a 40TB SQL Server Database by j88439h84 on Thursday July 30, 2020 @04:50PM (#24002706)
May I ask what kind of production environments you have in mind? Are these large-scale FAANG-style deployments or something else?
Re:Migrating a 40TB SQL Server Database by jeffbee on Thursday July 30, 2020 @06:38PM (#24003605)
Well, only to the extent that the management of small amounts of logs is not very interesting. There is not an ACM SIG for very small databases.

Anyway, GPDR requires you to have a purpose for any log that contains any IP address. Keeping logs for undefined purposes and unlimited time frames is not ok any more.

Re:Migrating a 40TB SQL Server Database by elorant on Thursday July 30, 2020 @05:25PM (#24003014)
It could be an e-commerce site, which depending on the case can produce a shitload of logs. Imagine having a few hundred of thousands of users daily and you record every page they view, along with heatmaps, and whatnot. In most cases those logs should never be touched by a human in raw format. You feed them in your analytics engine, and start making decisions about your conversion. And then you delete them because the goalpost is constantly moving.
Re:Migrating a 40TB SQL Server Database by oneplane on Thursday July 30, 2020 @10:14PM (#24005016)
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 by bob1029 on Thursday July 30, 2020 @03:34PM (#24002047)
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 process and then for each action we can pull all of the log entries that were generated. All of this is exposed through a nice web interface with full text search capability over the log messages. Without logging to SQL, we would have a hell of a time building something similar.

In order to keep this from exploding out of control, we have a strict 90 day expiration policy on all persisted business state and log entries. Our log table indicies are:

- User Action Trace Id (16 bytes)

- Timestamp (64 bits)

- Message (Variable - FTS)

We store all log entries in a single SQLite database (logs.db) contained in each environment. These are queried over HTTP from centralized management tools.

Re:Migrating a 40TB SQL Server Database by oneplane on Thursday July 30, 2020 @10:18PM (#24005046)
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 by tutfbhuf on Saturday August 01, 2020 @04:00AM (#24018639)
> 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.

There were always a bunch of bad decisions and a few good one in hindsight. I think it's completely fair to discuss solutions (including more recent tech like ELK) to the problem even more so if the problem still exists.

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

No I disagree, not logging 40 TB of data to SQL would have been at least an equally good if not better choice, based on the input we have from the blog post. Just keep the past few weeks of raw traffic logs and be fine.