Probkup and Performance Headaches

[Zebwen]

Happy Saturday all,

As part of our ongoing effort to improve the pitiful performance of our progress solution, we have recently had to turn our attentions to our overnight process. This set of processes perform a bunch of actions against our DB in order to get us ready for the next days trading. Over time this review process has degraded in performance to the point that if there is a particularly large number of accounts to review, or if there is a large report running against the live DB, or if there is a strong northerly wind, or if Jupiter is not in the correct alignment, then the process overruns, clashes with other timed processes and all hell breaks loose.

We have received some recommendations, and after trialling a couple of them in a test environment we have also started the process of testing them against our live environment (with backout plans in place should something go awry).

As a quick overview so you have a little background:

• Server is a VMWare VM.
• Windows Server 2003
• 12gb RAM.
• 4 Cores (2 processors x2 cores each) @ 2.99ghz
• Live DB is running on 6x SAS 15k 250gb drives in RAID 1/0 on 4gb Fibrechannel backbone.
• VM has secondary drives that are carved from the standard VM SAN allocation.

9:30pm

• Probkup online backup is scheduled. The resulting DB size is around 98.5gb.
• Backup runs to a second drive on the server (one of the VMFS drives on the standard VM SAN).
• This backup usually takes until around 2:30-3am, but can take longer (recently has slowed right down and can take until 5am, we are unsure why as yet).
• This same backup on our test server takes around 3 hours and 30 minutes. This is not a true comparison as reports and other DB accesses are always hitting our live DB, but at this time of night it shouldn't be enough to cause the difference we are seeing here.

12:01am

• Morning batch processing begins.
• The command that is called (by scheduled task) to run this process is: ""C:\program files\progress\bin\prowin32.exe" -db D:\documents -db D:\database -BL 200000 10000 -d dmy -crc -Bt 2500 -TM 31 -TB 32 -b -q -v6q -o LPT1: -ininame D:\ini\server.ini -p D:\nextday.p -Wa -wpp"
• Previous days accounts are reviewed, DB changes based on the previous days trading occur.
• This process usually completes at around 3am, but recently has been taking longer, to about 4:30am or even so long that it clashes with the next days daystart.

So, we have several issues here. The foremost is our rapidly degrading overall performance, but the critical issue right now is that this process must be completed correctly before the next days trading can begin.

