Description:
There is 7000 databases in the cluster, with 230 tables each = total of 1610000 tables.
I am dropping unused databases every day. MySQL 5.7.14 freezes during drop database statement and then kills itself
Mysql 5.6.30 works perfectly, no crashes
Log file:
2016-12-07T09:37:16.688769Z 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2016-12-07 10:37:16 0x7f61866e1700 InnoDB: Assertion failure in thread 140056843917056 in file ut0ut.cc line 917
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.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:37:16 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=262144
max_used_connections=1301
max_threads=1301
thread_count=6
connection_count=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1025588 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
/opt/mysql/bin/mysqld(my_print_stacktrace+0x47)[0x562f40c89e08]
/opt/mysql/bin/mysqld(handle_fatal_signal+0x46c)[0x562f4047cd27]
/lib64/libpthread.so.0(+0x10ec0)[0x7f73029feec0]
/lib64/libc.so.6(gsignal+0x3b)[0x7f7301e475eb]
/lib64/libc.so.6(abort+0x180)[0x7f7301e48a10]
/opt/mysql/bin/mysqld(+0x62726f)[0x562f4043f26f]
/opt/mysql/bin/mysqld(_ZN2ib5fatalD1Ev+0x6f)[0x562f40efcaaf]
/opt/mysql/bin/mysqld(srv_error_monitor_thread+0xeea)[0x562f40e98236]
/lib64/libpthread.so.0(+0x74d9)[0x7f73029f54d9]
/lib64/libc.so.6(clone+0x6d)[0x7f7301f01a7d]
Some data from monitoring stuff:
- a lot of disk write operations (1230 ops/sec)
- a lot of innodb data fsyncs (491/sec)
- a lot of innodb pages read (1580/sec)
- a lot of opened files (729/sec)
After this crash it's impossible to start mysql again, it freezes the same way after recovery
Maybe there's something wrong with page cleaner refactoring in mysql 5.7:
Log info:
2016-12-08T07:33:32.381816Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 875364ms. The settings might not be optimal. (flushed=0, during the time.)
2016-12-08T07:33:32.382092Z 0 [Note] InnoDB: Waiting for purge to start
<freezes here and kills itself>
How to Restart mysql:
setting innodb_buffer_pool_load_at_startup=0 allowed to restart mysql
Dropping databases after restart (empty buffer pool) works fine - no delays.
When I fill the buffer pool and then start dropping tables, mysql freezes after saveral drops.
Monitoring of buffer pool data during the process:
[[root@pm05 :: (none)]] > show global status like "%buffer_pool_bytes%";
+--------------------------------+-------------+
| Variable_name | Value |
+--------------------------------+-------------+
| Innodb_buffer_pool_bytes_data | 60038922240 |
| Innodb_buffer_pool_bytes_dirty | 372752384 |
+--------------------------------+-------------+
2 rows in set (0.00 sec)
[[root@pm05 :: (none)]] > show global status like "%buffer_pool_bytes%";
+--------------------------------+-------------+
| Variable_name | Value |
+--------------------------------+-------------+
| Innodb_buffer_pool_bytes_data | 60038922240 |
| Innodb_buffer_pool_bytes_dirty | 339984384 |
+--------------------------------+-------------+
2 rows in set (0.00 sec)
[[root@pm05 :: (none)]] > show global status like "%buffer_pool_bytes%";
+--------------------------------+-------------+
| Variable_name | Value |
+--------------------------------+-------------+
| Innodb_buffer_pool_bytes_data | 60038922240 |
| Innodb_buffer_pool_bytes_dirty | 325713920 |
+--------------------------------+-------------+
2 rows in set (0.00 sec)
[[root@pm05 :: (none)]] > show global status like "%buffer_pool_bytes%";
+--------------------------------+-------------+
| Variable_name | Value |
+--------------------------------+-------------+
| Innodb_buffer_pool_bytes_data | 60038922240 |
| Innodb_buffer_pool_bytes_dirty | 288768000 |
+--------------------------------+-------------+
2 rows in set (0.01 sec)
[[root@pm05 :: (none)]] > show global status like "%buffer_pool_bytes%";
+--------------------------------+-------------+
| Variable_name | Value |
+--------------------------------+-------------+
| Innodb_buffer_pool_bytes_data | 60038922240 |
| Innodb_buffer_pool_bytes_dirty | 288768000 |
+--------------------------------+-------------+
2 rows in set (0.00 sec)
How to repeat:
1) Start mysql
2) Create a lot of databases with a lot of tables each (7000 DBs, 230 tables each) with some example data
3) Wait for buffer pool to fill up
4) Start droping databases (you can do it in parallel to speed things up)
5) Mysql freezes
