Help!! Moved from 1K to 8K blocksize, now database pauses for minutes at a time

[daveprice74]

(The 8.3A nightmare continues...)

So we finally get this 8K database (matching our 8k OS blocksize) loaded, everything seems great (i.e. much faster than 1K)... but now and then we're getting a very nasty pause. Database just hangs, not responding, acts just like an AI/BI stall except there's no message in the log and eventually it just starts going again after 1-20 minutes of pausing.

Does this sound like -B? We were going 45000 on the main, which at 8K obviously is more than the 225000 we had on for 1K earlier, but maybe not enough? Do we need more APWs? More bibufs/aibufs? We moved -B to 65000 (edging closer the 1.75G limit for all 32-bit apps combined on 11.11 64-bit), we bumped aibufs and bibufs from 500 to 2000, and doubled APWs from 4 to 8. Fingers crossed right now, seems better atm.

It's almost like this new 8K database is too fast for... something, and it has to wait while something else catches up. But I don't know what.

We can roll back to our 1K database if we have to (it's still out there), but that's going to be moderately painful too.

Thanks in advance for any help from the always-knowledgable ProgressTalk gurus. (And sorry for hogging all the advice today! I think this is in the running for the worst day of my professional life.)

Some promon performance screens (this is AFTER the adjustments, so maybe not relevant, but fwiw...):

09/03/12 Activity: Performance Indicators
23:18:03 09/03/12 22:18 to 09/03/12 23:18 (59 min 53 sec)

Total Per Min Per Sec Per Tx

Commits 629293 10508 175.14 1.00
Undos 0 0 0.00 0.00
Index operations 169712 2834 47.23 0.26
Record operations 339268228 644929 10748.82 61.37
Total o/s i/o 1278546 21350 355.84 2.03
Total o/s reads 1165602 19464 324.40 1.85
Total o/s writes 112944 1886 31.43 0.17
Background o/s writes 112752 1883 31.38 0.17
Partial log writes 33080 552 9.20 0.05
Database extends 0 0 0.00 0.00
Total waits 36037 601 10.02 0.05
Lock waits 0 0 0.00 0.00
Resource waits 36037 601 10.02 0.05
Latch timeouts 122715 2049 34.15 0.19

Buffer pool hit rate: 2 %



09/03/12 Activity: Summary
23:19:11 09/03/12 22:18 to 09/03/12 23:18 (59 min 53 sec)

Event Total Per Sec |Event Total Per Sec

Commits 629293 175.1 |DB Reads 1163272 323.7
Undos 0 0.0 |DB Writes 71537 19.9
Record Reads 338754135 94281.6 |BI Reads 928 0.2
Record Updates 351694 97.8 |BI Writes 24705 6.8
Record Creates 81661 22.7 |AI Writes 16674 4.6
Record Deletes 80738 22.4 |Checkpoints 33 0.0
Record Locks 14844164 4131.4 |Flushed at chkpt 74 0.0
Record Waits 0 0.0

Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 99 % Writes by BIW 32 % Writes by AIW 99 %
DB Size: 67107840 K BI Size: 1572848 K AI Size: 132200 K
Empty blocks:3521508 Free blocks: 0 RM chain: 2
Buffer Hits 2 % Active trans: 4

0 Servers, 15 Clients (15 Local, 0 Remote, 4 Batch), 8 Apws


UPDATE: Parms:

# Startup Parameters for mfgpro85e database
-aibufs 2000
-aistall
-bibufs 2000
-bithold 999
-bistall
-B 65000
-Bt 1000
-c 250
-cpcoll basic
-cpinternal iso8859-1
-cprcodein iso8859-1
-cpstream ibm850
-D 500
-H hpqadprd
-inp 5000
-L 720000
-Ma 4
-Mi 3
-mmax 3500
-Mn 42
-Mf 30
-n 300
-nb 255
-N TCP
-s 180
-TB 31
-TM 31
-yy 1930

other info:

DB Blocks: 65,535.00 MB
DB HWM: 38,023.78 MB
Free DB Blocks Below DB HWM: 0.00 MB
Total Free Blocks: 27,511.22 MB
Total DB Size Used: 38,023.78 MB
Percent Used: 58.02057%
Total RM Blocks w/Free Space: 0.02 MB

BI HWM: 24.00 MB Total AI File Size: 358.25 MB
AI HWM: 263.30 MB Total DB File Size: 67,430.27 MB


We also have a side database which I don't think is the problem -- the issues seem to be with the main.

 

[TomBascom]

Did you forget to change the bi cluster size?

 

[TomBascom]

How much RAM does the server have? Your new -B is 1.5x the RAM of the old so it might be that you are swapping. RAM would need to be awfully tight - but this is, apparently, an ancient obsolete and ridiculous system so I guess it is possible...

 

[daveprice74]

Thanks Tom -- we have 20G of RAM, we actually can't even use all of it because of the issue with quadrants in HP-UX 11.11 that limits us to using 1.75G for shared. GLANCE says total mem usage never over 50%.

I will check the BI cluster size, thanks.

It's been okay since we upped the parameters/APWs last night -- got through the cost rollups (where it seemed to stall the first time) and MRP last night, which are the big processing hogs. The missed Autosys jobs all want to start at once when we exit an outage, might have been too much for the db after 12 hours of piling up. Waiting to hit our peak number of users in the next hour or so to see how that goes.

UPDATE: BI cluster size is 4K, block size is 16K. Should cluster be 8K? More? I suspect this is the issue, because the stall happened during a period with a lot of BI activity (cost roll-ups).

20:25:28 proutil -C truncate bi session begin for on /dev/pts/0. (451)
20:25:29 BI File Threshold size (-bithold): 0. (6550)
20:26:29 Before-image cluster size set to 4096 kb. (1620)
20:26:29 Before-image block size set to 16 kb (16384 bytes). (3781)
20:26:29 .bi file truncated. (123)
20:26:30 proutil -C truncate bi session end. (334)

Oddly I could not find "cluster size" in my database admin manual, but I think I see how to set it.


http://dba.fyicenter.com/Progress-D...Guide/321_Increasing_the_BI_Cluster_Size.html

proutil db-name -C truncate bi -bi size

Enter the following command:
For size, specify the new cluster size in kilobytes. The number must be a multiple of 16
in the range 16 to 262128 (16K*256MB). The default cluster size is 512K. Cluster sizes
from 512 to 16384 are common.

 

[TomBascom]

I'd go even higher. Back in the v8 days I considered 16384 to be the minimum useful setting.

BTW -- bi cluster size is reported in different ways in different versions and the various utilities & log files. Usually it is in KB units -- but sometimes in bytes. I'm talking about the value provided to proutil.

One other thing that people sometimes manage to forget and which I do not see in your startup params... -spin. Since this is v8 try -spin 10000

I notice that you aren't talking about the disk subsystem. Is that out of guilt?

You do realize that your phone probably has more processing power than whatever clunky POS you're running HPUX 11.11 on has?

 

[daveprice74]

Thanks, we do have -spin though it doesn't show up here (that one was a recommendation from you or Rob that saved us a while back -- I'm really in your debt here!). It's in the startup script, I should really get that in the .pf for consistency.

