Tabellen Optimierung Perfomance Einschätzung Detected Server

[tomtherock]

Halli hallo,

also hab die letzte Zeit Problem mit meinem Shop System gehabt und deswegen mehrere Sachen in betracht gezogen:

1. Hardware aufrüsten -> MYSQL Server auslagern
(wurde mir vorerst von mehreren Leute -> Webhoster abgeraten)

2. daraufhin hab ich mich an die Datenbank Optimierung gemacht und das eigentlich auch ganz gut hinbekommen.

Worum es mir geht ist es, da ja einige Profis hier im Forum sind die bestimmt auch gute Erfahrungswerte haben, und ich nicht erst wieder REAGIEREN will wenn es bereits auf dem Server brennt -> load average von 20 und größer

Hier das was ist:

Hardware:
DETECTED SERVER
2x XEON DUAL CORE
4x 1GB RAM
RAID 1 SCSI Platte soviel ich weiss

kurz um is schon ne ordentliche Maschine leuft aber alles drauf sprich:
Apache,Mysql und Mail Server

Zum Shopsystem Allgemein:
Dynamische Navi, dynamische banner, dynamische Artikelansicht(bestand), halb intelligente Suche in Echtzeit das is so grob das meiste was von MySQL verlangt wird

Hier nun die wichtigsten Tabellen mit Ihren größen(MyISAM):
tbl_artikel = 5.000
tbl_bilder = 14.000
tbl_zuweisungen = 5.200 (Wo der Artikel ist auch mehrfach Kategorien)
tbl_groessen = 55.000 (groessen jedes artikels wegen Warenwirtschaft externe daten)
tbl_kunden = 72.000

das sind mal die wichtigsten das ihr auch abschätzen könnt womit es der MySql zu tun hat.

Hier jetzt mal noch eines der hungrigsten Queries inkl. Explain:
bei Null Last auf dem Server

(12 insgesamt, die Abfrage dauerte 0.0426 sek.)
SELECT *,t1.id_artikel as id_artikel FROM s_artikel as t1 WHERE t1.aktiv_gr = 1 AND t1.aktiv = 1 
AND EXISTS ( SELECT * FROM s_zuweisung as t2 WHERE t1.id_artikel = t2.id_artikel AND t2.id_verwendung = 1
 AND EXISTS ( SELECT * FROM s_bild as t3 WHERE t1.id_artikel = t3.id_artikel ) ) 
AND t1.id_geschlecht != '' ORDER BY t1.einstell_datum DESC,t1.views DESC,t1.last_update_steps DESC LIMIT 0, 12 

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 PRIMARY t1 ref aktiv,aktiv_gr,id_geschlecht aktiv_gr 4 const 2687 Using where; Using filesort 
2 DEPENDENT SUBQUERY t2 ref idx_id_artikel idx_id_artikel 8 usr_web1_1.t1.id_artikel 1 Using where 
3 DEPENDENT SUBQUERY t3 ref idx_id_artikel idx_id_artikel 8 usr_web1_1.t1.id_artikel 4 Using index 

Hab meine ganzen Queries überarbeitet und auch einige Indexierungen
vorgenommen und auch einen großen Erfolg erzielen können von der Perfomance(50-70%)

mit EXPLAIN schauen die meisten Reports auch so aus und hab gelesen
das wenn ref oder eq_ref ausgegeben wird und auch kein ALL etc. eigentlich schon alles gut optimiert ist .

Im Moment leuft der Shop bei ca. 100 gleichzeitigen Besucher so bei Load
Average 2-3 ganz gut, aber denke halt bei 150 is schon kritisch dann und 200 Ende.

Klar weiss ich das ihr das natürlich jetzt schwer an den wenigen Daten
abschätzen könnt aber hoffe halt einfach Erfahrungswerte von Euch zu
bekommen stoß halt grad erst so richtig in die Mysql Welt vor hab eher mich auf PHP konzentriert und ging BIS JETZT auch gut nur irgendwann
muss man halt beides gut beherrschen.


Ich hoffe Ihr könnt mir eure Erfahrung mitteilen ob jetzt das "normal" ist
und nach optimierungen und und und einfach auch Hardware aufrüsten muss irgendwann oder einfach meine Datenbank anscheinend nicht gut aufgebaut ist und ich das was dran machen muss.

[TBT]

Also wenn ich solche Queries sehe, verwundert es mich nicht, dass du deine völlig überdimensionierte Maschine so ins Aus bugsiert hast.
Beschäftige dich ganz, ganz dringend mit JOIN !!!

