Evolve Your Application with OpenEdge 12.1www.progress.com/.../evolve-your-application-with-openedge-12-1Limit on the Number of Unique Shared Sequences IncreasedThe maximum number of unique shared sequences in an OpenEdge database has been increased to 32K regardless of block size. The maximum number of multi-tenant sequences is now 2000.
Each sequence uses 8 bytes in a sequence block. It means that now db can have up to 16 sequence blocks (in 1K db). All sequence blocks belong to a chain that called NOCHN (bk_frchn 127). First block on the chain is still reported by mb_seqblk in Master Block.
At db startup a broker does NOT load the sequence blocks into buffer pool. Promon/Activity: Buffer Cache screen shows two sequence blocks but the screen lies (so can we trust the values of the other fields added in 11.7?).
The numbers of new messages in 12.1: from 19426 to 19512. But only two of them are related to the sequences:
Cannot add any more multi-tenant sequences to the database. (19476)
In fact a session silently crashed after loading 2431 sequences (1K db) or 2555 sequences (8K db).
The load of 2300 sequences was successful and there were no system records with the sizes approaching 32K limit. Is it really dangerous to exceed the limit in 2000 sequences? Why I did not get the error 19476?
Please increase -B to fix more sequence buffers in memory for enhanced sequence performance. (19493)
What the enhanced sequence performance means?Under which conditions we can get the error 19493?
I'm interested in the answers you find to this question ... we are rapidly approaching the 2000 seq limit in 11.7, and until 12.1 we were looking at re-doing how we use sequences.
Now we have an second option to try, but it would be great to learn early what the practical limit of sequences are.
Sorry your having problems using this new feature of OpenEdge 12.1.
Not sure what you are running into but more information is needed to better understand it. I also suggest you engage Technical Support as this feature should be working as described in the documentation.
I have had little problem adding 32 000 sequences on 1k, 4k and 8k databases from a linux machine running OpenEdge 12.1 via a dictionary load .df.
Because this was all being done in one transaction cached in the client, my first attempt failed with the following error:
-s stack exceeded
Running with -s 100000 solved the problem for me. Probably could be smaller but I don't have the energy to find the minimum value.
"At db startup a broker does NOT load the sequence blocks into buffer pool."
The first 2 sequence blocks are loaded into the buffer pool at database startup. The remaining are loaded on demand.
"Promon/Activity: Buffer Cache screen shows two sequence blocks but the screen lies (so can we trust the values of the other fields added in 11.7?)."
I'm not sure how to address this statement. The values listed there should be correct at the time the buffer pool was scanned. They will change based on other activity occurring on the database. I believe they are correct and you can trust the fields added in 11.7. In OpenEdge 12.1, if you were to access a sequence out of each sequence block after database startup you should see that the expected # of sequence blocks are reported.
"The load of 2300 sequences was successful and there were no system records with the sizes approaching 32K limit."
I don't see a connection between max record size and the # of sequences.
"Is it really dangerous to exceed the limit in 2000 sequences?"
No it is not. As mentioned earlier in this post, it is expected that this new feature is working.
"Why I did not get the error 19476?"
Presumably because your attempt failed for some other reason than exceeding the maximum # of sequences. Maybe you encountered the "-s" client error as I did.
"Please increase -B to fix more sequence buffers in memory for enhanced sequence performance. (19493)
"What the enhanced sequence performance means?"
"Under which conditions we can get the error 19493?"
This enhancement is expected to be released with OpenEdge 12.2. in order to improve the concurrent access to sequences, an in-memory db technique will be used to access the data for sequence read requests. In support of that, the sequence blocks must be "anchored" in the buffer pool, that is prevented from being paged out of the buffer pool. In the case of ridiculously small buffer pools or deployments with many tenants and tenant specific sequences and relatively small buffer pools this message will appear stating that the performance enhancement will not be used to access sequence data.
One other clarification, with 32,000 sequences:
1K db: Sequence Blocks 256 (why do we still support 1K dbs?)
2K db: Sequence Blocks 128
4k db: Sequence Blocks 64
8k db: Sequence Blocks 32
To add to Rich's response:
Are you trying to add Multitenant sequence or shared sequence ? Maximum number of 32000 sequence is supported for only shared sequence in 12.1 This will be supported for Multitenant sequence in 12.2.
Multitenant sequences can exist in only first two sequence blocks in 12.1. This means, there can be maximum 2000 MT sequences for 8K blocks. This is similar to behaviour in 12.0 and previous versions. You will get message 19476, if you try to add MT sequences once first two sequence blocks are full.
Message 19493 is for sequence performance work in 12.2. You will not encounter this message in 12.1 .
Tests confirmed that in V12.1 the maximum number of sequences is 32000. In V12.0 or earlier it’s 250 * (Block Size / 1024).
An attempt to load an additional sequence causes prodict/_lodsddl.p to issue the message:"Maximum number of sequences has been reached."
It will issue the error # 9335:
Cannot add any more sequences to the database. (9335)
> Running with -s 100000 solved the problem for me. Probably could be smaller but I don't have the energy to find the minimum value.
Loading only one sequence vs. 2000 sequences at once with the -y option:
Memory usage summary: Current Max Used Limit (Bytes)
Stack usage (-s): 112 16064 131072
Stack usage (-s): 112 68120 131072
Max Used size is increased by 52,056 bytes or 26 bytes per sequence.
Loading 4000 sequences at once did result in the error:WARNING: -s stack exceeded. Raising STOP condition and attempting to write stack trace to procore file. Consider increasing -s startup parameter.
Memory usage summary: Current Max Used Limit (Bytes)
Stack usage (-s): 112 132896 131072
Max Used size is increased by another 64,776 bytes (32+ bytes per sequence).Despite the error all 4000 sequences were successfully loaded.
In my yesterday’s tests there were no errors. The session just crashed with procore file:
*** ABL Call Stack ***
Last action: BLOCK HEADER (2)
1111: prodict/dump/_load_df.p (prodict/dump/_load_df.r)
57: prodict/load_df.p (prodict/load_df.r)
If I load the sequences by the chunks of 2000 the time to load a chunk is growing with the number of previously loaded sequences:
Total SeqNum Time to load 2000 seq
DEFINE VARIABLE vDfFile AS CHARACTER NO-UNDO INITIAL "_seqdefs.df".
DEFINE VARIABLE vMyLog AS CHARACTER NO-UNDO INITIAL "seqload.log".
DEFINE VARIABLE vMaxSeq AS INTEGER NO-UNDO INITIAL 34000.
DEFINE VARIABLE vSeqChunk AS INTEGER NO-UNDO INITIAL 2000.
DEFINE VARIABLE vSeqNum AS INTEGER NO-UNDO.
DEFINE VARIABLE vTime1 AS INTEGER NO-UNDO.
DEFINE VARIABLE vTime2 AS INTEGER NO-UNDO.
REPEAT vSeqNum = 1 TO vMaxSeq:
IF vSeqNum MOD vSeqChunk EQ 1 THEN
OUTPUT TO VALUE(vDfFile).
"ADD SEQUENCE ~"SEQ" vSeqNum "~"" SKIP
" INITIAL " vSeqNum SKIP
" INCREMENT 5" SKIP
" CYCLE-ON-LIMIT no" SKIP
" MIN-VAL 1" SKIP(1).
IF vSeqNum MOD vSeqChunk NE 0 THEN
ASSIGN vTime1 = ETIME.
RUN prodict/load_df.p(INPUT vDfFile).
ASSIGN vTime2 = ETIME.
OUTPUT TO VALUE(vMyLog) APPEND.
PUT vSeqNum (vTime2 - vTime1) SKIP.
Dbanalys:256 sequence block(s) found in the database.
> why do we still support 1K dbs?
For the tests! Tests should be extreme. :-)“A kilogram of green is greener than half a kilo.”“One square centimeter of blue is not as blue as a square meter of blue."
> I don't see a connection between max record size and the # of sequences.
I expected the limit for the sequences is similar to the one for the number of the fields per table where it’s limited by the size of _File._Field-map. I was wrong: 32000 sequences looks like an artificial limit.
> I'm not sure how to address this statement. The values listed there should be correct at the time the buffer pool was scanned.
Mia culpa. In my yesteday’s tests I filled the small buffer pool by the sequence blocks and then I checked the “Activity: Buffer Cache” screen in promon. But I forgot to update the screen with ‘L’ or ‘U’ action. As result I saw the values as they were when I entered R&D level. The “* Blocks” fields are the status ones but they use the rules other than the fields on Status screens.
> The first 2 sequence blocks are loaded into the buffer pool at database startup. The remaining are loaded on demand.
proutil -C dprpr:
0000 bk_dbkey: 0x00000060 96
bk_type: 0x06 6 (Sequence Block)
bk_frchn: 0x7f 127 (NOCHN)
bk_incr: 0x0001 1
bk_nextf: 0x00000f40 3904
promon/Status: Cache Entries
Num DBKEY Area Hash T S Usect Flags Updctr Lsn Chkpnt Lru Skips
137 96 6 21 S 0 F 126 0 0 0 99
138 3904 6 18 S 0 F 131 0 0 0 99
So mb_seqblk and bk_nextf are loaded in buffer pool at startup.
What became a sequence block will be a sequence block forever, even if we will delete all sequences. The chain will not be changed. I guess it's a reason why the sequence blocks did not get their chain number. The likehood of the chain corruption is next to zero.