SELECT WHERE datetime abfrage - Optimierung

[Forumsgast]

hallo forum,

seit einiger zeit bin ich auf der suche nach optimierungen von 'WHERE datetime-feld' abfragen. leider habe ich bis jetzt noch keine lösung gefunden - aber vielleicht könnt ihr mir weiterhelfen.

vorab: ich bin nicht der administator der datenbank und kann somit nicht ins design eingreifen...

also die abfrage lautet in etwa wiefolgt:

SELECT meinPrimaryKey FROM tabelle WHERE LetzteAenderung>=$StartDateTime

problem ist leider die grösse dieser tabelle (>5mio zeilen) - eine solche abfrage dauert ca. 2000sek

kenn jemand eine möglichkeit solche abfragen performanter zu gestalten - also beispielsweise mit php nachzubearbeiten?

danke schonmal im voraus!

[Forumsgast]

- ein LIMIT ist immer sehr hilfreich, wenn du maximal X oder genau Y Ergebnisse brauchst
- du kannst die Spalten ruhig auch selektieren wenn du sie überhaupt brauchst, denn die Zeile hast du ja sowieso schon getroffen

LetzteAenderung ist vom Typ Datetime?
Was genau enthält $StartDateTime der Form nach?

[Forumsgast]

hi zergling,

danke für die schnelle antwort!

- ein LIMIT ist immer sehr hilfreich, wenn du maximal X oder genau Y Ergebnisse brauchst

das ergebnis beinhaltet durchschnitlich 1000 zeilen.
hm.. müsste ich mal checken!
also mache zuerst ein MAX(meinPrimaryKey), packe die abfrage in eine schleife welche zusätzlich ein ' AND meinPrimaryKey>$x AND meinPrimaryKey<$y' beinhaltet. um dies danach wieder mit php in ein einzelnes array zu schreiben? das wäre sicher ein versuch wert!

- du kannst die Spalten ruhig auch selektieren wenn du sie überhaupt brauchst, denn die Zeile hast du ja sowieso schon getroffen

das verstehe ich nun nicht ganz? meinst du zusätzliche spalte? die brauch ich nicht... ja, ich weiss - komische abfrage - dahinter steckt aber ein monitoring....

danke!

[Forumsgast]

LetzteAenderung ist vom Typ Datetime?
Was genau enthält $StartDateTime der Form nach?

$StartDateTime enthält ein datetime wert :wink:

also zb: '2006-03-14 11:00:54'

grz

Dann auf jeden Fall einen Index über LetzteAenderung erstellen.

also mache zuerst ein MAX(meinPrimaryKey), packe die abfrage in eine schleife welche zusätzlich ein ' AND meinPrimaryKey>$x AND meinPrimaryKey<$y' beinhaltet.

Was Du da mit $x und $y machen willst, klingt mindestens seltsam, eher falsch, wahrscheinlich sehr falsch.
Von Zergling gemeint war eine Abfrage der Form
SELECT ... FROM ... WHERE ... ORDER ... LIMIT x,y - sofern auf das Problem anwendbar.
Spätestens beim Wort "Schleife" ist in 99 von 100 Fällen ein Fehler im Datenbankzugriff oder -design anzunehmen.

[Forumsgast]

Dann auf jeden Fall einen Index über LetzteAenderung erstellen.

LetzteAenderung ist leider auch teilweise NULL - vorallem aber fehlen mir die nötigen rechte sowas zu ändern...

Was Du da mit $x und $y machen willst, klingt mindestens seltsam, eher falsch, wahrscheinlich sehr falsch.
Von Zergling gemeint war eine Abfrage der Form
SELECT ... FROM ... WHERE ... ORDER ... LIMIT x,y - sofern auf das Problem anwendbar.

ja, aber limit bringt doch bei solch einer abfrage nichts - limit wird doch auf das ergebnis angewendet? oder?

Spätestens beim Wort "Schleife" ist in 99 von 100 Fällen ein Fehler im Datenbankzugriff oder -design anzunehmen.

