Since using 1.1.118, I've been getting user reports of my app occasionally performing database tasks very slowly that were previously always fast. But sporadically. Then the problems mysteriously clear up. One user was techie enough to run top, and told me that my app was only using 5% CPU during the extremely slow database work.
I'm using MVCC and CACHE_TYPE=SOFT_LRU. I didn't even think the problem might have been related to the H2 update, until I read this thread. I'm providing this info because I think it is pertinent. I'm sorry that I can't provide repro steps, nor have I isolated factors, except my software is Mac-only. Regards, Steve On Sep 11, 6:02 pm, Sam Van Oort <[email protected]> wrote: > I don't see any obvious culprits for a performance regression in your > code. > > Could be one of the synchronization / concurrency bugs fixed in the > latest version, or maybe fixes for index corruption problems? > > Are you using any of the experimental features? > > Cheers, > Sam Van Oort > > On Sep 10, 10:45 am, Nuno Seco <[email protected]> wrote: > > > Hi. > > I really don't have that much time at the moment (I'm in the middle of > > alpha launch). > > > Anyway here is some more info, and I'll try to follow up with some > > example code as soon as I can. > > > * The insert statements are: > > insertWord = connection.prepareStatement("INSERT INTO WORD(WORD, > > IDDATA_SOURCE) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS); > > insertSynset = connection.prepareStatement("INSERT INTO SYNSET(OFFSET, > > POS, IIC, GLOSS, IDDATA_SOURCE) VALUES > > (?, ?, ?, ?, ?)",Statement.RETURN_GENERATED_KEYS); > > insertSynsetWord = connection.prepareStatement("INSERT INTO SYNSET_WORD > > (IDWORD, IDSYNSET) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS); > > > These are executed inside a loop. > > > * connection strings: > > jdbc:h2:tcp://127.0.0.1:9092/Textonomics/data/db/data (server mode) > > jdbc:h2://home/nseco/Textonomics/data/db/user (emebedded mode) > > > Both modes are really slower > > > * The script used to launch the h2 process (server mode): > > #!/bin/sh > > cp=/usr/local/h2/h2.jar > > java -Xmx512m -cp "$cp" org.h2.tools.Server -tcp > > > * Java Version: > > java version "1.6.0_16" > > Java(TM) SE Runtime Environment (build 1.6.0_16-b01) > > Java HotSpot(TM) 64-Bit Server VM (build 14.2-b01, mixed mode) > > > * The database script (only the relevant tables): > > > SET IGNORECASE TRUE; > > > CREATE TABLE SYNSET > > ( > > IDSYNSET INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, > > OFFSET INTEGER, > > POS VARCHAR(1), > > IIC DOUBLE, > > GLOSS VARCHAR(750), > > IDDATA_SOURCE INTEGER, > > CONSTRAINT FK_SYNSET_1 FOREIGN KEY (IDDATA_SOURCE) REFERENCES > > DATA_SOURCE(IDDATA_SOURCE) > > ); > > > CREATE INDEX SYNSET_OFFSET ON SYNSET(OFFSET); > > CREATE INDEX SYNSET_POS ON SYNSET(POS); > > > CREATE TABLE WORD > > ( > > IDWORD INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, > > WORD VARCHAR(90), > > IDDATA_SOURCE INTEGER, > > CONSTRAINT FK_WORD_1 FOREIGN KEY (IDDATA_SOURCE) REFERENCES > > DATA_SOURCE(IDDATA_SOURCE) > > ); > > > CREATE UNIQUE HASH INDEX HASH_WORD ON WORD(WORD); > > > CREATE TABLE SYNSET_WORD > > ( > > IDSYNSET_WORD INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, > > IDSYNSET INTEGER, > > IDWORD INTEGER, > > CONSTRAINT FK_SYNSET_WORD_1 FOREIGN KEY (IDSYNSET) REFERENCES > > SYNSET(IDSYNSET), > > CONSTRAINT FK_SYNSET_WORD_2 FOREIGN KEY (IDWORD) REFERENCES WORD > > (IDWORD) > > ); > > > CREATE TABLE DATA_SOURCE > > ( > > IDDATA_SOURCE INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY > > KEY, > > SOURCE VARCHAR(90), > > CONSTRAINT UNIQUE_DATA_SOURCE_1 UNIQUE(SOURCE) > > ); > > > =============================== > > > As I said, I'll try to follow up... > > I'm not sure this is helpful as is, but maybe you can spot something > > right off. > > > Cheers, > > > -- > > Nuno Seco > > > On Sep 10, 3:05 pm, Chuck Remes <[email protected]> wrote: > > > > On Sep 10, 2009, at 5:52 AM, Nuno Seco wrote: > > > > > Hi. > > > > > I have a project I'm working on where I load WordNet into a h2 > > > > database. > > > > I changed to version 1.1.118 this morning and its REALLY SLOWER > > > > inserting the data. > > > > > The only thing I've changed was the h2 jar file. I'm back to using > > > > version 1.1.117. > > > > > Anyway just wanted to share that piece of information. > > > > <sigh> What a useless report. > > > > What Java VM? Version? What command-line options do you use to start > > > it up? > > > > How much cache is allocated to H2? Do you set any other H2 options? > > > > What is your table structure? Which column(s) have indexes? > > > > Give us the SQL for your insert. > > > > Are you using PageStore or the original file format? > > > > This isn't even an exhaustive list of details you can and should > > > share. Feel free to expand on this. > > > > If you provided more information, folks with knowledge of the code and > > > the specific changes that occurred between 1.1.117 and 118 could get > > > to fixing it. > > > > cr --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
