SQL-Statement - Performance schlecht!

[DaWutz]

Hallo zusammen,

Ich hab hier ein "nettes" SQL-Statement, das leider mittlerweile alles andere als performant läuft, vielleicht hat ja jemand eine Idee das ganze wie das ganze umzuschreiben ist. Ich hab schon ein paar Variationen versucht, aber es wird nicht besser.

Hier das Statement:

SELECT  HIGH_PRIORITY
   c.customer_name AS  'customer',
   c.customer_freevolume AS  'freevolume',
   c.customer_price_per_gb AS  'pricePerGB',
   c.customer_currency AS  'currency',
   ROUND( ( ( SUM( d.data_recievedbytes )  + sum( d.data_sendbytes )  )  /1073741824 ), 3 ) AS  'traffic',
   ROUND( ( ( SUM( d.data_recievedbytes )  + sum( d.data_sendbytes )  )  /1073741824 ), 3 ) - c.customer_freevolume AS  'billedTraffic'
FROM billing_data d, billing_customer c
WHERE data_starttime LIKE  '200510%'
AND MID( d.data_site, 3, 3  )  = c.customer_short
GROUP  BY c.customer_name

Bei mir läuft das ganze auf einem P4 1700 1GB RAM und ca 3,8Mio Datensätze satte 38 Sekunden. Das ganze basiert auf MySQL 4.x. MySQL deshalb weil keine Transaktionen etc, die Daten sind mehr oder weniger statisch, es werden lediglich einmal pro Nacht Logfiles ausgewertet und hinzugefügt.

Irgendeine Idee das ganze zu beschleunigen?

Grüße aus Hameln!

[flaite]

Hi,

Hier ein paar Vorschläge:

Zunächst einmal solltest du prüfen, ob es auf die im join betroffenen Attribute (Spalten) der Tupel Relationen (ts, ts, ts  ) (Tabellen) Indexe in der Datenbank gibt. Hier ist die Syntax aus dem mySQL Handbook.
http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Wenige updates sind aber ein klarer Indikator dafür, direkt einen Cache einzubauen.
Einen solchen Cache kannst du a) in der Anwendung und b) eventuell in der Datenbank implementieren (weiss nicht obs bei mySQL da so gute Features gibt).

In der Anwendung könntest du das Resultat in einer globalen Variable speichern. Sowas geht z.B. in Notes nicht, aber z.B. in servlet/jsp schon. Mit welcher Sprache greifst du darauf zu?

In Java könnte das sehr vereinfacht so aussehen:

Code

Map cacheMap;

Data getData (String abfrageParameter) {
Data cacheObject = cacheMap.get(abfrageParameter); 

if ( cacheObject== null)  {

  cacheObject = doRealQuery(abfrageParameter); 
   cacheMap.put(abfrageParameter, cacheObject); 
   }

return cacheObject;
}

Die Abfrage geht also zuerst gegen das anwendungsglobale cache Objekt und erst dann gegen die Datenbank, wenn dieses anwendungsglobale Cache-Objekt für den jeweiligen Abfrageparameter (falls es das überhaupt gibt) keinen Inhalt hat. (Map in Java ist sowas wie List in LotusScript).

Du kannst dann festlegen, dass der cache alle 12 Stunden oder so gelehrt wird, so dass aktuelle Daten irgendwann zum User gelangen.
Man kann das natürlich smarter machen, indem ein Prozess überprüft, ob sich das aktuelle Resultset von den Cache-Daten unterscheidet. Wenn nicht, wird der cache nicht gelöscht. Einfach zu managen sind solche Dinge nicht.

Bzgl. Datenbank-Caching existieren AFAIK in anderen Datenbanken bestimmte view-Typen, die das Ergebnis halten. Glaub aber nicht, dass es dies für MySql gibt.
Dafür kann dieses Feature möglicherweise interessant sein: (Query Cache) http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

[flaite]

Wenn du dir erstmal nicht anders zu helfen weisst, kannst du das natürlich auch alles in eine Spezialtabelle schauffeln. Auf die setzt du jeden Abend ein delete ab und direkt danach wieder ein insert. Nur würde das gegen jegliche Normalisierung von Daten sprechen (eine Grund-Idee von Relationalen Datenbanken).
Auf welcher Plattform / Programmiersprache greifst du drauf zu ?

Ich will hier darauf hinweisen, dass die openSource Frameworks mit denen ich auf Java arbeite (Ibatis SQLMaps und Hibernate) für genau diese Anforderungen caches schon im Framework dabeihaben (in hibernate ist es der 2nd level cache und in ibatis einfach der cache). Es gibt zusätzlich sogar kommerzielle und eine openSource Lösung von JBoss, die solche caches auch durch Replizierung clusterfähig machen. Die kommerzielle Lösung (tangosol) arbeitet z.Zt. an wide area caches. D.h. caches, deren geclusterte Server über den Erdball verstreut sind. Entity EJBs haben auch solche Caches schon dabei.

Gruß Axel

[DaWutz]

Hallo Axel,

die Daten liegen auf einem Suse 8 Enterprise und werden über ein Webinterface (PHP) abgerufen.

Die Kombo PHP/MySQL deshalb, weil dort auch unser "normaler" Intranet-Webserver drüber läuft und da hat es sich angeboten, die Sache dort mit zu implementieren. Ist auch eigentlich nciht weiter schlimm, da die DB ja relativ statisch ist, sprich die Werte ändern sich nur einmal am Tag. (Ich wiederhole mich da aber.)

