Comment améliorer la performance de l'INSERT sur une très grande table MySQL
Je suis en train de travailler sur une grande base de données MySQL et j'ai besoin d'améliorer la performance de l'INSERT sur une table spécifique. Celui-ci contient environ 200 Millions de lignes et de sa structure est comme suit:
(un peu le principe: je ne suis pas un expert base de données, donc le code que j'ai écrit, peut être fondée sur de mauvaises fondations. Merci de m'aider à comprendre mes erreurs 🙂 )
CREATE TABLE IF NOT EXISTS items (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
key VARCHAR(10) NOT NULL,
busy TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY (id, name),
UNIQUE KEY name_key_unique_key (name, key),
INDEX name_index (name)
) ENGINE=MyISAM
PARTITION BY LINEAR KEY(name)
PARTITIONS 25;
Chaque jour, je reçois de nombreux fichiers csv dans lequel chaque ligne est composée par la paire "nom;la clé", j'ai donc à analyser ces fichiers (ajout de valeurs created_at et updated_at pour chaque ligne) et insérer les valeurs dans mon tableau. Dans celui-ci, la combinaison "nom" et "clé" DOIT être UNIQUE, donc j'ai mis en place l'insert de la procédure comme suit:
CREATE TEMPORARY TABLE temp_items (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
key VARCHAR(10) NOT NULL,
busy TINYINT(1) NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
PRIMARY KEY (id)
)
ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'file_to_process.csv'
INTO TABLE temp_items
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
(name, key, created_at, updated_at);
INSERT INTO items (name, key, busy, created_at, updated_at)
(
SELECT temp_items.name, temp_items.key, temp_items.busy, temp_items.created_at, temp_items.updated_at
FROM temp_items
)
ON DUPLICATE KEY UPDATE busy=1, updated_at=NOW();
DROP TEMPORARY TABLE temp_items;
Le code ci-dessus me permet d'atteindre mon but, mais, pour terminer l'exécution, il emploie environ 48 heures, ce qui est un problème.
Je pense que cette mauvaise performance sont causés par le fait que le script doit vérifier sur une très grande table (200 Millions de lignes) et pour chaque insertion que la paire "nom;la clé est unique.
Comment puis-je améliorer les performances de mon script?
Merci à tous à l'avance.
Vous devez vous connecter pour publier un commentaire.
Votre linéaire clé sur le nom et les grands indices ralentit les choses.
LINÉAIRE doit être calculé chaque insertion.
http://dev.mysql.com/doc/refman/5.1/en/partitioning-linear-hash.html
pouvez-vous nous montrer quelques exemples de données de file_to_process.csv peut-être une meilleure schéma doit être construire.
Modifier regardé de plus près
ce sera arrivions permettra de créer un disque de la table temporaire, c'est très très lent, de sorte que vous ne devriez pas l'utiliser pour obtenir plus de performance ou peut-être vous devriez vérifier certains paramètres de configuration de mysql comme tmp-tableau de taille et max-heap-tableau de taille peut-être que ces sont mal configurés.
Vous pouvez utiliser les méthodes suivantes pour accélérer les insertions:
Si vous insérez le nombre de lignes à partir du même client dans le même temps, l'utilisation des instructions INSERT avec plusieurs listes de VALEURS à insérer plusieurs lignes à la fois. C'est beaucoup plus rapide (plusieurs fois plus rapide dans certains cas) qu'à l'aide d'une ligne unique de l'INSERT. Si vous ajoutez des données dans un tableau non vide, vous pouvez régler la bulk_insert_buffer_size variable pour rendre les données d'insertion encore plus vite.
Lors du chargement d'une table à partir d'un fichier texte, utilisez les commandes LOAD DATA INFILE. C'est généralement de 20 fois plus rapide que l'utilisation des instructions INSERT.
Profiter du fait que les colonnes ont des valeurs par défaut. Insérer des valeurs explicitement uniquement lorsque la valeur insérée diffère de la valeur par défaut. Cela réduit l'analyse que MySQL doit faire et améliore l'insertion de la vitesse.
Il y a un morceau de la documentation, je tiens à souligner, La vitesse de l'INSERT.
Vous pouvez utiliser
etc...
La
REPLACE
de s'assurer que tous les doublons de valeur est remplacée par les nouvelles valeurs.Ajouter un
SET updated_at=now()
à la fin et vous avez terminé.Il n'est pas nécessaire pour la table temporaire.