Bug 3835 - windows does not release ram after operations
Summary: windows does not release ram after operations
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.21.5 (Jul2015)
Hardware: Other Windows
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-10-23 18:40 CEST by Anthony Damico
Modified: 2016-01-15 11:37 CET (History)
4 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Anthony Damico 2015-10-23 18:40:36 CEST
User-Agent:       Mozilla/5.0 (Windows NT 6.3; WOW64; rv:41.0) Gecko/20100101 Firefox/41.0
Build Identifier: 

i have screencasted a full walk-through available on youtube

https://youtu.be/X8fOSFplVh0

Reproducible: Always

Steps to Reproduce:
tables can be downloaded from this website

https://www.cms.gov/Research-Statistics-Data-and-Systems/Statistics-Trends-and-Reports/BSAPUFS/BSA_PDE_PUF.html

tables can be created with this command

CREATE TABLE testA (pde_event_id STRING, bene_sex_ident_cd INTEGER, bene_age_cat_cd INTEGER, pde_drug_cd INTEGER, pde_drug_str_cd INTEGER, pde_drug_str_units_cd INTEGER, pde_drug_dose_cd INTEGER, pde_drug_class_cd INTEGER, pde_drug_qty_dis INTEGER, pde_drug_day_sply_cd INTEGER, pde_drug_cost INTEGER, pde_drug_pat_pay_cd INTEGER, pde_drug_type_cd INTEGER);

tables can be imported with this command

COPY OFFSET 2 INTO testA FROM 'C:\Users\anthonyd.KFF\Desktop\2008_BSA_PartD_Events_PUF_1\2008_BSA_PartD_Events_PUF_1.csv' USING DELIMITERS ',','\n','"' NULL as '';

Actual Results:  
mserver holds ram that it should not

Expected Results:  
mserver should hold the same amount of ram after the operation completes as it did before the operation started
Comment 1 Ying Zhang cwiconfidential 2015-10-23 23:24:51 CEST
Hai Anthony,

Thanks for even creating a video for this!

Which Windows version are you using?

This looks similar to another bug: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3802.  Niels has already checked in some fix, but unfortunately, that doesn't seem to completely solve the problem.

I wonder if you can try updating your MonetDB installation to include Niels' fix to see if that has any effect for your use cases.

Regards,

Jennie
Comment 2 Anthony Damico 2015-10-24 12:40:55 CEST
thanks Jennie, i am using windows 8.1 in the video but the exact same thing happens on windows 7.

how do i install an mserver with niels' fix?

i looked here  http://dev.monetdb.org/hg/MonetDB/file/tip/NT/installer64

but i don't 100% understand what is going on or how to try updating my mserver version to include this fix?

sorry if i'm being silly

thank you!
Comment 3 Hannes Muehleisen cwiconfidential 2015-10-24 12:42:58 CEST
See here: http://monetdb.cwi.nl/testweb/web/57500:91007a50e91b/ for windows installers from yesterday.
Comment 4 Anthony Damico 2015-10-24 12:44:15 CEST
thanks!

which of these two do i want?


 [ ]	MonetDB5-SQL-Installer-x86_64-91007a50e91b.msi	2015-10-24 05:29 	5.5M	 
[ ]	MonetDB5-SQL-Installer-x86_64-OID32-91007a50e91b.msi	2015-10-24 04:02 	5.5M
Comment 5 Anthony Damico 2015-10-24 13:10:04 CEST
hi, i have just tried with

[ ]	MonetDB5-SQL-Installer-x86_64-91007a50e91b.msi	2015-10-24 05:29 	5.5M	 

the exact same thing happens.

the bugfix for #3802 does not solve the problem shown in the video screencast
Comment 6 Niels Nes cwiconfidential 2015-10-25 09:35:21 CET
anthony monetdb keeps the loaded tables in memory, ie your copy into's will grow
the tables and therefor requires more ram to store it. Intermediate tables / results should be freed again, ie should not lead to more used memory.
Comment 7 Anthony Damico 2015-10-25 09:56:16 CET
hi niels, just confirming that you see that this is not the same table?  these are ten distinct tables that are being COPY INTO'd in separate operations.  this is not one single table being appended to.  i've reopened the issue, but apologies if i'm overlooking something.  thanks for your time with this!
Comment 8 MonetDB Mercurial Repository cwiconfidential 2015-12-07 11:47:30 CET
Changeset 4faed73ce142, made by Sjoerd Mullender <sjoerd@acm.org> in the MonetDB repo, refers to this bug.

For complete details, see http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=4faed73ce142

Changeset description:

	Unload BATs as soon as the physical reference is zero and the BAT clean.
	The idea is, the O/S will keep any used pages in memory until the
	memory is needed again, and if we need the BAT again before that, we
	get to reuse those memory pages without the O/S having to reread from
	disk.
	This depends on the fact that indexes (imprints and hashes) are merely
	unloaded, not destroyed, when the BAT gets unloaded, and reloaded when
	operations need those indexes again.
	This should fix bug 3835.
Comment 9 Anthony Damico 2015-12-15 14:53:17 CET
this has made monetdb on windows much better, thank you for working on this!