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.
