UPDATE's und INSERT's viel zu langsam

[web4free]

Hallo Leute,

sicher ein Thema was schon des öfteren an den Tisch gebracht wurde.
Ich habe eine Tabelle in der zur Zeit ca. 460.000 Einträge vorhanden sind.

Bisher hatte ich anstelle eines Updates des jeweiligen Datensatzes immer den alten Datensatz verworfen und stattdessen einen neuen Datensatz angelegt.
Direkt über die Konsole mit

mysql --user=xxx --pass=xxx --host=xxx database < filename.sql 

ist das eine sehr schnelle Angelegenheit.
Bisher war es egal, ob sich bei einen Datensatz die ID ändert.
Da sich hier mit der Zeit aber - weil über den Tag verstreut sehr viele neue ID's generiert werden - keine schöne Sache ergibt hab ich begonnen Datensätze nicht mehr neu anzulegen sondern Upzudaten.

Somit hab ich jetzt in den SQL-Files nicht nur INSERT-Anweisungen sondern eben auch extrem viele UPDATE'S was ja seine Richtigkeit hat.

Nun hab ich zusätzlich versucht mittels

LOCK TABLES `tabelle1` WRITE, `tabelle2` WRITE; 

das ganze zu beschleunigen, was aber zur folge hat, dass es a) auf der Seite selbst da wo genau diese beiden Tabellen mittels SELECT abgefragt werden zu einer Bremse kommt, was lt. der MYSQL-Doku ja klar hervorgeht und b) der Insert und Update-Vorgang nicht wirklich viel schneller ist.

Nun meine Frage an Euch:
Wie kann ich den UPDATE-Vorgang extrem flott machen.
Die SQL-Files haben im Schnitt so zwischen 2 und 3MB Dateigröße, was für die Datensatzmengen die upgedatet bzw. geinsertet werden sollen vollkommen ok ist.
Die Updates sind folgenderweise aufgebaut:

UPDATE tabelle1 SET spalte1='blabla', spalte2='blabla' ..... WHERE id=betroffeneID 

Sollten noch wichtige Infos fehlen gebe ich Sie gerne bekannt, damit es nicht zu einer Rätselralley wird.

Vielen Dank

[onemorenerd]

Tabellentyp?

[web4free]

Original geschrieben von onemorenerd
Tabellentyp?

MyIsam

[PHP-Desaster]

Was für Spaltentypen, wo liegen Schlüssel, wie sehen die eingefügten Daten aus?

[web4free]

Hi,

Sorry die lange Response. Ich bin grad mit Arbeit überhäuft worden.
Anbei mal die Tabellenstruktur:

