"Vermeidung" / "Abschaltung" von tmp_tables bei select statements

Hallo Gemeinde !

In einer auf oscommerce adaptierten Variante erfolgt immer wieder ein SELECT
auf die Artikestammdaten.
Dieser enthält zwingend ein von uns ergänztes ORDERBY auf eine, dem Benutzer
zur Verfügung gestellten "Sortierungs-Spalte" - wir _müssen_ also zur
Laufzeit sortieren.

Auf anderen DBMS hatten wir bis dato damit auch keinerlei Probleme, nur
irgendwie auf mySQL zickt das Ganze etwas rum, da in nur 3 Tagen dadurch
über 100.000 created_tmp_tables zustande kamen, deren Erstellung jetzt immer
wieder den DB-Server mit seinen 2GB Arbeitsspeicher voll auslasten und dann
zu Abbrüchen führen.

Die Ansätze die ich gerne verfolgen möchte, sind diese:

- Kann ich dieses temp_tables bilden einfach abschalten?
- Wie optimiere ich die Sache mit dem zwingend erforderlichen OrderBy?
- Kann/Muss ich (wie ?) diese tmp_tables manuell löschen?

Bis auf dieses Problem läuft der dedizierte mySQL Server 4.0.27 schön
schnell und stabil.

Gruß,
Robert

Re: "Vermeidung" / "Abschaltung" von tmp_tables bei select statements

Robert Bollig schrieb:
> Hallo Gemeinde !
>
> In einer auf oscommerce adaptierten Variante erfolgt immer wieder ein SELECT
> auf die Artikestammdaten.
> Dieser enthält zwingend ein von uns ergänztes ORDERBY auf eine, dem Benutzer
> zur Verfügung gestellten "Sortierungs-Spalte" - wir _müssen_ also zur
> Laufzeit sortieren.
>
> Auf anderen DBMS hatten wir bis dato damit auch keinerlei Probleme, nur
> irgendwie auf mySQL zickt das Ganze etwas rum, da in nur 3 Tagen dadurch
> über 100.000 created_tmp_tables zustande kamen, deren Erstellung jetzt immer
> wieder den DB-Server mit seinen 2GB Arbeitsspeicher voll auslasten und dann
> zu Abbrüchen führen.
>
> Die Ansätze die ich gerne verfolgen möchte, sind diese:
>
> - Kann ich dieses temp_tables bilden einfach abschalten?
> - Wie optimiere ich die Sache mit dem zwingend erforderlichen OrderBy?
> - Kann/Muss ich (wie ?) diese tmp_tables manuell löschen?

Selbst wenn du sie abschalten könntest, irgendwie muss MySQL Daten
zwischenspeichern, wenn es ein Order by machen soll. Die Menge der Daten
scheint so erheblich zu sein, dass deine Kiste Probleme hat. Und wenn
ich mich nicht ganz irre, werden temporäre Tabellen auch immer auf
Festplatte gespeichert, das heisst evtl. hast du dort zuwenig Platz.

Löschen musst du gar nix, die temporären Tabellen werden automatisch
gelöscht, wenn sie nicht mehr gebraucht werden.

Das Optimieren würde ich bei der Menge der Daten anfangen, wenn das geht.

Grüße

Dirk

Re: "Vermeidung" / "Abschaltung" von tmp_tables bei select statements

"Robert Bollig" wrote:
>
> In einer auf oscommerce adaptierten Variante erfolgt immer wieder ein SELECT
> auf die Artikestammdaten.
> Dieser enthält zwingend ein von uns ergänztes ORDERBY auf eine, dem Benutzer
> zur Verfügung gestellten "Sortierungs-Spalte" - wir _müssen_ also zur
> Laufzeit sortieren.
>
> Auf anderen DBMS hatten wir bis dato damit auch keinerlei Probleme, nur
> irgendwie auf mySQL zickt das Ganze etwas rum, da in nur 3 Tagen dadurch
> über 100.000 created_tmp_tables zustande kamen,

Das ist an sich nichts schlechtes. Kritisch wird es, wenn das
created_tmp_disk_tables sind. Dann wird dein Server I/O bound.

> deren Erstellung jetzt immer
> wieder den DB-Server mit seinen 2GB Arbeitsspeicher voll auslasten und dann
> zu Abbrüchen führen.

Erkläre "voll auslasten". Erkläre "zu Abbrüchen führen"

> Die Ansätze die ich gerne verfolgen möchte, sind diese:
>
> - Kann ich dieses temp_tables bilden einfach abschalten?

Gar nicht. MySQL kann records nur dann in ORDER BY Reihenfolge lesen,
wenn es einen passenden Index gibt. Ansonsten wird das Ergebnis
materialisiert und sortiert. Genau dazu dient die temporäre Tabelle.

Wenn die temporäre Tabelle zu groß wird oder BLOBs enthält, wird sie
auf die Platte geschrieben.
Aus dem Manual: "Most temporary tables are memory-based hash tables.
Temporary tables with a large row length (calculated as the sum of all
column lengths) or that contain BLOB columns are stored on disk.
If an internal heap table exceeds the size of tmp_table_size, MySQL
handles this automatically by changing the in-memory heap table to a
disk-based MyISAM table as necessary. You can also increase the
temporary table size by setting the tmp_table_size option to mysqld,
or by setting the SQL option SQL_BIG_TABLES in the client program."

> - Wie optimiere ich die Sache mit dem zwingend erforderlichen OrderBy?

Genug RAM haben. tmp_table_size und sort_buffer groß genug konfigu-
rieren. Möglichst selten und möglichst wenig Daten sortieren.
Das Manual lesen und die Bedeutung von der von SHOW STATUS angezeigten
Werte verstehen.

> - Kann/Muss ich (wie ?) diese tmp_tables manuell löschen?

Nein.


XL