Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The One Billion Row Challenge

morling.dev

Post reply on HN
Print view View original

361–366 of 366 posts

Re: The One Billion Row Challenge

#361

Post by rustforlinux »

ftisiot wrote: post #189
arkh wrote: post #131

Earlier quoted context omitted.

I'd like to see it speed tested against an instance of Postgres using the file Foreign Data Wrapper https://www.postgresql.org/docs/current/file-fdw.html CREATE EXTENSION file_fdw; CREATE SERVER stations FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE records ( station_name text, temperature float ) SERVER stations OPTIONS (filename 'path/to/file.csv', format 'csv', delimiter ';'); SELECT station_name, MIN(temper…

Just modified the original post to add the file_fdw. Again, none of the instances (PG or ClickHouse) were optimised for the workload https://ftisiot.net/posts/1brows/

>The result is 44.465s!

Very nice! Is this time for the first run or second? Is there a big difference between the first and second run, please?

Re: The One Billion Row Challenge

#362

Post by asah »

anarazel wrote: post #349
asah wrote: post #348

Earlier quoted context omitted.

wow! awesome tips. I knew COPY rocks but didn't realize it would win vs INSERT INTO SELECT FROM ! https://pganalyze.com/blog/5mins-postgres-optimizing-bulk-lo... 1/10th scale tests: psql -c 'create table temps_int2_copy as select * from temps_int2_copy where 1=0;' psql -Xq -c 'COPY (SELECT (1000*random())::int2 as city, (random()*random()*1100)::int2 temp FROM (SELECT generate_series(1,1e8::int8))) TO STDOUT WITH BIN…

> wow! awesome tips. I knew COPY rocks but didn't realize it would win vs INSERT INTO SELECT FROM ! We (postgres) should fix that at some point... The difference basically is that there's a dedicated path to insert many tuples at once that's often used by COPY that isn't used by INSERT INTO ... SELECT. The logic for determining when that optimization is correct (consider e.g. after-insert per-row triggers, the trigge…

thx! Indeed, upon reading the source and sleeping on it, I agree, and in fact it looks like a single-user postgres backend with COPY FROM BINARY is approximately the same architecture as writing the database files in-place, and of course includes support for default values, triggers, constraints, TOAST and more.

I've reproduced the speed difference between pg COPY vs various cases.

Some results (middle result of 3 stable runs) from 1.4GB BINARY dump:

   echo "drop table if exists tbl; create table tbl(city int2, temp int2);copy tbl FROM '/citydata.bin' binary;" | ./pg/bin/postgres --single -D tmp -p 9999 postgres;

   real 0m34.508s


   # switching to unlogged table

   real 0m30.620s


   # hardcoding heap_multi_insert() to be a NOOP  (return early if ntuples>100)
   # fyi, heap_multi_insert() gets called with n=1000 tuples per call

   real 0m11.276s


   # hardcoding skip_tuple = true in src/backend/commands/copyfrom.c:1142

   real 0m6.894s


   # after testing various things
   time sh -c "tar cf - citydata.bin | (cd /tmp; tar xf -)"

   real 0m2.811s
Note: I tried increasing the blocksize (--with-blocksize) and also MAX_BUFFERED_TUPLES (copyfrom.c:65) but as expected they didn't help, I guess n=1000 tuples amortizes the overhead.

Re: The One Billion Row Challenge

#363

Post by ftisiot »

rustforlinux wrote: post #361
ftisiot wrote: post #189

Earlier quoted context omitted.

Just modified the original post to add the file_fdw. Again, none of the instances (PG or ClickHouse) were optimised for the workload https://ftisiot.net/posts/1brows/

>The result is 44.465s! Very nice! Is this time for the first run or second? Is there a big difference between the first and second run, please?

first run! I just rerun the experiment: - ~46 secs on the first run - ~22 secs on the following runs

Re: The One Billion Row Challenge

#364

Post by Ellipsis753 »

Interestingly, because your program runs on Linux and is run 5 times, Linux will almost certainly cache the 12gb file to RAM on the first invocation.

This means that future invocations don't have to load the file from disk. This also makes it pretty critical that your program doesn't use more than 16gb of ram itself (out of the server's 32gb) or it'll push the file out of cache making future invocations of your program slower.

Re: The One Billion Row Challenge

#365

Post by lessbergstein »

declaredapple wrote: post #343
lessbergstein wrote: post #183

Earlier quoted context omitted.

Yes, this is an extremely trivial problem. Anybody who knows how to program in more than one language is going to find this silly. awk or perl would finish it before jit compilation gets started.

Bit of an update, the record is now 8 seconds in java. I have tried 2 naive awk implementations and both took 10x the basic java implementation btw (I'm sure it too can be optimized).

Just stick to Java

Re: The One Billion Row Challenge

#366

Post by posix86 »

viraptor wrote: post #188
posix86 wrote: post #181

Earlier quoted context omitted.

But how would you detect that a station name is colliding or not colliding? With a hash set?

Search for cuckoo hashing. It's a whole thing for data structures.

Cuckoo doesn't aid in detecting collisions, the algorithm is about what happens IF a collision is found. The whole reason we're hashing in the first place is to not have to linearly compare station names when indexing.

In other words: Cuckoo is a strategy to react to the case if two values map to the same hash. But how to know weather you have two different values, or two of the same, if they have an identical hash?

Post reply on HN