In order to speed up the process, we have been looking at ways to speed up the probkup portion of the schedule, in the hopes that completing this sooner will subsequently speed up the account reviews that start after midnight (as the system won't also be running an online backup while the reviews are trying to run). After reading documents, forums and receiving some advice, we decided to try two things. The first is to add the -com switch to our probkup string, this has been successfully tested in our test environment, and the plan was to test it's effects on the live environment tonight. The second thing was to change the Default Configuration of or main DB, increasing the "Blocks in DB buffer" value from it's current 300000 to a new value of 400000, increasing the quantity of memory allocated to the DB as a whole, and hopefully increasing performance as a result. This change was put in place last night, and this is what happened:

Probkup process started, and continued running correctly, this was still running at midnight.
At 12:01am our "overnight.p" script attempted to run, but hung, no processing took place (I also received no log files telling me of failure, it just appeared not to run at all).
I manually tried to run the overnight.p file, when running manually I received a windows error popup stating: "Unable to attach shared memory Global\sharemem.e.vendor.db.live.pinsys.11, error 0. (1720)".
Unable to run anything required on the DB, I manually took the DB offline, and tried to start it back up (hoping that this would clear the cached memory. I then received: "Error in startup of database localhost:20931insysLive. Message: 07:36:48 BROKER : ** This process terminated with exit code 1. (8619) (JUNMsg024)"
At this point I rebooted the entire server, and on startup I changed the "Blocks in DB Buffer" value back to 300000 and started the DB, after which the DB was correctly running again and I was able to manually run the overnight process.

If you guys are able to help, I can provide any logs you might need. Warning in advance, I am a sysadmin, not a DB admin, and unfortunately I know very little about Progress, so I apologise in advance if I have to ask dumb questions to understand what I need to do.

Any suggestions or simply letting me know that I'm an idiot and the steps I've taken on this are stupid are greatly appreciated!

 

[TomBascom]

There is a lot of information there but, strangely, you have overlooked the most important thing to tell us -- what version of Progress is this?

And what sort of license? ("Enterprise" or "Workgroup")

It is always handy to post the startup section of your .lg file (the 50 -75 lines immediately following message "(333)").

I will guess that this is version 9 and that you have a workgroup license. Fixing those two things will do more for improving performance than anything else you can do. While you are fixing that you should also upgrade to Windows Server 2008 and make everything 64 bits so that you can increase -B successfully. You should also move everything out of the schema area and in to properly defined type 2 storage areas.

 

[Zebwen]

Apologies, I posted that information in my previous thread but should have also posted them here. The server is running on 9.1D, we know that it is horribly outdated but unfortunately we are unable to upgrade it as our Vendor will only support this version (if "support" is the right word for what they are doing), and they will also only support their solution on Server 2003, so our hands are tied. We have tried and failed on multiple occasions to get them to upgrade us to 9.1E SP4 but they steadfastly refuse to do this as apparently their software will not run on it.

Here is the info from the log file, hope this is what you were looking for!

09:33:14 BROKER  0: Multi-user session begin. (333)09:33:14 BROKER  0: Begin Physical Redo Phase at 0 . (5326)09:35:22 BROKER  0: Physical Redo Phase Completed at blk 2853 off 2421 upd 17231. (7161)
09:35:22 BROKER  0: Started for 30000 using TCP, pid 2904. (5644)
09:35:22 BROKER  0: Connecting to Admin Server on port 7835. (8836)
09:35:22 BROKER  0: Registered with Admin Server. (8846)
09:35:22 BROKER  0: PROGRESS Version 9.1D on WINNT. (4234)
09:35:22 BROKER  0: Server started by SYSTEM on CON:. (4281)
09:35:22 BROKER  0: Started using pid: 2904. (6574)
09:35:22 BROKER  0: Physical Database Name (-db): E:\vendor\db\live\pinsys. (4235)
09:35:22 BROKER  0: Database Type (-dt): PROGRESS. (4236)
09:35:22 BROKER  0: Force Access (-F): Not Enabled. (4237)
09:35:22 BROKER  0: Direct I/O (-directio): Not Enabled. (4238)
09:35:22 BROKER  0: Number of Database Buffers (-B): 300000. (4239)
09:35:22 BROKER  0: Maximum private buffers per user (-Bpmax): 64. (9422)
09:35:22 BROKER  0: Excess Shared Memory Size (-Mxs): 16465. (4240)
09:35:22 BROKER  0: The shared memory segment is not locked in memory. (10014)
09:35:22 BROKER  0: Current Size of Lock Table (-L): 32000. (4241)
09:35:22 BROKER  0: Hash Table Entries (-hash): 98407. (4242)
09:35:22 BROKER  0: Current Spin Lock Tries (-spin): 50000. (4243)
09:35:22 BROKER  0: Number of Semaphore Sets (-semsets): 1. (6526)
09:35:22 BROKER  0: Crash Recovery (-i): Enabled. (4244)
09:35:22 BROKER  0: Database Blocksize (-blocksize): 4096. (6573)
09:35:22 BROKER  0: Delay of Before-Image Flush (-Mf): 3. (4245)
09:35:22 BROKER  0: Before-Image File I/O (-r -R): Reliable. (4247)
09:35:22 BROKER  0: Before-Image Truncate Interval (-G): 60. (4249)
09:35:22 BROKER  0: Before-Image Cluster Size: 16777216. (4250)
09:35:22 BROKER  0: Before-Image Block Size: 16384. (4251)
09:35:23 BROKER  0: Number of Before-Image Buffers (-bibufs): 40. (4252)
09:35:23 BROKER  0: BI File Threshold size (-bithold): 0.0   Bytes. (9238)
09:35:23 BROKER  0: BI File Threshold Stall (-bistall): Disabled. (6552)
09:35:23 BROKER  0: After-Image Stall (-aistall): Not Enabled. (4254)
09:35:23 BROKER  0: After-Image Block Size: 16384. (4255)
09:35:23 BROKER  0: Number of After-Image Buffers (-aibufs): 40. (4256)
09:35:23 BROKER  0: Storage object cache size (-omsize): 1024 (8527)
09:35:23 BROKER  0: Maximum Number of Clients Per Server (-Ma): 4. (4257)
09:35:23 BROKER  0: Maximum Number of Servers (-Mn): 112. (4258)
09:35:23 BROKER  0: Minimum Clients Per Server (-Mi): 1. (4259)
09:35:23 BROKER  0: Maximum Number of Users (-n): 279. (4260)
09:35:23 BROKER  0: Host Name (-H): server. (4261)
09:35:23 BROKER  0: Service Name (-S): 30000. (4262)
09:35:23 BROKER  0: Network Type (-N): TCP. (4263)
09:35:23 BROKER  0: Character Set (-cpinternal): ISO8859-1. (4264)
09:35:23 BROKER  0: Parameter File: Not Enabled. (4282)
09:35:23 BROKER  0: Maximum Servers Per Broker (-Mpb): 50. (5647)
09:35:23 BROKER  0: Minimum Port for Auto Servers (-minport): 3000. (5648)
09:35:23 BROKER  0: Maximum Port for Auto Servers (-maxport): 5000. (5649)
09:35:23 BROKER  0: This broker supports 4GL server groups only. (8863)
09:35:23 BROKER  0: Large database file access has been enabled. (9426)
09:35:23 BROKER  0: Created shared memory with segment_id: 11599872 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 145817600 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 280035328 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 414253056 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 548470784 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 682688512 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 816906240 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 951123968 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 1085341696 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 1219559424 (9336)
09:35:23 SRV     1: Started on port 3000 using TCP, pid 2488. (5646)
09:35:24 SRV     1: Login usernum 389, userid user1, on comp00667. (742)
09:35:24 SRV     2: Started on port 3001 using TCP, pid 1804. (5646)
09:35:25 SRV     2: Login usernum 388, userid user2, on comp00632. (742)
09:35:25 SRV     3: Started on port 3003 using TCP, pid 2832. (5646)
09:35:26 SRV     3: Login usernum 387, userid user5, on comp00664. (742)
09:35:26 BROKER  4: Started for 30050 using TCP, pid 2808. (5644)
09:35:26 BROKER  4: This is an additional broker for this protocol. (5645)
09:35:26 BROKER  4: This broker supports SQL server groups only. (8864)
09:35:28 BIW   112: Started. (2518)
09:35:30 AIW   113: Started. (2518)
09:35:32 WDOG  114: Started. (2518)
09:35:34 APW   115: Started. (2518)
09:35:36 APW   116: Started. (2518)
09:35:38 APW   117: Started. (2518)
09:35:40 APW   118: Started. (2518)
09:35:40 SRV     2: Logout usernum 388, userid , on comp00632. (739)
09:35:49 SRV     2: Login usernum 388, userid user2, on comp00632. (742)
09:36:05 SRV     5: Started on port 3005 using TCP, pid 2420. (5646)
09:36:06 SRV     5: Login usernum 386, userid user3, on comp00626. (742)
09:36:09 SRV     1: Logout usernum 389, userid , on comp00667. (739)
09:36:15 SRV     1: Login usernum 389, userid user4, on comp00667. (742)
09:36:19 SQLSRV2 6: SQL Server 9.1D.09 started, configuration: "pinsyslive.defaultconfiguration" 
09:36:19 SQLSRV2 6: "SQL" started on port 3008, pid 3380 (0x00000d34).
09:36:19 SQLSRV2 6: Thread stack size: 1024000 (bytes).
09:36:19 SQLSRV2 6: DLC from ENVIRONMENT VARIABLE is: C:\Program Files\PROGRESS 
09:36:19 SQLSRV2 6: WRKDIR from REGISTRY is: C:\PROGRESS\WRK\ 
09:36:19 SQLSRV2 6: JDKHOME from REGISTRY is: C:\Program Files\PROGRESS\jdk 
09:36:19 SQLSRV2 6: JREHOME from REGISTRY is: C:\Program Files\PROGRESS\jre 
09:36:19 SQLSRV2 6: CLASSPATH from DEFAULT is:  
09:36:19 SQLSRV2 6: PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds
09:36:20 SRV     7: Started on port 3009 using TCP, pid 3396. (5646)
09:36:20 SQLSRV2 6: Login usernum 385, remote SQL client. (8873)
09:36:20 SQLSRV2 6: Usr 385 set name to sysprogress. (7129)
09:36:21 SRV     7: Login usernum 384, userid Admin, on comp00734. (742)
09:36:24 SRV     8: Started on port 3010 using TCP, pid 3384. (5646)

Richard

 

[Zebwen]

Apologies, I had posted the log file in my previous post but didn't think to post it here (oops!). We are running the server on 9.1D. We are aware that this is horribly outdated but have requested on multiple occasions that the Vendor update to 9.1E SP4 but unfortunately they steadfastly refuse, stating that their software will not function on that platform and so will be totally unsupported by them (if "support" is what you can call what we are getting from them). They have also stated that they will only support the software running on Server 2003, so our hands are well and truly tied. We have got to the end of our rope trying to coax our Vendor in to helping us out, and so are trying to get to the bottom of things ourselves :/

09:33:14 BROKER  0: Multi-user session begin. (333)09:33:14 BROKER  0: Begin Physical Redo Phase at 0 . (5326)
09:35:22 BROKER  0: Physical Redo Phase Completed at blk 2853 off 2421 upd 17231. (7161)
09:35:22 BROKER  0: Started for 30000 using TCP, pid 2904. (5644)
09:35:22 BROKER  0: Connecting to Admin Server on port 7835. (8836)
09:35:22 BROKER  0: Registered with Admin Server. (8846)
09:35:22 BROKER  0: PROGRESS Version 9.1D on WINNT. (4234)
09:35:22 BROKER  0: Server started by SYSTEM on CON:. (4281)
09:35:22 BROKER  0: Started using pid: 2904. (6574)
09:35:22 BROKER  0: Physical Database Name (-db): E:\database. (4235)
09:35:22 BROKER  0: Database Type (-dt): PROGRESS. (4236)
09:35:22 BROKER  0: Force Access (-F): Not Enabled. (4237)
09:35:22 BROKER  0: Direct I/O (-directio): Not Enabled. (4238)
09:35:22 BROKER  0: Number of Database Buffers (-B): 300000. (4239)
09:35:22 BROKER  0: Maximum private buffers per user (-Bpmax): 64. (9422)
09:35:22 BROKER  0: Excess Shared Memory Size (-Mxs): 16465. (4240)
09:35:22 BROKER  0: The shared memory segment is not locked in memory. (10014)
09:35:22 BROKER  0: Current Size of Lock Table (-L): 32000. (4241)
09:35:22 BROKER  0: Hash Table Entries (-hash): 98407. (4242)
09:35:22 BROKER  0: Current Spin Lock Tries (-spin): 50000. (4243)
09:35:22 BROKER  0: Number of Semaphore Sets (-semsets): 1. (6526)
09:35:22 BROKER  0: Crash Recovery (-i): Enabled. (4244)
09:35:22 BROKER  0: Database Blocksize (-blocksize): 4096. (6573)
09:35:22 BROKER  0: Delay of Before-Image Flush (-Mf): 3. (4245)
09:35:22 BROKER  0: Before-Image File I/O (-r -R): Reliable. (4247)
09:35:22 BROKER  0: Before-Image Truncate Interval (-G): 60. (4249)
09:35:22 BROKER  0: Before-Image Cluster Size: 16777216. (4250)
09:35:22 BROKER  0: Before-Image Block Size: 16384. (4251)
09:35:23 BROKER  0: Number of Before-Image Buffers (-bibufs): 40. (4252)
09:35:23 BROKER  0: BI File Threshold size (-bithold): 0.0   Bytes. (9238)
09:35:23 BROKER  0: BI File Threshold Stall (-bistall): Disabled. (6552)
09:35:23 BROKER  0: After-Image Stall (-aistall): Not Enabled. (4254)
09:35:23 BROKER  0: After-Image Block Size: 16384. (4255)
09:35:23 BROKER  0: Number of After-Image Buffers (-aibufs): 40. (4256)
09:35:23 BROKER  0: Storage object cache size (-omsize): 1024 (8527)
09:35:23 BROKER  0: Maximum Number of Clients Per Server (-Ma): 4. (4257)
09:35:23 BROKER  0: Maximum Number of Servers (-Mn): 112. (4258)
09:35:23 BROKER  0: Minimum Clients Per Server (-Mi): 1. (4259)
09:35:23 BROKER  0: Maximum Number of Users (-n): 279. (4260)
09:35:23 BROKER  0: Host Name (-H): server. (4261)
09:35:23 BROKER  0: Service Name (-S): 30000. (4262)
09:35:23 BROKER  0: Network Type (-N): TCP. (4263)
09:35:23 BROKER  0: Character Set (-cpinternal): ISO8859-1. (4264)
09:35:23 BROKER  0: Parameter File: Not Enabled. (4282)
09:35:23 BROKER  0: Maximum Servers Per Broker (-Mpb): 50. (5647)
09:35:23 BROKER  0: Minimum Port for Auto Servers (-minport): 3000. (5648)
09:35:23 BROKER  0: Maximum Port for Auto Servers (-maxport): 5000. (5649)
09:35:23 BROKER  0: This broker supports 4GL server groups only. (8863)
09:35:23 BROKER  0: Large database file access has been enabled. (9426)
09:35:23 BROKER  0: Created shared memory with segment_id: 11599872 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 145817600 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 280035328 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 414253056 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 548470784 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 682688512 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 816906240 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 951123968 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 1085341696 (9336)
09:35:23 BROKER  0: Created shared memory with segment_id: 1219559424 (9336)
09:35:23 SRV     1: Started on port 3000 using TCP, pid 2488. (5646)
09:35:24 SRV     1: Login usernum 389, userid user1, on comp00667. (742)
09:35:24 SRV     2: Started on port 3001 using TCP, pid 1804. (5646)
09:35:25 SRV     2: Login usernum 388, userid user2, on comp00632. (742)
09:35:25 SRV     3: Started on port 3003 using TCP, pid 2832. (5646)
09:35:26 SRV     3: Login usernum 387, userid user5, on comp00664. (742)
09:35:26 BROKER  4: Started for 30050 using TCP, pid 2808. (5644)
09:35:26 BROKER  4: This is an additional broker for this protocol. (5645)
09:35:26 BROKER  4: This broker supports SQL server groups only. (8864)
09:35:28 BIW   112: Started. (2518)
09:35:30 AIW   113: Started. (2518)
09:35:32 WDOG  114: Started. (2518)
09:35:34 APW   115: Started. (2518)
09:35:36 APW   116: Started. (2518)
09:35:38 APW   117: Started. (2518)
09:35:40 APW   118: Started. (2518)
09:35:40 SRV     2: Logout usernum 388, userid , on comp00632. (739)
09:35:49 SRV     2: Login usernum 388, userid user2, on comp00632. (742)
09:36:05 SRV     5: Started on port 3005 using TCP, pid 2420. (5646)
09:36:06 SRV     5: Login usernum 386, userid user3, on comp00626. (742)
09:36:09 SRV     1: Logout usernum 389, userid , on comp00667. (739)
09:36:15 SRV     1: Login usernum 389, userid user4, on comp00667. (742)
09:36:19 SQLSRV2 6: SQL Server 9.1D.09 started, configuration: "database.defaultconfiguration" 
09:36:19 SQLSRV2 6: "SQL" started on port 3008, pid 3380 (0x00000d34).
09:36:19 SQLSRV2 6: Thread stack size: 1024000 (bytes).
09:36:19 SQLSRV2 6: DLC from ENVIRONMENT VARIABLE is: C:\Program Files\PROGRESS 
09:36:19 SQLSRV2 6: WRKDIR from REGISTRY is: C:\PROGRESS\WRK\ 
09:36:19 SQLSRV2 6: JDKHOME from REGISTRY is: C:\Program Files\PROGRESS\jdk 
09:36:19 SQLSRV2 6: JREHOME from REGISTRY is: C:\Program Files\PROGRESS\jre 
09:36:19 SQLSRV2 6: CLASSPATH from DEFAULT is:  
09:36:19 SQLSRV2 6: PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds
09:36:20 SRV     7: Started on port 3009 using TCP, pid 3396. (5646)
09:36:20 SQLSRV2 6: Login usernum 385, remote SQL client. (8873)
09:36:20 SQLSRV2 6: Usr 385 set name to sysprogress. (7129)
09:36:21 SRV     7: Login usernum 384, userid Admin, on comp00734. (742)
09:36:24 SRV     8: Started on port 3010 using TCP, pid 3384. (5646)

 

[TomBascom]

I don't keep track of what posts are related to what others -- they all blend together after a while

On the bright side I was partially wrong -- you have an Enterprise DB license. That's good.

Can you compile the code?

If so then you don't need to be held hostage by an incompetent and intransigent supplier.

 

[TomBascom]

Having read your other post:

Find a way to exercise the escrow agreement... These guys are putting you out of business by refusing to act on perfectly reasonable requests.

While the lawyers chew that over -- to the extent that any of the overnight batches are (or can be) run on the same server as the db start using self-service connections rather than client server connections.

 

[Rob Fitzpatrick]

Regarding your attempt to increase -B (the size of the buffer pool; "blocks in DB buffer"), I suspect you ran into a shared memory limit. The -B parameter is specified in units of database blocks, so when you increase it you need to calculate whether the new number is feasible. You are using 32-bit Progress, so your broker's virtual memory address space size is 4 GB, of which the user-mode portion is half that (2 GB). That is the theoretical limit of addressable virtual memory by Progress (or any 32-bit process), however the practical limit will be a fair bit less, as part of the address space of the broker (or another database client that connects via shared memory) also has to include executable code, DLLs, thread stacks, heap, etc. This can be complicated by the presence of other databases. You didn't mention any other DBs, but if your client does need to connect to multiple databases then it needs to map the sum of all of their shared memory segments into its address space, which can be quite limiting on 32-bit.

Your database block size (as shown in your log file) is 4096 bytes. 300000 * 4096 = 1.14 GB. Increasing that to -B of 400000 means you need to allocate 1.53 GB of shared memory when you start the DB. It's possible that was too much given your configuration. As to the failure of your restart (I'm now moving into "guess" territory), it's possible that the shared memory allocated to the initial broker process wasn't freed when it crashed, and so the OS couldn't allocate another 1.14 GB. I've seen something similar happen in Linux when a broker is killed with kill -9. The shared memory segments aren't automatically freed, and will remain in use until they are deleted manually or the box is rebooted. To be honest I'm not too clear on shared memory segment troubleshooting in Windows.

As Tom said, once you move to a 64-bit database you won't have these restrictions. With a DB of about 100 GB, 1 GB of buffer pool isn't much at all. Getting to a server with more RAM and software with the ability to use it, you can tune for much better performance. Although you have fast disks and RAID 10 (good), to an extent you're defeating that advantage because you're doing more I/O than you should.

On the Windows side getting to 64-bit means getting to version 10 or later of Progress, which means you can dump and load into Type II storage areas which will also help with both application performance and backup performance. The only caveat might be on the Progress application side. If you have GUI code that runs on the server today, it may require 32-bit Progress, as there is no 64-bit GUI client. But it's still better to have a robust back end, even if it means running application code via client/server. Presumably your batch code should be able to run in a character client, and thus in a 64-bit runtime, unless it's taking dependencies in some way like defining OS DLL functions as external procedures. Anyway, your testing should bring such issues to light pretty quickly, if there are any.

 

[repostor]

Hi,
To shorten the backup window for your application, than it might be better to send the backup directly to TSM instead of:
1. backup to local disk
2. Perform a TSM backup on local disk to TSM Server

Data Protector for Progress OpenEdge 4GL using IBM Tivoli Storage Manager, might be usefull for your need:
1. backup (probkup) directly to TSM
1a. Full backup
1b. Incremental backup
1c. After-Image backup

Restore will be faster too, due to all data will be retrieved, and restored directly to the Progress OpenEdge database:
(restore as far as possible)

# progressbackup -s yourdb

(will first restore the fullbackup)
(secondly all incremental from last full to point in time, or as far as possible)
(restore all after-images needed, and recover in progress OpenEdge)

Only unique data will be sent if "client deduplication" is enabled on the TSM Client (unique since last full / inc)
With compression enabled in TSM Client, the data will be compressed before sent to TSM Server.

 

[Zebwen]

Thankyou for the suggestions all. There's some good stuff for us to chew over in there, unfortunately a lot of it would require us to effectively cut our losses and break from our Vendor (upgrading to new version of Progress / new version of Windows, escrow, etc), we are not yet sure that this is something we want to do.

Does anyone have ANY suggestions on how we might improve things with the version of Progress (9.1D SP9) that we are currently using? Either for just Probkup or for server performance as a whole? We are going through an auditing process currently, giving each server that makes connections to our DB it's own login, so that we can more easily see which connections are causing more slowdown so we can try and optimize / re-schedule them, but other than that we are still kind of stuck with this general sluggish performance. From what we are seeing here it looks like we may simply be screwed on the version of Progress that we are on, but if anyone has any magic-pill suggestions for us to try out it would be appreciated!

We have recently heard that our Vendor now MAY be entertaining a trial of 9.1E (hopefully SP4), so we have our fingers crossed for that, but it seems as though we will keep suffering until we can move over to a fully 64bit system?

Thanks again all!

 

[Cringer]

I can't advise as to your particular woes, but a conversation is needed with your vendor as to why they are holding you back on a version of Progress that is unsupported. Upgrading to v10 or v11 should just be a case of converting the database and recompiling the source.

 

[cj_brandt]

I don't think you'll see much of a performance boost going from 9.1D09 to 9.1E04. Running perfmon and looking at the average disk queue on the drives that hold the database extents will probably a lot of waits on disk IO. On Windows systems, I would run perfmon and track memory, CPU, disk io and a few other items.

Running a dump and load of your database would be a temp fix for the disk io issues, but that is a lot of work if you aren't familiar with Progress, and it would require some downtime. Clients I have run a D&L for generally saw excellent performance for 6 months and then it would gradually decrease. Open Edge 10 introduces something called Type II storage areas which addresses this issue.

On windows servers with a 4kb database blocksize, I think 325,000 or maybe 350,000 was as high as I could get the -B.

 

[TomBascom]

I too have generally found the -B limit on Windows to be in the 325,000 to 350,000 range (assuming a 4k db block).

Dump & load *might* help. It might even help a lot. But it isn't a sure thing. If you do dump & load take the opportunity to create storage areas -- even type 1 areas are better than stuffing everything in the schema area. And if you have some very high activity tables put them in dedicated areas. You can mimic a type 2 area that way. See http://dbappraise.com/ppt/sos.pptx and http://www.greenfieldtech.com/articles/storage_areas.shtml for details.

Also, as I said earlier -- if you can run any of these batch jobs as self-service processes rather than client-server that will also help. To run them self-service: 1) they must run on the db server, 2) you must *NOT* have -S on the command line or in the startup .pf file.

 

