DB is increasing in size, even though nothing is in there
Description
Environment
Gliffy Diagrams
Activity
Ivan Senic July 7, 2016 at 10:49 AMEdited
Now with JMX stuff this got even more problematic. If you monitor JMX data then it's even growing faster. Thus, I increased priority to highest.
Ivan Senic July 5, 2016 at 2:44 PM
Nothing is helping here, I tried compacting (both ways) and version update as well.
But at least it seams that I found what is a reason for the growth. It seams that it happens when I execute the query to load the complete agent from the DB:
StringBuilder gl = new StringBuilder(
"select distinct platformIdent from PlatformIdent as platformIdent left join fetch platformIdent.methodIdents methodIdent left join fetch platformIdent.jmxDefinitionDataIdents jmxDefinitionDataIdents left join fetch platformIdent.sensorTypeIdents left join fetch methodIdent.methodIdentToSensorTypes");
if (CollectionUtils.isNotEmpty(includeIdents) && CollectionUtils.isNotEmpty(excludeIdents)) {
gl.append(" where platformIdent.id in :includeIdents and platformIdent.id not in :excludeIdents");
} else if (CollectionUtils.isNotEmpty(includeIdents)) {
gl.append(" where platformIdent.id in :includeIdents");
} else if (CollectionUtils.isNotEmpty(excludeIdents)) {
gl.append(" where platformIdent.id not in :excludeIdents");
I am not sure why (maybe join) but this query is the problem. If I set all the relationships in the PlatformIdent to be eager and load the platform by the EntityManager#load() then everything works and database stays small even with reloading, new data, etc.
The only problem is that creating eager relationships there are requiring the complete overwrite of the Registration service. Thus, I must perform major changes there in the way how are things registered, because until now we always loaded PlatformIdent instance from the DB with each registration. And now this is killing us because of the eager loads. But anyway I assume even Registration service can be faster than it is right now, so changes there should be improvement as well. I will just need more time
Ivan Senic July 4, 2016 at 1:42 PM
I also experience this and I tested it with latest H2 and seams that problem is solved. What used to be ~6GB database file for me is now less than 0.5MB!!!! We should include this update before releasing 1.6.9.
We should execute a "SHUTDOWN COMPACT" Statement if the server is being stopped / restarted to clean up all of the data.
Furthermore, we should execute a regular task (like once per day) to do this:
https://groups.google.com/forum/#!topic/h2-database/CGXOfSx_Vq4
public static void compact(String dir, String dbName, String user, String password) throws Exception { String url = "jdbc:h2:" + dir + "/" + dbName; String file = "data/test.sql"; Script.execute(url, user, password, file); DeleteDbFiles.execute(dir, dbName, true); RunScript.execute(url, user, password, file, null, false); }