best practices for preparing a master to receive the writer role?
Hi, I'm operating an mmm setup and when switching between masters, my apps experience a period of significant performance degradation. It seems to take about 15 minutes until the new writer gets its caches all warmed up and can keep up with the load. Often, we end up with app downtime just after the move_role because the apps get overwhelmed while waiting for queries that are many times slower than normal.
Since we have almost enough innodb buffer space to hold the entire database, I've attempted to warm up the inactive master just before the switch by simply selecting all rows from all tables, as well as selecting all data contained exclusively in indexes, and this does improve the situation, but not completely, and it does take an extremely long time (I think it was close to an hour last time).
Is there some obvious method of "warming up" a mysqld that I'm unaware of? The next thing I'm contemplating is writing some code which will log an hour's worth of readonly queries from the apps so the queries can be replayed on the inactive master just before the switch.
Question information
- Language:
- English Edit question
- Status:
- Answered
- For:
- mysql-mmm Edit question
- Assignee:
- No assignee Edit question
- Last query:
- Last reply:
Can you help with this problem?
Provide an answer of your own, or ask ntucker for more information if necessary.