2 ms·

Even faster: loading half a billion rows in MySQL revisited

28 points by derwiki 14y ago

aaronblohowiak 14y ago
Sounds like a good use for MySQL partitioning, which does the table for each month thing automatically and transparently for you. IIRC, you will also avoid the slowdown as the table grows beaches each partition is pretty independent
- derwiki 14y ago
  We considered it, and I mentioned that as an alternative in my original post: MySQL Partitioning: this was the most similar to our existing set up, since MySQL internally stores the data into different tables. We decided against it because it seemed likely that it wouldn’t be much faster than our current solution (although MySQL can internally do some optimizations to make sure you only look at tables that could possibly have data you want). And it’s still the same complexity we were looking to reduce (and would further be the only database set up in our system using partitioning).
  - aaronblohowiak 14y ago
    I believe it would be faster because indexes and such are maintained per-partition, so you should not get that slowdown as the table size increases (assuming you are entering data into new partitions.) Additionally, the complexity would be the same as having one big table -- it logically is one big table, the only difference is in your CREATE TABLE statement.
- lann 14y ago
  (I'm a coworker of the OP) We discussed using MySQL partitions but decided we would rather avoid them if possible. That may have been in part an over-reaction to the pain of dealing with our application-level partitioning, but it was also in part because we don't have a dedicated DBA, which means one of us developer types has to re-learn the quirks of MySQL every time we touch it.
- wincent 14y ago
  We've used various partitioning schemes at Causes (I'm a coworker of the OP) but for this particular use case the monthly tables weren't a good fit. (Your comment about automatic/transparent makes me think you're probably thinking of merge tables. We've used those too for other models, but we're not big fans of them because they require you to use the MyISAM storage engine, which doesn't offer the same consistency guarantees that InnoDB does.) In this particular instance, a single large table with the right indices gives better performance and, importantly for us, speeds up developers by removing unnecessary complexity.
  - aaronblohowiak 14y ago
    I was referring to the MySQL default support for http://dev.mysql.com/doc/refman/5.1/en/partitioning.html http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

28 points by derwiki 14y ago

Sounds like a good use for MySQL partitioning, which does the table for each month thing automatically and transparently for you. IIRC, you will also avoid the slowdown as the table grows beaches each partition is pretty independent

derwiki 14y ago
We considered it, and I mentioned that as an alternative in my original post: MySQL Partitioning: this was the most similar to our existing set up, since MySQL internally stores the data into different tables. We decided against it because it seemed likely that it wouldn’t be much faster than our current solution (although MySQL can internally do some optimizations to make sure you only look at tables that could possibly have data you want). And it’s still the same complexity we were looking to reduce (and would further be the only database set up in our system using partitioning).
- aaronblohowiak 14y ago
  I believe it would be faster because indexes and such are maintained per-partition, so you should not get that slowdown as the table size increases (assuming you are entering data into new partitions.) Additionally, the complexity would be the same as having one big table -- it logically is one big table, the only difference is in your CREATE TABLE statement.
lann 14y ago
(I'm a coworker of the OP) We discussed using MySQL partitions but decided we would rather avoid them if possible. That may have been in part an over-reaction to the pain of dealing with our application-level partitioning, but it was also in part because we don't have a dedicated DBA, which means one of us developer types has to re-learn the quirks of MySQL every time we touch it.
wincent 14y ago
We've used various partitioning schemes at Causes (I'm a coworker of the OP) but for this particular use case the monthly tables weren't a good fit. (Your comment about automatic/transparent makes me think you're probably thinking of merge tables. We've used those too for other models, but we're not big fans of them because they require you to use the MyISAM storage engine, which doesn't offer the same consistency guarantees that InnoDB does.) In this particular instance, a single large table with the right indices gives better performance and, importantly for us, speeds up developers by removing unnecessary complexity.
- aaronblohowiak 14y ago
  I was referring to the MySQL default support for http://dev.mysql.com/doc/refman/5.1/en/partitioning.html http://dev.mysql.com/doc/refman/5.1/en/partitioning.html