|
|
[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. |
|