Performance: DROP TABLE very slow on large tables

[Wunderbar]

Hello to everyone,

I observed that DROP TABLE is very slow when it affects a large table.
The database consists of hundreds of tables, of which about the
largest 80 tables have about 60.000.000 records each. This is price
data of an exchange - four prices as double, a timestamp, and a few
boolean and int values where the timestamp is the primary key.
Although I know that this is quite a large database, I think that a
DROP TABLE statement should be quite fast.
I found that H2 takes about 10 minutes to drop such a large table. I
had hoped that this could be done in a few seconds. MySQL for example,
stores each table in a seperate file so a drop table is as fast as a
delete file command from the operating system shell.

Is there anything that I could do to speed up the DROP TABLE command?
If not, I would like to request this as a performance improvement for
a future release.

Cheers,
Wunderbar

[rado]

Hello,

As a workaround try first to perform TRUNCATE TABLE and then DROP
TABLE.

Regards
Rado

[kensystem]

Thank you rado, I will try that next time. I believe the time it took
was due the server creating a new/rollback version of the table
+database (clue was files created in the database's dir). I may try
SET LOCK_MODE=0 as well, next time.

Many thanks,
ken

[Thomas Mueller]

Hi,

For me, dropping a large table is fast. Could you create a test case
please? Maybe I'm doing something different.

Regards,
Thomas

[jarradk]

I dropped a table today and it took 7.5 hours!
There were 5 million rows.
There were 6 columns including an identity and a double value plus 4 foreign
identity columns that were indexed (both individually and one composite
index).
I did this from the web console using all default settings.
There were no other connections to the db.
The computer is quite standard and was not a constraint.
The CPU, memory and disk usage were quite standard.

I have a backup of the db so I also tried truncating the same table (on my
backup file) and this took just as long.

Previously I had tried to delete 4 of the 5 million rows but I cancelled
this because it took too long.
There was a huge delay to re-open the database after the cancellation
(several hours).
Then the db was ok again (i.e. responsive).

While I was waiting all those hours I was browsing this forum and also
looking for an alternative database product.
I can't afford to be delayed like this.
How can I avoid this ever happening again?


--
View this message in context: http://h2-database.66688.n3.nabble.com/Performance-DROP-TABLE-very-slow-on-large-tables-tp2885986p4024540.html
Sent from the H2 Database mailing list archive at Nabble.com.

[wburzyns]

I did a similar experiment. My table consists of 3 columns (BIGINT NOT
NULL DEFAULT NEXT VALUE FOR GLOBAL_SEQ PRIMARY KEY, BLOB, BLOB) an
currrently has over 11*10^6 rows (total DB size is over 17 GB).
Dropping of this table was ugly slow - I interrupted it after 15
minutes, cannot afford waiting for several hours like jarradk :) I
noticed that during the proccess the DB files have grown in size by
several GBs. Additionally 'pageStore: Transaction log could not be
truncated' appeared in the trace file.
I'm on latest-greatest version of H2.

[Steve McLeod]

I've also experienced this slowness when dropping a large table. I spent a considerable amount of time with the H2 source code trying to find a way to speed things up, but alas it turns out not to be an easy task with the current data store.

[Noel Grandin]

On 2012-07-05 12:25, Steve McLeod wrote:
> I've also experienced this slowness when dropping a large table. I
> spent a considerable amount of time with the H2 source code trying to
> find a way to speed things up, but alas it turns out not to be an easy
> task with the current data store.
>
>

Hmm, you're right, that code path is pretty deep and winding.

starts here
DropTable#executeDrop()
which calls
Database#removeSchemaObject(...)
which calls
DbObject#removeChildrenAndResources(Session)
which means it's actually calling
RegularTable#removeChildrenAndResources(Session)
which calls
Index#remove(Session)
which means it's actually calling
PageDataIndex#remove(Session)
which calls
PageDataIndex#removeAllRows()
which calls
PageData#freeRecursive()

Can you run a profiler across the code and see where in this call stack
it is spending the bulk of it's time?

[Thomas Mueller]

Hi,

What you could do is create the large table in a separate database, and link it using "create linked table". Instead of dropping the table, you could then just delete the database.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/VD5FkrFQnEkJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Steve McLeod]

I looked back at my work on this some months ago, and actually it was with TRUNCATE TABLE that I did my investigation. You can read the discussion about this here:

https://groups.google.com/forum/?fromgroups#!topic/h2-database/jUqGLVL1FeE

I posted there the following:

This line is the one consuming the time:

                file.readFully(test, 0, 16); 

which is org.h2.store.PageStore.java: line 451 in the current SVN trunk.

