Hi Thomas, setting the query cache to 0 solved the problem.
With a size of 1 the cache still consumed 70MB. The largest statement is approximately 140KB long. The statement is not a prepared statement. The statement outer joins two tables containing approximately 20000 rows/50 columns (Oracle's ALLT_TABLES view was created as a table with all data within a H2 database) and calculates the number of differences in the records (it's a kind of validation for a data migration). If the cache just stores the statements, then I wonder why it is so huge, when the statement is "only" 140 KB. Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail: mailto:[email protected] www: http://www.finaris.de und http://www.rapidrep.com From: Thomas Mueller <[email protected]> To: H2 Google Group <[email protected]> Date: 08.05.2013 21:49 Subject: Re: Memory Usage Problem Sent by: [email protected] Hi, There is not just one cache. It's in another cache, the "queryCache", not the regular row cache. See http://h2database.com/javadoc/org/h2/constant/DbSettings.html?highlight=queryCacheSize&search=queryCache#QUERY_CACHE_SIZE However, I wonder what data is cached in the query cache. How large are your query statements? Regards, Thomas On Wed, May 8, 2013 at 5:34 PM, <[email protected]> wrote: I did so, but as i see it, that setting does not have any effect for in memory DBs as I use it. Cache Settings The database keeps most frequently used data in the main memory. The amount of memory used for caching can be changed using the setting CACHE_SIZE. This setting can be set in the database connection URL ( jdbc:h2:~/test;CACHE_SIZE=131072), or it can be changed at runtime using SET CACHE_SIZE size. The size of the cache, as represented by CACHE_SIZE is measured in KB, with each KB being 1024 bytes. This setting has no effect for in-memory databases. For persistent databases, the setting is stored in the database and re-used when the database is opened the next time. However, when opening an existing database, the cache size is set to at most half the amount of memory available for the virtual machine (Runtime.getRuntime().maxMemory()), even if the cache size setting stored in the database is larger; however the setting stored in the database is kept. Setting the cache size in the database URL or explicitly using SET CACHE_SIZE overrides this value (even if larger than the physical memory). To get the current used maximum cache size, use the query SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'info.CACHE_MAX_SIZE' An experimental scan-resistant cache algorithm "Two Queue" (2Q) is available. To enable it, append ;CACHE_TYPE=TQ to the database URL. The cache might not actually improve performance. If you plan to use it, please run your own test cases first. Also included is an experimental second level soft reference cache. Rows in this cache are only garbage collected on low memory. By default the second level cache is disabled. To enable it, use the prefix SOFT_. Example: jdbc:h2:~/test;CACHE_TYPE=SOFT_LRU. The cache might not actually improve performance. If you plan to use it, please run your own test cases first. To get information about page reads and writes, and the current caching algorithm in use, call SELECT * FROM INFORMATION_SCHEMA.SETTINGS. The number of pages read / written is listed. Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail: mailto:[email protected] www: http://www.finaris.de und http://www.rapidrep.com From: Noel Grandin <[email protected]> To: [email protected] Cc: [email protected] Date: 08.05.2013 15:59 Subject: Re: Memory Usage Problem I could just tell you, but that would deprive you of discovering the excellent search feature on our website. On 2013-05-08 15:22, [email protected] wrote: Hi, I use H2 database as embedded database in memory. The queries processed by the database are quite large, as result my memory profiler tells me that the queryCache (Type org.h2.util.SamllLRUCache) member of an instance of org.h2.engine.Session consumes about 100 MB heap space. Is there a possibility to limit that cache size? Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail: mailto:[email protected] www: http://www.finaris.de und http://www.rapidrep.com ======================================================= Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873 Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl ======================================================= -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out. ======================================================= Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873 Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl ======================================================= -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out. ======================================================= Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873 Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl ======================================================= -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
