Hi Noel and Thomas, I finally fixed my performance issue but not in the way suggested. After reviewing the issue I decided to rebundle the messages (an architecture change) I had originally broken up so that instead of having 200 rows for each message I now had one row. The index tables shrunk dramatically and the database became /14 of what it was. The end result were queries ran much faster and inserts+deletes didn't swamp things as before.
So the lesson I learned was to NOT divide up my data too finely for faster queries as it will have an adverse effect and slow down inserts and deletes in this kind of FIFO queue schema. Regards, Jim McArdle On Feb 10, 12:45 pm, jim <[email protected]> wrote: > 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, > JimMcArdle > > 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, > > > JimMcArdle > > > > 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). > > > >> -- JimMcArdle > > > >> 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, > > >>> JimMcArdle > > >>> 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.
