In Servoy 5.2.x we can create datasources with dataset.createDataSource(name, columntypes). I’ve used that extensively in a solution and notice the memory usage increasing quite a bit. So I’m curious as to what is going on “under the hood” when we use that command. Is this creating temporary tables in the DB? Is there a way to remove the datasource from memory?
Scott,
The dataset.createDataSource call is creating an in-memory (hsql) table on the server.
You can reuse this table for a second call of createDataSource with the same name, but this only works when the column set is exactly the same.
The old data is deleted in that case and the table is filled with the new data
When the client closes the solution, these in-memory tables are dropped.
You cannot drop them yourself, but you can delete all data from them using dataset.removeRow(-1)
Note that Servoy 6 has an optimized create datasource call: databaseManager.createDataSourceByQuery() which runs a query on the server and puts the data directly in the in-memory table.
Rob
When the client closes the solution, these in-memory tables are dropped.
Is it possible that this doesn’t always happen correctly? Maybe because of how the connections are pooled? I have a situation where the memory on a Servoy Application server gradually increases to anywhere between 6-10 GB. Note that createDataSource in conjunction with the SolutionModel is used extensively to create dynamic datasources and display their results. However, when I go to the server during an off-peak period, and manually close any remaining open clients, the Servoy memory stays up very high. This morning there were no clients and the memory was still at about 5 GB. I did notice that when I went to the DB Servers page that most of the DB Servers had their idle connections showing as 2/10.
Is it possible that the in-memory tables aren’t being dropped and are hanging around with the idle connections?
What else could keep the memory up that high with no connected clients (and no batch processors opened)?
Using Servoy 5.2.10 -build 1021, and Postgres 9 (64 Bit), Java 1.6.0_27
Scott,
I just tried a little sample with clients connecting/deconnecting that use large in-memory tables.
The memory was cleaned up as expected.
Note that the garbage collector does not always immediately free memory.
Maybe you can try some memory profiling. If you run appserver with java 6 you can try jvisualvm for example.
Rob
Hi Scott, not sure if this is related but if you are using Linux for your server consider upgrading Java to 1.6.0_29, I experienced unexpectedly high memory usage with earlier versions and I was not the only one, see this thread: http://forum.servoy.com/viewtopic.php?f=5&t=17025&p=93265#p93265.
you can always dump the memory with tools like jvisualvm (part of a JDK installation) when you expect the memory usage to be very low
then we can look at it and see what is still inside.
I’ve made some changes to the solution, but its still using a lot of memory. The solution functions like an OLAP / data mining app. Where there are millions of rows, and users generate reports which load subsets of the data into the new datasource through the dataset SQL query. Then I make a new form, based on the dataset, to display the results. I’ve modified the solution so that when switching reports, I delete the data in the memory table with foundset.deleteAllRecords() on the form the data was loaded into, and then remove the form from history. The App server still uses about 8 GB of RAM with 25 users (web client). I do see it fluctuating up and down, so some memory is released.
I know that HSQLDB has 2 modes that the tables can be created in. Either 100% in memory, or Cached table, which combines memory and disk cache. Is Servoy using the 100% in memory option? If so, is there an option to get the datasource created using the Cached table instead? Its very possible for these tables to hold millions of rows depending on the reports the user is running.