L'insertion dans une grande table MySQL sans clé primaire à incrémentation automatique est très lente-Questions et réponses sur le réseau chinois PHP

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. 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. Voici le tableau que j'utilise : <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> 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. 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. 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 : <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é> Voici le résultat de l'instruction EXPLAIN pour la requête ci-dessus : <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> Ces problèmes sont relativement nouveaux et n’étaient pas visibles lorsque la table était environ deux fois plus petite. 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. 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 ! Modifier : J'aborderai certains des points et questions soulevés dans les commentaires et les réponses : <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> 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. 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. 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> <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>

répondre à tous(1)

P粉0221405762023-08-30 00:15:37 1 plancher

Je vais prendre des risques avec cette réponse.

Hypothèse

innodb_buffer_pool_size est légèrement inférieure à 20 Mo, et
1 000 sélections par seconde arrivent dans des parties aléatoires de la table, puis

Les 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.

Si les nombres ne sont pas trop grands, les deux premières colonnes risquent INT UNSIGNED（4 个字节而不是 8），甚至可能是 MEDIUMINT UNSIGNED（3 个字节））。注意 ALTER TABLE de verrouiller le tableau pendant une longue période.
Ces heures de début et de fin ressemblent à des horodatages avec des fractions de seconde et sont toujours ".000". DATETIME 和 TIMESTAMP Prend 5 octets (au lieu de 8 octets).
Votre exemple montre un temps écoulé de 0. Si (end-start) est généralement très petit, le stockage du temps écoulé au lieu de l'heure de fin réduira encore davantage les données. (Mais utiliser une heure de fin peut rendre les choses confuses).
Les exemples de données que vous avez fournis semblent "continus". C'est à peu près aussi efficace que l'auto-incrémentation. Est-ce la norme ? Dans le cas contraire, l'INSERT peut faire partie du battage des E/S.
Vous suggérez d'ajouter de l'intelligence artificielle ainsi que des index secondaires, ce qui double le travail d'insertion donc je ne le recommande pas ;

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 :

(recording_id, index)

Réponse :

SELECT  TRUE
FROM    saved_segment
WHERE   recording_id = ? AND `index` = ?

S'il est utilisé pour contrôler un autre SQL, pensez à l'ajouter à l'autre SQL :

... EXISTS ( SELECT 1
        FROM    saved_segment
        WHERE   recording_id = ? AND `index` = ? ) ...

Cette requête (sous quelque forme que ce soit) nécessite du contenu que vous possédez déjà

PRIMARY KEY(recording_id, index)

Vos autres besoins en matière de demandes

INDEX(recording_id, start_filetime)

Alors, ajoutez un index, ou ...

Mieux... Cette combinaison est meilleure pour les deux : SELECT

PRIMARY KEY(recording_id, start_filetime, index).
INDEX(recording_id, index)

Avec cette combinaison,

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.