[wburzyns]

"What you could do is create the large table in a separate database,
and
link it using "create linked table"."

Is two-phase commit working on linked tables? If not then at least for
me this workaround is no-go.

[jarradk]

Linking to a table in a separate db is a creative solution. Thomas, thank you for the suggestion. It may be useful to me.

 

The intended usage of my table is several hundred million rows.

The columns are one double value plus a primary key plus 3 foreign keys.

There is an index for each foreign key plus one composite index of 3 foreign keys.

Is this within the scope that H2 is designed for?

 

I will want to delete 20 million rows occasionally.

The deletion does not seem possible at this time.

I will try the settings suggested for bulk imports.

 

I have just 5 million rows at present and H2 is not coping.

I’m grateful for the replies by four people – I was despondent and you have cheered me up

 

From: [hidden email]

Sent: Thursday, July 05, 2012 6:40 PM

To: [hidden email]

Subject: Re: Performance: DROP TABLE very slow on large tables

 

Hi,

 

What you could do is create the large table in a separate database, and link it using "create linked table". Instead of dropping the table, you could then just delete the database.

 

Regards,

Thomas

On Thursday, July 5, 2012, Steve McLeod wrote:

I've also experienced this slowness when dropping a large table. I spent a considerable amount of time with the H2 source code trying to find a way to speed things up, but alas it turns out not to be an easy task with the current data store.

 

On Thursday, 5 July 2012 13:35:17 UTC+4, wburzyns wrote:

I did a similar experiment. My table consists of 3 columns (BIGINT NOT
NULL DEFAULT NEXT VALUE FOR GLOBAL_SEQ PRIMARY KEY, BLOB, BLOB) an
currrently has over 11*10^6 rows (total DB size is over 17 GB).
Dropping of this table was ugly slow - I interrupted it after 15
minutes, cannot afford waiting for several hours like jarradk :) I
noticed that during the proccess the DB files have grown in size by
several GBs. Additionally 'pageStore: Transaction log could not be
truncated' appeared in the trace file.
I'm on latest-greatest version of H2.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/VD5FkrFQnEkJ.

To post to this group, send email to <a href="javascript:_e({}, &#39;cvml&#39;, &#39;[hidden email]&#39;);" target="_blank">h2-database@....
To unsubscribe from this group, send email to <a href="javascript:_e({}, &#39;cvml&#39;, &#39;h2-database%[hidden email]&#39;);" target="_blank">h2-database+unsubscribe@....

For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To post to this group, send email to <A href="wlmailhtml:/user/SendEmail.jtp?type=node&amp;node=4024553&amp;i=0" rel=nofollow target=_top link="external">[hidden email].
To unsubscribe from this group, send email to <A href="wlmailhtml:/user/SendEmail.jtp?type=node&amp;node=4024553&amp;i=1" rel=nofollow target=_top link="external">[hidden email].

For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

---

If you reply to this email, your message will be added to the discussion below:

http://h2-database.66688.n3.nabble.com/Performance-DROP-TABLE-very-slow-on-large-tables-tp2885986p4024553.html

To unsubscribe from Performance: DROP TABLE very slow on large tables, click here.
NAML

---

View this message in context: Re: Performance: DROP TABLE very slow on large tables

[Noel Grandin]

Ah, yes, now I remember.

So there are a variety of ways to tackle this.  Of the top of my head I have:

(1) Sacrifice some recovery resistance by not overwriting the headers of the pages.

(2) Change the on-disk format to put headers together, making it quicker to overwrite them. But that would sacrifice performance for normal operations because it would increase the number of IO's performed.

(3) Do a hybrid approach where we don't immediately overwrite the page headers, but use a lower priority background thread to gradually overwrite the page headers.

(4) Use asynchronous IO to speed up the overwriting of the page headers - at the moment we perform the operation sequentially, which is hideously slow on modern hardware, because it waits for each IO to complete before performing the next one.

The option that most interests me is option (4). We could extend the FileStore interface to have a method
    public void writeToMultiple(byte[] data, int[] offsets, int len)
which would use asynchronous IO to push a bunch of writes to the disk.

Unfortunately, asynch disk IO (java.nio.channels.AsynchronousFileChannel) is only available for Java7.
So we have 2 options - emulate it using memory mapped files and the FileChannel#force() method, or simply only enable it when the code is running under Java7.

[Steve McLeod]

Noel,

This approach sounds promising:

> (4) Use asynchronous IO to speed up the overwriting of the page headers - at the moment we perform the operation sequentially, which is hideously slow on modern hardware, because it waits for each IO to complete before performing the next one.

