@funfun: pls provide your analysis

Please provide the following information:
(1) The server on which the test is performed.
(2) The queries that are used by 'Scenario 1' and 'Scenario 2' described in Excel spreadsheet.

Query

Make sure you enter a limit, i entered Max Limit 90000 in my Query window Max Results 

I tried ADS using head of v14 and v15 code. Below is the memory usage by executing "SELECT * FROM events_waits_summary_by_account_by_event_name E, events_statements_summary_by_host_by_event_name F limit L" where L is 50000 or 100000

The query is executed N times on each test. After each query execution, GC icon is clicked a few times and memory usage (Used/Total/Max) is recorded, then, continue to next query execution. At the end of test, wait for M minutes then GC icon is clicked a few times and memory usage is recorded.

   14.0.19-7 - 50K rows
      initial usage:  59/198/989 MB
      1st execution: 109/263/989 MB
      2nd execution: 109/276/989 MB
      3rd execution: 109/277/989 MB
      4th execution: 106/278/989 MB
      5th execution: 107/278/989 MB
      6th execution: 106/280/989 MB
      7th execution: 106/280/989 MB
      5-minute wait: 104/280/989 MB

   14.0.19-7 - 100K rows
      initial usage:  59/230/989 MB
      1st execution: 143/385/989 MB
      2nd execution: 149/507/989 MB
      3rd execution: 149/507/989 MB
      4th execution: 149/507/989 MB
      5th execution: 150/507/989 MB
      6th execution: 150/507/989 MB
      7th execution: 150/507/989 MB
      5-minute wait: 148/507/989 MB

   15.0.15 - 50K rows
      initial usage:  67/211/989 MB
      1st execution: 220/538/989 MB
      2nd execution: 355/864/989 MB
      3rd execution: 355/864/989 MB
      4th execution: 490/989/989 MB
      5th execution: 490/989/989 MB
      6th execution: 490/989/989 MB
      7th execution: 490/989/989 MB
      5-minute wait: 485/989/989 MB

   15.0.15 - 100K rows
      initial usage:  67/220/989 MB
      1st execution: 348/846/989 MB
      2nd execution: 616/989/989 MB
      3rd execution: failed - 'Low On Memory' dialog shows up.
      5-minute wait: 622/989/989 MB

By comparing "14.0.19-7 - 50K rows" and "15.0.15 - 50K rows" tests:
(1) It seems that more memory is needed to execute query in v15, it is hard to tell why more memory is needed from Memory Profiler.
(2) After 2nd execution in v15 test, there is a 'leak suspect' shows up in Memory Profiler. The object is com.aquafold.datastudio.queryanalyzer.ResultSetTableModel. This is not seen in v14 test. This object always reported by Memory Profiler after the 2nd query execution. The memory occupied by this object cannot be freed right away, there is a weak reference points to this object, see WeakReference screenshot for details.
(3) After 3rd execution in v15 test, the Used Memory reported by ADS might jump significantly, when this happened, an additional instance of com.aquafold.datastudio.queryanalyzer.ResultSetTableModel is reported by Memory Profiler. This only happens once in a while, there is no pattern can be used to consistently produce this problem.

It's hard to tell what had gone wrong based on Memory Profiler's outpout. Perhaps next step is to identify in which build this problem starts to show up: i.e. after the 2nd query execution, there is a significant jump on Used Memory reported by ADS. I looked into ADS Archived Builds, it seems that the compressed tar balls stored there are for Linux only and I cannot run ADS on Windows from decompressed files. Can QA help on this?

My testing indicates this first occurs with 15.0.0-rc-16

it does not occur with 15.0.0-rc-15

See screenshot check-ins. Given the way our build works, somewhere between svn#40024 - 40029, inclusive, could be the culprit. I'd first start w/ #40024.

Tested 15.0.0-rc-15 and 15.0.0-rc-16 on Linux one more time yesterday with @swissarmyknife.

I spent some time looking into this problem tonight. Since there is not much information provided in memory analyzer, I had to do it hard way. I went through changes made between SVN 40023 and SVN 40031, inclusive, and set breakpoints there; if breakpoints are reached after query is executed, then I reverted corresponding change in source code.

It turns out SVN 40030 is the one that caused the problem; more specifically, the change made in AFTableColumn.java is the one that caused the problem.
If I reverted change made at line #2308 in AFTableColumn.java
from
   typeInfo = new AFTableColumnInfo(BigDecimal.class, AFTableColumnInfo.NUMBER, true, java.sql.Types.BIGINT, BigDecimal.class);
back to
   typeInfo = new AFTableColumnInfo(BigInteger.class, AFTableColumnInfo.NUMBER, true, java.sql.Types.BIGINT, BigDecimal.class);
then, Used Memory in v15 is reduced significantly and I can execute 100K-row query described here 10+ times in v15 without any problem.

It is getting late and I did not get a chance to trace ADS code (I will be out of office for 2.5 weeks starting tomorrow) and see why change made by AFTableColumn.java at line #2308 caused the problem.

