Tabellen Optimierung Perfomance Einschätzung Detected Server

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • 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.

  • #2
    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

    Kommentar


    • #3
      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 
      Zuletzt geändert von tomtherock; 23.11.2007, 14:47.

      Kommentar


      • #4
        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

        Kommentar


        • #5
          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
          Zuletzt geändert von tomtherock; 23.11.2007, 15:20.

          Kommentar


          • #6
            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 ;(

            Kommentar


            • #7
              Bei einem dreifachen JOIN kann SELECT * FROM tödlich sein
              Gruss
              H2O

              Kommentar


              • #8
                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?

                Kommentar


                • #9
                  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

                  Ein netter Guide zum übersichtlichen Schreiben von PHP/MySQL-Code!

                  bei Klammersetzung bevorzuge ich jedoch die JavaCoding-Standards
                  Wie man Fragen richtig stellt

                  Kommentar


                  • #10
                    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

                    Kommentar

                    Lädt...
                    X