Hello,

I am using ICE 3.3.0.

Getting this warnings when running a query:
2010-03-05 16:06:27 Warning: 10054 uncompressed memory block(s) leaked out.
2010-03-05 16:06:27 Warning: 10054 compressed memory block(s) leaked out.
2010-03-05 16:10:32 Brighthouse engine shutdown.

Turned on
ControlMessages = 2

The query was hanging at the point of sorting data:
..
..
2010-03-05 16:04:18 [1] Packrows after exact evaluation (WHERE):
2010-03-05 16:04:18 [1] (t0): 1 all packrows, 1 to open (including 1 full)
2010-03-05 16:04:18 [1] (t1): 131 all packrows, 131 to open (including 131 full)
2010-03-05 16:04:18 [1] Join execution plan:
2010-03-05 16:04:18 [1] Cnd(0):  VC:0(t0a*) = VC:5(t1a*)    (35.96)
2010-03-05 16:04:18 [1] Traversed all 12 rows.
2010-03-05 16:04:21 [1] Produced 685018 tuples.
2010-03-05 16:04:21 [1] Tuples after inner join 0-1 [hash]: 685018
2010-03-05 16:04:21 [1] Sorting 685018 rows…
2010-03-05 16:04:22 [1] Sorted with 64+64-bit keys.
2010-03-05 16:04:57 [1] Stopped by user.

Some how the warning message won’t come out until I shutdown the database.

Tried changing the system memory, heap size,.. etc but did not make a difference.

Any help would be much appreicated.

Thanks and regards,
Addie

Hi,

Looks like the query is not hanging, but generating the output. There is 685018 of lines to be displayed, and they are generated before the client starts to display them, so it takes a while. Displaying so many lines also will take minutes. There are three ways to make it faster: use the possibly newest release (3.3.1 or earlier, when it will be available), export the result to file, or use a switch to enable pipelined output in client (in the case of Windows MySQL client it is “-q”, if I remember well).

The warning may be caused by stopping the query, it is not too harmful (in the worst case net queries will have a little bit less memory to use, until server restarts).

Regards,

Hi !

The message is generated by ICE at shutdown - it does an exit check. The memory blocks in question are data being sorted. When you have killed the query, sorting did not complete and left its data in memory.

I will try to check if it a false alarm only, or sorting has not done a proper cleanup.

Apparently you have been sorting using a long sort key…

Hi,

You are right. It did not hang but was very slow. I don’t understand why a bit change in where clause slowed the query from 50 second to 11.5 mins.

Trace from the one ran in 50 seconds:
..
..
2010-03-07 10:17:38 [8] Traversed all 56374 rows.
2010-03-07 10:17:40 [8] Produced 8231566 tuples.
2010-03-07 10:17:40 [8] Tuples after inner join 0-1 [hash]: 8231566
2010-03-07 10:17:47 [8] Initial execution plan (non-join):
2010-03-07 10:17:47 [8] Packs/packrows after KN evaluation:
2010-03-07 10:17:47 [8] (t0) Pckrows: 1, susp. 1 (0 empty 0 full). Packs opened in 0 cond.: 0
2010-03-07 10:17:47 [8] (t1) Pckrows: 126, susp. 126 (0 empty 0 full). Packs opened in 0 cond.: 0
2010-03-07 10:17:47 [8] Packrows after exact evaluation (WHERE):
2010-03-07 10:17:47 [8] (t0): 1 all packrows, 1 to open (including 1 full)
2010-03-07 10:17:47 [8] (t1): 126 all packrows, 126 to open (including 126 full)
2010-03-07 10:17:47 [8] Join execution plan:
2010-03-07 10:17:47 [8] Cnd(0):  VC:0(t0a*) = VC:5(t1a*)    (35.92)
2010-03-07 10:17:47 [8] Traversed all 12 rows.
2010-03-07 10:17:49 [8] Produced 656949 tuples.
2010-03-07 10:17:49 [8] Tuples after inner join 0-1 [hash]: 656949
2010-03-07 10:17:50 [8] Sorting 656949 rows…
2010-03-07 10:17:50 [8] Sorted with 64+64-bit keys.
2010-03-07 10:17:51 [8] Displaying result: 656949 rows.
2010-03-07 10:17:59 [8] Total data packs actually loaded (approx.): 7601
2010-03-07 10:17:59 [8]——————————————————————————————————————

Trace from the one ran in 11.5 mins:
..
..
2010-03-07 10:19:26 [8] Traversed all 58722 rows.
2010-03-07 10:19:27 [8] Produced 8559643 tuples.
2010-03-07 10:19:27 [8] Tuples after inner join 0-1 [hash]: 8559643
2010-03-07 10:19:29 [8] Initial execution plan (non-join):
2010-03-07 10:19:29 [8] Packs/packrows after KN evaluation:
2010-03-07 10:19:29 [8] (t0) Pckrows: 1, susp. 1 (0 empty 0 full). Packs opened in 0 cond.: 0
2010-03-07 10:19:29 [8] (t1) Pckrows: 131, susp. 131 (0 empty 0 full). Packs opened in 0 cond.: 0
2010-03-07 10:19:29 [8] Packrows after exact evaluation (WHERE):
2010-03-07 10:19:29 [8] (t0): 1 all packrows, 1 to open (including 1 full)
2010-03-07 10:19:29 [8] (t1): 131 all packrows, 131 to open (including 131 full)
2010-03-07 10:19:29 [8] Join execution plan:
2010-03-07 10:19:29 [8] Cnd(0):  VC:0(t0a*) = VC:5(t1a*)    (35.96)
2010-03-07 10:19:29 [8] Traversed all 12 rows.
2010-03-07 10:19:32 [8] Produced 685018 tuples.
2010-03-07 10:19:32 [8] Tuples after inner join 0-1 [hash]: 685018
2010-03-07 10:19:33 [8] Sorting 685018 rows…
2010-03-07 10:19:33 [8] Sorted with 64+64-bit keys.
2010-03-07 10:30:29 [8] Displaying result: 685018 rows.
2010-03-07 10:30:37 [8] Total data packs actually loaded (approx.): 312
2010-03-07 10:30:37 [8]——————————————————————————————————————

Why it took 11 mins to sort 685018 rows here while < 1 sec for 656949 in the prior run?

Any way to speed up the sorting step?

Thanks,
Addie

Hi !

It looks, that sorting was equally fast - message “Sorted ..”  says that sorting is finished. But, sorting only tells the required row order. The rows have to be generated according to the ordering and then passed to the client (Displaying result). Displaying takes same amount of time, but generating results lasts much longer in the second case. Probably because either the query result contains more columns (more data to read), or the row order is less optimal for disk access.

Probably (I do not have your query to confirm it) this problem has been solved in IEE, so the solution will be released in ICE in the future.