With above change reverted, Used Memory is reduced in v15 but is still higher than v14, so does Total Memory. In addition, the ResultSetTableModel leak suspect mentioned in item 3 here is still there. Maybe these problems are introduced by changes made before 15.0.0-rc-15.

I did some more tests using 100K-row scenario described here against "head of 15.0.0 branch" as well as "head of 15.0.0 branch + SVN-40030-change reverted".

When a query is executed, an instance of com.aquafold.datastudio.queryanalyzer.ResultSetTableModel is created; the memory consumed by this object is:
(a) Head of 15.0.0 branch: 281 MB.
(b) Head of 15.0.0 branch with SVN-40030-change reverted: 72 MB.

There are 100k rows in this test scenario and each row contains 34 columns, 29 out of 34 columns have a "BIGINT UNSIGNED" data type assigned. In com.aquafold.datastudio.queryanalyzer.ResultSetTableModel, each cell is represented by a com.aquafold.aquacore.scripts.engine.AFResultValueDF object.

In case (a) above, i.e. "head of 15.0.0 branch": for those columns that have a 'BIGINT UNSIGNED' data type, the value object passed to com.aquafold.aquacore.scripts.engine.AFResultValueDF is an instance of BigDecimal, the memory consumed by com.aquafold.aquacore.scripts.engine.AFResultValueDF is 88 bytes.  In case (b) above, i.e. "head of 15.0.0 branch with SVN-40030-change reverted": for those columns that have a 'BIGINT UNSIGNED' data type, the value object passed to com.aquafold.aquacore.scripts.engine.AFResultValueDF is an instance of BigInteger, the memory consumed by com.aquafold.aquacore.scripts.engine.AFResultValueDF is 16 bytes. Hence, in case (a), "(88-16) bytes/cell * 29 cells/row * 100K rows = 208 MB" more memory is required for cells corresponding to those columns that have a 'BIGINT UNSIGNED' data type assigned than case (b). See attached ResultSetTableModelMemory screenshot for details.

Approx. BigDecimal and BigInteger memory usage: http://stackoverflow.com/questions/2501176/java-bigdecimal-memory-usage

I did some more tests using query below which does not have BIGINT columns involved:
   SELECT E.USER, E.HOST, E.EVENT_NAME, F.HOST, F.EVENT_NAME FROM events_waits_summary_by_account_by_event_name E, events_statements_summary_by_host_by_event_name F
This query will generate 411345 rows and can be executed in both v14 and v15 many times without any problem.

After query executed a few times, the amount of Used Memory in v15 is higher than v14. This is because in v15, there is a ResultSetTableModel leak suspect (described in item 3 here, also see newly added v14-v15-Memory-Consumption screenshot) whose memory cannot be freed up by VM immediately. This ResultSetTableModel leak suspect is referenced by other object via a weak reference, the memory consumed by this object likely will be freed up by VM later on (in 10 minutes or so). Once the memory occupied by this object is freed by VM, then the amount of Used Memory in v15 will only be slightly higher than v14 (a few MB bytes).
 

@Sachin,

Based on tests we have done so far, we know that SVN r40030 introduced additional memory requirement in ADS. However, we do not know which builds introduced that weak reference problem. By just looking into the weak reference path presented in memory analyzer and eliminate it manually by modifying source won't help much because root cause is not identified; in addition, other soft/weak references are reported by memory analyzer after that weak reference is manually removed, see PossibleSoftWeakReferences screenshot for details.

It would help if we can identify the first build in which weak reference problem is introduced. Perhaps we can try the followings:

(1) Execute 500K-row query below in 15.0.0-rc-16. This query does not contain BIGINT columns, and the ResultSetTableModel object generated by this query consumes 70MB memory.

    SELECT E.USER, E.HOST, E.EVENT_NAME, F.HOST, F.EVENT_NAME FROM events_waits_summary_by_account_by_event_name E, events_statements_summary_by_host_by_event_name F limit 500000

(2) After query is executed, click GC icon displayed in ADS (lower right corner) a few times (6-8 times) and then write down the value of Used Memory.
(3) Execute query defined in (1) one more time.
(4) After query is executed, click GC icon displayed in ADS (lower right corner) a few times (6-8 times) and then write down the value of Used Memory.
(5) Compare the value of Used Memory from step (2) and step (4):
    * if the difference between these 2 values is around 70MB, then repeat step (1) through step (5) using an older 15.0 build until the difference is insignificant;
    * if the difference between these 2 values is insignificant, then repeat step (1) through step (5) using a newer 15.0 build until the difference is around 70MB.

Since my windows desktop cannot run archived builds, can we have someone who has a Linux environment to try above steps?  Or, if there is a Linux box that I can use, I am glad to do it myself.

I tried weak-reference-scenario described here on a Ubuntu box but was not able to reproduce the problem in v15. However, a new leak is discovered on Ubuntu box; it is also reproducible on Windows. Let's refer this new leak as pivot-grid-scenario which is described below:

