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:
Tags: disk, innodb, performance
Triage: Needs Triage: D3 (Medium)

[8 Feb 2012 0:55] Mark Callaghan
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
[8 Feb 2012 1:01] Mark Callaghan
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.
[8 Feb 2012 4:54] Valerii Kravchuk
Thank you for the problem report. Same constant is used in current mysql-5.5 and mysql-trunk also.
[7 Nov 2012 22:55] John Russell
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.