5

I'm pulling VIN specifications from the National Highway Traffic Safety Administration API for approximately 25,000,000 VIN numbers. This is a great deal of data, and as I'm not transforming the data in any way, curl seemed like a more efficient and lightweight way of accomplishing the task than Python (seeing as Python's GIL makes parallel processing a bit of a pain).

In the below code, vins.csv is a file containing a large sample of the 25M VINs, broken into chunks of 100 VINs. These are being passed to GNU Parallel which is using 4 cores. Everything is dumped into nhtsa_vin_data.csv at the end.

$ cat vins.csv | parallel -j10% curl -s --data "format=csv" \
   --data "data={1}" https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVINValuesBatch/ \
      >> /nas/BIGDATA/kemri/nhtsa_vin_data.csv

This process was writing about 3,000 VINs a minute at the beginning and has been getting progressively slower with time (currently around 1,200/minute).

My questions

  • Is there anything in my command that would be subject to increasing overhead as nhtsa_vin_data.csv grows in size?
  • Is this related to how Linux handles >> operations?

UPDATE #1 - SOLUTIONS

First solution per @slm - use parallel's tmp file options to write each curl output to its own .par file, combine at the end:

$ cat vins.csv | parallel \
--tmpdir /home/kemri/vin_scraper/temp_files \
--files \
-j10% curl -s \
--data "format=csv" \
--data "data={1}" https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVINValuesBatch/ > /dev/null

cat <(head -1 $(ls *.par|head -1)) <(tail -q -n +2 *.par) > all_data.csv

Second solution per @oletange - use --line-buffer to buffer output to memory instead of disk:

$ cat test_new_mthd_vins.csv | parallel \
    --line-buffer \
    -j10% curl -s \
    --data "format=csv" \
    --data "data={1}" https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVINValuesBatch/ \
    >> /home/kemri/vin_scraper/temp_files/nhtsa_vin_data.csv

Performance considerations

I find both the solutions suggested here very useful and interesting and will definitely be using both versions more in the future (both for comparing performance and additional API work). Hopefully I'll be able to run some tests to see which one performs better for my use case.

Additionally, running some sort of throughput test like @oletange and @slm suggested would be wise, seeing as the likelihood of the NHTSA being the bottleneck here is non-negligible.

10
  • My first thought was that by appending to the .csv over time in that manner, that you're having to seek to the end of the file over and over. Might be better to let the curl commands write to a lot of individual files and then collect them at the end and put them together into a single file. The single file also creates a source of contention for all the curl commands when they're trying to write data out. – slm Jul 15 '18 at 2:16
  • 2
    How have you made sure that you are not being rate limited by dot.gov? – Ole Tange Jul 15 '18 at 4:14
  • 1
    @OleTange I didn't see anything about NHTSA limiting anything when I was looking through their API FAQ prior to utilizing their service: vpic.nhtsa.dot.gov/api/Home/Index/FAQ. Unless they really buried it, feeling like that's not happening – kemri Jul 15 '18 at 4:20
  • 2
    I did a small test against dot.gov. I get way less than 10 MB/s. So my bet is that the bottleneck is at dot.gov. – Ole Tange Jul 15 '18 at 4:24
  • 1
    How fast can you download 1 GB through your internet connection? How fast can you store 1 GB on /tmp? How fast can you store 1 GB on /nas/...? Unless you have a 10 Gbps internet connection, my guess is that your internet connection will be a bottleneck before the disk I/O or CPU. But dot.gov may be a bottleneck before that. – Ole Tange Jul 15 '18 at 4:32
3

My suspicion is that the >> is causing you contention on the file nhtsa_vin_data.csv among the curl commands that parallel is forking off to collect the API data.

I would adjust your application like this:

$ cat p.bash
#!/bin/bash

cat vins.csv | parallel --will-cite -j10% --progress --tmpdir . --files \
   curl -s --data "format=csv" \
     --data "data={1}" https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVINValuesBatch/

This will give your curl commands their own isolated file to write their data.

Example

I took these 3 VINs, 1HGCR3F95FA017875;1HGCR3F83HA034135;3FA6P0T93GR335818;, that you provided me and put them into a file called vins.csv. I then replicated them a bunch of times so that this file ended up having these characteristics:

VINs per line
$ tail -1 vins.csv | grep -o ';' | wc -l
26
Number of lines
$ wc -l vins.csv
15 vins.csv

I then ran my script using this data:

$ ./p.bash

Computers / CPU cores / Max jobs to run
1:local / 1 / 1

Computer:jobs running/jobs completed/%of started jobs/Average seconds to complete
local:1/0/100%/0.0s ./pard9QD3.par
local:1/1/100%/10.0s ./paruwK9L.par
local:1/2/100%/8.5s ./parT6rCS.par
local:1/3/100%/7.3s ./pardzT2g.par
local:1/4/100%/6.8s ./parDAsaO.par
local:1/5/100%/6.8s ./par9X2Na.par
local:1/6/100%/6.7s ./par6aRla.par
local:1/7/100%/6.7s ./parNR_r4.par
local:1/8/100%/6.4s ./parVoa9k.par
local:1/9/100%/6.1s ./parXJQTc.par
local:1/10/100%/6.0s ./parDZZrp.par
local:1/11/100%/6.0s ./part0tlA.par
local:1/12/100%/5.9s ./parydQlI.par
local:1/13/100%/5.8s ./par4hkSL.par
local:1/14/100%/5.8s ./parbGwA2.par
local:0/15/100%/5.4s

Putting things together

When the above is done running you can then cat all the files together to get a single .csv file ala:

$ cat *.par > all_data.csv

Use care when doing this since every file has it's own header row for the CSV data that's contained within. To deal with taking the headers out of the result files:

$ cat <(head -1 $(ls *.par|head -1)) <(tail -q -n +2 *.par) > all_data.csv

Your slowing down performance

In my testing it does look like the DOT website is throttling queries as they continue to access their API. The above timing I saw in my experiments, though small, were decreasing as each query was sent to the API's website.

My performance on my laptop was as follows:

$ seq 5 | parallel --will-cite --line-buffer 'yes {} | head -c 1G' | pv >> /dev/null
   5GiB 0:00:51 [99.4MiB/s] [                                                                                                                                                                  <=>       ]

NOTE: The above was borrowed from Ole Tange's answer and modified. It writes 5GB of data through parallel and pipes it to pv >> /dev/null. pv is used so we can monitor the throughput through the pipe and arrive at a MB/s type of measurement.

My laptop was able to muster ~100MB/s of throughput.

FAQ for NHTSA API

API

For the ‘Decode VIN (flat format) in a Batch’ is there a sample on making this query by URL, similar to the other actions?

For this particular API you just have to put a set of VINs within the box that are separated by a “;”. You can also indicate the model year prior to the “;” separated by a “,”. There is an upper limit on the number of VINs you can put through this service.

Example in the box is the sample: 5UXWX7C5*BA,2011; 5YJSA3DS*EF

Source: https://vpic.nhtsa.dot.gov/MfrPortal/home/faq searched for "rate"

The above mentions that there's a upper limit when using the API:

There is an upper limit on the number of VINs you can put through this service.

References

1
  • 1
    For the API VIN "upper limit", I did a few tests on my end a couple days ago and found that the performance seemed pretty optimal when passing in about 100 VINs at a time, whereas 1000 was significantly slower, and 1 at a time seemed to be slowed down by the sheer number of calls to the API (this also butted up against the 1000/2000 transactions per minute limitation also specified in the FAQ) – kemri Jul 15 '18 at 15:04
3

Performance is usually limited by one of these:

  1. Network bandwidth. You can use sudo iftop to see if your network connection is 100% utilized.
  2. Network latency. If the server in the other end takes a long time to reply, then you will not see 100% utilization of bandwidth.
  3. Disk I/O. You can use iostat -dkx 1 to see if the I/O of any of your disks is 100% utilized.
  4. CPU. You can use top if your CPUs are 100% utilized. Press 1 to see the individual CPU threads. If one of these is at 100% then you have a single threaded program, that is limited by this.

GNU Parallel is good at running jobs in parallel to utilize more bandwidth, disk I/O and CPU.

But it also has its limitations.

GNU Parallel normally caches output in /tmp. This means that your disk I/O on /tmp may be the bottleneck.

Luckily dealing with CSV you rarely care about the order of lines: It is fine if lines mix, as long as it is full lines.

If you use --line-buffer from version >20170822 then GNU Parallel does not buffer output on disk - it only buffers one full line in memory. It takes a bit more CPU power to do, so check if parallel uses 100% CPU. If it uses less, then you have not hit that bottleneck.

$ cat vins.csv | parallel --line-buffer curl -s --data "format=csv" \
 --data "data={1}" https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVINValuesBatch/ \
  >> /nas/BIGDATA/kemri/nhtsa_vin_data.csv

You can see if you have a local bottleneck by:

$ seq 1000 | parallel --line-buffer 'yes {} | head -c 1G' | pv >> /nas/BIGDATA/test

On my lousy laptop I get around 100 MBytes/s. So my lousy laptop would be able to deal with 1 Gbit/s from dot.gov.

1
  • Thanks for the reply and the tips, here are some answers: 1. Don't have sudo access due usage of company analytics servers; 2. Sort of answered in the comments above; 3&4. Said analytics servers are brand new and very powerful, no I/O or CPU bottlenecks to speak of – kemri Jul 15 '18 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.