preformance Problem

[web4free]

Hi Leute,

sicher ein Tema welches schon oft diskutiert wurde. Es geht bei der INSERT und bei der UPDATE Anweisung um die Preformance.
Sicher sind meine Indexe und SQL-Statements nicht die besten. Da ließe sich sicher noch einiges herausholen. Aber ich sags mal so: Trotz der Zugriffe die wir verzeichenen hinkt die Seite kein bisschen und ich wüßte auch gar nimmer welche Indexe ich noch setzen sollte.

Aber ich habe alle 10 Minuten einen Job der die DB ziehmlich beansprucht und ich weiß nicht mehr wie ich diesem Problem entgegentreten könnte.
Wir besitzen eine Web2 KFZ-Börse, welche zur Zeit grad mal an die 135.000 aktive PKW Inserate heranngekommen ist. Ich bekomme von den Händlern in Sammelftp-Ordnern die Inserate per ZIP-File.
Diese werden dann nach der Reihe abgearbeitet und in die Datenbank befördert. Entweder als INSERT wenn komplett neues KFZ eingetragen wurde, oder als UPDATE wenn sich nur Daten an einen bestehenden Inserat geändert haben. Somit bleibt auch die ID sehr lange gleich und ändert sich nicht dauernd, was auch bzg. Google schei.. wäre.

Das Abarbeiten der ZIP-Files soll in Kürze ein 2. Server machen, damit der Hauptserver davon verschont bleibt.
Es geht dann aber immer noch um die Inserts und Updates auf dem Hauptrechner.
Klar kann ich einen Insert mittles LOAD DATA machen, was sicher noch schneller ginge als der momentane Schritt über die Konsole:

exec("mysql --user=xxx --pass=xxx --host=xxx database < file.sql, $ret); 

Klar mache ich auch noch ein

/*!40000 ALTER TABLE `anzeigen` DISABLE KEYS */;
LOCK TABLES `anzeigen` WRITE; 

und abschließend ein

UNLOCK TABLES;
/*!40000 ALTER TABLE `anzeigen` ENABLE KEYS */; 

Dazwischen sind die INSERT's und UPDATE's

Die SQL-Dateien welche dann eingespielt werden sind meistens so um die 5-9MB groß - es sind so zwischen 3.000 und 5.000 Datensätze die da eingefügt bzw. upgedatet werden.
Abschließend noch sei gesagt, dass ich beim INSERT nicht einzelne Inserts benutze sondern diese Variante

INSERT INTO `anzeigen` VALUES ('blabla', 'blabla', blabla'),
('blabla', 'blabla', blabla'),
('blabla', 'blabla', blabla'),
... 

Ich glaub auch nicht, dass die Insertanweisungen das lamende Thema sind, sondern die UPDATE-Anweisungen, welche wesendlich mehr sind als die INSERT-Anweisungen.
Jedenfalls fährt mir mysql immer auf über 93% Prozessorleistung.
Das kanns ja wohl nicht sein? Oder doch?

Hat irgendwer von euch für mich Tipps oder Ratschläge, damit ich dieses Problem aus der Welt schaffen kann?

Benutzte Konfiguration:
PHP 5.1.4
MYSQL 4.1.10a-Max-log

lg.
Werner

[asp2php]

Index haben Vor- und Nachteile. Man muss es bei Erstellung abwägen, denn Index beschleunigt zwar die Suche/das Auslesen, bremst aber auch das Einfügen. Also, wenn du sehr viel INSERT-Aktionen täglich hast, dann geht mit Index vorsichtig um. Lieber die User etwas warten lassen, als den kompletten DB-Server einfrieren.

[web4free]

Hi,

soetwas in der Art dachte ich mir schon :-(
In der Tat war heute mein Table 'anzeigen' bereits über 2GB groß.
Nach einen Optimice war er wieder auf 170MB herunten.
Das meiste fressen die Indexe.

Ich weiß nicht ob ich das richtige mache wenn ich bei meinen SELECT Anweisungen statt einen normalen "SELECT ......" ein "SELECT SQL_CACHE...." mache.
Ich werds einfach mal probieren und die gesetzten Indexe heraussichern.
Und dann werde ich den Unterschied eh merken.

Das ich eventuell die User etwas warten lassen muß gefällt mir halt so gra nicht. Zumal jetzt ein B2B Bereich für KFZ-Händler angestrebt wird.
Und wenn die auf der Seite ausgebremst werden ist es nicht grad die feine englische Art.
Aber ich entferne ja nur aus dem Table anzeigen mal die Indexe und schau was passiert.
Heute ist sowieso - ausser der Importmenge - fast nix los vom Traffic.
Da kann ich das mal verschmerzen und ggf. ja wieder hinzufügen die Indexe.

Ich gebe dann mal Bescheid wie das Ergebnis war/ist

lg.
Werner

[onemorenerd]

Auch einen Versuch wert: Index droppen, Updates/Inserts ausführen, Index wieder adden. MySQL muß dann zwar den Index komplett neu bauen, aber das geht u.U. schneller, als ihn die ganze Zeit parallel zu pflegen.

[web4free]

Original geschrieben von onemorenerd
Auch einen Versuch wert: Index droppen, Updates/Inserts ausführen, Index wieder adden. MySQL muß dann zwar den Index komplett neu bauen, aber das geht u.U. schneller, als ihn die ganze Zeit parallel zu pflegen.

Hi,

lieb gemeint aber ist das nicht das selbe was ich nicht oben bereits erwähnte??

/*!40000 ALTER TABLE `anzeigen` DISABLE KEYS */;
/*!40000 ALTER TABLE `anzeigen` ENABLE KEYS */; 

Ich hab die Indexe mal sehr stark reduziert und bei jeder SELECT Anweisung die SQL_CACHE hinzugefügt.
Aber gleich gescheit, damit ichs in der zentralen config.php aktivieren bzw. deaktivieren kann.

Was mich in diesem Zuge interessieren würde wäre, wie lange die Abfragen im Cache liegen bleiben?
Was ich weiß ist, dass der älteste geschmissen wird wenn er voll ist - oder lieg ich da falsch.
Von der Performance her spühre ich drotz weniger Indexe eine deutliche Steigerung der Abfragegeschwindigkeit, auch wenn mir die errechneten Millisekunden die ich zum debuggen anzeige das Gegenteil zeigen. Da hat sich nicht wirklich was verändert. Könnte aber am SQL_CACHE liegen, da der etwas von der Performance zerrt, soweit ich das auf mysql.de gelesen habe.

lg.
Werner

[ghostgambler]

Wieso lockst du die Tabelle?
Wenn dir dazu keine passende Antwort einfällt (denn mir fällt für diesen Fall keine ein), lass es

[web4free]

Locken tu ich weil es auch auf der Mysql-Doku als Performanceerhöhung steht. http://dev.mysql.com/doc/refman/5.1/...ert-speed.html

Wenn dir dazu keine passende Antwort einfällt (denn mir fällt für diesen Fall keine ein), lass es

Ich nehme mal an, dass sich das auf den Befehl

LOCK TABLES a WRITE; 

bezieht......

[ghostgambler]

Original geschrieben von web4free
Locken tu ich weil es auch auf der Mysql-Doku als Performanceerhöhung steht. http://dev.mysql.com/doc/refman/5.1/...ert-speed.html

*hust* ... ja ... was da nicht steht ist, dass bei einem WRITE-Lock alle anderen Connections, wenn sie auf die Tabelle zugreifen wollen, darauf warten, dass der Lock verschwindet. Wenn jetzt dein DB-Server max_connections=100 hat und alle 100 Verbindungen darauf warten, dass der Lock verschwindet, ist deine komplette Website tot...

Abgesehen davon sollte man den Absatz auch bitte bis zum Ende lesen:

Explizite Sperranweisungen sind nicht erforderlich, wenn Sie alle Datensätze mit einer einzelnen INSERT-Anweisung einfügen können.

und genau das tust du.


Abgesehen davon poste mal bitte die Tabellen-Struktur~

[web4free]

Naja nur inserts sinds ja leider keine. Das meiste sind die Updates.
Das Verhältnis ist so 4:6 - also auf 10 Inserate kommen 4 inserts (also neue Inserate) und 6 Updates bestehender Inserate.

Ich geh einfach mal her und deaktiviere im den LOCK und schau weiter.
Jedenfalls ist das Ausbremsen schon deutlich geringer - was dann, wenn die Hauptaufgaben am 2. Server ablaufen, nochmals weniger wird, weils dann wirklich nur mehr um die INserts und Updates geht.

Scheinbar hilft alle Theorie nix und man muß scheinbar bei einigen Sachen dieses erst in der Praxis sehen und kennenlernen ;-)

Aber danke mal für diesen Hinweis.
Die Tabellenstruktur würde ich gerne nicht öffentlich zeigen, weil ich mich dafür geniere und mich sicher einige schimpfen werden.
Auf der anderen Seite könnte mir da vielleicht auch mal einer einen Tipp geben wo ich was falsch gemacht habe :-)

Ich überlegs mir noch, ok? ;-)

lg.
Werner

[ghostgambler]

Original geschrieben von web4free
Aber danke mal für diesen Hinweis.
Die Tabellenstruktur würde ich gerne nicht öffentlich zeigen, weil ich mich dafür geniere und mich sicher einige schimpfen werden.
Auf der anderen Seite könnte mir da vielleicht auch mal einer einen Tipp geben wo ich was falsch gemacht habe :-)

Ich überlegs mir noch, ok? ;-)

