Abfrage hängt mit "Sending data"

Hallo !

Kämpfe seit Tagen mit einem Performance-Problem in verschiedenen=20
Ausprägungen:

Ein join auf eine grosse Tabelle (InnoDB 1.5 Mio Einträge) liefert=
=20
Antwort erst nach
einem Tag oder gar nicht. Tabellenstruktur, cnf-Setup und debug siehe=20
unten. Abfrage:
(aus zwei verschiedenen Gruppen [sub_id] suche alle Messungen aus=20
bestimmten
Zeitintervallen [zpd_time], die zeitlich nah beieinander liegen, und=20
zeige deren geo_id)

select t1.sub_id,t2.sub_id,t1.geo_id,t2.geo_id from results as=20
t1,results as t2
where t1.sub_id=3D139 and t2.sub_id=3D44 and
(t1.zpd_time between 20020924000000 and 20020924235959 or
t1.zpd_time between 20030322000000 and 20030322235959 or
t1.zpd_time between 20031216000000 and 20031216235959 or
t1.zpd_time between 20030401000000 and 20030401232359 or
t1.zpd_time between 20021020000000 and 20030312235959 or
t1.zpd_time between 20020918000000 and 20021013235959 or
t1.zpd_time between 20031021000000 and 20031024235959)
and abs(t2.zpd_time-t1.zpd_time) <5 limit 2000000;

geht sofort in den Status 'Sending data', aber kommt auch
nach 36 Stunden nicht zurück. CPU am Server (Sun Enterprise 250
mit 2x 400MHz UII Processor, Solaris 8, 2 GB RAM, Gbit Netzwerk)
am Anschlag, mysqld läuft immer mit 1.2 GB vsize, 680 MB rss.
Es werden aber keinerlei Daten an den Client versendet=20
(geprüft mit snoop). Normale Abfragen werden nach wie vor
in normaler Geschwindigkeit bearbeitet, die Datenbank
hat keine anderen Abfragen zu bearbeiten.

Hänge ich ein 'order by t2.sub_id,t1.geo_id' an,
geht es sofort in den Status 'Copying to tmp table', liefert
dann nach immerhin 19 Stunden ein Ergebnis.

Index gesetzt auf sub_id, explain liefert mit 'order':
+-------+------+---------------+--------+---------+-------+- ------+------=
---------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra=
|
+-------+------+---------------+--------+---------+-------+- ------+------=
---------------------------------------+
| t2 | ref | sub_id | sub_id | 4 | const | 2128 | where=
used; Using temporary; Using filesort |
| t1 | ref | sub_id | sub_id | 4 | const | 51584 | where=
used |
+-------+------+---------------+--------+---------+-------+- ------+------=
---------------------------------------+

und ohne 'order':
+-------+------+---------------+--------+---------+-------+- ------+------=
------+
| table | type | possible_keys | key | key_len | ref | rows | Extra=
|
+-------+------+---------------+--------+---------+-------+- ------+------=
------+
| t2 | ref | sub_id | sub_id | 4 | const | 2128 | where=
used |
| t1 | ref | sub_id | sub_id | 4 | const | 51584 | where=
used |
+-------+------+---------------+--------+---------+-------+- ------+------=
------+