CREATE TABLE `anzeigen` (
  `id` bigint(11) unsigned NOT NULL auto_increment,
  `insId` varchar(32) NOT NULL default '',
  `Datum` int(10) NOT NULL default '0',
  `Bild1` varchar(39) default NULL,
  `angebotdestages` int(1) NOT NULL default '0',
  `Bild2` varchar(39) default NULL,
  `Bild3` varchar(39) default NULL,
  `Bild4` varchar(39) default NULL,
  `Bild5` varchar(39) default NULL,
  `Bild6` varchar(39) default NULL,
  `Hersteller` varchar(100) default NULL,
  `Modell` varchar(100) default NULL,
  `modellfreitext` varchar(100) default NULL,
  `original` varchar(100) NOT NULL default '',
  `Fahrzeugtyp` varchar(30) default NULL,
  `Motor` int(10) NOT NULL default '0',
  `Kraftstoff` varchar(30) default NULL,
  `Getriebe` varchar(30) default NULL,
  `Erstzulassung` varchar(7) default NULL,
  `Kilometer` int(20) NOT NULL default '0',
  `Farbe` varchar(200) default NULL,
  `FarbeGlobal` varchar(200) default NULL,
  `Tueren` varchar(8) default NULL,
  `Preis` decimal(10,2) NOT NULL default '0.00',
  `PLZ` varchar(10) default NULL,
  `AU` decimal(5,4) NOT NULL default '0.0000',
  `user` varchar(50) default NULL,
  `Text` longtext,
  `Typ` varchar(30) default NULL,
  `Zylinder` int(2) NOT NULL default '0',
  `Hubraum` int(10) NOT NULL default '0',
  `Sitze` int(2) NOT NULL default '0',
  `Leergewicht` int(6) NOT NULL default '0',
  `toplist` int(1) NOT NULL default '0',
  `aktiv` int(1) NOT NULL default '1',
  `HU` decimal(5,4) NOT NULL default '0.0000',
  `mwst` int(1) NOT NULL default '0',
  `mwstSatz` decimal(3,2) NOT NULL default '0.00',
  `schadstoffklasse` varchar(20) default NULL,
  `verbrauchA` double(3,2) NOT NULL default '0.00',
  `verbrauchB` double(3,2) NOT NULL default '0.00',
  `verbrauchC` double(3,2) NOT NULL default '0.00',
  `Fgstnr` varchar(200) default NULL,
  `cozwei` int(10) NOT NULL default '0',
  `Ausstattung` longtext,
  `Komfort` longtext,
  `landregion` varchar(200) default NULL,
  `klassifizierung` varchar(200) default NULL,
  `counter` int(10) NOT NULL default '0',
  `einstelldauer` int(4) NOT NULL default '0',
  `freeid` varchar(200) default NULL,
  `suchwort1` varchar(200) default NULL,
  `suchwort2` varchar(200) default NULL,
  `suchwort3` varchar(200) default NULL,
  `suchwort4` varchar(200) default NULL,
  `suchwort5` varchar(200) default NULL,
  `bilderveroeffentlichen` int(1) NOT NULL default '0',
  `unfallfahrzeug` int(1) NOT NULL default '0',
  `internenummer` varchar(200) default NULL,
  `importwhere` varchar(30) default NULL,
  `landcode` char(3) default NULL,
  `fart` varchar(4) default NULL,
  `haendler` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `Hersteller` (`Hersteller`),
  KEY `Group 02` (`user`,`internenummer`,`importwhere`),
  KEY `Group 04` (`Kraftstoff`,`aktiv`,`fart`),
  KEY `Group 05` (`Preis`,`aktiv`,`fart`),
  KEY `Group 06` (`Kilometer`,`aktiv`,`fart`),
  KEY `Fahrzeugtyp` (`Fahrzeugtyp`),
  KEY `Erstzulassung` (`Erstzulassung`),
  KEY `Motor` (`Motor`),
  KEY `Kraftstoff` (`Kraftstoff`),
  KEY `klassifizierung` (`klassifizierung`),
  KEY `Kilometer` (`Kilometer`),
  KEY `Preis` (`Preis`),
  KEY `Group 03` (`fart`,`aktiv`,`Fahrzeugtyp`),
  KEY `Group 01` (`aktiv`,`Hersteller`,`Modell`),
  KEY `Hersteller_3` (`Hersteller`,`Fahrzeugtyp`,`aktiv`,`fart`),
  KEY `Hersteller_2` (`Hersteller`,`Fahrzeugtyp`,`user`,`aktiv`,`fart`),
  KEY `Count_Fahrzeuge` (`fart`,`aktiv`),
  FULLTEXT KEY `original` (`original`,`Text`,`Ausstattung`,`Komfort`)
) TYPE=MyISAM; 

Wenn noch Info fehlen sollte bitte sagen...

Was ich noch vergessen habe zu erwähnen:
DELAYED hätte ich auch schon probiert - dadurch wirds auch nicht wirklich besser.
Hatte auch schon die Idee die KEY's zu deaktivieren und nach der ganzen Einfüge/Update Aktion wieder zu enabeln - was aber auf der Seite selbst zu ewig langen Wartezeiten führen würde.

