MySQL Konfiguration: Datenbankabfragen / Seitenaufrufe sehr langsam!

Status
Not open for further replies.
J

JanJanusch

New Member
Hallo,
ich kenne mich zwar mit PHP und MySQL aus, komme allerdings mit Performance-Problemen nicht zurecht :/

Vielleicht könnt ihr mir ein paar Tipps zur richtigen Einstellung der my.ini-Werte geben oder wie man die Abfragen optimieren (DB-Struktur bzw. SELECT-Befehle) könnte?

Hier noch die erforderlichen Angaben:
Website ist unter http://goo.gl/j8os4 erreichbar. Dort sind z.B. die folgenden Aufrufe sehr langsam (z.T. 1 min und mehr!):
1.) Suchbegriff oben eingeben
2.) Sender und dann Sendung rechts auswählen
3.) ein Film aufrufen
4.) TV Programm (rechts) aufrufen

Folgende Abfragen werden bei den o.g. Fällen durchgeführt:
1.) (Suche durchführen)
Code: 
"SELECT COUNT(*) FROM filme WHERE (titel LIKE '%".$qqq."%' OR sendung LIKE '%".$qqq."%') AND anbieter IN ($anbieterjoin) AND datum between '$z1q 00:00:00' and '$z2q 23:59:59' AND art IN ($typjoin)"
"SELECT * FROM filme WHERE (titel LIKE '%".$qqq."%' OR sendung LIKE '%".$qqq."%') AND anbieter IN ($anbieterjoin) AND datum between '$z1q 00:00:00' and '$z2q 23:59:59' AND art IN ($typjoin) ORDER BY datum DESC LIMIT ".$page_ab.",10;"
2.) (Sendung aufrufen)
Code: 
"SELECT COUNT(*) FROM filme WHERE sendung='$sendung' AND anbieter='$anbieter'"
"SELECT * FROM filme WHERE sendung='$sendung' AND anbieter='$anbieter' ORDER BY datum DESC LIMIT ".$page_ab.",15;"
3.) (Film aufrufen)
Code: 
"SELECT * FROM filme WHERE sendung='$sendung' AND anbieter='$anbieter' ORDER BY datum DESC LIMIT 0,5;"
+ weitere schnelle Abfragen (Kommentare & Statistiken)

4.) (TV-Programm aufrufen)
Code: 
"SELECT * FROM filme WHERE anbieter='$anbieter' AND art='$typa' AND datum between '$dr 00:00:00' and '$dr 23:59:59' ORDER BY datum ASC";
Nun zur Datenbankstruktur:
Hauptsächlich wird die DB "filme" verwendet. Auf die anderen (Kommentare, Login etc.) gehen ich nicht ein!
Die Struktur habe ich an diesen Post per Bild angehängt. Es befinden sich 200000 bis 500000 Einträge (~200MB) in der DB.

Zur my.ini:
Habe ich auch angehängt.

Server:
4GB RAM, WIN Server 2008 R2 Datacenter (64 Bit) + Plesk, zirka 2x 2GHz

Der Server ist sehr leistungsfähig im Vergleich zu den geringen Datenmengen - daran kann es kaum liegen. Trotzdem sind immer mehr "slow querys" im System...

Ich würde mich über nützliche Vorschläge freuen.
Vielen Dank!

==
Angehängte Dateien:
MOD: Bitte immer als Anhang hier im Forum. Danke!
 
Last edited by a moderator:
Baumi75 said:
Kann sein, dass die nötigen Indizes fehlen
Click to expand...

Vielen Dank für den Tipp ;)
Das habe ich auch schon gedacht. Daher habe ich ein paar wichtige Indizes erstellt. Diese lassen sich auch auf dem Screenshot erkennen.

Ich bin mir aber nicht sicher, ob die Indizes (siehe Screenshot) zu den Abfragen (siehe vorherigen Beitrag) optimal eingestellt sind...
 
Du brauchst mindestens noch Indizes auf titel, sendung und art. Ich hab nicht mehr im Kopf, ob es bei MySQL reicht, einen Index auf die Spalte zu packen (glaube ja), oder ob es so läuft wie bei Oracle, wo man zu meiner Zeit einen Index mit genau den gleichen Where-Klauseln in genau der gleichen Reighenfolge erstellen musste.

