Optimizing server performance: Port encryption & Buffer Pool settings (Test Case #3 Results sidebar)

[back to "Optimizing server performance: Port encryption & Buffer Pool settings"]

Test Case #3 Results (sidebar)
These are the results we found when testing whether different system parameters affect the time required for rebuilding views. This first table shows the overall results:

Database Size (number of records)	At Start	At End	% Growth (after rebuilding)
8,000	7,127,040	24,788,992	248%
16,000	11,878,400	47,611,904	301%
32,000	21,381,120	93,028,352	335%
64,000	40,517,632	184,811,520	356%
128,000	78,675,968	369,360,896	369%
256,000	155,009,024	746,700,800	382%

In the next few tables, we used varying base database sizes, by varying the number of records the database stored. The tables show the resulting time it took to rebuild the view, the NSF_BUFFER_POOL_SIZE (at peak), and the total amount of memory allocated to perform the operation for the task. We performed this test with the following specifications:

One CPU, 64MB, no NSF_BUFFER_POOL_SIZE set, and one database
One CPU, 64MB, no NSF_BUFFER_POOL_SIZE set, and two databases running at the same time
One CPU, 64MB, the NSF_BUFFER_POOL_SIZE set at a quarter of available memory, and one database
One CPU, 64MB, the NSF_BUFFER_POOL_SIZE set at a quarter of available memory, and two databases running at the same time
One CPU, 64MB, the NSF_BUFFER_POOL_SIZE set at half of available memory, and one database
One CPU, 128MB, no NSF_BUFFER_POOL_SIZE set, and two databases running at the same time
Two CPUs, 64MB, no NSF_BUFFER_POOL_SIZE set, and two databases running at the same time

The following table shows the effect of rebuilding views for one database (one Update task), with one CPU 64MB, and no NSF_BUFFER_POOL_SIZE specification.

Database Size (number of records)	Time to rebuild view (sec)	BufferPool.Peak (K)	Memory Allocated (MB)
8,000	129	7,142	13
16,000	269	8,352	14
32,000	580	8,352	14
64,000	1204	8,352	14
128,000	2540	8,352	14
256,000	5571	8,352	13

The following table shows the affect of rebuilding views for two same sized databases (two Update tasks), with one CPU 64MB, and no NSF_BUFFER_POOL_SIZE specification. Notice that the time it takes to rebuild the views for two databases with the same size was about double the time it took for one database. For example, you can compare the results where we didn't specify a buffer pool size (this is the default), with a 32,000 record (single) database, and with the rebuilding results of two 16,000 record databases (a total of 32,000). In this case, the time it took to complete the rebuild was almost the same, and the memory allocation was greater when we rebuilt two smaller databases at the same time.

Database Size (number of records)	Time to rebuild view (sec)	BufferPool.Peak (K)	Memory Allocated (MB)
8,000	270	9,792	16
16,000	516	12,033	13
32,000	1073	12,033	17
64,000	2194	12,033	17
128,000	4819	12,033	17

The following table shows the effect of rebuilding views for one database (one Update task), with one CPU 64MB, and the NSF_BUFFER_POOL_SIZE specification set to a quarter of available memory.

Database Size (number of records)	Time to rebuild view (sec)	BufferPool.Peak (K)	Memory Allocated (MB)
8,000	128	7,546	13
16,000	285	13,420	19
32,000	608	16,934	22
64,000	2194	16,934	23
128,000	2466	16,934	22

The following table shows the effect of rebuilding views for two same-sized databases (two Update tasks), with one CPU 64MB, and the NSF_BUFFER_POOL_SIZE specification set to a quarter of available memory. The time it took to rebuild the views for two databases with the same size, was about double the time it took for one database.

Database Size (number of records)	Time to rebuild view (sec)	BufferPool.Peak (K)	Memory Allocated (MB)
8,000	262	9,792	16
16,000	513	12,038	18
32,000	1069	12,038	17
64,000	2171	12,038	17
128,000	4933	12,038	17

The following table shows the effect of rebuilding views for one database (one Update task), with one CPU 64MB, and the NSF_BUFFER_POOL_SIZE specification set to half of available memory. The time it takes to rebuild the view was consistent with the other rebuild times. Since we specified the buffer pool size, the overall memory allocation was higher.

Database Size (number of records)	Time to rebuild view (sec)	BufferPool.Peak (K)	Memory Allocated (MB)
8,000	144	8,640	15
16,000	284	14,457	21
32,000	625	26,035	33
64,000	1465	33,753	40
128,000	2786	33,753	39
256,000	6444	33,753	39

The following table shows the effect of rebuilding views for one database (one Update task), with one CPU 128MB, and no NSF_BUFFER_POOL_SIZE specification. Notice that except for the smallest sized database, there was a decrease in performance with the extra memory. As there was extra memory, the buffer pool handling logic started with a large base pool size, and incremented in larger pieces.

Database Size (number of records)	Time to rebuild view (sec)	BufferPool.Peak (K)	Memory Allocated (MB)
8,000	186	9,488	14
16,000	259	13,651	20
32,000	546	22,579	28
64,000	1096	22,579	28
128,000	2278	22,579	28
256,000	5320	22,579	28

The following table shows the effect of rebuilding views for two databases (two Update tasks), with two CPUs 64MB, and no NSF_BUFFER_POOL_SIZE specification.

Database Size (number of records)	Time to rebuild view (sec)	BufferPool.Peak (K)	Memory Allocated (MB)
8,000	143	12,844	19
16,000	294	12,844	19
32,000	621	12,844	19
64,000	1357	12,844	18
128,000	3028	12,844	18
256,000	7683	12,844	18

It is important to know the rate, and at what point the system allocates the buffer pool, when reviewing some of the performance numbers. Where there are changes in direction for some of the performance curves, this may be due to an increase in the NSF_BUFFER_POOL_SIZE specification, which would take a load off the processor and allow it to process additional information within the allocated space.