List:Cluster« Previous MessageNext Message »
From:Hendrik Woltersdorf Date:January 29 2010 2:57pm
Subject:Re: howto implement a fifo queue in MySQL Cluster ?
View as plain text  
Thanks for your effords. I will try setting prefetch to 1 in the triggers
some day next week and install a new version as soon as possible .
last_insert_id() does not provide any problems. It just fetchs the
generated value and works.

kind regards

Hendrik Woltersdorf
XCOM AG
Tel.: 0375/27008-580
Tel. mobil: 0178/5710078
Email: Hendrik.Woltersdorf@stripped

Martin.Skold@stripped schrieb am 29.01.2010 15:49:27:

> Martin Skold <Martin.Skold@stripped>
> Gesendet von: Martin.Skold@stripped
>
> 29.01.2010 15:49
>
> An
>
> Hendrik Woltersdorf <hendrik.woltersdorf@stripped>
>
> Kopie
>
> Cluster <cluster@lists.mysql.com>
>
> Thema
>
> Re: howto implement a fifo queue in MySQL Cluster ?
>
> I assume you have read the docs on last_insert_id()
> http://dev.mysql.com/doc/refman/5.1/en/information-
> functions.html#function_last-insert-id
> ...
> '* For stored functions and triggers that change the value, the value is
>    restored when the function or trigger ends, so following statements
> will not see a changed value. '
>
> BTW, the bug was related to delayed flushing of
> batched inserts when triggers are executed.
> Would be good to be able to rule out that one.
>
> Your experience with jumps in auto_increment values
> hints there is some prefetch going on somewhere.
> Try set it to 1 in the trigger just before the
> insert statement, just to be sure.
>
> BR
> -- Martin
>
> Hendrik Woltersdorf wrote:
> > No, I am using version 7.0.7 (and waiting for the binaries of 7.0.10).
> > The DATETIME (= NOW()) and the AUTO_INCREMENT column are in the same
table
> > (Q2X_HEAD). We do only single row inserts in these applications.
> > The AUTO_INCREMENT column in table Q2X_HEAD is (per default)
incremented by
> > 1.
> > Setting ndb_autoincrement_prefetch_sz to 1 made the "jumps" in the ID
> > values smaller (< 30). With ndb_autoincrement_prefetch_sz = 256
(yesterday)
> > I saw "jumps" of > 5000:
> >
> > ID    DATE_INSERT
> > ------------------------------------
> > 8484, '2010-01-28 15:00:28'
> > 8483, '2010-01-28 15:00:28'
> > 8482, '2010-01-28 15:00:28'
> > 8481, '2010-01-28 15:00:28'
> > 13945, '2010-01-28 15:00:23'
> > 13944, '2010-01-28 15:00:23'
> > ...
> > 13928, '2010-01-28 15:00:13'
> > 13927, '2010-01-28 15:00:13'
> > 8480, '2010-01-28 14:58:56'
> > 8479, '2010-01-28 14:58:56'
> >
> > I will repeat this tests as soon as I am able (and allowed) to install
a
> > newer version. If this does not help, I will have to fall back to the
> > "second best" method. That means, I will not use auto_increment but
> > generate the new ID values with select max(ID)+1 and deal with the
> > duplicate key and ndb timout errors. This will hurt performance
severely
> > but is guaranteed to work.
> >
> > kind regards
> >
> > Hendrik Woltersdorf
> > XCOM AG
> > Tel.: 0375/27008-580
> > Tel. mobil: 0178/5710078
> > Email: Hendrik.Woltersdorf@stripped
> >
> > Martin.Skold@stripped schrieb am 29.01.2010 15:03:08:
> >
> >> Martin Skold <Martin.Skold@stripped>
> >> Gesendet von: Martin.Skold@stripped
> >>
> >> 29.01.2010 15:03
> >>
> >> An
> >>
> >> Hendrik Woltersdorf <hendrik.woltersdorf@stripped>
> >>
> >> Kopie
> >>
> >> Cluster <cluster@stripped>
> >>
> >> Thema
> >>
> >> Re: Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue
> >> in MySQL Cluster ?
> >>
> >> I assume you are using the latest version?
> >> There was a bug fixed some months ago:
> >> Bug #38034 last_insert_id() value not shown in table with trigger
> >>
> >> Do I understand this correctly, the DATE (or TIMESTAMP)
> >> is on one table and the AUTO_INCREMENT is on another?
> >> What does your inserts into STATE_HISTORY look like?
> >> Are you doing any multi-inserts anywhere? If you do
> >> INSERT ... VALUES <multiple values>; then the auto_inc pre-fetch
> >> will be the number of values inserted. If you do INSERT ... WHERE
> >> then it will minimum 32 or the value of ndb_autoincrement_prefetch_sz
> >> if that is > 32. The trigger will execute for each row, but the
> >> last_insert_id() will see the value after prefetching.
> >> Even if you only do single row inserts then this could still be a
> >> problem, but of course difference should no be two minutes.
> >> Try defining the auto_increment on the Q2X_HEAD table instead, that
> >> should increase by steps of 1.
> >>
> >> BR
> >> -- Martin
> >> Hendrik Woltersdorf wrote:
> >>> Hi,
> >>>
> >>> I did not try this with other storage engines. And I need to do this
> >>> inside of the ndbcluster. Locking tables explicitly commits - so says
> >>> the manual - any open transactions. I cannot commit transactions in
> >>> these triggers. This would break the application transactions. btw.
the
> >
> >>> manual tells me also, that lock table works only for the sql node on
> >>> which the lock statement is issued. So the second sql node will not
see
> > it.
> >>> I do not think, that locking will help. In the resultset, I posted
> >>> earlier, the ID 14719 appears more than two minutes(!) after ID
14739.
> >>> And the clocks of the machines are in sync today.
> >>>
> >>> kind regards
> >>>
> >>> Hendrik Woltersdorf
> >>> XCOM AG
> >>> Tel.: 0375/27008-580
> >>> Tel. mobil: 0178/5710078
> >>> Email: Hendrik.Woltersdorf@xcom.de
> >>>
> >>> Inactive hide details for Martin Skold ---29.01.2010 14:06:13---Hi
> >>> again!Martin Skold ---29.01.2010 14:06:13---Hi again!
> >>>
> >>>                         *Martin Skold <Martin.Skold@stripped>*
> >>>                         Gesendet von: Martin.Skold@Sun.COM
> >>>
> >>>                         29.01.2010 14:05
> >>>
> >>>
> >>>
> >>> An
> >>>
> >>> Hendrik Woltersdorf <hendrik.woltersdorf@stripped>
> >>>
> >>> Kopie
> >>>
> >>> Cluster <cluster@stripped>
> >>>
> >
> >
> >
> >
> > *** XCOM AG Legal Disclaimer ***
> >
> > Diese E-Mail einschliesslich ihrer Anhaenge ist vertraulich und ist
allein
> > für den Gebrauch durch den vorgesehenen Empfaenger bestimmt. Dritten
ist
> > das Lesen, Verteilen oder Weiterleiten dieser E-Mail untersagt. Wir
bitten,
> > eine fehlgeleitete E-Mail unverzueglich vollstaendig zu loeschen und
uns
> > eine Nachricht zukommen zu lassen.
> >
> > This email may contain material that is confidential and for the sole
use
> > of the intended recipient. Any review, distribution by others or
forwarding
> > without express permission is strictly prohibited. If you are not the
> > intended recipient, please contact the sender and delete all copies.
> >
> > Hauptsitz: Bahnstrasse 37, D-47877 Willich, USt-IdNr.: DE 812 885 664
> > Kommunikation: Telefon +49 2154 9209-70, Telefax +49 2154 9209-900,
> > www.xcom.de
> > Handelsregister: Amtsgericht Krefeld, HRB 10340
> > Vorstand: Matthias Albrecht, Marco Marty, Dr. Rainer Fuchs, Dirk Werner
> > Vorsitzender des Aufsichtsrates: Stefan H. Tarach
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster?
> unsub=Hendrik.Woltersdorf@stripped
>