[Zebwen]

Removed, and apologies due to multiple posts :/ Will watch this in future.

 

[Zebwen]

Removed due to doublepost.

 

[Zebwen]

Apologies, apparently my replies aren't always getting through, I think they are awaiting moderator approval, but then sometimes I can post and they come through immediately (I think it might be my use of CODE in the posts), so I took to posting a simple reply and then editing it, I have just spotted my other posts come through and edited them to remove them, I'll remember this in future

In answer to your questions:

We have recently performed a dump and load. It seemed to increase performance slightly immediately after the load, but this benefit tapered off in about a week or so. Our DB is split in to areas, with nothing but the schema in the schema area. This may not be 100% optimized, and we are hopefully in the process of optimising further, but we are definately not running everything directly from schema.

The batch files for the overnight process are run directly on the server (they are called by a scheduled task), the batch script that is run is: "C:\program files\progress\bin\prowin32.exe" -db D:\documents -db D:\database -BL 200000 10000 -d dmy -crc -Bt 2500 -TM 31 -TB 32 -b -q -v6q -o LPT1: -ininame D:\ini\server.ini -p D:\nextday.p -Wa -wpp, so there is no mention of -S there. I will post the ini file referenced there just in case it contains anything useful:

[Startup]V6Display=no
;DefaultV6UpdateFont=Fixedsys, size=10
;V6fontnumber=10
DLC=c:\Progra~1\PROGRESS
Use-3D-Size=yes
PROBUILD=c:\Progra~1\PROGRESS\PROBUILD
PROCFG=G:\sys\UPDATES\PROGRESS.CFG
PROPATH=G:\sys\help,G:\sys\,G:\sys\trig,G:\sys\src,G:\sys\image,G:\sys\batch,G:\sys\pool,G:\sys\custom,.,G:\sys\bin,G:\sys\icons,G:\sys\WpProc,G:\sys\WpProc\Call,G:\sys\dialler,G:\sys\transact,G:\sys\multi,G:\sys\CCard,G:\sys\priority,G:\sys\prog1,G:\sys\prog2,G:\sys\report,G:\sys\gui,G:\sys\guimenu,G:\sys\ini,G:\sys\menu,G:\sys\util,G:\sys\spl,G:\sys\wp,G:\sys\wp\util,G:\sys\wpx,G:\sys\adm2,c:\Progra~1\PROGRESS\gui,c:\Progra~1\PROGRESS\gui\adecomm.pl,c:\Progra~1\PROGRESS\gui\adecomp.pl,c:\Progra~1\PROGRESS\gui\adedesk.pl,c:\Progra~1\PROGRESS\gui\adedict.pl,c:\Progra~1\PROGRESS\gui\adeedit.pl,c:\Progra~1\PROGRESS\gui\adeicon.pl,c:\Progra~1\PROGRESS\gui\adeshar.pl,c:\Progra~1\PROGRESS\gui\adeuib.pl,c:\Progra~1\PROGRESS\gui\adeweb.pl,c:\Progra~1\PROGRESS\gui\adexml.pl,c:\Progra~1\PROGRESS\gui\prodict.pl,c:\Progra~1\PROGRESS\gui\protools.pl,c:\Progra~1\PROGRESS\gui\as4dict.pl,c:\Progra~1\PROGRESS\gui\aderes.pl,c:\Progra~1\PROGRESS\PROBUILD\EUCAPP,c:\Progra~1\PROGRESS\PROBUILD\EUCAPP\EUC.PL,c:\Progra~1\PROGRESS,c:\Progra~1\PROGRESS\bin
FrameSpacing=0
OLD-PROPATH=.,G:\sys\help,G:\sys\tbiconv,G:\sys\,G:\sys\src,G:\sys\conversion,c:\dlcconversion\programs\conv_prog,G:\sys\image,G:\sys\batch,G:\sys\pool,G:\sys\custom,G:\sys\bin,G:\sys\icons,G:\sys\WpProc,G:\sys\WpProc\Call,G:\sys\dialler,G:\sys\transact,G:\sys\multi,G:\sys\CCard,G:\sys\priority,G:\sys\prog1,G:\sys\prog2,G:\sys\gui,G:\sys\guimenu,G:\sys\ini,G:\sys\menu,G:\sys\util,G:\sys\spl,G:\sys\wp,G:\sys\wp\util,G:\sys\wpx,G:\sys\adm2,c:\Progra~1\PROGRESS\gui,c:\Progra~1\PROGRESS\gui\adecomm.pl,c:\Progra~1\PROGRESS\gui\adecomp.pl,c:\Progra~1\PROGRESS\gui\adedesk.pl,c:\Progra~1\PROGRESS\gui\adedict.pl,c:\Progra~1\PROGRESS\gui\adeedit.pl,c:\Progra~1\PROGRESS\gui\adeicon.pl,c:\Progra~1\PROGRESS\gui\adeshar.pl,c:\Progra~1\PROGRESS\gui\adeuib.pl,c:\Progra~1\PROGRESS\gui\adeweb.pl,c:\Progra~1\PROGRESS\gui\adexml.pl,c:\Progra~1\PROGRESS\gui\prodict.pl,c:\Progra~1\PROGRESS\gui\protools.pl,c:\Progra~1\PROGRESS\gui\as4dict.pl,c:\Progra~1\PROGRESS\gui\aderes.pl,c:\Progra~1\PROGRESS\PROBUILD\EUCAPP,c:\Progra~1\PROGRESS\PROBUILD\EUCAPP\EUC.PL,c:\Progra~1\PROGRESS,c:\Progra~1\PROGRESS\bin


