在嵌入式模式下使用H2 DB的性能问题，数据库中的数据量很大 [英] Performance issues using H2 DB in embedded mode with heavy load of data in database
问题描述
我正在使用嵌入式模式下的H2数据库来运行Java应用程序。
我的应用程序消耗了150mb的堆内存。
I am working a java application using H2 Database in embedded mode. My Application consumes 150mb of heap memory.
问题：
步骤
当我用2 mb的数据加载H2数据库时，数据库访问速度很快，堆内存大小为160mb。
Problem: Steps When I load H2 database with 2 mb of data, database access is fast and heap memory size 160mb.
但是当我向H2数据库加载30 mb数据时（h2 db文件大小= 30 mb）。然后从我的应用程序访问数据库非常慢。原因是我的应用程序堆大小极大地增加到300mb，从而降低了性能。我确认使用JConsole。
But When I load H2 database with 30 mb of data(h2 db file size =30 mb). Then accessing the database from my application is very slow. the reason being my application heap size is hugely grown to 300mb of size hence degraded performance. I confirmed using JConsole.
所以我的理解是因为H2数据库是使用Java开发的，并且由于我以嵌入式模式使用H2数据库，所以添加了H2数据库的堆大小
So my understanding is since H2 database is developed using java and since I am using H2 database in embedded mode, the heap size of H2 Database is added to my application which is breaking the application.
问题是随着H2数据库大小的增加，我的应用程序的性能下降。
The problem is as H2 database size is grown, the performance of my application is degraded.
如何解决此问题？
我已将连接设置为
rurl = "jdbc:h2:file:/" + getDBPath() + dbname + ";CACHE_SIZE=" + (1024 * 1024) + ";PAGE_SIZE=512";
增加H2的缓存。
推荐答案
在大多数情况下，性能问题实际上与缓存大小或页面大小无关。要分析性能问题，请特别参见H2文档：
In most cases, performance problems are not actually related to the cache size or page size. To analyze performance problems, see the H2 documentation, specially:
- Database Performance Tuning
- Using the Built-In Profiler
- Application Profiling
- Database Profiling
- Statement Execution Plans
- How Data is Stored and How Indexes Work
如果您将缓存大小手动设置为1024 * 1024，则H2将使用1 GB堆内存。仅当JVM可用的物理内存超过1 GB时（使用 java -Xmx2048m 或类似的内存），才应使用此设置。否则，我建议改用默认设置（16 MB缓存大小）。
If you set the cache size manually to 1024 * 1024, then H2 will use 1 GB heap memory. This setting should only be use if you have a lot more than 1 GB of physical memory available to the JVM (using java -Xmx2048m or similar). Otherwise, I suggest to use the default settings (16 MB cache size) instead.
使用小于默认大小的页面可能会降低性能。这取决于硬盘，并且可能取决于访问模式。但是，没有使用非默认页面大小时的规则列表-找出的唯一方法是尝试不同的设置。
Using a smaller page size than the default might decrease performance. This depends on the hard disk, and possibly on the access pattern. However, there is no list of rules when to use a non-default page size - the only way to find out is to try different settings.
这篇关于在嵌入式模式下使用H2 DB的性能问题，数据库中的数据量很大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
