Mysql Grenzbereiche

[Simone]

HI,

Zur Situation

ca. 430MB Datenbank, Serverauslastung 200%

Das ganze nach ca. 6 Monaten und meine Server ging in die Knie keine Domain mehr erreichbar....

Die DB Strukturen (Abfragen) sind optimiert.
Der Inhalt der DB baut sich dynamisch auf (mit jedem Besuch ob Suchmaschine oder User)werden inhaltliche Verknüpfungen in der DB abgelegt. Suchanfragen laufen über einen Vorindex.
Inhalte beruhen auf eine Amazon API

Was kommt nach Mysql bzw. was ist leistungsfähiger ein anderen Server will ich mir nicht leisten.

Simone

[Dieter]

Hallo,

ca. 430MB Datenbank

430 MB ist für eine Datenbank eigentlich ein Witz, auch für MySQL.
Was wichtig bei einem Datenbankserver ist, dass dieser viel Ram hat, so dass die Tabellen _nicht_ von der Festplatte gelesen werden müssen.
Das heißt bei dir 430 MB + RAM für OS + RAM für Anwendungen.
Mit 1GB RAM solltest du also eigentlich ganz gut klar kommen.
Außerdem empfiehlt es sich, MySQL auf den neusten Stand zu bringen.

Die DB Strukturen (Abfragen) sind optimiert.

Bist du dir sicher, dass die Abfragen wirklich maximal optimiert sind?
Solltest du unter PHP auf die DB zugreifen, dann lassen sich per mysql_unbuffered_query() bei großen Abfragen noch viel Raushohlen.

Außerdem empfiehlt es sich hier, PHP als Apache Module (sofern Apache überhaupt verwendet wird) einzubinden und _nicht_ als CGI.

Schlechte PHP Scripts führen dazu, dass bei vielen Anfragen der freie RAM immer weniger wird und somit die Laufzeit deutlich ansteigt.
Gute PHP Scripts haben einen konstanten RAM bedarf und somit eine konstante Laufzeit.
Zum Beispiele manche schlechte Scripts haben unter Extrembedingungen zuerst eine Laufzeit von 0,1 Sekunden, doch mit der Zeit wird der freie RAM immer weniger (nimmt teilweise um über 150-200 MB ab) und somit steigt die Laufzeit auf über 3 Sekunden und mehr.
Gute Scripts, die die gleiche Aufgabe haben, verbrauchen nur rund 5-10 MB RAM und somit bleibt die Laufzeit auch konstant bei 0,1 Sek.
Es ist sehr intressant bei solch einem Benchmark die freie RAM-Kurve mit der durchschnittlichen Laufzeit zu vergleichen und man fatale Unterschiede zwischen guten und schlechten Scripts fest.

Auch sollte man die Überlegung anstellen, ob man nicht irgendwo Daten cachen kann.
Bei Foren/Boards kann man viel Rechenleistung sparen, wenn man den umgewandelten Beitrag (bb-codes etc. parsen) in einem Cache zum Beispiel für 10 Tage zwischenspeichert.

Was kommt nach Mysql bzw. was ist leistungsfähiger ein anderen Server will ich mir nicht leisten.

Es gibt weit aus größere Anwendungen die mit MySQL Arbeiten:

Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows.

MySQL

Sonst ist Oracle eigentlich bekannt dafür, mit sehr großen Datenbanken (wie gesagt, 400 MB ist eigentlich lächerlich, wenn du die Einheit in GB änderst bist du in der richtigen Dimension) zu arbeiten.

MFG

[Simone]

HI,

Danke für Deine Anregungen.

• Die Hardwarevoraussetzungen sollten passen.
• mysql_unbuffered_query() werde ich testen