Was ist da los ? Die gleiche Abfrage auf ähnlicher Tabelle in
Oracle liefert nach wenigen Sekunden eine Antwort. Ist=20
'Sending Data' nur eine Fehlinformation ? Dieses Problem taucht
immer wieder in den Newsgroups auf, aber jedesmal ohne eine
schlüssige Antwort.
==================== =====3D=
==================== =====3D=
====
Mysql Ver 12.14 Distrib 4.0.4-beta, for sun-solaris2.8=20
==================== =====3D=
==================== =====3D=
====
Tabellenstruktur:
create table results (
updated TIMESTAMP(14),
meta_id CHAR(20) NOT NULL,
target VARCHAR(60) NOT NULL,
sub_id INT(10) NOT NULL,
geo_id VARCHAR(22) NOT NULL,
casename VARCHAR(60) NOT NULL,
baseline VARCHAR(60) NOT NULL,
orbit INT(5) NOT NULL,
version ENUM("V0","V1","V2","V3","V4","V5","V0T","V1T",
"V2T","V3T","V4T","V5T","V0O","V1O","V2O",
"V3O","V4O","V5O","V0S","V1S","V2S",
"V3S","V4S","V5S") NOT NULL,
zpd_time TIMESTAMP(14) NOT NULL,
geo_lat FLOAT(7) NOT NULL,
geo_lon FLOAT(7) NOT NULL,
sun_elevation FLOAT(7) NOT NULL,
no_of_tangs SMALLINT(3) NOT NULL,
tang_min FLOAT(7) NOT NULL,
proc_time TIMESTAMP(14) NOT NULL,
status ENUM("UNKNOWN","OK","NOT_CONVERGED",
"FAILED","CORRUPT") NOT NULL,
PRIMARY KEY (meta_id, target)
) type=3DInnoDB;
Zusätzlicher Index gesetzt auf sub_id.
==================== =====3D=
===============3D
DB Setup orientiert sich an my.huge:
set-variable =3D key_buffer=3D512M
set-variable =3D max_allowed_packet=3D16M
set-variable =3D table_cache=3D256
set-variable =3D sort_buffer=3D60M
set-variable =3D record_buffer=3D60M
set-variable =3D read_rnd_buffer_size=3D60M
set-variable =3D thread_cache=3D4
# Try number of CPU's*2 for thread_concurrency
set-variable =3D thread_concurrency=3D4
set-variable =3D max_join_size=3D2000000000
set-variable =3D join_buffer_size=3D16000000 =20
log-bin
set-variable =3D innodb_buffer_pool_size=3D512M
set-variable =3D innodb_additional_mem_pool_size=3D64M
# Set .._log_file_size to 25 % of buffer pool size
set-variable =3D innodb_log_file_size=3D192M
set-variable =3D innodb_log_buffer_size=3D80M
innodb_flush_log_at_trx_commit=3D1
set-variable =3D innodb_lock_wait_timeout=3D50
==================== mysqladmin d=
ebug (Fall ohne 'order') =======3D
Alarm status:
Active alarms: 0
Max used alarms: 2
Next alarm time: 0

Status information:

Current dir: /home/Processor/mysql/var/
Current locks:
lock: 2f4f9518:
lock: 2f4f5ad8:
lock: 2f4f5a30:
lock: 2f4df300:
lock: 2f492ae0:
lock: 2f496b68: read
read : 2f49ff6c (13:1); 2d7ca84c (13:1);=20

key_cache status:
blocks used: 1
not flushed: 0
w_requests: 0
writes: 0
r_requests: 2
reads: 1

handler status:
read_key: 1391
read_next: 58842433
read_rnd 0
read_first: 3
write: 0
delete 0
update: 0

Table status:
Opened tables: 15
Open tables: 9
Open files: 2
Open streams: 0

Alarm status:
Active alarms: 0
Max used alarms: 2
Next alarm time: 0
==================== =3D 12 Stunde=
n später ==================== =
=======3D
Status information:

Current dir: /home/Processor/mysql/var/
Current locks:
lock: 2f492200:
lock: 2f4f4da0:
lock: 2f4f9518:
lock: 2f4f5ad8:
lock: 2f4f5a30:
lock: 2f4df300:
lock: 2f492ae0:
lock: 2f496b68: read
read : 2f49ff6c (13:1); 2d7ca84c (13:1);=20

key_cache status:
blocks used: 1
not flushed: 0
w_requests: 0
writes: 0
r_requests: 2
reads: 1

handler status:
read_key: 5454
read_next: 241595246
read_rnd 0
read_first: 11
write: 0
delete 0
update: 0

Table status:
Opened tables: 17
Open tables: 11
Open files: 2
Open streams: 0

Alarm status:
Active alarms: 0
Max used alarms: 3
Next alarm time: 0

