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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