[system]
;************************************************************************
;Standard system entries:
;
STARTIN=G:\sys
DATABASE=database
DATABASE2=-N TCP -S 40000 -H server
ARCHPN=archive
ARCHPN2=-N TCP -S 40010 -H server
;REPPN=G:\sys\db\report
;REPPN=G:\sys\db\report
;REPPN2=-1
DOSPIN=G:\sys\TMP
TMPPIN=G:\sys\TMP
DLCBIN=c:\Progra~1\PROGRESS\BIN
;STATDB=cf_stats
;STATDB2=-N TCP -S statsrv -H hostname
LFMPATH=G:\sys\LFM
DATABASETB=G:\sys\bin\1Menu.tb
ARCHIVETB=G:\sys\bin\2Menu.tb


BTCHENTDET=yes


[Colors]
color0=0,0,0
color1=0,0,128
color2=0,128,0
color3=0,128,128
color4=128,0,0
color5=128,0,128
color6=128,128,0
color7=128,128,128
color8=192,192,192
color9=0,0,255
color10=0,255,0
color11=0,255,255
color12=255,0,0
color13=255,0,255
color14=255,255,0
color15=255,255,255
color16=255,255,0


NORMAL=0,15
INPUT=15,0
MESSAGES=15,1


[Default Window]
x=60
y=20
rows=25
columns=80