*** XCOM AG Legal Disclaimer ***

Diese E-Mail einschliesslich ihrer Anhaenge ist vertraulich und ist allein
für den Gebrauch durch den vorgesehenen Empfaenger bestimmt. Dritten ist
das Lesen, Verteilen oder Weiterleiten dieser E-Mail untersagt. Wir bitten,
eine fehlgeleitete E-Mail unverzueglich vollstaendig zu loeschen und uns
eine Nachricht zukommen zu lassen.

This email may contain material that is confidential and for the sole use
of the intended recipient. Any review, distribution by others or forwarding
without express permission is strictly prohibited. If you are not the
intended recipient, please contact the sender and delete all copies.

Hauptsitz: Bahnstrasse 37, D-47877 Willich, USt-IdNr.: DE 812 885 664
Kommunikation: Telefon +49 2154 9209-70, Telefax +49 2154 9209-900,
www.xcom.de
Handelsregister: Amtsgericht Krefeld, HRB 10340
Vorstand: Matthias Albrecht, Marco Marty, Dr. Rainer Fuchs, Dirk Werner
Vorsitzender des Aufsichtsrates: Stefan H. Tarach
Thread
WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
  • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Martin Skold29 Jan
    • Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQLCluster ?Hendrik Woltersdorf29 Jan
      • Re: Antwort: Re: WG: Re: Antwort: Re: howto implement a fifo queue inMySQL Cluster ?Martin Skold29 Jan
        • Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
          • Re: howto implement a fifo queue in MySQL Cluster ?Martin Skold29 Jan
            • Re: howto implement a fifo queue in MySQL Cluster ?Hendrik Woltersdorf29 Jan
    • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Tom Hanlon29 Jan
    • Re: WG: Re: Antwort: Re: howto implement a fifo queue in MySQL Cluster?Tom Hanlon29 Jan
Re: howto implement a fifo queue in MySQL Cluster ?Tom Hanlon2 Feb