Shulou(Shulou.com)05/31 Report--

How to understand the Master instance Crash caused by semi-synchronization in MySQL, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

Scene:

The version of the database when Crash occurs: MySQL-5.7.12. Fix is officially marked at 5.7.17.

In the master-slave architecture where semi-synchronization is enabled, the slave library enables semi-synchronization, and starting / restarting slave threads causes the Master instance to Crash

Conclusion:

Mysql bug, attached bug single link: https://bugs.mysql.com/bug.php?id=79865

Problem description (excerpt):

Description: From 5.7, semimursync add Ack_receiver thread for listening slave ack,which use select (). But select () can only listen socket fd between 1 and _ FD_SET_SIZE (my os is 1024), when socket fd is bigger than _ FD_SET_SIZE, select () has no effect, and can never get ack from slave,then semi-sync can't run normally.even more,select () use array store fds, when use FD_SET store fd which is bigger than _ FD_SET_SIZE, array will overflow,so mysqld may crash.

The main problem lies in the select method of tcp connection. Usually, the operating system declares the most big data of the file descriptors that select can operate in a process through the macro FD _ SET_SIZE. However, in general, the value of this FD_SET_SIZE is only 1024.

In fact, there is less use of epoll or poll, and select seems to use very little.

Recurrence of the problem:

Prepare a master / slave architecture of MySQL-5.7.12 and enable semi-synchronization:

In order to enable a large number of file descriptors as simply as possible, the "features" of the MyISAM partition table are used here.

At this time, the select statement is executed several times in succession on the main library (> 5)

At this point, take a look at the number of file descriptors in the main library

So now restart slave on the semi-synchronous slave library and tail the log of the master library.

Crash occurs in the main library a few seconds after the thread is restarted

PS: during the test, the select statement was executed many times, and then it was confirmed that the semi-synchronous state of the master library was also ON. It was almost inevitable to restart slave on the slave library quickly.

PPS: the MyISAM table opens all partition files at the same time when open, so it is convenient to simulate situations that take up a large number of file descriptors.

(MyISAM partition table: http://blog.itpub.net/29510932/viewspace-2134679/)

PPPPPPPS: _ (: "∠") _

Attach the test script and Crash information

CREATE TABLE `myisam_ t` (

`id`int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

/ * 50100 PARTITION BY HASH (id)

PARTITIONS 2000 * /

Click (here) to collapse or open

2017-04-28T22:10:00.731611+08:00 5092 [Note] Start binlog_dump to master_thread_id (5092) slave_server (13043), pos (, 4)

2017-04-28T22:10:01.648365+08:00 5092 [Note] Start semi-sync binlog_dump to slave (server_id: 13043), pos (, 4)

* buffer overflow detected * *: / usr/sbin/mysqld terminated

Backtrace:

/ lib/x86_64-linux-gnu/libc.so.6 (+ 0x731af) [0x7fcdfc7981af]

/ lib/x86_64-linux-gnu/libc.so.6 (_ _ fortify_fail+0x37) [0x7fcdfc81dcf7]

/ lib/x86_64-linux-gnu/libc.so.6 (+ 0xf6f10) [0x7fcdfc81bf10]

/ lib/x86_64-linux-gnu/libc.so.6 (+ 0xf8c67) [0x7fcdfc81dc67]

/ usr/lib/mysql/plugin/semisync_master.so (_ ZN12Ack_receiver17get_slave_socketsEP6fd_set+0x83) [0x7fcc73d4a493]

/ usr/lib/mysql/plugin/semisync_master.so (_ ZN12Ack_receiver3runEv+0x603) [0x7fcc73d4aaf3]

/ usr/lib/mysql/plugin/semisync_master.so (ack_receive_handler+0x19) [0x7fcc73d4aba9]

/ usr/sbin/mysqld (pfs_spawn_thread+0x1b4) [0xe90784]

/ lib/x86_64-linux-gnu/libpthread.so.0 (+ 0x80a4) [0x7fcdfdf650a4]

/ lib/x86_64-linux-gnu/libc.so.6 (clone+0x6d) [0x7fcdfc80d87d]

Click (here) to collapse or open

14:10:01 UTC-mysqld got signal 6

This could be because you hit a bug. It is also possible that this binary

Or one of the libraries it was linked against is corrupt, improperly built

Or misconfigured. This error can also be caused by malfunctioning hardware.

Attempting to collect some information that could help diagnose the problem.

As this is a crash and something is definitely wrong, the information

Collection process might fail.

Key_buffer_size=8388608

Read_buffer_size=131072

Max_used_connections=5

Max_threads=9999

Thread_count=8

Connection_count=2

It is possible that mysqld could use up to

Key_buffer_size + (read_buffer_size + sort_buffer_size) * max_threads = 21899362 K bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0

Attempting backtrace. You can use the following information to find out

Where mysqld died. If you see no messages after this, something went

Terribly wrong...

Stack_bottom = 0 thread_stack 0x40000

/ usr/sbin/mysqld (my_print_stacktrace+0x2c) [0xe77fec]

/ usr/sbin/mysqld (handle_fatal_signal+0x459) [0x7a7019]

/ lib/x86_64-linux-gnu/libpthread.so.0 (+ 0xf8d0) [0x7fcdfdf6c8d0]

/ lib/x86_64-linux-gnu/libc.so.6 (gsignal+0x37) [0x7fcdfc75a067]

/ lib/x86_64-linux-gnu/libc.so.6 (abort+0x148) [0x7fcdfc75b448]

/ lib/x86_64-linux-gnu/libc.so.6 (+ 0x731b4) [0x7fcdfc7981b4]

/ lib/x86_64-linux-gnu/libc.so.6 (_ _ fortify_fail+0x37) [0x7fcdfc81dcf7]

/ lib/x86_64-linux-gnu/libc.so.6 (+ 0xf6f10) [0x7fcdfc81bf10]

/ lib/x86_64-linux-gnu/libc.so.6 (+ 0xf8c67) [0x7fcdfc81dc67]

/ usr/lib/mysql/plugin/semisync_master.so (_ ZN12Ack_receiver17get_slave_socketsEP6fd_set+0x83) [0x7fcc73d4a493]

/ usr/lib/mysql/plugin/semisync_master.so (_ ZN12Ack_receiver3runEv+0x603) [0x7fcc73d4aaf3]

/ usr/lib/mysql/plugin/semisync_master.so (ack_receive_handler+0x19) [0x7fcc73d4aba9]

/ usr/sbin/mysqld (pfs_spawn_thread+0x1b4) [0xe90784]

/ lib/x86_64-linux-gnu/libpthread.so.0 (+ 0x80a4) [0x7fcdfdf650a4]

/ lib/x86_64-linux-gnu/libc.so.6 (clone+0x6d) [0x7fcdfc80d87d]

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

Information that should help you find out what is causing the crash.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.