Schlechte PHP Scripts führen dazu, dass bei vielen Anfragen der freie RAM immer weniger wird und somit die Laufzeit deutlich ansteigt.
Gute PHP Scripts haben einen konstanten RAM bedarf und somit eine konstante Laufzeit.
Zum Beispiele manche schlechte Scripts haben unter Extrembedingungen zuerst eine Laufzeit von 0,1 Sekunden, doch mit der Zeit wird der freie RAM immer weniger (nimmt teilweise um über 150-200 MB ab) und somit steigt die Laufzeit auf über 3 Sekunden und mehr.
Gute Scripts, die die gleiche Aufgabe haben, verbrauchen nur rund 5-10 MB RAM und somit bleibt die Laufzeit auch konstant bei 0,1 Sek.
Es ist sehr intressant bei solch einem Benchmark die freie RAM-Kurve mit der durchschnittlichen Laufzeit zu vergleichen und man fatale Unterschiede zwischen guten und schlechten Scripts fest.

Ich habe root Zugriff, dennoch bin ich "nicht so" der Serveradmin
Aus diesem Grund habe ich mich für einen managed Server entschieden.

Dein Ansatz finde ich gut. Wie bzw. Wo kann ich die RAM Kurve vergleichen?

Gibt es PHP Funktionen / eine Anleitung / Programm dafür?

Auch sollte man die Überlegung anstellen, ob man nicht irgendwo Daten cachen kann.

Ist schon online.

Es gibt weit aus größere Anwendungen die mit MySQL Arbeiten:

Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows.

Naja, und wenn dann richtig teuer (Hardware) und nur mySql Server

Simone

[Dieter]

Moin,

Dein Ansatz finde ich gut. Wie bzw. Wo kann ich die RAM Kurve vergleichen?

Zuerst sollte man dies auf dem eigenem Rechner machen, da der doch sehr beansprucht wird, deswegen entweder die DB clonen oder mit einer reduzierten DB arbeiten.

Solche Programme simulieren z.B. 100 Benutzer die jede Sekunden eine bestimmte URL aufrufen und dies über 15 Minuten hinweg.
Darüber hinaus messen die dann, wielange es dauert, bis man das 1 Byte als Antwort bekommt und wann das letzte Byte eintrifft.

Es gibt ein paar kommerzielle Programme dazu, z.B. Webserver Stress Tool für 170 Euro, wobei man die kostenlose Testversion nicht wirklich gebrauchen kann (zuwenig Aufrufe pro Sekunde).

Sonst kenn ich noch PHP Benchmark (benötigt das .Net Framework 1.1 oder höher), welches kostenlos ist, aber nur in der Version 0.1 vorliegt, sprich, es können/sind noch Fehler enthalten.
Der Bericht wird zusätzlich als HTML Report abgespeichert um später auch Werte vergleichen zu können und man kann auch einzelne Prozesse beobachten (Empfehlenswert apache und mysqld)

Sonst von MS gibts noch das Web Application Stress Tool, wobei das bei mir auf dem Rechner irgendwie nie richtig funktioniert hat.

Sonst gabs noch irgendwo ein Kommandozeilen Tool speziell für Apache, weiß aber nicht mehr wie dieser heißt.

MFG

[Frank (no reg)]

Hallo,

identifiziere, ob die Datenbank wirklich das Bottleneck ist oder ob es vielleicht nicht auch der Webserver selbst sein kann, der durch das verarbeiten von irgendwelchen PHP such-scripts zu stark unter Beschuss genommen wird.

Liegen MySQL und WebServer auf einer physikalischen Maschine? Wenn ja, dann solltest du überlegen, beides physikalisch zu trennen. Auch ein Hardware-Upgrade in Sachen CPU (Dual-Core, Quad-Core) und Ram (8 GB mit konstanter Alloziierung für Webserver/Datenbank/Betriebssystem [soweit möchlich]) solltest du in Betracht ziehen.

Und ausserdem, es gibt sicher noch Optimierungsmöglichkeiten auf Datenbankseite, du könntest zb denormalisierte Tabellen mit Indizes für häufig wiederkehrende Suchmuster einführen.

Wie sieht das Nutzungsszenario der DB aus? hauptsächlich lesend oder schreibend?