Ich tippe aber darauf, dass Einzel-Indizes reichen, denn ich hab' beim schnellen Suchen nichts über Indizes über mehr als eine Spalte in mysql gefunden.

Die Like-Klauseln fangen leider mit Prozent-Zeichen an, damit können die nicht vom Index profitieren.
 
Die ganze my.cnf ist eine Katastrophe, ebenso das Tabellenschema und die Queries.
Da ist 1 Minute für die Queries durchaus noch schnell.


Du brauchst Jemanden der das professionell für Dich löst, wird aber nicht billig.
Für ein Forum ist der nötige Zeit- und Arbeitsaufwand zum Lösen Deines Problems schlicht unverhältnismässig hoch, zumal Du mit Deiner Datenbank Geld verdienst und wir hier unbezahlte Hivis spielen sollen.
 
Joe User said:
Die ganze my.cnf ist eine Katastrophe, ebenso das Tabellenschema und die Queries.
Click to expand...

Warum Katastrophe? Welche Werte sind schlecht gesetzt und warum sind die Queries (stehen in Referenzen) nicht optimal? Welche Alternativen können verwendet werden?

Das Portal wird als Hobby (Schüler/Student) betrieben. Damit entstehen eher hohe Kosten...
Es wurde sehr viel Zeit investiert - viele Nutzer freuen sich über den kostenlosen Service.
Dagegen wäre eine kleine Hilfe (nur Tipps) von erfahrenden Nutzern sehr hilfreich!
 
Gut, dann nur Tips:
*) Update auf MySQL 5.5/5.6
*) Dokumentation lesen und verstehen
*) Datenbankschema komplett überarbeiten
*) App/Queries komplett überarbeiten
*) my.cnf komplett überarbeiten
*) Abschliessendes Finetuning

Abgesehen vom letzten Punkt können wir im Rahmen des Forums nicht helfen.
 
Joe User said:
Gut, dann nur Tips:
*) Update auf MySQL 5.5/5.6
*) Dokumentation lesen und verstehen
*) Datenbankschema komplett überarbeiten
*) App/Queries komplett überarbeiten
*) my.cnf komplett überarbeiten
*) Abschliessendes Finetuning

Abgesehen vom letzten Punkt können wir im Rahmen des Forums nicht helfen.
Click to expand...

Danke! Das ist ja schon mal ein Anfang ;)
Gibt es eine empfehlenswerte Dokumentation zur Optimierung (habe schon im Netz gesucht - aber nichts Spezielles (DB-Schema,Queries, my.cnf) gefunden)?

Das DB-Schema wurde doch eigentlich nach dem Standard erstellt:
Art der Spalte setzen und Indizes für die abgefragten Spalten erstellt???
 
Ich muss Joe leider zustimmen. Bzgl. der DB-Struktur und der damit verbundenen Abfragen muss das gesamte DB-Konzept völlig neu überarbeitet werden. Alle Filmbeiträge in eine Tabelle zu packen und darüber dann mit like und Platzhaltern zu suchen, ist der Performancekiller schlechthin. Das erzeugt einen gigantischen Suchlauf durch die gesamte Tabelle. Tödlich, weil da spielt die Version des DBMS keine Rolle mehr und auch nicht irgendwelche Cache- bzw. Konfig-Einstellungen, die bisher gesehenen Abfragen laufen auf harte CPU-Zeit hinaus, die zusätzlich vermutlich schlecht parallelisiert werden kann.

Fazit: Normalisierung praktisch nicht vorhanden. Optimierte und strukturierte Abfragen (z.B. Views) sind ebenfalls nicht erkennbar. Stattdessen wird ein Mega-Select brutal auf die Tabelle abgefeuert. Hier ist Konzept-Arbeit notwendig, d.h. Du benötigst erweitertes Know-how zu Datenbank-Strukturen und DBMS im allgemeinen sowie Kenntnisse über Such-/Sortieralgorithmen.

