MySQL Forums
Forum List  »  Performance

Real weird slow down with select count(distinct x) and high max_heap_table_size
Posted by: Ben Li
Date: March 31, 2010 05:50AM

with set max_heap_table_size to high (from 16M default to 64M)´╝îselect distinct count query run slower.

1. populate tables:

create table dctest(a bigint);

delimiter //

CREATE PROCEDURE makedata(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; insert into dctest values(rand() * 30000000);
UNTIL @x > p1 END REPEAT;
END
//

delimiter ;

mysql> call makedata(1000);
Query OK, 1 row affected (0.29 sec)

mysql> call makedata(8000000);
Query OK, 1 row affected (39 min 30.63 sec)

mysql> select count(1) from dctest;
+----------+
| count(1) |
+----------+
| 8001003 |
+----------+

mysql> select count(distinct a) from dctest;
+-------------------+
| count(distinct a) |
+-------------------+
| 5755225 |
+-------------------+
1 row in set (6.66 sec)

mysql> show variables like 'max_heap_table_size';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+

mysql> set max_heap_table_size=67108864;
Query OK, 0 rows affected (0.00 sec)

mysql> select SQL_NO_CACHE count(distinct a) from dctest;
+-------------------+
| count(distinct a) |
+-------------------+
| 5755225 |
+-------------------+
1 row in set (9.52 sec)

mysql> set max_heap_table_size=1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> select SQL_NO_CACHE count(distinct a) from dctest;
+-------------------+
| count(distinct a) |
+-------------------+
| 5755225 |
+-------------------+
1 row in set (4.74 sec)


It's it a bug? or I'm misunderstand anything?

nothing changed with 5.5.2 and 5.1.45, 64bit linux edition (gcc or icc).

X5450 @ 3.00GHz * 2 with 32G memory, running Centos 5.4.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.