High CPU VM SQL TPC-C Test

[replicar]

Hi guys, I'm trying to conduct a test on a high CPU VM using SQL Server TPC-C config. I've setup 200 warehouses, ran 150 users through them, but I get the same results whether the machine is 96 CPUs or 224 CPUs. I've got SQL Server Enterprise installed on the VM, which means that I don't have a CPU limitation of standard 48 CPUs, but I'm not sure why I'm not seeing performance boost. Can someone please point me in the right direction?

[billramo]

Hi Dmitry,
You are likely constrained by the SQL Server log in terms of IOPS and/or MB/s with your file system. I'm guessing that with the core count, you have a fair amount of RAM as well on these VMs. Your CPU is not likely the system constraint. The size of the database is way too small to see any core benefit as well since you are not CPU constrained.

You need to start out with a hypothesis that aligns to what your trying to simulate. For example, when I benchmarked TPC-C against AWS and Azure with against 64 vCore VMs, I decided that the number of warehouses should generate a database size that would not fit into memory to simulate a realistic sizing found in the real world scenarios. So I compared systems like the AWS r5d.16xlarge vs. an Azure E64s_V3 instance using Premium storage and used the local SSD for the log files to avoid transaction log bottlenecks. Using warehouse sizes of 1000, 2000, and 8000, the database essentially got cached into RAM and the bottleneck was the Log. Once I got to 30000 warehouses with 377 users and no wait time stressed the system where it started to get CPU constrained.

If you want to see the impact of more cores, you will need a really good flash storage disk system with at least an 8 GB to 1 vCore ratio.

I hope this helps.
Regards,
Bill

[replicar]

Hi Bill,

Thank you for taking time to respond to this. Let me try to explain what I'm trying to accomplish here, and steps I've taken so far. I'm working with GCP, and I was trying to benchmark their offerings of CPU between past generation Intel SkyLake (96 CPUs), new Intel Cascade Lake (80 CPUs), and the AMD Rome (224 CPUs). The VM is using the same SSD for all tests (2TB, 15000 MB/s write), and even the HammerDB setup was same for all test, which was 200 warehouses, an autopilot run of 25/50/75/100/125/150 users. I was seeing pretty decent performance with Intel chipset, seeing utilization go up into 40% at 150 users, but AMD pretty much peaked and went flat at around 50 users with CPU not being able to go up any further. I scaled down the VM from 224 CPUs to 96, and got the same result, same exact peak, numbers within 2 percent of each run.

What I'm trying to accomplish here is max performance test, but it didn't make sense to me that AMD performed so poorly, despite having more than twice the cores, although that would explain the lack of CPU utilization.

[billramo]

Hi Dmitry,
This sounds like a benchmark that I did last year. ;-) The AMD Rome was not available at the time. I have seen "turkey" cloud VMs in the past. You might want to try a different zone or region to run the test again to see if you get the same results.

When I discovered things like this, I reached out to the Product Management team to see if they might know what is going on. Also, someone at Intel - like Steve Shaw - may know of an architectural reason why the instruction set for the AMD Rome processors could be a problem with SQL Server workloads. I would expect that PostgreSQL might have a similar problem. I would look at running PostgreSQL and maybe MySQL to see if the same thing occurs.

I'll poke around with some of my contacts to see what might be happening.
Cheers,
Bill

[replicar]

Thanks Bill, let me know if you discover something.