Database compression

Hi Yuri,

Only Journal Data Compression to be transmited between mirrors .

Hi

There are a few things to understand here. Cache (including Ensemble and IRIS) is different from other DBMS systems in that if a field/property/column is defined as having a size of 50 characters but you only write 20 characters into that field then only 20 characters will be used in the database record for that field. Unlike other DBMS it does not preallocate 50 characters for that field on every record it creates in the table. Records are created using $LIST. So let us assume you have a table/class with 5 fields then Cache will create a record as:

$listbuild(field1,field2,field3,field4,field5)

and if our fields have the following values:

So our class has the following definition:

Property Field1 as %String(MAXLEN=100);

property Field2 as %String;

property Field3 as %Date;

property Field4 as %Boolean;

property Field5 as %Integer;

and our values for these fields are "Nigel","Salm","1962-09-16","1","0608449093" then our record will look like this:

$lb("Nigel","Salm",44453,1,0608449093)

Internally (in layman's speak) stores each field in the list and at the beginning of each field it stores some information about the field, it stores the actual length of the field and it stores the internal datatype as that tells it how many characters are actually used. So the record looks like this

{5,S}Nigel{4,S}Salm,{1,N}4453,{1,B}1,{2,N}0608449093

This is a simplistic representation for the purposes of illustration. Alphabetic characters consume 1 character per character, numerics will only use as many characters as are required to store the number according to 8 bit characters

As I say, this is a simplistic explanation for exactly what is happening  at the disk level and I'm sure the ISC database experts would be able to explain it far better than I can but for the purposes of illustration this will suffice.

The next thing we need to take into account is a the binary tree structure of the cache.dat file itself. The cache.dat file stores a list of globals in the database. Each global is represented by a b-Tree structure and the the cache.dat file knows that global ^Nigel starts at block 12567

beneath that there is a layer of what we refer to as Map Blocks. The first Map block contains the block references for ^Nigel(1)-^Nigel(10), the next Map Block ^Nigel(11)-^Nigel(20) and so on. At the next map block layer the first map block tells us the block location for ^Nigel(1,0)-^Nigel(5,0) the next block at that level tells us the block location for ^Nigel(6,0)-^Nigel(10,0) and so on. Each block has a Right Link Pointer to the next logical block in the chain so when traversing a global cache can reference the block that contains the pointers for ^Nigel(1,0)-^Nigel(5,0) and the RLP (Right Link Pointer) holds the address for the next block at that level which contains ^Nigel(6,0)-^Nigel(10,0) so cache can follow those RLP's to find the block that contains the pointers for ^Nigel(7,0)

As the global grows in size the number of Map Block levels increases (depth)

Finally we get to the 'leaves' of the b-tree which are the Data Blocks where the actual record data is stored.

Using our simple example of very short little records a data block may contain say 10 records of data. So using an example fro the documentation the following global

^Data(1999) = 100
^Data(1999,1) = "January"
^Data(1999,2) = "February"
^Data(2000) = 300
^Data(2000,1) = "January"
^Data(2000,2) = "February"

is stored in one data block in the format

Data(1999):100|1:January|2:February|2000:300|1:January|2:February|...

Like all blocks in the b-tree structure the block has a RLP to the next data block that contains the next N records.

So let us assume that we have a data block that contains 10 records  and you delete a record, record 7 (for example). The data block at this point is sitting in memory and when you delete the record the data block is updated so that it now holds record1,record2,record3,record4,record5,record6,record8,record,9,record10 and the rest of the block will be filled with null characters. The block is flagged as 'dirty' which means that it has been modified and it will be written to the Write Image Journal file *WIJ) which is a file on the disk that holds a representation of all of the data blocks in play in the Global Buffer Pool in memory and a process known as the Write Daemon will write the block back to disk and flag the block in memory as clean. At some point that data block in memory will be removed as no further activity has occurred on it and the space in the global buffer pool is required for new data coming in. 

So let us assume that our 10 records completely filled the available space in the data block, essentially 100% of the available space is used. In removing record 7 we have removed 10% of the data in the block and so the data block is now only 90% full.

If you remove all records in the data block then cache will unlink that block from the b-tree structure and flag it as free. 

Lets look at a different scenario. Lets say that we want to add another record into our data block and that record is too large to fit into the available space in the block then cache will perform a block splitting action. where it grabs an empty (free) block and links it into the chain.

So in our chain we have Block A, Block B and Block C.  Our data is in Block A . Cache knows that Block D is free so it will grab Block D and move some of the data from block A and put it into Block D. It will then add the new record into Block A and it will adjust the RLP's     so that our sequence is now Block A -> Block D -> Block B -> Block C

This means that at least two of our Blocks are only partially filled and if we never try and add more data into Block A and Block D then they will remain partially empty.

At this point we might ask the question "Can I compress my global?" effectively we want to take all of the data our 4 blocks (A,D,B,C) and compress them into just 3 blocks (A,D,B).

