sehr viele Daten in einer MySQL Datenbank überprüfen/schreiben

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Sirius3
User
Beiträge: 7919
Registriert: Sonntag 21. Oktober 2012, 17:20

Mittwoch 4. Oktober 2017, 16:07

@__deets__: ich hab keine Ahnung, ob MySQL Anfragen parallelisiert.
DasIch
User
Beiträge: 2450
Registriert: Montag 19. Mai 2008, 04:21
Wohnort: Berlin

Mittwoch 4. Oktober 2017, 20:18

Sirius3 hat geschrieben:@DasIch: du glaubst nicht, wie lahm Datenbanken werden, wenn Indizes fehlen.
Langsam ist relativ. Ein Index hilft beim lesen aber dafür kostet es Zeit beim schreiben. Ersteres ist auch nur bei ausreichend großen Tabellen nennenswert.

Bei Größenordnungen von "mehreren tausenden Daten" wie hier bringt einem ein Index jetzt nicht so viel.
Sirius3
User
Beiträge: 7919
Registriert: Sonntag 21. Oktober 2012, 17:20

Mittwoch 4. Oktober 2017, 22:44

@DasIch: das ist der Unterschied, ob nun 1000 Elemente verglichen werden müssen, oder 2 bis 3. Ich würde sagen, das merkt man ziemlich schnell. Da ist die Zeit, die man braucht, um ein Element dem Index hinzuzufügen, vernachlässigbar.
Sirius3
User
Beiträge: 7919
Registriert: Sonntag 21. Oktober 2012, 17:20

Freitag 6. Oktober 2017, 09:20

Ich mach mal einen Full-Quote, damit der Beitrag aus dem falschen Thread im richtigen landet:
boeserkorn hat geschrieben:@Dasich

kannst du mir helfen?

hab heute Nacht die Daten importieren lassen und er hat innerhalb von 1.5h 20551 Datensätze in die Datenbank geschrieben.
Danach und davor und danach hat er ca. 8h für die gleiche Datenmenge gebraucht, Aufbau und Routine ist die gleiche gewesen

weisst du warum die Zeiten so unterschiedlich sind?

Code: Alles auswählen

05.10.2017 09:25:28
klaas_eisman
201703-09 dithmarsen.xls
215000019374
20551
05.10.2017 16:46:09
klaas_eisman
201703-09 immenste.xls
225010041504
20551
06.10.2017 00:59:53
klaas_eisman
201703-09 heinrichs.xls
215000047128
20551
06.10.2017 02:20:05
klaas_eisman
201703-09 113.xls
225010036614
20551
Was soll denn das Listing sagen? Was sind das für Zahlen und was für Dateien?

Die Erklärung habe ich Dir schon geliefert. Bei jedem UPDATE werden Deine Daten linear durchsucht. Solange beim Aufbau der Tabelle noch wenige Zeilen in der Tabelle stehen, geht das ziemlich schnell, später immer langsamer. Also beim Anlegen N*(N-1)/2 Vergleiche plus N INSERTS, beim Updaten N**2 Vergleiche plus N UPDATES. Wie sieht denn nun Dein Datenbankschema aus und wie der Pythoncode, den Du verwendest?
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

Freitag 6. Oktober 2017, 17:18

Code: Alles auswählen

Uhrzeit:      05.10.2017 09:25:28
Datenbank: klaas_eisman
Dateiname: 201703-09 dithmarsen.xls
Datensätze/Reihen: 20551
Uhrzeit:     05.10.2017 16:46:09
Datenbank: klaas_eisman
Dateiname: 201703-09 immenste.xls
Datensätze/Reihen: 20551
Uhrzeit:      06.10.2017 00:59:53
Datenbank: klaas_eisman
Dateiname: 201703-09 heinrichs.xls
Datensätze/Reihen: 20551
Uhrzeit:      06.10.2017 02:20:05
verstehe es nicht warum er für die gleiche Anzahl der Datensätze viel länger braucht
einmal schafft er es in 1,5h und danach wieder nur in 6-8h
Die Dateien enthalten 7 Monate Viertelstundenwerten also 20.551 Datensätze

ist nicht komplett vollständig

Code: Alles auswählen


Kunde.append("Hansen ")
sunnyportal_ID.append("03da8fbaa")
Datenbank.append(db)
AnlagenID.append(3)
vkn.append("****")



