It has been discussed already that long RM chains are bad. I would like to illustrate it with an example from our application.
Below is RM chain from area 7. I take dbanalys every 5 days, so here is RM chain report.
Date ChainBlocks AreaNumber TossLimit
-------------- ---------- ---------
03/20/16 3,791,847 7 1000
03/25/16 3,792,569 7 1000
03/30/16 3,794,510 7 1000
04/05/16 3,795,231 7 1000
04/10/16 3,792,281 7 1000
04/15/16 3,792,719 7 1000
04/20/16 3,790,278 7 1000
04/25/16 3,790,677 7 1000
04/30/16 3,795,934 7 1000
05/05/16 3,802,563 7 1000
05/10/16 3,802,373 7 1000
05/15/16 3,929,183 7 1000
05/20/16 3,934,823 7 1000
05/25/16 3,946,528 7 300
05/30/16 1,359,579 7 300 <<- Here we loaded lots of orders ( from .d )
06/05/16 1,393,186 7 1000
06/10/16 1,408,275 7 1000
06/15/16 1,454,203 7 1000
06/20/16 1,444,509 7 1000
06/25/16 1,484,358 7 1000
06/30/16 1,509,322 7 1000
07/05/16 1,518,243 7 1000
07/10/16 1,546,915 7 1000
07/15/16 1,578,015 7 1000
07/20/16 1,599,304 7 1000
07/25/16 1,619,368 7 1000
07/30/16 1,658,683 7 1000
08/05/16 1,694,500 7 1000
08/10/16 1,719,419 7 1000
08/15/16 1,742,451 7 1000
08/20/16 1,785,882 7 1000
08/25/16 1,812,369 7 1000
08/30/16 1,838,663 7 1000
09/05/16 3,386,546 7 1000 <<-- Here we start purge lots of orders
09/10/16 4,948,694 7 1000
09/15/16 5,265,264 7 1000
09/20/16 5,293,341 7 1000
09/25/16 5,615,544 7 1000
09/30/16 5,716,265 7 1000
10/05/16 6,166,931 7 1000 <<-- Here we end purge lots of orders
10/30/16 6,648,192 7 1000
11/05/16 6,702,610 7 1000
11/10/16 6,720,134 7 1000
11/15/16 6,742,676 7 1000
11/20/16 6,775,717 7 1000
11/25/16 6,802,389 7 1000
11/30/16 6,828,313 7 1000
12/05/16 6,853,843 7 1000
12/15/16 6,929,985 7 1000
12/20/16 6,951,442 7 1000
12/25/16 6,968,745 7 1000
12/30/16 6,977,389 7 1000
01/05/17 6,995,395 7 1000
01/10/17 7,014,438 7 1000
01/15/17 7,035,659 7 1000
01/20/17 7,061,193 7 1000
01/25/17 7,080,296 7 1000
01/30/17 7,093,229 7 1000
02/05/17 7,124,838 7 1000
02/10/17 7,152,079 7 1000
02/15/17 7,173,092 7 1000
02/20/17 7,191,032 7 1000
02/25/17 7,224,462 7 1000
03/05/17 7,261,207 7 1000
03/10/17 7,299,123 7 1000
03/15/17 7,325,660 7 1000
03/20/17 7,349,062 7 1000
03/25/17 7,393,918 7 1000
03/30/17 7,422,394 7 1000
04/05/17 7,461,012 7 1000
04/10/17 7,487,417 7 1000
04/15/17 7,535,696 7 1000
04/20/17 7,566,365 7 1000
04/25/17 7,598,086 7 1000
04/30/17 7,634,246 7 1000
05/05/17 7,678,492 7 1000
05/10/17 7,709,616 7 1000
05/15/17 7,736,554 7 1000
05/20/17 7,787,954 7 1000
05/25/17 7,822,075 7 1000
05/30/17 7,841,111 7 1000
06/10/17 7,914,903 7 1000
06/15/17 7,932,609 7 1000
06/20/17 7,947,074 7 1000
06/25/17 7,971,903 7 1000
06/30/17 8,003,669 7 1000
07/05/17 8,020,346 7 1000
07/10/17 8,049,855 7 1000
07/20/17 8,128,226 7 1000
07/25/17 8,158,551 7 ?
07/30/17 8,193,441 7 1000
08/05/17 8,243,791 7 1000
08/10/17 8,279,221 7 1000
08/15/17 8,312,551 7 1000
08/20/17 8,352,129 7 1000
08/25/17 8,394,456 7 1000
08/30/17 8,428,255 7 1000
09/05/17 8,460,226 7 1000
09/10/17 8,497,502 7 1000
09/15/17 8,451,635 7 1000
09/20/17 8,366,962 7 1000
09/25/17 8,278,908 7 1000
10/05/17 8,104,384 7 1000
10/10/17 8,093,380 7 1000
10/15/17 8,075,316 7 1000
10/20/17 8,060,160 7 1000
10/25/17 8,048,729 7 1000
10/30/17 8,038,997 7 1000
11/05/17 8,026,919 7 1000
11/10/17 8,009,376 7 1000
11/15/17 7,997,687 7 1000
11/20/17 7,985,458 7 1000
11/25/17 7,970,287 7 1000
11/30/17 7,953,863 7 1000
12/05/17 7,938,656 7 1000
12/10/17 7,921,915 7 1000
12/15/17 7,904,148 7 1000
12/20/17 7,891,940 7 1000
12/25/17 7,885,295 7 1000
12/30/17 7,877,904 7 1000
01/05/18 7,868,767 7 1000
01/10/18 7,859,442 7 1000
01/15/18 7,850,809 7 1000
01/20/18 7,831,235 7 1000
01/25/18 7,807,833 7 1000
01/30/18 7,785,875 7 1000
02/05/18 7,758,207 7 1000
02/10/18 7,725,572 7 1000
02/15/18 7,702,746 7 1000
02/20/18 7,682,891 7 1000
02/25/18 7,657,587 7 1000
03/05/18 7,617,311 7 1000
03/10/18 7,586,231 7 1000
03/15/18 7,571,272 7 1000
03/20/18 7,557,855 7 1000
03/25/18 7,543,017 7 1000
03/30/18 7,525,963 7 1000
04/10/18 7,495,817 7 1000
04/15/18 7,479,952 7 1000
04/20/18 7,461,741 7 1000
04/20/18 7,461,741 7 1000
YMMV, that means all applications are different. In our case order is first created, then updated. That is why I believe RM chain does not shrink well.
When we loaded orders from .d file in 05/27/16 we noticed a dramatic RM chain shrinking. Obviously.
Each time we purge orders (Sept 2016 ) RM chain grows. Also makes sense.
But day after day RM chain does not shrink much, just a little bit. An average record size for the "order-line" table ( it is only one table in area 7) is 501 with min 288 and max 714. So even the biggest record is much smaller than the Toss limit of 1000.
I do not think we are unique with such long RM Chains in our database.
One can also notice that RM chain grows for 1 year 09/2016-09/2017 and then starts shrinking a bit.
I believe business helped us. We start accepting more and more EDI orders, i.e. customer sending orders in a file, which essentially is like loading .d file. Web orders are also good for not affecting RM chain.
While it is great when business helps solve technical issues, we would love to see changes on a database side.
> I do not think we are unique with such long RM Chains in our database.
Correct.
How big is the evil of the long RM chains? ;-)
Thanks George, I was just about to ask the same question. Long rm chains are only a problem if the amount of free space in them are too small for new record creations. In that case, then the rm chain is searched, no space found and a new cluster needs to be created. This can adversely affect performance. The fact that rm chains grow and do not shrink is a factor of the record creation, update and delete activities. If rm free space is not used for new records of an appropriate size, then we have a different problem.
Really I meant that we need the tests that would show the impact of the long chains on performance. For example, we can compare the times of chanalys vs ixanalys running on data area. Chanalys will read only blocks on the chains, in other words it will read only x% of blocks in the area. Ixanalys will read sequantially all (data) blocks in the area. Chanalys can be many times slower than ixanalys though it reads a few times less blocks. Of course, it applies only for data on HDD.