Problem mit Group By Performance

Hallo,
folgende Abfrage bereitet mir Kummer:

SELECT
LEFT(TrmDateTime, 7) as "Month",
COUNT(*) AS "Transactions (Number)",
FORMAT(SUM(amount),2) AS "Transactions (EUR)",
COUNT(CASE WHEN matchesfraud =3D 1 THEN 1 ELSE null END) AS "Fraud
(Number)",
FORMAT(SUM(CASE WHEN matchesfraud =3D 1 THEN amount ELSE null END),2)
AS "Fraud (EUR)"
FROM
epfp2_all
GROUP BY LEFT(TrmDateTime, 7)
ORDER BY NULL;


efp2_all ist ein View auf mehrere Tabellen, in denen monatsweise
Transaktionen abgelegt sind. In jeder dieser Tabellen liegt ein Index
auf TrmDateTime (einer Datumsspalte). Insgesamt sind ca. 30 Millionen
Datensätze in diesen Tabellen.

Die Anzahl der Ergebniszeilen ist sechs, da nur sechs Monate
vorgehalten sind.

Nun geht meine Datenbank (MySQL 5.0.18 auf Windows XP, Single Opteron
2GHz, 3GB RAM) derart vor, daß es zunächst eine temporäre Tabelle
anlegt und dorthin eine Menge Daten kopiert. Das dauert natürlich
Ewigkeiten und ich bekam dann irgendwann eine Fehlermeldung, der Index
auf dieser temporären Tabelle sei kaputt.

Nun frage ich mich: Muß das sein? Eigentlich sollte ein einfacher
Table Scan doch ausreichen. Genau das machen nämlich Oracle und DB2
bei dieser Abfrage, das habe ich schon probiert. Gibt es eine
Möglichkeit meine obige Abfrage umzuformulieren, so daß ich mit einem
einfachen Tablescan und ohne die temporäre Tabelle auskomme?

Ach ja, was ich schon ausprobiert habe:
- Im View eine zusätzliche Spalte "Monat" mit Definition
LEFT(TrmDateTime, 7) angelegt und darüber gruppiert.
- Statt "LEFT(TrmDateTime, 7)" habe ich "DATE(TrmDateTime) verwendet
- SQL_SMALL_RESULT verwendet

... allles ohne Erfolg.

Wer hat Ideen? Danke.


Grüße, Nikolaus

Re: Problem mit Group By Performance

Nikolaus D. Bayer schrieb:
> Hallo,
> folgende Abfrage bereitet mir Kummer:
>
> SELECT
> LEFT(TrmDateTime, 7) as "Month",
> COUNT(*) AS "Transactions (Number)",
> FORMAT(SUM(amount),2) AS "Transactions (EUR)",
> COUNT(CASE WHEN matchesfraud = 1 THEN 1 ELSE null END) AS "Fraud
> (Number)",
> FORMAT(SUM(CASE WHEN matchesfraud = 1 THEN amount ELSE null END),2)
> AS "Fraud (EUR)"
> FROM
> epfp2_all
> GROUP BY LEFT(TrmDateTime, 7)
> ORDER BY NULL;


Zeige bitte den Explain des Statements. Daran kann man dann erkennen,
welche Daten in der temporären Tabelle landen.

Grüße

Dirk

Re: Problem mit Group By Performance

Nikolaus D. Bayer wrote:
> Hallo,
> folgende Abfrage bereitet mir Kummer:

..
> GROUP BY LEFT(TrmDateTime, 7)
..

> Nun geht meine Datenbank (...) derart vor, daß es zunächst eine=20
> temporäre Tabelle anlegt und dorthin eine Menge Daten kopiert.

da über einen nicht indizieten (und auch nicht indizierbaren)
Wert gruppiert werden soll ist das auch kein Wunder, die temporäre
Tabelle wird hier benötigt um die Ergebnisse zunächst zu sortieren
und dann zusammenzufassen.

> Das dauert natürlich
> Ewigkeiten und ich bekam dann irgendwann eine Fehlermeldung, der Index
> auf dieser temporären Tabelle sei kaputt.

Welche Fehlermeldung genau?

> Nun frage ich mich: Muß das sein? Eigentlich sollte ein einfacher
> Table Scan doch ausreichen. Genau das machen nämlich Oracle und DB2
> bei dieser Abfrage, das habe ich schon probiert.=20

auch Oracle und DB2 werden die Ergebnissmenge irgendwie gruppenweise
zusammenfassen müssen ...

