L'insertion dans une grande table MySQL sans clé primaire à incrémentation automatique est très lente
P粉845862826
2023-08-29 20:31:33
<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>
Je vais prendre des risques avec cette réponse.
Hypothèse
La valeur de-
- 1 000 sélections par seconde arrivent dans des parties aléatoires de la table, puis
innodb_buffer_pool_size
est légèrement inférieure à 20 Mo, etLes systèmes sont devenus limités aux E/S ces derniers temps, car le morceau "suivant" requis pour la prochaine sélection n'est de plus en plus souvent pas mis en cache dans le buffer_pool.
La solution simple consiste à obtenir plus de RAM et à augmenter le réglage de ce paramètre. Mais la table ne s'agrandira que jusqu'à la prochaine limite que vous achetez.
Au lieu de cela, voici quelques solutions partielles.
INT UNSIGNED
(4 个字节而不是 8),甚至可能是MEDIUMINT UNSIGNED
(3 个字节) )。注意ALTER TABLE
de verrouiller le tableau pendant une longue période.DATETIME
和TIMESTAMP
Prend 5 octets (au lieu de 8 octets).Plus
Oui, c'est le cas.
L'utiliser au début de
INDEX
,或者更好的是,作为PRIMARY KEY
vous apportera la meilleure aide pour vos deux requêtes :Réponse :
S'il est utilisé pour contrôler un autre SQL, pensez à l'ajouter à l'autre SQL :
Cette requête (sous quelque forme que ce soit) nécessite du contenu que vous possédez déjà
Vos autres besoins en matière de demandes
Alors, ajoutez un index, ou ...
Mieux... Cette combinaison est meilleure pour les deux :
Avec cette combinaison,SELECT
La vérification de l'existence d'une seule ligne sera effectuée "à l'aide d'un index" car elle est "couverte".-
Une autre requête trouvera toutes les lignes associées regroupées sur PK. -
(PK a ces 3 colonnes car il doit être unique. Les avoir dans cet ordre est bon pour votre deuxième requête. C'est aussi un PK, pas seulement un INDEX, donc il n'a pas besoin d'être entre le BTree dans l'index BTree entre rebond et données) -
Le "Clustering" - peut améliorer les performances en réduisant le nombre de blocs de disque requis pour de telles requêtes. Cela réduit le « thrashing » dans le buffer_pool, réduisant ainsi le besoin d'augmenter la RAM.
Mes suggestions d'indexation sont pour la plupart orthogonales à mes suggestions de types de données. -