4 ms·

I'd love to see a tool that lets you modify large files efficiently. I had to replace line 4 of a 200 GB SQL dump, it took a substantial amount of compute time

by nickjj 3y ago

I'd love to see a tool that lets you modify large files efficiently. I had to replace line 4 of a 200 GB SQL dump, it took a substantial amount of compute time to perform a find / replace with sed and it also required having over double the disk space since sed creates a temp file before it writes out the new file. Using a hex editor could have worked but it seemed too risky because data integrity was really important.

coolThingsFirst 3y ago
What’s the challenge in here? Loading the text efficiently in RAM?
- nickjj 3y ago
  It's mainly to save time. I don't remember exactly how long it took but I remember it being something like 30 minutes of purely waiting for the find / replace to finish on a 4 CPU core / 8 GB of memory machine. Memory wasn't an issue fortunately.
  - filleduchaos 3y ago
    I mean, if you knew you only wanted to find/replace on line 4, why not simply stop the search after the first match? The syntax escapes me at the moment, but I'm quite sure I've used sed (or maybe awk?) in the past to do exactly that
    - PetahNZ 3y ago
      Sed takes ages to remove a single line from huge files too.
    - nickjj 3y ago
      As far as I know sed will still read and write the full file, even when you do a modification on a specific line. I've target deleted 1 line with sed and it took quite some time too.
      - filleduchaos 3y ago
        There's a big difference between "quite some time" and 30 minutes, though. Of course sed needs to read the file and write it back to disk, but that's capped by I/O speed which is very high on modern drives - we're talking seconds for a file in the tens of gigabytes on the fastest SSDs, a very far cry from half an entire hour.
        nickjj 3y ago
        This was run on an AWS gp3 SSD EBS volume with 3k IOPS. It was a CPU optimized c6i.xlarge machine. The file in question was 200 GB. I used "quite some time" because this happened months ago and I don't recall the exact time on the delete. I don't think deleting a line was any faster than doing a find / replace on 1 line. If sed is reading and writing the file in both cases I'd expect both to have the same performance.
  - coolThingsFirst 3y ago
    Why would there be a market for something like this?
    - nickjj 3y ago
      Once in a while things come up where you need to make a surgical edit in a really large file and it could be in a scenario where time matters. For example if you're doing a SQL dump -> import, technically you could have downtime during this process to eliminate any chance of data loss. Having to wait ~30min for a command to finish is painful. I'm not saying to go off and make this product to sell but if such a tool existed and you positioned it at $39 or whatever, if it could eliminate half an hour of downtime for a business it pays for itself. Especially if the alternative is to muck around with hex editing the file. If 100,000 people have this problem and 3,000 of them would pay for it that's $117,000. If you spent 6 months making a super polished tool that made it easy to know exactly what's being edit (or deleting lines, etc.) that's pretty appealing. Even if the sales were half that's still solid but it could also be 5x too, who knows. Maybe you can finish such an app in 3 months instead of 6, etc.. It's also an app that mostly feels like it could reach a "done" state with little maintenance since you're editing text files which is a well known topic.
SushiHippie 3y ago
Wouldn't something like this, work for your specific use case: head -n4 sqldump > sqldump2 Then change the line 4 in sqldump2 to whatever you wanted to, and after that: tail -n+5 sqldump >> sqldump2 And now sqldump2 contains sqldump but with line 4 edited. This still requires double the disk space, but at least shouldn't take 30 minutes.
- nickjj 3y ago
  That would work but what would tail do different than sed here? Both would still need to read and write the full file (minus 4 lines for tail) which without testing indicates the time would be the same.
  - SushiHippie 3y ago
    I thought that sed may have a "high" overhead, and that's why it took so long. YMMV, but I just reproduced this on my machine, and the tail command took 3 minutes. Maybe you are limited by disk IO? What was the sed command you used? EDIT: sed was even a bit faster with: sed -i '4s/^.*$/your line four/' sqldump Took 20s less than my tail solution, and does not occupy duplicated disk space.
    - nickjj 3y ago
      I deleted the line with `sed -i "4d" sqldump`. The duplicated disk space comes from sed writing a temporary file out to the current directory, it does this in chunks as the command runs. It makes sense for sed to write a temp file even with `-i` because what happens if your power goes out mid-way through the command without the temp file? You'd have data loss. To combat that sed will write a temp file out and when the operation is completed the last step is to move the temp file to the original file to overwrite it. You might not notice this unless you monitor the directory for changes while sed runs. You can spam `ls -la` as sed runs on a decently sized file to see the temp file.
