Hi Noel, Thanks for the suggestion. I was thinking about it last night. Its an architecture issue for my program. It seems to be shifting the issue to the recreation of the view each time I add a new table to the mix.
Or I can use some modulo math on the date/time and select 1 of N tables to insert the data. Then as I begin inserting into the new table I would perform the truncation of that table first to clean out the old data. I'm not sure in that case if I need to: - rebuild the indexes and/or - recreate the view again to recognize the truncation and new inserts and/or - run the analyze again to improve the selectivity. Anyway, its something I can experiment with in the next release. It looks promising as a means to handle a FIFO structure in a database though. Thanks, Jim McArdle On Feb 10, 2:38 am, Noel Grandin <[email protected]> wrote: > http://www.h2database.com/html/grammar.html#truncate_table > > I would suggest you use a series of tables with one hour per table. > Then build a view over those tables for your select queries to operate on. > Then truncate the oldest tables as necessary. > > On 2012-02-10 02:48, jim wrote: > > > > > > > > > Hi Thomas, > > > You also mentioned truncating a table: > > > -- Does that incur copying the truncated rows to the transaction log? > > > -- How can I move rows to the table without incurring a transaction > > copy on delete? > > > -- Is there some kind of INSERT + DELETE syntax to simulate a block > > move? > > > otherwise doing an insert into a new table means I have to delete the > > rows from the old table and incur the transaction log copy. > > > ** This is a continuation of my previous two posts today. ** > > > Thanks, > > Jim McArdle > > > On Feb 9, 2:16 pm, jim<[email protected]> wrote: > >> correction on text in item #2: > > >> As an example, the first 14 deletes at 1000 rows average 2000 rows/ > >> sec. However the 15th 1000 row delete takes 200 secs to complete. > >> ( 14*(1000) + 1000)/(14*.5 + 200) = 72 rows/sec). > > >> -- Jim McArdle > > >> On Feb 9, 9:11 am, jim<[email protected]> wrote: > > >>> Hi Thomas, > >>> Thanks for the fast and thorough reply. > >>> 1) The sensor data table is basically a FIFO queue with new rows being > >>> added to the end and the oldest rows being deleted from the beginning. > >>> We don't delete anything in the middle at any time. > >>> 2) Deletes don't start until the 10 day range has been achieved. It is > >>> at that time that I see the average 60-70rows/sec delete rate after > >>> 15000 rows has been deleted. This makes me think that deletes are > >>> being queued until there is enough to delete and then the actual > >>> delete processing kicks in halting all queries until it is finished. > >>> I tried the idea of deleting smaller blocks of rows at 100 rows per > >>> delete. What I found is that when you've deleted 15000 in total then > >>> the server goes into a kind of garbage cleanup. As an example, the > >>> first 14 deletes at 1000 rows average 200 rows/sec. However the 15th > >>> 1000 row delete takes 200 secs to complete. ( 14*(1000) + 1000)/(14*.5 > >>> + 200) = 72 rows/sec). > >>> During that time I'm also adding 15000 rows of new data as data is > >>> continually streaming in. However, when I did the measurements above > >>> no data was streaming in. It was just me, H2 and the H2 shell > >>> sparring. > >>> The rownum<999 expression the 999 was meant to be a placeholder for > >>> whatever number I used. Yes the deletes are in a loop with small > >>> sleeps to allow the other queries to get processed. I trying to > >>> performance tune so that the enduser query doesn't take too long and > >>> waiting upto 3-5 mins for a small query of 48 to 96 rows is too long > >>> when I've seen it come back in 10 to 20 secs previously. > >>> 3) Based on #1 and #2 I don't fragmentation has become an issue as > >>> this happens as I reach the 10 day storage range. > >>> 4) Your suggestion of blocking the data into tables by day sounds > >>> interesting but it becomes an architectural issue for me as I would > >>> now have to do joins across tables to get at the data. > >>> -- Is dropping a table significantly faster than deleting rows from > >>> it? > >>> -- Isn't the table drop considered a transaction so that the dropped > >>> rows will need to be copied to the transaction log? > >>> -- If I create tables for each half hour time period (50K rows) or I > >>> create tables for each day (48 * 50k) will the time be significantly > >>> different for dropping the table? > >>> -- It seems that using joins will shift the time burden to the SELECT > >>> query slowing it down significantly? > >>> 5) I will try the -tcpShutdownForce option to see if things terminate > >>> quicker. > >>> 6) I will try using a profiling tool. Do you have any open source > >>> suggestions? My profiling has been mostly in app query timing and > >>> shell timing. > >>> 7) Is there any trace I can use on the server to see what its doing > >>> while its doing it? > >>> Thanks for your time, > >>> Jim McArdle > >>> On Feb 8, 11:27 pm, Thomas Mueller<[email protected]> > >>> wrote: > >>>> Hi, > >>>> What I'm seeing is that all the queries run quickly with the exception > >>>>> of deletes. > >>>> Deleting using the primary key should help. Another solution is to use > >>>> multiple tables, so that instead of deleting a number of rows you could > >>>> drop or truncate a table. > >>>> times for the 15 deletes and it averages out to 60-70 rows/sec. > >>>> Possibly you need to defragment the database. To do that, use SHUTDOWN > >>>> DEFRAG. There is also an option to only defragment partially. > >>>>> -- Is there a way to do smaller block deletes and to force the H2 > >>>>> Server to do the database cleanup? > >>>> You could delete less rows at a time using DELETE ... WHERE ROWNUM()< > >>>> 100. > >>>>> -- Could ANALYZE or some option on DELETE force a cleanup for smaller > >>>>> block deletes? > >>>> The statement ANALYZE calculates the selectivity as documented, nothing > >>>> else. > >>>>> LOB files > >>>>> -- How can these be eliminated? > >>>> Upgrade to the latest version. > >>>> -- Do I need to move to a newer version of H2 for lobs in database > >>>>> storage? > >>>> You actually need to re-build the database to make them go away. > >>>>> 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. > >>>> You could get a few full thread dumps to find out what is happening. I > >>>> usually use > >>>> jps -l > >>>> jstack -l<pid> > >>>>> We do use the -tcpShutdown but it doesn't seem to shutdown the server > >>>> as quickly as needed. > >>>> You could use -tcpShutdownForce as documented. > >>>>> The sql I use is: DELETE FROM MySensorTable WHERE SweepNo<99999 and > >>>> rownum<999; > >>>> That means at most 998 rows are deleted at any time. > >>>>> 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. > >>>> I don't understand, do you mean the "rownum<999" didn't work? Or did you > >>>> call the DELETE statement multiple times in a loop? > >>>>> I realize that deletes copy the row to the transaction log in case of > >>>>> failure or disconnect > >>>> Actually, it's a bit more complicated than that. > >>>> In most cases, I found the best way to deal with performance problems is > >>>> to > >>>> use a profiling tool. Sometimes the problem is in a completely different > >>>> area than originally thought. > >>>> Regards, > >>>> Thomas -- 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.
