Sorry for spreading info in so many replies. I'll try to recap here. About setCharacterStream it was introduced several years ago for buggy JDBC drivers compatibility (maybe could be safely removed in most cases).
About the negative numbers, I shared them at http://h2-database.66688.n3.nabble.com/h2-Continuous-Increase-in-H2-db-size-after-dropping-and-loading-same-data-repeatedly-tp4026836p4027139.html I repost it here for your convenience ---- Statistics ---- -- page count: 8763710, free: 4148621 -- page data bytes: head 263443992, empty 1859617598, rows -1552023894 (-225% full) -- free 23%, 2078475 page(s) -- data leaf 27%, 2375979 page(s) -- data node 0%, 17345 page(s) -- data overflow 0%, 56 page(s) -- btree leaf 20%, 1801340 page(s) -- btree node 0%, 22636 page(s) -- free list 0%, 533 page(s) -- stream trunk 0%, 4857 page(s) -- stream data 28%, 2462486 page(s) About the pending transaction, I suspect you ar eright: it's likely the cause of most of the issues. However the code is not mine, but it seems ok to me: it's single threaded, it acquires a single connection at the begin, sets the autocommit to false and then starts adding batches: it commits every N batch inserts and at the end of every table. At the end it restores the old autocommit (thought I don't remember if it actually closes the connection at the end... I'll check it). I'm going to add some log (just to see if there's some corner case that I didn't notice) and eventually debug. It would indeed be very useful if I had some sort of API (even H2 internal) exposing the connection transactional status (pending statements and so on). 2013/8/14 Thomas Mueller-6 [via H2 Database] < [email protected]> > 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 <<a > href="javascript:_e({}, 'cvml', '[email protected]');" > target="_blank">davide.cavestro@...> 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 <a href="javascript:_e({}, 'cvml', &# >> 39;h2-database%[email protected]');" >> target="_blank">h2-database+unsubscribe@.... >> To post to this group, send email to <a href="javascript:_e({}, >> 'cvml', '[email protected]');" >> target="_blank">h2-database@.... >> >> 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=4027143&i=0> > . > To post to this group, send email to [hidden > email]<http://user/SendEmail.jtp?type=node&node=4027143&i=1> > . > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > ------------------------------ > If you reply to this email, your message will be added to the discussion > below: > > http://h2-database.66688.n3.nabble.com/h2-Continuous-Increase-in-H2-db-size-after-dropping-and-loading-same-data-repeatedly-tp4026836p4027143.html > To unsubscribe from [h2] Continuous Increase in H2 db size after dropping > and loading same data repeatedly, click > here<http://h2-database.66688.n3.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=4026836&code=ZGF2aWRlLmNhdmVzdHJvQGdtYWlsLmNvbXw0MDI2ODM2fDI5MDM2MjI3NQ==> > . > NAML<http://h2-database.66688.n3.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> > -- View this message in context: http://h2-database.66688.n3.nabble.com/Re-h2-Continuous-Increase-in-H2-db-size-after-dropping-and-loading-same-data-repeatedly-tp4027144.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 [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.