430 MB sind nicht wirklich viel, das sollte normalerweise alles fast im Arbeitsspeicher liegen (falls genügend vorhanden ist). Da scheint der Server hardwaremässig wirklich etwas schwach auf der Brust zu sein.

Optimize first .. then .. Scale Up or Scale out .. ;)

Grüsse
Frank

[Simone]

HI,

identifiziere, ob die Datenbank wirklich das Bottleneck ist oder ob es vielleicht nicht auch der Webserver selbst sein kann, der durch das verarbeiten von irgendwelchen PHP such-scripts zu stark unter Beschuss genommen wird.

Ich habe einen managed Server
AMD Athlon X2 BE23xx
1024MB DDR2 SDRAM
2 x 160GB S-ATA2
8 MB Cache

Dort laufen ca. 15 Domains
Traffic Monat August ca. 20GB

Liegen MySQL und WebServer auf einer physikalischen Maschine? Ja

denormalisierte Tabellen mit Indizes für häufig wiederkehrende Suchmuster

Gibt es dafür ein Beispiel?

Grüße
Simone

[Frank (no reg)]

Hi,

ist es ein dedizierter Server, also für dich allein? Oder musst du mit anderen teilen (shared\virtual hosting)?

denormalisierte Tabellen mit Indizes für häufig wiederkehrende Suchmuster

Gibt es dafür ein Beispiel?

Nein, ein konkretes pauschales Beispiel dafür kann man wohl kaum geben. Es ist zum Grossteil von einer Analyse der Vorgänge auf der Website abhängig. Ich versuche es aber noch mal zu erläutern:

häufig wiederkehrende Suchmuster

Mach ein Log oder Trace von den datenbanklastigen Operationen deiner Website (wonach gesucht wird: Felder, Werte), welche Queries werden oft ausgeführt usw

Danach schau ob du durch Covering Indices (also Indices, welche komplett die gesamte Abfrage alleine und ohne Rückgriff auf die eigentliche Tabelle erfüllen können).

denormalisierte Tabellen

wenn auch das obige nicht viel bringt versuche zusätzliche physikalische Tabellen zu generieren die ganz bestimmte Queries abdecken, indiziere diese ebenfalls nochmals gemäss den Suchargumenten

Was "denormalisiert" im Sinne von relationalen Datenbank bedeutet, ist dir aber bekannt?

Solche denormalisierten Tabellen werden zu einem beträchtlichen Anstieg der Datenbankgrösse führen (aber bei 160GB Platz ... who cares?) und du musst dir einen Mechanismus überlegen, wie du diese Tabellen aktuell hältst.

Gruss, Frank

[Simone]

Hi,
ist es ein dedizierter Server -- nein

$query1= "
   SELECT id , titel , nr , productgroup , rub , node , unter , date
   FROM titel
   WHERE 1 AND unter =  '".$select_sql."'
   ORDER BY  date DESC  LIMIT 0, 5   ";
   unset($content_help);
   $gruresult = @mysql_query($query1, $conn_ln);
   while ($gruRow = @mysql_fetch_array($gruresult))
   {

$query2= "
    SELECT key\_id , rub , keyword , unter , id , date , LOWER( LEFT(
    IF (
    (
    NOT FIELD( LEFT( keyword, 2 ) , 'AE', 'OE', 'UE' ) ) , keyword, CONCAT( ELT( FIELD( LEFT( keyword, 2 ) , 'AE', 'OE', 'UE' ) , 'Ä', 'Ö', 'Ü' ) , SUBSTRING( keyword, 3 ) ) ) , 2
    )
    ) AS firstletter
    FROM keywords
    WHERE FIND_IN_SET( '".$select_sql."', CONCAT_WS( ',', unter, id ) )
    GROUP BY firstletter
    HAVING firstletter
    REGEXP '^[1]+$' AND LENGTH( RTRIM( firstletter ) ) = '2'
    ORDER BY firstletter";

}

Einige "Schwachstellen" konnte ich jetzt ausmachen.

Vielleicht könnte ich $query1 und $query2 in einen Join legen. Auch der REGEXP ist nachteilig (Index)

