6 ms·
Umm... Isn't a 50 million row export / import usually an hour long task... tops.. I mean assuming you have the scripts? Dump the first DB, import into the seco
by fleitz 12y ago
Umm... Isn't a 50 million row export / import usually an hour long task... tops.. I mean assuming you have the scripts?
Dump the first DB, import into the second? Maybe a couple ETL scripts in between to massage the data?
This task sounds like monodb_dump export_db (or whatever they call their backup tool) | etl.sh | psql -d import_db. Of course shell scripts probably aren't 'webscale' enough to handle 150 GB of data.
Protip: When IO is saturated, make the IO sequential before ever even thinking about threads. Exactly how are more CPUs supposed to make the disk faster? The best you'll do is exploit the command queue more fully.
- bsg75 12y agoWondering the same thing - What was the slow part, reading from Mongo or writing to Postgres? It should not be the latter, unless there are a lot of indexes that were not dropped before the bulk load, or the ETL works one...row...at...a...time... Even a simple 'mongoexport --csv | psql -c "COPY FROM stdin"' should faster for 50M records.
- rjbrock 12y agoIt does seem like it should be faster, but this isn't a vanilla export/import. He is going from unstructured to (I assume) structured data. So he needs to deduplicate and possibly do other operations on the data before importing
- allegory 12y agoThat's usually part of the ETL pipeline. If you look at SSIS and spring integration for example it has the ability to do this in the pipeline.
- fleitz 12y agoYou mean import the raw data into a json column and then SELECT DISTINCT INTO? There are only 50 million rows... this sounds like a 5 minute query.
- rjbrock 12y agoI was assuming he was moving the json into a structured foreign key-> data type of relationship
- zenogais 12y agoIt was actually a combination of several queries in the first database followed by several inserts into postgresql.
- bsg75 12y agoRatio of query time vs insert time?
- jerrysievert 12y agoi'm curious as well - even if the data was only somewhat structured on the mongo side, an import into a single column table with JSON should be fairly quick with \COPY, then with a couple of indexes applied on your disparate data in the JSON data you should have been able to query that into a new table structure quite easily. just wondering what the tradeoffs were in OP's eyes.
- collyw 12y agoWhat about importing the unstructured data into one table in Postgres and querying that table to populate a structured schema on the same Postgres server? It shouldn't be as IO bound, as its copying data from Postgres to another table rather than from Mongo. Can anyone tell me if that would likely bring a performance improvement?
- fleitz 12y agoProbably, when dealing with IO bound problems it's important to think of IO as a really long pipe extremely fat pipe. Think oil pipeline. The key is keeping the pipe full, requesting a piece of data based on what's in the pipe will mean that it's going to be hard to keep the pipe full. You want to request the data long before you're ever going to need it. If your SQL queries are done in such a way that they don't depend on data in the pipe they'll work fast, if they aren't you'll be not much better off than whatever was currently in place.
- allegory 12y agoSpot on. This is not hard work. I'm not sure exactly what was being done that made this take more than an hour. On our kit, which is pretty hefty, I'd schedule a 30 minute window for a 150Gb ETL even with heavy transformations. When we moved our stuff from Mongo to SQL Server it took an entire 6 minutes to drag 30Gb over including SQL full text. That was a 16 core Xeon with 64Gb of RAM and 15K SAS RAID 10 so not a big machine either.
- zenogais 12y agoThis is great. Appreciate the feedback :)