Thanks Thomas.
On Tuesday, June 24, 2014 3:04:21 PM UTC-4, Thomas Mueller wrote:
>
> 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]
> <javascript:>> 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].
>> 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.
>>
>
--
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.
