Pourquoi une requête d'insertion prend-elle parfois tant de temps à se terminer ?

Question

Ceci est un problème assez simple. Insérer des données dans la table fonctionne normalement très bien, sauf quelques fois où la requête d'insertion prend quelques secondes. (Je ne suis pas en train d'essayer d'insérer des données en masse.) J'ai donc mis en place une simulation du processus d'insertion pour comprendre pourquoi la requête d'insertion prend parfois plus de 2 secondes à s'exécuter. Joshua a suggéré que le fichier d'index pourrait être en train d'être ajusté ; j'ai supprimé l'identifiant (champ de clé primaire), mais le retard se produit toujours.

J'ai une table MyISAM : daniel_test_insert (cette table commence totalement vide) :

create table if not exists daniel_test_insert ( 
    id int unsigned auto_increment not null, 
    value_str varchar(255) not null default '', 
    value_int int unsigned default 0 not null, 
    primary key (id) 
)

J'insère des données dedans et parfois une requête d'insertion prend > 2 secondes à s'exécuter. Il n'y a pas de lectures sur cette table - Seulement des écritures, de façon séquentielle, par un programme à un seul thread.

J'ai exécuté exactement la même requête 100 000 fois pour comprendre pourquoi la requête prend parfois beaucoup de temps. Pour le moment, il semble que ce soit un événement aléatoire.

Cette requête par exemple a pris 4,194 secondes (un très long temps pour un insert) :

Query: INSERT INTO daniel_test_insert SET value_int=12345, value_str='afjdaldjsf aljsdfl ajsdfljadfjalsdj fajd as f' - a duré 4,194 secondes
status               | durée    | cpu_user  | cpu_system | context_volontaire | context_involontaire | fautes_de_page_mineures
démarrage            | 0,000042 | 0,000000  | 0,000000   | 0                  | 0                    | 0                
vérification des autorisations | 0,000024 | 0,000000  | 0,000000   | 0                 | 0                   | 0                
Ouverture des tables | 0,000024 | 0,001000  | 0,000000   | 0                 | 0                   | 0                
Verrou système       | 0,000022 | 0,000000  | 0,000000   | 0                 | 0                   | 0                
Verrouillage de table | 0,000020 | 0,000000  | 0,000000   | 0                 | 0                   | 0                
init                 | 0,000029 | 0,000000  | 0,000000   | 1                 | 0                   | 0                
update               | 4,067331 | 12,151152 | 5,298194   | 204894            | 18806               | 477995           
fin                  | 0,000094 | 0,000000  | 0,000000   | 8                 | 0                   | 0                
fin de la requête    | 0,000033 | 0,000000  | 0,000000   | 1                 | 0                   | 0                
libération des éléments | 0,000030 | 0,000000  | 0,000000   | 1                 | 0                   | 0                
fermeture des tables  | 0,125736 | 0,278958  | 0,072989   | 4294              | 604                 | 2301             
enregistrement de la requête lente | 0,000099 | 0,000000  | 0,000000   | 1                 | 0                   | 0                
enregistrement de la requête lente | 0,000102 | 0,000000  | 0,000000   | 7                 | 0                   | 0                
nettoyage            | 0,000035 | 0,000000  | 0,000000   | 7                 | 0                   | 0

