From: Hiromichi Watari
Date: June 18 2011 7:14pm
Subject: Re: Regression test returning different result each time it's run
List-Archive: http://lists.mysql.com/internals/38343
Message-Id: <206713.83292.qm@web121515.mail.ne1.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi =D8ystein,
Thank you for your pointer, that was very helpful.
Hiromichi


--- On Thu, 6/16/11, =D8ystein Gr=F8vlen <oystein.grovlen@stripped> wrote=
:

> From: =D8ystein Gr=F8vlen <oystein.grovlen@stripped>
> Subject: Re: Regression test returning different result each time it's ru=
n
> To: "Hiromichi Watari" <hiromichiwatari@stripped>
> Cc: internals@stripped
> Date: Thursday, June 16, 2011, 10:32 AM
> On 16/06/2011 16:10, Hiromichi Watari
> wrote:
> > Hi =D8ystein,
> > 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.
>=20
> Hi,
>=20
> By default, InnoDB statistics will vary slightly each time
> you start the=20
> server.=A0 Hence, you will get different output. (See my
> blog=20
> http://oysteing.blogspot.com/2011/04/more-stable-query-execution-time-by.=
html
>=20
> for a discussion about this.)
>=20
> By default, the select test uses the MyISAM storage engine
> which=20
> provides exact statistics.
>=20
> In order to mask such row count differences, a test can use
> the=20
> replace_column command:
>=20
> --replace_column 9 x
> explain select ...
>=20
>=20
> Hope this helps,
>=20
> --
> =D8ystein
>=20
> >
> > Hiromichi
> >
> >
> > --- On Thu, 6/16/11, =D8ystein Gr=F8vlen<oystein.grovlen@stripped>=A0
> wrote:
> >
> >> From: =D8ystein Gr=F8vlen<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 ?=A0 Or am I
> missing
> >> something ?
> >>
> >> The difference is in the estimated number of rows
> in table
> >> t2.=A0 This
> >> number is provided by the storage engine.=A0
> Maybe you
> >> are using a
> >> different storage engine?
> >>
> >> Hope this helps,
> >>
> >> --
> >> =D8ystein Gr=F8vlen, 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=3D/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 @@
> >>>=A0 =A0=A0=A01
> >> SIMPLE=A0 =A0 t2
> >> ref=A0 =A0 fld3
> >> fld3=A0 =A0 30
> >> const=A0 =A0 1=A0 =A0 Using where;
> >> Using index
> >>>=A0 =A0=A0=A0explain select fld3
> from t2 ignore
> >> index (fld3) where fld3 =3D 'honeysuckle';
> >>>=A0 =A0=A0=A0id
> >> select_type=A0 =A0 table
> >> type=A0 =A0 possible_keys
> >> key=A0 =A0 key_len
> >> ref=A0 =A0 rows=A0 =A0 Extra
> >>> -1=A0 =A0 SIMPLE
> >> t2=A0 =A0 ALL
> >> NULL=A0 =A0 NULL
> >> NULL=A0 =A0 NULL
> >> 1199=A0 =A0 Using where
> >>> +1=A0 =A0 SIMPLE
> >> t2=A0 =A0 ALL
> >> NULL=A0 =A0 NULL
> >> NULL=A0 =A0 NULL
> >> 1209=A0 =A0 Using where
> >>>
> >>>
> >>
> >>
> >> --
> >> MySQL Internals Mailing List
> >> For list archives: http://lists.mysql.com/internals
> >> To unsubscribe:=A0 =A0 http://lists.mysql.com/internals?unsub=3Dhiromi=
chiwatari@stripped
> >>
> >>
>=20
>=20
> --=20
> =D8ystein Gr=F8vlen, Principal Software Engineer
> MySQL Group, Oracle
> Trondheim, Norway
>