Unanswered: DB2 performance problem: cpu speed value?
I'm about to move a DB2 application from a laptop (core i5 mobile with DB2 9.7 FP6 running inside a VMware VM with Win2k8) to a "real" server.
The server runs Win2k8 Standard 64bit and is a VM on a KVM with 32 GB RAM and 4 vCores of a XEON. So much better hardware than on the laptop.
I installed DB2 10.5 FP3 and setup the database and the application.
Altough the server has much more RAM and computing power, the application and the DB are running significantly slower.
I just checked the CPU speed value of DB2 and it says:
on the server (DB2 10.5) CPUSPEED = 8,0e-007
on the laptop (DB2 9.7) CPUSPEED= 1,7e-007
I just installed DB2 10.5 on a physical server with Core i3 and it gets CPUSPEED=3,2e-007.
As I understand it, the lower the value, the better.
Why is the laptop cpu performing so good compared to the server?
Is this a difference in the DB2 versions or does a value of 8,0e-007 point to a CPU performance problem?
My guess it has more to do with the different DB2 versions than CPU, although the CPU value for the XEON processors seem high. You might what to force a recalculation of the value. Also make sure runstats is up to date and then rebind all packages.
Is the database on the laptop exactly the same size as the one on the server? what is that size in gigs?
How did you get the database on to V10.5, was it via a redirected restore? If so, did you do any post-restore tuning after the v9.7 to v10.5 upgrade?
Have you measured the I/O throughput that you really get in your virtual server? If you did, what were the results?
In your previous post you stated that the access plans were the same, but did not state how you proved this. Specifically, if using dynamic SQL, did you compare the db2exfmt output on the laptop and on the serveR?
Did you compare the dbm cfg, and the db cfg , and the db2set -all between the machines and resolve or understand all the differences?
I'm currently about to do a deep comparison between the laptop and the server.
Up to now I compared the dbm cfg and the only difference between laptop and server (apart from the db2 versions 9.7 and 10.5) is the cpu speed value.
I just installed a plain win2k8 machine and DB 10.5 without any Database configurations or data imports. I just looked up the cpuspeed value and it rates much better on the laptop than on the server. I recalculated it and it stays the same :-(
What cpu speed values do you have?
I'm just about to re-setup the server with the database and the application on top of it. the database only has 50.000 tuples and 20 tables.
I agree with Andy on that one. I have never even paid attention to what any of my machines ever had for that value.
Most of your performance is going to be based on your BP configuration, sort space/heaps, indexes, statistics, organization of your tables and how well your SQL is written.
Believe me, you can have everything set up on DB correctly, all tables perfectly organized, and up to the minute statistics, with all of the indexes you supposedly need for joins and for accessing data and we can write some SQL statements to bring your machine to its knees.
cpuspeed cfg value is just used for db2 optimzer to calulate cost of the execution plan。it should not be the cause of your problem.
Maybe db2 v10.5 and v9.7 have different thoughts about the cpu speed.
As db2mor metioned above, plz provide the full execution plan of your sqls(db2exfmt output). and if you could, provide the output of table function
mon_get_pkg_cache_stmt will be better. for example:
select * from table(mon_get_pkg_cache_stmt(null,null,null,-2)) t
where stmt_text like '%<part of your sql>%'