[fonts]
font0=Fixedsys, size=10
font1=MS Sans Serif, size=8
font2=Courier New, size=8
font3=Fixedsys, size=10
font4=MS Sans Serif, size=8
font5=MS Sans Serif, size=10
font6=Courier New, size=10
font7=System
font8=System
font9=System
font10=Fixedsys, size=10
font11=Tahoma, size=8
font12=System
font13=System
font14=System
font15=System
font16=System
font17=System
font18=System
font19=System
font20=System
font21=System
font22=System
font23=System
font24=System
font25=System
font26=System
font27=System
font28=System
font29=System
font30=MS Sans Serif, size=8


[Keys]
PUT=F6,CTRL-P
RECALL=F7,CTRL-R
CLEAR=F8,CTRL-Z
NEW-LINE=F9,CTRL-N,INSERT-HERE
DELETE-LINE=F10,CTRL-D
;FIND is used by AUTHOR for paragraphs.
FIND=F11,CTRL-F
APPEND-LINE=F12,CTRL-A
BLOCK=CTRL-V
OPTIONS=CTRL-O


[WinChar Startup]
;DLC=
;PROCFG=
;PROMSGS=
;PROPATH=
;DLC=c:\Progra~1\PROGRESS
;PROBUILD=c:\Progra~1\PROGRESS\PROBUILD
;PROPATH=.,c:\Progra~1\PROGRESS\PROBUILD\EUCAPP\EUC.PL,c:\Progra~1\PROGRESS\PROBUILD\EUCAPP


