Dear All, I posted a similar question on the H2 Group and got some good responses. Here is a link to the thread: http://groups.google.com/group/h2-database/browse_thread/thread/a6c377431ea4b9ed
Or here is a summary of the responses: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ from Sam Van Oort ... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If you look at the "Performance Tuning" section in the documents, there is some good general advice there. If you do a quick search of this group (H2 Database Group), there is also some various performance advice (much of which is still pertinent even with page store). I know I've written a number of posts dealing with performance issues & optimization. Additional advice: -- ALWAYS increase cache settings if you can. This is one of the biggest performance boosts you can give (besides good indices and in- memory tables/indices). -- Embedded mode is *much, much* faster than server mode for H2. If you're using it in the cloud, try to use embedded mode whenever possible! -- Closing a DB can be quite slow, as can opening a DB that was closed improperly (depending on settings it may have to rebuild indices!) -- For tables, speed and memory use increase in this order: Cached < Memory < Not Persistent -- Allocate much more memory than you expect to the JVM when using Memory/Not Persistent Tables and DBs or you can get an OutOfMemoryError -- Note that with "Not Persistent" tables, you lose the durability against power failure. They are extremely fast though. -- Java triggers and stored procedures are *very* fast. In some cases, they may be the fastest way to accomplish a complex operation. -- LZF compression can improve LOB read performance tremendously by reducing I/O, and I think will save memory for memory tables/DBs -- Compression is usually slower if you're on an SSD, unless your CPU is really, really fast -- Choose data types appropriately: Decimal/Numeric types are slower to use and take more storage than the other numeric types, and are really only there for storing currency amounts. -- Hunt down my other post in the groups on performance and data types -- this never made it into the documentation, but it is still important. -- Using the Full Text Search (http://www.h2database.com/html/ tutorial.html#fulltext) can improve performance by an order of magnitude over LIKE '%stuff%' While H2 is stable, some features which increase performance at the cost of stability / data integrity, or are experimental or still not fully tested. You can see a list of unsafe settings here: http://www.h2database.com/html/faq.html#reliable ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ >From Thomas Mueller ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Hi, My response is similar to what Sam already wrote. > high performance web services and sites. I'd like to get this group's opinion > on what the optimum settings I don't know what the exact requirements are, but usually the default settings are fine. The most simple way to speed up H2 is to use a larger cache size: http://www.h2database.com/html/grammar.html#set_cache_size In many cases, it's more important to speed up queries than trying to tweak database settings. Please read the optimization guide at: http://www.h2database.com/html/performance.html#database_performance_... - this will help you ensure indexes are used and such. Also, see http://www.h2database.com/html/performance.html#database_profiling > selects only I depends on the database size, and on how much memory you have. A read-only database is fine, maybe you could use in-memory indexes (they need more memory): http://www.h2database.com/html/grammar.html#create_table - CREATE MEMORY TABLE "Cached tables (the default) are persistent, and the number of rows is not limited by the main memory. Memory tables are persistent, but the index data is kept in main memory, that means memory tables should not get too large." > I've set the H2 database to read only so that it resides in memory. Read-only databases don't reside in memory. They are just read-only. If you want that everything is in memory, you could use an in-memory database - see http://www.h2database.com/html/features.html#in_memory_databases - but that would even need a lot more memory. > Maximum connections is set at 24 and connection timeout at 120. So, you are using a connection pool? If yes, which one? > maximize it's performance for the most concurrent requests? > For read only use would it be safe to use SET LOCK_MODE = 0 in a production > environment? Yes, that is safe. I'm not sure if it will help in your case however. If the database is read-only, then I think it's safe to use the multi-threaded kernel mode: http://www.h2database.com/html/grammar.html#set_multi_threaded Regards, Thomas -- Open BlueDragon Public Mailing List http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon mailing list - http://groups.google.com/group/openbd?hl=en !! save a network - please trim replies before posting !!
