Hi
I had a quick bash at implementing this by taking an exclusive lock on
the DB and re-using the existing compacting code.
So I added a
COMPACT
command.
But the unit test I added indicates that I'm corrupting the database
somehow.
Patch attached - Thomas, perhaps you have an idea?
Regards, Noel.
On 2012-02-15 11:51, IntensiveH2 wrote:
Thanks for the quick answer but currently it's not possible to reboot
my application on a regular basis because my application must ensure
scheduling, failover, HA ....
and it's not possible to stop it.
I really need to have a solution at runtime without restarting my
application.
Thanks.
On 15 fév, 10:40, Noel Grandin<noelgran...@gmail.com> wrote:
Not sure what Thomas' plans are,
but what I can suggest is that you set this setting to a nice large number
-Dh2.maxCompactTime=60000
And reboot your application on a regular basis.
See herehttp://www.h2database.com/html/grammar.html#shutdown
which sets the maximum time to compact the database on shutdown (in
milliseconds).
On 2012-02-15 11:05, IntensiveH2 wrote:
Hi,
I use H2 in a commercial product and I have an issue regarding the
size of the DB (with a customer).
Currently the DB size is 12 GB and after a shutdown compact/restart
the new size is 800 MB
When you start connection on DB (12 GB) it took around 70 minutes to
start (on solaris) before to shutdow it with the compact option.
This is not acceptable (from customer comment).
NOTE: current version used of h2 are 1.3.159, 1.3.161 and 1.3.163 (the
result is the same for all version)
Do you have planned (urgently) to defragment/compact at runtime
(similar to SHUTDOWN COMPACT) in a background thread?
Do you have another solution to avoid this issue (long startup and
compact at runtime)?
Best regards.
Thierry.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Index: src/docsrc/help/help.csv
===================================================================
--- src/docsrc/help/help.csv (revision 4119)
+++ src/docsrc/help/help.csv (working copy)
@@ -1494,6 +1494,16 @@
SET WRITE_DELAY 2000
"
+"Commands (Other)","COMPACT","
+COMPACT
+","
+Performs an online compaction and defrag of the database. Note that this will
obtain an exclusive lock on the database for the duration of the operation.
+
+Admin rights are required to execute this command, as it affects all
connections.
+","
+COMPACT
+"
+
"Commands (Other)","SHUTDOWN","
SHUTDOWN [ IMMEDIATELY | COMPACT | DEFRAG ]
","
Index: src/main/org/h2/command/Parser.java
===================================================================
--- src/main/org/h2/command/Parser.java (revision 4118)
+++ src/main/org/h2/command/Parser.java (working copy)
@@ -328,6 +328,8 @@
c = parseCheckpoint();
} else if (readIf("COMMENT")) {
c = parseComment();
+ } else if (readIf("COMPACT")) {
+ c = parseCompact();
}
break;
case 'd':
@@ -541,6 +543,11 @@
return new TransactionCommand(session, type);
}
+ private Prepared parseCompact() {
+ Set command = new Set(session, SetTypes.ONLINE_COMPACT);
+ return command;
+ }
+
private TransactionCommand parseRollback() {
TransactionCommand command;
if (readIf("TRANSACTION")) {
Index: src/main/org/h2/command/dml/Set.java
===================================================================
--- src/main/org/h2/command/dml/Set.java (revision 4118)
+++ src/main/org/h2/command/dml/Set.java (working copy)
@@ -346,6 +346,11 @@
addOrUpdateSetting(name, null, getIntValue());
break;
}
+ case SetTypes.ONLINE_COMPACT: {
+ session.getUser().checkAdmin();
+ database.onlineCompact(session);
+ break;
+ }
default:
DbException.throwInternalError("type="+type);
}
Index: src/main/org/h2/command/dml/SetTypes.java
===================================================================
--- src/main/org/h2/command/dml/SetTypes.java (revision 4118)
+++ src/main/org/h2/command/dml/SetTypes.java (working copy)
@@ -199,6 +199,11 @@
*/
public static final int REDO_LOG_BINARY = 37;
+ /**
+ * The type of a SET ONLINE_COMPACT statement.
+ */
+ public static final int ONLINE_COMPACT = 38;
+
private static final ArrayList<String> TYPES = New.arrayList();
private SetTypes() {
@@ -245,6 +250,7 @@
list.add(VARIABLE, "@");
list.add(QUERY_TIMEOUT, "QUERY_TIMEOUT");
list.add(REDO_LOG_BINARY, "REDO_LOG_BINARY");
+ list.add(ONLINE_COMPACT, "ONLINE_COMPACT");
}
/**
Index: src/main/org/h2/engine/Database.java
===================================================================
--- src/main/org/h2/engine/Database.java (revision 4118)
+++ src/main/org/h2/engine/Database.java (working copy)
@@ -2331,6 +2331,10 @@
conn.setTraceLevel(TraceSystem.OFF);
return conn;
}
+
+ public void onlineCompact(Session session) {
+ pageStore.onlineCompact(session);
+ }
public void setLogMode(int log) {
if (log < 0 || log > 2) {
Index: src/main/org/h2/store/PageStore.java
===================================================================
--- src/main/org/h2/store/PageStore.java (revision 4118)
+++ src/main/org/h2/store/PageStore.java (working copy)
@@ -462,9 +462,19 @@
* Shrink the file so there are no empty pages at the end.
*
* @param compactMode 0 if no compacting should happen, otherwise
- * TransactionCommand.SHUTDOWN_COMPACT or
TransactionCommand.SHUTDOWN_DEFRAG
+ * CommandInterface.SHUTDOWN_COMPACT or CommandInterface.SHUTDOWN_DEFRAG
*/
public synchronized void compact(int compactMode) {
+ compact(database.getSystemSession(), compactMode);
+ }
+
+ /**
+ * Shrink the file so there are no empty pages at the end.
+ *
+ * @param compactMode 0 if no compacting should happen, otherwise
+ * CommandInterface.SHUTDOWN_COMPACT or CommandInterface.SHUTDOWN_DEFRAG
+ */
+ private synchronized void compact(Session session, int compactMode) {
if (!database.getSettings().pageStoreTrim) {
return;
}
@@ -520,7 +530,7 @@
j = maxMove;
break;
}
- if (compact(full, firstFree)) {
+ if (compact(session, full, firstFree)) {
j++;
long now = System.currentTimeMillis();
if (now > start + maxCompactTime) {
@@ -540,17 +550,16 @@
recordedPagesList = New.arrayList();
recordedPagesIndex = new IntIntHashMap();
recordPageReads = true;
- Session s = database.getSystemSession();
for (Table table : tables) {
if (!table.isTemporary() &&
Table.TABLE.equals(table.getTableType())) {
- Index scanIndex = table.getScanIndex(s);
- Cursor cursor = scanIndex.find(s, null, null);
+ Index scanIndex = table.getScanIndex(session);
+ Cursor cursor = scanIndex.find(session, null, null);
while (cursor.next()) {
cursor.get();
}
for (Index index : table.getIndexes()) {
if (index != scanIndex && index.canScan()) {
- cursor = index.find(s, null, null);
+ cursor = index.find(session, null, null);
while (cursor.next()) {
// the data is already read
}
@@ -601,7 +610,7 @@
writeIndexRowCounts();
log.checkpoint();
writeBack();
- commit(systemSession);
+ commit(session);
writeBack();
log.checkpoint();
@@ -635,6 +644,19 @@
}
}
+ /**
+ * Shrink the file so there are no empty pages at the end.
+ */
+ public synchronized void onlineCompact(Session session) {
+ database.setExclusiveSession(session, true);
+
+ database.lockMeta(session);
+
+ compact(session, CommandInterface.SHUTDOWN_COMPACT);
+
+ database.setExclusiveSession(null, false);
+ }
+
private int getFirstFree(int start) {
int free = -1;
for (int id = getFreeListId(start); start < pageCount; id++) {
@@ -684,7 +706,7 @@
}
}
- private boolean compact(int full, int free) {
+ private boolean compact(Session session, int full, int free) {
if (full < MIN_PAGE_COUNT || free == -1 || free >= full ||
!isUsed(full)) {
return false;
}
@@ -706,7 +728,7 @@
trace.debug("move " + p.getPos() + " to " + free);
}
try {
- p.moveTo(systemSession, free);
+ p.moveTo(session, free);
} finally {
changeCount++;
}
Index: src/test/org/h2/test/unit/TestPageStore.java
===================================================================
--- src/test/org/h2/test/unit/TestPageStore.java (revision 4118)
+++ src/test/org/h2/test/unit/TestPageStore.java (working copy)
@@ -70,6 +70,7 @@
testUniqueIndex();
testCreateIndexLater();
testFuzzOperations();
+ testOnlineCompact();
deleteDb("pageStore");
}
@@ -674,6 +675,21 @@
conn.close();
}
+ private void testOnlineCompact() throws SQLException {
+ deleteDb("pageStore");
+ Connection conn = getConnection("pageStore");
+ Statement stat = conn.createStatement();
+ stat.execute("CREATE TABLE TEST(NAME VARCHAR) AS SELECT 1");
+ stat.execute("CREATE INDEX IDX_N ON TEST(NAME)");
+ stat.execute("INSERT INTO TEST SELECT X FROM SYSTEM_RANGE(20, 100)");
+ stat.execute("INSERT INTO TEST SELECT X FROM SYSTEM_RANGE(1000,
1100)");
+ stat.execute("COMPACT");
+ // verify that we can still read and write after the compact operation
+ stat.execute("SELECT * FROM TEST");
+ stat.execute("INSERT INTO TEST SELECT X FROM SYSTEM_RANGE(2000,
2100)");
+ conn.close();
+ }
+
private void testFuzzOperations() throws SQLException {
int best = Integer.MAX_VALUE;
for (int i = 0; i < 10; i++) {