--=20
Dr. Udo Grabowski email: udo.grabowski@imk.fzk.=
de
Institut f. Meteorologie und Klimaforschung ASF,Forschungszentrum Karlsru=
he
Postfach 3640, 76021 Karlsruhe, Germany Tel: (+49) 7247 82-60=
26
http://www.fzk.de/imk/asf/ame/grabowski/ Fax: " -70=
26

Re: Abfrage =?UTF-8?B?aMOkbmd0IG1pdCAnU2VuZGluZyBkYXRhJw==?

Udo Grabowski wrote:
>
> Ein join auf eine grosse Tabelle (InnoDB 1.5 Mio Einträge) liefert
> Antwort erst nach einem Tag oder gar nicht. Tabellenstruktur,
> cnf-Setup und debug siehe unten.

> select t1.sub_id,t2.sub_id,t1.geo_id,t2.geo_id from results as
> t1,results as t2
> where t1.sub_id=3D139 and t2.sub_id=3D44 and
> (t1.zpd_time between 20020924000000 and 20020924235959 or
> t1.zpd_time between 20030322000000 and 20030322235959 or
> t1.zpd_time between 20031216000000 and 20031216235959 or
> t1.zpd_time between 20030401000000 and 20030401232359 or
> t1.zpd_time between 20021020000000 and 20030312235959 or
> t1.zpd_time between 20020918000000 and 20021013235959 or
> t1.zpd_time between 20031021000000 and 20031024235959)
> and abs(t2.zpd_time-t1.zpd_time) <5 limit 2000000;

Auf den ersten Blick würde man einen Index auf (sub_id, zpd_time) haben
wollen, damit die Selektion der Teilmengen zu den beiden sub_ids über
den Index läuft. Allerdings selektierst du immer *alle* Meßwerte zur
zweiten Meßstelle, obwohl nur wenige jeweils in einem passenden
Zeitintervall liegen dürften.

Auf den zweiten Blick bemerkt man, daß immer noch ein full join bleibt,
noch dazu mit einer aufwendig zu berechnenden (nicht indizierbaren)
join-Bedingung. Der Aufwand ist etwa quadratisch in der mittleren Größe
der selektierten Teilmengen.

> +-------+------+---------------+--------+---------+-------+- ------+
> | table | type | possible_keys | key | key_len | ref | rows |
> +-------+------+---------------+--------+---------+-------+- ------+
> | t2 | ref | sub_id | sub_id | 4 | const | 2128 |
> | t1 | ref | sub_id | sub_id | 4 | const | 51584 |
> +-------+------+---------------+--------+---------+-------+- ------+

Mit dem unvollständigen Index auf sub_id sind immerhin ~110.000.000
Kombinationen zu bilden. Ein vollständiger Index (sub_id, zpd_time)
würde das vermutlich auf wenige Hundert kollabieren lassen, macht
weniger als 1 Mio Kombinationen.

Wenn man noch genauer hinschaut sieht man, daß die Zeitintervalle
jeweils so weit auseinander liegen, daß Meßwerte aus verschiedenen
Teilintervallen die Bedingung "max 5 sec Abstand" ohnehin nicht
erfüllen können. Die optimale Query würde dann für jedes Teil-
intervall einen JOIN machen und die Ergebnisse zusammenfassen.
Die erlaubten Werte für t2.zpd_time würde man natürlich auch
einschränken, indem man das Intervall für t1 jeweils um 5 Sekunden
nach vorn und hinten erweitert.

SELECT ...
FROM results AS t1,
results AS t2
WHERE t1.subid = $id1
AND t2.subid = $id2
AND t1.zpd_time BETWEEN $ts1 AND $ts2
AND t2.zpd_time BETWEEN $ts1-5 AND $ts2+5
AND ABS(t1.zpd_time-t2.zpd_time) < 5
UNION
SELECT ...
.... (weiter mit dem zweiten Zeitintervall)
UNION
SELECT ...
.... (weiter mit dem dritten Zeitintervall)
....

