Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Interbase Settings


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 0
Jeff Cope

Posts: 88
Registered: 1/27/01
Interbase Settings  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 8, 2017 9:05 AM
When selecting the following settings for a database:

Page Size
Page Buffers

The way I understand it, the best way to determine accurate page sizing is to start the with default of 4096. After data is added to the database, gstat for Index Pages. Indexes with a depth greater than 2 and average fill levels below 60% indicate you need to up the page size. Should page sizing changes be made incrementally, so the next page size would be 8192? Or is there a mathematical way to determine this based on numbers from gstat?

For page buffers, we have a customer set at 100,000. With a page size of 8192 * 100,000 = ~819mb. This server has 16 gb of ram so we're not veering into VM usage. Is the rule of thumb here to make this as big as your physical memory allows? After running gstat for indexes, we had some with a depth of 4 and average fill levels falling below 60%. To correct, we changed the page size to 16,384 and dropped page buffers down to 50,000. Not only did this drop index depth numbers and changed fill ratios to hit 80-90%, but performance improved.

Basically we are looking for best practices for determining what some of these settings should be. We have databases all over the board in terms of size so having something concrete we can use to change page size/buffers as necessary would be very useful.

Another question I have is related to the ibconfig file. Default SORTMEM_BUFFER_SIZE is 1,048,500. At some point along the way we have our default ibconfig files distributed with 10,485,000. What is the best way to determine what this number should be?

The only other change we have specific to our default ibconfig file relates to the TMP_DIRECTORY values. We have multiples setup with a specific max size on each.

TMP_DIRECTORY 2147483648 "c:\IBTemp\IBTEMP\IBTEMP_1"
TMP_DIRECTORY 2147483648 "c:\IBTemp\IBTEMP\IBTEMP_2"
TMP_DIRECTORY 2147483648 "c:\IBTemp\IBTEMP\IBTEMP_3"
TMP_DIRECTORY 2147483648 "c:\IBTemp\IBTEMP\IBTEMP_4"
TMP_DIRECTORY 2147483648 "c:\IBTemp\IBTEMP\IBTEMP_5"

Does this practice cause any performance degradation?

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02