L'insertion dans une grande table MySQL sans clé primaire à incrémentation automatique est très lente
<p>J'ai récemment remarqué une augmentation significative de la différence de temps nécessaire pour compléter une simple instruction INSERT. Bien que ces instructions prennent environ 11 millisecondes en moyenne, elles peuvent parfois prendre 10 à 30 secondes, et j'ai même remarqué qu'elles prenaient plus de 5 minutes à s'exécuter. </p>
<p>La version MySQL est <code>8.0.24</code>, exécutée sur Windows Server 2016. À ma connaissance, les ressources du serveur n'ont jamais été surchargées. Le serveur dispose d’une surcharge CPU importante et se voit attribuer 32 Go de RAM. </p>
<p>Voici le tableau que j'utilise : </p>
<pre class="brush:php;toolbar:false;">CREATE TABLE `saved_segment` (
`recording_id` bigint non signé NOT NULL,
`index` bigint non signé NOT NULL,
`start_filetime` bigint non signé NOT NULL,
`end_filetime` bigint non signé NOT NULL,
`offset_and_size` bigint non signé NOT NULL PAR DÉFAUT '18446744073709551615',
`storage_id` tinyint non signé NON NULL,
CLÉ PRIMAIRE (`recording_id`,`index`)
) MOTEUR=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</pre>
<p>Cette table n’a pas d’autres index ou clés étrangères et n’est utilisée comme référence aux clés étrangères dans aucune autre table. La taille totale de la table est d'environ 20 Go et le nombre de lignes est d'environ 281 Mo, ce qui, à mon avis, n'est pas trop grand. </p>
<p>La table est utilisée presque entièrement en mode lecture seule, avec jusqu'à 1 000 lectures par seconde. Toutes ces lectures se produisent dans des requêtes SELECT simples plutôt que dans des transactions complexes, et elles utilisent efficacement l'index de clé primaire. Il y a très peu, voire aucune, d'écritures simultanées dans cette table. Cela a été fait exprès pour essayer de déterminer si cela aiderait à une insertion lente, mais ce n'est pas le cas. En attendant, il y a toujours jusqu'à 10 insertions simultanées en cours. Les instructions UPDATE ou DELETE ne seront jamais exécutées sur cette table. </p>
<p>Les requêtes avec lesquelles j'ai des problèmes sont toutes construites de cette façon. Ils n'apparaissent jamais dans la transaction.Bien que les insertions basées sur la clé primaire clusterisée ne soient certainement pas des ajouts uniquement, la requête insérera presque toujours entre 1 et 20 lignes adjacentes dans la table : </p>
<pre class="brush:php;toolbar:false;">INSÉRER IGNORER DANS le segment_sauvé
(recording_id, `index`, start_filetime, end_filetime, offset_and_size, storage_id) VALEURS
(19173, 631609, 133121662986640000, 133121663016640000, 20562291758298876, 10),
(19173, 631610, 133121663016640000, 133121663046640000, 20574308942546216, 10),
(19173, 631611, 133121663046640000, 133121663076640000, 20585348350688128, 10),
(19173, 631612, 133121663076640000, 133121663106640000, 20596854568114720, 10),
(19173, 631613, 133121663106640000, 133121663136640000, 20609723363860884, 10),
(19173, 631614, 133121663136640000, 133121663166640000, 20622106425668780, 10),
(19173, 631615, 133121663166640000, 133121663196640000, 20634653501528448, 10),
(19173, 631616, 133121663196640000, 133121663226640000, 20646967172721148, 10),
(19173, 631617, 133121663226640000, 133121663256640000, 20657773176227488, 10),
(19173, 631618, 133121663256640000, 133121663286640000, 20668825200822108, 10)</pré>
<p>Voici le résultat de l'instruction EXPLAIN pour la requête ci-dessus : </p>
<table class="s-table">
<tête>
<tr>
<th>id</th>
<th>Sélectionnez le type</th>
<th>Table</th>
<th>Partition</th>
<th>Type</th>
<th>Touches possibles</th>
<th>touche</th>
<th>key_len</th>
<th>Référence</th>
<th>OK</th>
<th>Filtré</th>
<th>Extra</th>
≪/tr>
≪/tête>
<corps>
<tr>
<td>1</td>
<td>Insérer</td>
<td>Segments enregistrés</td>
<td>Vide</td>
<td>Tous</td>
<td>Vide</td>
<td>Vide</td>
<td>Vide</td>
<td>Vide</td>
<td>Vide</td>
<td>Vide</td>
<td>Vide</td>
≪/tr>
</tcorps>
</tableau>
<p>Ces problèmes sont relativement nouveaux et n’étaient pas visibles lorsque la table était environ deux fois plus petite. </p>
<p>J'ai essayé de réduire le nombre d'insertions simultanées dans le tableau d'environ 10 à 1. J'ai également supprimé les clés étrangères (<code>recording_id</code>) sur certaines colonnes pour accélérer encore plus les insertions. <code>L'analyse des tables</code> et l'analyse des schémas n'ont donné aucune information exploitable.</p>
<p>Une solution à laquelle j'ai pensé était de supprimer la clé primaire clusterisée et d'ajouter une clé primaire à incrémentation automatique et un index régulier sur la colonne <code>(recording_id, index)</code> À mon avis, cela aiderait à rendre l'insertion "en ajout uniquement".Je suis ouvert à toutes suggestions, merci d'avance ! </p>
<p>Modifier :
J'aborderai certains des points et questions soulevés dans les commentaires et les réponses : </p>
<ul>
<li><code>autocommit</code> est défini sur <code>ON</code></li>
La valeur de <li><code>innodb_buffer_pool_size</code> est <code>21474836480</code>, et la valeur de <code>innodb_buffer_pool_chunk_size</code> < ;/code> ≪/ li>
<li>Un commentaire a soulevé des inquiétudes concernant les conflits entre le verrou de lecture utilisé pour les lectures et le verrou exclusif utilisé pour les écritures. La table est utilisée un peu comme un cache, je n'ai pas besoin de lire pour toujours refléter le dernier état de la table si cela signifie une augmentation des performances. Cependant, la table doit rester durable même en cas de panne de serveur et de panne matérielle. Cela pourrait-il être réalisé avec un niveau d’isolement des transactions plus détendu ? ≪/li>
<li>L'architecture peut certainement être optimisée ; <code>recording_id</code> peut être un entier de 4 octets, <code>end_filetime</code> start_filetime</code> ; peut également être plus petit. Je crains que ces changements ne fassent que reporter le problème pendant un certain temps jusqu'à ce que la taille de la table augmente pour compenser l'espace économisé. ≪/li>
<li>Les insertions dans le tableau sont toujours continues
Un SELECT effectué sur la table ressemble à ceci : </li>
</ul>
<pre class="brush:php;toolbar:false;">SELECT TRUE
DE segment_enregistré
OÙ enregistrement_id = ? ET `index` = ?</pre>
<pre class="brush:php;toolbar:false;">SELECT index, start_filetime, end_filetime, offset_and_size, storage_id
DE segment_enregistré
OÙ enregistrement_id = ?
start_filetime >= ET ?
start_filetime <= ?
ORDER BY `index` ASC</pre>
<p>Le deuxième type de requête pourrait certainement être amélioré avec un index, mais je crains que cela ne dégrade davantage les performances d'INSERT. </p>
<p>Une autre chose que j'ai oublié de mentionner est qu'il existe un tableau très similaire à celui-ci. Il interroge et insère exactement la même chose, mais peut provoquer une pénurie supplémentaire d'E/S. </p>
<p>Modifier 2 :
<code>SHOW TABLE STATUS</code> les résultats de la table <code>saved_segment</code>, et une table très similaire <code>saved_screenshot</code> Il existe un index supplémentaire sur la colonne null</code></p>
<table class="s-table">
<tête>
<tr>
<th>Nom</th>
<th>Moteur</th>
<th>Version</th>
<th>Format de ligne</th>
<th>OK</th>
<th>Longueur moyenne de ligne</th>
<th>Longueur des données</th>
<th>Longueur maximale des données</th>
<th>Index_length</th>
<th>Aucune donnée</th>
<th>Incrémentation automatique</th>
<th>Temps de création</th>
<th>Mise à jour</th>
<th>Vérifier l'heure</th>
<th>Organisation</th>
<th>Somme de contrôle</th>
<th>Options de création</th>
<th>Commentaires</th>
≪/tr>
≪/tête>
<corps>
<tr>
<td>Capture d'écran enregistrée</td>
<td>InnoDB</td>
<td>10</td>
<td>Actualités</td>
<td>483430208</td>
<td>61</td>
<td>29780606976</td>
<td>0</td>
<td>21380464640</td>
<td>6291456</td>
<td>Vide</td>
<td>«2021-10-21 01:03:21»</td>
<td>«2022-11-07 16:51:45»</td>
<td>Vide</td>
<td>utf8mb4_0900_ai_ci</td>
<td>Vide</td>
<td></td>
<td></td>
≪/tr>
<tr>
<td>Segments enregistrés</td>
<td>InnoDB</td>
<td>10</td>
<td>Actualités</td>
<td>281861164</td>
<td>73</td>
<td>20802699264</td>
<td>0</td>
<td>0</td>
<td>4194304</td>
<td>Vide</td>
<td>«2022-11-02 09:03:05»</td>
<td>« 2022-11-07 16:51:22 »</td>
<td>Vide</td>
<td>utf8mb4_0900_ai_ci</td>
<td>Vide</td>
<td></td>
<td></td>
≪/tr>
</tcorps>
</table></p>
