4 ms·

To get back to the point of the article, is 130MM rows past the point where you should start reaching for "big data" tools? I recently did an ad-hoc analysis of

To get back to the point of the article, is 130MM rows past the point where you should start reaching for "big data" tools? I recently did an ad-hoc analysis of a 50MM row, 3GB dataset and saw similar performance to the OP with a local postgres database. Is it worth using specialized analysis tools for exploring these types of datasets? I ended up writing a script to slice the data, but with `wc -l` taking 2 minutes on my SSD, it wasn't a great interactive experience. What do other people use for this stuff?

d2xdy2 11y ago
Slightly different domain, but I recently had to work on a system that retained email addresses that were known targets of aggressive spamming (450MM rows). In a nutshell, the idea was to compare lists of emails and test to see how many accounts in the given list were also in the database. We'd typically get 10k item lists to test against the database. Initially, I was creating a temporary table and doing a unique intersection on it. That took about 45 minutes on a modest machine. Next, I hacked up a bloom filter search (since all we cared about was whether or not the items in the sample were in the database); that ran in a little under a second for a set of 10k, but got a little unwieldy with sets approaching 1MM+. We talked about it, and decided to just use Elasticsearch, and to just split the database into a bunch of buckets. Search time went up a small bit for small samples, but went waaay down for larger samples. Also of note, going from spinning platters to SSD did make a pretty huge improvement in things when we were using a relational database. Now that it's all basically in memory, it doesn't matter so much.
e12e 11y ago
With "just" 3GB of data, did you try just working on a copy located on a tmpfs/ramdisk?
- stevepike 11y ago
  Wow, on my desktop with 16GB of cheap, 4 year old ram and a Samsung SSD I bought this year, I'm getting 30x faster io on the ramdisk. Clever solution.
  - MikeTV 11y ago
    Even old DDR-200 (aka PC-1600, 1600 MB/s)[1] is faster than the theoretical maximum of SATA III (600 MB/s). Wikipedia says SATA 3.2 can reach 1,969 MB/s, but the more recent DDR3-1600 is 12,800 MB/s and some DDR4 reach 21,300 MB/s [2] ... SSDs aren't even close. DDR4 also theoretically supports 512 GB sticks [3] Can't wait. 1) http://www.crucial.com/usa/en/support-memory-speeds-compatability http://www.crucial.com/usa/en/support-memory-speeds-compatab... 2) http://www.crucial.com/usa/en/memory-ballistix-elite http://www.crucial.com/usa/en/memory-ballistix-elite 3) https://en.wikipedia.org/wiki/DDR4_SDRAM https://en.wikipedia.org/wiki/DDR4_SDRAM

d2xdy2 11y ago

Slightly different domain, but I recently had to work on a system that retained email addresses that were known targets of aggressive spamming (450MM rows). In a nutshell, the idea was to compare lists of emails and test to see how many accounts in the given list were also in the database. We'd typically get 10k item lists to test against the database. Initially, I was creating a temporary table and doing a unique intersection on it. That took about 45 minutes on a modest machine. Next, I hacked up a bloom filter search (since all we cared about was whether or not the items in the sample were in the database); that ran in a little under a second for a set of 10k, but got a little unwieldy with sets approaching 1MM+. We talked about it, and decided to just use Elasticsearch, and to just split the database into a bunch of buckets. Search time went up a small bit for small samples, but went waaay down for larger samples. Also of note, going from spinning platters to SSD did make a pretty huge improvement in things when we were using a relational database. Now that it's all basically in memory, it doesn't matter so much.

e12e 11y ago

With "just" 3GB of data, did you try just working on a copy located on a tmpfs/ramdisk?

stevepike 11y ago
Wow, on my desktop with 16GB of cheap, 4 year old ram and a Samsung SSD I bought this year, I'm getting 30x faster io on the ramdisk. Clever solution.
- MikeTV 11y ago
  Even old DDR-200 (aka PC-1600, 1600 MB/s)[1] is faster than the theoretical maximum of SATA III (600 MB/s). Wikipedia says SATA 3.2 can reach 1,969 MB/s, but the more recent DDR3-1600 is 12,800 MB/s and some DDR4 reach 21,300 MB/s [2] ... SSDs aren't even close. DDR4 also theoretically supports 512 GB sticks [3] Can't wait. 1) http://www.crucial.com/usa/en/support-memory-speeds-compatability http://www.crucial.com/usa/en/support-memory-speeds-compatab... 2) http://www.crucial.com/usa/en/memory-ballistix-elite http://www.crucial.com/usa/en/memory-ballistix-elite 3) https://en.wikipedia.org/wiki/DDR4_SDRAM https://en.wikipedia.org/wiki/DDR4_SDRAM