|
|
|
c-treeACE SQL statement caches provide a performance benefit by allowing the SQL engine to reuse previously parsed and optimized statements. Proper sizing of this cache can offer significant performance improvements to applications. Two independent caches are used, a static statement cache and a dynamic statement cache. c-treeACE SQL supports setting the size of these statement caches (the number of entries that can be stored) up to 16,000. Previously, the limit was 500 on Windows systems and 800 on Unix systems.
To set the static statement cache size, specify the following configuration option in ctsrvr.cfg:
SETENV DH_CACHED_STATEMENTS=n
To set the dynamic statement cache size, specify the following configuration option in ctsrvr.cfg:
SETENV DH_DYN_CACHED_STATEMENTS=n
On Windows the default size is 150 for each cache and on Unix systems the default size is 250 for each.
Performance Results
FairCom’s internal testing demonstrated that setting the static statement cache size to 1,000 dramatically improved the performance of the c-tree load test program, cttctx, on a Windows system with 16 CPUs when using PREIMG transactions and 16 connections. With the default static statement cache size of 150, the statement cache hit rate was 82% and the transaction rate was 3,700 transactions per second. With the static statement cache size set to 1,000, the statement cache hit rate was 99% and the transaction rate increased to 10,100 transactions per second (nearly three times faster).
The cttctx test program executes three SQL statements per file and operates on 23 files, for a total of 69 SQL statements. When more than one SQL thread runs concurrently, multiple copies of a statement can end up in the statement cache. This happens because while a thread is using a SQL statement it removes the statement from the cache. When running the cttctx test with 16 connections, we saw up to 16 copies of these 69 statements in the statement cache. As a result, the limit of 150 entries in the static statement cache was much lower than the total likely number of entries used by the 16 connections (which might be 69 * 8 = 552 on average).
FairCom found good performance (around 10,000 transactions per second) in the cttctx test with 16 connections when using static statement cache sizes as low as 400. Using a static statement cache size of 300 caused the test to begin with poor performance and after a few seconds it sped up to around 10,000 tps.
