Bug #84108 Mysql 5.7.14 hangs and kills itself during drop database statement
Submitted: 8 Dec 2016 11:49 Modified: 15 Feb 16:32
Reporter: Adrian Słowik Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.14 OS:Other (Gentoo)
Assigned to:

[8 Dec 2016 11:49] Adrian Słowik
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
[15 Feb 16:32] Sinisa Milivojevic
This bug is duplicate of the bug:

https://bugs.mysql.com/bug.php?id=84025

Beside that, you need to set number of files at maximum for your Linux and to assign as  many files as possible to InnoDB. Your installations requires lots of additional configuration. Do note that configuration and other support help is not provided on this forum. This forum is reserved only for the 100 % repeatable test cases. This test case is not repeatable when the system is optimally configured.
[17 May 11:42] Tom Sommer
It is not a duplicate, #84025 is about startup times, this is a crashbug.
[4 Jul 6:25] Arjen Lentz
Dear uncle Sinisa!
This crash problem is NOT a duplicate of the bug# you referred to, which is about a completely different topic.
Please reset the status from 'Duplicate' to something else, so it may be addressed correctly.
Perhaps Sveta can triage it again?
Thanks
Regards,
Arjen downunder.
[30 Aug 12:20] Reinder Repko
We have the same problem in 5.7.12. By the way: duplicate is not the correct status.
[31 Aug 5:08] Shane Bester
with reference to preventing of buffer pool loading on startup being a workaround, I wonder if this is similar to my bug?

https://bugs.mysql.com/bug.php?id=87222