It is. My main problem is how to mitigate the garbage collection issue which interferes with enduser query times.
On Feb 7, 11:11 pm, Noel Grandin <[email protected]> wrote: > Make your delete key your primary key. That will ensure that deleting > touches as few pages as possible. > > > > > > > > > > On Tuesday, 7 February 2012, jim <[email protected]> wrote: > > Hi Thomas, > > > I'm using H2.1.2.147 to maintain a database of 10 days worth of sensor > > data. The data is stored as an array of values keyed by datetime, > > sweep #, sensor, and frequency with measured values stored as blobs. > > The database size is roughly 100GB. All queries, inserts and deletes > > are run in autocommit mode. > > > Data is continually coming in from each sensor every 30 minutes while > > client applications are querying for the information sporadically. In > > order to maintain the database at 10 days, a delete thread is running > > and deleting blocks of data older than 10 days. Sweep # (unixsecs/1800 > > ie halfhour binning) is used as the key for deleting stale data. An > > index has been made for Sweep # to optimize the delete location of > > stale rows. > > > What I'm seeing is that all the queries run quickly with the exception > > of deletes. They seems to take the most time with an average of > > 60-70rows/sec. I've tried reducing the delete limit to 1000 rows which > > works initially (timing suggests 2000 rows/sec) until we reach ~15000 > > rows deleted and then database cleanup kicks in. Adding up the delete > > times for the 15 deletes and it averages out to 60-70 rows/sec. > > > -- Is there a way to do smaller block deletes and to force the H2 > > Server to do the database cleanup? > > > -- Could ANALYZE or some option on DELETE force a cleanup for smaller > > block deletes? > > > This would make query scheduling more manageable. As it stands now, > > the minimum block delete of 15000 rows has a predictable time to > > execute but users will see a 3-5min query delay when the delete is > > running so that I need to somehow schedule it during inactive times. > > > We also see a lot of lobs created in the lobs directory that never > > seem to go away. > > > -- How can these be eliminated? > > > -- Do I need to move to a newer version of H2 for lobs in database > > storage? > > > Lastly, our client apps and the H2 server are stopped abruptly when > > they aren't responding and upon restart for a 5-10 day database H2 > > Server takes an inordinate amount of time to startup which I suspect > > is rollback recovery of broken transactions. > > > -- Is this due to the lobs cleanup at closing you mentioned in some > > earlier posts on this forum? > > > -- Is there a way to monitor H2 Server activity? like what its doing > > at startup that is taking so long? > > > -- Is it hung up in rollback/recovery of broken transactions from the > > abrupt shutdown? or is it simply spinning its wheels because the > > database is broken beyond repair? > > > We do use the -tcpShutdown but it doesn't seem to shutdown the server > > as quickly as needed. Our apps only use tcp connections to the > > database. > > > -- Is there an effective way of stopping H2 server from the command > > line? > > > Beyond these problems, we've been pretty impressed by its performance > > and reliability during our testing. > > > Thanks for your time, > > > Jim McArdle > > > -- > > You received this message because you are subscribed to the Google Groups > > "H2 Database" group.> To post to this group, send email to > [email protected]. > > To unsubscribe from this group, send email to > > [email protected].> For more options, visit this group > at > > http://groups.google.com/group/h2-database?hl=en. > > > > > > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