for x in range(0,len(Kunde)):
	cnx = mysql.connector.connect(host = "192.168.178.57", user = "eisman", passwd = "eisman", db = Datenbank[x] )
	cursor = cnx.cursor()
	print time.strftime("%d.%m.%Y %H:%M:%S")
	a ="/home/pi/pi-share/"
	print Datenbank[x]
	#print os.listdir(a)
	filelist=[]
	filelist = glob.glob("/home/pi/pi-share/*.*")
	print filelist
	#print Kunde[x]
	f = 0
	for f in filelist:
		#print f
		#print Kunde[x]
		filename = f.replace('/home/pi/pi-share/','')
		#print filename
		#shutil.move('/home/pi/pi-share/'+filename ,'/home/pi/pi-share/geladen/')
		wb = xlrd.open_workbook(f)
		sheet_xls = wb.sheet_names()
		#print sheet_xls[0]
		sh =  wb.sheet_by_name(sheet_xls[0])
		rows = []
		#for row_number in xrange(sh.nrows):
		#	rows.append(sh.row_values(row_number))
		#for row in rows[:12]:
		#	pass
		#	print(row)
		Vertragskonto = sh.cell_value(0,2)
		zpb = sh.cell_value(1,2)
		#print "VKN: ", Vertragskonto
		if Vertragskonto == vkn[x]:
			print filename
			print Vertragskonto
			#print vkn [x] , "  " , Vertragskonto
			#print "correct"
			#print AnlagenID[x]
			#rows= []
			for y  in xrange(11,sh.nrows):
				date = sh.cell_value(y,0)
				date =  datetime(*xlrd.xldate_as_tuple(date,0))
				print date
				query = ("SELECT idLastgang FROM lastgang WHERE Viertelstunde =  '%s' AND anlagenpv_idAnlagenPV = %s ;"
						 %(  str(date)   , str(AnlagenID[x]) ) ) 
				cursor.execute(query)
				rows = cursor.fetchall()
				numrows = int(cursor.rowcount)
				if not numrows == 0 :
					idlastgang = rows[0][0]
					if not idlastgang is None :
						query = ("UPDATE `lastgang` SET `Viertelstunde` = '%s', `lastgangwert` = '%s', `Geschaftspartnernummer` = '%s', `ZPB` = '%s' WHERE `idLastgang` = '%s' AND `anlagenpv_idAnlagenPV` = '%s';" 
								%(str(date), str(sh.cell_value(y,4)),str(vkn[x]),str(zpb),str(idlastgang), str(AnlagenID[x]) ))
						cursor.execute(query)
						cnx.commit()
				else:
					query = ("INSERT INTO `lastgang` (`Viertelstunde`, `lastgangwert`, `Geschaftspartnernummer`, `ZPB`, `anlagenpv_idAnlagenPV`)" 
						"VALUES ('%s', '%s', '%s', '%s', '%s');" 
							%(str(date ), str(sh.cell_value(y,4)), str(vkn[x]), str(zpb), str(AnlagenID[x]) ))
					#print query
					cursor.execute(query)
					cnx.commit()
					#print query
					pass
Sirius3
User
Beiträge: 7919
Registriert: Sonntag 21. Oktober 2012, 17:20

Freitag 6. Oktober 2017, 18:58

@boeserkorn: Dein Code ist fast nicht lesbar. Lesbarer Code ist aber wichtig, um ihn zu verstehen, um Fehler zu finden und um erst gar keine Fehler zu machen. Kunde, sunnyportal_ID, Datenbank, AnlagenID und vkn scheinen alles Listen zu sein, die über Daten enthalten, die über ihren Index zusammengehören. Das ist ein schlechtes Konzept, da, sobald eine der Listen nicht synchron mit den anderen geändert wird, unauffindbare Fehler entstehen. Besser ist es eine Liste mit strukturierten Objekten (tuple, namedtuple, wörterbuch) zu haben.

Was soll dieses Zuweisen von Werten an Variablen, um sie gleich in der nächsten Zeile wieder zu überschreiben (Zeile 17 oder 21)?

Zeile 22: f ist ein schlechter Name. Variablennamen sollten aussagekräftig sein.
Zeile 25: Du suchst os.path.basename statt replace.
Zeile 52, 60, etc.: niemals Variablen in SQL-Statements hineinformatieren. Damit kannst Du leicht Deine ganze Datenbank kaputt machen (entweder durch einen böswilligen Nutzer, oder unabsichtlich). Parameter werden als zweites Argument an execute übergeben. Nebenbei, die ganzen str-Aufrufe sind überflüssig.
Zeile 55: wenn man nur ein Ergebnis aus einer SQL-Abfrage erwartet, nimmt man fetchone.
Zeile 61: Datenbanken können mit verschiedenen Typen umgehen, datetime-Objekte werden Automatisch zu TIMESTAMPS, Zahlen bleiben Zahlen.
Zeile 63: ein commit nach jedem Update, macht die Sache nicht gerade schneller.
Zeile 72: pass ist hier nutzlos.

Du hast jetzt nicht nur ein UPDATE sondern auch noch ein SELECT, so dass Du alle Datenbanksuchen doppelt machst.

Das eigentlich wichtige bei Deinem Problem, das Datenbankschema, hast Du noch nicht gepostet.
DasIch
User
Beiträge: 2450
Registriert: Montag 19. Mai 2008, 04:21
Wohnort: Berlin

Freitag 6. Oktober 2017, 19:40

INSERTs und UPDATEs die nur eine Zeile verändern ist schonmal grundsätzlich nicht schnell, erst recht nicht wenn du jedesmal eine ganze Transaktion machst. Du nutzt die Datenbank hier in einer sehr ineffizient Weise und dass kostet massiv an Performance.

