MySQL Bugs: #64258: WAIT_FOR_READ is stuck in the 90s

Bug #64258	WAIT_FOR_READ is stuck in the 90s
Submitted:	8 Feb 2012 0:55	Modified:	7 Nov 2012 22:55
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S5 (Performance)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	disk, innodb, performance

Description:
This constant does match flash/ssd read response times (5000 us == 5 ms) but flash/ssd can do reads in ~100 us.

/** Value in microseconds */
static const int WAIT_FOR_READ  = 5000;

It is used in buf_page_get_zip and buf_page_get_gen

For example:

        if (must_read) {
                /* Let us wait until the read operation
                completes */

                for (;;) {
                        enum buf_io_fix io_fix;

                        mutex_enter(block_mutex);
                        io_fix = buf_page_get_io_fix(bpage);
                        mutex_exit(block_mutex);

                        if (io_fix == BUF_IO_READ) {

                                os_thread_sleep(WAIT_FOR_READ);
                        } else {
                                break;
                        }
                }
        }

How to repeat:
Run Innodb on fast storage
Run PMP to find threads stuck in that wait loop

Suggested fix:
Use a shorter wait

I prefer that this get changed to use a condition variable and if that is too much work a my.cnf variable should work for now.

Thank you for the problem report. Same constant is used in current mysql-5.5 and mysql-trunk also.

Added to changelog for 5.1.67, 5.5.29, 5.6.9, 5.7.0: 

The timing values for low-level InnoDB read operations were adjusted
for better performance with fast storage devices, such as SSD. This
enhancement primarily affects read operations for BLOB columns in
compressed tables.