[WinChar Colors]
color0=WHITE/BLUE          "NORMAL"
color1=BLACK/GRAY          "INPUT, UNDERLINE"   
color2=BLACK/GRAY          "MESSAGES, REVERSE"
color3=BLUE/WHITE          "HIGHLITE, HELP"
color4=BLINK-RED/WHITE     "URGENT"


;NORMAL=WHITE/BLUE
;INPUT=BLACK/GRAY 
;MESSAGES=BLACK/GRAY 


[WinChar Default Window]
;rows=25
;rows=50


[WinChar Keys]
;GO=F1,CTRL-X


[Debug-Init]
;******************************************************************************
; THE FOLLOWING INFORMATION IS PRIVATE TO THE DEBUGGER. IT SHOULD NEVER BE
; MODIFIED EXCEPT BY THE DEBUGGER PROGRAM.
;
Location=1,146,15,460,464
Pane0=1,46," "
Pane1=1,14,""
Pane2=1,16,"Commands in Queue: 0"
Pane3=1,25,""
DebuggerName=PRODEBUG.EXE


[Debug-Macros]
Macro0=r, run &file
Macro1=c, continue
Macro2=s, step
Macro3=n, next
Macro4=b, break &file &line
Macro5=cb, cancel break &file &line
Macro6=sb, show breaks
Macro7=ss, show stack
Macro8=u, up
Macro9=d, down
Macro10=di, display &text
Macro11=sm, show macros


