Shulou(Shulou.com)06/01 Report--

The following brings you the introduction of the relevant key points about MySQL semaphore crash, if you are interested, let's take a look at this article. I believe it will be of some help to you after reading the introduction of the relevant key points of MySQL semaphore crash.

BA should respond to InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung. It is not unfamiliar at all. When the MySQL background thread srv_error_monitor_thread finds that there is a latch lock that is blocked for more than 600s, if it detects that the lock has not been released for 10 consecutive times, it will trigger panic to prevent the service from continuing hang.

What happened

Version number: MySQL 5.5.40

The output thread in the log is continuously waiting for the data dictionary lock, the location is dict0dict.c line 305, the waiting time is more than 900s.

The thread holding the lock is 139998697924352, and its hexadecimal is 7f53fca8a700.

-- Thread 139998393616128 has waited at dict0dict.c line 305 for 934.00 seconds the semaphore:X-lock on RW-latch at 0x105a1b8 created in file dict0dict.c line 748a writer (thread id 139998697924352) has reserved it in mode exclusivenumber of readers 0, waiters flag 1, lock_word: 0Last time read locked in file dict0dict.c line 302Last time write locked in file / pb2/build/sb_0-13157587-1410170252.03/rpm/BUILD/mysql-5.5.40/mysql-5.5.40/storage/innobase/dict/dict0dict.c line 305

Lock the transaction information of thread 139998697924352 and query the operation of the data dictionary table.

-TRANSACTION 0, not started updating table statistics:MySQL thread id 14075, OS thread handle 0x7f53fca8a700, query id 110414021 21.14.5.139 jzjkusrSELECT ROUND (SUM (DATA_LENGTH+INDEX_LENGTH+DATA_FREE) / 1024 AS MY_DB_TOTAL_SIZE FROM information_schema.TABLES)

Check the lock holding thread 139998697924352 to see if there are other locks waiting.

Found thread 139998697924352, self-lock in btr0sea.c line 1134, the lock structure is related to AHI.

-- Thread 139998697924352 has waited at btr0sea.c line 1134 for 934.00 seconds the semaphore:X-lock (wait_ex) on RW-latch at 0x1eb06448 created in file btr0sea.c line 178a writer (thread id 139998697924352) has reserved it in mode wait exclusivenumber of readers 1, waiters flag 1, lock_word: ffffffffffffffffLast time read locked in file btr0sea.c line 1057Last time write locked in file / pb2/build/sb_0-13157587-1410170252.03/rpm/BUILD/mysql-5.5.40/mysql-5.5.40/storage/innobase/btr/btr0sea.c line 1134

Next, let's look at which function the two lock structures are in:

Dict0dict.c line 305 is in the dict_table_stats_lock function

Btr0sea.c line 1134 is within the btr_search_drop_page_hash_index function

When will these functions be called?

When innodb_table_monitor is enabled, the X lock on dict_table_stats_lock is called when the log is output. This case is not opened.

When innodb_stats_on_metadata is enabled, querying the data dictionary table triggers an update of statistics and invokes the X lock on dict_table_stats_lock. This matches the transaction information of the locked thread.

Adaptive hash index (AHI) is the hash table structure that InnoDB uses to speed up index page lookups. When the number of page visits meets certain conditions, the address of the page will be stored in a hash table, reducing the overhead of B-tree query.

MySQL version 5.5 AHI is the global lock btr_search_latch that maintains the consistency of hash table modifications.

The InnoDB buffer pool status shows that free buffer basically remains idle at 0. When InnoDB buffer pool expels the data page, the btr_search_drop_page_hash_index function is called to clean up the data page from AHI.

-- BUFFER POOL AND MEMORY--Total memory allocated 17582522368; in additional pool allocated 0

Dictionary memory allocated 4289681

Buffer pool size 1048576

Free buffers 0

Database pages 1040831

Old database pages 384193

Modified db pages 0

Summary

AHI's global lock btr_search_latch is often a competitive hotspot that affects performance. After version 5.7, it has been improved and multi-instance splitting has been done as well as InnoDB buffer. In this case, when Innodb_stats_on_metadata parameters are enabled and metadata information is queried, statistics are updated, data dictionaries are locked, blocking a large number of business operations, and due to insufficient buffer pool space, the table expels old pages and triggers btr_search_latch lock competition in AHI, resulting in semaphore timeout crash.

