HSQLDB:为现有数据库启用LOB压缩 [英] HSQLDB: enable LOB compression for existing database

问题描述

我正在使用一个嵌入式HSQLDB 2.3.2实例将XML文档作为LOB存储在应用程序中一段时间​​，该应用程序与应该终止文档的任何位置都有断续的连接.

为限制HSQBDL .lobs文件大小的增长，我已通过文档(使用hsqldb.lob_compressed = true)，但是从最近发现的情况来看，此URL参数没有任何作用.

如果我正确理解HSQLDB JDBC URL解析代码，则org.hsqldb.persist.Logger#setVariables()应该在某个时候检查HsqlDatabaseProperties.hsqldb_lob_file_compressed以设置propCompressLobs，就像对LOB加密&一样. cryptLobs，但从未这样做.

使用URL中的此类参数创建的数据库中的database.script文件，尽管具有URL参数，也没有提及LOB压缩，并且不会压缩LOB.

我尝试在打开数据库后立即发出"SET FILES LOB COMPRESSED TRUE"语句，这对于全新的数据库正确运行(lob被压缩，database.script提到LOB压缩).

当我使用周围的现有数据库之一尝试此操作时，由于现有的LOB而出现错误(错误消息是正在使用数据文件").我理解这背后的原因，因为压缩所有LOB可能要花一些时间，并且可能会运行很长时间，如果被中断，可能会使db处于不良状态.

我认为我可以解决该限制，因为有时会删除XML文档(最终将它们发送出去之后)，因此从理论上讲，DB在某些时候没有LOB.

不幸的是，在空数据库上也会发生此错误，因为删除的LOB中有未使用的LOB条目，我认为这些条目会保留在那里以回收.lobs文件空间.似乎负责允许LOB压缩模式更改的org.hsqldb.persist.Logger#setLobFileCompressed()方法仅检查SYSTEM_LOBS.LOB_IDS中的条目数，无论它们是否正在使用.

根据我的阅读，无法清除已删除的LOB条目(例如，减小.lobs文件的大小)，因此基本上，即使当前存储在LOB中的所有XML文档都已从数据库发送和清除，这不是启用压缩的好时机，因为删除的LOB留下了无法启用压缩的占用空间.

因此，在没有剩余文件的情况下关闭数据库，销毁数据库并创建新的数据库，然后手动发出lob压缩语句的时间很短，我看不出有什么方法可以为具有现有数据库的应用程序启用LOB压缩.

我并不特别喜欢那种似乎可以破解的方法.

我还没有尝试2.3.3，但是从 sources ，在该版本中看起来并没有任何改善.

还有其他方法可以可靠地启用现有HSQLDB数据库的LOB压缩吗?甚至是空"的?

解决方案

无论数据库是一个新数据库还是一个已有活动的数据库，我都找到了一种仅使用SQL可靠地执行此操作的方法.

• 确保使用select count(*) from SYSTEM_LOBS.LOB_IDS
有0个现有LOB
• 在具有LOB的任何表中插入一行，因此LobManager usageChanged设置为true.
• 提交.
• 删除刚刚创建的行.
• 再次提交.
• 执行CHECKPOINT，以便清除所有lob元数据.由于最近添加了LOB，因此检查点将实际运行LobManager.deleteUnusedLobs()并清除未使用的lob条目.
• 使用SET FILES LOB COMPRESSED TRUE启用压缩.现在可以使用，因为SYSTEM_LOBS.LOB_IDS表中不再有任何内容.

如果您随后要可靠地修剪HSQLDB .lobs文件，该文件由于未激活压缩而变得很大:

• 插入带有LOB的另一行，以便HSQLDB可以计算lob空间的使用情况(检查点期间没有LOB时，LobManager.getLobUseLimit不起作用)
• 提交
• 再次执行CHECKPOINT，以便对lob文件进行修剪以使其使用.
• 删除在检查点之前创建的行.
• 提交

I'm using an embedded HSQLDB 2.3.2 instance to store XML documents as LOBs for a while in an application which has intermittent connection to wherever the documents are supposed to end.

To limit HSQBDL's .lobs file size growth, I enabled LOB compression through the jdbc connection URL as mentioned in the documentation (using hsqldb.lob_compressed=true), but from what I've recently discovered this URL parameter has no effect whatsoever.

If I understand the HSQLDB JDBC URL parsing code correctly, org.hsqldb.persist.Logger#setVariables() should at some point check HsqlDatabaseProperties.hsqldb_lob_file_compressed to set propCompressLobs, like it does for LOB crypto & cryptLobs, but never does so.

A database.script file from a database created with such parameter in the URL has no mention of LOB compression despite the URL parameter, and does not compress LOBs.

I've tried issuing a "SET FILES LOB COMPRESSED TRUE" statement just after opening the database, and this works correctly for a brand new database (lobs get compressed, database.script mentions LOB compression).

When I try this with one of the existing databases around I get an error due to the existing LOBs (error message is "data file in use"). I understand the reasoning behind this, since compressing all LOBs might take a while and would probably be a very long running operation which might leave the db in a bad state if interrupted.

I thought I could work around that limitation since there are times when the XML documents are deleted (after they eventually get sent), so theoretically the DB is empty of LOBs at some point.

Unfortunately this error also happens on empty databases because there are unused LOB entries of deleted LOBs, which I assume are kept there to recycle .lobs file space. It seems the org.hsqldb.persist.Logger#setLobFileCompressed() method which is responsible for allowing LOB compression mode changes only checks the number of entries in SYSTEM_LOBS.LOB_IDS, whether they are currently in use or not.

From what I've read, there is no way to purge the deleted LOB entries (for example to reduce .lobs file size) so basically even if all the XML documents currently stored in LOBs get sent and purged from the database, this is not a good time to enable compression either because deleted LOBs leave footprints which prevent enabling compression.

So short of closing the DB when it has no documents left, destroying it, and creating a new DB and then manually issuing the lob compression statement, I don't see any way of enabling LOB compression for applications with existing databases.

I'm not particularly fond of that option which seems way to hackish.

I haven't tried 2.3.3 yet, but from what I see of the sources, it doesn't look any better in that version.

Is there any other way to reliably enable the compression of LOBs of existing HSQLDB databases ? Even "empty" ones ?

解决方案

I've found a way to do it reliably with only SQL, whether the db is a new one or an existing one which has seen some activity.

• ensure there are 0 existing LOBs using select count(*) from SYSTEM_LOBS.LOB_IDS
• insert a row in any table with a LOB so LobManager usageChanged is set to true.
• commit.
• delete the row you just created.
• commit again.
• execute a CHECKPOINT so all lob metadata is cleaned up. Since a LOB has recently been added, the checkpoint will actually run LobManager.deleteUnusedLobs() and cleanup the unused lob entries.
• enable compression using SET FILES LOB COMPRESSED TRUE. This now works because there is no longer anything in the SYSTEM_LOBS.LOB_IDS table.

If you then want to reliably trim the HSQLDB .lobs file which grew huge because compression was not active:

• insert another row with a LOB so HSQLDB can compute the lob space usage (LobManager.getLobUseLimit doesn't work when there are no LOBs during checkpoint)
• commit
• execute a CHECKPOINT again so lob file gets trimmed to usage.
• delete the row you created before checkpoint.
• commit

这篇关于HSQLDB:为现有数据库启用LOB压缩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！