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.
