Re: Shudden Slow down of MySQL Queries

[Rick James]

> mixed engines ( innodb and myisam)

* Transactional semantics do not apply to MyISAM tables.

* Table locking on MyISAM can indirectly stall things. For example, this could happen if one connection is doing some long process on a MyISAM table (read or write) and then another connection writes to that table inside a BEGIN..COMMIT. The write will stall until it can get access to the table, thereby stalling the InnoDB transaction. Look at your code to see if there are any cases of MyISAM activity (of any type) between a BEGIN..COMMIT. (There are many other scenarios that can cause stalls.)

> While facing this problem, We usally just switch the Master-Slave behaviour. After this switching ( i.e, Slave becomes master) is working fine.

Probably coincidence. It is likely that the switch changed the timing of the operations such that the problem did not recur.

> Is this problem with tables fragmentation ( innodb tables).

Very unlikely.

> or Is this problem with innodb log file size, innodb_buffer_pool_size or any.

How big is the buffer_pool? How much RAM do you have? How big is the entire dataset? How big is the table (or set of tables) caught up in this problem? (SHOW TABLE STATUS, then look at Data_length+Index_length)

Set the buffer_pool to about 70% of available RAM.

What version of MySQL? There used to be a nasty problem wherein InnoDB would happily do lots of transactions per second, then suddenly do nothing for a second or so. Then repeat. Percona fixed it a few years ago, then Oracle fixed it. (I forget the specific version numbers.)

(I agree that DNS is an unlikely cause.)

Can you provide SHOW ENGINE INNODB STATUS right after it happens?
Can you provide the query that was running? (SHOW FULL PROCESSLIST) Also, provide SHOW CREATE TABLE for the table(s) involved.