When Steve Bradshaw decided to test an assertion made in his previous piece for PowerWire, he made a rather pleasing discovery…
In my last article, I mentioned how surprised I was that in v7.2 of IBM i, the SQL Query Engine (SQE) now supported Query/400 and Open Query File (OpnQryF). This simple fact meant that your applications could run faster without you making a single change to them.
This seems to have resonated with a lot of you, as I have been asked the same question over and over: “How much faster?”
Naively, I agreed to test it out. Let me state for the record that your mileage may vary but – I kid you not – my test (based on a real-world data set) showed a performance increase of 17 times.
Seriously, a Query/400 query that was taking seven minutes to run at v6.1 executed in 25 seconds at v7.2. No changes were made to the hardware, the configuration, the data or the Query itself; the only change was the upgrade to v7.2 of IBM i.
Many of you will have heard this sort of claim before. Like me, when you see such stats bandied about, you probably think they are marketing hype and that even if they were true, it would only be in a very specific set of circumstances – a set that are unlikely to apply to you.
Put it another way, and allow me to borrow at this point from Benjamin Disraeli: “There are three kinds of lies: lies, damned lies, and statistics”.
There is no such fudging of the figures here, though. I took a horrible Query/400 query that runs on real-world data and recorded how long it took to execute at v5.4, v6.1, v7.1 and v7.2 of IBM i. This was run on the same server every single time with the same processor, RAM and disk configuration, and using the same controls. The only variable was the operating system level.
This is not to say that every part of your application will run 17 times faster at v7.2. That is clearly not the case but many of you depend upon Query/400 and OpnQryF for key parts of your daily working life in IBM i. IBM has acknowledged this by optimising them in the same way that they would more contemporary functions like stored procedures and embedded SQL.
Many of you may still be sceptical, suspicious or, hopefully, just a bit curious, so here is a little more detail about what I did in my experiment.
Firstly, I took an entire system save from an existing (real world) v5.4 workload and reloaded it onto a small Power6 server with the following characteristics:
• 8203-E4A Power6 520 server
• single LPAR
• 1 x P6 Core
• 8GB main storage
• 6 x 139GB 15K SAS disks (mirrored)
• entry-level disk controller
The idea for using the same hardware with the same configuration was to rule out any factors that alternate hardware might apply.
I employed the following controls throughout the experiment to try and make the tests as consistent as possible:
• the query I used and the data set I used were always the same
• at no time during the experiment were any other workloads placed on the server
• at no time during the experiment was the data set changed in any way
• no other application functions were running during a test
• no other users where signed on during a test
• no other sys admin functions were run during a test.
After each test, the system would be IPL’d and allowed to settle for at least 30 minutes before the test was repeated to confirm execution time was consistent.
A current set of cumulative and group PTFs were applied to every release of IBM i we tested. After each test was completed and verified, we upgraded the operating system to the next release, applied PTFs and allowed the system to settle for 12 hours before starting the test.
At this point I would like to thank my colleague Graham Street, a seasoned RPG programmer and sys admin, who foolishly said he would like to learn more about the PTF and IBM i upgrade process.
Over the last week, he has ably assisted in more upgrades and PTF installs than in all the previous 30 years of his career. Thank you Graham. You are now in the special club of people who know what it is like to watch a server IPL to install PTFs at 4am. And let this be a warning to you, dear reader. Think before you say yes to anything.
The hardware test
Some of you may be thinking that it would be way easier to just upgrade the hardware and get the same results. I too was curious. So, once again, I got the test servers out. Using the same v5.4 environment, I tried the following upgrades to see what difference they would make.
|New Query/400 execution time
|Reduction in Query/400 execution time
|Added an extra 8GB RAM to the LPAR
|Added a faster disk controller and 20GB RAM to the LPAR
|Added a faster disk controller, 20GB RAM & 2nd Processor
In short, not a lot. This system was only doing one thing and it was of a reasonable specification for this process. So it was no surprise that the hardware upgrades had a minimal effect. The LPAR was not constrained by memory, I/O or processor. It was just the efficiency of the IBM i CQE database that was the bottleneck and the v7.2 upgrade blew that away.
I don’t want you to think that hardware upgrades never help. In the real world, the bottleneck is often the hardware. The simple fact is that IBM i has always been self-optimising and the constraint almost always gets pushed out to the hardware. In fact, I can’t recall an occasion where I have completed a hardware upgrade for a client and not seen a tangible improvement.
But wouldn’t it be nice if IBM gave you a tool that would look at your current workload and then told how much better your system would be if you added more memory, disk, processor or SSD? Good news folks. They have and its in v7.2 (more about that in a later article).
Nice to see you
Finally, it was great to see so many of you at the International i-Power event in Windsor in June. The atmosphere was fabulous and I can honestly say I’ve never had that much fun with a gong before. And with that many big brains in the room it was hard to know where to go next to get the latest tips and tricks for IBM i.
We had great fun putting the event together and if I did actually see you there in person, let me say thanks to you again (especially if you brought me a beer) and if I did not see you, then I truly hope you will come join us next time and maybe I’ll buy you one. More details at https://www.i-ug.com