best practices for preparing a master to receive the writer role?

Asked by ntucker

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:
Revision history for this message
Arjen Lentz (arjen-lentz) said :
#1

Yep, see http://openquery.com/blog/cache-preloading-mysqld-startup for our solution. This should only be necessary if you're not using read-slaves - since the other master executes all write queries as well, it would have all that data in cache.

Can you help with this problem?

Provide an answer of your own, or ask ntucker for more information if necessary.

To post a message you must log in.