(Ceci est une version abrégée de la commande SHOW PROFILE, j'ai supprimé les colonnes qui étaient toutes à zéro.)

Maintenant, la mise à jour a un nombre incroyable de changements de contexte et de fautes de page mineures. Le nombre de tables ouvertes augmente d'environ 1 toutes les 10 secondes sur cette base de données (nous n'épuisons pas l'espace de table_cache)

Statistiques :

• MySQL 5.0.89

• Matériel : 32 Go de RAM / 8 cœurs @ 2,66 GHz ; disques durs SCSI RAID 10 (SCSI II???)

• J'ai interrogé les disques durs et le contrôleur RAID : Aucune erreur n'est signalée. Les CPUs ont environ 50% de temps d'attente.

• iostat -x 5 (rapporte moins de 10% d'utilisation pour les disques durs) Le rapport top affiche une charge moyenne d'environ 10 pour 1 minute (normal pour notre machine de base de données)

• L'espace de swap a 156 ko utilisés (32 Go de RAM)

Je suis en train de chercher ce qui cause ce retard de performance. Cela ne se produit PAS sur nos esclaves à faible charge, seulement sur notre maître à forte charge. Cela se produit également avec des tables en mémoire et InnoDB. Est-ce que quelqu'un a des suggestions ? (C'est un système de production, donc rien d'exotique !)

Answer 1

J'ai remarqué le même phénomène sur mes systèmes. Les requêtes qui prennent normalement une milliseconde prendront soudainement 1-2 secondes. Tous mes cas sont simples, des requêtes d'INSERTION/MISE À JOUR/REMPLACEMENT sur une seule table --- pas sur des SELECT. Aucune charge, aucun verrouillage, ni accumulation de threads n'est évident.

J'avais soupçonné que cela était dû à la suppression des pages sales, à la mise en mémoire tampon des changements sur le disque, ou à un mutex caché, mais je n'ai pas encore pu le confirmer.

Aussi Exclu

• Charge du serveur -- pas de corrélation avec une charge élevée
• Moteur -- se produit avec InnoDB/MyISAM/Memory
• Cache de requêtes MySQL -- se produit que ce soit activé ou désactivé
• Rotations des journaux -- pas de corrélation dans les événements

La seule observation que j'ai actuellement est liée au fait que j'exécute la même base de données sur plusieurs machines. J'ai une application de lecture intensive donc j'utilise un environnement avec réplication -- la plupart de la charge se trouve sur les esclaves. J'ai remarqué que même s'il y a très peu de charge sur le maître, le phénomène se produit plus souvent là-bas. Même si je ne remarque aucun problème de verrouillage, peut-être que c'est Innodb/Mysql qui ont des problèmes de (thread) concurrence? Rappelez-vous que les mises à jour sur l'esclave seront en mode monothread.

Version MySQL 5.1.48

Mise à Jour

Je pense avoir trouvé une piste pour mon problème. Sur certains de mes serveurs, j'ai remarqué ce phénomène plus fréquemment que sur les autres. En examinant les différences entre les différents serveurs, et en ajustant les paramètres, j'ai été mené à la variable système innodb de MySQL innodb_flush_log_at_trx_commit.

J'ai trouvé la documentation un peu difficile à comprendre, mais innodb_flush_log_at_trx_commit peut prendre les valeurs 1,2,0 :

• Pour 1, le tampon du journal est vidé dans le fichier journal à chaque validation, et le fichier journal est vidé sur le disque à chaque validation.
• Pour 2, le tampon du journal est vidé dans le fichier journal à chaque validation, et le fichier journal est vidé sur le disque environ toutes les 1-2 secondes.
• Pour 0, le tampon du journal est vidé dans le fichier journal chaque seconde, et le fichier journal est vidé sur le disque chaque seconde.

En pratique, dans l'ordre (1,2,0), tel que rapporté et documenté, vous êtes censé obtenir une amélioration des performances en échange d'un risque accru.

Cela dit, j'ai constaté que les serveurs avec innodb_flush_log_at_trx_commit=0 fonctionnaient moins bien (c'est-à-dire qu'ils avaient de 10 à 100 fois plus de "longues mises à jour") que les serveurs avec innodb_flush_log_at_trx_commit=2. De plus, les choses se sont immédiatement améliorées sur les instances moins performantes lorsque je l'ai changé à 2 (notez que vous pouvez le modifier en cours d'exécution).

Alors, ma question est, quel est le vôtre réglé à? Notez que je ne blâme pas ce paramètre, mais je souligne plutôt que son contexte est lié à ce problème.

Answer 2

J'ai eu ce problème en utilisant des tables INNODB. (et les index INNODB sont encore plus lents à réécrire que MYISAM)

Je suppose que vous effectuez plusieurs autres requêtes sur d'autres tables, donc le problème serait que MySQL doit gérer des écritures sur disque dans des fichiers qui deviennent plus grands et a besoin d'allouer de l'espace supplémentaire à ces fichiers.

Si vous utilisez des tables MYISAM, je vous suggère fortement d'utiliser

LOAD DATA INFILE 'fichier-sur-disque' INTO TABLE `nomtable` 

commande; MYISAM est sensationnellement rapide avec ça (même avec des clés primaires) et le fichier peut être formaté en csv et vous pouvez spécifier les noms de colonnes (ou vous pouvez mettre NULL comme valeur pour le champ auto-incrémenté).

Voir la documentation MYSQL ici.

Answer 3

Pouvez-vous créer une autre table avec 400 colonnes (non nulles) et exécuter votre test à nouveau ? Si le nombre d'insertions lentes augmente, cela pourrait indiquer que MySQL perd du temps à écrire vos enregistrements. (Je ne sais pas comment cela fonctionne, mais il pourrait allouer plus de blocs ou déplacer quelque chose pour éviter la fragmentation... Vraiment je ne sais pas)

Answer 4

Le premier conseil que je vous donnerais est de désactiver la fonctionnalité d'autocommit et de commettre manuellement.

LOCK TABLES a WRITE;
... FAIRE DES INSERTIONS ICI
UNLOCK TABLES;

Cela bénéficie aux performances car le tampon d'index est vidé sur le disque une seule fois, après que toutes les déclarations INSERT ont été complétées. Normalement, il y aurait autant de vidages de tampons d'index qu'il y a de déclarations INSERT.

Mais probablement le mieux que vous puissiez faire, et si c'est possible dans votre application, c'est d'effectuer une insertion groupée avec une seule sélection.

Cela se fait via la Vector Binding et c'est la méthode la plus rapide que vous puissiez utiliser.

Au lieu de :
"INSERT INTO nomTable valeurs()"
FAIRE
"INSERT INTO nomTable valeurs(),(),(),().......(n)" ,

Mais envisagez cette option uniquement si la liaison vectorielle de paramètres est possible avec le driver MySQL que vous utilisez.

Sinon, je vous conseillerais d'opter pour la première possibilité et DE VERROUILLER la table pour chaque 1000 insertions. Ne la verrouillez pas pour 100k insertions, car vous obtiendrez un dépassement de tampon.

Answer 5

Nous avons rencontré exactement le même problème et l'avons signalé ici : http://bugs.mysql.com/bug.php?id=62381

Nous utilisons la version 5.1.52 et n'avons pas encore de solution. Nous pourrions avoir besoin de désactiver le QC pour éviter cette perte de performance.