> Gibt es eine
> Möglichkeit meine obige Abfrage umzuformulieren, so daß ich mit ein=
em
> einfachen Tablescan und ohne die temporäre Tabelle auskomme?

Da du nach Jahr und Monat, nicht nach dem vollen Datum, gruppieren
möchtests solltest du diese beiden Felder explizit anlegen und
einen Index darüber legen, also in der Tabelle zusätzlich

TrmYear INT,
TrmMonth INT,

anlegen und pflegen, Index drüber:

ALTER TABLE ... ADD INDEX (TrmYear, TrmMonth)

und dann

GROUP BY TrmYear, TrmMonth

dann sollte es auch ohne temporäre Tabelle gehen




--=20
Hartmut Holzgraefe, Senior Support Engineer .
MySQL AB, www.mysql.com

http://www.mysql.com/support/

Re: Problem mit Group By Performance

>> Das dauert natuerlich
>> Ewigkeiten und ich bekam dann irgendwann eine Fehlermeldung, der Index
>> auf dieser temporaeren Tabelle sei kaputt.
>
>
> Welche Fehlermeldung genau?

Error: java.sql.SQLException: General error message from server:
"Incorrect key file for table 'C:\WINNT\TEMP\#sql_fcc_1.MYI'; try to
repair it", SQL State: S1000, Error Code: 126
Error occured in:

SELECT
LEFT(TrmDateTime, 7) as "Month",
[..]


Ich probiere das jetzt mal mit den zusätzlichen Feldern TrmYear und
TrmDate, hoffentlich bringt das was. Danke soweit.

Gruesse, Nikolaus

Re: Problem mit Group By Performance

> Zeige bitte den Explain des Statements. Daran kann man dann erkennen,
> welche Daten in der temporären Tabelle landen.

Das habe ich schon versucht, aber nie durchlaufen lassen, denn es
dauerte zu lang. Die Datenbank gibt mir nämlich keine schnelle
Antwort, sondern fängt anscheinend an, das Select-Statement an sich
auszuführen. Ich könnte das mal über Nacht laufen lassen.

Wenn ich den gleichen Befehl mit EXPLAIN absetze, aber nicht auf den
View epfp2_all gehe, sondern auf die kleinste drunterliegende Tabelle,
dann kommt das:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE epfp2_200505 ALL 70638 Using
temporary


Gruesse, Nikolaus

Re: Problem mit Group By Performance

"Nikolaus D. Bayer" wrote:
>>> Das dauert natuerlich
>>> Ewigkeiten und ich bekam dann irgendwann eine Fehlermeldung, der Index
>>> auf dieser temporaeren Tabelle sei kaputt.
>>
>> Welche Fehlermeldung genau?
>
> Error: java.sql.SQLException: General error message from server:
> "Incorrect key file for table 'C:\WINNT\TEMP\#sql_fcc_1.MYI'; try to
> repair it", SQL State: S1000, Error Code: 126

Das ist schräg. Gibts auf deiner Maschine irgendwelche Programme,
die in %TEMP% löschen? Oder ist das vollgelaufen? Vielleicht ist
es gut, mysqld sein Tempdir in my.cnf explizit zu setzen.
Irgendwohin wo Platz ist. Und schnell.


XL

Re: Problem mit Group By Performance

On Tue, 07 Feb 2006 04:43:28 -0800, Nikolaus D. Bayer wrote:

> Error: java.sql.SQLException: General error message from server:
> "Incorrect key file for table 'C:\WINNT\TEMP\#sql_fcc_1.MYI'; try to
> repair it", SQL State: S1000, Error Code: 126 Error occured in:

2Gb Grenze? Kein Platz mehr?

Gruß,
Joachim

--
Warnung: \" kann Augenkrebs verursachen.

Re: Problem mit Group By Performance

On Tue, 07 Feb 2006 03:21:16 -0800, Nikolaus D. Bayer wrote:

> efp2_all ist ein View auf mehrere Tabellen, in denen monatsweise
> Transaktionen abgelegt sind. In jeder dieser Tabellen liegt ein Index auf
> TrmDateTime (einer Datumsspalte). Insgesamt sind ca. 30 Millionen
> Datensätze in diesen Tabellen.

Wie sieht die View aus? UNION oder UNION ALL?

Gruß,
Joachim

--
Warnung: \" kann Augenkrebs verursachen.

Re: Problem mit Group By Performance

Hi!

