Hi,
I am currently working on an ETL scenario, fetching tables from Progress through ODBC.
Unfortunately I'm working with an ancient 9.1D Progress version. However I know there are some of you who have huge amounts of experience with this version so this shouldn't be to much of a problem.
I use DataDirect ODBC drivers and SQL 92 statements to query the Progress database. I use only 'SELECT * FROM ....' statements so nothing fancy. The Extract only runs once per night.
What I would like to know is which parameters I have to set in order to get the data as quickly as possible out of Progress. I have already increased the number of servers, since only one SQL server connection is possible per server. Concrete questions are:
1) Does the -i parameter has any effect on sequential read performance?
2) Does the -noautoresult parameter has any effect on sequential read performance?
Are there any other things I should think about (fyi: hardware is top notch; plenty of memory, cpu and great SAN).
Like to hear from you,
Kind regards,
Dave
For anybody that is interested.... below the log with startup parameters.
| 23:31:56 BROKER 0: Begin Multi-user sessie. (333) |
| 23:31:56 BROKER 0: ** Deze sessie wordt gedraaid zonder de -i (no-integrity) optie. (512) |
| 23:31:56 BROKER 0: Begin van Physical Redo Phase bij 26624 . (5326) |
| 23:31:56 BROKER 0: Physical Redo Phase gereeb bij blk 26780 off 987 upd 1602. (7161) |
| 23:31:57 BROKER 0: Gestart voor 12786 gebruik tcp, pid 4808. (5644) |
| 23:31:57 BROKER 0: Progress OpenEdge Release 9.1E op WINNT. (4234) |
| 23:31:57 BROKER 0: Server gestart door srvclusteradmin op CON:. (4281) |
| 23:31:57 BROKER 0: Start met gebruik pid: 4808. (6574) |
| 23:31:57 BROKER 0: Fysieke Database Naam (-db): f:\dbtobiasodbc\tobias. (4235) |
| 23:31:57 BROKER 0: Database Type (-dt): PROGRESS. (4236) |
| 23:31:57 BROKER 0: Geforceerde toegang (-F): Not Enabled. (4237) |
| 23:31:57 BROKER 0: Directe I/O (-directio): Not Enabled. (4238) |
| 23:31:57 BROKER 0: Aantal Database Buffers (-B): 5208. (4239) |
| 23:31:57 BROKER 0: Maximaal aantal prive buffers per gebruiker (-Bpmax): 64. (9422) |
| 23:31:57 BROKER 0: Shared Memory overloop grootte (-Mxs): 16574. (4240) |
| 23:31:57 BROKER 0: De shared memory segment is not locked in memory. (10014) |
| 23:31:57 BROKER 0: Huidige grootte van de Lock Table (-L): 100000. (4241) |
| 23:31:57 BROKER 0: Hash Tabel Invoer (-hash): 1733. (4242) |
| 23:31:57 BROKER 0: Huidige Spin Lock Tries (-spin): 0. (4243) |
| 23:31:57 BROKER 0: Anntal Semaphore Sets (-semsets) 1. (6526) |
| 23:31:57 BROKER 0: Crash Herstellen (-i): Not Enabled. (4244) |
| 23:31:57 BROKER 0: Database Blocksize (-blocksize): 4096. (6573) |
| 23:31:57 BROKER 0: Vertraging van de Before-Image Flush (-Mf): 3. (4245) |
| 23:31:57 BROKER 0: Before-Image bestand I/O (-r -R): Not Reliable. (4247) |
| 23:31:57 BROKER 0: Before-Image inkort interval (-G): 0. (4249) |
| 23:31:57 BROKER 0: Before-Image cluster grootte: 524288. (4250) |
| 23:31:57 BROKER 0: Before-Image Block grootte: 4096. (4251) |
| 23:31:57 BROKER 0: Aantal Before-Image buffers (-bibufs): 5. (4252) |
| 23:31:57 BROKER 0: BI File Threshold grote (-bithold): 0.0 Bytes. (9238) |
| 23:31:57 BROKER 0: BI File Threshold Stall (-bistall): GeDeactiveerd. (6552) |
| 23:31:57 BROKER 0: After-Image Stall (-aistall): Not Enabled. (4254) |
| 23:31:57 BROKER 0: Aantal After-Image buffers (-aibufs): 5. (4256) |
| 23:31:57 BROKER 0: Storage object cache grote (-omsize): 1024 (8527) |
| 23:31:57 BROKER 0: Maximum aantal Clients Per Server (-Ma): 5. (4257) |
| 23:31:57 BROKER 0: Maximum aantal servers (-Mn): 151. (4258) |
| 23:31:57 BROKER 0: Minimaal aantal Clients per server (-Mi): 1. (4259) |
| 23:31:57 BROKER 0: Maximum aantal gebruikers (-n): 651. (4260) |
| 23:31:57 BROKER 0: Host naam (-H): SVTOBIASY1. (4261) |
| 23:31:57 BROKER 0: Service Naam (-S): 12786. (4262) |
| 23:31:57 BROKER 0: Netwerk Type (-N): tcp. (4263) |
| 23:31:57 BROKER 0: Karakter Set (-cpinternal): ISO8859-1. (4264) |
| 23:31:57 BROKER 0: Parameter bestand: Not Enabled. (4282) |
| 23:31:57 BROKER 0: Minimum port nummer voor Auto Servers (-minport): 3000. (5648) |
| 23:31:57 BROKER 0: Maximum port nummer voor Auto Servers (-maxport): 5000. (5649) |
| 23:31:57 BROKER 0: Deze broker ondersteund 4GL en SQL server groepen. (8865) |
-i is "no integrity". You sould never use this in a production scenario. It has no bearing on read performance but it might improve write performance (it is sometimes useful when performing a completely repeatable maintenance operation like a dump & load).
-spin is set to zero. If this is a workgroup license then that is your problem. There is a major performance issue with WG licenses on SMP systems (you mentioned that the hardware is "top notch" so I'll assume multiple cores...) It is partially fixed in 10.1B. The only real solution, if performance is your objective, is to upgrade the license to an Enterprise license.
If you have an Enterprise license just set -spin to something reasonable like 10,000 and it'll be the closest thing to magic that you've ever seen.
-B is way too small. In fact that looks like a default value. With 4k blocks a value of -B 250000 would give you a 1GB buffer cache. That would be very, very good for performance. (500k would be even better). Small increments to -B are a waste of time. The improvement gained follows an inverse square law.
There is also a whole lot that you can do in terms of properly assigning tables to storage areas and such but the only information to go on is that this looks like a default install so there is probably a very large amount of room for improvement.
Running update statistics is very important for SQL performance. Have you ever done so?
Hi Tom,
In what way does an 'update statistics' help in my case. I was under the presumption the statistics are only relevant when using joins, where clauses et cetera . Is 'update statistics' relevant for sequential read access?
Kind regards,
Dave
Tom,
One more thing....
In my server log I see one client openingen mutiple SQL connections. Since each server can handle only one SQL connection, a total of 54 servers are started. I wonder what hapens if i set the -B parameter to the proposed value of 250000 blocks.....
Is every server going to allocate 1GB?
Is this a potential allocation of 1GB based on usage or a direct allocation of 1GB when starting a server?
Kind regards,
Dave
No, the buffer pool is shared memory. There is just one copy.
Oops, you are correct.
That does however bring up a good point -- if you were to upgrade to 10.2 and use type 2 storage areas you could leverage the fast table scan capability.
Thanx a lot,
Kind regards,
Dave