That is, if it is okay with Thomas to have some "if (isJava7) {} else {}" type code.

I might experiment a little with this in the coming days.

[Thomas Mueller]

Hi,

That sounds interesting. Could some of the the code be implemented in
the file system abstraction? Maybe using two write methods (writeAsync
and write) or similar.

Regards,
Thomas

> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/h2-database/-/aE_96rLMMDMJ.
>
> To post to this group, send email to h2-da...@googlegroups.com.

> To unsubscribe from this group, send email to

> h2-database...@googlegroups.com.

[jarradk]

To make the serial functionality parallel may not solve the core problem.

 

My database file is 13G and I can copy it to another file on the same HDD in about 10 minutes.

i.e. all the data can be read and re-written to the same (constrained) device.

Assume that additional processing increases this by one order of magnitude higher.

That would take 100 minutes.

Using a parallel strategy might reduce this.

But the actual time was 450 minutes (to drop one table).

 

Another way of saying this is that 10 minutes of the 450 minutes is the streaming of data on the disk.

So the parallel approach might reduce that 10 minutes.

But the remaining 440 minutes might still take 440 minutes.

So the parallel approach might drop one table in 445 minutes.

 

From: [hidden email]

Sent: Friday, July 06, 2012 9:25 AM

To: [hidden email]

Subject: Re: Performance: DROP TABLE very slow on large tables

 

Ah, yes, now I remember.

So there are a variety of ways to tackle this.  Of the top of my head I have:

(1) Sacrifice some recovery resistance by not overwriting the headers of the pages.

(2) Change the on-disk format to put headers together, making it quicker to overwrite them. But that would sacrifice performance for normal operations because it would increase the number of IO's performed.

(3) Do a hybrid approach where we don't immediately overwrite the page headers, but use a lower priority background thread to gradually overwrite the page headers.

(4) Use asynchronous IO to speed up the overwriting of the page headers - at the moment we perform the operation sequentially, which is hideously slow on modern hardware, because it waits for each IO to complete before performing the next one.

The option that most interests me is option (4). We could extend the FileStore interface to have a method

    public void writeToMultiple(byte[] data, int[] offsets, int len)

which would use asynchronous IO to push a bunch of writes to the disk.

Unfortunately, asynch disk IO (java.nio.channels.AsynchronousFileChannel) is only available for Java7.
So we have 2 options - emulate it using memory mapped files and the FileChannel#force() method, or simply only enable it when the code is running under Java7.

On 2012-07-05 20:37, Steve McLeod wrote:

I looked back at my work on this some months ago, and actually it was with TRUNCATE TABLE that I did my investigation. You can read the discussion about this here:

https://groups.google.com/forum/?fromgroups#!topic/h2-database/jUqGLVL1FeE

 

I posted there the following:

This line is the one consuming the time:

                file.readFully(test, 0, 16);Â

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To post to this group, send email to <A href="wlmailhtml:/user/SendEmail.jtp?type=node&amp;node=4024564&amp;i=0" rel=nofollow target=_top link="external">[hidden email].
To unsubscribe from this group, send email to <A href="wlmailhtml:/user/SendEmail.jtp?type=node&amp;node=4024564&amp;i=1" rel=nofollow target=_top link="external">[hidden email].

For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

If you reply to this email, your message will be added to the discussion below:

http://h2-database.66688.n3.nabble.com/Performance-DROP-TABLE-very-slow-on-large-tables-tp2885986p4024564.html

To unsubscribe from Performance: DROP TABLE very slow on large tables, click here.
NAML

---

View this message in context: Re: Performance: DROP TABLE very slow on large tables

[Steve McLeod]

Hmm, I started looking at the async write approach, then realised that the bottleneck is with reading, not writing. It's the "readFully" that is the culprit. Any suggestions? 

[Noel Grandin]

It should be possible to initiate multiple asynch reads, and then wait for them all to finish, instead of doing a read-one-wait-one loop.

[Noel Grandin]

You know, if the readFully() really is the bottleneck, we should just cache this information.
We only need one bit per page, so it seems pretty cheap to me.
We can just keep another BitField like the "freed" bitfield and save on doing an expensive read.

On 2012-07-09 10:32, Steve McLeod wrote:

[Thomas Mueller]

Hi,

I think before we try to fix the issue, we need to really understand what the problem is. A simple, reproducible test case that includes a micro-benchmark, and optionally uses the profiler tool would be nice. I'm very hesitant to change the storage layer just because we *think* there is way to improve performance.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.

[Noel Grandin]

