MySQL Bugs: #68197: InnoDB reports that it's going to wait for I/O but the I/O is async

Bug #68197	InnoDB reports that it's going to wait for I/O but the I/O is async
Submitted:	28 Jan 2013 5:56	Modified:	20 Apr 2013 14:01
Reporter:	Laurynas Biveinis (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5, 5.6	OS:	Any
Assigned to:	Inaam Rana	CPU Architecture:	Any
Tags:	innodb, threadpool

Description:
Credit for discovery goes to MariaDB developers.

buf_read_page_low() reads:

thd_wait_begin(NULL, THD_WAIT_DISKIO);
if (zip_size) {
     fil_io(...);
} else {
     fil_io(...);
}
thd_wait_end(NULL, THD_WAIT_DISKIO);

The problem is that fil_io might be async here, returning much sooner than the sync call would (buf_read_page_low() has the ibool sync arg for that). Thus the thread might get needlessly reassigned to another query while it should have proceeded with the current one.

Don't know if it's a real issue with a thread pool or just a minor code quality as I cannot test with the Oracle thread pool.

How to repeat:
Might be possible to concoct some benchmark with MySQL Enterprise Edition, which I don't have an access to.

Suggested fix:
Guard thd_wait_begin()/thd_wait_end() calls with an if (sync) check.

It is verified based on the code inspection and analysis.

InnoDB team was also consulted.

Thanks Sinisa.

Alexey Kopytov has pointed out that simple if (sync) guard is not enough, as async I/O will wait if no AIO slot available.

The bug as described does exist but it is not nearly as bad as it sounds. In InnoDB reads are synchronous. Async IO reads are typically readahead which does not usually consist bulk of read workload.

I think a 'sync' check guard should be decent enough fix. It is true that aio will wait if we are unable to find a slot but that happens only when system is under very high pressure. The default for read_io_thread is 4. Each thread can handle upto 256 concurrent IO requests. This means we have to have 1024 outstanding read requests (generated by read ahead) before we have to wait for an aio slot. If the system goes in such busy state then we actually need to tune it with higher number of read_io_threads.

Added to changelog for 5.5.31, 5.6.11: 

Internal read operations could be misclassified as synchronous when
they were actually asynchronous. When the I/O requests returned
sooner than expected, threads could be scheduled inefficiently. This
issue mainly affected read-ahead requests, and thus had relatively
little impact on I/O performed by user queries.

5.5$ bzr log -r 4189
------------------------------------------------------------
revno: 4189
committer: Inaam Rana <inaam.rana@oracle.com>
branch nick: mysql-5.5
timestamp: Fri 2013-02-01 09:47:16 -0500
message:
  Bug#16249505 INNODB REPORTS THAT IT'S GOING TO WAIT FOR I/O BUT THE
  I/O IS ASYNC
  
  rb://1934
  approved by: Mikael Ronstrom (over email)
      
  When submitting AIO read request don't signal that the thread is
  about to wait on DISKIO

4701 in 5.6.