mysql crashing with "mysqld got signal 11"
I'm running application which reading and writing data with mysql database. I'm having both mysql ndbclustr and master master replication in same database. (bcoz of master master replication tables' engines are innodb, clustering won't replicate data of those tables.)
I'm having 64GB RAM in each MYSQL server and there are 2 servers with same configurations
Issue I'm getting is, when writing to tables after a few minutes of a time I'm getting
05:49:16 UTC - mysqld got signal 11 ;
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=134217728
read_buffer_size=131072
max_used_connections=502
max_threads=600
thread_count=505
connection_count=501
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 369478 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7fe8ac0273a0
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 = 7fea140bae70 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xf7246b]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x82a6e1]
/lib64/libpthread.so.0(+0xf100)[0x7fee30719100]
/usr/sbin/mysqld(_ZN12NdbOperation23insertKEYINFO_NdbRecordEPKcj+0xda)[0x136d69a]
/usr/sbin/mysqld(_ZN12NdbOperation21buildSignalsNdbRecordEjyPKj+0x5bd)[0x136de1d]
/usr/sbin/mysqld(_ZN16NdbScanOperation23takeOverScanOpNdbRecordEN12NdbOperation13OperationTypeEP14NdbTransactionPK9NdbRecordPcPKhPKNS0_16OperationOptionsEj+0x1f1)[0x1370651]
/usr/sbin/mysqld(_ZN13ha_ndbcluster14ndb_update_rowEPKhPhi+0x5e7)[0x12ec577]
/usr/sbin/mysqld(_Z12mysql_updateP3THDR4ListI4ItemES4_y15enum_duplicatesPyS6_+0x1305)[0xdc5625]
/usr/sbin/mysqld(_ZN14Sql_cmd_update23try_single_table_updateEP3THDPb+0x1d6)[0xdc7b16]
/usr/sbin/mysqld(_ZN14Sql_cmd_update7executeEP3THD+0x36)[0xdc7e56]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x5d0)[0xd3d680]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3b5)[0xd43a55]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xa7a)[0xd4454a]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x19f)[0xd45f8f]
/usr/sbin/mysqld(handle_connection+0x288)[0xe055d8]
/usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0x142e644]
/lib64/libpthread.so.0(+0x7dc5)[0x7fee30711dc5]
/lib64/libc.so.6(clone+0x6d)[0x7fee2f1cd1cd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fe8ac02b980): update dash_subscription_total_count set subscription = 38, unsubscription =10, authentication = 2368553
Connection ID (thread ID): 1107
Status: NOT_KILLED
Below contains the outputs related to
mysql> SHOW GLOBAL STATUS LIKE 'key_%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 107171 |
| Key_blocks_used | 3 |
| Key_read_requests | 6 |
| Key_reads | 3 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+--------+
mysql> SHOW GLOBAL STATUS LIKE 'uptime_%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| Uptime_since_flush_status | 613 |
http://mysqlcalculator.com/ this site shows the output for memory as 32522 MB.
Mysql Configuration
[mysqld]
#ndbcluster # run NDB storage engine
open_files_limit =4096
max_connections = 600
event_scheduler = OFF
server-id = 14
log-bin=mysql-bin
expire_logs_days=1
binlog_format=mixed
relay-log=mysql-relay-bin
replicate-do-table=css_cluster_ndb.transaction_log_0
replicate-do-table=css_cluster_ndb.transaction_log_1
replicate-do-table=css_cluster_ndb.transaction_log_2
replicate-do-table=css_cluster_ndb.transaction_log_3
replicate-do-table=css_cluster_ndb.transaction_log_4
replicate-do-table=css_cluster_ndb.transaction_log_5
replicate-do-table=css_cluster_ndb.transaction_log_6
replicate-do-table=css_cluster_ndb.transaction_log_7
replicate-do-table=css_cluster_ndb.transaction_log_8
replicate-do-table=css_cluster_ndb.transaction_log_9
skip-name-resolve=1
symbolic-links=0
wait_timeout=120
interactive_timeout=120
query_cache_size=0
query_cache_type=0
query_cache_limit=32M
thread_cache_size=36
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=15G
innodb_flush_method=O_DIRECT #O_DSYNC, O_DIRECT
innodb_doublewrite = 0
innodb_file_per_table=1
innodb_lock_wait_timeout=120
key_buffer_size=128M
max_connect_errors = 2000
innodb_log_file_size=1G
innodb_buffer_pool_instances=15
slave-skip-errors=1590
slave-skip-errors=1533
table_open_cache =8000
#######################
#######################################
explicit_defaults_for_timestamp = 1
log_timestamps = SYSTEM
##############################
auto-increment-increment = 2 # No of replication servers
auto-increment-offset = 5 # First replication server
log-error=/var/log/mysqld.log
ndbcluster
#bind-address=10.81.133.2
#bind-address=10.80.172.194
ndb-connectstring=10.81.133.5:1186 # IP address for server management node
#ndb-connectstring=10.81.133.5:1186 # IP address for server management node
default_storage_engine=ndbcluster # Define default Storage Engine used by MySQL
#log-error=/var/log/mysqld.log
[mysql_cluster]
#ndb-connectstring=10.81.133.5 # IP address for server management node
ndb-connectstring=10.81.133.5
SHOW ENGINE INNODB STATUS
| InnoDB | |
=====================================
2018-03-12 13:00:06 0x7f4d495c0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 24 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 422 srv_active, 0 srv_shutdown, 210 srv_idle
srv_master_thread log flush and writes: 632
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3725
OS WAIT ARRAY INFO: signal count 4814
RW-shared spins 0, rounds 1901, OS waits 128
RW-excl spins 0, rounds 69393, OS waits 5
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 1901.00 RW-shared, 69393.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2024054201
Purge done for trx's n:o < 2024041077 undo n:o < 0 state: running but idle
History list length 53
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421461956475616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421461956474704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421461956473792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
13166 OS file reads, 6011 OS file writes, 2135 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.75 writes/s, 1.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 160766, seg size 160768, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 3983951, node heap has 0 buffer(s)
Hash table size 3983951, node heap has 0 buffer(s)
Hash table size 3983951, node heap has 0 buffer(s)
Hash table size 3983951, node heap has 0 buffer(s)
Hash table size 3983951, node heap has 1 buffer(s)
Hash table size 3983951, node heap has 0 buffer(s)
Hash table size 3983951, node heap has 0 buffer(s)
Hash table size 3983951, node heap has 0 buffer(s)
0.37 hash searches/s, 0.21 non-hash searches/s
---
LOG
---
Log sequence number 1096750789777
Log flushed up to 1096750789777
Pages flushed up to 1096750789777
Last checkpoint at 1096750789768
0 pending log flushes, 0 pending chkp writes
3508 log i/o's done, 0.83 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 16491479040
Dictionary memory allocated 268388
Buffer pool size 982920
Free buffers 972688
Database pages 10231
Old database pages 3874
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 10192, created 39, written 5341
0.00 reads/s, 0.04 creates/s, 0.92 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 10231, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 65528
Free buffers 64836
Database pages 692
Old database pages 275
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 692, created 0, written 403
0.00 reads/s, 0.00 creates/s, 0.08 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 692, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 65528
Free buffers 64759
Database pages 769
Old database pages 303
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 769, created 0, written 415
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 65528
Free buffers 64818
Database pages 710
Old database pages 282
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 710, created 0, written 282
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 710, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 65528
Free buffers 64872
Database pages 656
Old database pages 262
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 656, created 0, written 309
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 656, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 65528
Free buffers 64836
Database pages 692
Old database pages 273
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 688, created 4, written 534
0.00 reads/s, 0.04 creates/s, 0.67 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 692, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 65528
Free buffers 64960
Database pages 568
Old database pages 229
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 568, created 0, written 379
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 568, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 65528
Free buffers 65058
Database pages 470
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 470, created 0, written 267
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 470, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 65528
Free buffers 64751
Database pages 777
Old database pages 306
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 777, created 0, written 365
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 777, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 8
Buffer pool size 65528
Free buffers 64883
Database pages 645
Old database pages 258
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 645, created 0, written 168
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 645, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 9
Buffer pool size 65528
Free buffers 64778
Database pages 750
Old database pages 296
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 750, created 0, written 374
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 750, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 10
Buffer pool size 65528
Free buffers 64811
Database pages 717
Old database pages 284
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 717, created 0, written 254
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 717, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 11
Buffer pool size 65528
Free buffers 64944
Database pages 583
Old database pages 235
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 583, created 0, written 272
0.00 reads/s, 0.00 creates/s, 0.17 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 583, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 12
Buffer pool size 65528
Free buffers 64783
Database pages 745
Old database pages 295
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 745, created 0, written 471
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 745, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 13
Buffer pool size 65528
Free buffers 64694
Database pages 834
Old database pages 327
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 799, created 35, written 570
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 834, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 14
Buffer pool size 65528
Free buffers 64905
Database pages 623
Old database pages 249
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 623, created 0, written 278
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 623, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=988, Main thread ID=139970148284160, state: sleeping
Number of rows inserted 427, updated 0, deleted 0, read 8
0.50 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Answer
Think I found the issue. Bad mistake in here.
As per following equation the configurations must change. As per the following equation needs more than 6TB RAM.
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 6295169214 K bytes
Think this will solve it. Anyway after changing this, it should worked. But still didn't.