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
-~----------~----~----~----~------~----~------~--~---
H2Test.java
Description: Binary data