Mir ist auch bewußt, dass einige KEY's in der Form gar nicht sein dürften wie zB. der KEY Hersteller_2 und Hersteller_3

Mein Problem was sich langsam aber sicher ergibt ist, dass ich viele Importe noch in der Warteschleife habe und ständig welche hinzukommen und ich den Berg nicht rasch genug abarbeiten kann.

[PHP-Desaster]

Ich hab jetzt nicht die riesen Ahnung, aber wahrscheinlich ist die Tabelle einfach zu dick
Hast du einmal einen Beispieldatensatz und ein Beispiel-Update?

[web4free]

Ja also ein Datensatz könnte ungefär so aussehen:

INSERT INTO `anzeigen` VALUES (11222881, '1182ea0a3d1b4b775b5e9b14310f20f9', 
1197602701, '1182ea0a3d1b4b775b5e9b14310f20f9_01.jpg', 0, '', '', '', '', '', 
'FORD', 'Fiesta', ' 1.4 16V Fun X', 'FORD Fiesta 1.4 16V Fun X', 'Limousine', 59, 
'Benzin', 'Handschaltung', '2007.05', 19800, 'schwarz (pantherschwarz)', '', 
'4', 10750.00, '32469', 5.2010, 'username', '', 'Gebrauchtfahrzeug', 0, 0, 0, 0, 1, 1, 5.2010, 
1, 19.00, '', 0.00, 0.00, 6.40, '', 152, 'Außenspiegel von innen verstellbar (mechanisch), 
Audiosystem \\'5000\\' (Radio/Cassette), Airbag Fahrer-/Beifahrerseite, Seitenairbag vorn, 
Zeituhr (digital), Zierleisten geklebt, Zentralverriegelung mit Fernbedienung, B A R P R E I S, ', 
'ABS, Airbag, Elektr. Fensterheber, Klimaanlage, Wegfahrsperre, Zentralverriegelung, Metalliclack', 
'Deutschland|Nordrhein-Westfalen|Petershagen', '', 0, 0, '', '', '', '', '', '', 0, 0, 'se03', 
'importeur', 'de', 'PKW', 1); 

Und ein UPDATE würtde dann zB. so aussehen können:

UPDATE anzeigen SET aktiv=9, insID='bc0faf0a3d1a745363a56e633dc2daf8', 
Bild1='bc0faf0a3d1a745363a56e633dc2daf8_01.jpg', 
Bild2='bc0faf0a3d1a745363a56e633dc2daf8_02.jpg', 
Bild3='bc0faf0a3d1a745363a56e633dc2daf8_03.jpg', 
Bild4='bc0faf0a3d1a745363a56e633dc2daf8_04.jpg', 
Bild5='bc0faf0a3d1a745363a56e633dc2daf8_05.jpg', 
Bild6='', 
angebotdestages=0, 
Hersteller='Mercedes-Benz', 
Modell='A 170', 
modellfreitext=' CDI Automatik, Klima', 
original='Mercedes-Benz A 170 CDI Automatik, Klima', 
Fahrzeugtyp='Limousine', 
Motor=70, Kraftstoff='Diesel', 
Getriebe='Automatik', 
Kilometer=173600, 
AU=4.2008, 
PLZ='73230', 
Erstzulassung='2003.04', 
Farbe='Silber', 
FarbeGlobal='', 
Preis=8450.19, 
verbrauchA=0, 
verbrauchB=0, 
verbrauchC=0, 
cozwei=0, 
Komfort='Kann auch sehr langer Text sein', 
Ausstattung='Kann auch sehr langer Text sein', 
Text='Teilweise sehr langer Text', 
user='username', 
Typ='Gebrauchtfahrzeug', 
Hubraum=0, 
toplist=1, 
HU=4.2008, 
mwst=1, 
mwstSatz='19', 
landregion='Deutschland|Baden-Württemberg|Kirchheim unter Teck', 
klassifizierung='A-Klasse', 
unfallfahrzeug=0, 
internenummer='3999219', 
importwhere='importeur', 
landcode='de', 
fart='PKW', 
haendler=1 
WHERE id=11189934; 

