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.