>> On Tue, 07 Feb 2006 04:43:28 -0800, Nikolaus D. Bayer wrote:
>> Error: java.sql.SQLException: General error message from server:
>> "Incorrect key file for table 'C:\WINNT\TEMP\#sql_fcc_1.MYI'; try to
>> repair it", SQL State: S1000, Error Code: 126 Error occured in:
>
> 2Gb Grenze? Kein Platz mehr?

Plattenplatz ist auf jeden Fall noch da. Die Indexdateien der Tabellen,
aus denen der View aufgebaut ist, haben zusammen aktuell eine Größe
von 1,23GB.

Was hat es mit der "2Gb Grenze" auf sich? Laufe ich hier ggf. gegen ein
Limit? Ich kann mich natürlich mal hinsetzen und beobachten, wie groß
die Indexdatei wird. Aber es ist doch sehr unwahrscheinlich, daß der
Index der temporären Tabelle größer als der der Quelltabellen wird,
oder?

Grüße, Nikolaus

Re: Problem mit Group By Performance

Hallo,
habe Deinen Vorschlag befolgt und die zusätzlichen Felder angelegt.
Aber geändert hat das nix, es dauert weiterhin ewig und irgendwann
kommt die erwähnte Index-Fehlermeldung.

Interessanterweise funktioniert EXPLAIN SELECT auf eine der
Basistabellen in Sekundenbruchteilen, während EXPLAIN SELECT auf den
erwähnten View über die Basistabellen) nicht sofort eine Antwort
liefert. Bei dieser Abfrage sieht es eher so aus, als würde zunächst
das Select ausgeführt (Festplatte rotiert ohne Ende, temporäre
Tabelle wird angelegt,...).

Mittlerweile bin ich allerdings über dieses gestolpert:

http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.htm l,
hier heißt es " View processing is not optimized [...]".

Insbesondere heißt es, daß bei den Views keine Indizes benutzt
werden, wenn der temptable-Algorithmus verwendet wird.

Ich versuche nun, einfach eine MERGE-Tabelle zu erstellen, daß müsste
ja theoretisch gehen, da meine Basistabellen alle gleich definiert
sind. Da gibt es wieder Probleme, aber das ist ein anderer Thread...

Grüße, Nikolaus

Re: Problem mit Group By Performance

> On Tue, 07 Feb 2006 03:21:16 -0800, Nikolaus D. Bayer wrote:
>> efp2_all ist ein View auf mehrere Tabellen, in denen monatsweise
>> Transaktionen abgelegt sind. In jeder dieser Tabellen liegt ein Index auf
>> TrmDateTime (einer Datumsspalte). Insgesamt sind ca. 30 Millionen
>> Datensätze in diesen Tabellen.
>
> Wie sieht die View aus? UNION oder UNION ALL?

Habe bisher nur "UNION" verwendet:

CREATE VIEW epfp2_all AS
SELECT
PROJECT, REVISION, [...]
FROM epfp2_200506
UNION
SELECT
PROJECT, REVISION, [...]
FROM epfp2_200507
UNION
[..];

Kann gerne mal ausprobieren, ob UNION ALL was bringt, allerdings ist da
ja http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.htm l und "
View processing is not optimized [...]" (siehe anderes Posting von mir
von heute).

Grüße, Nikolaus

Re: Problem mit Group By Performance

Nikolaus D. Bayer wrote:

>>>efp2_all ist ein View auf mehrere Tabellen, in denen monatsweise
>>>Transaktionen abgelegt sind. In jeder dieser Tabellen liegt ein Index auf
>>>TrmDateTime (einer Datumsspalte). Insgesamt sind ca. 30 Millionen
>>>Datensätze in diesen Tabellen.
>>
>>Wie sieht die View aus? UNION oder UNION ALL?
>
>
> Habe bisher nur "UNION" verwendet:
>
> CREATE VIEW epfp2_all AS
> SELECT
> PROJECT, REVISION, [...]
> FROM epfp2_200506
> UNION
> SELECT
> PROJECT, REVISION, [...]
> FROM epfp2_200507
> UNION
> [...];

Ohje,
eine UNION ist DISTINCT, also wird erst das distinct ausgeführt und
danach gruppiert.
Und da du pro Monat eine Tabelle hast, ergibt ein UNION ALL das gleiche
Ergebnis.

Die Frage ist aber, warum machst du diesen riesen Umweg und nimmst nicht
gleich eine neue View mit folgender Definition:

SELECT
200506 as "Month",
COUNT(*) AS "Transactions (Number)",
FORMAT(SUM(amount),2) AS "Transactions (EUR)",
COUNT(CASE WHEN matchesfraud = 1 THEN 1 ELSE null END) AS "Fraud
(Number)",
FORMAT(SUM(CASE WHEN matchesfraud = 1 THEN amount ELSE null END),2)
AS "Fraud (EUR)"
FROM
epfp2_200506

UNION ALL

SELECT
200507 as "Month",
COUNT(*) AS "Transactions (Number)",
FORMAT(SUM(amount),2) AS "Transactions (EUR)",
COUNT(CASE WHEN matchesfraud = 1 THEN 1 ELSE null END) AS "Fraud
(Number)",
FORMAT(SUM(CASE WHEN matchesfraud = 1 THEN amount ELSE null END),2)
AS "Fraud (EUR)"
FROM
epfp2_200507

UNION ALL
....

Wenn du einen neuen Monat hinzufügst/den ältesten löschst, musst du
deine View sowieso pflegen und kannst dann gleich die zweite mitanpassen.

Noch was, wie stabil sind deine Daten eigentlich?
Wenn die alten Monate nicht mehr geändert werden, kannst du die Daten
einmal vorberechnen, statt es immer wieder 'on-the-fly' zu machen.

Dieter

Re: Problem mit Group By Performance

On Mon, 20 Feb 2006 01:15:36 -0800, Nikolaus D. Bayer wrote:


> Was hat es mit der "2Gb Grenze" auf sich? Laufe ich hier ggf. gegen ein
> Limit?

Die 2 GByte Grenze ist ein Limit für Dateigrößen. Sie ergibt sich aus
der Adressierung Deines Dateisystems.

Bis auf wenige Exoten liegt diese Grenze bei den meisten Dateisystemen bei
eben erwähnten 2 GByte

> Grüße, Nikolaus

Bye, Norbert

Re: Problem mit Group By Performance

Norbert Melzer wrote:
> Bis auf wenige Exoten liegt diese Grenze bei den meisten Dateisystemen =
bei
> eben erwähnten 2 GByte

Zählen aktuelle ext2/ext3 Versionen für dich zu den Exoten? ;)

--=20
Hartmut Holzgraefe, Senior Support Engineer .
MySQL AB, www.mysql.com

http://www.mysql.com/support/

Re: Problem mit Group By Performance

On Mon, 20 Feb 2006 16:59:32 +0100, Hartmut Holzgraefe wrote:

> Norbert Melzer wrote:
>> Bis auf wenige Exoten liegt diese Grenze bei den meisten Dateisystemen bei
>> eben erwähnten 2 GByte
>
> Zählen aktuelle ext2/ext3 Versionen für dich zu den Exoten? ;)

Normal nicht, aber die haben doch auch das 2 GB Limit für Dateien oder
liege ich da gerade falsch? Ich bin halt auch nicht unfehlbar :-D Wäre ja
auch langweilig sonst :-)

Ich lasse mich da gerne eines Besseren belehren.

Norbert

Re: Problem mit Group By Performance

Norbert Melzer wrote:
> On Mon, 20 Feb 2006 16:59:32 +0100, Hartmut Holzgraefe wrote:
>
>> Norbert Melzer wrote:
>>> Bis auf wenige Exoten liegt diese Grenze bei den meisten Dateisystemen bei
>>> eben erwähnten 2 GByte
>> Zählen aktuelle ext2/ext3 Versionen für dich zu den Exoten? ;)
>
> Normal nicht, aber die haben doch auch das 2 GB Limit für Dateien oder
> liege ich da gerade falsch? Ich bin halt auch nicht unfehlbar :-D Wäre ja
> auch langweilig sonst :-)
>
> Ich lasse mich da gerne eines Besseren belehren.

Nein, haben sie nicht.

Ich hab hier aktuell eine ibdata1 mit 7GB, auf einem anderen Rechner
eine mit 3,5GB und eine temporäre Tabelle hat mir mal mit >8GB mein /tmp
platzmäßig zum Überlauf gebracht.

Grüße
Kai

Re: Problem mit Group By Performance

Hartmut Holzgraefe wrote:
> Norbert Melzer wrote:
> > Bis auf wenige Exoten liegt diese Grenze bei den meisten Dateisystemen =
bei
> > eben erwähnten 2 GByte
>
> Zählen aktuelle ext2/ext3 Versionen für dich zu den Exoten? ;)
>

Nun, auch NTFS verträgt definitiv Dateien, die größer als 2GB sind.
Meine aktuell größte MySQL-Tabelle belegt z.B. 21GB.

Daran kann es also nicht liegen.