Hate when that happens !
--- On Thu, 6/16/11, Rick James <rjames@stripped> wrote:
> From: Rick James <rjames@stripped>
> Subject: Re: Regression test returning different result each time it's run
> To: internals@stripped
> Date: Thursday, June 16, 2011, 2:49 PM
> InnoDB's estimates of Rows varies by
> up to about a factor of 2, based on
> the phase of the moon.
>
> On 6/16/11 7:32 AM, Øystein Grøvlen wrote:
> > On 16/06/2011 16:10, Hiromichi Watari wrote:
> >> Hi Øystein,
> >> Thank you for your reply.
> >>
> >> I am using the same storage engine (Innob I
> believe) and repeating the test results in different output
> each time.
> > Hi,
> >
> > By default, InnoDB statistics will vary slightly each
> time you start the
> > server. Hence, you will get different output.
> (See my blog
> > http://oysteing.blogspot.com/2011/04/more-stable-query-execution-time-by.html
> > for a discussion about this.)
> >
> > By default, the select test uses the MyISAM storage
> engine which
> > provides exact statistics.
> >
> > In order to mask such row count differences, a test
> can use the
> > replace_column command:
> >
> > --replace_column 9 x
> > explain select ...
> >
> >
> > Hope this helps,
> >
> > --
> > Øystein
> >
> >> Hiromichi
> >>
> >>
> >> --- On Thu, 6/16/11, Øystein
> Grøvlen<oystein.grovlen@stripped> wrote:
> >>
> >>> From: Øystein Grøvlen<oystein.grovlen@stripped>
> >>> Subject: Re: Regression test returning
> different result each time it's run
> >>> To: "Hiromichi Watari"<hiromichiwatari@stripped>
> >>> Cc: internals@stripped
> >>> Date: Thursday, June 16, 2011, 3:22 AM
> >>> On 15/06/2011 17:08, Hiromichi Watari
> >>> wrote:
> >>>> Hi,
> >>>>
> >>>> I'm running a regression test
> >>> (/mysql-test/t/select.test) on 5.5 server
> (LaunchPad rev
> >>> 3487) but each time I run the test I get
> different number of
> >>> rows in explain select statements.
> >>>> Is this normal behavior ? Or am I
> missing
> >>> something ?
> >>>
> >>> The difference is in the estimated number of
> rows in table
> >>> t2. This
> >>> number is provided by the storage
> engine. Maybe you
> >>> are using a
> >>> different storage engine?
> >>>
> >>> Hope this helps,
> >>>
> >>> --
> >>> Øystein Grøvlen, Principal Software
> Engineer
> >>> MySQL Group, Oracle
> >>> Trondheim, Norway
> >>>
> >>>> Thanks,
> >>>> Hiromichi
> >>>>
> >>>> p.s. I start up the server first then
> issue the
> >>> following command,
> >>>> [hwatari@desktop-intel mysql-test]$ sudo
> >>> ./mysql-test-run.pl --extern
> socket=/tmp/mysql.sock
> >>> t/select.test
> >>>>
> >>>>
> >>>
> -------------------------------------------------------------------------
> >>>>
> >>>> ---
> >>>
> /usr/local/mysql-5.5-3487/mysql-test/r/select.result
> >>> 2011-05-03 15:00:59.000000000 +0300
> >>>> +++
> >>>
> /usr/local/mysql-5.5-3487/mysql-test/r/select.reject
> >>> 2011-06-15 17:09:43.126761202 +0300
> >>>> @@ -133,10 +133,10 @@
> >>>> 1
> >>> SIMPLE t2
> >>> ref fld3
> >>> fld3 30
> >>> const 1 Using
> where;
> >>> Using index
> >>>> explain select fld3
> from t2 ignore
> >>> index (fld3) where fld3 = 'honeysuckle';
> >>>> id
> >>> select_type table
> >>> type possible_keys
> >>> key key_len
> >>> ref rows Extra
> >>>> -1 SIMPLE
> >>> t2 ALL
> >>> NULL NULL
> >>> NULL NULL
> >>> 1199 Using where
> >>>> +1 SIMPLE
> >>> t2 ALL
> >>> NULL NULL
> >>> NULL NULL
> >>> 1209 Using where
> >>>>
> >>>
> >>> --
> >>> MySQL Internals Mailing List
> >>> For list archives: http://lists.mysql.com/internals
> >>> To unsubscribe: http://lists.mysql.com/internals?unsub=1
> >>>
> >>>
> >
>
> --
> Rick James - MySQL Geek
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=1
>
>
