0

I'm loading the complete TIGER 2013 dataset into a Postgis database and finding that the process is taking an extraordinarily long time. I was surprised by how long it took to load the TIGER 2012 dataset locally (over 60 hours), but the TIGER 2013 load is so long as to be almost unusable.

Loading a single state's worth of data (whether it be Alaska, Alabama, or Arkansas) is taking 12+ hours from my local machine on a high speed network into an Amazon RDS Postgres 9.3 instance with Postgis 2.1. Note that this doesn't include downloading the files from ftp2.census.gov, since I pre-downloaded all the files. This is all time taking to process the SHP files and insert them into Postgres.

I don't seem to be throttled by local resources - my CPU is nearly idle, and my load average is hovering around 2. My network has plenty of spare bandwidth.

I can't imagine I'm throttled by network on the RDS instance, although it could be limited by CPU or memory (it's an m1.large RDS). There is literally nothing else running against the database though, so the load should be minimal.

This situation is further complicated by the fact that the default TIGER scripts for Postgis don't lend themselves to parallelizability - they drop and create the tiger_staging schema, which makes it impossible to load states in parallel. I'm planning to modify the scripts to allow parallel load, but before I do that I'd like to know if there's a more fundamental problem.

Has anyone done a full soup-to-nuts load of TIGER 2013 data? If so, how long did it take? Are there any configuration parameters I should be looking at that might be throttling this process?

Any advice or guidance would be appreciated.

  • What is the latency to the server? – BradHards Dec 9 '13 at 0:00
  • Not exactly sure - ping isn't enabled on the RDS host. It's definitely significant, but it shouldn't be more than a few 100msec. After further investigation it appears that, whatever the latency is, it's almost certainly the core problem. Installing locally is literally at least two orders of magnitude faster. – Peter Goldstein Dec 9 '13 at 7:09
  • 1
    Be careful what you wish for. Parallel loading of spatial data into a single table causes spatial fragmentation, which cripples query performance. Defragmenting a very large table is likely to take longer than loading it single-threaded (and use twice the space). – Vince Dec 9 '13 at 12:53
  • @Vince That's not how TIGER data is structured. Each state (and US territory) has its own set of tables. So if it were parallelized by state then there would be no access to common tables by different threads. – Peter Goldstein Dec 9 '13 at 16:17
  • And yet the proper organization is to concatenate the tables (so you don't need sixty layers in a mapping app to draw anywhere in the US). You'd also experience plain-old disk fragmentation loading multiple tables into a single disk volume. – Vince Dec 9 '13 at 16:38
2

This situation is further complicated by the fact that the default TIGER scripts for Postgis don't lend themselves to parallelizability - they drop and create the tiger_staging schema, which makes it impossible to load states in parallel.

I've seen people having problems with loading data into RDS with osm2pgsql caused by latency. I've wanted to experiment with RDS, but I haven't had the time or a project that needed.

I would try, instead of using PostGIS loading scripts, using ogr2ogr. You could then try it single-threaded, and running multiple processes in parallel. This should tell you how it scales to multiple threads accessing RDS.

| improve this answer | |
0

You should load the data into the remote server and run the import there. If you're running the script locally it means that the command is sent to server, executed and when the confirmation returns to your machine only then next command is sent.

That means if the lag is 100ms you lose 1 second to lag every 10 rows.

| improve this answer | |
  • It's an RDS database, meaning that I can't run the import 'there'. I can either run it from my local machine, or from an EC2 server in the same zone (which should have substantially less latency). The latter is probably the next option I'll try. – Peter Goldstein Dec 9 '13 at 16:15
  • If you use pg_dump to get the dump of the db and load that it should be much faster. The instruction is here: docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… – Jakub Kania Dec 9 '13 at 23:43
0

I loaded all states with the loader_generate_script command from the documentation modified for mac based on http://www.kyngchaos.com/software/postgis_tiger_geocoder.

In this instance I actually downloaded all the data locally via FTP modified the script to not do the wget command. Downloading the data didn't take too long, ~24 hours. The data extraction and loading into the database however has been running for over a week now.

I'm just letting it run.

| improve this answer | |
  • I'm actually planning to self-answer this at some point in the near future. I ran through the whole import and the critical issue turned out to be network latency. Getting the process loading the data as close as possible to the database server is key. It took me about a week to do a complete load from an EC2 server to an RDS instance in the same zone. – Peter Goldstein Feb 6 '14 at 19:51
  • Thanks for this. I'm just just running this on my mac book, it is now going on two and a half weeks. driving me nuts ;) I just need it to finish. – ubique Feb 9 '14 at 14:12
  • Hmm...ran faster on my local machine - took about 5 days to finish. How many states have completed? – Peter Goldstein Feb 10 '14 at 20:49
  • I checked last night and MI had finished in an alphabetical list of all states. Roughly 1/2 way. – ubique Feb 11 '14 at 23:39

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.