Test case: 4.1.3 much slower than 4.0.20d

Hello,

on ftp://ftp.mysql.com/pub/mysql/upload/ please find
file perf_test_case.rar (performance test case).

Within RAR archive there are two files:

- perf_test_data.sql.gz -- test case's data (~105M of data)
- perf_test_query.sql -- test case's query

Test have been done on Windows with 4.0.20d and 4.1.3-beta mysql
version servers.

Query results:

4.0.20d - ~0.14s
4.1.3 - ~10.61s ( ~ 43 times slower ! )

Hope it could be useful if such results is caused by a bug.

Please excuse me, if you already know about that kind of problems.

Regards,
--
Saulius




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Test case: 4.1.3 much slower than 4.0.20d

Hi!

On Jul 12, SaGu wrote:
> Hello,
>
> on ftp://ftp.mysql.com/pub/mysql/upload/ please find
> file perf_test_case.rar (performance test case).
>
> Within RAR archive there are two files:
>
> - perf_test_data.sql.gz -- test case's data (~105M of data)
> - perf_test_query.sql -- test case's query
>
> Test have been done on Windows with 4.0.20d and 4.1.3-beta mysql
> version servers.
>
> Query results:
>
> 4.0.20d - ~0.14s
> 4.1.3 - ~10.61s ( ~ 43 times slower ! )
>
> Hope it could be useful if such results is caused by a bug.

No, it's not a bug (though it took me quite a while to figure it out :)

It's a new feature that exposed a problem in your application.

Check EXPLAIN output - in 4.0 e* tables are accessed by "ref" join
method (like in indexed_column=some_value)
while in 4.1 it's "ref_or_null" (like in indexed_column=some_value OR
indexed_column IS NULL). MySQL-4.0 was not able to use indexes for the
latter expression, "ref_or_null" was implemented in 4.1.

The second plan in fact looks better - number of rows accessed by
"ref_or_null" is less that the number of rows in 4.0 accessed with "ref".
But in fact the plan in 4.1 is ~40 times slower - so something must be
wrong with the cost estimation.

And this is your problem - tables are not analyzed, so MySQL cannot get
any meaningful estimates about number of rows. After doing ANALYZE TABLE
for all your tables, the query is very fast in 4.1 too.

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Test case: 4.1.3 much slower than 4.0.20d

Hello,

thanks a lot, you are absolutely right after analyze
speed is much much better. Now I can start to use/test 4.1 for our
development apps. :)

--
Saulius

Sergei Golubchik wrote:

> Hi!
>
> On Jul 12, SaGu wrote:
>
>>Hello,
>>
>> on ftp://ftp.mysql.com/pub/mysql/upload/ please find
>> file perf_test_case.rar (performance test case).
>>
>>Within RAR archive there are two files:
>>
>> - perf_test_data.sql.gz -- test case's data (~105M of data)
>> - perf_test_query.sql -- test case's query
>>
>>Test have been done on Windows with 4.0.20d and 4.1.3-beta mysql
>>version servers.
>>
>>Query results:
>>
>>4.0.20d - ~0.14s
>>4.1.3 - ~10.61s ( ~ 43 times slower ! )
>>
>>Hope it could be useful if such results is caused by a bug.
>
>
> No, it's not a bug (though it took me quite a while to figure it out :)
>
> It's a new feature that exposed a problem in your application.
>
> Check EXPLAIN output - in 4.0 e* tables are accessed by "ref" join
> method (like in indexed_column=some_value)
> while in 4.1 it's "ref_or_null" (like in indexed_column=some_value OR
> indexed_column IS NULL). MySQL-4.0 was not able to use indexes for the
> latter expression, "ref_or_null" was implemented in 4.1.
>
> The second plan in fact looks better - number of rows accessed by
> "ref_or_null" is less that the number of rows in 4.0 accessed with "ref".
> But in fact the plan in 4.1 is ~40 times slower - so something must be
> wrong with the cost estimation.
>
> And this is your problem - tables are not analyzed, so MySQL cannot get
> any meaningful estimates about number of rows. After doing ANALYZE TABLE
> for all your tables, the query is very fast in 4.1 too.
>
> Regards,
> Sergei
>

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org