Na, nachdem was wir hier schon alles gesehen haben ... ich glaube das kannst du nicht übertreffen ^^;

[web4free]

Ok! Auf eigene Gefahr hin komme ich dem Wunsch gleich nach und poste Euch mal meine Tabellenstruktur.
Wird aber noch ein bisserl dauern, da ich nochmals die Feldwerte überarbeite.

Und dann könnt Ihr über mich schimpfen
Wenn es aber Verbesserungsvorschläge gibt bin ich natürlich säuisch erfreut und dankbar dafür.

[web4free]

So anbei die Struktur wie sie zur Zeit ist.
Wenns noch wichtige Dinge gibt die zu wissen sind sagt es mir bitte.

CREATE TABLE `anzeigen` (
`id` bigint(11) unsigned NOT NULL auto_increment,
`Datum` int(10) NOT NULL default '0',
`Bild1` varchar(39) NOT NULL default '',
`angebotdestages` int(1) NOT NULL default '0',
`Bild2` varchar(39) NOT NULL default '',
`Bild3` varchar(39) NOT NULL default '',
`Bild4` varchar(39) NOT NULL default '',
`Bild5` varchar(39) NOT NULL default '',
`Bild6` varchar(39) NOT NULL default '',
`Hersteller` varchar(100) NOT NULL default '',
`Modell` varchar(100) NOT NULL default '',
`modellfreitext` varchar(100) NOT NULL default '',
`Fahrzeugtyp` varchar(30) NOT NULL default '',
`Motor` int(10) NOT NULL default '0',
`Kraftstoff` varchar(30) NOT NULL default '',
`Getriebe` varchar(30) NOT NULL default '',
`Erstzulassung` decimal(5,4) NOT NULL default '0.0000',
`Kilometer` int(20) NOT NULL default '0',
`Farbe` varchar(200) NOT NULL default '',
`FarbeGlobal` varchar(200) NOT NULL default '',
`Tueren` int(1) NOT NULL default '0',
`Preis` decimal(10,2) NOT NULL default '0.00',
`TUeV` decimal(5,4) NOT NULL default '0.0000',
`AU` decimal(5,4) NOT NULL default '0.0000',
`user` varchar(100) NOT NULL default '',
`Text` longtext NOT NULL,
`Typ` varchar(30) NOT NULL default '',
`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',
`vb` varchar(200) NOT NULL default '',
`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) NOT NULL default '',
`cozwei` int(10) NOT NULL default '0',
`Ausstattung` longtext NOT NULL,
`Komfort` longtext NOT NULL,
`landregion` varchar(200) NOT NULL default '',
`klassifizierung` varchar(200) NOT NULL default '',
`counter` int(10) NOT NULL default '0',
`einstelldauer` int(4) NOT NULL default '0',
`freeid` varchar(200) NOT NULL default '',
`suchwort1` varchar(200) NOT NULL default '',
`suchwort2` varchar(200) NOT NULL default '',
`suchwort3` varchar(200) NOT NULL default '',
`suchwort4` varchar(200) NOT NULL default '',
`suchwort5` varchar(200) NOT NULL default '',
`bilderveroeffentlichen` int(1) NOT NULL default '0',
`unfallfahrzeug` int(1) NOT NULL default '0',
`internenummer` varchar(200) NOT NULL default '',
`importwhere` varchar(30) NOT NULL default '',
`landcode` char(3) NOT NULL default '',
`fart` varchar(4) NOT NULL default '',
PRIMARY KEY  (`id`),
KEY `Hersteller` (`Hersteller`),
KEY `Modell` (`Modell`),
KEY `user` (`user`,`internenummer`,`importwhere`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

[ghostgambler]

hm... da sind ne Menge varchar und longtext-Spalten oo,
Und der user-index über 3 varchar-Spalten mit einer gesamt-Länge von 330 Zeichen ist nicht gerade prickelnd, aber naja ... hm

Farbe varchar(200) könnte man eventuell ein enum draus machen
user varchar(100)? Hast du keine user-Tabelle wo du mit ids arbeitest, sodass man das varchar-Feld in ein id-Feld umändern könnte?

Hersteller könnte man auslagern in eine eigene Tabelle und nur dessen id dann in der Tabelle hier speichern; Stichwort Normalisierung

Aber es ging mir ja eigentlich mehr um die Indizes ^^;


Zeig mal bitte die UPDATE-Queries aus dem Dump (einen von jeder Sorte ... du brauchst nicht ein Dutzend Queries posten, die im Endeffekt alle gleich sind nur andere Werte in anderen Zeilen ändern) und am besten auch gleich die Ausgabe von EXPLAIN
Die Update-Queries dürften ja irgendwie so aussehen:
UPDATE tabelle SET spalte=wert, spalte2=wert WHERE bla="blubb";
Und du setzt dann mal für alle der verschiedenen Update-Queries die du uns hier postest einen Query allá
EXPLAIN SELECT * FROM tabelle WHERE bla="blubb";
ab - also einfach einen SELECT mit der Where-Klausel vom Update

[Wyveres]

so ganz ins Blaue gesagt würde ich jetzt erlich gesagt laut schreiend davon rennen.

so wie es aussieht Befindet sich die DB tabelle NUR in der 1. Normalform.

vieleicht solltest du sie Normalisieren. das vermindert den aufwand des Updaten/Insert um einiges. auch wenn es dadurch Komplizierter wirkt. (kein Insert mehr in eine Tabelle sondern in mehrere)
würde es doch viel Zeit einsparen.

Original geschrieben von ghostgambler

Na, nachdem was wir hier schon alles gesehen haben ... ich glaube das kannst du nicht übertreffen ^^;

also vom dem was ich bisher gesehen habe ist das hier das Monströsteste. aber es ist wenigstens nicht totaler misst.

deswegen mein TIP: von der 1. Normalform in die 3. Normalform bringen.
wirst Sehen der Aufwand wird sich Lohnen.

[web4free]

Antwort siehe weiter unten...