In this post, I want to share a case I worked recently to increase a query speed from 3~4 sec to 0.3~0.4 sec.
An user compared the query loading speed for a small size of VPS vs a large siez of VPS.
The spec for small size VPS
The spec for large size VPS
Small VPS processes 0.5 sec and large VPS processes 3~4 sec for a query with small no of data rowset. By using EXPLAIN command, the query needs to process 51k of data row. It does not really looks much and SSD disk can handle well but why slow?
Let do some tune of this large size VPS by adjusting KVM cpu model to host-model. There are 2 cpu model of host-model and host-passthrough. Neither of it produce a siginificant performance difference, so I keep it as host-model. This link consists more of KVM cpu model definition.
I played a little bit on Centos 7 tuned command by setting the recommeded profile “virtual-guest”. Okay. The speed reduced to 0.7~0.8 sec.
User didn’t seem fine with the speed. What’s next?
I have migrated this vps to a different node with different CPU on node level. The speed reduced till 0.35~0.5 sec. Woah. That’s a big difference this time.
Original node CPU – Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz
New node CPU – Intel(R) Xeon(R) Silver 4208 CPU @ 2.10GHz
View the CPU comparison at here. So, the winner here is Intel(R) Xeon(R) Silver 4208 CPU.
In conclusion, CPU performance plays a role on database query performance. Of course, well organized database query with MySQL Indexing can further speed up the query duration.