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.
