Hi,
NoOfFragmentLogFiles might be needed an increase as a consequence of other
parameter changes such as TimeBetweenLocalCheckpoints.
TimeBetweenLocalCheckpoints can be used to increase time between LCP's.
The parameters below will however affect performance during the LCP's and
the slower one writes to disk the more log files are needed since they need to
take care of the time it takes to run 3 LCP's.
[NDBD]NoOfDiskPagesToDiskAfterRestartTUP
When executing a local checkpoint the algorithm flushes all data pages to disk. Merely doing as quickly as possible without any moderation is likely to impose excessive loads on processors, networks, and disks. To control the write speed, this parameter specifies how many pages per 100 milliseconds are to be written. In this context, a "page" is defined as 8KB; thus, this parameter is specified in units of 80KB per second. Therefore, setting NoOfDiskPagesToDiskAfterRestartTUP to a value of 20 entails writing 1.6MB in data pages to disk each second during a local checkpoint. This value includes the writing of UNDO log records for data pages; that is, this parameter handles the limitation of writes from data memory. UNDO log records for index pages are handled by the parameter NoOfDiskPagesToDiskAfterRestartACC. (See the entry for IndexMemory for information about index pages.)
In short, this parameter specifies how quickly local checkpoints will be executed, and operates in conjunction with NoOfFragmentLogFiles, DataMemory, and IndexMemory.
The default value is 40 (3.2MB of data pages per second).
[NDBD]NoOfDiskPagesToDiskAfterRestartACC
This parameter uses the same units as NoOfDiskPagesToDiskAfterRestartTUP and acts in a similar fashion, but limits the speed of writing index pages from index memory.
The default value of this parameter is 20 index memory pages per second (1.6MB per second).
Rgrds Mikael
Adam D wrote:
> Ive been looking at tuning a few values in the
> cluster; Ive found two which look to be 'the ones'
> which will allow the cluster to carry out many
> querys, many updates etc. However not sure as to
> how to configure them.
>
> 2005-09-26 15:45:56 INFO -- Node 11: Local
> checkpoint 822 started. Keep GCI = 821860 oldest
> restorable GCI = 821967
> 2005-09-26 15:57:37 INFO -- Node 11: Local
> checkpoint 823 started. Keep GCI = 822181 oldest
> restorable GCI = 822293
>
>
> NoOfFragmentLogFiles = 16
> Of which defaults to 8, so I thought I would
> double this value. To provide 1gb of logfiles.
>
> TimeBetweenLocalCheckpoints = 22
> I was a little unsure of this one, as it mentions
> busy clusters will need to checkpoint right after
> another completes, and setting this to 6 or below
> would make this happen, however from what I read
> it seems that setting this higher would allow more
> write operations.
>
>
> I was also unable to change some of these values
> with a rolling restart of the nodes, So Im not
> sure if this one will require a full cluster
> backup restore or a rolling initial for each
> node.
>
> Can someone provide some more clarification here
> as to how to tune these values. Or if there are
> other values that I should look at to avoid issue
> when the cluster gets 'hit hard' with load.
>
>
> Chopped up config;
> MaxNoOfConcurrentOperations=170000
> MaxNoOfConcurrentTransactions=150000
> MaxNoOfLocalOperations=170000
> MaxNoOfAttributes = 500000
> MaxNoOfOrderedIndexes=500
> MaxNoOfUniqueHashIndexes=500
> ##MaxNoOfFiredTriggers Default is 4000
> MaxNoOfFiredTriggers=2000
> MaxNoOfTables=350
>
> TimeBetweenLocalCheckpoints = 22
> TimeBetweenGlobalCheckpoints = 1500
>
>
> Id=2
> hostname=172.16.250.1
> BatchSize=256
> MaxScanBatchSize=2M
>
>
> Adam
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com