[Debug-Buttons]
Button0=Run,run &file
Button1=Cont,continue
Button2=Step,step
Button3=Next,next
Button4=Break,break &file &line
Button5=Stack,show stack
Button6=Up,up
Button7=Down,down
Button8=Disp,display &text
Button9=Exit,exit


[ProADE]
;DividerFgColor=15
;DividerBgColor=1
;OKBoxFgColor=1
;OKBoxBgColor=8
;FillinFgColor=0
;FillinBgColor=8
;Editor4GLFgColor=DEFAULT
;Editor4GLBgColor=DEFAULT
;Editor4GLFgSmallColor=0
;Editor4GLBgSmallColor=8
;FixedFont=0
;StandardFont=1
;Editor4GLFont=2
;EditorTabStop=4


[Proedit]
SaveSettings=yes
BufList=
ExitWarning=yes
SaveBufList=no
MinimizeBeforeRun=no
RestoreAfterRun=yes
PauseAfterRun=no
AutoCleanup=yes
EditorFont=3
New=SHIFT-F3
Open=F3
Close=F8
NewProcedureWindow=CTRL-F3
Save=F6
SaveAs=SHIFT-F6
Undo=CTRL-Z
Cut=CTRL-X
Copy=CTRL-C
Paste=CTRL-V
Find=CTRL-F
FindNext=F9
FindPrevious=SHIFT-F9
Replace=CTRL-R
GotoLine=CTRL-G
List=CTRL-L
NextBuffer=F7
PreviousBuffer=SHIFT-F7
Information=F12
Run=F2
CheckSyntax=SHIFT-F2
Debug=SHIFT-F4
CompilerMessages=CTRL-E
MRUFile1=G:\sys\prog1\dt-s11.p
MRUFile2=\\server\system\live\util\reviewcount.p
MRUFile3=G:\sys\util\FixWpdri.p
MRUFile4=\\nas01\user files\James\user.p