Disks are very new and fast (including dynamically allocated SSD cache) under a SAN, they upgraded those a little while back. We used to make Glance scream "RED ALERT!!" about 8 hours a day, now it barely ever yawns.

Haha, my phone is almost as old as the system, but point taken. Unfortunately 11.11 is where RISC ended, so according the UNIX guys it's the end of the line unless we upgrade everything. Fortunately our 4 processors rarely max out.

 

[Rob Fitzpatrick]

Disks are very new and fast (including dynamically allocated SSD cache) under a SAN, they upgraded those a little while back.

Do you know what RAID level is implemented on the SAN for the LUNs where your databases reside?

BI file
You have a BIW and 8 APWs. Your stats for one hour show one checkpoint about every two minutes, and some buffers flushed but not many. No huge problems there. Is this sample period a time of high transaction activity?

The stat that stands out for me is the 2000 AI and BI buffers. I remember hearing Gus say that you want your BI buffers to be less than the size of a cluster. With 16 KB blocks you have 32 MB of BI buffers, whereas your cluster size is much smaller at 4 MB. As Tom said, increase your BI cluster size. And after doing that you may want to experiment with a smaller number of BI buffers and see if it impacts the freezing issue. Look at promon empty buffer waits (if your version has that stat) to see if it remains at or near zero.

Buffer pool/shared memory
Your -B (for this database only) is about 0.5 GB (65,000 * 8192 bytes). This is most of shared memory, but not all of it; especially when you have a very large lock table (720,000 records). Not knowing what else is consuming memory (clients, servers, broker, other database, etc.) it's hard to say if you can increase -B further or not.

