Smart_open: Slow S3 write for csv.gz file

[kasgu]

Problem description

Be sure your description clearly answers the following questions:

• What are you trying to achieve?
  We try to move data (in memory) from database to S3 (in csv.gz format) using smart_open.
• What is the expected result?
  We are reading 10M at a time & write it back to s3. This write should not take more than 5 mins.
• What are you seeing instead?
  This write is taking more 2hr

Steps/code to reproduce the problem

params = {'session': session}
with smart_open.open(f's3://{bucket}/{key}', 'w', encoding='utf-8', transport_params=params) as fout:
    writer = csv.writer(fout,
                        delimiter = '\x01',
                        quoting = csv.QUOTE_ALL,
                        escapechar = '\\',
                        quotechar = '`')
    print(f"{datetime.datetime.now()}: Initial Fetch Started.")
    rows = cur.fetchmany(10000000)
    row_count = 0
    print(f"{datetime.datetime.now()}: Downloaded initial Data from the databases.")

    while rows:
        print(f"{datetime.datetime.now()}: data write start for csv for {len(rows)}.")
        writer.writerows(rows)
        row_count += len(rows)
        print(f"{datetime.datetime.now()}: {row_count} rows written in s3 csv gzip.")
        rows = cur.fetchmany(10000000)
        print(f"{datetime.datetime.now()}: Downloaded data from the databases.")

fout.close()

Versions

Linux-4.9.217-0.1.ac.205.84.332.metal1.x86_64-x86_64-with-redhat-5.3-Tikanga
Python 3.6.10 (default, Jul 12 2020, 20:42:51)
[GCC 4.9.4]
smart_open 2.0.0

[piskvorky]

What happens if you drop the .gz (write only .csv), is writing still slow? Or if you store the output to a local file (not to S3).

I'm trying to localize what the issue may be: network connectivity, compression, DB…

Is the output file written correctly? When you download & unzip the generated .csv.gz from S3, does it open correctly.

[kasgu]

Q: What happens if you drop the .gz (write only .csv), is writing still slow? Or if you store the output to a local file (not to S3).
A: I need it to be .gz & can't store data in local data.

Q: Is the output file written correctly?
A: Yes, I was able to read the file correctly.

Q: what the issue may be: network connectivity, compression, DB…
A: I have network connectivity of 2GB/s. Data fetch from DB to RAM for 10 M takes around 9 mins.

Are there any settings which can improve the performance for the large data volume write to S3?

[piskvorky]

A: I need it to be .gz & can't store data in local data.

Why not?

What speed do you get when you download the .csv.gz and upload it to S3 using the native aws s3 cp CLI command? On the same machine where you're reporting this.

[kasgu]

Upload speed - 11.1 MB/s
download speed - 55.6 MB/s

[piskvorky]

And how large is your batch of 10M rows, in bytes?

At 11.1 MB/s, you could theoretically upload 78 GB in 2 hours (theoretical maximum). Maybe the compression could make the upload CPU bound, instead of network bound? Just guessing.

[mpenkov]

There are several factors at play here.

First, gzip compression is CPU-bound. If you have multiple cores, then you could get more done by processing parts in parallel.

Second, smart_open _streams_ the result to S3. This is convenient (obviously, otherwise you wouldn't be using smart_open :)) but this convenience comes at a price: you cannot parallel-process a single stream the same way you can parallel-process a file. For example, tools like the AWS CLI download/upload files so quickly by leveraging parallel processing. They break up the file into chunks and upload each chunk in parallel. This isn't something that smart_open can do, because it uses a completely different abstraction (streams, not files).

[mpenkov]

Q: What happens if you drop the .gz (write only .csv), is writing still slow? Or if you store the output to a local file (not to S3).
A: I need it to be .gz & can't store data in local data.

I don't think @piskvorky was proposing a workaround. Instead, I think the Q is a suggestion for an experiment that you could perform to determine where the bottleneck is. The results of that experiment would the help us come up with ideas. Help us help you ;)

[piskvorky]

The upload still happens in chunks IIRC (multipart upload), right? So the file abstraction is not that far off – it's not like smart_open sends each line over the network, separately. Just the chunks themselves are serial.

I do wonder where the time goes for the OP. A printout with timings of the individual pieces would be helpful. Even the non-parallelized mode shouldn't take 2 hours to upload on a 11MB/s connection, unless the data is really large, or the compression really slow.

Dropping .gz would verify the latter; storing locally the former.

[kasgu]

10M is around 2.4 GB.

What setting I can modify at smart_open to get some benfit in performance.

[piskvorky]

smart_open comes with all benefits already enabled, so there's nothing you can do here (besides trying the options above to provide more clues).