So this is all interesting from a technical point of view but from a business point of view it makes no sense. The amount of time that you had a talented DB dev working on a project that could have been solved by spending ~25K on a new system seems silly. Bring the new system up and move the data over the network doing the conversion as part of the move. 100TB over a 10G network is ~22H theoretical transfer rate.
Migrating a 40TB SQL Server Database
81–90 of 126 posts
Re: Migrating a 40TB SQL Server Database
#82Earlier quoted context omitted.
> Why on earth would you do this, and even then not take the opportunity to do it right while you are migrating anyway? Because as a database administrator, you often have to solve hosting problems without telling your developers to rewrite all their code.
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.
Re: Migrating a 40TB SQL Server Database
#83Earlier quoted context omitted.
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 w…
> unless, of course it's a bug that ... is similarly rare... I'll give an example: a bug in the revision ordering logic that could be triggered by two edits being submitted almost simultaneously where one of the editors had made another edit within the past few minutes. The bug resulted in rows being interleaved between the two revisions and the post being rendered as a combination of two unrelated edits. Almost impo…
"at this scale even very unlikely bugs do show up with some frequency" exactly! In the past I've worked on systems where "this will never happen" occurs because "this will never happen" really means the probability of this occurring is extremely low. But when you're dealing with millions of possibilities that low probability event starts occurring every day. And when it occurs for one of your largest customers it's no longer "this will never happen" but "why is this happening?"
Re: Migrating a 40TB SQL Server Database
#84Earlier quoted context omitted.
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.
Where did you work? CERN?
It's adorable when people think scientific computing has the same scale as a Google or Microsoft.
Re: Migrating a 40TB SQL Server Database
#85I've done migrations of several databases with ~20TB of data before. Assuming you're data is immutable, A much easier approach than what they did is to restore a backup of the database onto a new machine. Since you don't have to worry about serving production traffic you can have the machine go full throttle on performing the migration. When the migration has finished you have to copy the new data that came in during…
Similar thought here. Was kind of surprised they didn't go with some kind of checkpoint/snapshot approach for the data sets, + stream them to the new machine, + add any newly arrived log data.
Re: Migrating a 40TB SQL Server Database
#86any reason why creating a table per day / month i stead of using one table partitioned by day / month?
Re: Migrating a 40TB SQL Server Database
#87any reason for creating a table per day / month instead of a single table partitioned by day / month?
Re: Migrating a 40TB SQL Server Database
#88Earlier quoted context omitted.
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…
May I ask what kind of production environments you have in mind? Are these large-scale FAANG-style deployments or something else?
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
#89Earlier quoted context omitted.
Where did you work? CERN?
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...
Re: Migrating a 40TB SQL Server Database
#90I run a 30TB MySql server on EC2. We raid 0 multiple EBS volumes for space and iops. It is completely unmanageable - I inherited it. We’re moving data out, but slowly. This DB houses PII for tens of millions of users.
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.