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:
- 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? - 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.
- 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
Vous devez vous connecter pour publier un commentaire.
0. Informations préliminaires
Vos paramètres:
Donc votre "journal de la capacité du groupe" = 2 x 50331648 = 96 MO
1. Comment faire pour déterminer la plus grande ligne
Il n'existe pas de méthode directe. Mais on peut facilement calculer la taille d'une ligne donnée sur la base de ces tables (compression ne devrait pas question pour nous ici, si, comme je le suppose, les lignes ne sont pas compressés dans des fichiers journaux).
2. L'Impact de
innodb_log_file_size
Manuel de référence:
3. Rien d'autre à vous soucier de
6169.8 GO /650k rows = environ 10 MO par ligne en moyenne
C'est un grave problème en soi, si vous avez l'intention d'utiliser votre base de données transactionnelle, la situation multi-utilisateur. Pensez à stocker votre
BLOB
's que les fichiers à l'extérieur de la base de données. Ou, au moins, de les stocker dans un autre MyISAM (non-transactionnelle) de la table.innodb_log_file_size
est réglé? J'ai posté ma toute ma.cnf. La valeur par défaut est 2x5...Votre serveur peut être fait à l'aide d'un fichier de configuration différent (voir ici pour les emplacements possibles).
pour la postérité, la valeur par défaut pour
innodb_log_file_size
est 48mo, pas 2x5.Ce qui a changé en v5.6.8. Je ne connaissais pas, merci pour le heads up.
OriginalL'auteur RandomSeed