Kleine Effizienzbetrachtung: nehmen wir an, jedes der 8 Zeitintervalle
liefert 10 Meßwerte. Der JOIN über alle 8 Intervalle erzeugt also
800*800 = 640.000 Zwischenwerte. 8 Einzeljoins hingegen 8*10*10 = 800.
Also Faktor 800 schneller. -> make it so!


> Mysql Ver 12.14 Distrib 4.0.4-beta, for sun-solaris2.8

Ebenso könnte es helfen, diese fast dreieinhalb Jahre(!) alte Beta(!)
gegen eine aktuellere Version zu tauschen. Zumindest gegen eine
aktuelle 4.0.x, besser 5.0.x.


XL

Re: Abfrage hängt mit "Sending data"

Udo Grabowski schrieb:
> Hallo !

Ja, Sending Data ist definitiv ein seltsamer Status, habe ich auch schon
beobachtet.

Du hast sicher schon bemerkt, dass du eine beta-Software verwendest, für
Sun Solaris gibt es auch eine 4.0.26.

http://downloads.mysql.com/archives.php?p=mysql-4.0&o=solari s

Das Update würde ich auf jeden Fall mal andenken.

Desweiteren würde ich auf jeden Fall auf zpd_time einen Index setzen und
auf das Limit würde ich auch verzichten, wenn du sowieso nur 1.5
Millionen Datensätze hast, können 2.0 Millionen gar nicht geliefert
werden. Das wird aber wahrscheinlich nichts ändern.

Dann würde ich den Wert für innodb_buffer_pool_size noch höher setzen,
und im Gegenzug key_buffer runter, wenn du nur InnoDB verwendest.

Das wären meine Ideen.

Grüße

Dirk

Re: Abfrage =?UTF-8?B?aMOkbmd0IG1pdCAnU2VuZGluZyBkYXRhJw==?

Aha !

Habe mit einem zweiten, getrennten Index auf zpd_time experimentiert,=20
bringt aber nix,
da es sowieso schon intern Integer ist. Werde mal den kombinierten Index =

probieren.

>Allerdings selektierst du immer *alle* Meßwerte zur
>zweiten Meßstelle, obwohl nur wenige jeweils in einem passenden
>Zeitintervall liegen dürften.=20
> =20
>
Hier habe ich die gleiche Bedingungsphalanx nochmal für t2.zpd_time mit=
=20
and angehängt,
bringt aber nix. Der Aufwand scheint in ganz bestimmten der gegeben=20
Zeitintervalle exorbitant
hoch zu sein. Habe diese weiter aufgesplittet, muss dann aber teilweise=20
in 4tel Tagen
vorwärtskriechen, damit das klappt:

explain select .... from results as t1 , results as t2 =20
where t1.sub_id=3D139 and t2.sub_id=3D44 and=20
abs(t1.zpd_time-t2.zpd_time) <5 and t2.zpd_time=20
between 20020920180000 and 20020920235959 and=20
t1.zpd_time between 20020920180000 and 20020920235959;

liefert immer noch rows=3D2128 x 18543, und mysql schaltet allein=20
auf ref t1=3DNULL, weil der Aufwand für den Index nix mehr bringt.
Endet dann wieder im 'Sending Data' Zustand ohne Ende.
Habe mittlerweile den Eindruck, dass 'Sending Data' einfach=20
heisst: Ich mache irgendwas, wofür ich gerade keine passende
Message hab...

>Wenn man noch genauer hinschaut sieht man, daß die Zeitintervalle
>jeweils so weit auseinander liegen, daß Meßwerte aus verschiedenen
>Teilintervallen die Bedingung "max 5 sec Abstand" ohnehin nicht
>erfüllen können. =20
>
Guter Punkt, tatsächlich bilden alle ausser jeweils einer Gruppe mit=20
allen anderen einen
unnötigen quadratischen Suchraum in der Zeit . Allerdings vermindert=20
dies für einige
Intervalle nicht den Aufwand, siehe oben .

