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

11–20 of 101 posts

Re: Loading half a billion rows into MySQL

#11

Post by fleitz »

whalesalad wrote: post #8

I want to give this 10 upvotes. This is one of those things you only see on HN occasionally, and it's full of all kinds of awesome little nuggets. That command to get the count from the information_schema in and of itself is gold (I now know about the tee command)

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

Re: Loading half a billion rows into MySQL

#12

Post by jakejake »

whalesalad wrote: post #8

I want to give this 10 upvotes. This is one of those things you only see on HN occasionally, and it's full of all kinds of awesome little nuggets. That command to get the count from the information_schema in and of itself is gold (I now know about the tee command)

I ran this select from information_schema to get the count of a table with aprox 1.2 million rows. each time I run the query, though, the number returned is different. it seems to vary by about 200k either way. does anybody know why that would be happening?

Re: Loading half a billion rows into MySQL

#14

Post by derwiki »

fleitz wrote: post #11
whalesalad wrote: post #8

I want to give this 10 upvotes. This is one of those things you only see on HN occasionally, and it's full of all kinds of awesome little nuggets. That command to get the count from the information_schema in and of itself is gold (I now know about the tee command)

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.

Re: Loading half a billion rows into MySQL

#15

Post by kristjan »

jakejake wrote: post #12
whalesalad wrote: post #8

I want to give this 10 upvotes. This is one of those things you only see on HN occasionally, and it's full of all kinds of awesome little nuggets. That command to get the count from the information_schema in and of itself is gold (I now know about the tee command)

I ran this select from information_schema to get the count of a table with aprox 1.2 million rows. each time I run the query, though, the number returned is different. it seems to vary by about 200k either way. does anybody know why that would be happening?

It's just an estimate for the query engine, not a true row count. But good enough for a progress bar.

From http://dev.mysql.com/doc/refman/5.0/en/tables-table.html:

> For InnoDB tables, the row count is only a rough estimate used in SQL optimization.

Re: Loading half a billion rows into MySQL

#16

Post by james4k »

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.

Re: Loading half a billion rows into MySQL

#17

Post by juiceandjuice »

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 hierarchical triangular mesh for point-radius searches)

Re: Loading half a billion rows into MySQL

#18

Post by gtani »

Good info. Did percona fix the load nulls ticket?

(LOAD DATA INFILE has this ticket from 2006, that,sometime during the 2000's was converted to a feature request

http://bugs.mysql.com/bug.php?id=23212

I think that's why my load does

    FIELDS ESCAPED BY '\\'
but it's been so long i can't remember (at this point a lot fo folks would mention Postgres, but i'll refrain

Re: Loading half a billion rows into MySQL

#19

Post by cookiecaper »

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?
Post reply on HN