List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 29 1999 11:43pm
Subject:Performance problems under RH6.1/Mysql3.22.27
View as plain text  
Hi!

>>>>> "Yermo" == Yermo Lamers <yml@stripped> writes:

Yermo> The quick summary:

Yermo> A GROUP BY query running on a RedHat 4.2 box running Mysql 3.21.30 takes
Yermo> 1.5 seconds to run.

Yermo> The exact same query running on the exact same table over the
Yermo> same dataset on a Redhat 6.1 box running Mysql 3.22.27 takes anywhere from
Yermo> 29 seconds to 1 minute 45 seconds. [dataset moved using mysqldump -c from
Yermo> the 3.21.30 box and imported into the 3.22.27 box using mysql data <
Yermo> dumpfile.]

<cut>

Yermo>
> +------------------+------+------------------+------------------+---------+------+------+-------+
Yermo> | table            | type | possible_keys    | key              | key_len
Yermo> | ref  | rows | Extra |
Yermo>
> +------------------+------+------------------+------------------+---------+------+------+-------+
Yermo> | clientactivity_1 | ref  | client_event_idx | client_event_idx |       4
Yermo> | ???  |   18 |       |
Yermo>
> +------------------+------+------------------+------------------+---------+------+------+-------+
Yermo> 1 row in set (0.00 sec)


Note that the 'rows' in 3.21.30 was wrong.

Yermo> select count(*) as count,
Yermo> WEEK( client_date ) as date_group
Yermo> from clientactivity_1  
Yermo> where client_event = 0
Yermo> group by date_group
Yermo> order by client_date

<cut>

Yermo> 17 rows in set (1.39 sec)

<cut>
 
Yermo> explain select count(*) as count,
Yermo> WEEK( client_date ) as date_group
Yermo> from clientactivity_1
Yermo> where client_event = 0
Yermo> group by date_group
Yermo> order by client_date

Yermo>
> +------------------+------+------------------+------------------+---------+------+------+-------+
Yermo> | table            | type | possible_keys    | key              | key_len
Yermo> | ref  | rows | Extra |
Yermo>
> +------------------+------+------------------+------------------+---------+------+------+-------+
Yermo> | clientactivity_1 | ref  | client_event_idx | client_event_idx |       4
Yermo> | ???  | 7285 |       |
Yermo>
> +------------------+------+------------------+------------------+---------+------+------+-------+
Yermo> 1 row in set (0.18 sec)

The above rows is probably ok.

Yermo> select count(*) as count,
Yermo> WEEK( client_date ) as date_group
Yermo> from clientactivity_1
Yermo> where client_event = 0
Yermo> group by date_group
Yermo> order by client_date

Yermo> +-------+------------+
Yermo> | count | date_group |
Yermo> +-------+------------+
Yermo> |   575 |         29 |
Yermo> |  1247 |         30 |
Yermo> |  1017 |         31 |
Yermo> |   792 |         32 |
Yermo> |   971 |         33 |
Yermo> |   836 |         34 |
Yermo> |   739 |         35 |
Yermo> |   770 |         36 |
Yermo> |   647 |         37 |
Yermo> |   625 |         38 |
Yermo> |   593 |         39 |
Yermo> |   667 |         40 |
Yermo> |   587 |         41 |
Yermo> |   660 |         42 |
Yermo> |   660 |         43 |
Yermo> |   764 |         44 |
Yermo> |   339 |         45 |
Yermo> +-------+------------+
Yermo> 17 rows in set (1 min 19.30 sec)

<cut>

Yermo> Before I upgraded to RH6.1/Mysql 3.22.27 it seemed like the mysql data <
Yermo> import approach ran alot faster. Now it takes over 30 minutes to import
Yermo> the data this way (and it's only 400,000 some odd rows . . . )

Yermo> Any ideas?

Try using SELECT SQL_SMALL_RESULT  ; This will tell MySQL to use
another algorithm to execute the group by.  Try also running the query 
twice to get some data cached into memory.  (MySQL 3.23 is much faster 
on GROUP BY queries)

Note that if something forces your PC to swap, this could easily
explain this problem!

Regards,
Monty

PS: Sorry for the late reply;  I am still trying to catch up with the
    mails that piled up during my vacation.
Thread
Performance problems under RH6.1/Mysql3.22.27Yermo Lamers11 Nov
  • Performance problems under RH6.1/Mysql3.22.27Michael Widenius30 Nov