17 ms·
Migrating a 40TB SQL Server Database
- rrdharan 6y agoIt seems concerning that they would be hanging onto traffic logs for that long. Seems like a massive PII / data breach liability.
- tutfbhuf 6y agoAgreed, it's questionable why a company needs to save raw traffic logs for years.
- valuearb 6y agoYea, it seems like this project should have started with a cost/benefit analysis. Like what is this data used for, who is using it, how often and how to quantify the benefits the users derive from it. Not sure which the data couldn’t just be compressed into daily summaries in the DB, while the raw logs are stored (Compressed) in the file system in case you ever had to go back.
- dt3ft 6y agoCame here for this comment. 4 years of traffic logs... is just plain brutal.
- bryan_w 6y agoYou 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.
- m3047 6y ago+1 for them. Pretty cynical, I know. But imagine the request for legal discovery...
- deleted 6y ago[deleted]
- dsr_ 6y agoRight 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?
- Shog9 6y ago> 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 impossible to reproduce. Affected a low double-digit number of posts out of millions edited over the course of several years. Was able to finally nail down the scenario by pulling logs for all editors involved in each of the events over the years and identifying the common factor. (You can argue this is a crazy way of storing revisions, but that's beside the point; at this scale even very unlikely bugs do show up with some frequency and it is nice to be able to investigate them)
- buerkle 6y ago"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?"
- jpalomaki 6y agoStack Overflow HAproxy logs likely don’t have too much sensitive information. These are most likely logs about visited pages with timestamps - maybe with client IP addresses. Storing them raw can make sense. You might later come up with new ways to analyze the data which your aggregation logic of choice does not support.
- rrdharan 6y ago> maybe with client IP addresses https://www.whitecase.com/publications/alert/court-confirms-ip-addresses-are-personal-data-some-cases https://www.whitecase.com/publications/alert/court-confirms-...
- jeffbee 6y agoStoring web logs in a SQL database is a pretty bad plan.
- posix_me_less 6y agoYeah, this poke my eye as well.
- tyingq 6y agoCombined with "The database was initially designed to have a single table for each day." Oy.
- cm2187 6y agoYeah, if they do that they might as well shard databases and not have this problem in the first place (not that there aren’t better tools)
- isatty 6y agoAt this scale and amount of data, definitely. I have however dumped short term data into an unlogged temp Postgres table to query data using Metabase/psql. Worked surprisingly well enough for me to debug something. I'm sure another index on ES would've worked well too, but I'm more familiar with SQL than ES syntax and it's cheaper too.
- posix_me_less 6y ago> "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 data? Why not just dump it into linear files?
- tutfbhuf 6y ago> Why not just dump it into linear files? I guess because they want to query them, but in this case I would prefer the ELK stack (Elasticsearch + Logstash + Kibana).
- oneplane 6y agoBut even then, normal files work great with (all non-Microsoft) software that is actually intended to work with this type of data at this scale. You could do it on one machine with Pandas DataFrames or on multiple machines with Spark DataFrames. You could keep warm logs in standard log files and put everything else in Parquet. You could use a parallel database. Heck, you could use any of the cloud offerings to do it. And you could use ElasticSearch. ElasticSearch would be fine for this.
- Semaphor 6y ago> But even then, normal files work great with (all non-Microsoft) software Even Microsoft has a tool [0] that allows you to query IIS logfiles with SQL [0]: https://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx https://www.microsoft.com/technet/scriptcenter/tools/logpars...
- Shog9 6y agoCurious what use-cases you're envisioning here. Having worked with the system described in the past, it's quite flexible: data can be easily pulled out into more specialized systems for aggregation or trend analysis, while the raw logs remain quickly accessible over long enough periods of time to allow for digging into everything from support cases involving a single person to monitoring distributed attacks. Access can be controlled and monitored with reasonable granularity, and training new folks to use it is as easy as teaching some basic Select queries. Not gonna suggest it's the most efficient system (if nothing else, SQLServer wastes a TON of disk space), but it minimizes complexity while deftly avoiding the choice between throwing away information when it's still needed and keeping everything in flat files which are too unwieldy to be used.
- oneplane 6y agoThis reads like the most Microsoft or most legacy-on-prem problem ever. Why on earth would you do this, and even then not take the opportunity to do it right while you are migrating anyway? Are you mad? Well, that was my first reaction anyway. It seems to me that this is more of a 'when all you have is a hammer, everything looks like a nail' to me; if you are a vendor shop (i.e. Microsoft-only) and you are a DBA and specifically just for MSSQL, then this is probably the best way you could come up with, based on the skills, experience and tools at hand. It does make me wonder if at any point someone thought to ask for help instead of keeping this going.
- BrentOzar 6y ago> 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.
- pc86 6y agoWhich speaks to much more dire organizational issues - that nobody can throw up a red flag and say "hold the eff up, we've reached a scale where this is broken and we need to address it."
- hn_acc_2 6y agoI would say this is actually the norm... In most orgs the leadership is not highly technical and this is always a hard sell. The best you can hope for after a "hold the eff up" rewrite is for everything to keep working the same "but it's more hardened/scalable/modular/blah blah", and the worst you can hope for is to screw up some critical business process while the kinks are worked out. Also, there is rarely any incentive for anyone from Joe Developer all the way up to C level to even call for this in the first place.
- oneplane 6y agoUnless 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.
- deleted 6y ago[deleted]
- hansitomani 6y agoWow. 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.
- greggyb 6y agoThey 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.
- yknx4 6y agoI'm baffled with the fact that they were toying with the production database in the production machine. That sounds incredibly dangerous
- BrentOzar 6y ago> 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.
- adfinoinio 6y agoWhich 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.
- Shog9 6y agoUnfortunately, 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 https://news.ycombinator.com/item?id=23976881
- PaywallBuster 6y agoActually if you read the article this is not actually critical infrastructure. All the data is duplicated in both DCs and the source of data still has a copy of the data. I imagine is not a problem if it goes down for some moments. Developers may be unable to review traffic logs, that's it.
- jto1218 6y ago> 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 change? Was it 11-months-worth-of-dev-time faster?
- BrentOzar 6y ago> 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. ;-)
- deleted 6y ago[deleted]
- iliasku 6y agoI found this article useful, mostly for people who don't have similar experience dealing with large databases. It gives so many reasons on why following this path is such a bad idea. Please people, stop writing custom scripts and then run them untested on the production db. If the company doesn't pay to give you enough disk space to take a proper backup to test just quit or buy it yourself. It will save you 11 months and many hours of sleep.
- polygotdomain 6y agoI 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.
- amichal 6y agoTotally 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.
- user5994461 6y agoI'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-graylog-lessons-learned/ 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.
- thisisbrians 6y agoI'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.
- user5994461 6y agoThe trick is to find a common baseline format, like CSV or JSON, then you can leverage the bazillion of existing tools. The second trick is to split the data in manageable chunks (a day of logs for example), then you can transfer one unit at a time to the new system. Dump a chunk on one side, load on the other side, verify it's good. It will still take a while but you have a good indication of progress and don't need to start all over if one chunk fails.
- dswalter 6y agoAccording 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/ https://nimbusdata.com/products/exadrive/pricing/
- thisisbrians 6y agoYes. Or even stripe some cheaper $/TB drives together in a RAID to cover the needs.
- BrentOzar 6y ago> That would have been worth the expense in DBA time alone, right? You're assuming the DBA did nothing else during the project's timeline, and that's not likely the case. A lot of work in projects like this is just letting the batch job run while you keep an eye on it.
- malisper 6y agoI'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 the migration into the new database which isn't too bad. When I've done this with 20TB DBs, it usually took several days to complete. That's much better than the 11 months mentioned in the blog post.
- atwebb 6y agoYeah I only scanned through but there seem to be better tools for the job in the environment. SSIS is scriptable/has APIs so you can shred the data into n packages and run it. Also, columnstore_archive gives amazing compression if you're low on the read side and immutable data means you can load into partitions. Column diffs are a bit annoying but not unmanageable. Then again, arm chair architecting isn't ever as accurate as you assume. I do know you can move billions of rows daily using SSIS across thousands of tables.
- justinclift 6y agoSimilar 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.
- d_k_f 6y agoI'm late to the party, but he mentioned this in the article. The new server didn't have enough space to hold both the backup and the newly restored database, so that approach didn't work. Quote: "We’re a very lean shop when it comes to hardware, and this is by far our largest chunk of data we have to store, so there wasn’t a place anywhere on the network to store a 33-38TB full backup temporarily, while it was being restored to a server." As others have mentioned in the thread, a lot of the problems seem to have been solvable by just buying/attaching more storage, even if only temporarily.
- bluedino 6y agoHah. My first thought was, 'what kind of idiot lets a file get to be 44TB... Then I realized who it was. Nice to see that even StackOverflow had an issue like this.
- user5994461 6y agoThis is all the reasons why logs must have a limited retention period (usually 1 to 3 months). Then nobody has to waste 11 months of their life trying to retain and migrate TB of old logs that nobody needs, with no storage to do so and no backup.
- WorldMaker 6y agoBulk copy is about the fastest you could do in C# or PowerShell, but you would still be moving a ton of data back and forth over the wire between the DB server and the "jump" server running the script. I would have tried a hybrid approach with the dynamic SQL one. Dynamic SQL sprocs like that don't seem to generate good query plans (at least not with a lot of prodding) in my experience, but if the dynamically generated SQL was done in the C# or PowerShell side rather than a sproc I feel the query analyzer would be less confused about individual queries coming out of the scripts. The only other advice experience from a past life I have to offer is that I often saw better performance from T-SQL MERGE than T-SQL INSERT FROM. Dynamically generating MERGE statements from C# is not fun, but in that past experience it would give the best results for single-server data migrations like this. (Multi-server there's not much you can do beyond Bulk Copy.)
- dkdk8283 6y agoI 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.
- miahi 6y agoI worked on a system that generated around 500GB-1TB of log data monthly. An estimation done way before it was put in production said that it would generate less than 100GB/month, so the DB storage was sized accordingly (~6TB, half of which were used for other data). A change that came later in the project (on a different system) was disregarded, but it increased the application traffic by a factor of 5, and the space estimation was not updated. So it was an interesting surprise when the space started filling up way faster after the go live. To make the issue even more interesting, the system was mission-critical at five nines and the log data was sensitive + had a legal requirement to be kept online for at least one year, for queries. So deletion was not an option and the migration had to be done online. Fortunately the system used Oracle DB and the log table was partitioned by date. Unfortunately, a partition kept three months of data, so it could reach the full amount of free space on the DB. There was no easy way to add storage to that server, but we did have some space on another one, with lower speed (the prod DB was RAID10, the other one was RAID1) In the end we had to create a new partition scheme, with monthly partitions stored in different tablespaces, create another table with the same structure and switch it with the online one (a couple of seconds of downtime), backup the database and restore it on the slow storage, then delete the old table structure and tablespaces/datafiles to free the space on production. A procedure was created to detach the oldest partition from the production database and reattach it to the slower database (where we also implemented compression at a later stage). This meant that the query system needed changes to work with both databases, but at least the system was up. The good thing was that in Oracle you can detach partitions and tablespaces and transport them to a different database without downtime. I don't know MS SQL enough, but I understand from the migration procedure that this is not possible there. I guess they did that manual daily partitioning by table, but they did not store each table in its own datafile, so it can be moved as a whole (maybe with downtime, but at least it would free the space faster).
- metadata 6y agoI think 11 months is absolutely crazy, and the context for that is that I earn for living by selling my database migration software [1]. They could have used my software to copy up to 16 tables in parallel for a mere $3k. They could have copied a few hundred tables at a time, compare source and target to make sure everything is fine, then drop and compact. As more disk space frees up, they could have moved more and more data in one go. If the data needed to be transformed much, they could have wrote views to pull the data in a proper format and creating tables on the target with that data as they go. True, spinning drives would kill performance, but my tool is easily reaching 50MB/s on SSDs and spinning drives wouldn't add months. I mean, when you have off the shelf products doing what you need, spending so much time fighting with custom scripts is and odd choice. This is really not meant to advertise, I'm just baffled by how much effort was needed for this project. [1] https://www.spectralcore.com/fullconvert https://www.spectralcore.com/fullconvert
- champtar 6y agoIf they want to migrate to something else, they need to have a look at ClickHouse. When switching from Elasticsearch to ClickHouse 1.5 years ago, I reduced my storage needs by 20, gained SQL, performance, and a ton of analytics features. In hindsight I would say that Elasticsearch is for full text queries, and if you are using it for something else (access logs) there is a good chance this is the wrong tool for the job.
- myrandomcomment 6y agoSo 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.
- bjg2 6y agoany reason why creating a table per day / month i stead of using one table partitioned by day / month?
- bjg2 6y agoany reason for creating a table per day / month instead of a single table partitioned by day / month?
- idlewords 6y agoI'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.
- nwmcsween 6y agoSomeone 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.
- tuananh 6y agoI'm surprised why the author didn't think of availability group earlier? maybe i miss sth from the post?
- jiggawatts 6y agoI can't imagine why he'd use LINQPad when it's possible to get two SQL Servers to talk directly to each other. Just configure a linked server and write an INSERT statement pulling the data from the other server.
- muzaffarpur 6y agoI wonder how effective it would move data using spark? Have anyone considered that!