I don't know what to say about the ratio of logical to physical reads. I've never seen a value like 2% for buffer hits before, but then I never used 8.3 so it may not even be calculated correctly. That said, your buffer pool is quite small in relation to the size of your database. I don't know how you're going to address that short of going to a 64-bit release.

And do you really need that large a lock table?

Client/server
You have:
-n 300
-Mn 42
-Ma 4
-Mi 3

I don't know if your remote clients have response-time issues, but these values don't look ideal. I'd get rid of the -Mi 3; the default of 1 (assuming it was also default in the 8.3A days) should provide better round-robin distribution of clients to servers.

Finally, your server pf file, if that's what we see above, contains some client-only parameters. Not harmful, but they don't belong there.

 

[daveprice74]

Thanks Rob. We actually bumped up the buffers from ~500 in hopes it would resolve the issue with hanging (flailing).

Thanks for the cluster advice, I think you guys have nailed it. I found some more supporting evidence, this locking up during checkpointing they describe sounds exactly like what we experienced during a period of high BI writes:

http://www.westnet.com/~gsmith//content/pperform.htm#TofC64

Changing BI Cluster and block sizeWhen adding new transactions, those transactions are saved in the BI file until they hit a certain size, called the BI cluster size. At the same time, any database updates resulting from these transactions are applied to database buffer cache (the one allocated by -B that reads are also cached in). When the cluster size is reached and the cluster is full, Progress must allocated a new BI cluster to be able to store new transactions. Because of this, any outstanding database updates stored in the buffer cache are first applied to the database itself, so that if even if a problem should occur and the new cluster can't be allocated the database won't lose any of the existing transactions (running APWs will help keep things continuously flushing to the database file and reduce the length of this pause). Then a new BI cluster needs to get allocated. If you're lucky, the writing of database blocks will have finished all the transactions in one cluster of the BI file and that one will get reused. Otherwise, a new cluster gets allocated and initialized for use (possibly increasing the size of the BI file itself, if no free clusters are available). This entire process of flushing and new cluster formatting is referred to as a checkpoint, and can take a good chunk of time--time during which your database is frozen. Because of this potentially lengthy freeze, correctly managing checkpoints is one of the most important factors in getting good performance.

Because of the method used, the easiest way to keep checkpoints from happening as frequently is to increase the size of the BI cluster size. That way the overhead of initializing the new cluster doesn't happen as much. But be warned that, although not as frequent, the new cluster initialization will take longer because there's more data to initialize. The BI file will also take up more space, and crash recovery will take longer since all active but uncompleted transactions in the current BI cluster need to be scanned after the database crashes to back them out (so if the cluster is bigger, that list is correspondingly longer). Figuring out what size is best for your database and application is best done by watching the checkpointing statistics that promon generates when your system is under a heavy transaction load; having the cluster too small or too big each create their own problems (in general, however, the default you get is probably too small).

With V6 and V7, you specify the cluster size in KB with a line like this:
proutil db -C truncate bi -bi size
The size parameter can range from 16 (16KB) up to 262128 (256MB) and must be a multiple of 16. The default varies depending on your Progress version; it was 16 in 6.2, and is up to 128 in V7. You can find out what the value is on your system with promon, or you can use the fact that empty databases start with 4 clusters in them and figure it out that way; that's why an empty database under V6 is 64K and under V7 it is 512K. Progress recommends the V7 default of 128KB should be good for databases up to 100MB. Between 100MB and 500MB, they recommend 512KB. Databases over 500MB should get a cluster size over 1MB, but details on whether you need to go higher than that are very system specific.

We don't actually have any remote clients, all clients are Telnet sessions. Our services file contains amusing references to databases last extant in 1997, and our side database has not used -S for eleven years.

As you can see, the parms are a bit of a neglected kludge, so thanks for your help. 720K is probably excessive for the lock table but I haven't been able to persuade them to drop it.

 

[Rob Fitzpatrick]

The lock table high water mark is your evidence to support resizing the lock table. If your DB has been up for a while, use the HWM to determine whether or not the default value is sufficient for you. You will define "a while", based on whether the application has been through at least one typical cycle of activity (batch runs, queries, month-end jobs, reporting jobs, etc.

 

[TomBascom]

You really probably don't need even 100 bibufs and aibufs.

-Mf 30 is also kind of crazy. You should basically never touch that, it doesn't help anything.

2% hit ratios are quite common with v8 -- it is a calculation bug. As I recall it didn't finally go away until 10.something.

You're probably paying more in maintenance (or even just raw electricity) for that old clunker than it would cost to replace it with a Linux box that is 10x faster...