Announcing Synergy/DE 12.3 LTS Release
March 8, 2024
Automotive Entrepreneur Travels Successful Road with Synergy
April 3, 2024
When it comes to ISAM files, typically only a single factor limits the size of an ISAM file: available disk space. Of course, there is the 2-GB limit (solved by adding “,tbyte” to the file) and the physical ISAM file maximum of 256 TB, but those situations are few and far between. Recently, a Synergy application developer discovered another answer to the “how big is too big” question. While STOREing records to an ISAM file on a disk with hundreds of gigabytes free, the application failed with a Windows system error 665 accompanied by a somewhat cryptic message: “The requested operation could not be completed due to a file system limitation.” Some Google research revealed that this error could also indicate an SQL server problem on Windows having to do with VERY large files (>100 GB) that have reached a limit in fragmentation. Because Synergy also can have VERY large files, a similar problem can occur with our DBMS ISAM files.
Fragmentation issues can typically be avoided by keeping the disk defragmented through regular use of the Microsoft Drive Optimizer or Sysinternal’s contig utility. Unfortunately, it turns out that once a file encounters a fragmentation problem, these utilities do not seem to resolve the issue. The best and quickest solution to get back up and running is to simply copy the file. This seems to do the trick, even with SQL Server, but at a cost of downtime.
How does an ISAM file get fragmented? Whenever a file is extended, if the space that has been allocated to the file is insufficient, the operating system may need to allocate another fragment (or extent) somewhere else on the disk. Over time, active files that are frequently updated will become fragmented, and there really is no way to prevent it. Files on HDDs that are heavily fragmented can affect performance. Regularly using the defragmentation utilities mentioned above can help to alleviate the issue.
Which kinds of ISAM files get fragmented most? The data file (.is1) tends to get the most fragmentation, especially when small segments are written. In the case mentioned above, the record size was 124 bytes with the most common compressed segment size being around 100 bytes. However, the biggest contributor to fragmentation was how the file was used. All writes to the file were in the form of new records added (records were seldom, if ever, deleted), causing the file to get extended frequently.
But how do we avoid this from happening in the first place? If you find yourself in a similar situation and you have an ISAM file where all updates are in the form of new records (no DELETEs) or you have encountered error 665, you may be interested in a few new command options recently added to the isutl utility. These new options were available in the 12.2.1 feature release and are now part of the 12.3 LTS release.
The new isutl -ex #% file command allows you to add free space to a file. By specifying a percentage, you can extend a file by (hopefully) a single extent, depending on available filesystem contiguous free space, pre-allocating contiguous free segments at one time. And, to help choose a percentage to specify, we’ve enhanced the -b (bucket usage statistics) option. This option used to only be allowed following a full file verify (-v), which as files get VERY large can take quite some time to produce. Now, it is also a standalone command that just scans the data file. You can simply use the command isutl -bfs file to produce a listing, where -f means to display the number of file fragments and -s means to suppress the freelist scan, which can take some time on large files with potentially millions of free segments.
What you are looking for is how close to zero the columns under %free and #free are, compared to the #inuse column and across the other buckets.
Bucket Allocation: slen #inuse %free #free lhead 95: 109 22024588 0.8427 185611 0x208059ff2b 96: 110 22504159 0.7515 169124 0x20a8156434 97: 111 6885278 0.6968 47980 0x2083293630 98: 112 9672375 0.6884 66583 0x20a870438e 99: 113 8040938 0.6958 55945 0x20844846b7 100: 114 12481383 0.3766 47004 0x2042ccf717 101: 115 6394156 0.6867 43911 0x20865d674e 102: 116 12822400 0 0 103: 117 3992881 0.6789 27107 0x2087a17239 104: 118 5724366 0.6995 40040 0x2087f0420c 105: 119 7020137 0.7181 50413 0x20a9864053
The above output would indicate that 114- and 116-byte segments appear to be more frequent at this time for some reason. Each record that fits into bucket 102 will be extending the file and more than likely creating a fragment. To avoid further fragmentation, a recommendation would be to reset the upper freelist percentage to at least 0.7%. This command will expand all buckets below 0.7% and ignore those at or above 0.7%:
isutl -ex 0.7% file
resulting in
Bucket Allocation: slen #inuse %free #free lhead 95: 109 22024588 0.8427 185611 0x208059ff2b 96: 110 22504159 0.7515 169124 0x20a8156434 97: 111 6885278 0.7000 48197 0x20aa1042b4 98: 112 9672375 0.7000 67707 0x20aa10a0cb 99: 113 8040938 0.7000 56287 0x20aa128c8b 100: 114 12481383 0.7000 87370 0x20aa132381 101: 115 6394156 0.7000 44760 0x20aa595afd 102: 116 12822400 0.7000 89757 0x20aa5ad860 103: 117 3992881 0.7000 27951 0x20aaf83a21 104: 118 5724366 0.7000 40071 0x20aaf83d6d 105: 119 7020137 0.7181 50413 0x20a9864053
Also, to remain accurate, you may want to run the -b command on a scheduled basis (depending on how fast the file grows) to determine trends. Until you determine how fast the freelist is being depleted, there’s no reason to adjust it. Remember, each time you use -ex, you will be creating another fragment. But consider this: Let us say the above bucket allocation shows a file that just one month ago was extended to 1% free. Given this new information, you might consider extending #free to 1.2% and checking back again in three weeks. After understanding these trends, you may decide to spread this out over many months using much larger free percentages.
Under normal circumstances, a file’s freelist will expand and contract as records are stored and deleted, so unless you are experiencing heavy fragmentation growth or have encountered the aforementioned error 665, you should not have to use this new command. Note that both new commands, -b and -ex, can be used while the file is in use, so there is no need to schedule them during downtime. Now that I’ve given you another reason for a file to be too big, know there is now a tool to make it even bigger.