On 2012-07-09 19:03, Thomas Mueller wrote:
> I think before we try to fix the issue, we need to really understand
> what the problem is. A simple, reproducible test case that includes a
> micro-benchmark, and optionally uses the profiler tool would be nice.
> I'm very hesitant to change the storage layer just because we *think*
> there is way to improve performance.
>
>

Hi

I've checked in a new class to test this:
org.h2.test.todo.TestDropTableLarge

Regards, Noel Grandin


Profiler: top 3 stack trace(s) of 99 ms [build-167]:
44/47 (93%):
at java.io.RandomAccessFile.readBytes(Native Method)
at java.io.RandomAccessFile.read(RandomAccessFile.java:338)
at org.h2.store.fs.FileDisk.read(FilePathDisk.java:424)
at org.h2.store.fs.FileUtils.readFully(FileUtils.java:357)
at org.h2.store.FileStore.readFully(FileStore.java:276)
at org.h2.store.PageStore.readPage(PageStore.java:1285)
at org.h2.store.PageStore.getPage(PageStore.java:735)
at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:226)
at org.h2.index.PageDataNode.freeRecursive(PageDataNode.java:263)
at org.h2.index.PageDataIndex.removeAllRows(PageDataIndex.java:379)
at org.h2.index.PageDataIndex.remove(PageDataIndex.java:354)
at
org.h2.table.RegularTable.removeChildrenAndResources(RegularTable.java:672)
at org.h2.engine.Database.removeSchemaObject(Database.java:1638)
at org.h2.command.ddl.DropTable.executeDrop(DropTable.java:103)
at org.h2.command.ddl.DropTable.update(DropTable.java:113)
at org.h2.command.CommandContainer.update(CommandContainer.java:75)
at org.h2.command.Command.executeUpdate(Command.java:230)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:177)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
at org.h2.test.todo.TestDropTableLarge.test(TestDropTableLarge.java:52)
at org.h2.test.todo.TestDropTableLarge.main(TestDropTableLarge.java:29)
2/47 (4%):
at java.io.RandomAccessFile.seek(Native Method)
at org.h2.store.fs.FileDisk.position(FilePathDisk.java:432)
at org.h2.store.FileStore.seek(FileStore.java:294)
at org.h2.store.PageStore.readPage(PageStore.java:1284)
at org.h2.store.PageStore.getPage(PageStore.java:735)
at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:226)
at org.h2.index.PageDataNode.freeRecursive(PageDataNode.java:263)
at org.h2.index.PageDataIndex.removeAllRows(PageDataIndex.java:379)
at org.h2.index.PageDataIndex.remove(PageDataIndex.java:354)
at
org.h2.table.RegularTable.removeChildrenAndResources(RegularTable.java:672)
at org.h2.engine.Database.removeSchemaObject(Database.java:1638)
at org.h2.command.ddl.DropTable.executeDrop(DropTable.java:103)
at org.h2.command.ddl.DropTable.update(DropTable.java:113)
at org.h2.command.CommandContainer.update(CommandContainer.java:75)
at org.h2.command.Command.executeUpdate(Command.java:230)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:177)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
at org.h2.test.todo.TestDropTableLarge.test(TestDropTableLarge.java:52)
at org.h2.test.todo.TestDropTableLarge.main(TestDropTableLarge.java:29)
1/47 (2%):
at org.h2.store.PageLog.addUndo(PageLog.java:478)
at org.h2.store.PageStore.free(PageStore.java:1216)
at org.h2.store.PageStore.free(PageStore.java:1199)
at org.h2.index.PageDataLeaf.freeRecursive(PageDataLeaf.java:426)
at org.h2.index.PageDataNode.freeRecursive(PageDataNode.java:263)
at org.h2.index.PageDataIndex.removeAllRows(PageDataIndex.java:379)
at org.h2.index.PageDataIndex.remove(PageDataIndex.java:354)
at
org.h2.table.RegularTable.removeChildrenAndResources(RegularTable.java:672)
at org.h2.engine.Database.removeSchemaObject(Database.java:1638)
at org.h2.command.ddl.DropTable.executeDrop(DropTable.java:103)
at org.h2.command.ddl.DropTable.update(DropTable.java:113)
at org.h2.command.CommandContainer.update(CommandContainer.java:75)
at org.h2.command.Command.executeUpdate(Command.java:230)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:177)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
at org.h2.test.todo.TestDropTableLarge.test(TestDropTableLarge.java:52)
at org.h2.test.todo.TestDropTableLarge.main(TestDropTableLarge.java:29)
packages:
97%: org.h2.store.fs
2%: org.h2.store
.