Hi,
The MVStore doesn't re-use the disk space as efficiently as the PageStore.
Version 1.4.x uses the MVStore by default. To shrink a database with
version 1.4.x, you need to run "shutdown defrag". What you did ("shutdown
compact") doesn't have a big effect yet, it only frees up about 16 MB per
second, which is not that much. With the "defrag" option, it shrinks much
faster, but it is not incremental (everything in one step). Closing a
database doesn't shrink the database currently.
This is the main area I will work on in the next weeks. Disk space should
be re-used earlier, and both closing a database and "shutdown compact"
should shrink the database more efficiently.
Regards,
Thomas
On Tuesday, June 24, 2014, Kenton Garner <[email protected]> wrote:
> Looking for best practices to control DB File size - indexes seem to be an
> issue...
>
> Had a DB file nearing 6.5GB.
> Deleted all but 7 rows and size remained constant. Not surprised
> documents indicate that space is reused.
> However, closing the connection is supposed to run some minimal cleanup
> and compression - not noticing any.
>
> Ran "SHUTDOWN COMPACT" to force a full compact and regained a few MB -
> nothing much.
>
> Dropped all of my indexes ( then recreated them ) and "SHUTDOWN COMPACT"
> had significant effect down to 5 MB total.
>
> Ran the compact routine that is listed in documentation
>
> public static void compact(String dir, String dbName,
>> String user, String password) throws Exception {
>> String url = "jdbc:h2:" + dir + "/" + dbName;
>> String file = "data/test.sql";
>> Script.execute(url, user, password, file);
>> DeleteDbFiles.execute(dir, dbName, true);
>> RunScript.execute(url, user, password, file, null, false);
>> }
>>
>>
>
> Now my DB file is 16KB total. ( from 6.5GB to 16KB )
>
> 1. Are indexes ever cleaned-up, space reused, etc.?
>
> 2. Is there anyway to rebuild indexes without regenerating the DB file
> completely?
>
> 3. Is it safe to drop and create indexes while the database is in use?
> Oracle throws a hissy fit with this but includes an "online" argument to
> support it.
>
> I need a way to compact without requiring manual intervention or serious
> down-time. Since I am running in mixed-mode I will have no way to know if
> a monitoring session is connected to the database at the same time the
> application is running. Therefore, I cannot simply stop the connection and
> run the compact routine because other processes may have an active
> connection I am unaware of.
>
> As I stated I started with a DB File of approx 6.5GB. This was for only
> one table containing approx 950K rows of data approx 160 bytes/row.
> Even with indexes I am concerned that DB file was that large.
> I have to be able to support 10 times that number of rows in production.
>
> *connection URL:
> "jdbc:h2:audit;AUTOCOMMIT=ON;MVCC=TRUE;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=8700*
>
> H2 version: 1.4.179
>
>
>
>
> --
> 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]
> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');>
> .
> To post to this group, send email to [email protected]
> <javascript:_e(%7B%7D,'cvml','[email protected]');>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
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.
For more options, visit https://groups.google.com/d/optout.
