Long Semaphore Wait Due to Async Flush + InnoDB Monitor + buf0flu.c + buf0buf.c (Doc ID 1347842.1)

Last updated on MARCH 08, 2017

Applies to:

MySQL Server - Version 4.0 and later
Information in this document applies to any platform.

Symptoms

The MySQL error log contains warnings from InnoDB that there are long semaphore waits and the main thread is in the "flushing buffer pool pages" state. For the semaphores there will be messages about locks created in the files buf0buf.c and/or buf0fly.c

Here is a sample of the InnoDB Monitor Output from MySQL 5.0:

=====================================
110811 14:26:00 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 35 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1652, signal count 1646
--Thread 1388053680 has waited at ../include/btr0btr.ic line 28 for 245.00 seconds the semaphore:
X-lock on RW-latch at 0x4cb1ceb4 created in file buf0buf.c line 497
a writer (thread id 1388053680) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file buf0flu.c line 585
Last time write locked in file ../include/btr0btr.ic line 28
Mutex spin waits 0, rounds 4590, OS waits 171
RW-shared spins 2757, OS waits 1378; RW-excl spins 114, OS waits 103

...

--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 3092, id 1367475376, state: flushing buffer pool pages
Number of rows inserted 11763, updated 5964, deleted 11682, read 1049624
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms