Tabellenoptimierung / Indezes

Hallo,

ich habe folgendes Tabellendesign:

CREATE TABLE `server_traffic` (
`server` int(10) unsigned NOT NULL default '0',
`incoming` bigint(20) unsigned NOT NULL default '0',
`outgoing` bigint(20) unsigned NOT NULL default '0',
`timestamp` varchar(12) default NULL,
`unixstamp` int(12) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

server referenziert eine ID einer anderen Tabelle, die Felder incoming
und outgoing beherbergen 64-Bit Integer Zahlen welche alle 5min zwecks
Trafficmessung von einem entsprechenden Skript an geeigneten
Netzwerk-Switches ausgelesen und in die Tabelle geschrieben werden. Das
ganze wird mit einem Zeitstempel versehen: timestamp bzw. unixstamp.
timestamp ist ein Relikt und wird demnächst rausgekickt wenn alle
Skripte angepasst wurden, welche dies noch verwenden.
Gemessen werden recht viele Zähler, entsprechend wächst die Tabelle
vergleichsweise rasant und ist aktuell rund 700mb gross.
Da dieser Aufbau nur schlecht skaliert ist gerade ein Redesign in Form
einer Round-Robin Datenbank im Gange, das soll hier aber nicht das Thema
sein.

Benötigt werden die gesammelten Daten für zweierlei:

* Darstellung von Grafiken wofür die 400 neuesten Messwerte abgefragt werden
* Summierung aller Messwerte des vergangenen Monats für Abrechnungszwecke


Was ich nun suche sind Hinweise wie man hier für maximale Performance am
geschicktesten indiziert. Meine Versuche haben leider keine spürbare
Besserung gebracht.

Hier die beiden queries welche einen performance-Schub vertragen könnten:

Grafik:

SELECT incoming, outgoing FROM server_traffic WHERE server =
'$this->server' ORDER BY unixstamp DESC LIMIT 0, 400

Summierung:

SELECT SUM(incoming) AS incoming, SUM(outgoing) AS outgoing FROM
server_traffic WHERE
server = '$server' AND
unixstamp >= '$start' AND
unixstamp <= '$end'

Insbesondere letzteres dauert z.T. sehr lange, vor allem dann, wenn jene
Abfrage für z.B. 10 verschiedene $server durchgeführt wird. Hier
entzieht sich meiner Kenntnis ob MySQL bzw. PHP die Abfragen parallel
durchführt und diese Mehrfachbelastung für die schwächelnde Performance
verantwortlich ist.

Noch ein paar Worte zur Technik: bei dem Server handelt es sich um einen
Dual Xeon 2,4GHz mit 4GB Ram und SCSI Festplatten im Raid-1, also nicht
gerade ne lahme Schnecke.
Ich verwende PHP 5.1.2 für die Skripte und MySQL 4.1.11-a auf einem
Debian Sarge Linuxsystem.

Vielen Dank für jede Hilfe im Voraus!

Grüsse,
Nico

Re: Tabellenoptimierung / Indezes

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Tabellenoptimierung / Indezes

Andreas Kretschmer schrieb:
> begin Nicolaj Kamensek schrieb:
>>
>> CREATE TABLE `server_traffic` (
>> `server` int(10) unsigned NOT NULL default '0',
>> `incoming` bigint(20) unsigned NOT NULL default '0',
>> `outgoing` bigint(20) unsigned NOT NULL default '0',
>> `timestamp` varchar(12) default NULL,
>> `unixstamp` int(12) unsigned NOT NULL default '0'
>
> Hat MySQL dafür keine besseren Datentypen?

Das hängt ausschließlich davon ab, was man mit den Daten machen will.
Aber, ja, es gibt auch Datumstypen in mysql.

>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>>
>> server referenziert eine ID einer anderen Tabelle, die Felder incoming
>
> Naja, referentielle Integrität und MySQL...

Überflüssiger Seitenhieb.
(Mit InnoDB kann mysql auch RI, nur nebenbei bemerkt... wenn Du Dich mit
mysql nicht auskennst, warum pöbelst Du hier rum?)

>> Hier die beiden queries welche einen performance-Schub vertragen könnten:
>>
>> Grafik:
>>
>> SELECT incoming, outgoing FROM server_traffic WHERE server =
>> '$this->server' ORDER BY unixstamp DESC LIMIT 0, 400
>
> Hast Du einen Index auf unixstamp? Und auf server?
> Die gute achricht: Indexe beschleunigen Abfragen erheblich.

Richtig. Hier sollte es einen Index über server und unixstamp geben.

> Die schlechte für Dich: MySQL kann pro SELECT und Table wohl nur max.
> einen Index nutzen.

Ich weiß auch nicht, was mehrere Indexe bringen sollen.
Außer Du meinst einen Index, der über mehrere Felder geht - das
beherrscht mysql schon länger.

>> Summierung:
>>
>> SELECT SUM(incoming) AS incoming, SUM(outgoing) AS outgoing FROM
>> server_traffic WHERE
>> server = '$server' AND
>> unixstamp >= '$start' AND
>> unixstamp <= '$end'
>>
>> Insbesondere letzteres dauert z.T. sehr lange, vor allem dann, wenn jene
>> Abfrage für z.B. 10 verschiedene $server durchgeführt wird. Hier
>
> Hier wäre wieder ein Index auf server und unixstamp sinnvoll, mit
> derselben Einschränkung wie oben.

Ack.

>> Ich verwende PHP 5.1.2 für die Skripte und MySQL 4.1.11-a auf einem
>> Debian Sarge Linuxsystem.
>
> Unbestätigten Gerüchten zufolge soll MySQL 5.x schneller & besser als
> 4.x sein, aber die Einschränkung mit dem 1 Index pro Table und Select
> gilt da wohl auch noch. Falls Du nicht warten willst, bis MySQL das
> besser kann: PG kann das schon seit Jahren besser. Jede Wette, daß
> Abfragen wie diese auf einem PG-System erheblich schneller sind.

Auch das sind unbestätigte Gerüchte.
5.x hat einiges an neuen Features, und einige davon können tendenziell
die Performance belasten, insofern glaub ich gar nicht mal, dass 5.x in
allen Situationen wirklich schneller ist.
Das sind allerdings wirklich einfache Queries, bei denen der Optimierer
nicht viel zu melden hat, insofern dürften sich mysql 4, mysql 5 und
pgsql da nicht viel geben.

> Davon
> unabhängig: wenn Du passendere Datentypen für timestamp als oben nehmen
> würdest (char(12)), hätte die DB die Chance, da schneller zu sein.

Wohl kaum. Ob es nun TIMESTAMP, DATE oder DATETIME ist: =, > und <
dürften so ziemlich genau so lang dauern.
Die wichtigste Optimierung ist der Index. Ein Umstieg auf pgsql mag
etwas bringen oder nicht, er ist auf jeden Fall aufwändig und bringt
viel weniger.

(Ich geb Andreas recht, dass pgsql gegenüber mysql eine Menge Vorteile
hat. mysql hat halt den Vorteil der breiteren Installationsbasis, und
das gibt dann in der Praxis oft den Ausschlag. Und in mancher Hinsicht
kocht auch pgsql nur mit Wasser - Queries mit JOINs, ORDER BY und LIMIT
kann es auch nicht vernünftig optimieren, und genau das benötigt man bei
Webapplikationen eigentlich dringend.)

Grüße
Jo

Re: Tabellenoptimierung / Indezes

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Tabellenoptimierung / Indezes

Andreas Kretschmer wrote:

Hi,

>> CREATE TABLE `server_traffic` (
>> `server` int(10) unsigned NOT NULL default '0',
>> `incoming` bigint(20) unsigned NOT NULL default '0',
>> `outgoing` bigint(20) unsigned NOT NULL default '0',
>> `timestamp` varchar(12) default NULL,
>> `unixstamp` int(12) unsigned NOT NULL default '0'
>
> Hat MySQL dafür keine besseren Datentypen?

wo genau? timestamp fliegt ohnehin raus und ich denke mit dem Typ
integer lässt sich noch am schnellsten rechnen zumal unix timestamps im
Grunde ja nichts anderes sind.

> Hast Du einen Index auf unixstamp? Und auf server?

Ich habe mal so einige angelegt:

Index über server
Index über unixstamp
Index über server & unixstamp

Die Performance ist nun deutlich spürbar nach oben gegangen. Welcher
dieser 3 dafür verantwortlich ist weiss ich aber nicht. Eventuell kannst
du hier Licht ins Dunkle bringen, unnötige Indezes können ja wieder raus.
Bei rund 700mb dauert das Indizieren jedesmal ein paar Minuten, try &
error ist deshalb weniger spassig ;-)

> Die gute achricht: Indexe beschleunigen Abfragen erheblich. Die
> schlechte für Dich: MySQL kann pro SELECT und Table wohl nur max. einen
> Index nutzen.

Damit kann ich leben, sämtliche andere Abfragen funktionieren sehr
schnell, nur diese ist ein Problemfall.

> Hier wäre wieder ein Index auf server und unixstamp sinnvoll, mit
> derselben Einschränkung wie oben.

Das ist die Frage: je einen Index auf Server und unixstamp oder ein
Index über server _und_ unixstamp, also ein Index über 2 Felder?

> Wenn Du eine PHP-Verbindung offen hast, dann wird darüber auch nur eine
> Abfrage gefahren. Wenn Du N Abfragen in einer Schleife für N server
> machst, sieht der DB-Server nacheinander N Abfragen. Nix parallel.

Ok, dann bremsen die sich wenigstens nicht gegenseitig aus.

> Du könntest aber mal spaßeshalber die Sache umbauen und mit einer
> Abfrage gleich für die N server das gruppiert abfragen. Also in der
> select-Liste noch server und danach gruppieren.

Teste ich mal bei Gelegenheit :-)


Danke für die Hilfe & Grüsse,
Nico

Re: Tabellenoptimierung / Indezes

Joachim Durchholz wrote:

Hi,

>>> CREATE TABLE `server_traffic` (
>>> `server` int(10) unsigned NOT NULL default '0',
>>> `incoming` bigint(20) unsigned NOT NULL default '0',
>>> `outgoing` bigint(20) unsigned NOT NULL default '0',
>>> `timestamp` varchar(12) default NULL,
>>> `unixstamp` int(12) unsigned NOT NULL default '0'
>>
>> Hat MySQL dafür keine besseren Datentypen?
>
> Das hängt ausschließlich davon ab, was man mit den Daten machen will.
> Aber, ja, es gibt auch Datumstypen in mysql.

bekannt, aber ich gehe doch mal schwer davon aus, dass unix timestamps
als durchgehende Integer-Werte deutlich besser bei mathematischen
Operationen performen, oder?

Grüsse,
Nico

Re: Tabellenoptimierung / Indezes

Nicolaj Kamensek wrote:
> Joachim Durchholz wrote:

Wer hat eigentlich das folgende geschrieben?
http://learn.to/quote

>>>> CREATE TABLE `server_traffic` (
>>>> `server` int(10) unsigned NOT NULL default '0',
>>>> `incoming` bigint(20) unsigned NOT NULL default '0',
>>>> `outgoing` bigint(20) unsigned NOT NULL default '0',
>>>> `timestamp` varchar(12) default NULL,
>>>> `unixstamp` int(12) unsigned NOT NULL default '0'
>>>
>>> Hat MySQL dafür keine besseren Datentypen?
>>
>> Das hängt ausschließlich davon ab, was man mit den Daten machen will.
>> Aber, ja, es gibt auch Datumstypen in mysql.
>
> bekannt, aber ich gehe doch mal schwer davon aus, dass unix timestamps
> als durchgehende Integer-Werte deutlich besser bei mathematischen
> Operationen performen, oder?

Welche sinnvollen (!) mathematischen Operationen auf einem Integerwert,
der ein Datum repräsentiert, kennst du denn?

- Vergleiche auf Datumstypen sind repräsentationsbedingt genausoschnell.
- Addiere mal einen Monat auf den Integerwert (FROM_UNIXTIME gilt nicht).
- Was ist 2*'2007-04-11'?

Grüße
Kai

--
This signature is left as an exercise for the reader.

Re: Tabellenoptimierung / Indezes

Kai Ruhnau schrieb:

> - Was ist 2*'2007-04-11'?

Welche Praxisrelevanz hat diese Frage? Oder anders gefragt: Zu welchem
Zweck und in welcher Form werden Datumsangaben im realen Leben
multipliziert?

Gruß. Claus

Re: Tabellenoptimierung / Indezes

Kai Ruhnau wrote:

> Welche sinnvollen (!) mathematischen Operationen auf einem Integerwert,
> der ein Datum repräsentiert, kennst du denn?

Du hast natürlich recht, ich hatte die speziellen Datumstypen die MySQL
zur Verfügung stellt vollkommen vergessen(in o.g. Struktur wird
sinnloserweise varchar verwendet, das Design stammt noch aus
schlechteren Zeiten :-)), damit sind Vergleichsoperationen wohl genauso
schnell wie auf Integers.

Grüsse,
Nico

Re: Tabellenoptimierung / Indezes

Claus Reibenstein schrieb:
> Kai Ruhnau schrieb:
>
>> - Was ist 2*'2007-04-11'?
>
> Welche Praxisrelevanz hat diese Frage? Oder anders gefragt: Zu welchem
> Zweck und in welcher Form werden Datumsangaben im realen Leben
> multipliziert?

Genau das war ja Kais Frage :) Er hat lediglich eine unsinnige
Möglichkeiten geboten, um zu zeigen, daß es keine Gründe gibt,
mathematische Operationen auf Daten (Datums, Datumsen oder sonstwie)
auszuführen.


Grüße,
Dominik

Re: Tabellenoptimierung / Indezes

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Tabellenoptimierung / Indezes

Andreas Kretschmer wrote:

> Vielleicht wiederhole ich mich ja, aber MySQL kann je Query und Table
> nur einen Index nutzen. Damit dürften die verbleibenden Optionen klar
> sein.

Vielleicht wiederhole ich mich ja, aber du erzählst Schwachsinn.
Also nochmal:

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimizat ion.html


XL

Re: Tabellenoptimierung / Indezes

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Tabellenoptimierung / Indezes

Andreas Kretschmer wrote:
> begin Axel Schwenke schrieb:
>> Andreas Kretschmer wrote:
>>
>>> Vielleicht wiederhole ich mich ja, aber MySQL kann je Query und Table
>>> nur einen Index nutzen. Damit dürften die verbleibenden Optionen klar
>>> sein.
>> Vielleicht wiederhole ich mich ja, aber du erzählst Schwachsinn.
>> Also nochmal:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimizat ion.html
>
> Darf ich Dich auf die vom Fragesteller verwendete Version 4.1.11-a
> hinweisen?

Ja, darfst du, aber du brauchst dich nicht wundern, dass man dich
korrigiert, wenn du im Allgemeinen (!) falsche Aussagen ohne konkretes
Zitat, das den Kontext enthält, produzierst. Mache die nötigen
Einschränkungen bei deiner Aussage (du weißt ziemlich gut, wann sie
gilt, die Kompetenz hast du in letzter Zeit bewiesen) und keiner wird
meckern.

Grüße
Kai

Re: Tabellenoptimierung / Indezes

Andreas Kretschmer wrote:

Hallo,

> Wenn Dir 'EXPLAIN' als Beleuchtung reicht?

tut es, danke ;-)

> Vielleicht wiederhole ich mich ja, aber MySQL kann je Query und Table
> nur einen Index nutzen. Damit dürften die verbleibenden Optionen klar
> sein.

Inwiefern schliesst dies einen der 3 Indiezes aus? Es wird für diese
Tabelle je Query nur einer der 3 Indiezes genutzt, das sagt imo noch
nichts darüber aus welcher es ist.

Grüsse,
Nico

Re: Tabellenoptimierung / Indezes

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Tabellenoptimierung / Indezes

Andreas Kretschmer schrieb:
> begin Joachim Durchholz schrieb:
>
>>> Die schlechte für Dich: MySQL kann pro SELECT und Table wohl nur max.
>>> einen Index nutzen.
>> Ich weiß auch nicht, was mehrere Indexe bringen sollen.
>
> Das glaub ich Dir, und ich erkläre es Dir: Geschwindigkeit.

Das ist keine Erklärung, das ist eine schlichte Behauptung.

Etwas konkreter, bitte. Was meinst Du überhaupt mit "mehrere Indexe"?
Die Nutzung mehrerer Indexe in einem JOIN? (Macht im Kontext wenig
Sinn.) Aus mehreren Feldern zusammengesetzte Indexe? (Mysql kann das,
sogar mit popeligem Myisam, und zwar schon länger.)

>> (Ich geb Andreas recht, dass pgsql gegenüber mysql eine Menge Vorteile
>> hat. mysql hat halt den Vorteil der breiteren Installationsbasis, und
>> das gibt dann in der Praxis oft den Ausschlag. Und in mancher Hinsicht
>> kocht auch pgsql nur mit Wasser - Queries mit JOINs, ORDER BY und LIMIT
>> kann es auch nicht vernünftig optimieren, und genau das benötigt man bei
>> Webapplikationen eigentlich dringend.)
>
> Gerade bei solchen Abfragen hat PG massiv in der Performance zugelegt.
> Das, was Du da eben sagtest, ist seit einigen Versionen nicht mehr wahr.

Hmm... also, ich bin ja genau über dieses Problem gestolpert. Vor
ungefähr einer Woche.
Und habe rumgegoogelt wie ein Weltmeister.
Und irgendwann in meiner Verzweiflung auch mal bei pgsql geschaut, ob
das Problem denn dort gelöst sei... und siehe da: in den Foren wurde
genau dieses Problem ebenfalls diskutiert und als "der Optimierer kann's
leider nicht" zu den Akten gelegt.

Jetzt würd mich allerdings interessieren, ob das an der Uninformiertheit
der Forenteilnehmer lag oder daran, dass da ein alter Versionsstand
diskutiert wurde. (Ich hatte da andere Prioritäten als pgsql ernsthaft
zu untersuchen, vor allem, wenn die ersten Indizien schon mal in die
falsche Richtung zeigen.)
Sprich: gibt es irgendwo Aussagen, welche Transformationen der
Optimierer durchführt? Gibt es irgendwo eine Datenbank, auf der man das
testen kann? (Bei mir müsste ich erstmal ein pgsql installieren, den
ganzen XAMPP-Stack drauf umstellen, die unvermeidlichen
SQL-Inkompatibilitäten ausbügeln, bevor ich mit dem Testen überhaupt
anfangen könnte - da geht schnell mal eine Woche drauf, und die hab ich
nicht. Und da ist die Zeit zum Einarbeiten in die Administrationstools
noch gar nicht berücksichtigt...)

Grüße
Jo

Re: Tabellenoptimierung / Indezes

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Tabellenoptimierung / Indezes

This is a multi-part message in MIME format.
--------------000401070001000407030905
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit

Andreas Kretschmer schrieb:
> begin Joachim Durchholz schrieb:
>> Andreas Kretschmer schrieb:
>>> begin Joachim Durchholz schrieb:
>>>
>>>>> Die schlechte für Dich: MySQL kann pro SELECT und Table wohl nur max.
>>>>> einen Index nutzen.
>>>> Ich weiß auch nicht, was mehrere Indexe bringen sollen.
>>> Das glaub ich Dir, und ich erkläre es Dir: Geschwindigkeit.
>> Das ist keine Erklärung, das ist eine schlichte Behauptung.
>
> Okay. Du solltest Dir vielleicht einfach nochmals die Ausgangsfrage
> anschauen.
> [Langes Beispiel zur Indexnutzung rausgeschnitten]

Eulen nach Athen brauchen wir hier nicht. Ich weiß recht gut, wozu ein
Index gut ist - lineare Scans vermeiden (und unter manchen Umständen
auch Zwischenergebnisse stutzen, um einer kombinatorischen Explosion
vorzubeugen).

Meine eigentliche Frage war: wozu sollen *mehrere* Indexe gut sein?
Was meinst Du überhaupt mit "mehrere Indexe"?
Jede Interpretation von "mehrere Indexe", die ich mir vorstellen kann,
macht entweder keinen Sinn, oder mysql kann es schon seit Urzeiten.

>> Hmm... also, ich bin ja genau über dieses Problem gestolpert. Vor
>> ungefähr einer Woche.
>> Und habe rumgegoogelt wie ein Weltmeister.
>> Und irgendwann in meiner Verzweiflung auch mal bei pgsql geschaut, ob
>> das Problem denn dort gelöst sei... und siehe da: in den Foren wurde
>> genau dieses Problem ebenfalls diskutiert und als "der Optimierer kann's
>> leider nicht" zu den Akten gelegt.
>
> Niemand hier kennt Dein Problem,

Ich hab ja auch im englischen mysql-Forum gefragt.
Es haben sich sogar kompetente Leute mit Hilfsabsicht gemeldet, aber
eine Lösung hatte trotzdem keiner.

Die Frage ist ganz einfach: Er soll die Kombination aus JOIN, ORDER BY
und LIMIT ordentlich optimieren.
MySQL kann *das* definitiv nicht.

Postgresql offenbar auch nicht. Jedenfalls, wenn das angehängte
Protokoll das letzte Wort in der Sache ist.

Ich gebe zu, dass ich mich in die EXPLAIN-Ausgabe nicht eingelesen habe;
es ist einfach zu spät (vielleicht morgen nochmal). Allerdings: diese
Query hat auf dem Testserver (Load Average 0.02, Athlon 3700+, 1GB RAM,
Standard-IDE-Platte) 10 Sekunden gebraucht, und das ist definitiv zu
lang. (Einzige Optimierung: ANALYZE über den betroffenen Tabellen.)

Ich gebe gern zu, dass das eine anspruchsvolle Aufgabe ist. Leider ist
diese spezielle Feature-Kombination für eine sauber programmierte
Listenausgabe aus einem Webserver-Skript ziemlich unvermeidlich.
(Alternativen: Denormalisierung, oder auf Applikationsebene optimieren,
was eigentlich die DB optimieren sollte...)

> aber Du kannst Dich gerne in einer der
> beiden deutschen PG-Mailinglisten dazu mal melden, oder im Forum, oder
> im #IRC. Das die Entwicklung auch bei PG nicht innehält und mit jeder
> neuen Version neben neuen Features auch Performancegewinne kommen,
> bestreitet niemand. Der Umkehrschluß ist natürlich, daß auch PG in
> manchen Dingen anderen Systemen noch hinterherschaut.

Man ist ohnehin nicht immer frei in der Wahl der Version.

>> Sprich: gibt es irgendwo Aussagen, welche Transformationen der
>
> IMHO kann man das nicht wirklich pauschal definieren, da das z.B. auch
> von den Statistiken, der Tablegröße etc. abhängt.

Oh, eine Liste der verfügbaren Optimierungen würde schon helfen.
Aber mittlerweile hab ich die PGSQL-Doku selbst gefunden.

>> Optimierer durchführt? Gibt es irgendwo eine Datenbank, auf der man das
>> testen kann? (Bei mir müsste ich erstmal ein pgsql installieren, den
>
> Es gibt eine Live-CD mit PG 8.2 ;-)
>
> Ansonsten könntest Du Dich bei http://templarshells.mine.nu/ anmelden,
> die bieten auch eine PG-Datenbank. Nein, getestet habe ich das dort noch
> nicht. Andererseits: die Installation von PG ist nicht wirklich
> kompliziert, und das kann auch neben MySQL tun.

Letzteres weiß ich.
Allerdings hatte ich dann doch ein paar mühselige Momente beim
Einrichten der Datenbankuser.

> Und wenn Du ein wirklich kniffliges Problem hast und das z.B. in der
> Mailingliste pgsql-performance@postgresql.org nennst, kannst Du fast
> sicher sein, von Leuten wie Tom Lane Hilfe zu bekommen.
> (übrigens auch, wenn man offensichtliche Fehler anspricht, wie ich
> kürzlich...)

Na ja... die Software soll auch auf Fremdservern lauffähig sein, wenn
sie fertig ist. Da ist Insider-Tuning oft nicht möglich, weil man gar
keinen administrativen Zugang kriegt.

Ich hab mich ohnehin schon auf die Strategie festgelegt, dass im
Zweifelsfall statt der Liste die Fehlermeldung kommt, die Liste sei zu
lang und unhandlich... muss der Benutzer halt einen Filter angeben. So à
la "bitte nur alle Schulzes" - das sind dann nur noch ein paar tausend
Sätze, die die DB sortieren muss, und schon fällt das auf moderner
Hardware nicht mehr auf.
Ja, ich weiß, dafür sind Datenbanken eigentlich nicht gemacht. Aber der
Kunde hat halt leider nichts davon, wenn ich erstmal ein Problem
"richtig" löse und er hat solange überhaupt keine Software...

Grüße
Jo

--------------000401070001000407030905
Content-Type: text/plain;
name="Dokument2.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="Dokument2.txt"

test=# EXPLAIN
test-# SELECT
test-# verbaende.nummer AS lv_nr,
test-# bundeslaender_liste.kuerzel AS lv_kuerzel,
test-# vereine.nummer AS verein_nr,
test-# vereine.kurzname AS verein_name,
test-# personen.vorname AS vorname,
test-# personen.nachname AS nachname,
test-# mitglieder.id AS id
test-# FROM
test-# mitglieder
test-# JOIN vereine ON mitglieder.verein_id = vereine.id
test-# JOIN verbaende ON vereine.verband_id = verbaende.id
test-# JOIN bundeslaender_liste ON verbaende.bundesland_id = bundeslaender_liste.id
test-# LEFT JOIN personen ON mitglieder.person_id = personen.id
test-# LEFT JOIN kontaktwege ON mitglieder.kontaktweg_id = kontaktwege.id
test-# LEFT JOIN staaten_liste ON kontaktwege.land_id = staaten_liste.id
test-# ORDER BY lv_nr ASC, lv_kuerzel ASC, verein_nr ASC, verein_name ASC, vorname ASC, nachname ASC LIMIT 12
test-# ;
QUERY PLAN
------------------------------------------------------------ ------------------------------------------------------------ ---------------------------
Limit (cost=140472.80..140472.83 rows=12 width=75)
-> Sort (cost=140472.80..140851.67 rows=151545 width=75)
Sort Key: verbaende.nummer, bundeslaender_liste.kuerzel, vereine.nummer, vereine.kurzname, personen.vorname, personen.nachname
-> Merge Right Join (cost=108918.55..111192.82 rows=151545 width=75)
Merge Cond: ("outer".id = "inner".land_id)
-> Sort (cost=20.76..21.31 rows=220 width=11)
Sort Key: staaten_liste.id
-> Seq Scan on staaten_liste (cost=0.00..12.20 rows=220 width=11)
-> Sort (cost=108897.79..109276.65 rows=151545 width=85)
Sort Key: kontaktwege.land_id
-> Merge Right Join (cost=76519.11..77397.81 rows=151545 width=85)
Merge Cond: ("outer".id = "inner".kontaktweg_id)
-> Index Scan using kontaktwege_pkey on kontaktwege (cost=0.00..114.12 rows=2738 width=20)
-> Sort (cost=76519.11..76897.97 rows=151545 width=89)
Sort Key: mitglieder.kontaktweg_id
-> Merge Right Join (cost=37304.83..44279.12 rows=151545 width=89)
Merge Cond: ("outer".id = "inner".person_id)
-> Index Scan using personen_pkey on personen (cost=0.00..4321.47 rows=151861 width=31)
-> Sort (cost=37304.83..37683.69 rows=151545 width=80)
Sort Key: mitglieder.person_id
-> Merge Join (cost=3.30..7284.84 rows=151545 width=80)
Merge Cond: ("outer".id = "inner".verein_id)
-> Nested Loop (cost=3.30..818.19 rows=2059 width=54)
Join Filter: ("outer".verband_id = "inner".id)
-> Index Scan using vereine_pkey on vereine (cost=0.00..73.65 rows=2059 width=48)
-> Materialize (cost=3.30..3.46 rows=16 width=26)
-> Merge Join (cost=2.96..3.28 rows=16 width=26)
Merge Cond: ("outer".bundesland_id = "inner".id)
-> Sort (cost=1.48..1.52 rows=16 width=30)
Sort Key: verbaende.bundesland_id
-> Seq Scan on verbaende (cost=0.00..1.16 rows=16 width=30)
-> Sort (cost=1.48..1.52 rows=16 width=16)
Sort Key: bundeslaender_liste.id
-> Seq Scan on bundeslaender_liste (cost=0.00..1.16 rows=16 width=16)
-> Index Scan using verein_id on mitglieder (cost=0.00..4567.99 rows=151545 width=46)

--------------000401070001000407030905--

Re: Tabellenoptimierung / Indezes

Joachim Durchholz wrote:



> Die Frage ist ganz einfach: Er soll die Kombination aus JOIN, ORDER BY
> und LIMIT ordentlich optimieren.
> MySQL kann *das* definitiv nicht.

Im allgemeinen Fall wird das niemand können. Anders gesprochen: es
lassen sich für jede Datenbank pathologische Fälle von JOIN, ORDER BY
und LIMIT konstruieren, die schlecht performen.

Aber schauen wir uns lieber ein praktisches Besipiel an:

> test=# EXPLAIN
> test-# SELECT
> test-# verbaende.nummer AS lv_nr,
> test-# bundeslaender_liste.kuerzel AS lv_kuerzel,
> test-# vereine.nummer AS verein_nr,
> test-# vereine.kurzname AS verein_name,
> test-# personen.vorname AS vorname,
> test-# personen.nachname AS nachname,
> test-# mitglieder.id AS id
> test-# FROM
> test-# mitglieder
> test-# JOIN vereine ON mitglieder.verein_id = vereine.id
> test-# JOIN verbaende ON vereine.verband_id = verbaende.id
> test-# JOIN bundeslaender_liste ON verbaende.bundesland_id = bundeslaender_liste.id
> test-# LEFT JOIN personen ON mitglieder.person_id = personen.id
> test-# LEFT JOIN kontaktwege ON mitglieder.kontaktweg_id = kontaktwege.id
> test-# LEFT JOIN staaten_liste ON kontaktwege.land_id = staaten_liste.id
> test-# ORDER BY lv_nr ASC, lv_kuerzel ASC, verein_nr ASC, verein_name ASC, vorname ASC, nachname ASC LIMIT 12

Weia! Das sind 7 Tabellen und vom Ergebnis wirfst du nach dem Sortieren
den vermutlich größten Teil weg. Die einzige Optimierung, die die Daten-
bank hier machen kann, ist den JOIN in Sortierreihenfolge abzuarbeiten.
Dann muß sie nicht erst ein Zwischenergebnis materialisieren und
sortieren. Genau das geht aber nicht, wegen der Indirektion von vereine
auf personen über mitglieder. Bis verein_name ist die Reihenfolge noch
durch geschickte Ausführung des JOINs machbar (ich setze mal voraus daß
die Sortierspalten UNIQUE sind). Danach ist Schluß.

Allerdings machst du es der Datenbank auch extra schwer:

- warum ein LEFT JOIN mit personen? Wieso kann es überhaupt Mitglieder
geben, die keinen personen-Eintrag haben? Hier gehört ein referenti-
elles Constraint drauf und dann wird das ein INNER JOIN

- wozu die beiden letzten LEFT JOINs? Brauchtest du noch ein paar
Duplikate in der Ergebnismenge?


XL

Re: Tabellenoptimierung / Indezes

Axel Schwenke schrieb:
> Joachim Durchholz wrote:
>
>
>
>> Die Frage ist ganz einfach: Er soll die Kombination aus JOIN, ORDER BY
>> und LIMIT ordentlich optimieren.
>> MySQL kann *das* definitiv nicht.
>
> Im allgemeinen Fall wird das niemand können. Anders gesprochen: es
> lassen sich für jede Datenbank pathologische Fälle von JOIN, ORDER BY
> und LIMIT konstruieren, die schlecht performen.
>
> Aber schauen wir uns lieber ein praktisches Besipiel an:
>
>> test=# EXPLAIN
>> test-# SELECT
>> test-# verbaende.nummer AS lv_nr,
>> test-# bundeslaender_liste.kuerzel AS lv_kuerzel,
>> test-# vereine.nummer AS verein_nr,
>> test-# vereine.kurzname AS verein_name,
>> test-# personen.vorname AS vorname,
>> test-# personen.nachname AS nachname,
>> test-# mitglieder.id AS id
>> test-# FROM
>> test-# mitglieder
>> test-# JOIN vereine ON mitglieder.verein_id = vereine.id
>> test-# JOIN verbaende ON vereine.verband_id = verbaende.id
>> test-# JOIN bundeslaender_liste ON verbaende.bundesland_id = bundeslaender_liste.id
>> test-# LEFT JOIN personen ON mitglieder.person_id = personen.id
>> test-# LEFT JOIN kontaktwege ON mitglieder.kontaktweg_id = kontaktwege.id
>> test-# LEFT JOIN staaten_liste ON kontaktwege.land_id = staaten_liste.id
>> test-# ORDER BY lv_nr ASC, lv_kuerzel ASC, verein_nr ASC, verein_name ASC, vorname ASC, nachname ASC LIMIT 12
>
> Weia! Das sind 7 Tabellen und vom Ergebnis wirfst du nach dem Sortieren
> den vermutlich größten Teil weg.

In der Tat. Wir haben

bundeslaender 1:N verbände 1:N vereine 1:N mitglieder

und ungefähr 150.000 Sätze in mitglieder.

> Die einzige Optimierung, die die Datenbank hier machen kann, ist den
> JOIN in Sortierreihenfolge abzuarbeiten. Dann muß sie nicht erst ein
> Zwischenergebnis materialisieren und sortieren.

Das ist richtig.

> Genau das geht aber nicht, wegen der Indirektion von vereine
> auf personen über mitglieder. Bis verein_name ist die Reihenfolge noch
> durch geschickte Ausführung des JOINs machbar (ich setze mal voraus daß
> die Sortierspalten UNIQUE sind).