Um dies etwas zu verdeutlichen: Beruflich bin ich mitverantwortlich für die Verarbeitung von den Ergebnissen eines Optimierungsprozeses der um die 12GB an Daten und hunderte Millionen von Zeilen produziert, dass lässt sich in ~45min in eine MySQL Datenbank kopieren.

Selbst die 1.5h sind unglaublich langsam, da gibt es massives Optimierungspotential.

Um tatsächlich zu sagen wo konkret es hakt müsste man sich die Query Pläne ansehen aber ich würde davon ausgehen dass du keinen Index auf anlagenpv_idAnlagenPV hast. Den anzulegen sollte die Performance schon wesentlich verbessern.

Idealerweise hättest du die Daten als CSV vorliegen und würdest sie dann mit COPY (Postgres) oder LOAD DATA INFILE (MySQL) in die Datenbank kopieren ggfs. in eine temporäre Tabelle. Du könntest dann mit INSERTs und DELETEs (statt UPDATE) arbeiten um die Tabelle in nur wenigen Statements zu aktualisieren. Solche "großflächigen" Änderungen sind wesentlich effizienter und schneller als Zeile für Zeile zu arbeiten.
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

Freitag 6. Oktober 2017, 20:22

@Sirius3 Lasse bitte deine Kommentare, du kritisiert nur und gibts mir nicht wirklich Ratschläge.


@Dasich würdest du mir helfen den Ablauf vernümftig hinzu bekommen?

das mit den temp_Tabellen usw. verstehe ich nicht wirklich und blicke da nicht durch.

für mich war das Reihe für Reihe am leichtesten das umzusetzten um meine Ziele zu erreiche , nun ist es aber so groß geworden das ich mich nur noch mit importieren beschäftige.

würde mich freuen
Benutzeravatar
/me
User
Beiträge: 3191
Registriert: Donnerstag 25. Juni 2009, 14:40
Wohnort: Bonn

Freitag 6. Oktober 2017, 22:00

boeserkorn hat geschrieben:@Sirius3 Lasse bitte deine Kommentare, du kritisiert nur und gibts mir nicht wirklich Ratschläge.
Doch, gibt er. Zum Beispiel: "Dein Code ist fast nicht lesbar." inklusive Hinweisen, wie man es besser machen kann. Du nimmst dir aktuell Leser und damit potenzielle Helfer weg. Mich hat ein Blick auf den Code davon abgeschreckt, mich weiter mit der eigentlichen Frage der Performance zu beschäftigen.
boeserkorn
User
Beiträge: 16
Registriert: Freitag 22. September 2017, 19:46

Samstag 7. Oktober 2017, 10:02

die Tabelle habe so erstellt und danach nochmal geändert, leider bekomme ich "idLastgang" nicht geändert, so das er nicht mehr als PrimaryKey eingetragen ist.

es würde sonst die Routine funktionieren
[codebox=mysql file=Unbenannt.sql]INSERT INTO eisman10.lastgang (Viertelstunde, lastgangwert, anlagenpv_idAnlagenPV) VALUES ('2017-09-01 00:00:00', 0.555,1)
ON DUPLICATE KEY UPDATE Viertelstunde = '2017-09-01 00:00:00', lastgangwert = 0.500;[/code]

ich bin kein Profi, das weiss ich selber. alles was ich bis jetzt gelernt habe ist entweder aus der Schulzeit/Ausbildung oder ich habe mir es mühevoll in der Freizeit dazu gelernt, durch probieren und erfragen, überwiegend aber durch rumprobieren.
klar ist nicht alles perfekt was ich mache, klar sind meine Variablen ab und zu mal komisch benannt, aber es ist doch egal ob die Variable Viertelstunde oder Messzeitpunkt heißt, solange ich weiss in welchen Bezug sie gehört und Viertelstunde ist für mich besser und kann ich mir besser merken.

[codebox=mysql file=Unbenannt.sql]CREATE TABLE IF NOT EXISTS `lastgang` (
`idLastgang` INT(11) NOT NULL AUTO_INCREMENT,
`Viertelstunde` DATETIME NULL DEFAULT NULL,
`lastgangwert` FLOAT NULL DEFAULT NULL,
PRIMARY KEY (`idLastgang`))[/code]


[codebox=mysql file=Unbenannt.sql]ALTER TABLE `lastgang`
ADD COLUMN `anlagenpv_idAnlagenPV` INT(11) NOT NULL AFTER `ZPB`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`idLastgang`, `anlagenpv_idAnlagenPV`),
ADD INDEX `fk_lastgang_anlagenpv1_idx` (`anlagenpv_idAnlagenPV` ASC);
ALTER TABLE `lastgang`
ADD CONSTRAINT `fk_lastgang_anlagenpv1`
FOREIGN KEY (`anlagenpv_idAnlagenPV`)
REFERENCES `anlagenpv` (`idAnlagenPV`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;[/code]
Antworten