Tuning a Development DB

[Cringer]

I have a copy of our live database that I have on a USB drive (not ideal I know, but I use the database in the office and at home). Currently the startup settings on the database are fairly much taken without much thought, and whilst the database runs ok, I'm sure I could tune it a bit.

Progress v11.2.1
Win7 32bit

Hardware:
Processor: Intel(R) Core(TM)2 Duo CPU E8500 @ 3.16GHz 3.17 GHz
Installed memory (RAM): 4.00 GB (3.50 GB usable)

System type: 32-bit Operating System

At some point I should probably upgrade to a 64bit OS, but at the moment we can't manage certain ocx's from a 64 bit OS.

Database log:

[2013/07/27@09:43:07.016+0100] P-5140       T-4000  I BROKER  0: (4237)  Force Access (-F): Not Enabled. 
[2013/07/27@09:43:07.017+0100] P-5140       T-4000  I BROKER  0: (4238)  Direct I/O (-directio): Not Enabled. 
[2013/07/27@09:43:07.018+0100] P-5140       T-4000  I BROKER  0: (-----) LRU mechanism enabled.
[2013/07/27@09:43:07.018+0100] P-5140       T-4000  I BROKER  0: (-----) Number of LRU force skips (-lruskips): 50
[2013/07/27@09:43:07.018+0100] P-5140       T-4000  I BROKER  0: (-----) Number of LRU2 force skips (-lru2skips): 0
[2013/07/27@09:43:07.019+0100] P-5140       T-4000  I BROKER  0: (4239)  Number of Database Buffers (-B): 30000. 
[2013/07/27@09:43:07.019+0100] P-5140       T-4000  I BROKER  0: (-----) Number of Alternate Database Buffers (-B2): 0.
[2013/07/27@09:43:07.022+0100] P-5140       T-4000  I BROKER  0: (9422)  Maximum private buffers per user (-Bpmax): 64. 
[2013/07/27@09:43:07.022+0100] P-5140       T-4000  I BROKER  0: (4240)  Excess Shared Memory Size (-Mxs): 25. 
[2013/07/27@09:43:07.022+0100] P-5140       T-4000  I BROKER  0: (10014) The shared memory segment is not locked in memory. 
[2013/07/27@09:43:07.023+0100] P-5140       T-4000  I BROKER  0: (4241)  Current Size of Lock Table (-L): 8192. 
[2013/07/27@09:43:07.023+0100] P-5140       T-4000  I BROKER  0: (13953) Maximum Area Number (-maxArea): 32000. 
[2013/07/27@09:43:07.023+0100] P-5140       T-4000  I BROKER  0: (4242)  Hash Table Entries (-hash): 9337. 
[2013/07/27@09:43:07.023+0100] P-5140       T-4000  I BROKER  0: (4243)  Current Spin Lock Tries (-spin): 12000. 
[2013/07/27@09:43:07.024+0100] P-5140       T-4000  I BROKER  0: (6526)  Number of Semaphore Sets (-semsets): 3. 
[2013/07/27@09:43:07.024+0100] P-5140       T-4000  I BROKER  0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 256 Mb. 
[2013/07/27@09:43:07.024+0100] P-5140       T-4000  I BROKER  0: (4244)  Crash Recovery (-i): Enabled. 
[2013/07/27@09:43:07.025+0100] P-5140       T-4000  I BROKER  0: (6573)  Database Blocksize (-blocksize): 8192. 
[2013/07/27@09:43:07.025+0100] P-5140       T-4000  I BROKER  0: (4245)  Delay of Before-Image Flush (-Mf): 3. 
[2013/07/27@09:43:07.027+0100] P-5140       T-4000  I BROKER  0: (4247)  Before-Image File I/O (-r -R): Reliable. 
[2013/07/27@09:43:07.031+0100] P-5140       T-4000  I BROKER  0: (4249)  Before-Image Truncate Interval (-G): 0. 
[2013/07/27@09:43:07.033+0100] P-5140       T-4000  I BROKER  0: (4250)  Before-Image Cluster Size: 524288. 
[2013/07/27@09:43:07.035+0100] P-5140       T-4000  I BROKER  0: (4251)  Before-Image Block Size: 8192. 
[2013/07/27@09:43:07.035+0100] P-5140       T-4000  I BROKER  0: (4252)  Number of Before-Image Buffers (-bibufs): 20. 
[2013/07/27@09:43:07.043+0100] P-5140       T-4000  I BROKER  0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2013/07/27@09:43:07.043+0100] P-5140       T-4000  I BROKER  0: (9238)  BI File Threshold size (-bithold): 0.0   Bytes. 
[2013/07/27@09:43:07.044+0100] P-5140       T-4000  I BROKER  0: (6552)  BI File Threshold Stall (-bistall): Disabled. 
[2013/07/27@09:43:07.044+0100] P-5140       T-4000  I BROKER  0: (4254)  After-Image Stall (-aistall): Not Enabled. 
[2013/07/27@09:43:07.044+0100] P-5140       T-4000  I BROKER  0: (4255)  After-Image Block Size: 8192. 
[2013/07/27@09:43:07.047+0100] P-5140       T-4000  I BROKER  0: (4256)  Number of After-Image Buffers (-aibufs): 20. 
[2013/07/27@09:43:07.048+0100] P-5140       T-4000  I BROKER  0: (-----) Partition Manager cache size (-mtpmsize): 1024
[2013/07/27@09:43:07.048+0100] P-5140       T-4000  I BROKER  0: (8527)  Storage object cache size (-omsize): 1024 
[2013/07/27@09:43:07.048+0100] P-5140       T-4000  I BROKER  0: (4257)  Maximum Number of Clients Per Server (-Ma): 5. 
[2013/07/27@09:43:07.049+0100] P-5140       T-4000  I BROKER  0: (4258)  Maximum Number of Servers (-Mn): 6. 
[2013/07/27@09:43:07.049+0100] P-5140       T-4000  I BROKER  0: (4259)  Minimum Clients Per Server (-Mi): 1. 
[2013/07/27@09:43:07.050+0100] P-5140       T-4000  I BROKER  0: (-----) Use pollset mechanism for client/server (-pollset): Disabled
[2013/07/27@09:43:07.050+0100] P-5140       T-4000  I BROKER  0: (-----) Server network message wait time (-Nmsgwait): 2
[2013/07/27@09:43:07.050+0100] P-5140       T-4000  I BROKER  0: (-----) Delay first prefetch message (-prefetchDelay): Disabled
[2013/07/27@09:43:07.051+0100] P-5140       T-4000  I BROKER  0: (-----) Prefetch message fill percentage (-prefetchFactor): 0
[2013/07/27@09:43:07.051+0100] P-5140       T-4000  I BROKER  0: (-----) Minimum records in prefetch msg (-prefetchNumRecs): 16
[2013/07/27@09:43:07.052+0100] P-5140       T-4000  I BROKER  0: (-----) Suspension queue poll priority (-prefetchPriority): 0
[2013/07/27@09:43:07.052+0100] P-5140       T-4000  I BROKER  0: (4260)  Maximum Number of Users (-n): 26. 
[2013/07/27@09:43:07.052+0100] P-5140       T-4000  I BROKER  0: (4261)  Host Name (-H): ITT04894. 
[2013/07/27@09:43:07.053+0100] P-5140       T-4000  I BROKER  0: (4262)  Service Name (-S): jp-icmasliv. 
[2013/07/27@09:43:07.053+0100] P-5140       T-4000  I BROKER  0: (14268) TCP/IP Version (-ipver) : IPV4 
[2013/07/27@09:43:07.053+0100] P-5140       T-4000  I BROKER  0: (4263)  Network Type (-N): TCP. 
[2013/07/27@09:43:07.054+0100] P-5140       T-4000  I BROKER  0: (4264)  Character Set (-cpinternal): ISO8859-1. 
[2013/07/27@09:43:07.054+0100] P-5140       T-4000  I BROKER  0: (4282)  Parameter File: Not Enabled. 
[2013/07/27@09:43:07.054+0100] P-5140       T-4000  I BROKER  0: (5647)  Maximum Servers Per Broker (-Mpb): 5. 
[2013/07/27@09:43:07.055+0100] P-5140       T-4000  I BROKER  0: (5648)  Minimum Port for Auto Servers (-minport): 3000. 
[2013/07/27@09:43:07.055+0100] P-5140       T-4000  I BROKER  0: (5649)  Maximum Port for Auto Servers (-maxport): 5000. 
[2013/07/27@09:43:07.055+0100] P-5140       T-4000  I BROKER  0: (8865)  This broker supports both 4GL and SQL server groups. 
[2013/07/27@09:43:07.056+0100] P-5140       T-4000  I BROKER  0: (12540) Size of JTA transaction table (-maxxids):  100 
[2013/07/27@09:43:07.056+0100] P-5140       T-4000  I BROKER  0: (9426)  Large database file access has been enabled. 
[2013/07/27@09:43:07.057+0100] P-5140       T-4000  I BROKER  0: (9336)  Created shared memory with segment_id: 30146560 
[2013/07/27@09:43:07.060+0100] P-5140       T-4000  I BROKER  0: (12813) Allowed index cursors (-c): 104. 
[2013/07/27@09:43:07.061+0100] P-5140       T-4000  I BROKER  0: (12814) Group delay (-groupdelay): 10. 
[2013/07/27@09:43:07.063+0100] P-5140       T-4000  I BROKER  0: (12815) Lock table hash table size (-lkhash): 1237 
[2013/07/27@09:43:07.064+0100] P-5140       T-4000  I BROKER  0: (12816) Maxport (-maxport): 5000 
[2013/07/27@09:43:07.064+0100] P-5140       T-4000  I BROKER  0: (12817) Minport (-minport): 3000 
[2013/07/27@09:43:07.064+0100] P-5140       T-4000  I BROKER  0: (12818) Message Buffer Size (-Mm): 4096 
[2013/07/27@09:43:07.065+0100] P-5140       T-4000  I BROKER  0: (12820) Maximum Servers per Broker (-Mpb): 5 
[2013/07/27@09:43:07.065+0100] P-5140       T-4000  I BROKER  0: (12821) Use muxlatches (-mux): 1 
[2013/07/27@09:43:07.065+0100] P-5140       T-4000  I BROKER  0: (12823) Semaphore Sets (-semsets): 3 
[2013/07/27@09:43:07.066+0100] P-5140       T-4000  I BROKER  0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0  KBytes. 
[2013/07/27@09:43:07.066+0100] P-5140       T-4000  I BROKER  0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000. 
[2013/07/27@09:43:07.067+0100] P-5140       T-4000  I BROKER  0: (15219) Encryption enabled: 0 
[2013/07/27@09:43:07.067+0100] P-5140       T-4000  I BROKER  0: (15218) Encryption cache size (-ecsize): 1000 
[2013/07/27@09:43:07.067+0100] P-5140       T-4000  I BROKER  0: (15824) Multi-tenancy enabled: 0 
[2013/07/27@09:43:07.134+0100] P-5140       T-4000  I BROKER  0: (10471) Database connections have been enabled.

