MySQL Forums :: Performance :: Real weird slow down with select count(distinct x) and high max_heap_table_size


Advanced Search

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


Subject Views Written By Posted
Real weird slow down with select count(distinct x) and high max_heap_table_size 4517 Ben Li 03/31/2010 05:50AM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1412 Ventsislav Alexandriyski 03/31/2010 06:30AM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1263 Ben Li 03/31/2010 08:42PM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1473 Ventsislav Alexandriyski 04/01/2010 04:39AM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1418 Ben Li 04/01/2010 07:28PM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1372 Ventsislav Alexandriyski 04/02/2010 06:25AM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1258 Ben Li 04/04/2010 08:45AM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1604 Rick James 04/02/2010 11:02PM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 2102 Ben Li 04/04/2010 09:00AM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1214 Ben Li 04/08/2010 04:16AM
Re: Real weird slow down with select count(distinct x) and high max_heap_table_size 1268 Rick James 04/08/2010 07:37AM


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.