截断花费太多时间hsqldb [英] Truncating takes too much time hsqldb

问题描述

我使用HSQLDB 2.3.2，但遇到以下问题:

I use HSQLDB 2.3.2 and I've got the following issues:

1. 我的数据库已缓存具有1000000条记录的表，而没有任何约束和索引.它的大小约为900mb.我关闭了自动提交模式，当我尝试执行截断表tableName"时，执行挂起，但是只有dbName.backup在增长.这就是原因:

追踪引擎:? -copyShadow [大小，时间] 2246252 9721
TRACE 引擎:? -setFileModified标志集
跟踪引擎:? -缓存保存 行[count，time]总计24801,9921操作24801,9921 txts 96
追踪引擎:? -copyShadow [大小，时间] 4426920 7732
TRACE 引擎:? -缓存保存行[count，time]总计49609,17775操作 24808,7854 txts 96
追踪引擎:? -copyShadow [大小，时间] 6574796 9024

TRACE ENGINE:? - copyShadow [size, time] 2246252 9721
TRACE ENGINE:? - setFileModified flag set
TRACE ENGINE:? - cache save rows [count,time] totals 24801,9921 operation 24801,9921 txts 96
TRACE ENGINE:? - copyShadow [size, time] 4426920 7732
TRACE ENGINE:? - cache save rows [count,time] totals 49609,17775 operation 24808,7854 txts 96
TRACE ENGINE:? - copyShadow [size, time] 6574796 9024

这大约需要1500-2000秒，最后我可以得到空表或类似这样的异常:

It takes about 1500-2000 seconds and finally I can get either empty table or exception that is something like this:

Tests run: 0, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 1,525.509 sec
org.apache.maven.surefire.util.SurefireReflectionException: java.lang.reflect.InvocationTargetException; nested exception is java.lang.reflect.InvocationTargetException: null
java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
    at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
    at org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:175)
    at org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:107)
    at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:68)
Caused by: java.lang.OutOfMemoryError: Java heap space

截断的dbName.backup被删除之后.我不必在应用程序中使用任何备份，如何避免复制?

After the truncating dbName.backup is removed. I don't have to use any backups in my application, how can I avoid the copying?

1. dbName.properties不起作用.它包含以下文本:
   

1. dbName.properties doesn't work. It contains the following text:
   

#HSQL数据库引擎2.3.2

#HSQL Database Engine 2.3.2

#Thu Mar 19 08:42:10 EAT 2015

#Thu Mar 19 08:42:10 EAT 2015

版本= 2.3.2
Modify = no

version=2.3.2
modified=no

我尝试附加hsqldb.applog=1，但没有任何反应.
dbName.app.log出现，以防您更改dbName.script
中的行SET DATABASE EVENT LOG LEVEL 1. 从我的应用程序使用数据库后，dbName.properties被覆盖:"yes"上的"modified"更改和下面的任何行均被删除.我该怎么办?

I tried to append hsqldb.applog=1 but nothing happened.
dbName.app.log appears in case you change the line SET DATABASE EVENT LOG LEVEL 1 in dbName.script
After working with the database from my application dbName.properties is overwrited: 'modified' changes on 'yes' and any lines below are deleted. What do I do wrong?

推荐答案

我的数据库已缓存了具有1000万条记录的表，而没有任何记录 约束和索引.

My database has cached table with 10 000 000 records without any constraints and indexes.

拥有一个没有任何约束和索引的大表是完全错误的.该表上任何影响几行的SELECT，UPDATE或DELETE都必须搜索表中的所有行.

It is simply wrong to have a large table without any constraints and indexes. Any SELECT, UPDATE or DELETE on this table that affects a few rows has to search all the rows in the table.

我尝试执行截断表tableName"

I try to execute "Truncate table tableName"

以上语句使您可以在提交之前回滚操作.用于回滚的信息存储在内存中，并且当表很大时，内存将用完.当您确实要提交更改时，请改为使用以下语句:

The above statement allows you to roll back the action before you commit. The information for rolling back is stored in memory and memory will run out when the table is very large. As you do want to commit the change, use this statement instead:

TRUNCATE TABLE tableName AND COMMIT

《指南》中提到了这一点: http://hsqldb.org /doc/2.0/guide/dataaccess-chapt.html#dac_truncate_statement

This is mentioned in the Guide: http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_truncate_statement

您可以对.properties文件进行的唯一添加就是将数据库文件设置为只读.任何其他更改都将被忽略并删除.

The only addition that you can make to the .properties file is to set the database files readonly. Any other change will be ignored and deleted.

.backup文件仅供数据库引擎内部使用，您不能停止其使用.

The .backup file is for internal use of the database engine and you cannot stop its use.

这篇关于截断花费太多时间hsqldb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！