7.3x, 8.xx, 9xx Upgrade planning for OLTP product line

I am looking for information to assist in planning a product upgrade to the 7.3x, 8.xx, or 9,xx server level. We had been running on Informix Online 4.10 for several years, up until 1995. When this became unsupported we attempted to migration to Informix Online 7.0. We ran through about a dozen special consultants from our hardware vendor and from Informix, pretty high up the food chain. The product is an existing ESQL/C OLTP product with about 100 installed sites running on turnkey systems for which my group is responsible. The sites vary in size from 20 to 800 users. What I found in 1995 was: Informix 5 was the same speed as Informix 4, but needed 40% more memory Informix 7 was 80% slower than Informix 4, but needed 250% more memory Ultimately the above information proved false - that release of Informix 7 didn't perform with an infinite amount of memory, kernel tuning, database reorganization, and disk striping. With a million lines of code and a completed port, we were told that to make use of the Informix 7 features we would need to do a complete rewrite. So we ported to 5.0. So the first questions become What is the state of the art release for Informix db servers for an ESQL/C OLTP environment What is the performance differences for OLTP between Informix 5 How much more system memory must be added to achieve these results How much more cpu must be added to achieve these results I would appreciate whatever advice you can give, especially if you do OLTP in an environment of about 1000000 i/o's per second. Thanks in advance, Will Roper will@geac.com

8.x is not for OLTP. Don't consider it. You're probably safest going to 7.3x because Informix support for 9.1x is lukewarm. If you want to go to 9.x, wait for 9.2, which will come out sooner or later. Sorry I can't help with your resource questions. Seth Will Roper wrote: > > I am looking for information to assist in planning a product upgrade to > the 7.3x, 8.xx, or 9,xx server level. > > We had been running on Informix Online 4.10 for several years, up until > 1995. When this became unsupported we attempted to migration to > Informix Online 7.0. We ran through about a dozen special consultants > from our hardware vendor and from Informix, pretty high up the food > chain. The product is an existing ESQL/C OLTP product with about 100 > installed sites running on turnkey systems for which my group is > responsible. The sites vary in size from 20 to 800 users. What I > found in 1995 was: > > Informix 5 was the same speed as Informix 4, but needed 40% more memory > Informix 7 was 80% slower than Informix 4, but needed 250% more memory > > Ultimately the above information proved false - that release of Informix > 7 didn't perform with an infinite amount of memory, kernel tuning, > database reorganization, and disk striping. With a million lines of > code and a completed port, we were told that to make use of the Informix > 7 features we would need to do a complete rewrite. So we ported to 5.0. > > So the first questions become > > What is the state of the art release for Informix db servers for an > ESQL/C OLTP environment > > What is the performance differences for OLTP between Informix 5 > > How much more system memory must be added to achieve these results > > How much more cpu must be added to achieve these results > > I would appreciate whatever advice you can give, especially if you do > OLTP in an environment of about 1000000 i/o's per second. > > Thanks in advance, > > Will Roper > will@geac.com -- Seth Grimes Alta Plana database & Web / design & development grimes@altaplana.com http://altaplana.com 301-891-2581

Seth Grimes wrote: > > 8.x is not for OLTP. Don't consider it. You're probably safest going > to 7.3x because Informix support for 9.1x is lukewarm. If you want to > go to 9.x, wait for 9.2, which will come out sooner or later. Sorry I > can't help with your resource questions. Actually Seth Informix has discovered that though they build 8.xx for huge DSS systems it is the fastest OLTP engine on the market. The shared nothing architecture allows the database to be distributed across several to many cluster nodes and queries are distributed among them automatically with great results. IDS/DSS-XPO (8.xx) is definitely what Will may be seeking for ultimate OLTP services. Will, the thing about 5.xx -vs- 7.xx performance is that 5.xx takes (took?) better advantage of meager resources but could not scale well to many users and larger hardware and larger database requirements. If you have a larger database (5.xx was limited to 32GB tables and about 100GB total server instance size; 7.xx is limited to ~2TB tables or server) you need 7.xx or better. Version 7.30 has been significantly improved in raw speed and is now competitive with 5.xx servers according to several users and 7.31, due out in Feb., has had some communications streamlined to increase performance and reliability for network clients even further. The 7.24+ SET OPTIMIZATION FIRST_ROWS; feature and 7.30 index hints together restore user control of the server's behavior which was lost when the optimizer went cost based after 1.10->4.00. It is worth a look again. Does 7.xx perform better with more memory and multiple fast CPUs? Of course it does and the more hardware resources available and the more load you throw at it the better IDS7 looks over OL5. An example: I wrote my dbcopy.ec utility originally to copy 5.0x database tables from one server to another. In 5.0x we got pretty good performance by running up to 20 copies of dbcopy at a time to move multiple tables or key ranges of one table in parallel. We could not run more than 20 copies or the production applications would show severe slowdown. Now under 7.21->7.24 on the same hardware, I can easily run over 40 copies of dbcopy concurrently and the applications do not even notice! Is each copy any faster than it was under 5.0x? Only if I enable the FETCH ARRAY code in the most recent version of dbcopy which was not available in 5.0x ESQL libraries. But I get 70% total throughput improvement running twice as many dbcopy clients without slowing users even without the new code enabled. Can you get even more performance by recoding to take advantage of 7.3x features? Sure FETCH ARRAY and FETBUFSIZE are just 2 such features. Dbcopy runs at twice the throughput per copy with these two features enabled. Other improvements are fragmentation of large tables to take advantage of parallel query and reduce concurrency problems between mulitple OLTP clients. Art S. Kagel

