Distributed Proofreaders has a very large InnoDB-backed table that was created many years ago on a MySQL version that only supported the system tablespace (ibdata1). We’ve since upgraded to 5.7 which supports file-per-table tablespaces and we have innodb_file_per_table=ON.
With file-per-table tablespaces enabled, some table operations will move tables from the system tablespace to their own per-file tablespace and given the size of the table in question it was important to understand which ones would cause this to happen.
My research lead me to the Online DDL Operations doc which was the key. Any operation that says it Rebuilds Table will move an InnoDB table out of the system tablespace, regardless if In Place says “Yes”.
For example, the following will keep the table where it is:
- Creating, dropping, or renaming non-primary-key indexes
- Renaming a column
- Setting a column default value
- Dropping a column default value
And these will rebuild the table and move it to its own tablespace:
- Adding or dropping primary keys
- Adding or dropping a column
- Reordering columns
- Changing column types
- Converting a character set
The above are not an exhaustive list, see the Online DDL Operations documentation for your version of MySQL for the definitive list.
It’s important to know that OPTIMIZE TABLE will also move an InnoDB table out of the system tablespace.
If there’s an operation you want to perform that will rebuild the table but you want to keep the table in the system tablespace, you can temporarily set innodb_file_per_table=OFF (it’s a dynamic variable), do the operation, and then turn it back ON.
And for the curious, if you have a table already in its per-file tablespace and set innodb_file_per_table=OFF, making changes that will rebuild the table won’t move it to the system tablespace. It looks like you have to drop and recreate the table to do that.
It’s December, which means it’s time to reveal this year’s playlist (aka: mix cd). Like every year, this playlist is a year in review.
Woman Up, Wannabe, and This Is Me is a shout-out to all the amazing women in my life. Thank you for being awesome just the way you are.
My friend Sam introduced me to Cowboy Bebop and the intro credits won me over. Sadly, the original Tank! by The Seatbelts isn’t available for digital purchase so I had to buy a CD and rip it. (The version on the Spotify list is a cover.)
At the beginning of the year I got to see Steve Grand in concert down in Puerto Vallarta, so he had to be on the list for sure. Pink and Norah Jones released new albums so they got a lot (and I mean a lot) of airtime. Yes, there are 4 Pink songs on the list this year — she’s just that amazing. And while Michael Buble’s new album was released in 2018, I didn’t discover it until the beginning of 2019.
From Pink’s 90 Days, which tears me up every time I hear it, I discovered Wrabel’s music. If you’re up for an emotional roller coaster about trans acceptance, watch his music video of The Village. Then feel a little better by telling all the conservatives assholes You Need To Calm Down. Finally, pay homage to the leader of the queer mafia himself: Elton John. His biopic Rocketman reminded me that yes, sometimes that’s really why they call it the blues.
And most importantly: I got married this year! Up’s Married Life seemed like the most perfect way to end the set.
- Tank! – The Seatbelts
- Woman Up – Meghan Trainor
- Wannabe – Spice Girls
- (Hey Why) Miss You Sometimes – Pink
- Hustle – Pink
- This Is Me – Keala Settle
- Wintertime – Norah Jones
- Help Me Make It Through The Night – Michael Buble
- You Need To Calm Down – Taylor Swift
- Walk Me Home – Pink
- 90 Days – Pink & Wrabel
- Stay – Steve Grand
- Walking – Steve Grand
- 11 Blocks – Wrabel
- The Village – Wrabel
- Easy Like Sunday Morning – Lionel Richie
- That’s Why They Call It The Blues – Elton John
- Your Song (instrumental) – Moulin Rouge Soundtrack, disc 2
- Married Life – Up! Soundtrack
You can listen to the songs on Spotify too (except for track 1 where you get a cover of Tank! and track 16 because Lionel Richie isn’t on Spotify). As always, the order of the songs have been carefully curated. You may not be able to listen to them in order with the Spotify free account.