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

[franzcois]

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?

// 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;

[ChrisB]

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

[thomas_w]

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

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

[franzcois]

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

[thomas_w]

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

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)

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

CREATE INDEX sx_apache_log_user_agents_01 on apache_log_user_agents(id, bot);

Grüße
Thomas

[franzcois]

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?

[thomas_w]

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

[franzcois]

Beispiel: diese Abfrage braucht über 300 Sekunden:

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

[thomas_w]

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.

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

[thomas_w]

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