Bug #75110 Massive, to-be-compressed not committed InnoDB table is total database downtime
Submitted: 5 Dec 2014 1:02 Modified: 5 Dec 2014 12:32
Reporter: Jouni Järvinen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[5 Dec 2014 1:02] Jouni Järvinen
Description:
Currently, when you tell MySQL Server to shut down (like through the §/etc/init.d/mysql§ script like in my case), all connections are terminated, the software drops to shutdown process, no connections whatsoever can be made, and in InnoDB's case, and my usage case, will start compressing and committing the data a transaction hadn't committed yet. With little data or not compressing this wouldn't be a problem, but I had a Python script INSERTing a 115GB text file's data of which around 50-60GB were added during that transaction, and MySQL has been shutting down, compressing and committing that data for a little over a week by now, preventing all connections, which means every Server Software depending on MySQL has to be down.

How to repeat:
1) Configure MySQL to use highest compression for compressing InnoDB tables (§innodb_compression_level=9§) so that it takes longer to compress
2) Create InnoDB table with compression
3) Use a script to INSERT really alot of data, in tens of gigabytes
4) Tell MySQL to shut down

Suggested fix:
Instead of terminating the connections and preventing any more connections by dropping to the process of going down, InnoDB deals with getting the transactions completed in addition to whatever else it has to, -then- dropping to shutdown process, avoiding all the downtime that in extreme case can take even a month.

If MySQL used multithreaded zlib too, alot time would be shaved.
[5 Dec 2014 11:53] Umesh Shastry
Hello Jouni Järvinen,

Thank you for the report.
Imho this behavior is by design and hence not a bug, please see http://dev.mysql.com/doc/refman/5.6/en/server-shutdown.html
Also, it is not recommended to have such big transactions(per your note  50-60GB ).

I would recommend you to take a look at available options such as innodb_fast_shutdown etc explained here http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_fast_shutdown 

Thanks,
Umesh
[5 Dec 2014 12:32] Jouni Järvinen
I know it's by design and not a bug. I also have that setting disabled so there's less chances of data loss, as much as I don't like it.