das verstehe ich nun nicht ganz?
diese datenbank wurde für den zweck designed, für welchen sie auch eingesetzt wird. ich versuche das ganze zu monitoren.. evtl. müsste ich wohl doch mal mit dem admin sprechen...:wink:
aber vergesst nicht - wir sprechen hier von >5mio zeilen - nach meiner erfahrung ist es in dieser dimension effizienter 500'000 abfragen zu starten als eine einzelne.

LetzteAenderung ist leider auch teilweise NULL - vorallem aber fehlen mir die nötigen rechte sowas zu ändern...

teilweise NULL Werte stören mysql nicht beim Indizieren. Die notwendigen Rechte sind da schon eher ein Problem. Ja, Admin fragen. Der Platzbedarf ist sicherlich ewiglangen Anfrage-Bearbeitungen vorzuziehen. Wenn es ein ausgewiesener DB-Admin ist, sollte er eh früher oder später auf Dich zukommen wegen des fehlenden Index, wenn Du den Server damit 2000 Sekunden beschäftigen kannst.

ja, aber limit bringt doch bei solch einer abfrage nichts - limit wird doch auf das ergebnis angewendet? oder?

Deshalb das: sofern auf das Problem anwendbar.

diese datenbank wurde für den zweck designed, für welchen sie auch eingesetzt wird.

Habe ich nie bezweifelt.

Spätestens beim Wort "Schleife" ist in 99 von 100 Fällen ein Fehler im Datenbankzugriff oder -design anzunehmen.

Wenn das Design stimmt, greift vielleicht der erste Teil der Aussage? Abfrage-Schleifen sollen vermieden werden. Jede Abfrage kostet Extra-Zeit. Meistens ist auch die zusammengestoppelte Abfrage komplexer in der Abarbeitung als die eigentlich richtige Abfrage.
Sollte eine Schleife tatsächlich unvermeidbar sein, kann das Datenbankdesign (und sei es auch nur für diese eine Aufgabe) ungeeignet sein. Ist das Datenbankdesign nicht zu verbessern, liegt der 1 von 100 Fällen vor. So einfach ist das.

aber vergesst nicht - wir sprechen hier von >5mio zeilen - nach meiner erfahrung ist es in dieser dimension effizienter 500'000 abfragen zu starten als eine einzelne.

Das ist entweder schon lange her oder das Datenbanksystem (oder die Hardware?) taugt nichts oder es wurde sub-optimal genutzt - zB durch fehlende Indizes, unnötige Typkonvertierung bei Vergleichen, fehlerhaften WHERE/ON-Bedingungen oder oder oder.
Wenn Du durch einen einzelnen, einfachen Datumvergleich aus 5 Millionen Datensätzen 1000 auswählst, dann ist das keine schlimme Aufgabe für eine Mysql Datenbank.

[Forumsgast]

Du kannst das Ergebnis ja eventuell auch cachen, also jede Nacht generieren lassen und nur auf diese Werte zugreifen.

[Forumsgast]

guten morgen..

LetzteAenderung ist leider auch teilweise NULL - vorallem aber fehlen mir die nötigen rechte sowas zu ändern...

teilweise NULL Werte stören mysql nicht beim Indizieren. Die notwendigen Rechte sind da schon eher ein Problem. Ja, Admin fragen. Der Platzbedarf ist sicherlich ewiglangen Anfrage-Bearbeitungen vorzuziehen. Wenn es ein ausgewiesener DB-Admin ist, sollte er eh früher oder später auf Dich zukommen wegen des fehlenden Index, wenn Du den Server damit 2000 Sekunden beschäftigen kannst.

stimmt - denk ich auch!

Das ist entweder schon lange her oder das Datenbanksystem (oder die Hardware?) taugt nichts oder es wurde sub-optimal genutzt - zB durch fehlende Indizes, unnötige Typkonvertierung bei Vergleichen, fehlerhaften WHERE/ON-Bedingungen oder oder oder.
Wenn Du durch einen einzelnen, einfachen Datumvergleich aus 5 Millionen Datensätzen 1000 auswählst, dann ist das keine schlimme Aufgabe für eine Mysql Datenbank.

