Comment puis-je améliorer mon instruction INSERT de performance?

Tandis que Josh réponse ici m'a donné une bonne longueur d'avance sur la façon d'insérer une 256x64x250 tableau de valeur dans une base de données MySQL. Quand j'ai essayé de son instruction INSERT sur mes données, il s'est avéré horriblement lent (comme dans 6 minutes pour un 16 mo de fichier).

ny, nx, nz = np.shape(data)
query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s)"""
for frames in range(nz):
    for rows in range(ny):
        for cols in range(nx):
            cursor.execute(query, (frames, rows, cols, data[rows,cols,frames]))

Je lisais MySQL pour Python, qui a expliqué que ce n'était pas la bonne approche, car l'exécution de 4 millions de séparer les plaquettes est très inefficace.

Maintenant mes données se composent de beaucoup de zéros (plus de 90% en fait), donc je l'ai jeté dans une instruction if donc, je n'insérez des valeurs supérieures à zéro et j'ai utilisé executemany() à la place:

query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
values = []
for frames in range(nz):
    for rows in range(ny):
        for cols in range(nx):
            if data[rows,cols,frames] > 0.0:
                values.append((frames, rows, cols, data[rows,cols,frames]))           
cur.executemany(query, values)

Ce miraculeusement apporté mon temps de traitement jusqu'à environ 20 secondes, dont 14 secondes passent sur la création de la liste de valeurs (37k lignes) et 4 secondes sur la réelle insertion dans la base de données.

Alors maintenant, je me demandais, comment puis-je accélérer ce processus davantage? Parce que j'ai le sentiment que ma boucle est horriblement inefficace et il y a une meilleure façon. Si j'ai besoin d'insérer des 30 mesures par chien, ce serait encore prendre 10 minutes, ce qui semble beaucoup trop long pour cette quantité de données.

Voici deux versions de mes fichiers raw: avec les en-têtes ou sans les en-têtes. J'aimerais essayer de le LOAD DATA INFILE, mais je ne peux pas comprendre comment analyser correctement les données.

D'où viennent les données?
C'est un gros fichier texte avec des en-têtes au-dessus de chaque cadre, qu'est-ce précisément que vous voulez savoir?
Je suis juste curieux de savoir si le long processus de la prise de est de la lecture du fichier ou les boucles for imbriquées pour créer la requête.
Qu'est-ce que data? Peut-être que vous pouvez obtenir values par l'application d'une fonction sur data? Cela pourrait vous donner des principaux speedup.
de données est un tableau numpy, qui est chargé dans la mémoire, donc je suppose qu'il s'agit de boucles imbriquées. Si il y a un autre moyen de récupérer toutes les non-valeurs zéro avec leurs index, alors je suis tout ouïe
Gardez à l'esprit que le plus de [INSÉRER] les opérations sont effectuées, la probabilité de performances seront un sujet de préoccupation. Les bases de données d'écriture sur le disque - alors qu'il y a un tampon, il peut être épuisé. Aussi, vous voudrez peut-être regarder dans le MySQL LOAD DATA INFILE pour le chargement de fichiers, mais je ne pense pas qu'il prend en charge la logique de décision que vous utilisez pour lancer des données (pourquoi est-il connecté en premier lieu?).
les données sont exportées à partir d'un autre logiciel, donc je n'ai pas d'influence sur le format. Je peux imaginer que le chargement d'un fichier directement dans la base de données serait encore plus vite, mais je ne suis pas sûr de savoir comment assurez-vous qu'il a le bon format.
J'ai ajouté une modification à ma réponse originale à cette question qui pourrait être d'intérêt.
numpy.nonzero()

InformationsquelleAutor Ivo Flipse | 2011-03-27

mysql python

5

Si les données est un tableau numpy, vous pouvez essayer ceci:
```
query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
values = []
rows, cols, frames = numpy.nonzero(data)
for row, col, frame in zip(rows, cols, frames):
    values.append((frame, row, col, data[row,col,frame]))

cur.executemany(query, values)
```
ou
```
query = """INSERT INTO `data` (frame, sensor_row, sensor_col, value) VALUES (%s, %s, %s, %s ) """
rows, cols, frames = numpy.nonzero(data)
values = [(row, col, frame, val) for row, col, frame, val in zip(rows, cols, frames, data[rows,cols,frames])]
cur.executemany(query, values)
```
Espère que cela aide
- Je pense qu'il ya quelque chose de mal avec la compréhension de liste, mais votre première boucle est extrêmement rapide: 0.34 secondes
- Quelle erreur avez-vous? Êtes-vous en cours d'exécution aussi les lignes, les cols, les cadres = numpy.différent de zéro(les données)? J'ai édité le post pour le rendre clair?
- Pas de qui l'explique 😉 Il est même légèrement plus rapide!
- Juste par curiosité, pouvez-vous essayer (avis de l' ;, je ne peux pas mettre de nouvelles lignes dans les commentaires): ndx = numpy.différent de zéro(données); valeurs = numpy.hstack((numpy.transpose(ndx), les données[ndx].remodeler((-1, 1)))); cur.executemany(requête, valeurs) ou ndx = numpy.différent de zéro(données); valeurs = numpy.hstack((numpy.transpose(ndx), les données[ndx].remodeler((-1, 1)))); cur.executemany(requête, des valeurs.tolist())
- Il a besoin de valeurs.tolist(), mais cela finit le tout dans 3.65 secs. La plupart du temps est passé sur le executemany. Si bien sûr c'est beaucoup moins lisible
InformationsquelleAutor Hernan
6

le moyen le plus rapide pour insérer 4 millions de lignes (16 mo de données) serait d'utiliser load data infile - http://dev.mysql.com/doc/refman/5.0/en/load-data.html

donc si possible de générer un fichier csv, puis utilisez les commandes load data infile..

espère que cela aide 🙂

MODIFIER

J'ai donc pris un de vos fichiers de données d'origine pente.dat et a écrit un rapide et sale programme pour convertir les suivantes format csv.

Télécharger les images.dat à partir d'ici: http://rapidshare.com/files/454896698/frames.dat

Images.dat
```
patient_name, sample_date dd/mm/yyyy, frame_time (ms), frame 0..248, row 0..255, col 0..62, value
"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,5,39,0.4
"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,5,40,0.4
...
"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,10,42,0.4
"Krulle (opnieuw) Krupp",04/03/2010,0.00,0,10,43,0.4
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,4,40,0.4
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,5,39,0.4
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,5,40,0.7
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,6,44,0.7
"Krulle (opnieuw) Krupp",04/03/2010,7.94,1,6,45,0.4
...
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,10,0.4
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,11,0.4
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,12,1.1
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,13,1.4
"Krulle (opnieuw) Krupp",04/03/2010,1968.25,248,241,14,0.4
```
Le fichier contient des données uniquement pour les images qui ont des valeurs de chaque ligne et le col - de sorte que les zéros sont exclus. 24799 lignes de données ont été générées à partir de votre fichier d'origine.

Ensuite, j'ai créé un temporaire de chargement (mise en scène) de la table dans laquelle les cadres.dat fichier est chargé. C'est une table temporaire qui vous permettra de manipuler/transformer les données avant de les charger dans la bonne production/tableaux de présentation.
```
drop table if exists sample_temp;
create table sample_temp
(
patient_name varchar(255) not null,
sample_date date,
frame_time decimal(6,2) not null default 0,
frame_id tinyint unsigned not null,
row_id tinyint unsigned not null,
col_id tinyint unsigned not null,
value decimal(4,1) not null default 0,
primary key (frame_id, row_id, col_id)
)
engine=innodb;
```
Tout ce qui reste est pour charger les données (remarque: je suis à l'aide de windows de sorte que vous aurez à modifier ce script pour le rendre linux compatible - vérifier les chemins d'accès et le changement "\r\n' à '\n')
```
truncate table sample_temp;

start transaction;

load data infile 'c:\\import\\frames.dat' 
into table sample_temp
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\r\n'
ignore 1 lines
(
patient_name,
@sample_date,
frame_time,
frame_id,
row_id,
col_id,
value
)
set 
sample_date = str_to_date(@sample_date,'%d/%m/%Y');

commit;

Query OK, 24799 rows affected (1.87 sec)
Records: 24799  Deleted: 0  Skipped: 0  Warnings: 0
```
24K lignes ont été chargés de 1,87 secondes.

Espère que cela aide 🙂
- Il ne fait que travailler sur CSV ou également sur les fichiers délimités par des tabulations?
- MySQL permet de régler la delineators sont.
- comment puis-je comprendre ce que les colonnes j'ai besoin d'attribuer à lire correctement le fichier? Ou aurais-je à analyser les "brutes" fichier Python pour supprimer les en-têtes et tel?
- J'ai lu cette page, mais pour la vie de moi je ne peux pas comprendre comment analyser correctement les données. Soin d'élaborer sur elle, car elle permettrait de supprimer certains redondant étapes
- je serais heureux de vous aider, mais vous pouvez poster un peu plus d'infos à propos de ce que vous faites avec les données une fois chargée, car il permettra de déterminer l'orientation de ma conception. L'affichage quelconque tableau les définitions que vous avez, nombre de patients, la fréquence des analyses/mesures, typique de requêtes, etc etc...
- désolé d'avoir pris autant de temps, mais j'ai décidé d'envelopper le complément d'info dans un post de blog.. Si vous avez besoin d'informations spécifiques, laissez-moi savoir.
- Merci pour la réponse intéressante @f00, mais ne serait pas la conversion de .dat ou tout fichier CSV créer autant de surcharge comme votre très rapide solution permet de résoudre?
- pour l'exemple de fichiers de données que vous avez fournies (pente) de la conversion, écrit en C# dans mon cas, a pris quelques millisecondes.
- Hmmm, dans ce cas, je pourrais toujours regarder dans un outil de traitement par lot pour transformer ces fichiers @f00. De toute façon, merci pour la mise à jour j'aime la façon dont il est rapide!
- Pas de probs, mais c'est seulement le début des choses, vous aurez à examiner la façon de transformer les données dans le sample_temp table pour mieux répondre à vos reporting/analyse des exigences. Tableaux de synthèse (matérialisé points de vue), l'utilisation de innodb cluster primaire clés pour optimiser les requêtes et beaucoup, beaucoup plus de considérations... de toute façon, toujours heureux de vous aider si vous êtes dans le besoin de conseils 🙂
InformationsquelleAutor Jon Black
1

Je n'utilise pas de Python ou de mySQL, mais de lot de la performance de l'insert peut souvent être accéléré avec des de transactions.
- J'ai du mal à penser à comment ajouter des opérations dans le mélange d'améliorer les performances...
- Pourquoi je mérite un downvote parce que vous arrive d'être mal à penser?
- Je suis sûr que le downvote n'est pas Marc. (Ce n'était pas moi, non plus.) J'ai peut-être tort, mais je suppose que c'était ceejayoz, qui est le seul participant dans ce fil de discussion qui a enregistré toutes les downvotes aujourd'hui. Je déteste anonyme downvotes; l'électeur doit au moins donner à l'affiche certaines informations au sujet de ce qui est mauvais.
- Je n'ai pas downvote vous, mais par rapport à d'autres réponses, je ne vois vraiment pas comment il m'a aidé non plus. Si seulement parce que je sais si peu de la base de données que je ne sais même pas ce que tu veux dire avec "les transactions" dans ce contexte
- plutôt que de se concentrer sur le downvote, il pourrait être une meilleure option pour expliquer comment une transaction (généralement ajoute une petite quantité de surcharge pour le processus de la transaction, et peut-être quelques blocks) permettrait d'améliorer les performances ici.
- L'OP a mentionné 30 points de données (colonnes) et éventuellement dans sa rangée. Mon hypothèse était que certains de ces colonnes seraient indexés. Une "performance technique" pour un lot de plaquettes est de désactiver temporairement l'indexation et le ré-activer l'index par la suite. Deuxième hypothèse: l'enveloppant d'un lot insérer dans une transaction se reporter à l'index-écrit jusqu'à ce que après l'explicite s'engager, plutôt que d'avoir l'index-écriture qui se passe coïncide avec la prochaine itération de l'insertion, comme ce serait le cas lorsque les lignes sont insérées dans une boucle serrée, avec un commit implicite à chaque itération.
- eh bien, je ne suis pas sûr de 30 points de données va faire beaucoup de différence, mais un bon contenu. Vous devez déplacer que dans la réponse. +1 ici (et les mathématiques devrait vous dire le -1 n'est pas moi)
InformationsquelleAutor Tim
0

Si je comprends bien, executemany() exécute une requête INSERT INTO pour chaque ligne que vous souhaitez insérer. Cela peut être amélioré par la création d'une seule requête d'INSERTION avec toutes les valeurs, qui devrait ressembler à ceci:
```
INSERT INTO data
  (frame, sensor_row, sensor_col, value)
VALUES
 (1, 1, 1, 1),
 (2, 2, 2, 2),
 (3, 3, 3, 3),
 ...
```
Votre code python devrait générer les valeurs de ligne de crochets et de créer une chaîne de requête pour enfin exécuter la requête une fois.
- C'est ce que cette ligne n' @Alp: values.append((frames, rows, cols, data[rows,cols,frames])) Le problème est, il est horriblement lent pour composer une liste de ce type et je n'ai aucune idée de comment l'optimiser
- Peut-être que je n'ai pas l'obtenir, mais en voyant ton code, je pense que les requêtes suivantes seront exécutés: INSERT INTO données (image, sensor_row, sensor_col, valeur) des VALEURS (1, 1, 1, 1);, INSERT INTO données (image, sensor_row, sensor_col, valeur) des VALEURS (2, 2, 2, 2); et ainsi de suite. Corrigez-moi si je me trompe.
- print(requête, valeurs) renvoie ('INSERT INTO de données` (cadre, sensor_row, sensor_col, valeur) VALUES (%s, %s, %s, %s ) ', [(0, 31, 45, 0.40000001), (0, 31, 46, 0.40000001), (0, 32, 45, 0.40000001),`
- Ok, alors, considérer ma réponse inutile.
InformationsquelleAutor Alp
0

Insertion de plusieurs lignes sur chaque instruction est un moyen d'optimiser. Cependant, pourquoi la nécessité pour les 3 boucles? Peut-être une sorte de transformation de données pourrait être utile à la place.

Une autre option est de désactiver des index lors de l'insertion, si vous êtes certain que vous n'aurez pas de dupliquer les données (en supposant que vous avez réellement index sur la table). Les index doivent être mis à jour pour chaque instruction, et également vérifié pour éviter les doublons.

Invoquer ALTER TABLE tablename DISABLE KEYS avant de commencer vos inserts, et quand c'est fini invoquer ALTER TABLE tablename ENABLE KEYS et voir si ça aide

À partir de la notice:

ALTER TABLE ... DISABLE KEYS dit à MySQL pour arrêter la mise à jour des index non unique. ALTER TABLE ... PERMETTRE à TOUCHES, puis devrait être utilisé pour re-créer des index manquants. MySQL fait avec un algorithme spécial qui est beaucoup plus rapide que l'insertion de clés une par une, si la désactivation des touches avant d'effectuer les opérations bulk insert doit donner une considérable accélération. À l'aide de l'instruction ALTER TABLE ... DÉSACTIVER les TOUCHES nécessite l'INDICE de privilège en plus des avantages mentionnés précédemment.
- Je suppose que je suis en train de faire quelque chose de mal, parce que j'ai essayé cur.execute("""ALTER TABLE data ENABLE KEYS""") et a obtenu Table de moteur de stockage pour les "données" ne dispose pas de cette option Donc je suppose que je suis en train de faire quelque chose de mal.
- Hm, peut-être qu'il ne fonctionne que pour les tables MyISAM. Qu'utilisez-vous?
- Je l'ai eu sur InnoDB (paramètre par défaut dans HeidiSQL), mais de la modifier à MyISAM donne la même erreur. Je suppose que ma conception de tableau ne correspond pas au format de données
- Ouais, il ne fonctionne pas avec innoDB, mais doit être fine avec MyISAM, c'est étrange.
- Cependant, InnoDB est assez lent pour la non-concordance des opérations, le fait d'avoir plusieurs threads/connexion de l'insertion de données dans ce cas serait probablement plus rapide, mais si MyISAM est une option que vous pourriez veux essayer. Désactiver les touches doivent travailler avec MyISAM.
- J'ai exécuté le script, une couple de fois avec vos lignes et n'avais pas remarqué que ça n'a pas donner le message d'erreur lorsque je l'avais mis sur MyISAM, donc oui, c'était le travail. Cependant, de cette façon, il a pris 6,4 secondes alors que sur InnoDB, qui ne le supporte pas, il faut seulement 5,8 secondes
InformationsquelleAutor jishi
-1

Vous pourriez employer liste comprehenshions au lieu de for boucles:
```
values = [(frames, rows, cols, data[rows,cols,frames]) \
        for frames in range(nz) for rows in range(ny) \
        for cols in range(nx) if data[rows,cols,frames] > 0.0]           
```
Je l'estiment que cela pourrait vous donner une légère vitesse, telles que 10 à 20%.
- À partir de l'essayer, il ne semble pas améliorer le processus à tous. Je vais essayer deux ou trois fois plus.
- J'ai essayé plusieurs fois, ma boucle ou la compréhension de liste à la fois près de 14 secondes. Le executemany partie "seulement" 4 secondes
- Désolé, essayé de les aider:).
- Je sais, j'ai été réellement attendent de lui donner un coup de fouet :\
InformationsquelleAutor pajton

Vous devez vous connecter pour publier un commentaire.