10 ms·
Loading half a billion rows into MySQL
- tantalor 14y agoI'm not familiar with Percona, what problem does it solve?
- derwiki 14y agoPerformance, for one: http://www.percona.com/software/percona-server/benchmarks/ http://www.percona.com/software/percona-server/benchmarks/ They also provide a lot of nice tools: http://www.percona.com/software/percona-toolkit/ http://www.percona.com/software/percona-toolkit/
- beersigns 14y agoPercona also absorbed Maatkit in their tools for those familiar with it. Very handy stuff in there for automation of various processes. Pretty happy with Percona as a drop in replacement for MySQL.
- dstorrs 14y agoIt's a fork of MySQL that provides substantially better durability, admin tools, an easy hot-backup solution, better instrumentation of your DB, etc etc. Deploying is it a question of "stop db, replace mysql server binary with percona server binary, start db". My personal opinion is that unless you have a support contract with MySQL AB, there is very little reason not to use Percona.
- latimer 14y agoTheir Xtradb storage engine has very tangible performance benefits over InnoDB, especially on SSDs.
- Cloven 14y agoBeyond the other listed benefits: Percona also provides support contracts quite inexpensively, which can be worth their weight in diamonds. I bought a support contract after our Percona instance started to slow down, and the level of quality of the analysis and the results blew my mind. And I've been doing SQL for 25 years.
- Gonsalu 14y agoI fail to see why setting the transaction isolation level to read uncommitted would make any difference to the data load process.
- stonemetal 14y agoWithout 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.
- Gonsalu 14y agoWrites still take locks. The isolation level is called Read-uncommitted for a reason.
- dstorrs 14y agoFTA: "...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.
- derwiki 14y agoMy 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 https://github.com/causes/mock_redis), and I feel "safer" keeping important data in something as mature as MySQL.
- whalesalad 14y agoI've heard so many stories of people moving away from MongoDB once they reach scale like yours, I don't blame you.
- bsg75 14y agoIs there a pattern for putting multiple Redis "collectors" in front of a single RDBMS aggregate / history store?
- james4k 14y agoOne 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.
- taligent 14y agoNot 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 https://jira.mongodb.org/browse/SERVER-863
- 14y ago
- whalesalad 14y agoI 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)
- fleitz 14y agoAlso the while; do; loop could probably be replaced with 'watch'.
- derwiki 14y agoI tried 'watch' first (love that lil' command), but for some reason it parses incorrectly and doesn't work.
- jlgreco 14y agoiirc, watch calls /bin/sh to execute what you give it. Maybe there was a quoting issue?
- jerf 14y agoWhile 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.
- dredmorbius 14y agoSetting 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.
- tantalor 14y agoSELCT table_rows Should be, SELECT table_rows
- derwiki 14y agoThanks, fixed
- autoreverse 14y agoThe chown statement in the article uses a period instead of a colon. Should it be "chown -R mysql:mysql" ?
- lost-theory 14y agoThe period works with GNU coreutils chown (e.g. on Linux), but not other versions.
- staunch 14y agoI think it whines that it's deprecated though.
- ralph 14y agoYes, it's a good habit to get into because . is allowed in user and group names whereas : can't be; it's the field separator in /etc/{passwd,group}.
- juiceandjuice 14y agoI 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)
- reitzensteinm 14y agoTo save everyone the math, that's ~20k inserts a second.
- lukev 14y agoOracle can do a hell of a lot more than that if you preload your tables as transportable table spaces.
- dkhenry 14y agoMySQL can do a whole lot more then that too. I routinly get 15k to 20k writes per second while the server is under normal load. You can spike that a great deal higher depending on what else your doing and what kind of table your writing.
- gizzlon 14y agoJust curious; what's the hardware like? Is it reading from spinning disks?
- juiceandjuice 14y agoDell X4240 with dual quad core opteron 2384 (2700Mhz), 64GB memory, 6TB disk space in RAID-5, I believe 10000RPM drives. Oracle partitions are set by week, then subpartitioned by hierarchical triangular mesh region (http://www.skyserver.org/htm/ http://www.skyserver.org/htm/), and our data is inherently anisotropic. Block size is 4kb I believe.
- gtani 14y agoGood 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 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
- cookiecaper 14y agoIf 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?
- crazygringo 14y agoI'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?
- spudlyo 14y agoYa, recovery time is a big deal. It's like the difference between ext2 and ext3. With ext2 a crash is gonna require a lengthy and painful fsck, especially if your table/filesystem is large. A journaled filesystem is a lot like a database engine with transaction/redo logs. After you crash, you replay the last 'n' uncommitted transactions (they're all idempotent so you can do this multiple times) in the journal and you're good to go. Also don't forget about MyISAM's lack of row level locks, and really nowadays there are fewer and fewer cases where it's faster than InnoDB.
- xentronium 14y ago* 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
- gcarre 14y ago"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.html http://dev.mysql.com/doc/refman/5.5/en/partitioning-hash.htm...)?
- lann 14y ago(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.
- advocate1 14y agoIt'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.
- aw3c2 14y agowhat is the technical difference? I would have thought the dbms takes care of a performant storage/organization system for me.
- dfragnito 14y agoWe have been doing some tests with http://www.monetdb.org/Home http://www.monetdb.org/Home. We saw inserts per second in the range of 50k/sec on a laptop.
- advocate1 14y agommap. +1. 10MB source tarball. -1. Looks like it's worth a test. Thanks for the reference.
- alexatkeplar 14y agoAssuming 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 https://github.com/snowplow/snowplow).
- timaelliott 14y agoAre 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 actually designed for fast searching (such as a simple btree in most rdbms) makes perfect sense here. If the data is purely for archive purposes and is rarely queried or is queried in very random patterns that can't really make adequate usage of indices, then I'd agree with your suggestion.
- alexatkeplar 14y agoSadly 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.
- trimbo 14y agoOn 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/ http://code.google.com/p/crush-tools/
- spudlyo 14y agoI saw chunk load time increase from 1m40s to around an hour per million inserts. Your insert performance falls off a cliff when a majority of the index pages for the table no longer fit into the innodb buffer pool. After that happens, there is gonna be a bunch of random i/o. You can solve this problem by using partitioning, that way only a single partition's worth of index pages need to fit into the buffer pool to keep inserts into that partition fast. Of course you have to size your partitions accordingly. A few other tips. Disable fsync() at commit entirely. Set innodb_flush_log_at_trx_commit=0. If you crash curing the data load, start over. Set your transaction logs to be as large as possible, which is usually 4G.
- kodablah 14y ago"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.
- kristjan 14y agoMaybe 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.
- plasma 14y agoIn 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.
- tlrobinson 14y agoGood stuff. I remember trying to load a Wikipedia database dump into MySQL on my laptop years ago and giving up.
- jtchang 14y agoDoes 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.
- jpitz 14y agoMy 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.
- spudlyo 14y agoHopefully you don't have an auto-increment int (2^31) as a PK, or you'll be worrying sooner than you think.
- rs 14y agoThat'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 http://dev.mysql.com/doc/refman/5.5/en/integer-types.html
- Cyndre 14y agoThat is interesting to know, I have been setting my index's to bigint for a long time.
- jpitz 14y agoUgh. No. This uses a multi-column natural key. I have a tendency to avoid synthetic keys when I can.
- fleitz 14y agoI'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
- mcartyem 14y agoWhy would anyone partition tables -- ever?
- advocate1 14y agoThis discussion reminds me of non-technical users. They will tolerate incredible sluggishness. It is amazing what Oracle and the free alternatives get away with. People, including engineers/developers, not only tolerate sluggishness but when something actually works on the first try they think it's amazing. I've witnessed this tolerance for mediocrity for years and it still continues to blow my mind.
- dkhenry 14y agoI want to say this gentleman is doing it wrong. If his current set-up is what I perceive from the description he is not going to be gaining any speed or scalability. by moving to one giant table. In fact he might be losing some. It sounds like he set up 12 monthly tables and is inserting data into each one by month then he is querying across all 12 tables and joining the results. You can do that query in one fell swoop using UNION, If you have a significant number of entries the overhead of opening the 12 tables is negligible. If your looking up on a primary key then you will be more then fast enough. Switch your tables into MyISAM and make a 13th table. insert the current month into the 13 table and query across all 13 tables when you need to get data out. At the end of the month move all records in the 13th table into its proper month table. ( make the 13th table a memory table sync it to disk every few minutes or put it on a really fast SSD )
- eklitzke 14y agoWhy do you think it's going to be faster to do a UNION query against 12 (or 13) tables than it will be to do a non-UNION query against a single table? It's also unclear to me why you're suggesting the author switch to MyISAM. Presumably they're using InnoDB because it's actually ACID compliant, and even if they're not, according to MySQL's own benchmarks InnoDB is as fast or faster than MyISAM for heavy read workloads, e.g. as described at http://www.oracle.com/partners/en/knowledge-zone/mysql-5-5-innodb-myisam-522945.pdf http://www.oracle.com/partners/en/knowledge-zone/mysql-5-5-i...
- dkhenry 14y agoThe main source for my reasoning is experience. The UNION query will be faster because you will be doing a UNION against 12 MyISAM tables instead of doing a single query against a InnoDB table. MyISAM is much faster in read only situations. Once you start writing to it it becomes slower, but the 13th table prevents writes, for that 13th table you might need to go with a slow table like InnoDB. ( yeah I know about that benchmark its a flawed benchmark )
- pbreit 14y agoI didn't downvotes but I can see that while he gist of your comment may be reasonable, the first sentence is harsh and not necessarily correct with our limited info.
- evanelias 14y agoAll great advice! As a fellow habitual migrator of billions of rows in MySQL, a few things I'd add: Do several chunked LOAD DATA INFILE queries in parallel. It's counter-intuitive but you'll get a performance boost. I usually chunk large data sets into 100+ pieces and do 40 concurrent import queries. Mileage may vary, depends a lot on your hardware and data set's sweet spot. Don't use mysqldump for the export. SELECT...INTO OUTFILE is faster, especially since you can apply the exact same parallelized chunking technique. Do this on a standby slave (ie, one not receiving reads) with replication stopped, so that you get a consistent dump at a point in time. The TSV dump doesn't need to be in your database directory. It can be anywhere mysqld can access. It can be beneficial to put it on a separate volume entirely, so that you're reading heavily from one volume while writing heavily to another. Be mindful of disk scheduler choices in Linux if you're exporting from a faster disk to a slower disk. There's some definite edge cases that can lead to starvation of core processes. Also, be mindful of disk scheduler choices in Linux in general. Avoid use of CFQ on your database data volume. Watch disk utilization with iostat or another tool of your choice. If you're coming close to saturating one or more volumes, that probably means you're on the right track :) As other commenters have mentioned, the row count in information_schema (or equivalently SHOW TABLE STATUS) is an estimate, due to how MVCC works. If you need a more accurate count (but not quite perfect count for a specific moment-in-time for a growing data set), do that in parallel chunked SELECT COUNT(*) queries. Happy migrating!
- dasil003 14y ago> Do several chunked LOAD DATA INFILE queries in parallel. It's counter-intuitive but you'll get a performance boost. I usually chunk large data sets into 100+ pieces and do 40 concurrent import queries. Mileage may vary, depends a lot on your hardware and data set's sweet spot. I'm super curious about this. Is it because loading data—even in raw CSV rather than SQL format—is CPU-bound rather than IO bound?
- evanelias 14y agoIt's hard to say. A single-threaded import might not be saturating any particular hardware component; there's other overhead in the work. And when you account for multiple CPUs and multi-disk RAID arrays, the picture gets more complicated. InnoDB journals data. It's writing the data sequentially and then later arranging it to the correct place, which will also be sequential by primary key. So if you're loading in a bunch of sequential chunks in parallel, I imagine InnoDB can probably order the final B-Tree writes so that they also end up being sequential, even if the initial writes to its logfile ended up being interleaved.
- neilk 14y agoThis struck me as odd: "You could drop the indices completely and add them later, but with a table size this big I didn’t think it would help much." Surely this is one of the main reasons why the load speed went way down as the database size increases. It has to be better to add an index later, possibly much better.
- evanelias 14y agoI initially thought so too, but I found the same result -- loading indexes later gave little or no improvement in import speed. Sometimes it can even be a penalty. This actually makes sense though: InnoDB secondary indexes are just additional B-trees, with the key storing the indexed columns and the leaf nodes storing the full primary key (I think minus any redundant columns with the index, but don't quote me on that). When doing a huge import, you always want to do it in order, since InnoDB's clustered indexes mean that the main table data is stored in primary key order. So you don't get B-tree page splits in the primary data structure while doing a sequential import. Secondary indexes, however, won't have this benefit. Their insertion will effectively be random I/O with lots of page splits and fragmentation. This will be equally true whether you build them at import time or build them later. In both cases, MySQL doesn't have the data ordered by the new secondary index yet until it has built the index. Building secondary indexes at import time also benefits from being able to do a chunked concurrent import (multiple LOAD DATA INFILE queries at once). On my hardware I've found this will build a secondary index on a standby slave much faster than an ALTER TABLE will, since an ALTER TABLE rewrites the entire table, in 5.1 anyway. So actually when adding indexes to existing large tables, I sometimes dump out all the data in parallel INTO OUTFILE queries, drop the table, recreate the table with the new indexes in the DDL, and then re-import all the data with parallel LOAD DATA INFILE queries. As a bonus this also defragments. That said, a single ALTER TABLE is a lot less human work, if you haven't automated your imports and exports.
- amalag 14y agoIf you are storing this data for archival purposes & querying, did you check out Infobright? They have built it as a MySQL engine.
- j-b 14y agoIssues that can also severely degrade a systems performance and may not come into play until your data is growing over extended periods of time (not represented in the benchmark here): 1) Table statistics can grow stale and degrade the engines ability to select the correct access path. This can be corrected by running CREATE STATISTICS, depending on your DB version. 2) Page fragmentation, not to be confused with disk fragmentation, when the engine is selecting non-contiguous space. A rebuild of the db will be necessary if your pages are extremely fragmented and/or using a non-optimized allocation size.