Hi, > Our use case involves running a financial system on an H2 server in > server mode. Our application will insert/delete entries from H2 every > hour. This mission critical financial storage is expected to run > without any downtime, since there are more than 30 servers rely on the > storage.
Do you use some kind of clustering? If not, how do you ensure the servers (hardware) doesn't fail? > The major issue that we are encountering is the H2 DB performance > degraded as the size of the DB grown over time. In order to regain the > H2 performance our operation team needs to remove the database > completely and let the application rebuild the database from scratch, > two times a week. I don't think the problem is related to AUTOVACUUM. Empty pages are automatically re-used at runtime, auto-vacuum is not required at all. Did you check the heap memory? A slowdown can be caused by a memory leak. To verify, run jps -l (to get the process id) and then "jmap -histo <pid>". Please send me or post the result of a slow process. If this is not the problem, could you send me a "slow" and a "fast" database? Maybe the index pages get "almost empty" somehow. If you can't send the database, could you run the Recover tool and send me or post the "Statistics" section of a slow and a fast database? Please not the databases should contain a similar amount of data, otherwise the comparison is meaningless. Example: ---- Statistics ---------- -- page count: 157693 free: 95781 -- page data head: 94947 empty: 5518 rows: 10145679 -- page count type: 0 67% count: 106204 -- page count type: 1 3% count: 5003 -- page count type: 2 0% count: 30 -- page count type: 3 12% count: 20000 -- page count type: 4 0% count: 26 -- page count type: 5 0% count: 1 -- page count type: 6 0% count: 9 -- page count type: 7 0% count: 53 -- page count type: 8 16% count: 26364 Another possible reason could be the order of pages stored on disk. For a large database it can be important, specially when using media with very long seek times (such as CD or DVD). It can also be a problem for hard disks, but usually not. There is a new experimental feature to re-organize the pages: SHUTDOWN DEFRAG. > Does it needs to rebuild the index at some > point as we insert the new data into the empty space? No, unless there is a bug in the indexing algorithm that could cause "almost empty" pages. > Is there any tool that we can use to rebuild the index at runtime? Currently not. 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.