[count]
general=77


[Procomp]
SaveSettings=no
FileSpec01=Batch,*.p *.w
DefFileSpec=*.p *.w
LogFile=compile.log
ShowStatus=yes
RemoveOldRs=yes
IfNoR=no
SubDirs=yes
SaveNewRs=yes
XrefAppend=no
ListAppend=no
PageLength=60
PageWidth=80
V6Frame=No
StreamIO=no
FileSpec06=GUImenu,*.p *.w
FileSpec05=GUI,*.p *.w
FileSpec07=image,*.p *.w
FileSpec03=Custom,*.p *.w
MinRcodeSize=no
FileSpec02=CCard,*.p *.w
FileSpec04=dialler,*.p *.w
FileSpec08=import,*.p *.w
FileSpec09=messages,*.p *.w
FileSpec10=multi,*.p *.w
FileSpec11=pool,*.p *.w
GenerateMD5=no
FileSpec12=report,*.p *.w
FileSpec13=scheduler,*.p *.w
FileSpec14=transact,*.p *.w
FileSpec15=wpproc,*.p *.w


[ProTools]
FunctionDefs=c:\Progra~1\PROGRESS\gui\protools\protools.dat
PaletteLoc=83,67
ItemsPerRow=18
Visualization=buttons


[ProUIB]
Disabled_Cue_Cards=SmartWindow,SmartDialog
SaveSettings=yes
GridFactorHorizontal=1
AdvisorSettings=----------
PropertyEditorPosition=184,31,492,310
GiveHints=no
ShowAdvisor=no
IconDirectories=Icons,adeicon,G:\sys\Icons,C:system\Icons,Icons


[datasource]
;DB1=rep_scan
;DB2=database


[uib]


[ProAB]
MRUFile1=G:\sys\custom\wamcat.w
MRUFile2=G:\sys\custom\bAccountLookup.w
MRUFile3=G:\sys\custom\dAccLu.w
MRUFile4=G:\sys\custom\fAccLUCri.w
SaveSettings=no
MRUEntries=4
PrintFont=0
AdvisorSettings=-----------
LocalHost=SYSHOST
WebBrowser=C:\Program Files\Internet Explorer\iexplore.exe
OpenNewBrowser=yes
WebBroker=http://localhost/scripts/cgiip.exe/WService=wsbroker1
RemoteFileManagement=no
PropertyEditorPosition=354,221,317,297
ABMainLocation=98,0


[Desktop]
Minimize=yes


[ADM]
Color3DFace=17
Color3DHighLight=18
Color3DShadow=19
[ProSpy]
TreeFont=Medium
HideADE=FALSE
HideUIB=TRUE


[JAVA]
JDKHOME=c:\progra~1\progress\JDK
JREHOME=c:\progra~1\progress\jre
JRECP=c:\progra~1\progress\jre\lib\rt.jar;c:\progra~1\progress\jre\lib\i18n.jar
JDKCP=c:\progra~1\progress\jdk\lib\tools.jar
CP=
REPLCP=
PROGRESSCP=c:\progra~1\progress\Java\progress.jar;c:\progra~1\progress\Java\messages.jar;
JVMEXE=Java
JVMARGS=-ms8m
JAVAPOLICY=c:\progra~1\progress\Java\java.policy
[WidAttr]
ObjRef-Object=Async-request

And the server.pf

-db D:\database -N TCP -S 40000 -H server-db D:\documents -N TCP -S 40020 -H server
-db D:\images -N TCP -S 40030 -H server
-db D:\payment -N TCP -S 40040 -H server


#-p         _desk.p


-d          dmy
-q
-n          63


-v6q
-yy         1940
-tok          6400
#-o          lpt1:
-crc
-s          163
-T          C:\temp
-h          6
-inp         8192
#-noinactiveidx
#-p    .\guimenu\menu

So the -S switch is referenced here, although, there are quite a few .pf files in the folder, how can I be sure that this is the one being used?

Thanks,

Zeb

 

[Cringer]

Indeed they are getting sent for auto-moderation. Not sure why to be honest, but it should stop when you get to 10 posts (IIRC). I'll approve things now.

 

[Zebwen]

Indeed they are getting sent for auto-moderation. Not sure why to be honest, but it should stop when you get to 10 posts (IIRC). I'll approve things now.

Thanks, I'll watch for this in future and try not to tripplepost

 

[Cringer]

No problem at all - it's easily done when your posts go to the moderation queue.

 

[Rob Fitzpatrick]

I see the -ini parameter on your command line but not "-basekey ini", so I'm not even sure that that .ini file is being used for your client. It may be using info in the registry instead. Also, you have shown a client command line and the contents of server.pf, but the client does not reference it with "-pf server.pf". So I don't know which database(s) your client accesses.

I see several databases referenced; does your client connect to all of them? If so you need to look at their configuration as well, and get an understanding of what part they play in your application's I/O profile. If it does 99% of its CRUD activity in DB 1 and the other 1% in DBs 2 and 3, then you know where to focus your efforts.

Do you have a test environment with a data set similar to production, where you can do performance testing?

Also, I suggest you post your DB structure files. Even if you have data storage areas outside of the schema area, if your records-per-block values are badly chosen for your data, then that could significantly hurt your I/O (too much) and your DB caching efficiency (too low). In order to change those values you have to dump and load your data. I would suggest it if you can afford the time, and the downtime.