>Kleine Effizienzbetrachtung: nehmen wir an, jedes der 8 Zeitintervalle
>liefert 10 Meßwerte. Der JOIN über alle 8 Intervalle erzeugt also
>800*800 =3D 640.000 Zwischenwerte. 8 Einzeljoins hingegen 8*10*10 =3D 80=
0.
>Also Faktor 800 schneller. -> make it so!
>
> =20
>
Klingt sehr überzeugend, für die meisten Intervalle bringt das auch w=
as.=20
Mal sehen, ob der
Kombi-Index in den problematischen Intervallen weiterhilft.

> Ebenso könnte es helfen, diese fast dreieinhalb Jahre(!) alte Beta(!)=

>
>gegen eine aktuellere Version zu tauschen. Zumindest gegen eine
>aktuelle 4.0.x, besser 5.0.x.
>
> =20
>
Im Prinzip jaaa.... In Produktionsumgebungen ist das immer so eine=20
Sache, unglücklicherweise
kam dazu eine unglückliche inkompatible Änderung für Timestamps in =
den neuen
Versionen ab 4.1, die uns mit unseren existierenden Scripten ziemlich=20
kalt erwischt
hat, und die Beta ist einfach Beta geblieben, da die nachfolgenden Fixes =

bis zum Release
für uns nicht relevant waren. Momentan versuche ich hier aber doch, auf=
=20
eine aktuelle
5.0.16 zu kommen, irgendwann muss man mal in den sauren Apfel beissen=20
und Scripte
umschreiben.

Also erst einmal herzlichen Dank für die tieferen Einsichten !

--=20
Dr. Udo Grabowski email: udo.grabowski@imk.fzk.=
de
Institut f. Meteorologie und Klimaforschung ASF,Forschungszentrum Karlsru=
he
Postfach 3640, 76021 Karlsruhe, Germany Tel: (+49) 7247 82-60=
26
http://www.fzk.de/imk/asf/ame/grabowski/ Fax: " -70=
26

Re: Abfrage hängt mit "Sending data"

Dirk Brosowski wrote:

> Udo Grabowski schrieb:
>
>> Hallo !
>
>
> Ja, Sending Data ist definitiv ein seltsamer Status, habe ich auch=20
> schon beobachtet.
>
> Du hast sicher schon bemerkt, dass du eine beta-Software verwendest,=20
> für Sun Solaris gibt es auch eine 4.0.26.
>
Ja, siehe meine Antwort auf Axel Schwenkes Hilfe.

> Desweiteren würde ich auf jeden Fall auf zpd_time einen Index setz=
en ...

zpd_time ist schon Integer und fast eindeutig, einen Index habe ich=20
jetzt auch gesetzt, bringt aber nix.

> Dann würde ich den Wert für innodb_buffer_pool_size noch h=C3=
=B6her setzen,=20
> und im=20

> Gegenzug key_buffer runter, wenn du nur InnoDB verwendest.

Werde es ausprobieren.
Danke für die Hinweise !

--=20
Dr. Udo Grabowski email: udo.grabowski@imk.fzk.=
de
Institut f. Meteorologie und Klimaforschung ASF,Forschungszentrum Karlsru=
he
Postfach 3640, 76021 Karlsruhe, Germany Tel: (+49) 7247 82-60=
26
http://www.fzk.de/imk/asf/ame/grabowski/ Fax: " -70=
26

Re: Abfrage =?UTF-8?B?aMOkbmd0IG1pdCAnU2VuZGluZyBkYXRhJw==?

Udo Grabowski wrote:
>
> Habe mit einem zweiten, getrennten Index auf zpd_time experimentiert,
> bringt aber nix,

Logisch. MySQL (vor 5.0) verwendet nur einen Index je Tabelle.

> Habe mittlerweile den Eindruck, dass 'Sending Data' einfach
> heisst: Ich mache irgendwas, wofür ich gerade keine passende
> Message hab...

Nö. MySQL verwendet ja diese zweistufige Architektur mit SQL-Parser und
Storage-Engine. Sobald die obere Schicht so weit ist, daß "nur noch"
das Resultset gelesen und an den Client geschickt werden muß, wechselt
der Status der Verbindung zu "Sending Data". Allerdings ist zum Lesen
der Ergebnisse für jede Zeile wieder ein Roundtrip zur Storage Engine
nötig. Wenn der lange dauert, dann klemmt es halt.

