Help! One database suddenly slow, other fine

[dp74]

I did the test both ways again just to be sure I wasn't fooling myself, and -spinlock 10000 totally eliminates the performance issue in Test.

Just for the hell of it, I ran the 34-process spawner 4 more times in one test, checking performance each time, so eventually I had 170 processes running at once. It took a little longer for screens to come back (we were beating the 2 Test CPUs pretty hard) but the DB performance issue was still not seen.

Then the Unix guys came over and asked me what the hell I was doing...

 

[TomBascom]

On a multi-cpu system changing -spin from 0 to almost anything is as close to a silver bullet as you will ever find.

BTW, the internet never forgets bad advice. There are a great many legends about formulas for setting -spin. They are all crap. The current recommendation, which is better than most and not worse than any, for setting -spin = integer( pi * birthyear ). Rich, Gus, Dan, Paul, Adam and myself determined this to be true after a lengthy session of intense beer drinking at PUG Challenge. It works because it always results in a 4 digit number.

Lastly, with version 8 you almost certainly have a 32 bit Progress (there were 64 bit Tru 64 and HPUX ports, but it seems unlikely that you have one of those). Thus -B is limited to a maximum of around 2GB. Possibly much less depending on OS and what else is going on.

 

[TomBascom]

I wrote a utility to tell me how many records the DB can return per millisecond...

You might find this interesting: http://www.greenfieldtech.com/articles/how_fast_will_it_go.shtml

 

[Rob Fitzpatrick]

Thanks Tom. I had a "how fast will it go" scenario like that this week, so this code will come in handy. Cheers.

 

[dp74]

Just an update: with -spin, it appears we've passed our Monday acid test with flying colors. Performance has remained optimal this morning. Thanks again to Tom for that suggestion.

Possibly interesting side note: this whole database is apparently now running on solid-state disks. Users generally saw a 2x - 5x improvement when we transitioned to the new disks a few weeks back (before that we were in a near-constant disk bottleneck throughout the business day; GLANCE was screaming it about nonstop in the advisor files -- now, nothing). In addition to the increased # of users, I suspect the speed of the disks was also a factor in overloading the semaphore system and thus requiring spinlocks (because requests to fill buffers were being processed faster).

Next, I'm wondering if this version (8.3a) will let us push our BI on the main db to 16G and set the stall at 7.9G. Even with a utility that kicks people if they hold a tx for longer than 20 minutes, we got worryingly close to the current 1G stall limit the other day, so I bumped that down to 10 minutes. This has become a bigger worry with our ridiculous disk throughput, as they can fill up that 1G pretty fast. Going to give bigger BI a try in Test soon, maybe this week.

 

[cj_brandt]

version 8 has 2gb limit on bi.