favstarの中の人のActiveRecordパターンからの移行について

Tim Haines 🇺🇦 @TimHaines

Finally moving away from the ActiveRecord pattern for some of my 'models' in Favstar.

Tim Haines 🇺🇦 @TimHaines

Removing one little integer column seems to have reduced the time to import this table into mysql from 12+ hours to 15 minutes.

aviel 🚜 @aviel

@timhaines dude you should be using Mongo not SQL. What are you THINKING?!

Tim Haines 🇺🇦 @TimHaines

That was a non-PK auto incrementing integer column (which requires an index) on a 3 column table with a composite primary key.

Damon Cortesi @dacort

@timhaines Holy hell.

Tim Haines 🇺🇦 @TimHaines

@aviel Dude, I've just got out of a bad relationship with Riak. I don't want to get into one with Mongo.

Tim Haines 🇺🇦 @TimHaines

@dacort This table structure is how I model favs, retweets, and friendships. Bastardized structure to fit it into Rails's ActiveRecord reqs.

𝕵𝖆𝖘𝖔𝖓 𝕽 @knowncitizen

@timhaines Is that trial by error? :)

Tim Haines 🇺🇦 @TimHaines

@knowncitizen something like that. Beat your head with your mistakes until you learn?

Kenn Ejima @kenn

@timhaines in that, moved away and created a composite primary key, you mean?

Tim Haines 🇺🇦 @TimHaines

@kenn No - it always had a composite primary key. I just removed the (now) redundant non-PK auto-inc column I had.

Kenn Ejima @kenn

@timhaines heh. if you're on mysql 5.1+ and innodb you can take advantage of fast index creation, import without index and built it later.

Tim Haines 🇺🇦 @TimHaines

@kenn I read about that briefly this morning. It's for non-PK indexes right? I don't think I'll need it now.

Kenn Ejima @kenn

@timhaines ah yes, you're right, it's secondary indexes only. for innodb, be careful tho: compound primary key = large clustered index.

Tim Haines 🇺🇦 @TimHaines

@kenn Yeah - I've been using this schema for a couple of years. The tables/indexes are indeed large.

Kenn Ejima @kenn

@timhaines that is, all secondary indexes have VERY large pointer to the record, which is the same size as the compound index

Kenn Ejima @kenn

@timhaines yeah, actually rails' convention is not bad in that sense. innodb works best when you have autoincremented single primary key

Tim Haines 🇺🇦 @TimHaines

@kenn The "insert into t_new select.." was the step that dropped the auto inc column. I wonder why there's a difference in the 13G and 7G.

Kenn Ejima @kenn

@timhaines that's another thing: fragmentation, i assume. you can have the same effect by empty ALTER TABLE if you use innodb_file_per_table

Kenn Ejima @kenn

@timhaines did you check data/index ratio? SELECT table_name, table_rows, data_length, index_length FROM information_schema.tables

Tim Haines 🇺🇦 @TimHaines

@kenn the table_rows column returns a vastly different number each time I run it.

Kenn Ejima @kenn

@timhaines sure, it's just an educated guess by the engine. its histogram is used by the cost-based optimizer and don't need to be accurate

Tim Haines 🇺🇦 @TimHaines

@kenn I would have expected running the "insert into select.." would have had the same result as the dump/reimport.

Kenn Ejima @kenn

@timhaines yes, that's true and that part, I have no idea.

Tim Haines 🇺🇦 @TimHaines

@kenn It jumped from 300m rows to 230m rows between queries. Very inaccurate.