[Python] read_csv from python is slow on some work loads

[asfimport]

Hi!

I've noticed that pyarrow.csv.read_csv can be slow on real workloads, processing data around 0.5GiB/s. "Real workloads" means many string, float, and all-null columns, and large file size (5-10GiB), though the file size didn't matter to much.

Moreover, profiling a little a bit with py-spy, it seems that maybe 30-50% of the time is spent on shared pointer lock mechanisms (though I'm not sure if this is to be trusted). I've attached the dumps in svg format.

I've also attached a script and a Dockerfile to run a benchmark, which reproduces the speeds I see. Building the docker image and running it on a large Azure machine, I get speeds around 0.3-1.0 GiB/s, and it's mostly around 0.5GiB/s.

This is all also available here: https://github.com/drorspei/arrow-csv-benchmark

Environment: Machine: Azure, 48 vcpus, 384GiB ram
OS: Ubuntu 18.04
Dockerfile and script: attached, or here: https://github.com/drorspei/arrow-csv-benchmark
Reporter: Dror Speiser

Related issues:

• [C++] Consider using fast-double-parser (is related to)
• [C++] Improve UTF8 validation speed and CSV string conversion (is related to)

Original Issue Attachments:

• arrow-csv-benchmark-plot.png
• arrow-csv-benchmark-times.csv
• benchmark-csv.py
• Dockerfile
• profile1.svg
• profile2.svg
• profile3.svg
• profile4.svg

_{Note: This issue was originally created as ARROW-10308. Please see the migration documentation for further details.}

[asfimport]

Antoine Pitrou / @pitrou:
Two things:

1. you are using a Python file object (a BytesIO object). This will unnecessarily reduce performance. Instead you should use an Arrow native file object (for example pyarrow.BufferReader).
2. depending on the CSV file size and structure, it can be beneficial to change the CSV read block size in pyarrow.csv.ReadOptions: https://arrow.apache.org/docs/python/generated/pyarrow.csv.ReadOptions.html

[asfimport]

Dror Speiser:
Thanks for the quick response!

1. Sorry, I should have made this more explicit: while the benchmark uses BytesIO, I was experiencing these speeds when calling pd.read_csv("/path/to/my.csv"). Does pyarrow use BufferReader in this case?

2. Thanks for the tip, I'll try this out and report back if the numbers change.

[asfimport]

Antoine Pitrou / @pitrou:

1. No, it uses native file objects in that case.

2. Thank you, don't hesitate to report the numbers!

[asfimport]

Dror Speiser:
The bad news: the default block_size of 1MB, and the default use of native file objects, are not so good for my workloads. Moreover, I don't know what's going on with the speeds O_O

The good news: I now know how to consistently get around 1.8GiB/s speed for my workload.

Attached is a csv with all the numbers: 220 runs = (5 rounds) x (2 buffer types) x (11 block sizes) x (2 times everything) arrow-csv-benchmark-times.csv

And also a scatter plot. 

Note that the x-axis is log in base 2 of the block size.

Do you think there's a place for changing the defaults of block_size and buffer objects for local paths?

[asfimport]

Antoine Pitrou / @pitrou:
The adequate block size is heavily dependent on various characteristics, so it's not really possible to provide a one-size-fits-all default value.

As for "buffer objects for local paths", I guess I don't really understand the question.

Also: when you say "1.8GiB/s speed", this is in single-thread or multi-thread mode? If the latter how many CPU cores are active?

[asfimport]

Dror Speiser:
Also, given the suggested results in the profiling I did, there still is the possibility of winning 30-50% performance for the defaults, if it's really about lock synchronisation.

[asfimport]

Dror Speiser:
I'm running in multi-thread, with 48 vcpus. htop shows them all lighting up when running the benchmark.

For buffer objects: for most cases it would be faster to read entire files and then use BufferReader, though there's a higher chance of maxing out on available ram.

[asfimport]

Antoine Pitrou / @pitrou:
If you really have 400 columns in your file, you may want to try a much larger block size, e.g. 32 MB.

[asfimport]

Dror Speiser:
Yup, the graph confirms that block size in the range 32-100 MB is a good choice for my files.

But it still only gets to 1.8 GiB/s, which is slower than my SSD (2+ GiB/s). Is this reasonable? Are you not expecting the processing to be at least as fast as reading the files?

[asfimport]

Antoine Pitrou / @pitrou:
Processing a CSV file can be costly. On a 12-core 24-thread machine with a 64 MiB block size, I get around 1.5 GiB/s.

Profiling at the C++ level, it seems that the main bottlenecks are:

• CSV parsing itself (finding boundaries, escape characters etc.): 22% of total CPU time

• Building up double arrays (most of which is converting from string to double): 53% of total CPU time

• Building up string arrays: 19% of total CPU time

  If you're generating the data yourself (as opposed to getting it from a third party), I would really recommend using Parquet rather than CSV.

[asfimport]

Antoine Pitrou / @pitrou:
Also, if you're interested in only some of the columns, you can also reduce the processing time using ConvertOptions.include_columns: https://arrow.apache.org/docs/python/generated/pyarrow.csv.ConvertOptions.html

But really, consider using Parquet if you can. It's a highly optimized binary format.

[asfimport]

Dror Speiser:
Thanks for the suggestions :) I am indeed getting the files from a third party, and I'm converting them to parquet on arrival using arrow. I'm actually content with 0.5 GiB/s. I'm here because I saw a tweet by Wes Mckinney saying that the csv parser in arrow is "extremely fast". I tweeted back my results and he suggested that I open an issue.

I would like to note that the numbers don't quite add up. If the cpu usage is totally accounted for by the operations of parsing and building arrays, then that would mean that a single processor is doing between 0.06 to 0.13 GiB/s, which is very slow.

When I run the benchmark without threads I get 0.3 GiB/s, which is reasonable for a single processor. But, it also means that the 48 vcpus I have are very far from achieving a linear speedup, which is in line with my profiling (though the attached images are block size of 1 mb). Do you see a linear speedup on your machine?

As for processing csv's being costly in general, I'm not familiar enough with other libraries to say, but I am familiar with the simdjson library that claims to parse json files at over 2 GiB/s, on a single core. I'm looking at the code of both projects, hoping I'll be able to contribute something from simdjson to the csv parser in arrow.

[asfimport]

Wes McKinney / @wesm:
I do think we should be doing better here than we are so it merits some analysis to see if some default options should change. The results do strike me as peculiar

[asfimport]

Antoine Pitrou / @pitrou:
"vcpu" doesn't mean anything precise unfortunately. What is the CPU model and how many physical cores are allocated to the virtual machine?

I am familiar with the simdjson library that claims to parse json files at over 2 GiB/s, on a single core

It all depends what "parsing" entails, what data it is tested on, and what is done with the data once parsed.

On our internal micro-benchmarks, the Arrow CSV parser runs at around 600 MB/s (on a single core), but that's data-dependent. I tend to test on data with narrow column values since that's what "big data" often looks like, and that's the most difficult case for a CSV parser. It's possible that better speeds can be achieved on larger column values (such as large binary strings).

But parsing isn't sufficient, then you have to convert the data to Arrow format, which also means you switch from a row-oriented format to a column-oriented format. That part probably hits quite hard on the memory and cache subsystem.

[asfimport]

Antoine Pitrou / @pitrou:
For the record, on a 12-core 24-thread CPU, I get between 8x and 10x scaling from single-core to multi-core. This is far from linear scaling, but not horrific either.