I'd really appreciate any hints you can give me on this. DB tuning really isn't my thing

 

[TomBascom]

You could increase -B. 200,000 might be a good start.

 

[Cringer]

Thanks Tom - will see how I go.

 

[Rob Fitzpatrick]

You can certainly use 32-bit DLLs in the 32-bit subsystem of a 64-bit OS (in SysWOW64). I'd be surprised if you can't do the same with 32-bit OCXs.

Having only 2 GB to work with, I think -B 200,000 will be a bit high with 8 KB block size and a 32-bit OS. That's 1.5 GB of buffer pool, and about 1.6 GB of total shared memory. There's a good chance you won't be able to allocate that large a single shared memory segment, and I'm not sure to what extent the broker retries the allocation. If the proserve fails, you could reduce -B or add -shmsegsize 128.

Sizing also depends on your PC's workload. If you aren't running anything else you may be OK with this much shared memory. If you have a high memory workload outside of OE then the OS could start outpaging, which will make your performance worse.

I don't know what you're doing with the DB. If you'll be doing reads, this may be enough of an optimization. For development you'd probably want to tune -tablerangesize and -indexrangesize as appropriate for your schema so you can see client CRUD stats. I assuming you're using a dev product, so you have the Personal RDBMS license. There's only so much tuning you can do with that.

I have a copy of our live database that I have on a USB drive

You're encrypting that USB drive, right?

 

[Cringer]

Thanks Rob - already worked out the -B was a little large when the machine ground to a halt. Set it to 100000 and it seems ok.