Hi, It seems like there is a long running transaction, because "-- stream data 29%, 440237 page(s)" means there is 860 MB of transaction log. Each page is 2048 bytes by default (unless you changed the page size).
Could you verify you don't keep a connection (with autocommit disabled, and with uncommitted changes) open for a long time? When this is fixed, let's see how this changed the numbers. Maybe there is still a problem, but maybe not. Maybe this unclosed / uncommitted connection has a temporary table that is removed when the connection is closed, committed, or rolled back. Regards, Thomas On Wed, Aug 14, 2013 at 4:01 PM, davide.cavestro <[email protected]<javascript:_e({}, 'cvml', '[email protected]');> > wrote: > Now I've run 5 times the drop/data import process copying data from a > smaller Postgres local DB (the first import produces a h2 db of less than > 800mb). > > All the import were executed within the same JVM instance. These are the > size measurements taken after every import (with the Tomcat JVM still > running): > > # Size(MB) > 1 755 > 2 1267 > 3 1779 > 4 2547 > 5 3059 > > So in this scenario the db size trend seems almost linear: > > > <https://docs.google.com/spreadsheet/ccc?key=0Am3ZF0bmUOy8dDlCSndfSU9kYW9fYTlhM3piWmhfSkE&usp=sharing> > > Then I shut down Tomcat, and the db size slightly decreased to 2888MB. > Then I ran the recovery tool on the db. These are the relevant statistics: > > ---- Transaction log ---- > ---- Statistics ---- > -- page count: 1478666, free: 1274362 > -- page data bytes: head 66033313, empty 510471785, rows 773802742 (63% full) > -- free 0%, 2 page(s) > -- data leaf 44%, 659330 page(s) > -- data node 0%, 4466 page(s) > -- data overflow 0%, 2652 page(s) > -- btree leaf 24%, 366293 page(s) > -- btree node 0%, 4723 page(s) > -- free list 0%, 91 page(s) > -- stream trunk 0%, 869 page(s) > -- stream data 29%, 440237 page(s) > > Then I exported the DB to a sql script and used RunScript to reimport it > into a new one, which size in turn decreased to 471MB. Then I relaunched > recovery tool that produced the following stats: > > ---- Transaction log ---- > ---- Statistics ---- > -- page count: 241351, free: 80334 > -- page data bytes: head 12427119, empty 95612024, rows 147534873 (63% full) > -- free 0%, 1 page(s) > -- data leaf 51%, 124792 page(s) > -- data node 0%, 853 page(s) > -- data overflow 0%, 311 page(s) > -- btree leaf 14%, 34583 page(s) > -- btree node 0%, 460 page(s) > -- free list 0%, 15 page(s) > -- stream trunk 0%, 159 page(s) > -- stream data 33%, 80174 page(s) > > It seems that the rows number of the huge db is nearly 5 times the shrunk > one. OTOH on a raw check (comparing the number of rows for the bigger > tables) it seems that the shrunk db contains the same data. > > Would it be compatible with a scenario of pending transactions/sessions? > > Thomas Mueller-6 wrote > Hi, How large is the database exactly, and how does it grow exactly? Are > you completely sure it doesn't stop growing? If yes, could you kill the > process while the database is about 10 times as big as it should be, and > then run the recovery tool, and post the statistics part of the the output? > And then, could you shrink the file (for example by export to SQL script / > import from SQL script), run recovery again, and post the statistics of > this new file? Regards, Thomas On Tue, Aug 13, 2013 at 6:27 PM, > davide.cavestro <[hidden > email]<http://user/SendEmail.jtp?type=node&node=4027140&i=0>>wrote: > > I didn't measure it, but it was certainly lower. I'll give you more data > > asap. > > > Noel Grandin wrote > > On Tue, Aug 13, 2013 at 5:56 PM, > davide.cavestro > > < > > > davide.cavestro@ > > > > wrote: > >> I've > tried replacing setCharacterStream() with setString() but the db > >> size > > >> still continues to increase. Is there any way to get an idea of actual > > >> fragmentation of internal data-structures? > >> > > > > Sorry, no, no > idea. > > But I thought we were chasing a bug where shutdown was taking too > > > long, in which case making this change should make a difference. > > > Did you measure your new shutdown time with this change? > > > > -- > > 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+unsubscribe@ > > > > . > > To post to this group, send email to > > > h2-database@ > > > . > > > Visit this group at http://groups.google.com/group/h2-database. > > For > more options, visit https://groups.google.com/groups/opt_out. > > > > > > > -- > View this message in context: > > http://h2-database.66688.n3.nabble.com/h2-Continuous-Increase-in-H2-db-size-after-dropping-and-loading-same-data-repeatedly-tp4026836p4027132.html> > Sent from the H2 Database mailing list archive at Nabble.com. > > -- > > 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 [hidden > email]<http://user/SendEmail.jtp?type=node&node=4027140&i=1>. > > To post to this group, send email to [hidden > > email]<http://user/SendEmail.jtp?type=node&node=4027140&i=2>. > > Visit this group at http://groups.google.com/group/h2-database. > For > more options, visit https://groups.google.com/groups/opt_out. > > > -- > 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 [hidden > email]<http://user/SendEmail.jtp?type=node&node=4027140&i=3>. > To post to this group, send email to [hidden > email]<http://user/SendEmail.jtp?type=node&node=4027140&i=4>. > Visit this group at http://groups.google.com/group/h2-database. For more > options, visit https://groups.google.com/groups/opt_out. > > > ------------------------------ > View this message in context: Re: [h2] Continuous Increase in H2 db size > after dropping and loading same data > repeatedly<http://h2-database.66688.n3.nabble.com/h2-Continuous-Increase-in-H2-db-size-after-dropping-and-loading-same-data-repeatedly-tp4026836p4027140.html> > Sent from the H2 Database mailing list > archive<http://h2-database.66688.n3.nabble.com/>at Nabble.com. > > -- > 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 [email protected] <javascript:_e({}, > 'cvml', 'h2-database%[email protected]');>. > To post to this group, send email to > [email protected]<javascript:_e({}, 'cvml', > '[email protected]');> > . > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > > > > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
