Migration: workgroup --> enterprise; same performance level?

[ssopar]

Hi,

we've gone through upgrade from workgroup to enterprise versione of OpenEdge, primarly because we needed more than 50 users, but I was also left to belive that the performance of the db itself should significantly approve. After the upgrade we've launched some queries for which we knew execution lasted for a long time but found almost no approvement in performance. The company that gives us db support told that this is because the tables are not well structured (bad indexes, etc). The thing is that the same company developed those applications (sic!).

Anyway, I would like to know, based on our current configuration, should I try to find somebody else to fine tune the db, or should I find somebody else to re-develop applications.

TIA,
Sasa

The server is quad-core bi-processor system with 16 GB RAM, 2 RAID-1 fields (15k disks); one field for OS and BI files and the other for dbs on Microsoft Windows 2003 Version 5.2 Service Pack 2 (Build 3790).
On the same server we also run web speed app server.

OE (10.2B0300 1375) config (database size is 2.6 GB, in two extents):
(4236) Database Type (-dt): PROGRESS.
(4237) Force Access (-F): Not Enabled.
(4238) Direct I/O (-directio): Enabled.
(4237) Force Access (-F): Not Enabled.
(4238) Direct I/O (-directio): Enabled.
(-----) LRU mechanism enabled.
(4239) Number of Database Buffers (-B): 100000.
(-----) Number of Alternate Database Buffers (-B2): 0.
(9422) Maximum private buffers per user (-Bpmax): 64.
(4240) Excess Shared Memory Size (-Mxs): 44.
(10014) The shared memory segment is not locked in memory.
(4241) Current Size of Lock Table (-L): 200000.
(13953) Maximum Area Number (-maxArea): 32000.
(4242) Hash Table Entries (-hash): 25621.
(4243) Current Spin Lock Tries (-spin): 50000.
(6526) Number of Semaphore Sets (-semsets): 3.
(13924) Maximum Shared Memory Segment Size (-shmsegsize) 1024 Mb.
(4244) Crash Recovery (-i): Enabled.
(6573) Database Blocksize (-blocksize): 8192.
(4245) Delay of Before-Image Flush (-Mf): 3.
(4247) Before-Image File I/O (-r -R): Reliable.
(4249) Before-Image Truncate Interval (-G): 0.
(4250) Before-Image Cluster Size: 524288.
(4250) Before-Image Cluster Size: 524288.
(4251) Before-Image Block Size: 8192.
(4252) Number of Before-Image Buffers (-bibufs): 25.
(-----) Record free chain search depth factor 5 (-recspacesearchdepth)
(9238) BI File Threshold size (-bithold): 0.0 Bytes.
(6552) BI File Threshold Stall (-bistall): Disabled.
(4254) After-Image Stall (-aistall): Not Enabled.
(4255) After-Image Block Size: 8192.
(4256) Number of After-Image Buffers (-aibufs): 25.
(8527) Storage object cache size (-omsize): 1024
(4257) Maximum Number of Clients Per Server (-Ma): 5.
(4258) Maximum Number of Servers (-Mn): 17.
(4259) Minimum Clients Per Server (-Mi): 1.
(4260) Maximum Number of Users (-n): 81.
(4282) Parameter File: Not Enabled.
(5647) Maximum Servers Per Broker (-Mpb): 15.
(5648) Minimum Port for Auto Servers (-minport): 3000.
(5649) Maximum Port for Auto Servers (-maxport): 5000.
(8863) This broker supports 4GL server groups only.
(9336) Created shared memory with segment_id: 1
(12813) Allowed index cursors (-c): 324.
(12814) Group delay (-groupdelay): 10.
(12815) Lock table hash table size (-lkhash): 25621
(12816) Maxport (-maxport): 5000
(12817) Minport (-minport): 3000
(12818) Message Buffer Size (-Mm): 1024
(12820) Maximum Servers per Broker (-Mpb): 15
(12821) Use muxlatches (-mux): 1
(12823) Semaphore Sets (-semsets): 3
(13870) Database Service Manager - IPC Queue Size (-pica) : 64.0 KBytes.
(13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
(15219) Encryption enabled: 0

 

[MaximMonin]

add few APW processes and increase BI cluster size.

 

[TomBascom]

You should find someone else to tune your db. See signature

Is your data in type 2 storage areas?

Is after-imaging enabled?

 

[ssopar]

Thank you all for responses; after-imaging is not enabled, I was told it counts only should I want to replicate the db.
I would say it's not in type 2, as in the structure file it is defined beeing in the "Schema area" which is area 1, if I'm not mistaking...

 

[cj_brandt]

The improvements from workgroup to enterprise seem to focus more on the ability to write data than read data - although you did set the spin parameter. It would be interesting to see how many "DB Reads" vs "Record Reads" promon shows while your queries are running. If your user tables are still in the schema area, then the db probably requires a lot more physical reads than necessary. That can be fixed with a dump and load along with a move to Type II storage.

 

[TomBascom]

After imaging is essential to data integrity and recovery. It is necessary for replication but that is NOT the only reason for it.

Without after-imaging your exposure to data loss is the entire period since your last recoverable backups. If you do nightly backups at midnight and your server goes up in flames at 11:59 (or if someone backs up a truck to your data center and steals it...) you lose the entire day of work. (Assuming you sent your previous days backup offsite and you can restore it -- you'd be surprised how many backups cannot actually be restored.)

Can the business /really/ reconstruct an entire day? (Or more?)

After imaging also protects you from human errors like accidentally deleting the database or running the purge routine against "test" only to discover that that was actually production (if you think these things will never happen to you you should rethink that position...)

Not running after-imaging is a career limiting move for a DBA and a business threatening move for a business.

And, as CJ indicated, having all of your data in the schema area is a bad thing too.

 

[tamhas]

Bottom line. There are some preliminary indicators that your database was not well tuned when it was running on workgroup, not the least of which is not using type 2 storage areas. Merely moving to enterprise is not going to fix that. All it is apparent that you have changed is -spin, which you have set to a possibly dubious value. The enterprise license is not a magic fix. It enables more fixes than workgroup, but you have a lot you could have done even on workgroup that still needs doing to get the most out of what you have.