We store our data using unixsecs as a primary key, I've created a second field as dividing unixsecs by 1800 to make a sweep # (ie time in half-hour increments) and created an index table on sweep # which should contain about 480 values at any given time. The sql I use is: DELETE FROM MySensorTable WHERE SweepNo<99999 and rownum<999;
The issue I'm seeing is that it looks like delete changes are being queued up and then processed after 15000 rows have been deleted. This makes it difficult to handle user queries in a timely manner if its going to take the delete process 3 minutes to complete before my user query can be run. The issue is very similar to garbage collection in java apps where you don't want the app readjusting memory while you're waiting for an answer for 3 minutes. I've had to resort to a scheme where I delay deleting until I see a quiet time and then continue deleting blocks of 15000 at a time. The problem is that the user may see the 3 minute response delay on the first selection and think that things are broken and force a restart of the apps. I realize that deletes copy the row to the transaction log in case of failure or disconnect but I'd like it to do it immediately instead of waiting for 15000 rows to be deleted. The row store an array of values as a BLOB and I was thinking there's maybe an issue with how LOBS are handled in this version that got fixed in a future version but I wanted to get Thomas Mueller's response on this. Thanks, Jim McArdle On Feb 8, 2:48 pm, Noel Grandin <[email protected]> wrote: > Is your primary key in the sense of "you've defined it as your primary key" > or is your primary key in the sense of CREATE TABLE .... PRIMARY KEY ..... > > Because the 2nd thing is what I'm talking about. > > > > > > > > On Wed, Feb 8, 2012 at 17:48, jim <[email protected]> wrote: > > 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 > > athttp://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.
