Today I learned something about the buffer pool in MySQL that made it seem less like performance black magic! So, before today I knew that the buffer pool was an in-memory cache for MySQL that let you do super fast lookups (especially PK lookups, but maybe only PK lookups (? uncertain on that one)), like, our PK lookups with the BP turned on take microseconds, and they did so even before we moved to SSDs for all our databases . I didn't know ANYTHING about how the buffer pool worked though, other than like, "caching: it makes things faster".
So, today we were doing a routine schema change truncating some old tables. We did a TRUNCATE operations live with all the databases still in configuration serving live traffic , because a TRUNCATE essentially does a DROP/CREATE at the table level, instead of row by row (since MySQL 5.1, we're on 5.5), so it's usually reasonably high performance. TURNS OUT, it also does some really not-so-performant cache invalidation on the buffer pool! This