Informix

 View Only
Expand all | Collapse all

How many aio/KAIO

  • 1.  How many aio/KAIO

    Posted Fri November 26, 2021 03:20 AM
    Hello All

    When using KAIO, what is recommended for vpclass CPU and AIO please ?

    Thanks

    ------------------------------
    John Smith
    ------------------------------


  • 2.  RE: How many aio/KAIO

    IBM Champion
    Posted Fri November 26, 2021 10:51 AM
    As for AIO VPs, you will likely need between 2 and 6 AIO VPs with KAIO enabled and DIRECT_IO set to 4 (temp dbspaces processed with KAIO) rather than 0 or 1 if your temp dbspaces are not on RAW disk. If your temp dbspaces are cooked and DIRECT_IO is set to 1, you may need more. AIO VPs are used to process flat file IO like the message and console logs. writing to disk for SET EXPLAIN, and temp dbspaces not using KAIO. The bottom line is to configure a reasonable value and monitor the onstat -g iov report. You want to see at least one AIO VP with an io/wkup less than 1.0. If all are 1.0 or greater you need more AIO VPs. If many are less than 1.0, especially if some are 0.0 then you have more configured than you need.

    For CPU VPs - you need as many as you need to get the work done and keep the server responsive to users. How to know? I monitor the ready queue (onstat -g rea -r 1) at 1 second intervals during peak load. You want to see that there are fewer sqlexec sessions waiting in the ready queue than there are CPU VPs and that no session remains in the ready queue for more than a second or two. Note that in my experience, a CPU VP can take advantage of between 500 and 750 MHZ of processing cycles. So if you have a 4GHZ processor the main hardware thread can handle as many as6 or 8 CPU VPs (Ignore x86 Hyperthreads, they are only about 25-40% of the main thread and activity on the hyperthread will slow processing on the main thread). Indeed it is best to disable hyperthreading on Intel processors for Informix. On IBM/AIX Power processors, the first half of the SMT threads will perform at close to 80% or the primary thread so IBM recommends disabling the second half which don't perform as well for database systems like Informix, so on a Power8 or Power9 processor with 8 SMT threads available you should set the cores to SMT4 mode. On Sparc/Solaris systems you can count all of the hardware threads as if they are physical cores as they perform nearly as well as the primary thread.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: How many aio/KAIO

    Posted Mon November 29, 2021 04:31 AM
    Hi Art
    thanks a lot, :)
    just wanted to confirm for power8/9, should put core with SMT4 mode, and set 4 CPU vps per core ?
    i read somewhere that it"s better with SMT2 and 2 CPU vps / core, think was on of your or lester"s presentaion :)
    thanks

    ------------------------------
    John Smith
    ------------------------------



  • 4.  RE: How many aio/KAIO

    IBM Champion
    Posted Mon November 29, 2021 08:53 AM

    Art:

    The next time I get a chance to do some serious benchmarking, I'll have to test that out. In my previous rounds of testing, I found that with x86 hyperthreading enabled, I could configure the system as if it had about a third more cores than it actually did, which is right in line with your 25-40% figure. But it didn't occur to me to disable hyperthreading and "overload" the CPU VPs on the system. In my test setup, with hyperthreading enabled, I found I could configure our 16-core machine as though it had 21 cores, configuring 19-20 CPU VPs and not "crowding out" the OS. But in hyperthreading-disabled tests, I never exceeded 15 CPU VPs for testing. Something I'll definitely play with next time.

     

    This raises another interesting hypothetical, though: If you're configuring a new system from the ground up for Informix, licensing concerns aside, would you rather have fewer, faster cores or more cores that are somewhat slower? (A real-world example: 20 cores at 2.4 GHz or 16 cores at 3.1 GHz?)






  • 5.  RE: How many aio/KAIO

    IBM Champion
    Posted Mon November 29, 2021 09:14 AM
    Tom:

    It is a long standing question about whether more processors or faster ones will perform better. 

    I think that for Informix it should be a toss up as long as you configure appropriately. Here is my thinking:
    • A faster core will allow more internal session threads per VP to be productive.
    • Faster cores will also support more CPU VPs per core.
    • In contrast more cores will allow the configured CPU VPs to run with less interference.
    • Hardware threads (which hyperthreads technically are not) skew this a bit.
    • Bottom line is the total number of cycles/sec available, and there should be only a slight difference between two 2GHZ cores and one 4GHZ core.
    • License costs with fewer faster cores versus more cores definitely affects TCO.
    All of this is speculation until we can physically test it. That, I think, is the real loss when IBM decided to cease publishing benchmarks. Comparing apples-to-apples benchmarks of the same database on different platforms, whether DB2, Oracle, MS SQL Server, or Informix gave us bases to extrapolate to Informix performance even on different platforms when selecting one "model" versus another. We don't really have that any longer.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 6.  RE: How many aio/KAIO

    Posted Mon November 29, 2021 09:29 AM

    Until recently I had always found HT off and overloading to be faster than HT on and overloading – for dedicated DB servers.  But now you throw VMs into the mix and I've not found a solid set of rules. The VM config, which I generally have no control over, seems to dictate the final performance.  The last one was HT on, 4 DB VMs and overloading was best until you drove really hard then I never saw any significant delta with HT on/off, overloading on/off  but by then the disk subsystems were giving up.

     

     

    Cheers

    Paul

     






  • 7.  RE: How many aio/KAIO

    IBM Champion
    Posted Mon November 29, 2021 10:19 AM
    I have had similar experiences with VMs, especially on Cloud services. A client of mine didn't get consistent performance behavior at AWS until they contracted for a bare iron server and AWS's fastest IO dedicated exclusively to their servers. 

    During the last Advanced Performance Tuning class Lester and I ran, my VM had the fastest performance on the OLTP benchmark at over 20,000TPM, which was faster than the best runs on Lester's bare iron servers before he sold them off, while the slowest clocked in at about 4,500TPM which was slower than the slowest bare iron server. All five systems were configured identically on AWS. On the physical servers Lester used to use, all seven machines performed within 15% of each other even with the differences between the tuning details between different attendees (including Lester and me).

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 8.  RE: How many aio/KAIO

    Posted Mon November 29, 2021 10:30 AM
    AMD has tried to answer the question of CPU selection for databases in this document:
    https://developer.amd.com/wp-content/resources/56949_1.0.pdf

    It's interesting that they focus just as much on how many sockets the server has and how many NUMA zones are configured.

    Ben.

    ------------------------------
    Benjamin Thompson
    ------------------------------



  • 9.  RE: How many aio/KAIO

    Posted Mon November 29, 2021 04:42 AM
    As Art has already indicated in his post, the answer to this may be platform dependent.

    I have often wondered about the answers to questions like this and am fortunate that our company has invested in a very nice application load testing set up. With this we've provided HCL with some test results which have fed into 14.10.FC6 and FC7 performance enhancements. These versions are much faster in a handful of areas, notably ER in 14.10.FC6+. It's been years since I have been near a non-Linux/Intel system though.

    In our load testing on Linux, we do not see any benefit from increasing VPCLASS CPU beyond the CPU core count, i.e. having multiple CPU VPs per core. Having even two per core may even be marginally detrimental, although our testing finds it doesn't make a lot of difference. I agree about hyperthreading, although we don't switch it off, we use the processor affinity setting to ensure one CPU VP per physical core.

    On Art's statement about a maximum 500-750 MHz of processing cycles per CPU VP, it is not something I observe on our kit. I can only speculate this may be something to do with the bus speed relative to the processor speed on some hardware, or a lack of NUMA.

    Again on Linux if you have a lot of CPUs VPs especially, you should increase kernel parameter aio-max-nr to 2, 4 or even 8 million. This can restrict throughput and there is no metric that exposes I/O requests blocked by this system limit, beyond the raw numbers in your test.

    Ben.

    ------------------------------
    Benjamin Thompson
    ------------------------------



  • 10.  RE: How many aio/KAIO

    Posted Mon November 29, 2021 05:09 AM
    Thanks for your useful post, i noted this on Linux, but for this moment, i'm interested of IBM Power with AIX ;), and unfortunately did"t have to means to do benchmarks, so i'll be thankful of experiences on Power Systems with AIX

    ------------------------------
    John Smith
    ------------------------------



  • 11.  RE: How many aio/KAIO

    IBM Champion
    Posted Mon November 29, 2021 06:49 AM
    John:

    So, I believe that Lester's presentation was written when Power6 was the bee's knees. So, with only 4 SMT threads per core setting to SMT2 to use only 1/2 of the hardware threads was the correct advice. However, with the Power8 processor the number of SMT threads was increased to eight and according to IBM the "disable half of the threads" for monolithic processes still holds, so set SMT4 on Power8 or later. 

    As far as how many CPU VPs, since you can treat each active SMT thread as a real processor, start out with one CPU VP per SMT thread, so if you have two processors in your box configure 8 CPU VPs which is, yes, four per core or one per thread. If you see the ready queue backing up (see my notes in my original response for interpreting that queue), you can try increasing that to two per thread.

    On Ben's experience, here are my comments:
    • My formal testing (which included several others performing the same tests on their systems) was long ago. The Informix code has been vastly improved making it faster and so maybe better able to utilize the available cycles. In addition, the IO systems we are using today are at least 3X faster given the advantages of SSD drives and that means fewer wait states so more processing gets done. That said, keep reading.
    • I continue to make the same recommendations because of my continuing field experience. Example: At a client using a Sparc system with 48 hardware threads (which are a bit better even than the Power threads) they were struggling using 48 CPU VPs. I suggested increasing that to 96 VPs and their ready queues have been empty ever since (OK except for that weird incident when one of their applications went berserk and spawned over 20,000 copies of itself).
    • Note that if the existing CPU VPs are keeping the sessions serviced, ie the ready queue always has fewer waiting sqlexec threads than the number of CPU VPs and they are all leaving the queue within a second of two, then more CPU VPs will not improve things at all. More VPs don't make any session run faster (unless you are running high PDQPRIORITY data warehouse sessions) they only serve to process more threads concurrently. So, I don't know how Ben was stress testing, so I cannot discount his results, however, see the next point.
    • Intel processors have always been the least powerful of the major CPUs out there. In the testing we did back in the mid  '90s they were always at the high end of the range. At the time the range I recommended was 450-1000MHZ and on Intel recommended using one VP per 1GHZ (1000MHZ) while on Sparc using one VP per 450MHZ. So that doesn't diverge as much from Ben's test results as it would seem.
    • Lester and I run his Advanced Performance Tuning course once or twice a year. Part of that is tuning for the open source OLTP benchmark running 1000 concurrent user sessions on an Intel server with four physical cores. There is always a performance improvement experienced by increasing the number of CPU VPs from 1 per core to two per core. Less so for four per core, but still an incremental improvement in total throughput counted in number of completed transactions per minute. Note that the test session threads are also being executed on the same box which is not ideal for true benchmarking, but does give one an idea that if those sessions were moved to a separate box the performance improvement from more VPs might be even more dramatic. 
    Ben: Thanks for joining the discussion. Your experience with massive OLTP systems is valuable to the community as is the feedback you give to the Informix development team.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 12.  RE: How many aio/KAIO

    InnerCircle
    Posted Mon November 29, 2021 10:32 AM
    Interesting discussion. I don't agree with a lot of things said here, mostly about hyperthreading and "overloading" cores with Informix CPUVPs, but, whatever works for some (test) environments has a right to be presented, I guess.

    Some AIX / POWER comments.

    On POWER you are very likely to become familiar with NUMA (Non Uniform Memory Architecture). It's not something specific to this hardware platform, but higher likelihood is from the fact that POWER usually has less cores per CPU (socket or node) compared to AMD/Intel and even SPARC.

    In that environment (and for certain types of workloads) it's frequently not about how many cores you give to Informix, but about where these cores (and memory) are coming from.  Make sure you become familiar with output from "lssrad -av" and understand "affinity domains", and most importantly, never run Informix on CPUs and memory coming from different CECs, no good will come out of it.

    Next thing to consider is the optimization goal - namely "throughput" or "response time". For the latter you'll want lower SMT, but for the throughput - everything will be fine up to SMT-8 (unless you run into NUMA)
    The "sweet spot" for Informix is between one and two active sessions per CPUVP (best efficiency), but Informix on POWER handles higher load quite well if you run Informix on resources with maximum degree of affinity.

    One more thing to consider is the I/O to CPU ratio. For most deployments on the current hardware CPU capability makes it very likely to hit I/O limit for the storage subsystem. You must make every effort to fit the "working set" in memory (Informix buffer pool). If that's not possible - the flash storage (or any storage that can provide sustained 200k IOPs or better) is the only answer.

    In terms of testing methodology, if it comes to that, there is noticeable difference in running Informix and "client" on the same cores vs them using separate resources. Get familiar with "rsets" and very useful commands like "execrset", etc. 

    It is recommended running the instance in dedicated capped LPAR using resources with maximum possible affinity for consistent and predictable performance. 

     


    ------------------------------
    Vladimir Kolobrodov
    ------------------------------



  • 13.  RE: How many aio/KAIO

    Posted Mon November 29, 2021 11:32 AM

    Agreed, the key is to test in your world.

     






  • 14.  RE: How many aio/KAIO

    IBM Champion
    Posted Mon November 29, 2021 12:06 PM
    Thanks for adding to the discussion Vladimir. A comprehensive Tuning Informix (or RDBMS in general) on Power8/9/10 White Paper based on benchmarks would be very useful for the community. Perhaps you would take a shot at such a document?

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------