（エラーメッセージの原因と対策）Long Semaphore Wait Due to Async Flush + InnoDB Monitor + buf0flu.c + buf0buf.c
(Doc ID 2241513.1)
Last updated on FEBRUARY 27, 2020
適用範囲:
MySQL Server - バージョン 4.0 以降この文書の内容はすべてのプラットフォームに適用されます。
現象
MySQLのエラーログに、InnoDBのメインスレッドが"flushing buffer pool pages"というステータスで、長時間セマフォを待っているという警告が出力されることがあります。本記事での対象のセマフォは、buf0buf.cおよびbuf0fly.cにおいて作成されたものです。
下記に、MySQL 5.0におけるInnoDBモニターの出力のサンプルを示します。
=====================================
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
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
原因
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
本書の内容
| 現象 |
| 原因 |
| 解決策 |
| 参照情報 |