Timed MySQL crash caused by adaptive hash index restart

Source: Internet
Author: User
Let's take a look at this error log: InnoDB: Warning: alongsemaphorewait: -- Thread140593224754944haswaitedatbtr0cur. cline528for241.00secondsthesemaphore: X-lockonRW-

Let's take a look at this error log: InnoDB: Warning: alongsemaphorewait: -- Thread140593224754944haswaitedatbtr0cur. cline528for241.00secondsthesemaphore: X-lockonRW-

Let's take a look at the error log:

InnoDB: Warning: a long semaphore wait: -- Thread 140593224754944 has waited at btr0cur. c line 528 for 241.00 seconds the semaphore: X-lock on RW-latch at 0x7fd9142bfcc8 created in file dict0dict. c line 1838a writer (thread id 140570526021376) has reserved it in mode exclusivenumber of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0cur. c line 535 Last time write locked in file /PBS/build/sb_0-10180689-1378752874.69/mysql-5.5.34/storage/innobase/btr/btr0cur. c line 528 InnoDB: Warning: a long semaphore wait: -- Thread 140570431108864 has waited at btr0cur. c line 528 for 241.00 seconds the semaphore: X-lock on RW-latch at 0x7fd9142bfcc8 created in file dict0dict. c line 1838a writer (thread id 140570526021376) has reserved it in mode exclusivenumber of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0cur. c line 535 Last time write locked in file/PBS/build/sb_0-10180689-1378752874.69/storage/innobase/btr/btr0cur. c line 528 ........................ End of innodb monitor output ============================ InnoDB: ###### Diagnostic info printed to the standard error streamInnoDB: Error: semaphore wait has lasted> 600 secondsInnoDB: We intentionally crash the server, because it appears to be hung.140101 4:32:58 InnoDB: assertion failure in thread 140570570065664 in file srv0srv. c line 2502 InnoDB: We intentionally generate a memory trap. innoDB: Submit a detailed bug report to InnoDB: If you get repeated assertion failures or crashes, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: Upload uption in the InnoDB tablespace. please refer toInnoDB: InnoDB: about forcing recovery.20: 32: 58 UTC-mysqld got signal 6; This cocould be because you hit a bug. it is also possible that this binaryor one of the libraries it was linked against is already upt, improperly built, or misconfigured. this error can also be caused by malfunctioning hardware. we will try our best to scrape up some info that will hopefully helpdiagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. bytes = 1600thread_count = 516connection_count = 515It is possible that mysqld cocould use up bytes + (bytes + sort_buffer_size) * max_threads = 444459 K bytes of memoryHope that's OK; if not, decrease some variables in the equation. thread pointer: 0x0Attempting backtrace. you can use the following information to find outwhere mysqld died. if you see no messages after this, something wentterribly wrong... stack_bottom = 0 thread_stack 0x30000/usr/local/mysql/bin/mysqld (my_print_stacktrace + 0x35) [0x7a5f15]/usr/local/mysql/bin/mysqld (handle_fatal_signal + 0x403) [0x673a13]/lib/libpthread. so.0 (+ 0xef60) [0x7fde6901cf60]/lib/libc. so.6 (gsignal + 0x35) [0x7fde68219165]/lib/libc. so.6 (abort + 0x180) [0x7fde6821bf70]/usr/local/mysql/bin/mysqld [0x7ff2ce]/lib/libpthread. so.0 (+ 0x68ba) [0x7fde690148ba]/lib/libc. so.6 (clone + 0x6d) [clone] The manual page at containsinformation that shocould help you find out what is causing the crash.131231 04:34:11 mysqld_safe Number of processes running now: 0131231 04:34:11 mysqld_safe mysqld restarted


In the early morning, the MySQL process crashed, and all error logs contained
InnoDB: Warning: a long semaphore wait
-- Thread 140570431108864 has waited at btr0cur. c line 528 for 241.00 seconds the semaphore:
X-lock on RW-latch at 0x7fd9142bfcc8 created in file dict0dict. c line 1838

View the Monitoring Chart (refer to the 25-31 days)

We found that the waiting time for spin waits and OS waits was quite high. We found this sentence in the manual:

You can monitor the use of the adaptive hash index and the contention for its use in the SEMAPHORES section of the output of the show engine innodb status command. if you see your threads waiting on an RW-latch created in btr0sea. c, then it might be useful to disable adaptive hash indexing.


Sometimes, the read/write lock that guards access to the adaptive hash index can become a source of contention under heavy workloads, such as multiple concurrent joins.


Because the adaptive hash index causes a lot of lock contention, many processes are blocked, and MySQL crashes and restarts.


After finding the cause, the adaptive hash index is disabled. After one day (see performance month 1), the waiting time for spin waits and OS waits is gradually reduced.

Set global innodb_adaptive_hash_index = 0;


The final cause is solved.


Reference manual:


This article is from the "hechun's technical column" blog. Be sure to keep this source

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.