Concurrent write causes huge file size

[3Dmondo]

Hello,

In my application I need to (concurrently) access as quick as possible to byte arrays stored on disk.
I have a multi threaded method where each thread preform read write access to its own byte array, one array per thread. The method is executed in a loop; at each iteration I need to read the previous state and save the next one. The read operation is performed at the begin of the method to initialize it and the write is at the end, to store the iteration state. The length of each byte array may change from one iteration to the other.

To satisfy these specifications I use an esent database where each byte array is stored in a different table having one row and one column.

Everything works well on my development machine, with relatively low degree of parallelism and fast disk (SSD), but, as often happens, the problem arisen in the production environment, a virtualized machine with 12 cores and possibly slower, but still SSD, disk.

The size of the .edb file in the production machine reached ~50GB after few iterations, while in the dev environment it never exceeded the 15GB.

I also reproduced the problem on my dev environment writing a program that just write byte arrays of random length, filled with random data thus maximizing the concurrent write accesses.
I temporarily solved the problem with a Lock statement around the write operation, but I'm looking for a better solution (e.g. using a number of databases equal to the degree of parallelism).

The following is a snippet of my test application with full parallel and locked versions:

string parallel = "parallel.csv";
using (StreamWriter outfile = new StreamWriter(parallel)){
    for (int i = 0; i < iterations; i++){
        Parallel.For(0, tables, j => {
            byte[] buffer;
            Random r = rr[j]; 
            long size = r.Next(minBytes, maxBytes);
            buffer = new byte[size];
            r.NextBytes(buffer);
            esentDb.Write(j, buffer); //save the data on disk
        });
        outfile.WriteLine("{0}",  new FileInfo(parallel).Length);
    }
}
string locked = "locked.csv";
using (StreamWriter outfile = new StreamWriter(locked))
{
    for (int i = 0; i < iterations; i++)
    {
        Parallel.For(0, tables, j => {
            byte[] buffer;
            Random r = rr[j];
            long size = r.Next(minBytes, maxBytes);
            buffer = new byte[size];
            r.NextBytes(buffer);
            lock (lockObject){
                esentDb.Write(j, buffer); //save the data on disk
            }
        });
        outfile.WriteLine("{0}", new FileInfo(locked).Length);
    }
}

with this example I optained a .edb file size of ~1.5GB in thhe full parallel case and ~300MB in the locked one, both after 100 iterations

[machish]

What does esentDb.Write() look like? What is your primary index? What other indices do you have?

Go in to your log directory, and do esentutl -ml -v edb00abcd.log > abcd.txt (dump one of the recent logs). On Win10 and above you'll see every log record dumped. On Win8.1 and earlier you'll just see the summary. Scroll to the bottom and look for the various types of records, and how large they are.

My guess is that the Split2 records will be taking up a lot of the space in the log record.
Again, I'll take a guess that your primary index is on that integer j, and that the density of the primary index was set high (like 100%). This is controlled via either the ulDensity parameter, or the JET_SPACEHINTS structure when creating the index or table.
If all of your insertions are done sequentially, then everything will be an Append and fairly efficient.
But if you insert out of order, then ESENT will fill up the page to the specified density. A later insertion in the middle of a page won't fit, and then ESENT will have to "split" the page, moving about half of the data to a new page, and then the new record will fit on one of these new pages. A page split affects the old page, the new page, the parent page, and one of the Left or Right pages (the bottom level of the B+-tree), and for crash-consistency's sake, the moved data gets recorded in the log files.

The easy solutions are only for newer databases, since you can't change the density on an existing table/index, only for newly created tables. You can set the density to 80 or 90%, and you should also try playing around with changing the database page size (default is 4k, up to 32k is now supported. XP only supported 8k. I forget when 32k page support was added -- Win7?).

Also, if you have an auto-inc column, do you happen to read it via JetRetrieveColumn( ... RetrieveFromCopyBuffer)? That can make the multi-threaded insertions slightly slower because the database engine has to allocate the number sooner, and that can affect some data layout.

I also had trouble with a multi-threaded program causing lots of splits, and the above did help somewhat, but didn't fix it entirely.

Maybe @michaelthorp has some more ideas for us? :)

[3Dmondo]

Hi @machish,

Thanks for your reply, it helped me to understand some of the esent configurations.
Unfortunately neither reducing the density nor increasing the page size helped.

To answer your question:

What does esentDb.Write() look like?

By default I have a different table for each j index containing just one row and one column, at each write I simply replace the whole content of the j-th table.

What is your primary index? What other indices do you have?

Given the above configuration I basically have no indexes except fore the table name.

do you happen to read it via JetRetrieveColumn(... RetrieveFromCopyBuffer)?

I don't.

I also have a different configuration where I have a single table, and one row for each item. In this case I have two columns: the Long j, and the JET_coltyp.LongBinary data where j is my only index.
I found the same problem of file size for both configurations.

My guess is that the Split2 records will be taking up a lot of the space in the log record.

You are right, I repeated the test described in my previous comment, and I found a big difference in the Split2 records between the full parallel and the locked scenario (i.e. 4 vs. 2000+ records), but this happens only in my default configuration (the one with many tables) while in the other case (one table, many rows) I found the opposite: 36 Split2 records for the full parallel and 2000+ for the locked case.

In my test I ran 200 iterations, writing 1000 items at each iteration (j in [1, 1000]) each item with a size ranging (randomly) from 128 to 65536 bytes.

I obtained the smallest file size with the second configuration (one table, many rows) and the lock strategy (~40 MB) while in the many tables case I obtained a file size of ~200MB (again with the lock). Running in full parallel I get files of 4+GB in both cases.

Reducing the density up to 50 didn't help, and increasing the page size resulted in bigger files.

I ran my tests on Win10 and the application is not expected to run on win versions < 8 - server 2012.

[michaelthorp]

@3Dmondo - how do you do the writes? Do you do an InsertCopyDeleteOriginal on the byte array when updating it, by any chance? We did have an issue (fixed in Windows 10 19H1 I believe, where in certain conditions upon doing an InsertCopyDeleteOriginal, the original copy did not get deleted and would get orphaned. You can check for this by taking one of your databases (one of the bloated ones in production) and running 'esentutl /g ' (note: the database needs to be in CleanShutdown state. If you do dirty term's you'll need to run recovery first - 'esentutl /r '). After running esentutl /g there will be a INTEG.raw file in that directory which you can look at and check for anything like: "WARNING: orphaned LV").