Sorry - wasn't trying to be rude, I was just embarrassed that I did not know that (needs to be first column of index).
I have included my table and indexes below. This application runs 24x7. During testing we will run constantly. The application will insert a row when a new event occurs. The State of the row will change multiple times ( 4+ times) while it is processed each one causes an update to the row. Then the record will stay in the database for a minimum of 24 hours before the row is deleted. So depending on data flow we can have 1 to 5 million rows inserted during a 24 hour period. I have the application set to 1. Drop and recreate the indexes; 2. Shutdown the DB with DEFRAG; 3. Restart the DB once a day around midnight. I have done this to try and control the size of the DB file. In this most recent case where it took 30 minutes to shutdown it was during a normal application shutdown which just closes the database connection in a shutdown hook as the application goes down. It is the only connection so the db shuts down and removes the lock file. It was the end of the day and I was trying to stop the application for a quick parameter change before leaving. I waited 15 minutes and gave up because the lock file was still there. In the morning the DB File's time stamp indicated that it was last updated 30 minutes after I had shutdown the application. That was probably more that you wanted to know. As far as my indexes go. The MSGDATE is not the first column in the primary key index. However, it happens to be the first column in another index. That little nuance is new to me. Every time I think I fully understand indexes I realize I haven't a clue. CREATE TABLE IF NOT EXISTS CDS_HISTORY( > ID VARCHAR(64) NOT NULL, > MSGDATE TIMESTAMP NOT NULL, > SEQNUM INT NOT NULL, > XACTID VARCHAR(255), > FILENAME VARCHAR(255), > STATE VARCHAR(3) DEFAULT '---', > PRIMARY KEY (ID, MSGDATE, SEQNUM) ) > > In addition to the default index that is generated on the primary key of > the table > I also add the following indexes. > > CREATE INDEX IF NOT EXISTS IDX_HISTORY_FWD ON CDS_HISTORY(MSGDATE, SEQNUM) > CREATE INDEX IF NOT EXISTS IDX_HISTORY_XACTID ON CDS_HISTORY(STATE, XACTID) > Thanks again. On Saturday, July 19, 2014 3:15:03 AM UTC-4, Thomas Mueller wrote: > > Hi, > > I'm aware that the MVStore currently uses a lot more disk space than the > PageStore. I expect big improvements in this area in the next months. I > think in the future, disk space usage will be about the same. It will > probably use a bit more disk space temporarily, but if you keep the > database open then the file will shrink automatically to about the same > size as with the PageStore. > > > It took approx 30 minutes for the db lock file to clean-up > > How did you stop or close the database? Did you run "shutdown defrag" or > so? Could you take a few (30 or so) full thread dumps during the shutdown? > To do that, use "jps -l" to get the process id, then "jstack -l <pid> >> > threadDumps.txt" to get the thread dumps. > > > The MSGDATE field is part of the primary key > > You did not answer this question. What *exactly* is the primary key? It's > not enough if it's _part_ of the primary key, it needs to be the first > column of an index. > > Regards, > Thomas > > > > > > > On Fri, Jul 18, 2014 at 4:44 PM, Kenton Garner <[email protected] > <javascript:>> wrote: > >> Thomas, >> The MVStore is obviously your storage of choice going forward - and it >> has nice benefits. However, in my tests yesterday I inserted ( then >> updated a few times ) a total of 1,696,792 rows. >> This created a dbFile of 8.8GB in size. That works out to 5175.9 bytes >> per row. The pageStore version was closer to 500 bytes per row. That is a >> huge difference. >> >> But the real kick in the ass was when I shutdown my application - It took >> approx 30 minutes for the db lock file to clean-up so that application >> could be re-started. That is a show stopper. >> >> I have re-worked my delete to calculate the number of rows I am deleting >> and I will add (n) deletes of 10000 records at a time into a jdbc batch >> statement. >> Hopefully this will prevent the OutOfMemory exception on the delete and I >> am returning to the PageStore. >> >> Thanks for all your work. >> >> >> >> On Thursday, July 17, 2014 11:33:19 AM UTC-4, Kenton Garner wrote: >>> >>> Thanks again Thomas, >>> >>> I downloaded your latest version and switched back to the MVStore. I am >>> running tests now. >>> One observation that i have noticed is that with the MVStore enabled the >>> database file is approx 6X the size of when I was using the PageStore. >>> I am at approx 1.2 Million messages and the database file is 6+ GB. >>> When I had 2.1 million records in the PageStore the database file was only >>> about 1GB. >>> >>> The change log indicated that you have just implemented the start of the >>> auto-compression for the MVStore. Do you expect to see better sizing as >>> this version matures? >>> Can you give a quick 10000 foot overview of the overhead differences so >>> that we can determine which version is best for us. >>> >>> I have plans to store actual message files in the database. I tested >>> with the PageStore version and was impressed with the compression I was >>> getting. >>> I am however, concerned with the MVStore version trying to store the >>> same files. >>> >>> I can post this question as a new thread if you prefer. >>> >>> >>> On Wednesday, July 16, 2014 3:37:59 AM UTC-4, Thomas Mueller wrote: >>>> >>>> Hi, >>>> >>>> > The MSGDATE field is part of the primary key >>>> >>>> Well, what *exactly* is the primary key? It's not enough if it's _part_ >>>> of the primary key, it needs to be the first column of an index. >>>> >>>> Out of memory: The problem could be MVCC in combination with the >>>> PageStore, I think this can run out of memory. Could you try using the >>>> MVStore? To do that, you would need to create a new database. >>>> >>>> Regards, >>>> Thomas >>>> >>>> >>>> On Wed, Jul 16, 2014 at 9:28 AM, Noel Grandin <[email protected]> >>>> wrote: >>>> >>>>> >>>>> >>>>> You shouldn't be getting OOM, even with very large deletes. >>>>> Try turning on the heap dump on OOM feature of the JVM, and then >>>>> running the Eclipse Memory Analyzer over the resulting dump. >>>>> That should point out the problematic code. >>>>> >>>>> >>>>> -- >>>>> 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/d/optout. >>>>> >>>> >>>> -- >> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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/d/optout.