Will Roper wrote: > > I am looking for information to assist in planning a product upgrade to > the 7.3x, 8.xx, or 9,xx server level. > > We had been running on Informix Online 4.10 for several years, up until > 1995. When this became unsupported we attempted to migration to > Informix Online 7.0. We ran through about a dozen special consultants > from our hardware vendor and from Informix, pretty high up the food > chain. The product is an existing ESQL/C OLTP product with about 100 > installed sites running on turnkey systems for which my group is > responsible. The sites vary in size from 20 to 800 users. What I > found in 1995 was: > > Informix 5 was the same speed as Informix 4, but needed 40% more memory > Informix 7 was 80% slower than Informix 4, but needed 250% more memory > > Ultimately the above information proved false - that release of Informix > 7 didn't perform with an infinite amount of memory, kernel tuning, > database reorganization, and disk striping. With a million lines of > code and a completed port, we were told that to make use of the Informix > 7 features we would need to do a complete rewrite. So we ported to 5.0. > > So the first questions become > > What is the state of the art release for Informix db servers for an > ESQL/C OLTP environment > > What is the performance differences for OLTP between Informix 5 > > How much more system memory must be added to achieve these results > > How much more cpu must be added to achieve these results > > I would appreciate whatever advice you can give, especially if you do > OLTP in an environment of about 1000000 i/o's per second. > > Thanks in advance, > > Will Roper > will@geac.com Hmmm... A million I/O's per second. Thats a lot. About 3.6 Billion per hour. Or between and 60 and 360 Billion pagereads per hour, depending on your BUFFERS hit ratio. And that charitably assumes all Reads on an OLTP system. And a mere 800 users are generating it. That must be some shockingly bad SQL. Now admittedly, I have no hands on experience with 4.x Informix releases. But either 4.x was a couple of decades ahead of its time (and the corresponding hardware to support it...), your info is inaccurate, or you sir are a troll. Which is it? greg

In article <36A89F1F.35D8@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >Seth Grimes wrote: >> >> 8.x is not for OLTP. Don't consider it. You're probably safest going >> to 7.3x because Informix support for 9.1x is lukewarm. If you want to >> go to 9.x, wait for 9.2, which will come out sooner or later. Sorry I >> can't help with your resource questions. > >Actually Seth Informix has discovered that though they build 8.xx for >huge DSS systems it is the fastest OLTP engine on the market. The >shared nothing architecture allows the database to be distributed >across several to many cluster nodes and queries are distributed among >them automatically with great results. IDS/DSS-XPO (8.xx) is >definitely what Will may be seeking for ultimate OLTP services. > Correct, I even heard that 8.x with a single co-server is faster than 7.x (probably due to the fact that 8.x does not include as many features as 7.x e.g. does 8.x have triggers??? >Will, the thing about 5.xx -vs- 7.xx performance is that 5.xx takes >(took?) better advantage of meager resources but could not scale well >to many users and larger hardware and larger database requirements. >If you have a larger database (5.xx was limited to 32GB tables and >about 100GB total server instance size; 7.xx is limited to ~2TB tables >or server) you need 7.xx or better. Version 7.30 has been >significantly improved in raw speed and is now competitive with 5.xx >servers according to several users and 7.31, due out in Feb., has had >some communications streamlined to increase performance and reliability >for network clients even further. The 7.24+ SET OPTIMIZATION !!! I didn't realise 7.31 has this, I think all 7.x users should move to at least 7.30.UC6 ASAP...We found 7.30.UC2 to be unstable with >1 CPUVP but 7.30.UC5+ is fine as we have several customers hitting it all day every day on Solaris 2.6 with no problems... So many problems on c.d.i are for 7.1x or 7.2x systems..hardly anyone mentions problems with 7.3x systems... >FIRST_ROWS; feature and 7.30 index hints together restore user control >of the server's behavior which was lost when the optimizer went cost >based after 1.10->4.00. It is worth a look again. > True... >Does 7.xx perform better with more memory and multiple fast CPUs? Of >course it does and the more hardware resources available and the more >load you throw at it the better IDS7 looks over OL5. An example: I >wrote my dbcopy.ec utility originally to copy 5.0x database tables from >one server to another. In 5.0x we got pretty good performance by >running up to 20 copies of dbcopy at a time to move multiple tables or >key ranges of one table in parallel. We could not run more than 20 >copies or the production applications would show severe slowdown. Now >under 7.21->7.24 on the same hardware, I can easily run over 40 copies >of dbcopy concurrently and the applications do not even notice! Is each >copy any faster than it was under 5.0x? Only if I enable the FETCH >ARRAY code in the most recent version of dbcopy which was not available >in 5.0x ESQL libraries. But I get 70% total throughput improvement >running twice as many dbcopy clients without slowing users even without >the new code enabled. > !!! 7.3x rocks!!! >Can you get even more performance by recoding to take advantage of 7.3x >features? Sure FETCH ARRAY and FETBUFSIZE are just 2 such features. >Dbcopy runs at twice the throughput per copy with these two features >enabled. Other improvements are fragmentation of large tables to take >advantage of parallel query and reduce concurrency problems between >mulitple OLTP clients. > >Art S. Kagel -- David Williams

