php-resource



Zurück   PHP-Scripte PHP-Tutorials PHP-Jobs und vieles mehr > Entwicklung > SQL / Datenbanken
 

Login

 
eingeloggt bleiben
star Jetzt registrieren   star Passwort vergessen
 

 

 


SQL / Datenbanken Probleme mit SQL? Hier könnt ihr eure Fragen zu SQL (MySQL, PostgreSQL, MS-SQL und andere ANSI-SQL Server) los werden.

Antwort
 
LinkBack Themen-Optionen Thema bewerten
  #1 (permalink)  
Alt 23-11-2007, 14:27
tomtherock
 Registrierter Benutzer
Links : Onlinestatus : tomtherock ist offline
Registriert seit: Jun 2002
Ort: München
Beiträge: 334
tomtherock ist zur Zeit noch ein unbeschriebenes Blatt
Standard Tabellen Optimierung Perfomance Einschätzung Detected Server

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
PHP-Code:
(12 insgesamt, die Abfrage dauerte 0.0426 sek.)
SELECT *,t1.id_artikel as id_artikel FROM s_artikel as t1 WHERE t1.aktiv_gr AND t1.aktiv 
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 012 

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 whereUsing 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.
Mit Zitat antworten
  #2 (permalink)  
Alt 23-11-2007, 14:36
TBT
  Moderator
Links : Onlinestatus : TBT ist offline
Registriert seit: Sep 2002
Ort: Berlin
Beiträge: 2.787
TBT befindet sich auf einem aufstrebenden Ast
TBT eine Nachricht über ICQ schicken TBT eine Nachricht über AIM schicken TBT eine Nachricht über Yahoo! schicken
Standard

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
__________________
TBT

Die zwei wichtigsten Regeln für eine berufliche Karriere:
1. Verrate niemals alles was du weißt!


PHP 2 AllPatrizier II Browsergame
Mit Zitat antworten
  #3 (permalink)  
Alt 23-11-2007, 14:45
tomtherock
 Registrierter Benutzer
Links : Onlinestatus : tomtherock ist offline
Registriert seit: Jun 2002
Ort: München
Beiträge: 334
tomtherock ist zur Zeit noch ein unbeschriebenes Blatt
Standard

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
PHP-Code:
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 
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 012 

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 whereUsing temporaryUsing 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 

Geändert von tomtherock (23-11-2007 um 14:47 Uhr)
Mit Zitat antworten
  #4 (permalink)  
Alt 23-11-2007, 15:05
TBT
  Moderator
Links : Onlinestatus : TBT ist offline
Registriert seit: Sep 2002
Ort: Berlin
Beiträge: 2.787
TBT befindet sich auf einem aufstrebenden Ast
TBT eine Nachricht über ICQ schicken TBT eine Nachricht über AIM schicken TBT eine Nachricht über Yahoo! schicken
Standard

Code:
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
__________________
TBT

Die zwei wichtigsten Regeln für eine berufliche Karriere:
1. Verrate niemals alles was du weißt!


PHP 2 AllPatrizier II Browsergame
Mit Zitat antworten
  #5 (permalink)  
Alt 23-11-2007, 15:10
tomtherock
 Registrierter Benutzer
Links : Onlinestatus : tomtherock ist offline
Registriert seit: Jun 2002
Ort: München
Beiträge: 334
tomtherock ist zur Zeit noch ein unbeschriebenes Blatt
Standard

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.)
PHP-Code:
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 012 
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

Geändert von tomtherock (23-11-2007 um 15:20 Uhr)
Mit Zitat antworten
  #6 (permalink)  
Alt 23-11-2007, 15:30
tomtherock
 Registrierter Benutzer
