Last week I was faced to a strange problem while benchmarking
MySQL 5.6: some of my user sessions become "frozen" periodically
and finally my whole test was not valid..
More in-depth analyze shown that:
- sessions are "frozen" when executing a SELECT statement..
- sessions are not really frozen, but waiting on read!..
- the read seems to be a kind of long read, involving many I/O operations..
- all makes think that query is execution a full table scan (200M rows, 40GB table) and ignoring indexes, so instead of 1ms I have >5min response time..
But the problem is completely "random" - it's not happening all of the time and not in all users sessions. And every time I'm executing EXPLAIN for any given "wrong" query - it's always giving me a right execution plan.. So, seems that something is going wrong on the time when queries are executed concurrently, and not when I'm …[Read more]