Hi,
Thanks a lot for the test case! I am working on a fix for this problem
now. I believe this will be fixed in the next release. The problem is
that for MVCC the rows are copied in one large transaction (CREATE
TABLE... and then INSERT INTO .. SELECT). I will try to use CREATE
TABLE ... AS SELECT, this should solve the problem.
Regards,
Thomas
On Tue, Oct 28, 2008 at 7:35 PM, Christopher Ekberg
<[EMAIL PROTECTED]> wrote:
> We see a sudden huge ramp in execution times for ALTER TABLE ADD .. as
> we increase the number of rows in our database. It starts getting bad
> around 90000 rows when we use MVCC.
>
> We're using H2 1.0.77.
> We get similar results for encrypted and non-encrypted tests.
> We get similar results for specifying a default for the new column
> versus not specifying ("NOT NULL DEFAULT 0")
> "SET MAX_MEMORY_ROWS 1000000" doesn't seem to help.
> "SET MAX_MEMORY_UNDO 1000000" doesn't seem to help.
> "SET MAX_OPERATION_MEMORY 0" doesn't seem to help.
>
> I do not seem to get the same problem when I don't specify MVCC=true!
>
> Attached is a test program, which can be used to generate output like
> I saw:
>
> encryped=false MVCC=false, 0 rows: insert elapsed=141ms avg=0.000,
> alter elapsed=18ms avg=0.000
> encryped=false MVCC=true, 0 rows: insert elapsed=2ms avg=0.000, alter
> elapsed=3ms avg=0.000
> encryped=false MVCC=false, 10000 rows: insert elapsed=578ms avg=0.058,
> alter elapsed=149ms avg=0.015
> encryped=false MVCC=true, 10000 rows: insert elapsed=371ms avg=0.037,
> alter elapsed=200ms avg=0.020
> encryped=false MVCC=false, 20000 rows: insert elapsed=605ms avg=0.030,
> alter elapsed=201ms avg=0.010
> encryped=false MVCC=true, 20000 rows: insert elapsed=640ms avg=0.032,
> alter elapsed=406ms avg=0.020
> encryped=false MVCC=false, 30000 rows: insert elapsed=854ms avg=0.028,
> alter elapsed=374ms avg=0.012
> encryped=false MVCC=true, 30000 rows: insert elapsed=992ms avg=0.033,
> alter elapsed=512ms avg=0.017
> encryped=false MVCC=false, 40000 rows: insert elapsed=1124ms
> avg=0.028, alter elapsed=481ms avg=0.012
> encryped=false MVCC=true, 40000 rows: insert elapsed=1280ms
> avg=0.032, alter elapsed=762ms avg=0.019
> encryped=false MVCC=false, 50000 rows: insert elapsed=1483ms
> avg=0.030, alter elapsed=693ms avg=0.014
> encryped=false MVCC=true, 50000 rows: insert elapsed=1653ms
> avg=0.033, alter elapsed=1019ms avg=0.020
> encryped=false MVCC=false, 60000 rows: insert elapsed=1723ms
> avg=0.029, alter elapsed=1118ms avg=0.019
> encryped=false MVCC=true, 60000 rows: insert elapsed=2007ms
> avg=0.033, alter elapsed=1471ms avg=0.025
> encryped=false MVCC=false, 70000 rows: insert elapsed=2115ms
> avg=0.030, alter elapsed=1466ms avg=0.021
> encryped=false MVCC=true, 70000 rows: insert elapsed=2422ms
> avg=0.035, alter elapsed=2036ms avg=0.029
> encryped=false MVCC=false, 80000 rows: insert elapsed=2379ms
> avg=0.030, alter elapsed=1931ms avg=0.024
> encryped=false MVCC=true, 80000 rows: insert elapsed=2678ms
> avg=0.033, alter elapsed=2225ms avg=0.028
> encryped=false MVCC=false, 90000 rows: insert elapsed=2755ms
> avg=0.031, alter elapsed=2037ms avg=0.023
> encryped=false MVCC=true, 90000 rows: insert elapsed=3075ms
> avg=0.034, alter elapsed=26044ms avg=0.289
> encryped=false MVCC=false, 100000 rows: insert elapsed=3134ms
> avg=0.031, alter elapsed=2391ms avg=0.024
> encryped=false MVCC=true, 100000 rows: insert elapsed=3500ms
> avg=0.035, alter elapsed=120592ms avg=1.206
> ...
>
>
> Note the huge jump in the ALTER TABLE ADD time when I had created
> 90000 rows in an MVCC database - jumped tenfold over previous step.
> And went up another 5-fold when I created 100000 rows.
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
