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 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.
