为什么我的H2数据库在磁盘上的容量比应有的大小大7倍? [英] Why is my H2 database 7x larger on disk than it should be?

问题描述

我有一个H2数据库，该数据库的大小已膨胀到几千兆字节，从而导致各种操作问题.数据库大小似乎不正确.因此，我只取了一张小表，只是一张桌子，试图弄清楚发生了什么.

I have an H2 database that has ballooned to several Gigabytes in size, causing all sorts of operational problems. The database size didn't seem right. So I took one little slice of it, just one table, to try to figure out what's going on.

我将此表带入了测试环境:

I brought this table into a test environment:

根据我的计算，这些列每行最多可包含80个字节.

The columns add up to 80 bytes per row, per my calculations.

该表有280,000行.

The table has 280,000 rows.

对于此测试，所有索引均已删除.

For this test, all indexes were removed.

桌子应该占据大约

80 bytes per row * 280,000 rows = 22.4 MB on disk.

但是，它实际上占用了 157 MB .

However, it is physically taking up 157 MB.

我希望在这里和那里看到一些开销，但是为什么这个数据库比可以合理估计的数据库大7倍?

I would expect to see some overhead here and there, but why is this database a full 7x larger than can be reasonably estimated?

更新

CALL DISK_SPACE_USED的输出

推荐答案

在数据库创建中设置MV_STORE=FALSE可解决此问题.现在，整个数据库(不是示例中的测试切片)大约小10倍.

Setting MV_STORE=FALSE on database creation solves the problem. Whole database (not the test slice from the example) is now approximately 10x smaller.

更新

我最近不得不重新讨论该主题，并且不得不与MySQL进行比较.在我的测试数据集上，当MV_STORE = FALSE时，H2数据库占用360MB磁盘空间，而MySQL 5.7 InnoDB上具有默认配置的相同数据则占用432MB. YMMV.

I had to revisit this topic recently and had to run a comparison to MySQL. On my test dataset, when MV_STORE=FALSE, the H2 database takes up 360MB of disk space, while the same data on MySQL 5.7 InnoDB with default-ish configurations takes up 432MB. YMMV.

这篇关于为什么我的H2数据库在磁盘上的容量比应有的大小大7倍?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！