Gibt es denn nicht so wie beim INSERT auch eine Möglichkeit mehrere UPDATES in einen Rutsch zu machen??

[onemorenerd]

In einem Rutsch (= 1 Transaktion) geht es nur mit storage engines, die Transaktionen auch unterstützen, z.B. InnoDB.

start transaction;
update 1;
...
update n;
commit;


Das ist nicht zwangsläufig schneller, aber einen Versuch wert.

[web4free]

Meinst Du ich sollte den TabellenTyp von dieser einen Tabelle auf InnoDB ändern??
Verloren geht da ja eh nix, oder??

NACHTRAG:
Nachdem ich ja bei reinen INserts eine superschnelle Sache hatte und die UPDATEs so lange brauchen hätte ich gerade eine andere Idde wo ich aber noch nicht weiß, ob die besser wäre.

Da ich ja die ID weiß könnte ich ja diesen besagten Datensatz löschen und durch ein INSERT mit der der selben ID nochmals einfügen, womit auch eine Lücke wieder geschlossen werden würde.

Ich weiß jetzt nur nicht ob ein DELETE FROM Table WHERE id='xy' schneller ist als ein UPDATE - horcht sich halt irgendwie kontraproduktiv an

[PHP-Desaster]

Das ist sogar definitiv Kontraproduktiv. Der Datensatz muss erstmal komplett entfernt werden, d.h. auch aus dem Index. In Algorithmen und Datenstrukturen lernst du als erstes, dass das Löschen von Knoten aus einem Baum das schwierigste ist! Ein Update ist definitiv schneller als ein Delete und ein Insert!

[web4free]

Hallo,

Ich hätte da jetzt mal etwas in meinen Augen völlig kontraproduktives gemacht:

Beim durchgehen der Datensätze kenne ich ja die eideutige ID.
Somit ist das Erste was ich mache ein Delete aller bekannten ID's mit anschließenden INSERT der neuen Daten mit den selben ID's
Im Prinzip hat der ursprüngliche Datensatz den veränderten Teil aber die selbe ID - also so als hätte ich ein UPDATE gemacht.

Zusätzlich dachte ich mir dass es vielleicht etwas mehr Sinn ergibt die DELETE-Anweisung nicht so zu lösen

DELETE FROM anzeigen WHERE id=11188291;
DELETE FROM anzeigen WHERE id=11188292;
DELETE FROM anzeigen WHERE id=11188279;
DELETE FROM anzeigen WHERE id=11188280;
usw. 

sondern so:

DELETE FROM anzeigen WHERE id=11188291 OR id=11188292 OR id=11188279 OR id=11188280 ... 

Komisch an der Sache, dass diese Variante um einiges schneller läuft als mit einen UPDATE und das versteh ich nicht.
Kann mir das mal jemand erklären, damit ich das auch verstehen kann.
Denn nur eine Lösung zu haben ohne zu wissen warum das so ist, ist auch nicht grad toll, weil man den Fehler sonst wieder macht.

[web4free]

Original geschrieben von PHP-Desaster
Das ist sogar definitiv Kontraproduktiv. Der Datensatz muss erstmal komplett entfernt werden, d.h. auch aus dem Index. In Algorithmen und Datenstrukturen lernst du als erstes, dass das Löschen von Knoten aus einem Baum das schwierigste ist! Ein Update ist definitiv schneller als ein Delete und ein Insert!

Warst jetzt ein bisserl schneller als ich mit meiner Erkenntnis die ich gepostet hatte

[onemorenerd]

