Réglage correct innodb_log_file_size dans mysql

Nous avons couru un alter table aujourd'hui, aujourd'hui, qui a pris en bas de la DB. Nous n'avons pas réussi à l'esclave, et dans le post-mortem, nous avons découvert cela dans la base de données 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.

Cette erreur est la vérité, parce que nous avons travaillé sur une très grande table qui contient les types de données BLOB.

La meilleure réponse nous avons trouvé en ligne, a déclaré

Pour le résoudre, vous devez arrêter MySQL proprement (très important), supprimer l'existant InnoDB fichiers journaux (probablement lb_logfile* dans votre MySQL répertoire de données, sauf si vous les avez déplacés), puis réglez le innodb_log_file_size pour répondre à vos besoins, et puis commencer à MySQL de nouveau. Cet article de la base de la performance de blog pourrait être instructif.

et dans les commentaires

Oui, le serveur de bases de données de manière efficace accrocher pour les mises à jour les tables InnoDB lorsque le journal est plein. Il peut paralyser un site.

qui est je crois ce qui s'est passé sur la base de notre actuel (par défaut) innodb_log_file_size de 48 mo?

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   | ./       |
+-----------------------------+----------+

Donc, ce qui m'amène à deux questions précises et on ouvert:

  1. Comment pouvons-nous déterminer la plus grande ligne de sorte que nous pouvons définir notre innodb_log_file_size être plus grand que cela?
  2. Quelle est la conséquence de l'action à l'étape 1? J'avais lu sur long temps de récupération avec de plus grands journaux.
  3. Est-il autre chose que je devrait s'inquiéter sur les migrations, en considérant que nous avons une grande table (650k lignes, 6169.8 GO) avec effrénée, de longueur variable des champs BLOB.

Nous sommes en cours d'exécution mysql 5.6 et voici notre 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
```

Comme un suivi de la liste de suggestions ci-dessous, j'ai commencé une enquête dans le fichier de la taille de la table en question. J'ai couru un script qui écrit le combiné taille en octets des trois champs BLOB à une table appelée pen_sizes. Voici le résultat de la plus grande taille en octets:

select pen_size as bytes
pen_size  /  1024 / 1024 as mb
pen_id from pen_sizes
group by pen_id
order by bytes desc
limit 40
+---------+------------+--------+
| bytes   | mb         | pen_id |
+---------+------------+--------+
| 3542620 | 3.37850571 |  84816 |
| 3379107 | 3.22256756 |  74796 |
| 3019237 | 2.87936878 | 569726 |
| 3019237 | 2.87936878 | 576506 |
| 3019237 | 2.87936878 | 576507 |
| 2703177 | 2.57795048 | 346965 |
| 2703177 | 2.57795048 | 346964 |
| 2703177 | 2.57795048 |  93706 |
| 2064807 | 1.96915340 | 154627 |
| 2048592 | 1.95368958 | 237514 |
| 2000695 | 1.90801144 |  46798 |
| 1843034 | 1.75765419 | 231988 |
| 1843024 | 1.75764465 | 230423 |
| 1820514 | 1.73617744 |  76745 |
| 1795494 | 1.71231651 | 650208 |
| 1785353 | 1.70264530 |  74912 |
| 1754059 | 1.67280102 | 444932 |
| 1752609 | 1.67141819 |  76607 |
| 1711492 | 1.63220596 | 224574 |
| 1632405 | 1.55678272 |  76188 |
| 1500157 | 1.43066120 |  77256 |
| 1494572 | 1.42533493 | 137184 |
| 1478692 | 1.41019058 | 238547 |
| 1456973 | 1.38947773 | 181379 |
| 1433240 | 1.36684418 |  77631 |
| 1421452 | 1.35560226 | 102930 |
| 1383872 | 1.31976318 |  77627 |
| 1359317 | 1.29634571 | 454109 |
| 1355701 | 1.29289722 | 631811 |
| 1343621 | 1.28137684 |  75256 |
| 1343621 | 1.28137684 |  75257 |
| 1334071 | 1.27226925 |  77626 |
| 1327063 | 1.26558590 | 129731 |
| 1320627 | 1.25944805 | 636914 |
| 1231918 | 1.17484856 | 117269 |
| 1223975 | 1.16727352 |  75103 |
| 1220233 | 1.16370487 | 326462 |
| 1220233 | 1.16370487 | 326463 |
| 1203432 | 1.14768219 | 183967 |
| 1200373 | 1.14476490 | 420360 |
+---------+------------+--------+

Ce qui me fait croire que la taille moyenne de ligne est plus proche de 1 mo que les 10 proposés. Peut-être que la taille de la table que j'ai énumérés plus tôt comprend les indices, trop?

J'ai couru

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = 'codepen'
+-------------------+------------+
| Tables            | Size in MB |
+-------------------+------------+
...snip
| pens              |    6287.89 |
...snip

OriginalL'auteur timsabat | 2013-09-14