Should We Adjust Spin Frequency (-spin)?

Server Information
Version: SUSE Linux Enterprise Server 11 (i586)
Processor: Single
Max Speed: 30000 MHz

Database Information
Size: 2.2 GB
Current Spin: 10000
Average Number of Users: 100
Average Latch Timeout: 35/min

Given this information, could our performance improve if we adjusted -spin? (We can switch to a multiprocessor machine if it will help.)

I doubt you will see any meaningful difference with that few timeouts per second (less than 1) and one processor. Switching to multiple processes is much more likely to help than tweaking spin.

Take a sample for a few minutes with promon, R&D, 2, 1 (post the screenshot) and then go back to the main R&D menu and type debghb,6,11 to get the details on which latches (should be two screens worth).

If you don't have ProTop (http://www.dbappraise.com/protop.html) then download it. It will help you diagnose the issues more easily.

Do you know if the database is Enterprise or Workgroup also what version number you are using ?

The database is Type II and uses Enterprise and OpenEdge 10.2b SP1.

By the way, we don't have a specific performance problem. We're working at an acceptable speed, and no one has complained; I just want the system to run at its best.

I have had a customer try to run DBs in a one-core VM. I advise against it. There are several different threads that could be compute-bound at some point in time (e.g. various proutil commands, probkup, a long-running ABL or SQL query, etc.) and when that happens, application performance will be affected.

It's a small database but that doesn't mean you can't have demand for several resources at once and the need for concurrency, especially with 100 users. If you can add cores, you should.

Some suggestions based on the data so far....

You aren't running APWs, an AIW or a BIW. Start one of each. If your write activity is consistently low it may not make much of a difference but I suspect you will have peak write times.

Either you aren't using -B2 or it doesn't have the right objects stored in it. Set up the alternate buffer pool (-B2) and move your commonly used lookup tables into the alternate pool. This will help eliminate some portion of the physical DB reads and LRU latches.

Look at the _TableStat and _IndexStat VSTs to determine if you have indexing/coding issues with some of your tables.

Add cores... more cores equals faster performance with multiple users.

Are you really on Service Pack 1?

I've haven't seen that in the wild

Service Pack 7 (trusted) and 8 (latest) would make for a more solid reliable experience - but test first!

Following Mad's advice about the writers check your BI and AI buffers set to decent values 200 and 300 would be a good start i.e. AI = BI * 1.5. This should help with speed for CRUD operations.

Also, not that I come across many single core servers anymore, the old advice was not to set spin for single cores.

We ran SP01 internally, when it was current, and got onto SP02 ASAP. SP01 had a nasty probkup bug where each successive online backup would take one _Connect slot and not give it back when it finished. When all of the connection slots are used, no one can connect to the DB until you restart it. That bug was fixed in SP02. A related bug that messed up the totals in _License and in dbname.lic (if you care about that) was fixed in SP05.

On 10.2B, SP08 is where you want to be.

If you don't actually have a problem -spin is a strange place to start looking.

Well, we don't have a problem in the sense that no one's complained. Maybe we don't really have a problem; then again, our users are a pretty patient bunch.

Thanks, everyone. If nothing else, we can add cores to our server CPU.