Zum Vergleich: wenn man ORDER BY verwendet und der Optimizer entschei-
det, eine temporäre Tabelle zu verwenden, läuft der Löwenanteil der
Zeit im Status "Copying to Temp Table". Danach läuft der Rest der
Arbeit ohne daß die Storage Engine involviert ist. Wenn die temporäre
Tabelle dann sortiert ist, wechselt der Status zu "Sending Data".

Mal sehr volkstümlich erklärt. Aber ich hoffe das Prinzip wird klar.

>>Kleine Effizienzbetrachtung: nehmen wir an, jedes der 8 Zeitintervalle
>>liefert 10 Meßwerte. Der JOIN über alle 8 Intervalle erzeugt also
>>800*800 =3D 640.000 Zwischenwerte. 8 Einzeljoins hingegen 8*10*10 = 800.
>>Also Faktor 800 schneller. -> make it so!

Öhhm. Zahlenrechnen sollte ich lieber vermeiden. Im ersten Fall kommen
80 * 80 = 6400 Zwischenergebnisse raus. Also nur Faktor 8.

>> Ebenso könnte es helfen, diese fast dreieinhalb Jahre(!) alte Beta(!)
>>
>>gegen eine aktuellere Version zu tauschen. Zumindest gegen eine
>>aktuelle 4.0.x, besser 5.0.x.
>>
> Im Prinzip jaaa.... In Produktionsumgebungen ist das immer so eine
> Sache, unglücklicherweise
> kam dazu eine unglückliche inkompatible Änderung für Timestamps in
> den neuen
> Versionen ab 4.1

Dan wechsele halt auf die letzte 4.0.

> und die Beta ist einfach Beta geblieben, da die nachfolgenden Fixes
> bis zum Release für uns nicht relevant waren.

Das bezweifle ich. Insbesondere an InnoDB wurde damals noch massiv
rumgeschraubt. Von Crashes über schlechte Performance ist da eigentlich
noch alles möglich.


XL

Re: Abfrage =?UTF-8?B?aMOkbmd0IG1pdCAnU2VuZGluZyBkYXRhJw==?

Axel Schwenke wrote:

>>Habe mittlerweile den Eindruck, dass 'Sending Data' einfach
>>heisst: Ich mache irgendwas, wofür ich gerade keine passende
>>Message hab...
>> =20
>>
>
>Nö. MySQL verwendet ja diese zweistufige Architektur mit SQL-Parser un=
d
>Storage-Engine. ...
>
>Mal sehr volkstümlich erklärt. Aber ich hoffe das Prinzip wird klar.=

>
> =20
>
Ah ! Das erklärt jetzt einiges. =20

>Dan wechsele halt auf die letzte 4.0.
>
> =20
>
Nee, wenn schon, denn schon. Ausserdem hat die 5er Serie eine Reihe=20
hübscher neuer
Features, die wir unbedingt ausprobieren wollen (z.B. die neuen Typen=20
für geographische
Daten). Wir wechseln bei unserem 24x7 Betrieb nicht oft, wenn, dann aber =

richtig.

Habe mittlerweile auch den Kombi - Index implementiert , und: Bingo !,=20
die Zahlen
gehen drastisch runter, statt 2400 x 21000 joints nur noch 2400 x 2500 !
Das werd' ich jetzt auch bei unseren restlichen Tabellen machen, da gibt'=
s
noch einige Sorgenkinder.

Heissen Dank für den guten Tip !
--=20

Dr. Udo Grabowski email: udo.grabowski@imk.fzk.=
de
Institut f. Meteorologie und Klimaforschung ASF,Forschungszentrum Karlsru=
he
Postfach 3640, 76021 Karlsruhe, Germany Tel: (+49) 7247 82-60=
26
http://www.fzk.de/imk/asf/ame/grabowski/ Fax: " -70=
26