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

31–40 of 101 posts

Re: Loading half a billion rows into MySQL

#31

Post by firemanx »

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 that!

Re: Loading half a billion rows into MySQL

#32

Post by reitzensteinm »

juiceandjuice wrote: post #17

I routinely load and reload ~7 billion rows into oracle 11g, once every 5 months or so. It takes about 4 days, 20 days if you do something stupid like create the indexes before loading, although I think oracle can go quite a bit faster, and that 4 days is limited by processing and non-DB I/O. We use oracle because the partitioning options are better and bitmapped indexes. (We wanted more partitions so we could use a…

To save everyone the math, that's ~20k inserts a second.

Re: Loading half a billion rows into MySQL

#33

Post by taligent »

james4k wrote: post #16
dstorrs wrote: post #6

FTA: "...I decided to migrate the existing system into a single table setup." "Alternative Proposed Solutions: MySQL Partitioning and Redis" I'm surprised he didn't consider at Mongo, Couch, etc.

One of the shockers I came across with MongoDB is that each instance of a key takes up memory. There is no form of a symbol table for the keys, so this means a huge amount of data overhead if each 'row' of data uses keys at all, which they likely do. No one just uses arrays.

Not really sure what you mean about the rows given that it is JSON but anyway.

I think what you are referring to is the tokenization of field names: https://jira.mongodb.org/browse/SERVER-863

Re: Loading half a billion rows into MySQL

#34

Post by alexatkeplar »

timaelliott wrote: post #28
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 ).

Are there any good resources which cover HDFS+Hive? I'd really love to see performance measurements but the entire premise of "Let's analyze ALL the data every single query, we'll just use a zillion workers!" has always came across to me as incredibly inefficient and computationally expensive. I'd wager they query their data frequently and in a predictable manner, at which point using some sort data structure actuall…

Sadly there's not a great lot of documentation about HDFS/Hive - we're learning a lot as we go with SnowPlow.

But I do agree with you - if the same queries keep coming up frequently, then it's worth putting some tech in place to save on ad hoc querying costs. But an RDBMS isn't a great fit for this - because the data is a) non-relational and b) immutable. Happily there's a whole class of database designed for this type of work - analytics databases such as Greenplum and Infobright (Infobright is a modded MySQL too).

In any case, if you have your event data stored in flat files, then as well as a "raw load" into your analytics database, you can also schedule regular map-reduce jobs to populate specific cubes into your analytics db. This is something we're working on for SnowPlow now as well.

Re: Loading half a billion rows into MySQL

#35

Post by lukev »

reitzensteinm wrote: post #32
juiceandjuice wrote: post #17

I routinely load and reload ~7 billion rows into oracle 11g, once every 5 months or so. It takes about 4 days, 20 days if you do something stupid like create the indexes before loading, although I think oracle can go quite a bit faster, and that 4 days is limited by processing and non-DB I/O. We use oracle because the partitioning options are better and bitmapped indexes. (We wanted more partitions so we could use a…

To save everyone the math, that's ~20k inserts a second.

Oracle can do a hell of a lot more than that if you preload your tables as transportable table spaces.

Re: Loading half a billion rows into MySQL

#36

Post by alexatkeplar »

trimbo wrote: post #30
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 ).

On the other end of the spectrum, for "small data" (i.e. millions of rows as opposed to tens of billons)... check out crush-tools. I love it, use it all the time. http://code.google.com/p/crush-tools/

Thanks for the tip - looks cool, like a mini-map-reduce for Bash!

Re: Loading half a billion rows into MySQL

#38

Post by alexatkeplar »

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…

You're right, there can be something of a "small files" issue with HDFS. This is a good article for strategies to get round it: http://www.cloudera.com/blog/2009/02/the-small-files-problem...

Re: Loading half a billion rows into MySQL

#39

Post by plasma »

In my experience the built-in partitioning support of MySQL (using PARTITION BY RANGE for example) is very good.

Inserts no longer drag due to huge indexes, delete's are instant (drop a partition of stale data), and SELECT's can limit their lookups to specific underlying files.

This is for a 500m row table of time series data.

Re: Loading half a billion rows into MySQL

#40

Post by Gonsalu »

stonemetal wrote: post #27
Gonsalu wrote: post #5

I fail to see why setting the transaction isolation level to read uncommitted would make any difference to the data load process.

Without thinking about it to hard my guess is there is some overhead in tracking what is readable and what is not readable at the higher read levels. Read uncommitted means it doesn't have to track anything.

Writes still take locks. The isolation level is called Read-uncommitted for a reason.
Post reply on HN