nun dies ist ein mysql-replication-server - und hardwaremässig nicht gerade optimal bestückt.. aus diesem grunde auch das problem mit grösseren abfragen - 1gb ram reichen nicht aus... bei dieser abfrage beginnt der server zu swappen.... das hardware-upgrade hat also höchste prio!

die abfrage selbst ist dieselbe, mal abgesehen von den den feld und tabellennamen, ich benötige nur den primary-key...

bezüglich schleifen:
ich habe nun kurz einen test gemacht mit einem hochzählenden 'limit' von 5000 Zeilen - Dauer: 6367.34721 sek (inklusive array auslesen, gruppieren) - schade! nur einen vorteil sehe ich - die Tabelle ist nicht wirklich lange 'locked', dies bleibt also eher unbemerkt! nachteil - diese abfrage sollte mal stündlich ausgeführt werden - passt so nicht rein...

Du kannst das Ergebnis ja eventuell auch cachen, also jede Nacht generieren lassen und nur auf diese Werte zugreifen.

Wenn ich dich richtig verstanden habe, mache ich dies bereits. Die abfrage wird ein einem Script weiterverarbeitet und in eine andere Datenbank geschrieben. Diese Abfrage sollte aber eigentlich jede Stunde durchgeführt werden... und da ist es schlecht, wenn die Tabelle jedesmal für 30 min Locked ist.

Eine kleine Optimierung habe ich inzwischen gefunden!

SELECT meinPrimaryKey FROM tabelle WHERE LetzteAenderung BETWEEN $StartDateTime AND NOW();

ist ca. 200 sekunden schneller :wink:


btw: Langsam frag ich mich ob ich nicht sauberer ist auf die Binlogs des Replicant zugreife um diese mehr oder weniger Realtime weiter zu verarbeiten...

danke für eure hilfe! ich werde bald mal alle unnötigen dienste stoppen um etwas speicher freizugeben und die abfragen nochmal laufen lassen - mal sehen!
grz

Ich habe eben ein paar Tests auf meinem Arbeitsrechner (p4-2GHz mit 1GB Ram, einzelne SATA-Platte) durchgeführt.

Nur Primärindex über Id