PS: auch für größere Shops stelle ich maximal einen Vserver mit 512 Ram und 2Ghz CPU bereit. Und diese machen dann auch noch Mailserver, etc

[tomtherock]

Outsch was für ein vernichtendes Urteil

mhhh also ich hatte am Anfang ja auch JOINS bzw benutze für die gleiche Abfrage ja auch JOINS nur sind die JOINS ab 250 rows halt langsamer als mit EXISTS
oder sind meine JOINS auch einfach nur kacke ?

Hier der gleiche query nur mit JOIN den ich unter 250 rows nutze:
(12 insgesamt, die Abfrage dauerte 0.2492 sek.) -> 1440 rows
mit EXISTS 0.05 sec bei 1440 rows unter 250 rows ist der JOIN schneller

SELECT *, t1.id_artikel as id_artikel 
FROM s_artikel as t1 
INNER JOIN s_zuweisung as t2 ON t1.id_artikel = t2.id_artikel
INNER JOIN s_bild as t3 ON t1.id_artikel = t3.id_artikel
WHERE t1.aktiv_gr = 1 
AND t1.aktiv = 1
AND t2.id_verwendung = '1'
AND t1.id_geschlecht IS NOT NULL
GROUP BY t1.id_artikel
ORDER BY t1.einstell_datum DESC,t1.views DESC,t1.last_update_steps DESC LIMIT 0, 12 

EXPLAIN:
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE t1 ref PRIMARY,aktiv,aktiv_gr,id_geschlecht aktiv_gr 4 const 2687 Using where; Using temporary; Using filesort 
1 SIMPLE t2 ref idx_id_artikel idx_id_artikel 8 usr_web1_1.t1.id_artikel 1 Using where 
1 SIMPLE t3 ref idx_id_artikel idx_id_artikel 8 usr_web1_1.t1.id_artikel 4 

[TBT]

SELECT *, t1.id_artikel as id_artikel 
FROM s_artikel as t1 
INNER JOIN s_zuweisung as t2
	ON ( t1.id_artikel = t2.id_artikel AND t2.id_verwendung = 1 )
INNER JOIN s_bild as t3
	ON ( t1.id_artikel = t3.id_artikel )
WHERE t1.aktiv_gr = 1 
	AND t1.aktiv = 1
	AND t1.id_geschlecht IS NOT NULL
GROUP BY t1.id_artikel
ORDER BY t1.einstell_datum DESC,t1.views DESC,t1.last_update_steps DESC
LIMIT 0, 12

mindestens Index auf
- t1.id_artikel
- t2.id_artikel, t2.id_verwendung (einer! über zwei spalten)
- t3.id_artikel

Bei t2.id_vwerwendung die ' weg, wenn es ein Int ist. Ansonsten muss
MySQL immer erst eine Typcast machen.

IS NOT NULL ist sehr schlecht! ändere das Feld auf einen NOT NULL Wert
wie Enum( w, m, u ) - weiblich,männlich,unbekannt - und frage dann nach != 'u'
ab. Natürlich kannste das auch mit 0,1,2 und Tinyint machen

[tomtherock]

super werd ich gleich mal austesten!
Das mit id_geschlecht hatte ich nur mal eben so ausgetestet hatte das
davor mit:
t1.id_geschlecht != ''

mhh also hab eigentlich auf alle drei Tabellen einen Index!
Bei tbl_artikel bin ich mir nicht klar weil das ja PRIMARY KEY ist wobei ich
gelesen hab das MySQL da keinen Unterschied macht oder doch?

tbl_artikel:
PRIMARY PRIMARY 4435 id_artikel

tbl_zuweisung:
idx_id_artikel INDEX 5162 id_artikel

tbl_bild:
idx_id_artikel INDEX 3455 id_artikel

index über 2 spalten? wie geht denn das im phpmyadmin?

-->> ah habs geschnallt! und gemacht

Ergebnis:
Zeige Datensätze 0 - 11 (12 insgesamt, die Abfrage dauerte 0.0317 sek.)

SELECT *, t1.id_artikel as id_artikel
FROM s_artikel as t1
INNER JOIN s_zuweisung as t2 ON t1.id_artikel = t2.id_artikel
INNER JOIN s_bild as t3 ON t1.id_artikel = t3.id_artikel
WHERE t1.aktiv_gr = 1
AND t1.aktiv = 1
AND t2.id_verwendung = 7
AND t1.id_geschlecht != ''
GROUP BY t1.id_artikel ORDER BY t1.einstell_datum DESC,t1.views DESC,t1.last_update_steps DESC LIMIT 0, 12 

