Large group by queries never complete - server at 100% cpu(all cores) until MonetDB stopped

[monetdb-team]

Date: 2013-09-26 18:38:20 +0200
From: Bryan <>
To: SQL devs <>
Version: 11.15.19 (Feb2013-SP6)
CC: @mlkersten, @njnes, @yzchang

Last updated: 2014-02-20 15:02:57 +0100

Comment 19179

Date: 2013-09-26 18:38:20 +0200
From: Bryan <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.76 Safari/537.36
Build Identifier:

The following query never returns a result set, with full cpu utilization until the database is shutdown.

SELECT
year_value,
quarter_value,
month_value,
equipment_id,
assigned_location_id,
equipment_owner_id,
equipment_type_id,
equipment_model_id,
equipment_manufacturer_id,
is_rented,
sum(seconds) as seconds,
sum(case when is_utilized = 1 then seconds else 0 end) as utilized_seconds,
1 as equipment_count,
max(case when is_utilized = 1 then 1 else 0 end) as used_equipment_count,
sum(rental_expense) as rental_expense,
count(*) as fact_count,
now() as create_date
FROM fact_utilization_hourly as f
join dim_date_hour as d on f.date_hour_id = d.date_hour_id
--where date_value between '2010-04-01' and '2010-10-31'
GROUP BY year_value,
quarter_value,
month_value,
equipment_id,
assigned_location_id,
equipment_owner_id,
equipment_type_id,
equipment_model_id,
equipment_manufacturer_id,
is_rented;

This is somewhat data dependent; as this never ending query manifests when there is only a few days of data (~4M rows in the fact ) table or when we have 41 months (~261M rows) but does NOT manifest with our 9 month (~50M row test set).

All the above queries return successfully in July2012SP2 & Oct2012SP3 on both Windows and Linux.

Reproducible: Always

Steps to Reproduce:

1. Submit the query above

Actual Results:

MonetDB shows a "normal" cpu pattern for about 10 minutes (appears to be in sync with disk i/o) then disk i/o stops and cpu's spike to 100% forever.
After 16 hours need to shutdown the database to allow other connections as this query has consumed full cpu.

Expected Results:

Query to return data. Note: the 50M rows set took about 9 minutes to complete the query and insert. Removing the Insert and wrapping with a select count(*) from (...) a also never returns.

Comment 19180

Date: 2013-09-26 18:39:57 +0200
From: Bryan <>

Created attachment 234
Tables structures and MAL explain plan

Attached file: agg_month_1_feb2013sp3.lst (application/octet-stream, 118671 bytes)
Description: Tables structures and MAL explain plan

Comment 19181

Date: 2013-09-26 18:40:31 +0200
From: Bryan <>

Created attachment 235
Oct2012SP3 explain plan (query completes)

Attached file: agg_month_1_oct2012sp3.lst (application/octet-stream, 96483 bytes)
Description: Oct2012SP3 explain plan (query completes)

Comment 19182

Date: 2013-09-26 18:41:49 +0200
From: Bryan <>

We have multiple queries that follow the same pattern, aggregating to Year/Quarter/Month and or other denormilizations of various FKs, all fail as above.

Comment 19183

Date: 2013-09-26 19:16:15 +0200
From: @mlkersten

Thank you for your detailed report, we'll look into it

Martin

Comment 19209

Date: 2013-09-30 00:18:27 +0200
From: @yzchang

Corrected the MonetDB version, since this bug report was submitted before Feb2013-SP4 was released, and attachment 234 includes MAL explain plan of Feb2013-SP3.

Comment 19398

Date: 2013-12-08 17:50:29 +0100
From: @njnes

was this fixed by the SP6 release? If not, the only way to progress is to have data (generated/anon.. is fine).

Comment 19413

Date: 2013-12-11 13:04:28 +0100
From: @njnes

openbi.com reply indicates this is fixed now. Open issue seems to be related to more IO WAIT. This is a new problem, so for now we close this bug.

Comment 19614

Date: 2014-02-20 15:02:57 +0100
From: @sjoerdmullender

Jan2014 has been released.