(0) Launch ADS using the latest v15 build, make sure 'Show Grid' icon and 'Show Pivot Grid' icon are enabled in toolbar.
(1) Execute 500k-row query described here.
(2) After query is executed, click GC icon displayed in ADS (lower right corner) a few times (6-8 times) and then write down the value of Used Memory.
(3) Execute query defined in (1) one more time.
(4) After query is executed, click GC icon displayed in ADS (lower right corner) a few times (6-8 times) and then write down the value of Used Memory. On Ubuntu, this value is the same as the value recorded in step 2; on Windows, this value is about 70MB larger than the value recorded in step 2.
(5) Click 'Pivot Grid' tab to make it the selected tab.
(6) Click 'Grid' tab to make it the selected tab.
(7) Execute query defined in (1) again.
(8) After query is executed, click GC icon displayed in ADS (lower right corner) a few times (6-8 times) and then write down the value of Used Memory. On Ubuntu, this value is about 90MB larger than the value recorded in step 4; on Windows, this value is about 70MB larger than the value recorded in step 4.

Repeat above test steps using the latest v14 build on Ubuntu, you will notice that there is no difference among the values recorded in step 2, step 4 and step 8. So, the question is why step 8 produces different values on Ubuntu?

I then tried older v15 builds on Ubuntu and found that 15.0.0-dev-17 is the first build that has this problem. I reviewed the changes made between 15.0.0-dev-16 and 15.0.0-dev-17, it seems that SVN r34250 for fixing BI #201 is the one that caused this problem.  

SVN r34250 modified two files: ResultSetTableView.java and ResultSetTableModel.java. If I reverted SVN r34250 changes in v15 ResultSetTableView.java, then v15 will behave the same as the latest v14 build on Windows, i.e. the leaks introduced by both weak-reference-scenario and pivot-grid-scenario are gone (based on the output of memory analyzer, see PivotGridScenario screenshot for details). Whether to revert SVN r34250 changes in ResultSetTableModel.java does not seem to matter.

After spending some time studying the memory dumps captured by Eclipse MAT, here is what I found:

1. The test case described here is not reproducible on OSX. It is also not reproducible on Windows if the ADS's Look and Feel is set to "Metal"

2. The leak "suspect" com.aquafold.datastudio.queryanalyzer.ResultSetTableModel  was held on by com.sun.java.swing.plaf.windows.XPStyle.SkinPainter (in an instance of CachedPainter's ImageCache) as a SoftReference. It was also held by com.aquafold.aquacore.open.chart.jogl.ChartPanel._weakChartPanels as a WeakReference 

3. Invoking System#gc (i.e. clicking on ADS's trash can icon on the lower right) does not guarantee the clearing of memory and of soft and weak references. Therefore, ADS's memory indicator does not accurately represent the memory leak profile of a running JVM. See this reference.

4. Given time (10 min to hours depending on current memory profile, and/or at the discretion of) the JVM's garbage collector will clear these soft and weak references, then ADS's memory indicator will return back to the startup value.  See this reference.

5. This leak "suspect" shows up in v15 because of SVN r34250. In v14, the data list in the previous ResultSetTableModel was cleared out by ResultSetTableView when performing a new query. SVN r34250 disabled this clearing out because the same data list could be shared by VA, or we will have to make a complete copy of the queried data (doubling the memory requirement) prior to launching VA.

In summary, from the JVM point of view, there is no memory leak in v14 and v15 with respect to ResultSetTableModel. The leak "suspect" just manifests itself differently in these two versions.

From a user perspective, the end result is that more RAM is required to do the same operation in v14 vs 15. Re-opening to have further discussions on what else we could possibly do.

Thanks Sachin, I would agree-- I'm basically having to close and restart Data Studio extremely frequently-- I'm executing queries pulling back 800K rows, opening the results in VA, couple of quick charts, closing VA and want to execute another large query-- at which point Data Studio becomes unusable-- and I've already bumped MX to 2GB :(

2. The leak "suspect" com.aquafold.datastudio.queryanalyzer.ResultSetTableModel  was held on by com.sun.java.swing.plaf.windows.XPStyle.SkinPainter (in an instance of CachedPainter's ImageCache) as a SoftReference. It was also held by com.aquafold.aquacore.open.chart.jogl.ChartPanel._weakChartPanels as a WeakReference 

Lets focus on CachedPainter's ImageCache and see what we can do to avoid the soft reference as it also impacts 13366

As a follow up on this comment, we have decided to implement these two changes:

1. During memory cannibalization, make a check to see if the table model's dataset has been referenced by others (e.g. VA). If not, clear the data set to free memory for the next query. This should bring memory profile closer to that of v14.

2. Remove direct and indirect strongly reachable (hard) references to the current ResultSetTableModel before query execution. This ensures that during the next query execution, at most only WeakReference and SoftReference are holding onto the previous ResultSetTableModel (e.g. Windows XPStyle's SkinPainter/CachedPainter) - memory used by the previous ResultSetTableModel will eventually be garbage collected by JVM under low memory situation.