CREATE TABLE `seltest` (
  `Id` int(11) NOT NULL auto_increment,
  `payload` varchar(128) collate latin1_german1_ci default NULL,
  `LetzteAenderung` datetime default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci

SELECT Count(Id) FROM seltest -> 5000000, Dauer:0.01 Sekunde
SELECT Count(Id) FROM seltest WHERE LetzteAenderung >= '2006-03-01 12:00:00' -> 915, Dauer:2.282 Sekunden

zusätzlicher Index über LetzteAenderung (das Hinzufügen des Index bei 5M Datensätzen hat ca. zwei Minuten Vollast erzeugt)

CREATE TABLE `seltest` (
  `Id` int(11) NOT NULL auto_increment,
  `payload` varchar(128) collate latin1_german1_ci default NULL,
  `LetzteAenderung` datetime default NULL,
  PRIMARY KEY  (`Id`),
  KEY `indexLA` (`LetzteAenderung`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci

SELECT Count(Id) FROM seltest WHERE LetzteAenderung >= '2006-03-01 12:00:00' -> 915, Dauer:0.02 Sekunden

Ich habe daraufhin noch einige zehntausend Datensätze hinzufügen lassen und Mysql neu gestartet, damit kein cache das Ergebnis verfälscht.
SELECT Count(Id) FROM seltest WHERE LetzteAenderung >= '2006-03-01 12:00:00' -> 927, Dauer:0.01 Sekunden

2.282 Sekunden gegenüber 0.02 Sekunden. Das ist Faktor 114 und sollte für sich selbst sprechen.
Eigentlich müsste der Test noch mehrmals und auch mit mehr Daten wiederholt werden. Allein schon, da sich die 0.01/0.02 Sekunden anscheindne im Rahmen der Meßungenauigkeit befinden und die "Aufwärmphase" für die Abfrage zu stark niedeschlagen könnte.

Die Datensätze wurden zufällig erzeugt

$now = getdate();
try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    $stmt = $dbh->prepare("INSERT INTO seltest (payload, LetzteAenderung) VALUES (?, ?)");
    $stmt->bindParam(1, $payload);
    $stmt->bindParam(2, $LA);
    
    for($i=0; $i<5000; $i++) {
        echo $i, '.cycle   ';
        for($j=0; $j<1000; $j++) {
            if (rand(0,4900)==2500) {
                $LAt = mktime(rand(0,23), rand(0,59), rand(0,59), $now['mon'], $now['mday']-rand(0,30), $now['year']);
            }
            else {
                $LAt = mktime(rand(0,23), rand(0,59), rand(0,59), $now['mon']-rand(1,24), $now['mday']-rand(1,30), $now['year']);
            }
                
            $LAt = getdate($LAt);
            $LA = sprintf('%04s-%02s-%02s %02s:%02s:%02s', $LAt['year'],$LAt['mon'],$LAt['mday'],$LAt['hours'],$LAt['minutes'],$LAt['seconds']);
            $payload = md5($LA);
            $stmt->execute();
        }
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "
";
    die();
} 

[Forumsgast]

guten morgen bruchpilot,

vielen dank für deinen umfangreichen test!
ich habe inzwischen einen index angelegt: (dauerte mehrs als 12h...)

// query 0: >='2006-03-14 11:00:59'
query 0 dauer: 42.63169s
// query 1: BETWEEN '2006-03-13 11:02:34' AND NOW()'
query 1 dauer: 8.43394s

fragt sich ob query1 vom cache profitiert hat....

ansonsten... wunderbar! problem ist aber, dass dieser index nur auf dem von mir verwendeten slave erzeugt wurde... dies könnte evtl mal probleme geben.. aber soweit so gut!

danke euch

Wie ist der Server eigentlich ausgerüstet?
Und wieviel Nutzdaten werden über den Daumen gepeilt von mysql an den Client übertragen?

Nur so interessehalber, weil sich die Werte ja um Größenordnungen unterscheiden.

[Forumsgast]

hi

Wie ist der Server eigentlich ausgerüstet?

hardware? nicht gerade server-mässig... (ist aber bestellt...)
1gb ram, 1xP4 mit 2,4 ghz

Und wieviel Nutzdaten werden über den Daumen gepeilt von mysql an den Client übertragen?

~500'000 selects die minute (total ~1 mio abfragen die minute)

Nur so interessehalber, weil sich die Werte ja um Größenordnungen unterscheiden.

diese beiden test wurden nicht unter 'last' ausgeführt.
ich hab dies aber gerade nachgeholt, und die reihenfolge der abfragen mal umgekehrt.
anscheinend benötigt die erste abfrage immer länger als die zweite.... fragt sich ob der mysql-server teile der erste abfrage cachen kann um die dann in der zweiten abfrage wieder zu verwenden?

grz

ch habe eben ein paar Tests auf meinem Arbeitsrechner (p4-2GHz mit 1GB Ram, einzelne SATA-Platte) durchgeführt.

SELECT Count(Id) FROM seltest WHERE LetzteAenderung >= '2006-03-01 12:00:00' -> 915, Dauer:0.02 Sekunden

Deine Kiste ist (den knappen Eckwerten nach) schneller als meine und braucht so viel länger für

SELECT meinPrimaryKey FROM tabelle WHERE LetzteAenderung>=$StartDateTime

Ich frage nur das Count() ab und nicht die eigentlichen Nutzdaten. Das kann schon etwas ausmachen. Aber so viel... ?
Im Grunde kann's mir ja egal sein. Aber verwundern tut's mich halt doch.

~500'000 selects die minute (total ~1 mio abfragen die minute)

Hust, das sind 500000/60 also ~8300 pro Sekunde. Sicher? Soviele SELECT x,y,z, FROM a WHERE.... ?
Wer erzeugt denn wofür soviele SQL Anfragen?

Wie gesagt, eigentlich egal, aber es macht mich neugierig.