Description:
I have had multiple MySQL crashes the details of which are below:
2015-06-15 04:31:42 7f5e54b3e700 InnoDB: Assertion failure in thread 140043124729600 in file ha_innodb.cc line 10730
InnoDB: Failing assertion: index->table->stat_initialized
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.
11:31:42 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=33554432
read_buffer_size=131072
max_used_connections=6139
max_threads=7000
thread_count=4112
connection_count=4109
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2810236 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0xce7b1b30
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 = 7f5e54b3de18 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8db795]
/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x664dc4]
/lib64/libpthread.so.0[0x3280a0f710]
/lib64/libc.so.6(gsignal+0x35)[0x3280632625]
/lib64/libc.so.6(abort+0x175)[0x3280633e05]
/usr/sbin/mysqld[0x9893b4]
/usr/sbin/mysqld[0x98baab]
/usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3e)[0x5a8a1e]
/usr/sbin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x684)[0x76ded4]
/usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0x1e3)[0x69cc13]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0xc9f)[0x69f49f]
/usr/sbin/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x4f)[0x69f5cf]
/usr/sbin/mysqld[0x6df2e1]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1553)[0x6e1c63]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3c0)[0x6e5790]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x152b)[0x6e6d6b]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6b32bf]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6b33e7]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0x960b4a]
/lib64/libpthread.so.0[0x3280a079d1]
/lib64/libc.so.6(clone+0x6d)[0x32806e88fd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f5de8f1fac0): is an invalid pointer
Connection ID (thread ID): 246446
Status: NOT_KILLED
Enabling general_log yielded a query which when run manually did not induce any crash.
The crashes were gone after I resized table_open_cache down from 16000 to 1000.
Contents from MySQL configuration file are below:
[client]
# CLIENT #
port = 3306
loose-default-character-set = utf8
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = innodb
port = 3306
init-connect = ''
default-time-zone = 'America/Los_Angeles'
# CHARACTER SET #
character-set-server = utf8
collation-server = utf8_general_ci
character-set-client-handshake = 1
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
innodb = FORCE
innodb-strict-mode = 1
lower-case-table-names = 1
log-bin-trust-function-creators= 1
# BINARY LOGGING #
log-bin = binary_log
binlog-format = ROW
expire-logs-days = 5
sync-binlog = 0
# REPLICATION #
server-id = 123231
skip-slave-start = 1
log-slave-updates = 1
relay-log = relay-bin
slave-net-timeout = 60
report-host = hostname
# CACHES AND LIMITS #
tmp-table-size = 256M
max-heap-table-size = 256M
query-cache-type = 0
query-cache-size = 0
max-connections = 7000
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 16000
table-open-cache-instances = 16
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 2G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 105791M
innodb-buffer-pool-dump-at-shutdown= 1
innodb-buffer-pool-load-at-startup= 1
# LOGGING #
long-query-time = 0.5
slow-query-log = 1
log-warnings = 2
general-log = 0
# Performance_schema #
performance-schema = 1
Note that table_open_cache defined above as 16000, was changed to 1000 and there have been no crashes since. We used to have a crash every 3-4 days, after reconfiguring table_open_cache, we haven't had a crash for a month.
mysql> select @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
| 1000 |
+--------------------+
1 row in set (0.00 sec)
How to repeat:
Size a large enough table cache but have enough tables to have good amount of cache misses.
