Mysql slave state invalidating

As explained in an earlier blog post, we recently started using My SQL master-slave replication on in order to provide the scalability necessary to accommodate our growing demands.

While the asynchronous nature has its advantages, it is also means that the slaves might be (slightly) out of sync.

Consider the following pseudo-code: to query the master, we risk not being able to present the visitor the data that he just saved.

In other cases, we risk introducing data inconsistencies due to the race conditions.

I'm currently working on a My SQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that are being executed on many of the tables.

What I'm trying to determine is whether or not there is any benefit at all to allowing the query cache to be used for SELECT statements that are being run against these tables.

Since they get invalidated so quickly, it seems to me the best thing would be to just use SQL_NO_CACHE on SELECT statements with these tables.

Is the overhead of frequent invalidation ever worth it? The Query Cache will always butt heads with Inno DB.

Edit: At the request of the user @Rolando My SQLDBA below, here's the info on My ISAM and INNODB. It would be nice if Inno DB's MVCC would let queries be served from the query cache if modifications do not affect repeatable reads for other transactions. Apparently, you have a lot of queries that get invalidated rather quickly and are probably not being reused.

For Inno DB under My SQL 4.0, the query cache was disabled for transactions.

Tags: , ,