5 ms·

4-6 hours still seems like a long time for this, did you have sorted indices on the join columns? How high of cardinality (distinct counts) did the join columns

by kyllo 7y ago

4-6 hours still seems like a long time for this, did you have sorted indices on the join columns? How high of cardinality (distinct counts) did the join columns have? Traditional RDBMS are pretty fast at joining when you have the right indices in place, especially when the cardinality of the join columns isn't very high. And how much of that time was just getting the data into the tables? There are fast and slow ways to do that, too...

f311a 7y ago
IIRC, it took me 4-7 hours to load 600GB of uncompressed data to the PostgreSQL after disabling WAL and tunning some config variables. I didn't have time to test all possible speed improvements. After import, it takes 3-6 hours additionally to create indexes for those tables. I think the main problem was that I had text indices. I'm not an expert in RDBMS and used a simple join that was taking ages to start producing the actual data. There are definitely a lot of ways to tune such queries and PostgreSQL configs, but I wanted a simple and universal solution.
- creddit 7y ago
  On average each line was 1GB of data!? Can you give a rough description of the content of the two files?
  - fourthark 7y ago
    600G / 300M = 2K
    - creddit 7y ago
      Woof, yeah, bad arithmetic by me. Thanks!
  - tsimionescu 7y ago
    Not GP and nothing to do with them, but I have an example of a CSV format which yields absurdly long lines. We have a CSV for timeseries data, where for some reason someone decided to force each line to represent one data point. However, for some cases, one data point may contain around 30 different statistics for 4 million different IPs, which get represented as 120 million columns in the CSV (think a CSV header like 'timestamp,10.0.0.1—Throughput,10.0.0.1-DroppedBytes,[...],10.215.188.251-Throughput,[...]'). With large numbers represented as text in a CSV, this can sometimes reach more than one 1GB per line.

by kyllo 7y ago

f311a 7y ago

IIRC, it took me 4-7 hours to load 600GB of uncompressed data to the PostgreSQL after disabling WAL and tunning some config variables. I didn't have time to test all possible speed improvements. After import, it takes 3-6 hours additionally to create indexes for those tables. I think the main problem was that I had text indices. I'm not an expert in RDBMS and used a simple join that was taking ages to start producing the actual data. There are definitely a lot of ways to tune such queries and PostgreSQL configs, but I wanted a simple and universal solution.

creddit 7y ago
On average each line was 1GB of data!? Can you give a rough description of the content of the two files?
- fourthark 7y ago
  600G / 300M = 2K
  - creddit 7y ago
    Woof, yeah, bad arithmetic by me. Thanks!
- tsimionescu 7y ago
  Not GP and nothing to do with them, but I have an example of a CSV format which yields absurdly long lines. We have a CSV for timeseries data, where for some reason someone decided to force each line to represent one data point. However, for some cases, one data point may contain around 30 different statistics for 4 million different IPs, which get represented as 120 million columns in the CSV (think a CSV header like 'timestamp,10.0.0.1—Throughput,10.0.0.1-DroppedBytes,[...],10.215.188.251-Throughput,[...]'). With large numbers represented as text in a CSV, this can sometimes reach more than one 1GB per line.