There are global functions that will allow you to do this (and in very old versions of Cache and it's predecessor MUMPS it was quite common to do this form of compression as disk space was limited and costly. In this day and age it is hardly ever considered), There is one big disadvantage to compressing globals and that is should you add a record into a compressed block structure cache will have to do a block splitting action and if you add enough records over time cache will have had to do many such block splitting actions which, from a cache point of view are very costly due to the amount of work that is required to add a free block into the chain and shuffle the data around. Ultimately you will end up in the same position of having many data blocks that are partially filled.

So to summarise at this point, Cache only stores the actual data written into any field. Records are only as long as the data they contain. They are not as long as the class/table definition.  Cache likes to keep blocks roughly 80% filled so there is some free space to add more data to a block without necessarily having to do a block splitting action.

The next thing to consider is Fragmentation. In an ideal world an entire global b-tree structure (Global A) would occupy a contiguous set of directory, map and data blocks starting from location 1 and ending at location N and global B would occupy the next chunk of contiguous blocks from block N+1 through N+n. In reality this is seldom possible and so it is quite likely that a global may occupy random chunks of non-contiguous blocks. In the very dark ages of the 1980's this would be problematic from the point of view of the Hard Disk Platter technology available at the time where a global could be so fragmented that every block read could send the disk platter and disk head spinning and moving hectically as cache attempted to get all of the required blocks from disk into memory and so Cache technicians would spend a lot of time specifying the start position of a global and allocate a chunk of contiguous blocks from that start point based on the expected size of the global. As technology improved and we moved onto disk arrays and  Solid State Drives these issues become inconsequential.

Generally speaking the advise given to cache IT Operators is "Let cache deal with the management of the disk system, the operating file system and the operating system as every instance of Cache is highly optimised for the platform it is running on". In fact the more you try and interfere the more likely it is that you will introduce performance and integrity issues.

Then there is fragmentation at the Disk level. Many of you who work on windows will be familiar with disk fragmentation where there is not enough contiguous space on the drive to store your file in one contiguous chunk and as a result your file gets split across many chunks of the hard drive. It is for this reason that in production systems InterSystems will always advise that your cache databases be allocated to their own disk system and that they do not have to compete with files being created by a million and one other applications. You can use disk defrag tools to sort out this form of defragmentation but you will notice that these tools will not attempt to move chucks of a cache.dat file from one sector to another in order to make it one contiguous file occupying a contiguous chunk of disk blocks. Cache won't allow it as it has built up its own internal picture of where cache,dat blocks are physically located and no defrag tool can just go and move chucks of a cache.dat file around without it upsetting Cache's internal picture of where data is physically stored.

The final point to consider is whether you can shrink a cache.dat file and I suspect that that is the real question you are asking here. In real life in typical transaction based applications data comes in, gets processed, is kept in the database for a period of time and then purged. Your data will consist of static data in the form of code tables, config tables and so on. There may be a large data take on to build these tables to start with but once in place they typically don't grow very much over time. Your transaction data will initially grow your database to a certain size and it will continue growing by a predictable amount every day/week/month based on the amount of incoming traffic. If you persist your transactional data for ever then your database will grow and grow but again the growth will be predictable based on the quantity of incoming data and assuming that over time more users get added to the system and there is a predictable growth in your business then the quantity of incoming data will grow accordingly. Cache is designed to increase in configurable chunks (the default is 1Mb at a time if I remember correctly) . Again there is a formula that can be followed to calculate the optimal size for each new database expansion. If that number is to small then cache will end up expanding the database lots and lots of times in small incremental chunks. Make the number to big and you end up with a database that is way bigger than you actually need. Expanding the database is costly from Cache's point of view. Therefore you don't want cache to be constantly increasing the database size in small little chunks. When it expands it should expand iby a reasonable amount to allow for the expected growth in data for say the next day/week/month

 Here are some things to consider though:

1) How long do you need to keep transaction data?

- Some applications require data to be held for a minimum period of time ranging from 6 months through 5 years or more depending (in most cases) on what is required by law or required for future data analysis. Most applications will archive data to a data warehouse where you can use Deep See to cube it and slice and dice it and produce impressive management stats. 

- Once data has been processed and has served its usefulness then you would typically have some form of housekeeping task that will archive, transform into an operational data store and ultimately be purged from the run time production database

-- If you are using Ensemble and your Production classes contain loads of $$$TRACE (and other forms of logging) then this can create huge amounts of data in ^Ens.Util.LogD and ^Ens.Util.LogI. Make sure that log files are purged automatically after a number of days (the default is 7). Like wise the ensemble message bank

- Developers have a habit of writing debugging information into globals and then when the code goes into production they forget to take those debug statements out and at some point in the future someone will ask "What is this global ^NigelDebug?", "Why is it so big?" and because no one can remember who Nigel was  and though they can probably find the places in the code where ^NigelDebug is being created they don't want to remove those lines of code because they don't know if they will break something and cause the application to crash. Good developer teams will decide on a structured way of creating debug logs and build in a flag that says "if this is production don't create this debug log"

So strictly speaking if your housekeeping is in order and ensemble message and trace logs are purged automatically and frequently then your database size and growth over time are merely predictable mathematical calculations.

If you have the situation where a database has grown very large and someone decides that it is time to run a purge that deletes massive amounts of data then you can end up with a database that is far larger than it needs to be and that is when the question arises "How to I compress a cache.dat". The answer is that "You can't", not yet anyway. Based on everything I have said so far it tells us that we expect databases to grow and grow in a predictable manageable way. The concept of wanting to shrink a database was never really given much priority because of this expectation that databases will always grow, never shrink.

So how do you compress a database between now and that point in time when IRIS supports a database shrink function? The only way that I know and that I have done in the past is to create a brand new database,  make its initial size as large as you realistically expect it to be, make sure that the growth factor is calculated with some thought and then copy your data across. Starting with your static data. That's the easy bit. When it comes to your transaction data you have to be a bit clever. You need to set a point in time where you can track any changes to the database after that point in time. You then copy your transactional data across to the new database and then, when you have a planned system down time you copy across all of the database changes that have occurred in the period between that fixed point in time and now. Once you are happy that that process is complete and you have data integrity then you move your users and application connections over to the new database and once you are confident that all is well go and delete the original cache.dat

It was a very simple question you asked and I have probably given far more information than is strictly required to answer the question but I'm getting old and with lock down still in force I am enjoying getting some of the 30 years of knowledge of cache that I have stored in my head down onto paper (or in this case some virtual server in the ethernet of everything)

Yours

Nigel