This project has moved and is read-only. For the latest updates, please go here.

Esent cache and seeking for random text

Jan 23, 2013 at 11:23 AM

We have encountered an issue in which I think is related to a way how does the cache in Esent work. The problem is constantly rising seek time if we are doing more and more inserts. In our Add method the Api.TrySeek is used to detect if an inserted item is the new one or it already exists to either insert or just update. The seek is done by using key column which is JET_coltyp.LongText. The performance of the seeking is the problem ONLY if the key value is _random string_ (hashes). If the stored keys are sequential (e.g. 'item/1' 'item/2' or number sequence: '1', '2', '3' ... ) then a cache mechanism of Esent ensures fast seeking, right? Otherwise it cannot build the cache properly what means a lot of I/O operations for every seeking. Please confirm that my understanding is correct. If yes are there any options to optimize seeking in that case?

PS. How does Esent builds the cache internally? (b-trees ?)

Jan 23, 2013 at 11:40 AM

You are correct. There is caching done by Esent. The sizing is done automatically by default. You can override the automatic sizing by SystemParameters.CacheSizeMin and SystemParameters.CacheSizeMax. Look at the <summary> comments on those parameters. You can see what the current cache size is by looking at Perfmon counters. Look at the 'Database' category.

If you are doing random inserts, then you will probably want to specify a ulDensity for the indices of less than 100% (e.g. 80 or 90%). If you specify 100% then insertions will result in very-tightly-packed index pages. The density sounds great, but that means an insertion in the middle of a page will require a page split, rather than just a shuffling around of the data.

Esent uses b-trees for the sorting of all of the data on-disk. The in-memory cache is exactly the same data structure.

Does that make sense?

-martin

Jan 23, 2013 at 12:09 PM

Thank you Martin. That confirms my suppositions.

Jan 25, 2013 at 10:03 AM

Martin,

All of our tables are using density of 80%.

We use the CacheSizeMax to 1/4 of the physical memory (on the test machine, that is 2 GB dedicated to the cache).

Anything else that we can try?

Jan 25, 2013 at 11:02 PM

Unfortunately there are a TON of dials to tweak to get the absolute maximum performance.

Try the perf counters (Open perfmon, look under 'Database') and look to see how much of the database cache is in use.

Another possibility is that you're running in to log buffer stalls. Again, there is a perf counter for that. The solution would be to increase the log buffer size.

But then there are other times where we get hung up trying to update the checkpoint file.

And then there are others when we're simply saturating the IO subsystem. ;) But exhausting the CacheSizeMax is a more likely culprit.

In fact ESE goes crazy with perf counters. You can see a LOT more perf counter by setting 'Squeaky Lobster'. Follow the directions at http://support.microsoft.com/kb/556030, but use 'ESENT\Performance' instead of 'ESE\Performance'. They are hidden by default to reduce clutter, not because they're confidential.

-martin