Ankündigung

Einklappen
Keine Ankündigung bisher.

[Erledigt] Geschwindigkeitsprobleme mit großer normalisierter Datenmenge (Apache Logs

Einklappen

Neue Werbung 2019

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

  • [Erledigt] Geschwindigkeitsprobleme mit großer normalisierter Datenmenge (Apache Logs

    Hallo,

    für die Auswertung des Traffics unseres Root-Servers via PHP und MySql habe ich die Apache Log-Dateien in eine MySql-Datenbank eingelesen und im Anschluss über anhängende Tabellen normalisiert.

    Diese Tabellen habe ich via INNER JOIN verbunden und in einen VIEW gepackt.

    Leider dauert die nachfolgende Abfrage auf das VIEW über 70 Sekunden:

    SELECT domain_id,domain,count(*) c
    FROM apache_logfiles
    WHERE 1 AND bot=0 AND zeitpunkt BETWEEN '2009-12-01 00:00:00' AND '2010-04-01 23:59:59'
    GROUP BY domain_id ORDER BY c DESC
    LIMIT 0,100

    Die Haupttabelle (apache_logs) enthält 5.623.973 Datensätze.

    Welche Möglichkeiten gibt es das ganze dramatisch zu optimieren?

    Code:
    // die Tabellen und die Anzahl der Datensätze
    apache_logs	5623973
    apache_log_domains	2739
    apache_log_hosts	1402
    apache_log_ips	334601
    apache_log_keywords	41924
    apache_log_path	31090
    apache_log_querys	883520
    apache_log_sites	1068692
    apache_log_user_agents	43680 
    
    // Der View mit den Joins
    CREATE VIEW apache_logfiles AS 
    SELECT l.*,ip.ip, 
      s.host_id,s.domain_id , s.path_id ,s.query_id, h.host,d.domain,p.path,q.query, 
      rh.id 'ref_host_id', rd.id 'ref_domain_id', rp.id 'ref_path_id', rq.id 'ref_query_id',
      rh.host 'ref_host',rd.domain 'ref_domain',rp .path 'ref_path',rq.query 'ref_query',
      k.id 'keyword_id',k.keyword, 
      ua.browser,ua.parent 'browser_version',ua.platform,ua.bot
    FROM apache_logs l 
    INNER JOIN apache_log_sites       s  ON s.id  = l.visit_site_id 
    INNER JOIN apache_log_user_agents ua ON ua.id = l.user_agent_id 
    INNER JOIN apache_log_ips         ip ON ip.id = l.ip_id
    INNER JOIN apache_log_sites       r  ON r.id  = l.referer_site_id 
    
    INNER JOIN apache_log_hosts       h  ON h.id  = s.host_id 
    INNER JOIN apache_log_domains     d  ON d.id  = s.domain_id 
    INNER JOIN apache_log_path        p  ON p.id  = s.path_id 
    INNER JOIN apache_log_querys      q  ON q.id  = s.query_id 
    
    INNER JOIN apache_log_hosts       rh ON rh.id = r.host_id 
    INNER JOIN apache_log_domains     rd ON rd.id = r.domain_id 
    INNER JOIN apache_log_path        rp ON rp.id = r.path_id 
    INNER JOIN apache_log_querys      rq ON rq.id = r.query_id 
    INNER JOIN apache_log_keywords    k  ON k.id  = r.keyword_id
    
    // Die Tabellen  
    CREATE TABLE `apache_logs` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `zeitpunkt` datetime NOT NULL default '0000-00-00 00:00:00',
      `visit_site_id` int(11) unsigned NOT NULL default '0',
      `referer_site_id` int(11) unsigned NOT NULL default '0',
      `user_agent_id` int(5) unsigned NOT NULL default '0',
      `ip_id` int(10) unsigned NOT NULL default '0',
      `method` varchar(4) NOT NULL default '',
      `status` tinyint(3) unsigned NOT NULL default '0',
      PRIMARY KEY  (`id`),
      KEY `zeitpunkt` (`zeitpunkt`),
      KEY `visit_site_id` (`visit_site_id`),
      KEY `referer_site_id` (`referer_site_id`),
      KEY `user_agent_id` (`user_agent_id`),
      KEY `ip_id` (`ip_id`),
      KEY `method` (`method`),
      KEY `status` (`status`)
    ) ENGINE=MyISAM AUTO_INCREMENT=5623974 DEFAULT CHARSET=latin1;
    
    CREATE TABLE `apache_log_domains` (
      `id` int(6) unsigned NOT NULL auto_increment,
      `domain` varchar(255) NOT NULL default '',
      `referer` tinyint(1) unsigned NOT NULL default '1',
      PRIMARY KEY  (`id`),
      UNIQUE KEY `domain` (`domain`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2740 DEFAULT CHARSET=latin1;
    
    CREATE TABLE `apache_log_hosts` (
      `id` int(6) unsigned NOT NULL auto_increment,
      `host` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`id`),
      UNIQUE KEY `host` (`host`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1403 DEFAULT CHARSET=latin1;
    
    CREATE TABLE `apache_log_ips` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `ip` varchar(15) NOT NULL default '',
      PRIMARY KEY  (`id`),
      UNIQUE KEY `ip` (`ip`)
    ) ENGINE=MyISAM AUTO_INCREMENT=334602 DEFAULT CHARSET=latin1;
    
    CREATE TABLE `apache_log_keywords` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `keyword` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`id`),
      UNIQUE KEY `keyword` (`keyword`)
    ) ENGINE=MyISAM AUTO_INCREMENT=41925 DEFAULT CHARSET=latin1;
    
    CREATE TABLE `apache_log_path` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `path` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`id`),
      KEY `path` (`path`)
    ) ENGINE=MyISAM AUTO_INCREMENT=45716 DEFAULT CHARSET=latin1;
    
    
    CREATE TABLE `apache_log_querys` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `query` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`id`),
      KEY `query` (`query`)
    ) ENGINE=MyISAM AUTO_INCREMENT=883521 DEFAULT CHARSET=latin1;
    
    
    
    CREATE TABLE `apache_log_sites` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `domain_id` int(11) unsigned NOT NULL default '0',
      `host_id` int(6) unsigned NOT NULL default '0',
      `path_id` int(11) unsigned NOT NULL default '0',
      `query_id` int(11) unsigned NOT NULL default '0',
      `keyword_id` int(11) unsigned NOT NULL default '0',
      PRIMARY KEY  (`id`),
      UNIQUE KEY `eindeutig` (`domain_id`,`host_id`,`path_id`,`query_id`),
      KEY `keyword` (`keyword_id`),
      KEY `domain_id` (`domain_id`),
      KEY `host_id` (`host_id`),
      KEY `query_id` (`query_id`),
      KEY `path_id` (`path_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1068693 DEFAULT CHARSET=latin1;
    
    CREATE TABLE `apache_log_user_agents` (
      `id` int(5) unsigned NOT NULL auto_increment,
      `user_agent` varchar(255) default NULL,
      `bot` tinyint(1) unsigned NOT NULL default '0',
      `browser` varchar(100) NOT NULL default '',
      `platform` varchar(50) NOT NULL default '',
      `parent` varchar(100) NOT NULL default '',
      PRIMARY KEY  (`id`),
      UNIQUE KEY `user_agent` (`user_agent`),
      KEY `Index 3` (`browser`),
      KEY `Index 4` (`platform`),
      KEY `Index 5` (`parent`)
    ) ENGINE=MyISAM AUTO_INCREMENT=43681 DEFAULT CHARSET=latin1;


  • #2
    Erster Schritt in solchen Fällen: Query mittels EXPLAIN analysieren lassen.

    Kommentar


    • #3
      Könntest Du den SQL mit EXPLAIN ausführen und das Ergebnis (am Beste als Bild wegen der Formatierung) hier wieder reinstellen.

      Code:
      EXPLAIN 
      SELECT domain_id,domain,count(*) c 
      FROM apache_logfiles 
      WHERE 1 AND bot=0 AND zeitpunkt BETWEEN '2009-12-01 00:00:00' AND '2010-04-01 23:59:59' 
      GROUP BY domain_id ORDER BY c DESC 
      LIMIT 0,100
      Grüße
      Thomas

      Kommentar


      • #4
        Hallo Chris,
        Hallo Thomas,

        anbei ein Screenshot vom Explain. Mir ist vorher schon aufgefallen, dass immer eine temporäre Tabelle erstellt wird, muss das so sein?

        HIER: Screenshot vom Explain (der Anhang geht nicht)

        Gruß Francois
        Angehängte Dateien

        Kommentar


        • #5
          Soweit ich sehe, ist auf der Spalte "bot" der Tabelle apache_log_user_agents kein Index.

          Code:
          CREATE INDEX sx_apache_log_user_agents_01 on apache_log_user_agents(bot);
          Wäre mal für einen ersten Test interessant. Auf der Tabelle apache_log_user_agents sind schon eine Menge anderer Index. Die Frage wäre, ob sich da was zusammenfassen läßt.

          Der Teil "1" in Deiner Abfrage ist natürlich auch sehr spannend (einfach mal weglassen)

          Code:
          ...
          FROM apache_logfiles 
          WHERE 1 AND ...
          ...
          Für Deine Abfrage benötigst Du eigentlich keine VIEW, sondern nur drei Tabellen, ohne die VIEW und direkt mit den drei Tabellen wird es auf jeden Fall schneller.

          EDIT - Create Index angepaßt (aufgrund des EXPLAIN)
          Code:
          CREATE INDEX sx_apache_log_user_agents_01 on apache_log_user_agents(id, bot);


          Grüße
          Thomas

          Kommentar


          • #6
            Das glaub ich jetzt nicht, die Abfrage braucht nur noch 7 Sekunden! Wow!

            "possible_keys" ist also als Vorschlag zur Optimierung zu sehen?

            Wenn in "Extra" nicht "Using index" steht, kann man dann auch noch was optimieren?

            Kommentar


            • #7
              In "possible_keys" sind die Indices aufgelistet, die eventuell in Betracht kommen, MySQL bei der Suche zu helfen. in "key" steht der Index, der dann wirklich verwendet wird. "Using index" in Extra bedeutet, dass MySQL nur den "Index" (und nicht die restlichen Spalten der Tabelle) benötigt, um die Abfrage auszuführen. Ein INDEX-Only Zugriff also. Wenn in "Extra" nichts steht, ist dass nicht unbedingt schlecht. Dazu sollte ich den SQL und den EXPLAIN dazu sehen.

              Grüße
              Thomas

              Kommentar


              • #8
                Beispiel: diese Abfrage braucht über 300 Sekunden:

                Code:
                select CONCAT(host_id,':',host,IF(host='','','.'),domain),count(*) c 
                from apache_logfiles 
                WHERE bot=0 AND zeitpunkt BETWEEN '2009-12-01 00:00:00' AND '2010-04-01 23:59:59' 
                AND domain_id=3
                group by host_id order by c DESC
                explain

                Kommentar


                • #9
                  Mal davon abgesehen, dass die Abfragen auf jeden Fall schneller wären, wenn Du nicht die VIEW, sondern nur die betroffenen Tabellen benutzen würdest, könntest Du ja mal die folgenden Indices testen.

                  Code:
                  CREATE INDEX sx_apache_log_sites_01 ON apache_log_sites (host_id, domain_id);
                  CREATE INDEX sx_apache_logs_01 ON apache_logs (visit_site_id, id);
                  Mir wird es aus der Entferung nicht klar, welcher Index (oder beide oder keinen) MySQL wählen wird.

                  Auf Deinen Tabellen sind inzwischen jede Menge Keys. Quasi fast auf jeder Spalte einer. Das Einfügen oder Löschen dürfte somit ziemlich langsam sein. Ziel sollte es sein, einige Index zusammen zu fassen und auf die wesentlichen/häufigen Queries zu optimieren.




                  Grüße
                  Thomas

                  Kommentar


                  • #10
                    Allgemeine Anmerkung:

                    Neben der Optimierung durch Indices ist natürlich auch die Parametrierung der MySQL Datenbank an sich wesentlich. Genügend RAM muss da sein und dieser auch der MySQL zugewiesen.

                    Grüße
                    Thomas

                    Kommentar

                    Lädt...
                    X