heiland ZACK du bist mein Held hatte nicht gewusst das man auch über 2 spalten nen index legen kann! SUPER das hilft mir schon mal ungeheim weiter!!!! DICKEN SCHMATZER HEHE

[tomtherock]

oh mein Fehler muss leider das Ergebniss canceln hatte id_verwendung = 7

index auf 2 spalten in der zuweisung:
artikel_verwendung INDEX 5162 id_artikel
id_verwendung


Zeige Datensätze 0 - 11 (12 insgesamt, die Abfrage dauerte 0.2279 sek.)

SELECT *, t1.id_artikel as id_artikel
FROM s_artikel as t1
INNER JOIN s_zuweisung as t2 ON (t1.id_artikel = t2.id_artikel AND t2.id_verwendung = 1)
INNER JOIN s_bild as t3 ON t1.id_artikel = t3.id_artikel
WHERE t1.aktiv_gr = 1
AND t1.aktiv = 1
AND t1.id_geschlecht != ''
GROUP BY t1.id_artikel ORDER BY t1.einstell_datum DESC,t1.views DESC,t1.last_update_steps DESC LIMIT 0, 12 

hat leider nicht den erwünschten perfomance zuwachs gehabt ;(

[H2O]

Bei einem dreifachen JOIN kann SELECT * FROM tödlich sein

[tomtherock]

ah das war noch sehr guter Tipp so komm ich auch bei vielen rows an das Ergebnis von meiner EXISTS abfrage rann hab jetzt mal das * raus gemacht und nur so:

Zeige Datensätze 0 - 11 (12 insgesamt, die Abfrage dauerte 0.0521 sek.)

SELECT t1.id_artikel AS id_artikel, t1.titel
FROM s_artikel AS t1
INNER JOIN s_zuweisung AS t2 ON ( t1.id_artikel = t2.id_artikel
AND t2.id_verwendung =1 ) 
INNER JOIN s_bild AS t3 ON t1.id_artikel = t3.id_artikel
WHERE t1.aktiv_gr =1
AND t1.aktiv =1
AND t1.id_geschlecht != ''
GROUP BY t1.id_artikel
ORDER BY t1.einstell_datum DESC , t1.views DESC , t1.last_update_steps DESC 
LIMIT 0 , 12 

das sieht ja schon viel besser aus!!! Danke dir! Aber ob ich das noch mehr optimieren kann ?! Hab jetzt eigentlich keine Querys mehr die über 0.10 Sek ist also kann doch meine Datenbank-Struktur gar nicht so falsch sein oder?

[ghostgambler]

0.10 Sekunden für einen Query ist eigentlich immer noch zu langsam..
Aber so etwas optimiert man nicht "mal eben" im Forum. Da braucht es die genauen Queries, eventuell abhängig von den Datenbeständen. Die Konfigurationen mag man in dem Zug auch gleich mal überdenken, (vor allem wenn viele verschiedene Applikationen auf dem gleichen Server laufen, muss man doch schon etwas planen, wie man z.B. den Ram verfeuern will).

Entweder liest du dich in das Thema ein, oder du bezahlst einen (vernünftigen) Webprogrammierer dafür, der auch Ahnung von Optimierung hat (Programmieren und Optimieren ist was verschiedenes).
Wenn du dich einliest solltest du die Konfiguration zumindest ausreichend alleine hin kriegen und bei problematischen Queries kann man hier im Forum immer noch helfen -- aber einen Rundumschlag kannst du hier nicht erwarten zu erhalten~


Übrigens, was ist denn ein Detected Server? Du meinst wohl Dedicated?! x_X

[tomtherock]

ja meinte ich .

Ja klaro weiss ja das in sachen MySQL nicht wirklich der Totalblicker bin,
drum wollte ich ja hier erstmal Meinungen von denen Leuten einholen die
eben Ahnung davon haben.

Und das hab ich ja und das ihr alle meien Querys optimiert will ich gar nicht sonst
lern ich ja nix neues . Denke werd mir ein nette Buch besorgen speziell für MySql und Datenbank Design evtl. könntet ihr mir ja da ein gutes empfehlen was langsam einführt aber auch dann richtig ins detail geht einfach Rumum alles.

Mit der Konfiguration wird sich mein Administrator kümmer muss ihm halt
dann evtl. auch ein paar daten liefern na mal sehen, aber jedenfalls leuft
der server jetzt mit den neuen Index die ich davor gesetzt hab schon echt flott und kein Query von dem Shop kommt noch über 0,10 sek alle so um die 0.05 sek.

Das ganze summiert sich halt einfach...naja meld mich auf jedenfall noch
wenn ich nicht weiter komm :P.

Danke an Alle für die guten Tipps