setting the right mysql innodb_log_file_size

setting the right mysql innodb_log_file_size

We ran an alter table today today that took down the DB. We failed over to
the salve, and in the post-mortem, we discovered this in the mysql
error.log
InnoDB: ERROR: the age of the last checkpoint is 90608129,
InnoDB: which exceeds the log group capacity 90593280.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
This error rings true because we were working on a very large table that
contains BLOB data types.
The best answer we found online said
To solve it, you need to stop MySQL cleanly (very important), delete the
existing InnoDB log files (probably lb_logfile* in your MySQL data
directory, unless you've moved them), then adjust the innodb_log_file_size
to suit your needs, and then start MySQL again. This article from the
MySQL performance blog might be instructive.
and in the comments
Yes, the database server will effectively hang for any updates to InnoDB
tables when the log fills up. It can cripple a site.
which is I guess what happened, based on our current (default)
innodb_log_file_size?
SHOW GLOBAL VARIABLES LIKE '%innodb_log%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| innodb_log_buffer_size | 8388608 |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
+-----------------------------+----------+
So, this leads me to two pointed questions and one open-ended one:
how do we determine the largest row so we can set our
cinnodb_log_file_size to be bigger than that?
what is the consequence of the action in step 1? I'd read about long
recovery times with bigger logs.
is there anything else I should worry about regarding migrations,
considering that we have a large table (650k rows, 6169.8GB) with
unrestrained, variable length BLOB fields.
We're running mysql 5.6 and here's our my.cnf.
[mysqld]
#defaults
basedir = /opt/mysql/server-5.6
datadir = /var/lib/mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
tmpdir = /tmp
bind-address = 0.0.0.0
#logs
log_error = /var/log/mysql/error.log
expire_logs_days = 4
slow_query_log = on
long_query_time = 1
innodb_buffer_pool_size = 11G
#http://stackoverflow.com/a/10866836/182484
collation-server = utf8_bin
init-connect ='SET NAMES utf8'
init_connect ='SET collation_connection = utf8_bin'
character-set-server = utf8
max_allowed_packet = 64M
skip-character-set-client-handshake
#cache
query_cache_size = 268435456
query_cache_type = 1
query_cache_limit = 1048576
```