Jedoch fehlt mir bis jetzt der Blick wie ich die beiden Tabellen verbinden könnte und auf den REGEXP verzichten könnte.

Simone

---

1. [:alnum:] ↩︎

[Dieter]

Tach,
dein Code weist einige Schwächen auf.

1. mysql_fetch_array sollte man nicht verwenden, sondern lieber mysql_fetch_assoc oder noch schneller mysql_fetch_row
Siehe php Manual => mysql_fetch_array => Kommentar von robjohnson at black-hole dot com

2. WHERE 1 macht keinen Sinn/ist überflüssig

3. In wiefern wird $query1 für $query2 benötigt?
Eine SQL Abfrage in einer Schleife ist eh Problematisch und JOINs wirken Wunder.
Auch REGEXP sind eigentlich ein No-Go.
Wenn ich das richtig sehe, überprüfst du ob firstletter nur aus Buchstaben&Zahlen besteht und ob firstletter 2 Zeichen lang ist.

Sollte diese Anfrage oft ausgeführt werden, könnte man beim Speichern in 'keywords' dieses gleich überprüfen.
Du erstellst ein (indiziertes) tinyint Feld.
Wenn die Länge von firstletter 2 ist und beide Buchstaben Alphanumerisch ist, dann setzt du den Wert auf 1 sonst bleibt er 0.

Beim Abfragen dann einfach nur noch:
SELECT .. FROM keywords WHERE bedingung = 1

4. Auch Sachen wie if und und lower sind sehr problematisch.
Wenn du die Kriterien einschränken kannst, ist es meistens besser beim Speichern diese zu überprüfen und dann entsprechende bool Felder zu verwenden ob die Bedingung erfüllt ist oder nicht.

So muss pro Datensatz nur 1 mal die Bedingung überprüft werden (beim Speichern) und nicht immer wieder bei jeder Abfrage.

MFG

[Ilja]

yo,

Du erstellst ein (indiziertes) tinyint Feld.
Wenn die Länge von firstletter 2 ist und beide Buchstaben Alphanumerisch ist, dann setzt du den Wert auf 1 sonst bleibt er 0.

das würde ich nur verwenden, wenn sich nicht andere optimuerungsmöglichkeiten finden lassen, die zum gewünschten erfolg führen, dass durch prozessdaten immer die gafahr der inkonsistenz besteht.

4. Auch Sachen wie if und und lower sind sehr problematisch.

in diesem falle unbroblematisch, da sie nicht für deie Selektion benutzt werden, sondern in der projektion.

Ilja

[Ilja]

yo,

als ergänzung zu dem bereits gesagten, noch folgende anmerkungen von mir:

die zweite abfrage aus der schleife rauszunehmen und miteinander zu kombinieren erscheint mir ebenfalls als sehr sinnvoll, ja nach design und gewünschter ergebnismenge würden sich neben joins noch unterabfragen anbieten.

deine zweite abfrage würde bei jedem anderen dbms eine fehlermeldung ausgeben, es ist die "alte" problematik mit mysql und der implementierung von group by.

ich kann den sinn für die verwendung von having bei deiner abfrage nicht erkennen, bzw. warum du überhaupt gruppierst.

wenn ich mich nicht ganz täusche, dann brauchst du nach einer gruppierten spalten nicht noch zusätzlich unter mysql sortieren, da bin ich mir aber nicht sicher.

des weiteren solltest du die abfragen zuerst immer unabhängig von dem php umfeld testen, zum beispiel über phpmyadmin oder aber wenn möglich über eine lokale testdatenbank. es macht immer sinn sinn, sich mal den ausführungsplan der beiden abfragen anzuschauen, vermutlich wird zumindestens in der zweite abfrage es zu fullscans kommen und das dann noch durch die schleife mehrmals (5x) ausgeführt.

wenn du willst kannst du ja mal einen ausschnitt deines daten-designs geben und sagen, welche ergebnismengen du haben willst, am besten immer mit beispiel-datensätzen. dann kann man die abfrage optimieren.

Ilja