线上故障排查-mysql

报表系统运行sql，执行出错。

问题描述： 别的sql报表都没问题，只有一个报表sql有问题，一执行，mysql立马宕机，sql大概有300行左右。

首先监控系统状态，执行sql后发现cpu总利用率位置在50左右，内存利用率在70左右，并没有达到极限。

其次怀疑max_connections和buffer_size设置小了，调大重启后无果。

打开错误日志，看到如下报错信息。发现应该innodb的日志的问题，百度后查到了innodb force recovery=4这个参数，

作用是不写入缓冲区。重启后sql正常执行。

161108 11:36:45 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var

2017-08-15 11:36:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-08-15 11:36:46 5497 [Note] Plugin 'FEDERATED' is disabled.

2017-08-15 11:36:46 7f11c48e1720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

2017-08-15 11:36:46 5497 [Note] InnoDB: Using atomics to ref count buffer pool pages

2017-08-15 11:36:46 5497 [Note] InnoDB: The InnoDB memory heap is disabled

2017-08-15 11:36:46 5497 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-08-15 11:36:46 5497 [Note] InnoDB: Memory barrier is not used

2017-08-15 11:36:46 5497 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-08-15 11:36:46 5497 [Note] InnoDB: Using CPU crc32 instructions

2017-08-15 11:36:46 5497 [Note] InnoDB: Initializing buffer pool, size = 16.0M

2017-08-15 11:36:46 5497 [Note] InnoDB: Completed initialization of buffer pool

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 5.

InnoDB: You may have to recover from a backup.

2017-08-15 11:36:46 7f11c48e1720 InnoDB: Page dump in ascii and hex (16384 bytes):

 len 16384; hex 

InnoDB: End of page dump

2017-08-15 11:36:46 7f11c48e1720 InnoDB: uncompressed page, stored checksum in field1 1954074744, calculated checksums for field1: crc32 993334256, innodb 2046145943, none 3735928559, stored checksum in field2 1139795846, calculated checksums for field2: crc32 993334256, innodb 1606613742, none 3735928559, page LSN 0 254222157, low 4 bytes of LSN at page end 254221236, page number (if stored to page already) 5, space id (if created with >= MySQL-4.1.1 and stored already) 0

InnoDB: Page may be a transaction system page

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 5.

InnoDB: You may have to recover from a backup.

InnoDB: It is also possible that your operating

InnoDB: system has corrupted its own file cache

InnoDB: and rebooting your computer removes the

InnoDB: error.

InnoDB: If the corrupt page is an index page

InnoDB: you can also try to fix the corruption

InnoDB: by dumping, dropping, and reimporting

InnoDB: the corrupt table. You can use CHECK

InnoDB: TABLE to scan your table for corruption.

InnoDB: See also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

InnoDB: Ending processing because of a corrupt database page.

2017-08-15 11:36:46 7f11c48e1720  InnoDB: Assertion failure in thread 139714288817952 in file buf0buf.cc line 4201

InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

InnoDB: If you get repeated assertion failures or crashes, even

InnoDB: immediately after the mysqld startup, there may be

InnoDB: corruption in the InnoDB tablespace. Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.

03:36:46 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.

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed, 

something is definitely wrong and this may fail.

key_buffer_size=16777216

read_buffer_size=262144

max_used_connections=0

max_threads=1000

thread_count=0

connection_count=0

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 798063 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/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0x8e64b5]

/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x41b)[0x652fbb]

/lib64/libpthread.so.0(+0xf7e0)[0x7f11c44c77e0]

/lib64/libc.so.6(gsignal+0x35)[0x7f11c315d625]

/lib64/libc.so.6(abort+0x175)[0x7f11c315ee05]

/usr/local/mysql/bin/mysqld[0xa585c5]

/usr/local/mysql/bin/mysqld[0xa6c7b4]

/usr/local/mysql/bin/mysqld[0xa6cbc7]

/usr/local/mysql/bin/mysqld[0xa5bce2]

/usr/local/mysql/bin/mysqld[0xa1e2ba]

/usr/local/mysql/bin/mysqld[0xa0bf60]

/usr/local/mysql/bin/mysqld[0x95a427]

/usr/local/mysql/bin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x58f788]

/usr/local/mysql/bin/mysqld[0x6e4a36]

/usr/local/mysql/bin/mysqld(_Z11plugin_initPiPPci+0xb3e)[0x6e826e]

/usr/local/mysql/bin/mysqld[0x582d85]

/usr/local/mysql/bin/mysqld(_Z11mysqld_mainiPPc+0x4d8)[0x587d18]

/lib64/libc.so.6(__libc_start_main+0xfd)[0x7f11c3149d5d]

/usr/local/mysql/bin/mysqld[0x57a019]

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

informat
--------------------- 
作者：冰 河 
来源：CSDN 
原文：https://blog.csdn.net/l1028386804/article/details/77199194 
版权声明：本文为博主原创文章，转载请附上博文链接！