Im Gegensatz zu Joe tendiere ich aber dazu, dass hier durchaus Hilfe für das zukünftige Konzept geleistet werden kann. Aber dafür musst Du das gesamte Projekt offenlegen. So ein paar Screenshots reichen da nicht und das ist auch nicht in ein paar Tagen abgehändelt.
 
Last edited by a moderator:
Die offizielle MySQL-Doku ist der erste Anlaufpunkt und bereits recht umfangreich.

TEXT und BLOB will man um jeden Preis vermeiden, da diese grundsätzlich on-disk-Temptables erzeugen.
Wenn TEXT/BLOB dann niemals in den frequentiertesten Tabellen.
JOINs möchte man vermeiden, wenn möglich in die App-Logik verlagern.
Fulltablescans will man ebenfalls vermeiden.
"SELECT * ..." ist langsam und nahezu immer überflüssig.

Das war nur die Spitze des Eisbergs, denn in der my.cnf passt eigentlich gar nichts, da sind die Defaults teils um Längen besser.

Insgesamt eine Menge Arbeit, für Profis grob eine Woche, für Laien durchaus Monate.
 
Joe User said:
...
Wenn TEXT/BLOB dann niemals in den frequentiertesten Tabellen.
JOINs möchte man vermeiden, wenn möglich in die App-Logik verlagern.
Fulltablescans will man ebenfalls vermeiden.
"SELECT * ..." ist langsam und nahezu immer überflüssig...

Insgesamt eine Menge Arbeit, für Profis grob eine Woche, für Laien durchaus Monate.
Click to expand...
Yepp - das war die technische Beschreibung meiner Ausführungen. Der primäre Suchvorgang muss grundlegend vom Ablauf (Algorithmus) her überarbeitet werden.
 
Last edited by a moderator:
Terrorkarotte said:
Ein Anfang wäre es zumindest einen create table Befehl zu posten.
Click to expand...
Ich will es mal knallhart formulieren, das DB-Design hat das Niveau eines Kindergeburtstags will aber auf der großen Bühne spielen. Nope. Is nich. Hierzu müssen die Anforderungen des Projektes analysiert und draus ein Konzept entwickelt werden.
 
Terrorkarotte said:
Ein Anfang wäre es zumindest einen create table Befehl zu posten.
Click to expand...

Hier der Befehl:

Code: 
CREATE TABLE IF NOT EXISTS `filme` (
  `nummer` int(11) NOT NULL AUTO_INCREMENT,
  `anbieter` int(11) NOT NULL,
  `id` varchar(500) NOT NULL,
  `titel` text NOT NULL,
  `image` text NOT NULL,
  `beschreibung` text NOT NULL,
  `zeit` int(11) NOT NULL,
  `datum` datetime NOT NULL,
  `sendung` text NOT NULL,
  `download` text NOT NULL,
  `art` int(11) NOT NULL,
  PRIMARY KEY (`nummer`),
  KEY `id` (`id`),
  KEY `anbieter` (`anbieter`),
  KEY `datum` (`datum`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=181921 ;

Ich werde jetzt erst noch einmal die Dokumentationen durchforsten und hier auf Tipps warten ;)
 
JanJanusch said:
Vielen Dank für eure Tipps! Nachdem ich nun den Tabellen-Typ umgestellt habe und doch noch einen Fulltext-Index erstellen konnte, lädt das Portal deutlich schneller!
Click to expand...
Sorry, aber das ist auch nur ein Pflaster bzw. ein Tropfen auf den heißen Stein. Wenn der Aggregator-Dienst weiterhin so anwächst wie bisher, wird sich der "Kater" früher oder später wieder einstellen.

Trotz Deiner Erläuterungen gehe ich nach wie vor (ähnlich wie Joe) von einem kommerziellen Service aus, allein auf Grund der Tatsache, dass auf Grund der verwendeten Bilder/Snapshots entsprechende Lizenzabkommen mit den Sendeanstalten abgeschlossen sein müssen.

Fazit: Du kommst nicht umhin ein neues Konzept bzgl. Deiner Suchfunktion aufzustellen.
 
@all Das Konzept wird natürlich noch weiter verändert...
@Admin Problem wurde gelöst - Thread kann geschlossen werden.
 
Status
Not open for further replies.
Back
Top