From: Øystein Grøvlen
Date: June 16 2011 2:32pm
Subject: Re: Regression test returning different result each time it's run
List-Archive: http://lists.mysql.com/internals/38341
Message-Id: <4DFA13EC.7080401@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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=hiromichiwatari@stripped
>>
>>


-- 
Øystein Grøvlen, Principal Software Engineer
MySQL Group, Oracle
Trondheim, Norway