Die Links helfen mir leider auch nicht, da dies auf Version 5.x abzieht, ich hier aber nur 4.0.x zur Verfügung habe. Die Sache mit den temporären Tabellen habe ich mir auch schon überlegt, aber wieder verworfen, da ich ja denn für jeden Fall der Abfrage eine entsprechende Tabelle nachts generieren müsste. Das ist aber, wie Du schon erwähnt hast, aber eigentlich Humbug und widerspricht jeglicher "DB-Ethik".

Ich habe mittlerweile auch noch ein paar Sekunden "rausgekitzelt" indem ich andere Indizes gesetzt habe und die Tabelle mittles "Alter table" umsortiert habe. Aber das kann es noch nicht so ganz sein, ich denke da läßt sich noch einiges feilen. Ich bleibe aber am Ball...

[flaite]

Es sollte in php eine Möglichkeit geben, cache Daten im globalen Speicher der Anwendung zu halten?
Wenn ich php cache in google eingebe, bekomme ich Ergebnisse. Weiss aber nicht, ob es das ist was ich meine. Ich hab von php wenig bis keine Ahnung.

Wenn es einen Anwendungsfall für einen Applikationscache gibt, dann dieser Fall: komplexer Query, Daten werden selten und offenbar festgesetzten Zeitpunkten upgedated.

Vielleicht gibt es diesen QueryCache doch schon in mySql 4.0 


Axel

[DaWutz]

Vielleicht gibt es diesen QueryCache doch schon in mySql 4.0  Huh

Du hast natürlich recht, den QueryCache gibts schon. Ich habe nun mal ein wenig mit den Werten dieser Parameter gespielt, und habe recht ansehnlich Ergebnisse erzielt. Ich werde das aber morgen noch ein wenig weiter austesten müssen, um zu sehen, ob miene Ergebnisse nun auch aussagekräftig sind.

Bis morgen,

Daniel!

[flaite]

Cool 
Wie viele Tupel (Datensätze) sind eigentlich in dem Resultset?
Wenn das sehr viele sind, ist vielleicht ein Anwendungs-Cache auch nicht das wahre, weil das dann vielleicht einfach zu viel vom Arbeitsspeicher wegnimmt.

Ich bin da auch nicht so der super-guru. Nur das erschien mir schon ein Fall für einen cache auf die eine oder andere Weise. Damit kann man die wirklich dicken Performance-Gewinne erzielen. Problematisch sind caches natürlich bei vielen updates.   

[DaWutz]

Zwischenstand:
Verbesserung von bisher 39 auf 10 Sekunden! 

Wodurch:

• Ich hab das "böse" LIKE ersetzt durch BETWEEN - grenzt meinen Bereich weiter ein und scheint dadurch gleich drastisch performanter zu sein.
• Ich habe 2 neue Indexe auf die benutzten Spalte der Tabelle gesetzt
• Ein paar Drehungen an den Parametern query_cache_limit, query_cache_size
• Neuordnen der Tabelle durch "ALTER TABLE billing_data ORDER BY data_starttime, data_site"

Nun werde ich das ganze mal weiter beobachten und schauen was weiter passiert.

Ach ja, das Resultset hat eigentlich nur 15 Tupel, aber die für die Berechnung - das SUM und ROUND usw. - werden pro Monat 285000 Tupel durchforstet.

Edit:
Hier das "neue" Statement:

Code

SELECT HIGH_PRIORITY
	c.customer_name AS 'customer',
	c.customer_freevolume AS 'freevolume',
	c.customer_price_per_gb AS 'pricePerGB',
	c.customer_currency AS 'currency',
	ROUND(((SUM(d.data_recievedbytes) + sum(d.data_sendbytes))/1073741824),3) AS 'traffic',
	ROUND(((SUM(d.data_recievedbytes) + sum(d.data_sendbytes))/1073741824),3) - c.customer_freevolume AS 'billedTraffic'
FROM billing_data d, billing_customer c
WHERE d.data_starttime BETWEEN '20051001000000' AND '20051031235959'
	AND MID(d.data_site,3,3) = c.customer_short
GROUP BY c.customer_name

[flaite]

Aber noch mal: Kann man den Inhalt des Resultsets  nicht in der PHP Anwendung als globales Objekt speichern? Zumindest in php 5 scheint es so etwas zu geben:
http://www.sitepoint.com/forums/showthread.php?t=305737
Oder/und der global scope von dem die hier reden:
http://www.webreference.com/programming/phpanth2/3.html
Das scheint doch so etwas zu sein. Du brauchst noch nicht mal unbedingt Objekte (natürlich besser). EinfachSchwieriger das Resultset als 2-dimensionalen Array speichern?
Diesen Array bei dem ersten Request am Tag auf null setzen und neu aus der Datenbank füllen?
Alle Requests des Tages nach dem ersten holen sich die Daten aus dem globalen Objekt und nicht aus der Datenbank.

Falls es keinen scope für anwendungs-globale Objekte/Arrays in php gibt (was ich nicht glaube), kannst du dir sogar überlegen, die 15 Tupel in einer Datei zwischenzuspeichern. Das ist zwar auch unschön, aber besser als den User jedesmal 10 sek. warten lassen, wenn er drauf zugreift.

Axel