Original geschrieben von PHP-Desaster
Das ist sogar definitiv Kontraproduktiv. Der Datensatz muss erstmal komplett entfernt werden, d.h. auch aus dem Index. In Algorithmen und Datenstrukturen lernst du als erstes, dass das Löschen von Knoten aus einem Baum das schwierigste ist! Ein Update ist definitiv schneller als ein Delete und ein Insert!

Theorie ist toll. Aber bitte im Kontext des Problems.

Hier geht es nicht nur um Bäume, sondern auch um Daten. Die Bäume (Indizes) passen hübsch in den RAM. Die Daten liegen auf Platte, wo jeder Zugriff um Faktor 10^3 teurer ist.

Deswegen werden die Daten auch gar nicht von der Platte gelöscht. Lediglich der Zeiger auf sie wird aus allen Zugriffsstrukturen entfernt und der Freispeicherverwaltung zurückgegeben. Löschen ist also eine RAM-only Operation.

INSERTs sind beim Rausschreiben auf die Platte schneller als UPDATEs, weil neue Datensätze einfach hingeschrieben werden, wo es gerade passt. Im Idealfall einfach ans Ende des Tablespace, also in einer sequentiellen Schreiboperation. Nur einmal den Kopf positionieren, billiger gehts nicht.
Bei UPDATEs von Feldern variabler Länge (varchar) kann es außerdem vorkommen, dass der neue Datensatz nicht an die Stelle des alten passt. Das DBMS muß ihn dann segmentieren, einen größeren Freiraum suchen oder komplett hinten anhängen.

Wenn das Löschen und Einfügen in einer Transaktion geschieht, können sich die Operationen auf den Indizes teilweise aufheben.
In der Theorie würde man berechnen: Löschen eines Knotens, Baum reorganisieren, Löschen eines weiteren Knotens, reorganisieren, ..., Einfügen eines Knotens, reorganisiern, Einfügen eines weiteren Knotens, reorganisieren usw.
Aber im Idealfall gibt es weniger zu reorganisieren, weil einige neue Knoten an den Positionen der alten genau richtig sind - immer dann, wenn die Index-Spalten gleich sind. Last but not least wird jedes vernünftige DBMS auch nur einmal am Ende der Transaktion reorganisieren.

[PHP-Desaster]

Wow, da habe ich aber viel zu abstrakt gedacht! Du hast natürlich recht, sehr gut erklärt ! Wenn es intern wirklich so läuft wie du sagst, kann ich mir doch vorstellen, dass löschen und einfügen schneller sein wird als das abändern der Daten!

[web4free]

Hallo Ihr 2,

Das würde ja eigendlich sogar meine visuelle Überwachung bestätigen.
Ich hebe mir die SQL-Files ca. 4h auf bevor ich Sie von der Platte lösche.
Hat den Grund, dass wenn was schiefgeht ich zumindest die letzten 4h nochmals einspielen kann.

Mit der UPDATE-Variante hatte ich ein SQL-File auf ca. 10 Minuten.
Das ist horror langsam, wenn man dem Kunden verspricht, dass das Update seinen Bestandes innerhalb von 1-2 Minuten online ist.

Mit meiner Laienhaften Delete-Insert-Lösung bin ich wieder auf diesen Level, dass pro 1-2 Minuten die Updates durch sind und online verfügbar.

Wie sich das jetzt über Nacht auswirkt weiß ich aber noch nicht!
Um 02:30h täglich bekomme ich einen Schub von ca. 950 bis 1500 ZIP-Files in einen Ordner den ich bis spätestens 9h abgearbeitet haben will (was bis jetzt immer der Fall war).
Zusätzlich hab ich aber ab 00h bis ca. 8h (kann auch mal 10h werden) Wartungsarbeiten am Server in der DB.
Ich werde Morgen berichten ob alles so schnell weiterläuft wie es das jetzt tut!

Immerhin tut er seit meiner Umstellung wieder ganz fleißig schwitzen und das im positiven Sinne