Links : Onlinestatus : tomtherock ist offline
Registriert seit: Jun 2002
Ort: München
Beiträge: 334
tomtherock ist zur Zeit noch ein unbeschriebenes Blatt
Standard

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.)
PHP-Code:
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 012 
hat leider nicht den erwünschten perfomance zuwachs gehabt ;(
Mit Zitat antworten
  #7 (permalink)  
Alt 23-11-2007, 16:02
H2O
 PHP Junior
Links : Onlinestatus : H2O ist offline
Registriert seit: Jul 2007
Beiträge: 937
H2O ist zur Zeit noch ein unbeschriebenes Blatt
Standard

Bei einem dreifachen JOIN kann SELECT * FROM tödlich sein
__________________
Gruss
H2O
Mit Zitat antworten
  #8 (permalink)  
Alt 23-11-2007, 16:55
tomtherock
 Registrierter Benutzer
Links : Onlinestatus : tomtherock ist offline
Registriert seit: Jun 2002
Ort: München
Beiträge: 334
tomtherock ist zur Zeit noch ein unbeschriebenes Blatt
Standard

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.)
PHP-Code:
SELECT t1.id_artikel AS id_artikelt1.titel
FROM s_artikel 
AS t1
INNER JOIN s_zuweisung 
AS t2 ON t1.id_artikel t2.id_artikel
AND t2.id_verwendung =
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?
Mit Zitat antworten
  #9 (permalink)  
Alt 23-11-2007, 17:24
ghostgambler
 Master
Links : Onlinestatus : ghostgambler ist offline
Registriert seit: Jul 2004
Ort: DE - NRW
Beiträge: 4.620
ghostgambler ist zur Zeit noch ein unbeschriebenes Blatt
Standard

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
Mit Zitat antworten
  #10 (permalink)  
Alt 23-11-2007, 20:30
tomtherock
 Registrierter Benutzer
Links : Onlinestatus : tomtherock ist offline
Registriert seit: Jun 2002
Ort: München
Beiträge: 334
tomtherock ist zur Zeit noch ein unbeschriebenes Blatt
Standard

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
Mit Zitat antworten
Antwort

Lesezeichen


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
 

Themen-Optionen
Thema bewerten
Thema bewerten:

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge hochzuladen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are an


PHP News

ebiz-trader 7.5.0 mit PHP7 Unterstützung veröffentlicht
ebiz-trader 7.5.0 mit PHP7 Unterstützung veröffentlichtDie bekannte Marktplatzsoftware ebiz-trader ist in der Version 7.5.0 veröffentlicht worden.

28.05.2018 | Berni

Wissensbestand in Unternehmen
Wissensbestand in UnternehmenLebenslanges Lernen und Weiterbilden sichert Wissensbestand in Unternehmen

25.05.2018 | Berni


 

Aktuelle PHP Scripte

Top-Side Guestbook

Gästebuch auf Textbasis (kein MySQL nötig) mit Smilies, Ip Sperre (Zeit selbst einstellbar), Spamschutz, Captcha (Code-Eingabe), BB-Code, Hitcounter, Löschfunktion, Editierfunktion, Kommentarfunktion, Kürzung langer Wörter, Seiten- bzw. Blätterfunktion, V

22.10.2018 webmaster10 | Kategorie: PHP/ Gaestebuch
ebiz-trader 6.0 - Das professionelle PHP Marktplatz Script ansehen ebiz-trader 6.0 - Das professionelle PHP Marktplatz Script

Mit unserer Lösungen können Sie nahezu jeden B2B / B2C Marktplatz betreiben den Sie sich vorstellen können. Ganz egal ob Sie einen Automarktplatz, Immobilenportal oder einfach einen Anzeigenmarkt betreiben möchten. Mit ebiz-trader können Sie Ihre Anforder

11.10.2018 Berni | Kategorie: PHP/ Anzeigenmarkt
PHP Server Monitor

PHP Server Monitor ist ein Skript, das prüft, ob Ihre Websites und Server betriebsbereit sind.

11.09.2018 Berni | Kategorie: PHP/ Security
 Alle PHP Scripte anzeigen

Alle Zeitangaben in WEZ +2. Es ist jetzt 07:46 Uhr.