When discussing possible values to set for -spin there is always some relationship between how high -spin should be set and what speed of CPU's exist in the machine being used.
There are other factors which effect what value to set for -spin.
-spin is a database startup parameter which is available with Enterprise Database license.
Certain operations within Progress are very quick in duration. For many quick duration operations it is better to use an OS construct called a latch instead of the standard OS mechanism called a semaphore. When Progress uses -spin,
Latches are uses to prevent multiple users from making certain modifications simultaneously with other users.
When a user requests a latch that is in use by another user or process Progress will request the latch upto the count of spin. Each attempt to get the latch takes one CPU cycle.
If the latch is not available (not released by the other user / process) we will keep requesting up to the limit of -spin and then we will wait (nap) a certain number of milliseconds (this is governed by an internal algorithm which progressively increases the duration of the nap if a user exhausts the spin count without obtaining the latch) and repeat this loop until we obtain the latch.
The speed of a CPU is measured in Megahertz or Gigahertz. Either millions or billions of operations per second.
- -spin typically uses one CPU clock cycles for each spin.
- If the system is a 500 Mhz system then it can do 500 millions operations per second.
- If the system is a 1 Ghz system then it can do 1 billion operations per second.
If Progress is set with a -spin of 2000 this indicates that when a task is requested we will ask the CPU up to 2000 times usually to acquire a latch. Each user attempting to get a latch (the same latch or different latch) operates in this fashion. This is so small as to be nearly meaningless to the CPU but with a high enough number of users it can cumulatively use more and more of available CPU.
When considering a multiprocess environment, eg. many clients, each client performing operations will cumulatively add to the load of the system.
Therefore if 50 users are each working in the database and the database is set with -spin 5000, each of those users could request the CPU up to the limit of spin for certain operations (typically acquiring a latch).
This might use as much as 250000 clock cycles if none of the clients can get the latch or requested resource.
If either the value for -spin were higher or the user count was higher this might lead to delays if the maximum capacity of the CPU availability for the machine were reached.
The -spin can be set on a single processor system but should only be set with a value of 1. This will allow the database to use the -spin algorithm and under some conditions can improve performance.
For systems with processors 200 Mhz or higher it is typically expected that a multiprocessor system should be able to handle a -spin value of 5000 per CPU.
There is no hard or fast rule for what to set -spin.
By reviewing Promon->R&D->Option 3->Option 1
Specifically the latch wait timeouts should be reviewed.
- If there is available CPU on the system and the database was started with at least -spin 1 then -spin should be tuned up to decrease latch wait timeouts.
The CPU availability on the machine should be monitored closely during this tuning to make sure the available CPU doesn't drop below 3-5%. The machine should always have some available CPU. If the value of available CPU falls below the 3-5% threshold then -spin should be decreased.
- promon->R&D->Option 4->Option 4 can be used to modify the value of -spin online, if it was previously set when the database was started multi-user.
