Just to add more info, this was introduced in 1.1.118; thru 1.1.117 sequence allocation stays fast.
In 1.1.118 an after, after 50k allocations ro so, the rateof seqeu allocation drops from 500k/sec to 5k/sec. Ouch. I point out that within this transaction, these ids eventually are shoved into the database as part of data rows, committing ever 20k or so. So we are dropping back to 1.1.117 for production use. So far, I have no better test case for you, though I may try to run it in the debugger and see what the difference is between the first 50k and the slow ones that come after. Chris On Mar 9, 2:52 pm, "[email protected]" <[email protected]> wrote: > FileDescriptor.sync() line: not available [native method] > Thomas, > > I am tracking a problem that is really vexing. We pre-allocated > sequences a lot in our application, using the idiom: > > "select nextval('SEQ') from system_range(1, 100)"; > > a LOT, usually around 10000 at a clip. When I test this for speed in > its own transaction, nothing else going on in the db, I get around > 400k ids/sec generated -- nice. > > However, I am seeing a slowdown when I do this in a transaction that > already has other updates to the database; sequence generation that > took a fraction of a second previously now takes 15 seconds. > > Note that as of 1.1.109, this was fast as goose grease. ;-) > > In the debugger, the stack trace *always* looks like the one I pasted > here. > > Any ideas? Did something change in the lock handling for sequences > such that sequence generation is not cheap and fast anymore? > > I'll try to put together a test case that shows it but I am on a plane > tomorrow early, so. > > I'd hate to devolve to 1.1.109 or so to fix this. > > The trace below is from 1.2.131. > > Chris > > FileObjectDisk.sync() line: 28 > FileStore.sync() line: 453 [local variables unavailable] > PageStore.writeVariableHeader() line: 664 > PageStore.setLogFirstPage(int, int, int) line: 660 > PageLog.removeUntil(int) line: 669 > PageStore.switchLog() line: 564 > PageStore.checkpoint() line: 340 > PageStore.commit(Session) line: 1112 > Database.commit(Session) line: 1672 > Session.commit(boolean) line: 448 > Sequence.flush(Session) line: 135 > Sequence.getNext(Session) line: 93 > SequenceValue.getValue(Session) line: 30 > Function.getValueWithArgs(Session, Expression[]) line: 1041 > Function.getValue(Session) line: 417 > Select.queryFlat(int, LocalResult, long) line: 494 > Select.queryWithoutCache(int) line: 555 > Select(Query).query(int) line: 233 > CommandContainer.query(int) line: 80 > CommandContainer(Command).executeQuery(int, boolean) line: 132 > JdbcPreparedStatement.executeQuery() line: 96 -- 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.