verticalscaler 3y ago
There is this thing called dd. Hold it like so: seek=$bytes-offset-until-line-4 conv=notrunc,sync Enjoy responsibly :)

by nickjj 3y ago

coolThingsFirst 3y ago

What’s the challenge in here? Loading the text efficiently in RAM?

nickjj 3y ago
It's mainly to save time. I don't remember exactly how long it took but I remember it being something like 30 minutes of purely waiting for the find / replace to finish on a 4 CPU core / 8 GB of memory machine. Memory wasn't an issue fortunately.
- filleduchaos 3y ago
  I mean, if you knew you only wanted to find/replace on line 4, why not simply stop the search after the first match? The syntax escapes me at the moment, but I'm quite sure I've used sed (or maybe awk?) in the past to do exactly that
  - PetahNZ 3y ago
    Sed takes ages to remove a single line from huge files too.
  - nickjj 3y ago
    As far as I know sed will still read and write the full file, even when you do a modification on a specific line. I've target deleted 1 line with sed and it took quite some time too.
    - filleduchaos 3y ago
      There's a big difference between "quite some time" and 30 minutes, though. Of course sed needs to read the file and write it back to disk, but that's capped by I/O speed which is very high on modern drives - we're talking seconds for a file in the tens of gigabytes on the fastest SSDs, a very far cry from half an entire hour.
      - nickjj 3y ago
        This was run on an AWS gp3 SSD EBS volume with 3k IOPS. It was a CPU optimized c6i.xlarge machine. The file in question was 200 GB. I used "quite some time" because this happened months ago and I don't recall the exact time on the delete. I don't think deleting a line was any faster than doing a find / replace on 1 line. If sed is reading and writing the file in both cases I'd expect both to have the same performance.
- coolThingsFirst 3y ago
  Why would there be a market for something like this?
  - nickjj 3y ago
    Once in a while things come up where you need to make a surgical edit in a really large file and it could be in a scenario where time matters. For example if you're doing a SQL dump -> import, technically you could have downtime during this process to eliminate any chance of data loss. Having to wait ~30min for a command to finish is painful. I'm not saying to go off and make this product to sell but if such a tool existed and you positioned it at $39 or whatever, if it could eliminate half an hour of downtime for a business it pays for itself. Especially if the alternative is to muck around with hex editing the file. If 100,000 people have this problem and 3,000 of them would pay for it that's $117,000. If you spent 6 months making a super polished tool that made it easy to know exactly what's being edit (or deleting lines, etc.) that's pretty appealing. Even if the sales were half that's still solid but it could also be 5x too, who knows. Maybe you can finish such an app in 3 months instead of 6, etc.. It's also an app that mostly feels like it could reach a "done" state with little maintenance since you're editing text files which is a well known topic.

SushiHippie 3y ago

Wouldn't something like this, work for your specific use case: head -n4 sqldump > sqldump2 Then change the line 4 in sqldump2 to whatever you wanted to, and after that: tail -n+5 sqldump >> sqldump2 And now sqldump2 contains sqldump but with line 4 edited. This still requires double the disk space, but at least shouldn't take 30 minutes.

nickjj 3y ago
That would work but what would tail do different than sed here? Both would still need to read and write the full file (minus 4 lines for tail) which without testing indicates the time would be the same.
- SushiHippie 3y ago
  I thought that sed may have a "high" overhead, and that's why it took so long. YMMV, but I just reproduced this on my machine, and the tail command took 3 minutes. Maybe you are limited by disk IO? What was the sed command you used? EDIT: sed was even a bit faster with: sed -i '4s/^.*$/your line four/' sqldump Took 20s less than my tail solution, and does not occupy duplicated disk space.
  - nickjj 3y ago
    I deleted the line with `sed -i "4d" sqldump`. The duplicated disk space comes from sed writing a temporary file out to the current directory, it does this in chunks as the command runs. It makes sense for sed to write a temp file even with `-i` because what happens if your power goes out mid-way through the command without the temp file? You'd have data loss. To combat that sed will write a temp file out and when the operation is completed the last step is to move the temp file to the original file to overwrite it. You might not notice this unless you monitor the directory for changes while sed runs. You can spam `ls -la` as sed runs on a decently sized file to see the temp file.

verticalscaler 3y ago

There is this thing called dd. Hold it like so: seek=$bytes-offset-until-line-4 conv=notrunc,sync Enjoy responsibly :)