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

71–80 of 101 posts

Re: Loading half a billion rows into MySQL

#71

Post by bsg75 »

derwiki wrote: post #7
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.

My coworkers and I have absolutely zero experience with Mongo, Couch, etc. We do have experience with MySQL and Redis (see https://github.com/causes/mock_redis ), and I feel "safer" keeping important data in something as mature as MySQL.

Is there a pattern for putting multiple Redis "collectors" in front of a single RDBMS aggregate / history store?

Re: Loading half a billion rows into MySQL

#72

Post by [deleted] »

Andys wrote: post #63
fleitz wrote: post #56

Earlier quoted context omitted.

I'm more impressed with how cheap it is. An SSD is on par with a 15K disk, but delivers 10X the perf, throw 10 SSDs in an array and you've got what used to cost $100,000 for about $5K

But SSDs that cost the same as a 15K disk do not have anywhere near the life time of the spinning disk due to write endurance limits. So load half a billion rows but don't update it too often!

[deleted]

Re: Loading half a billion rows into MySQL

#73

Post by staunch »

lost-theory wrote: post #48
autoreverse wrote: post #37

Earlier quoted context omitted.

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.

I think it whines that it's deprecated though.

Re: Loading half a billion rows into MySQL

#74

Post by gaius »

jtchang wrote: post #47

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.

I was impressed by stuff like this in the 90s ;-)

What's impressive now is not the software but that the hardware is so cheap.

Re: Loading half a billion rows into MySQL

#75

Post by xentronium »

crazygringo wrote: post #24
cookiecaper wrote: post #19

If this data is primarily archival and there are multiple backups of the same dataset out there, why not use MyISAM? In my mind the only reasons to use InnoDB are integrity-related, things like real foreign keys and a more ACIDy commit method. If the dataset is read-only and copied in several places, surely this stuff does not matter too much and MyISAM is much more performant. Maybe I misread the use case?

I'm thinking if the server dies, MyISAM tables tend to corrupt more easily, and repairing a table that large will take a long time. But that's the only thing I can think of -- and the speed/size of MyISAM tables have a lot going for them, I'd be tempted to go with MyISAM. Anybody else?

* Adding an index to myisam takes the same time as recreating the table (additionally taking as much space)

* Any operation on myisam table locks it from writing

Re: Loading half a billion rows into MySQL

#76

Post by rs »

spudlyo wrote: post #68
jpitz wrote: post #54

Earlier quoted context omitted.

My telemetry table is >2B rows and growing. 5B is starting to worry me; .5B, not so much. This is even backed by ( not much ) spinning rust.

Hopefully you don't have an auto-increment int (2^31) as a PK, or you'll be worrying sooner than you think.

That's one reason why switching auto-increments to bigint does help (a lot):

http://dev.mysql.com/doc/refman/5.5/en/integer-types.html

Re: Loading half a billion rows into MySQL

#77

Post by aw3c2 »

advocate1 wrote: post #22

It's more work for the computer to process data stored as "rows" than data stored as columns. Think of how a disk is organized. If you abandon MySQL and store data as columns you can load a trillion rows. But forget I mentioned this.

what is the technical difference? I would have thought the dbms takes care of a performant storage/organization system for me.

Re: Loading half a billion rows into MySQL

#78

Post by fleitz »

Andys wrote: post #63
fleitz wrote: post #56

Earlier quoted context omitted.

I'm more impressed with how cheap it is. An SSD is on par with a 15K disk, but delivers 10X the perf, throw 10 SSDs in an array and you've got what used to cost $100,000 for about $5K

But SSDs that cost the same as a 15K disk do not have anywhere near the life time of the spinning disk due to write endurance limits. So load half a billion rows but don't update it too often!

Given the cost of power and space and vs. the number of additional drives necessary I'm not sure that longevity is worth it.

Re: Loading half a billion rows into MySQL

#79

Post by gizzlon »

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…

Just curious; what's the hardware like? Is it reading from spinning disks?

Re: Loading half a billion rows into MySQL

#80

Post by alexatkeplar »

MichaelSalib wrote: post #43
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 o…

Thanks for sharing Michael - Trecul looks to be the first OSS release from Akamai (at least on GitHub)?

While we're on the subject of high-performance alternatives to Hive for event analysis, we've been watching the development of Ben Johnson's behavioural db, called Sky, with interest:

https://github.com/skylandlabs/sky

Post reply on HN