Re: [h2] 1.4 beta creates much bigger database file
Actually, I was using the Tomcat JDBC pool with the default settings. The connection within the pool were never released - and it seems this prevented H2 to do its cleanup. After I've put the maxAge property on the pool (auto release after 10 minutes), the problem disappeared. The database file grows steadily but comes down to a normal size every 10 minutes. I'd wish the database would not grow as fast, but it's already much better. Damien. On Thu, Feb 12, 2015 at 6:05 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, Disk space is re-used after 45 seconds (the default retention time). Disk space should stabilise at some point, unless if you add more and more data. checkpoint should reduce disk space usage, but you may need to call it a few times. Regards, Thomas On Friday, February 6, 2015, Damien Coraboeuf damien.corabo...@gmail.com wrote: FYI, we were using 1.4.184 together with a JDK 1.8.0u11, on a CentOS 6, and the database kept growing by chunks of ~ 10 MB. We have upgraded to JDK 1.8.0u31, and although the file keeps growing, it is now by tiny chunks of kilobytes. So much better. I just hope there is some kind of runtime cleanup going on, because it would still go over 1.0 GB in 30 days :( Is there a way to launch some SQL commands at runtime to make the database shrink? Damien. On Wednesday, 4 February 2015 23:01:07 UTC+1, Max Lord wrote: I also had problems with an mvstore db growing out of control (about 3GB for 1M rows). I updated to 1.4.184, reimported, and it was much smaller (100MB). So the recent changes have had a very positive effect. Unfortunately, I was using 1.4.178 because that was the current jar bundled with the jdbc-h2 ruby gem. That version doesn't seem like such a good default. On Monday, February 2, 2015 at 2:42:28 PM UTC-5, Damien Coraboeuf wrote: I have replaced by BLOB columns by BINARY(32000) ones (more than enough in our case). After exporting the database in SQL ('script' command), recreating a blank database and reimporting the SQL ('runscript'), I went from 1.7 Gb to 17 Mb. I'll monitor the database in the next days to see if the inflation starts again. Damien. On Monday, 2 February 2015 17:40:49 UTC+1, Damien Coraboeuf wrote: Hi, Speaking of real world example - we are using H2 1.4.x to hold results for a continuous delivery chain. With 1.4.177, our database was 600 Mb, and after a 'shutdown defrag', we went down to... 11 Mb. We switched to 1.4.184 but now, the database has grown up to 1.7 Gb. That's a serious issue for us :( The URL we use is: jdbc:h2:/opt/ontrack/database/data;MODE=MYSQL Damien. On Monday, 5 January 2015 18:15:56 UTC+1, Thomas Mueller wrote: Hi, OK, that's nice! There is still quite a lot of room for improvements, and I don't consider this completely fixed, but will not work on it with very high priority any longer. Regards, Thomas On Sunday, December 21, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, The database file size in 1.4.184 is much, much better than in earlier 1.4.x releases. I've done some trials and these are my findings: 1.3.176: Fully loaded database after shutdown is 317 Mb 1.4.184: Fully loaded database after shutdown is 380 Mb This seems reasonable. On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote: Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas
Re: [h2] 1.4 beta creates much bigger database file
Hi, Disk space is re-used after 45 seconds (the default retention time). Disk space should stabilise at some point, unless if you add more and more data. checkpoint should reduce disk space usage, but you may need to call it a few times. Regards, Thomas On Friday, February 6, 2015, Damien Coraboeuf damien.corabo...@gmail.com wrote: FYI, we were using 1.4.184 together with a JDK 1.8.0u11, on a CentOS 6, and the database kept growing by chunks of ~ 10 MB. We have upgraded to JDK 1.8.0u31, and although the file keeps growing, it is now by tiny chunks of kilobytes. So much better. I just hope there is some kind of runtime cleanup going on, because it would still go over 1.0 GB in 30 days :( Is there a way to launch some SQL commands at runtime to make the database shrink? Damien. On Wednesday, 4 February 2015 23:01:07 UTC+1, Max Lord wrote: I also had problems with an mvstore db growing out of control (about 3GB for 1M rows). I updated to 1.4.184, reimported, and it was much smaller (100MB). So the recent changes have had a very positive effect. Unfortunately, I was using 1.4.178 because that was the current jar bundled with the jdbc-h2 ruby gem. That version doesn't seem like such a good default. On Monday, February 2, 2015 at 2:42:28 PM UTC-5, Damien Coraboeuf wrote: I have replaced by BLOB columns by BINARY(32000) ones (more than enough in our case). After exporting the database in SQL ('script' command), recreating a blank database and reimporting the SQL ('runscript'), I went from 1.7 Gb to 17 Mb. I'll monitor the database in the next days to see if the inflation starts again. Damien. On Monday, 2 February 2015 17:40:49 UTC+1, Damien Coraboeuf wrote: Hi, Speaking of real world example - we are using H2 1.4.x to hold results for a continuous delivery chain. With 1.4.177, our database was 600 Mb, and after a 'shutdown defrag', we went down to... 11 Mb. We switched to 1.4.184 but now, the database has grown up to 1.7 Gb. That's a serious issue for us :( The URL we use is: jdbc:h2:/opt/ontrack/database/data;MODE=MYSQL Damien. On Monday, 5 January 2015 18:15:56 UTC+1, Thomas Mueller wrote: Hi, OK, that's nice! There is still quite a lot of room for improvements, and I don't consider this completely fixed, but will not work on it with very high priority any longer. Regards, Thomas On Sunday, December 21, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, The database file size in 1.4.184 is much, much better than in earlier 1.4.x releases. I've done some trials and these are my findings: 1.3.176: Fully loaded database after shutdown is 317 Mb 1.4.184: Fully loaded database after shutdown is 380 Mb This seems reasonable. On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote: Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even
Re: [h2] 1.4 beta creates much bigger database file
FYI, we were using 1.4.184 together with a JDK 1.8.0u11, on a CentOS 6, and the database kept growing by chunks of ~ 10 MB. We have upgraded to JDK 1.8.0u31, and although the file keeps growing, it is now by tiny chunks of kilobytes. So much better. I just hope there is some kind of runtime cleanup going on, because it would still go over 1.0 GB in 30 days :( Is there a way to launch some SQL commands at runtime to make the database shrink? Damien. On Wednesday, 4 February 2015 23:01:07 UTC+1, Max Lord wrote: I also had problems with an mvstore db growing out of control (about 3GB for 1M rows). I updated to 1.4.184, reimported, and it was much smaller (100MB). So the recent changes have had a very positive effect. Unfortunately, I was using 1.4.178 because that was the current jar bundled with the jdbc-h2 ruby gem. That version doesn't seem like such a good default. On Monday, February 2, 2015 at 2:42:28 PM UTC-5, Damien Coraboeuf wrote: I have replaced by BLOB columns by BINARY(32000) ones (more than enough in our case). After exporting the database in SQL ('script' command), recreating a blank database and reimporting the SQL ('runscript'), I went from 1.7 Gb to 17 Mb. I'll monitor the database in the next days to see if the inflation starts again. Damien. On Monday, 2 February 2015 17:40:49 UTC+1, Damien Coraboeuf wrote: Hi, Speaking of real world example - we are using H2 1.4.x to hold results for a continuous delivery chain. With 1.4.177, our database was 600 Mb, and after a 'shutdown defrag', we went down to... 11 Mb. We switched to 1.4.184 but now, the database has grown up to 1.7 Gb. That's a serious issue for us :( The URL we use is: jdbc:h2:/opt/ontrack/database/data;MODE=MYSQL Damien. On Monday, 5 January 2015 18:15:56 UTC+1, Thomas Mueller wrote: Hi, OK, that's nice! There is still quite a lot of room for improvements, and I don't consider this completely fixed, but will not work on it with very high priority any longer. Regards, Thomas On Sunday, December 21, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, The database file size in 1.4.184 is much, much better than in earlier 1.4.x releases. I've done some trials and these are my findings: 1.3.176: Fully loaded database after shutdown is 317 Mb 1.4.184: Fully loaded database after shutdown is 380 Mb This seems reasonable. On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote: Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of
Re: [h2] 1.4 beta creates much bigger database file
I also had problems with an mvstore db growing out of control (about 3GB for 1M rows). I updated to 1.4.184, reimported, and it was much smaller (100MB). So the recent changes have had a very positive effect. Unfortunately, I was using 1.4.178 because that was the current jar bundled with the jdbc-h2 ruby gem. That version doesn't seem like such a good default. On Monday, February 2, 2015 at 2:42:28 PM UTC-5, Damien Coraboeuf wrote: I have replaced by BLOB columns by BINARY(32000) ones (more than enough in our case). After exporting the database in SQL ('script' command), recreating a blank database and reimporting the SQL ('runscript'), I went from 1.7 Gb to 17 Mb. I'll monitor the database in the next days to see if the inflation starts again. Damien. On Monday, 2 February 2015 17:40:49 UTC+1, Damien Coraboeuf wrote: Hi, Speaking of real world example - we are using H2 1.4.x to hold results for a continuous delivery chain. With 1.4.177, our database was 600 Mb, and after a 'shutdown defrag', we went down to... 11 Mb. We switched to 1.4.184 but now, the database has grown up to 1.7 Gb. That's a serious issue for us :( The URL we use is: jdbc:h2:/opt/ontrack/database/data;MODE=MYSQL Damien. On Monday, 5 January 2015 18:15:56 UTC+1, Thomas Mueller wrote: Hi, OK, that's nice! There is still quite a lot of room for improvements, and I don't consider this completely fixed, but will not work on it with very high priority any longer. Regards, Thomas On Sunday, December 21, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, The database file size in 1.4.184 is much, much better than in earlier 1.4.x releases. I've done some trials and these are my findings: 1.3.176: Fully loaded database after shutdown is 317 Mb 1.4.184: Fully loaded database after shutdown is 380 Mb This seems reasonable. On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote: Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please?
Re: [h2] 1.4 beta creates much bigger database file
I have replaced by BLOB columns by BINARY(32000) ones (more than enough in our case). After exporting the database in SQL ('script' command), recreating a blank database and reimporting the SQL ('runscript'), I went from 1.7 Gb to 17 Mb. I'll monitor the database in the next days to see if the inflation starts again. Damien. On Monday, 2 February 2015 17:40:49 UTC+1, Damien Coraboeuf wrote: Hi, Speaking of real world example - we are using H2 1.4.x to hold results for a continuous delivery chain. With 1.4.177, our database was 600 Mb, and after a 'shutdown defrag', we went down to... 11 Mb. We switched to 1.4.184 but now, the database has grown up to 1.7 Gb. That's a serious issue for us :( The URL we use is: jdbc:h2:/opt/ontrack/database/data;MODE=MYSQL Damien. On Monday, 5 January 2015 18:15:56 UTC+1, Thomas Mueller wrote: Hi, OK, that's nice! There is still quite a lot of room for improvements, and I don't consider this completely fixed, but will not work on it with very high priority any longer. Regards, Thomas On Sunday, December 21, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, The database file size in 1.4.184 is much, much better than in earlier 1.4.x releases. I've done some trials and these are my findings: 1.3.176: Fully loaded database after shutdown is 317 Mb 1.4.184: Fully loaded database after shutdown is 380 Mb This seems reasonable. On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote: Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all
Re: [h2] 1.4 beta creates much bigger database file
Hi, Speaking of real world example - we are using H2 1.4.x to hold results for a continuous delivery chain. With 1.4.177, our database was 600 Mb, and after a 'shutdown defrag', we went down to... 11 Mb. We switched to 1.4.184 but now, the database has grown up to 1.7 Gb. That's a serious issue for us :( The URL we use is: jdbc:h2:/opt/ontrack/database/data;MODE=MYSQL Damien. On Monday, 5 January 2015 18:15:56 UTC+1, Thomas Mueller wrote: Hi, OK, that's nice! There is still quite a lot of room for improvements, and I don't consider this completely fixed, but will not work on it with very high priority any longer. Regards, Thomas On Sunday, December 21, 2014, Steve McLeod steve@gmail.com javascript: wrote: Hi Thomas, The database file size in 1.4.184 is much, much better than in earlier 1.4.x releases. I've done some trials and these are my findings: 1.3.176: Fully loaded database after shutdown is 317 Mb 1.4.184: Fully loaded database after shutdown is 380 Mb This seems reasonable. On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote: Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using:
Re: [h2] 1.4 beta creates much bigger database file
Hi, OK, that's nice! There is still quite a lot of room for improvements, and I don't consider this completely fixed, but will not work on it with very high priority any longer. Regards, Thomas On Sunday, December 21, 2014, Steve McLeod steve.mcl...@gmail.com wrote: Hi Thomas, The database file size in 1.4.184 is much, much better than in earlier 1.4.x releases. I've done some trials and these are my findings: 1.3.176: Fully loaded database after shutdown is 317 Mb 1.4.184: Fully loaded database after shutdown is 380 Mb This seems reasonable. On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote: Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware. pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='co m.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_ LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Muel -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop
Re: [h2] 1.4 beta creates much bigger database file
Hi Thomas, The database file size in 1.4.184 is much, much better than in earlier 1.4.x releases. I've done some trials and these are my findings: 1.3.176: Fully loaded database after shutdown is 317 Mb 1.4.184: Fully loaded database after shutdown is 380 Mb This seems reasonable. On Friday, 19 December 2014 17:15:29 UTC+8, Thomas Mueller wrote: Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.to...@gmail.com javascript: wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve@gmail.com javascript: wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware. pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='co m.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_ LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Muel -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database.
Re: [h2] 1.4 beta creates much bigger database file
Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve.mcl...@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.po kercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='co m.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_ LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Muel -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For
Re: [h2] 1.4 beta creates much bigger database file
Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve.mcl...@gmail.comwrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.po kercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='co m.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_ LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Muel -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send
Re: [h2] 1.4 beta creates much bigger database file
Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware. pokercopilot/database/pokercopilot;DATABASE_EVENT_ LISTENER='com.barbarysoftware.pokercopilot.database. DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536; RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve.mcl...@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.po kercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER=' com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_ LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Muel -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.comjavascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com'); . To post to this group, send email to h2-database@googlegroups.comjavascript:_e(%7B%7D,'cvml','h2-database@googlegroups.com'); . Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.comjavascript: wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware. pokercopilot/database/pokercopilot;DATABASE_EVENT_ LISTENER='com.barbarysoftware.pokercopilot.database. DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536; RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com javascript:. To post to this group, send email to h2-da...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comjavascript: wrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com javascript:. To post to this group, send email to h2-da...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve.mcl...@gmail.comwrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comjavascript: wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com javascript:. To post to this group, send email to h2-da...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve.mcl...@gmail.comwrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware. pokercopilot/database/pokercopilot;DATABASE_EVENT_ LISTENER='com.barbarysoftware.pokercopilot.database. DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536; RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] 1.4 beta creates much bigger database file
Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve.mcl...@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.