> > Egg 3 times ["2018-08-13 23:12:18"2018-08-14 12:13:43"2018-08-16 13:42:36"]

MySQL Service start-> 3 times ["2018-08-13 23:12:59"2018-08-14 12:15:20"2018-08-16 13:46:37"]

Which RW-latch is the thread waiting for, including lock location, number of occurrences, thread id (number of occurrences), focusing on those with more occurrences:

* Which thread waited lock * row0purge.cc:861-> 58 140477266503424: (57) 140617703745280: (1) gi.cc:14791-> 1 140477035656960: (1) trx0undo.ic:171-> 1 140617682765568: (1) ha_innodb.cc:14791-> 620 140617389913856: (58) 140202719565568: (58) 140202716903168: (57) 140477029533440: (56) 140617407219456: (55) 140477035656960: (52) 140477035124480: (29) 140477108467456: (29) 140477025539840: (26) 140477031130880: (25) 140477027669760: (22) 140617634944768: (21) 140617634146048: (21) 140477019948800: (21) 140477026604800: (20) 140477022078720: (18) 14047701888384040: (16) 140477028734720: (10) 140477022877440: (9) 140477034325760: (1) 140477031663360: (1) srv0srv.cc:1968-> 1420477276993280: (185) 1406177235136: (23) ha_innodb.cc:5510 > 601 140617398296 (15) 140477028734720: (10) 140477022877440: (9) 140477034336360: (1) 140477031663360: (1) 140477027276993280: (185) 1406177235136: (23) ha_innodb.cc:5510 > 601 1406173988296 (14) 140477017615655 (1490476): 1479776 (1479776) ) 140617636275968: (45) 140617632548608: (40) 14061763425486048: (33) 140617634675648: (32) 140617397102336: (28) 140617639409408: (23) 140617617635743488: (21) 140617617637811968: (18) 1406176173992256: (12) 1406176173992256: (12) 140617638344448: (10) 140617638078208: (10) 140477033793280: (10) 140477029267200: (10) 140617768576 (9) 140176351008: (6) 14061763661216 (140636428) 140626528 (140476) (140476) : (26) 140477123688192: (12) 140477038851840: (5) 140477030065920: (4) 140617634412288: (4) row0trunc.cc:1835-> 1 140477109798656: (1)

Which of the above locks hold X locks, focusing on those that occur more frequently:

* Which writer threads block at *

140616681907968-> 1 trx0undo.ic:171: (1) 140477173069568-> 243 srv0srv.cc:1968: (185) row0purge.cc:861: (57) row0trunc.cc:1835: (1) 140617682765568-> 29 srv0srv.cc:1968: (23) ha_innodb.cc:5510: (5) row0purge.cc:861: (1)

Write the transaction information corresponding to the thread, or there may be log records that do not output transaction information:

* These writer threads trx state * MySQL thread id 83874, OS thread handle 140477173069568, query id 13139674 10.0.1.146 aml deleting from reference tables

Count the S lock held by the writer thread:

* These writer threads at last time reads locked *

140477173069568-> 243 row0purge.cc:861: (243) 140617682765568-> 24 row0purge.cc:861: (24) 140616681907968-> 1 trx0undo.ic:190: (1)

Count the X lock held by the writer thread:

* These writer threads at last time write locked *

140477173069568-> 243 dict0stats.cc:2366: (243) 140617682765568-> 24 dict0stats.cc:2366: (24) 140616681907968-> 1 buf0flu.cc:1198: (1)

Through the post-log analysis, it is possible that the transaction information of the thread is not output to the log, and it is impossible to know what the transaction has done. In response to this situation, Mini Program joined the collection of transaction information.

The usage is as follows:

Hongbin@MBP ~ > mysqldba-uxxx-pxxx doctor-w

It monitors the error log of the target mysql, and whenever the keyword "a writer (thread id 140616681907968) has reserved it in mode" appears, it queries the ps for transaction information and saves it.

Read the above details about MySQL semaphore crash, whether there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.