Nein, sind sie nicht.
In der Praxis ist der Anteil an nicht-UNIQUE-Sätzen natürlich extrem
niedrig, aber "Ralf Müller" gibt's halt mehr als einmal.

> Danach ist Schluß.

Das ist bei den üblichen Optimierungen wohl leider wahr.

Allerdings könnte die Datenbank durchaus in einer Weise vorgehen, dass
diese Abfrage effizient wird, d.h. nur O(Ergebnisgröße) statt
O(Tabellengröße) liest. Das Vorgehen wäre etwa so:

1. Den vollen JOIN mit LIMIT 1 ausführen. Dafür muss in jeder Tabelle
genau ein Satz gelesen werden.
2. Nach der Indexreihenfolge die nächsten Sätze aus mitglieder mit der
gleichen verein_id lesen. Stop, sobald 12 Sätze gelesen sind.
3. Gibt es nicht genügend mitglieder-Sätze mit der gleichen verein_id,
den nächsten Satz aus vereine mit der gleichen verband_id lesen. Gibt es
dort keinen, den nächsten Satz aus verbaende lesen. Mit dem ersten neuen
Mitglied des neuen Vereins (und ggf. Verbands) weitermachen.

Alternativ:
1. SELECT id FROM verbaende LIMIT 12
2. SELECT * FROM vereine WHERE verein.verband_id IN (Ergebnis aus 1)
LIMIT 12
3. SELECT * FROM mitglieder WHERE mitglied.verein_id IN (Ergebnis aus 2)
LIMIT 12

Das Ganze lässt sich natürlich auch auf Anwendungsseite programmieren.
Dagegen sprechen aber die folgenden Gründe (unwichtigere zuerst):
1. Wir haben auch kleine Tabellen mit wenigen Dutzend Sätzen, da ist das
ineffizient. Ich habe im Moment keine Zahlen, ob die Leute mehr auf die
großen oder die kleinen Tabellen losgehen, deshalb ist nicht
abzuschätzen, ob unterm Strich ein Durchsatzgewinn oder -verlust
herauskommt.
2. Ich müsste eine Menge Bibliothekscode schreiben, was ein paar Wochen
zusätzlicher Arbeit bedeutet.
3. Aus jedem JOIN in SQL würden mehrere Zeilen PHP-Code, der zudem pro
Zeile noch fehleranfälliger wäre (PHP ist halt nicht das Gelbe vom Ei,
wir verwenden das auch nur, weil die Software für möglichst viele
Webmaster attraktiv sein soll und PHP nun mal überall vorinstalliert
ist... in ein paar Jahren können wir hoffentlich auf Ruby umsteigen).
Alternativ könnte ich einen SQL-Parser schreiben, der die JOINs in
Einzelqueries auflöst (*noch* mehr Aufwand, und PHP ist für sowas nicht
wirklich gut geeignet :-( ... )

> Allerdings machst du es der Datenbank auch extra schwer:
>
> - warum ein LEFT JOIN mit personen? Wieso kann es überhaupt Mitglieder
> geben, die keinen personen-Eintrag haben? Hier gehört ein referenti-
> elles Constraint drauf und dann wird das ein INNER JOIN

Ah, danke, richtig. Den hatte ich übersehen.
(Die Freuden zu häufiger Änderungen am Datenmodell... und ein fälschlich
stehengebliebener LEFT JOIN generiert gemeinerweise keine Warnungen.)

Allerdings macht es unterm Strich keinen Unterschied.
Erstaunt mich aber nicht. Ich wüsste auch nicht, wie er das optimieren
soll, wenn er erst sortieren und dann limitieren will.

> - wozu die beiden letzten LEFT JOINs? Brauchtest du noch ein paar
> Duplikate in der Ergebnismenge?

Nein, es gibt Mitglieder, zu denen keine Adressdaten vorliegen.
Der weitere JOIN mit staaten_liste ist nur deshalb LEFT, weil bei
fehlenden Adressdaten halt auch keine land_id vorliegt.
Das könnte man theoretisch ein bisschen weiteroptimieren, indem man erst
den JOIN zwischen kontaktwege und staaten_liste macht, aber ich glaub
nicht, dass die Schwierigkeiten wirklich hier liegen.

Grüße
Jo

Re: Tabellenoptimierung / Indezes

Joachim Durchholz wrote:

>> - wozu die beiden letzten LEFT JOINs? Brauchtest du noch ein paar
>> Duplikate in der Ergebnismenge?
>
> Nein, es gibt Mitglieder, zu denen keine Adressdaten vorliegen.
> Der weitere JOIN mit staaten_liste ist nur deshalb LEFT, weil bei
> fehlenden Adressdaten halt auch keine land_id vorliegt.

Ich glaube, Axel wollte auf etwas andres hinaus:
AFAICS wird kein Feld aus kontaktwege oder staaten_liste verwendet - weder
in der Feldliste noch im ORDER BY.

Aber wenn es anständige Indices gibt, dürfte der Unterschied marginal
sein...


Thomas
--
Diese Signatur gilt nur am Freitag.