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

Loading half a billion rows into MySQL

derwiki.tumblr.com

Post reply on HN
Print view View original

41–50 of 101 posts

Re: Loading half a billion rows into MySQL

#41

Post by jerf »

derwiki wrote: post #14
fleitz wrote: post #11

Earlier quoted context omitted.

Also the while; do; loop could probably be replaced with 'watch'.

I tried 'watch' first (love that lil' command), but for some reason it parses incorrectly and doesn't work.

While it is possible to beat that command with backslashes until it works, I often just give up and make it a shell script, on the grounds that usually by the time I have something that complicated I want to "watch" I ought to be checking it into source control, or at least have it on disk for the next time I want it. For database stuff like that I've had nearly a 100% hit rate of either reusing such things, or at least consulting them later for the crafting of a related command.

Re: Loading half a billion rows into MySQL

#42

Post by lann »

gcarre wrote: post #20

"MySQL Partitioning. We decided against it because it seemed likely that it wouldn’t be much faster than our current solution." What if you partition by HASH(user_id) instead of partitioning by month ( http://dev.mysql.com/doc/refman/5.5/en/partitioning-hash.htm... )?

(another Causes engineer here) HASH partitioning could make sense if our queries were primarily filtering on e.g. user_id, but that isn't the case here. Instead we are often querying against one or both of two columns: user_id and action_id. Partitioning by timestamp allows us to ignore partitions older than the user and/or action's creation dates.

Re: Loading half a billion rows into MySQL

#43

Post by MichaelSalib »

alexatkeplar wrote: post #23

Assuming your event data is immutable (i.e. no UPDATEs, just INSERTs), you'd probably have fewer headaches long-term if you just dumped the database to flatfiles, stored in HDFS and queried using Hive (which has MySQLish query syntax anyway). This architecture will take you to billions of rows quite happily. This is the architecture we use for eventstream analysis at SnowPlow ( https://github.com/snowplow/snowplow ).

At my company, we experimented with Hive and found it to be too slow. Once you decide that every row of data will be materialized into a java hash table instance, there are real performance limitations that follow from that representation. We decided to use HDFS and Hadoop but built our own query language called Trecul; it uses LLVM to jit compile dataflow code into native code. Picking off a single field to filter on is a single machine instruction for us.

The code is open source (albeit with rough edges) at https://github.com/akamai-tech/trecul/

Re: Loading half a billion rows into MySQL

#44

Post by MichaelSalib »

firemanx wrote: post #31
alexatkeplar wrote: post #23

Assuming your event data is immutable (i.e. no UPDATEs, just INSERTs), you'd probably have fewer headaches long-term if you just dumped the database to flatfiles, stored in HDFS and queried using Hive (which has MySQLish query syntax anyway). This architecture will take you to billions of rows quite happily. This is the architecture we use for eventstream analysis at SnowPlow ( https://github.com/snowplow/snowplow ).

How big are the flatfiles that you're storing in HDFS? I've looked at it before for such a use, but for durability I want to write events in an isolate manner, which means lots and lots of small writes, either to single files or as a series of small files. I was under the impression that HDFS doesn't perform well in a use case like this (due to the size of it's write block size), but would LOVE if I could use it like…

We're using HDFS with a periodic merger process that occasionally merges small files into larger files. Given the block size, HDFS really does want larger files, but it can tolerate a decent number of small files. The bigger problem with this approach is providing a consistent view of the dataset so that already running programs don't have the world totally change out from under them.

Re: Loading half a billion rows into MySQL

#45

Post by kristjan »

kodablah wrote: post #29

"push as much complexity as possible to the database" In general, this is usually bad practice and difficult to scale. I do agree w/ using a single table, but I disagree with the general premise of that statement.

Maybe this is more consumable as "remove as much complexity as possible from the code". In this case, both the code and the DB become considerably simpler, and it doesn't sound like there was anything fancy at all going on in the end result. I think derwiki just meant the DB will handle this, so you can leave the implementation there, not in custom sharding code.

Re: Loading half a billion rows into MySQL

#47

Post by jtchang »

Does it blow anyone else's mind that database servers are so powerful nowadays that you can just load half a billion rows into a SINGLE TABLE and pretty much expect things to work? Obviously there are caveats but all in all a nice chunk of RAM backed by a large SSD goes a long way.

Re: Loading half a billion rows into MySQL

#48

Post by lost-theory »

autoreverse wrote: post #37
derwiki wrote: post #13

Earlier quoted context omitted.

Thanks, fixed

The chown statement in the article uses a period instead of a colon. Should it be "chown -R mysql:mysql" ?

The period works with GNU coreutils chown (e.g. on Linux), but not other versions.

Re: Loading half a billion rows into MySQL

#50

Post by dredmorbius »

jerf wrote: post #41
derwiki wrote: post #14

Earlier quoted context omitted.

I tried 'watch' first (love that lil' command), but for some reason it parses incorrectly and doesn't work.

While it is possible to beat that command with backslashes until it works, I often just give up and make it a shell script, on the grounds that usually by the time I have something that complicated I want to "watch" I ought to be checking it into source control, or at least have it on disk for the next time I want it. For database stuff like that I've had nearly a 100% hit rate of either reusing such things, or at le…

Setting a really large $HISTSIZE can accomplish some of this.

Though I'll admit to occasional traumatic moments when my workstation crashes and bash history is polluted with multiple shells filling it and the stuff I've been ctrl-R invoking (recursive reverse search) for months evaporates.

Post reply on HN