Will Roper wrote: > snip..... > Well, I've been called a lot of things, but never a troll, so I don't > think that's it. > OK, I got a little carried away...my apologies, no offense meant. > The million is a max - on the systems that do that, the daytime average > was more like 400k, evenings about 250k. If it got higher I never knew, > because sar would barf and start telling me negative numbers. But one > must, after all, tune for peak performance. That's still an appalling amount of I/O for Unix hardware that's 0 to several years old. Since sar is involved, I assume these are real disk operations, not buffer reads. I have trouble even imagining a contrived scenario where you could drive that much I/O and still have a usable system. > > About 3% writes. And yes, 4.1 was way ahead of its time, it was > basicly the core of the 5.0 engine, which we have it in use on 4 > continents, having found a way to scale it quite nicely. > > The bottlenecks that I had seen in 7.0 were in disk buffer management. > In the old 4.1/5.0 design it was basicly a serial resource; when they > added the multi-threading and turned it into a parallel resource it > seemed like the disk buffer could never be big enough. I have trouble seeing how parallelizing buffer management could harm things, but who knows. > > From what I hear, the 8xx stuff is not the direction to head, > it deals with the problem by distributing the data across platforms, > and we are not yet maxing out the current hardware. > > 9xx is supposedly too new and green. Not that it's my decision to > make, but I got burned that way when I put up 7.0 back in 1995 and > later had to downgrade the system. 7.x probably was too new to beat on that hard in 95, Though I can't imagine a 5.x based machine circa 1995 that could accomodate that kind of I/O. > > The KISS rule seems to indicate 7.3. > > I'd love to hear some real feedback from users who have gone this > route, particularly supermarkets and other places where barcode > readers are jamming a stream of data into a central database. The most effective thing I know of to do is get a disk array with a real big (2 to 4 GB) static cache memory (like EMC or DG Clariion...), and do staged writes to the logs, that's where I would expect the worst bottlenecks to be. > > cheers, > > Will Roper

> Hmmm... > A million I/O's per second. Thats a lot. About 3.6 Billion per hour. > Or between and 60 and 360 Billion pagereads per hour, depending on your > BUFFERS hit ratio. And that charitably assumes all Reads on an OLTP > system. And a mere 800 users are generating it. That must be some > shockingly bad SQL. > > Now admittedly, I have no hands on experience with 4.x Informix > releases. But either 4.x was a couple of decades ahead of its time > (and the corresponding hardware to support it...), your info is > inaccurate, or you sir are a troll. Which is it? > > greg Well, I've been called a lot of things, but never a troll, so I don't think that's it. The million is a max - on the systems that do that, the daytime average was more like 400k, evenings about 250k. If it got higher I never knew, because sar would barf and start telling me negative numbers. But one must, after all, tune for peak performance. About 3% writes. And yes, 4.1 was way ahead of its time, it was basicly the core of the 5.0 engine, which we have it in use on 4 continents, having found a way to scale it quite nicely. The bottlenecks that I had seen in 7.0 were in disk buffer management. In the old 4.1/5.0 design it was basicly a serial resource; when they added the multi-threading and turned it into a parallel resource it seemed like the disk buffer could never be big enough. From what I hear, the 8xx stuff is not the direction to head, it deals with the problem by distributing the data across platforms, and we are not yet maxing out the current hardware. 9xx is supposedly too new and green. Not that it's my decision to make, but I got burned that way when I put up 7.0 back in 1995 and later had to downgrade the system. The KISS rule seems to indicate 7.3. I'd love to hear some real feedback from users who have gone this route, particularly supermarkets and other places where barcode readers are jamming a stream of data into a central database. cheers, Will Roper