So I did my test again with your tweaks and disabling foreign key, I have the same performance issue : more we have rows more H2 is slow.
Starting 500k rows inserted, mysql is more faster for insertion than H2. MySQLDB... ~7573 i/s 0:06:36.124 (396124 ms) Close 0:00:00.001 (1 ms) HSQLDB... ~4853 i/s 0:10:18.080 (618080 ms) Close 0:00:00.005 (5 ms) H2DB... ~1874 i/s 0:26:40.641 (1600641 ms) Close 0:00:08.438 (8438 ms) Le lundi 12 août 2013 12:52:18 UTC+2, Noel Grandin a écrit : > > > On 2013-08-12 06:12, Pierre wrote: > > Thanks Thomas but the profiler doesn't showed something interesting. > Anyway, the issue is related to H2, it doesn't supports massive inserts. > When the table size > 3 millions rows, it begins to be very slow. Adding > LOG=0;UNDO_LOG=0;NOLOCK/WHATEVER optmisation just delay the ineluctable > slow insertions, it optimize a lot but only if < 3 millions rows. > > I know that because I created a bench and just looks at the numbers : > > > Two things are at work here: > > (1) MySQL and HSQLDB cheat a little by not performing fsync() as often as > they should, so they are trading off increased risk of losing data against > better speed > > (2) When you create an FK constraint, we create an index on the column to > speed up the foreign key checking, which it looks like MySQL and HSQLDB do > not do. This costs us in the insert benchmark because we have to update an > extra on-disk datastructure. > > When I take those two factors out, the numbers start looking roughly > comparable. > > One thing you might want to do when trying to run large volumes of data > through H2 is to bump up our cache size using something like: > ";CACHE_SIZE=65536" in the URL. > > Our default cache setting is a little conservative for larger deployments. > > > > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
