MySQL Bugs: #22169: Too much memory consumption with count(distinct)

Bug #22169	Too much memory consumption with count(distinct)
Submitted:	9 Sep 2006 12:13	Modified:	20 May 2007 7:36
Reporter:	Georg Richter
Status:	Closed
Category:	Server: Falcon	Severity:	S1 (Critical)
Version:	5.2	OS:	Any (all)
Assigned to:	Christopher Powers	Target Version:

Description:
Using statements in mysql-test/t/count_distinct3.test, I get a crash.

How to repeat:
 The following is an exact copy of mysql-test/t/count_distinct3.test,
except for the addition of a line "SET STORAGE_ENGINE=FALCON" at the
start. Just cut and paste it into the mysql client. Then cut and
paste it again. Then cut and paste it again. Repeat till it crashes.

The mysql client doesn't accept statements like 'let $1 = 1000;"
but that doesn't matter. The important things are:
(1) the result of count(distinct) is wrong
(2) it crashes.

SET STORAGE_ENGINE = FALCON;

#
# this is a test for error 1032 in count(distinct) + group by, introduced in
# mysql-4.1
#

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);

--disable_query_log
SET @rnd_max= 2147483647;
let $1 = 1000;
while ($1)
{
  SET @rnd= RAND();
  SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
  SET @id_rev= @rnd_max - @id;
  SET @grp= CAST(127.0 * @rnd AS UNSIGNED);
  INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev);
  dec $1;
}
set @@read_buffer_size=2*1024*1024;
CREATE TABLE t2 SELECT * FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
DROP TABLE t2;
--enable_query_log

SELECT COUNT(*) FROM t1;

# As t1 contains random numbers, results are different from test to test.
# That's okay, because we test only that select doesn't yield an
# error. Note, that --disable_result_log doesn't suppress error output.

--disable_result_log
SELECT COUNT(DISTINCT id) FROM t1 GROUP BY grp;
--enable_result_log
DROP TABLE t1;

set @@read_buffer_size=default;

It doesn't crash for me on either Win32 or AMD64.  I'm going to mark it closed.  If the
reappears, please re-open this bug.

This problem was resolved by the changes associated with Worklog #3722, Falcon Record
Scavenging. 

When the number of pending records in a transaction reaches a threshold, the records are
removed from the record cache and written to the Serial Log until needed, thus freeing
space for additional records.

Hakan verified that the test case succeeded on a 1GB Linux system, apparently with little
or no memory swapping.

I found that repeated invocations of the test case within the same instance of the engine
succeeded on a 2GB Windows system, and that the amount of memory used by the mysqld
process remained stable.

Test case falcon_bug_22169.test runs fine now on a Linux 32-bit 1GB RAM system.

Memory usage is high but stable and I see no swap activity during the test.

Best regards,

Hakan

A note has been added to the 6.0.1 changelog.