How to monitor SQLServer on Windows with Prometheus

TL;DR

  • Enable SQLServer agent in SSMS
  • Install the Prometheus Windows exporter from github the installer is in the Assets section near the bottom of the page
  • Install Prometheus scraper/database to your monitoring server/laptop via the appropriate installer
  • Point a browser to the prometheus server e.g. <linux-prometheus-server-ip>:9090
    • Add a new target, which will be the Windows exporter installed in step.
    • It will be something like <SQLSERVERIP>:9182/metrics
    • Ensure the Target shows “Green”
  • Check that we can scrape SQLserver tranactions. In the search/execute box enter something like this
    rate(windows_mssql_sqlstats_batch_requests[30s])*60
  • Put the SQLserver under load with something like HammerDB
  • Hit Execute on the Prometheus server search box and you should see a transaction rate similar to HammerDB
  • Install Grafana and Point it to the Prometheus server (See multiple examples of how to do this)
Continue reading

How to generate load on MicrosoftSQL SQLserver on Windows from HammerDB on Linux

HammerDB on Linux driving load to Windows SQL Server

Often it’s nice to be able to drive Windows applications and databases from Linux, especially if you are more comfortable in a Unix environment. This post will show you how to drive a Microsoft SQL Server database running on a Windows server from a remote Linux machine. In this example I am using Ubuntu 22.04, SQLserver 2019, Windows 11 and HammerDB 4.4

Continue reading

QCOW 3 Ways

How to mount QCOW images as Linux block devices

tl;dr
  • guestmount (requires libguestfs-tools) sudo guestmount -d <vm-name> --ro -i <mountpoint>
  • qemu-nbd (requires the nbd driver)
    • Load the kernel module modprobe nbd max_part=8
    • Bind the device to the image qemu-nbd --connect=/dev/nbd0 <vmdiskimage.qcow>
    • Assuming partition #1 is the target mount /dev/ndb0p1 /a
  • loopback mount. Requires converting qcow to raw
    • Convert qcow to raw qemu-img convert vmdisk.qcow2 -f qcow2 -O raw vmdisk.raw
    • Create a loopback device losetup -f -P vmdisk.raw
    • Locate name of loopback device losetup -l | grep vmdisk.raw
    • Mount (assuming partition #1 on loopback device 99 mount /dev/mapper/loop99p1 /a
Continue reading

Using cloud-init with AHV command line

TL;DR

  • Using cloud-init with AHV is conceptually identical to using KVM/QEMU- we need to use a few different tools with AHV
  • You will need a Linux image that is configured to use cloud-init. A good source is cloud-images.ubuntu.com
  • We will create a cloud-init textual file and create a mountable version using the cloud-localds tool on a Linux host
  • We will attach the cloud-init enabled ubuntu image and our cloud-init customization file to the VM at boot time
  • At boottime ubuntu will access the cloud-init data mounted as a CDROM and do the customization for us
Continue reading

Comparing RDS and Nutanix Cluster performance with HammerDB

tl;dr

In a recent experiment using Amazon RDS instance and a VM running in an on-prem Nutanix cluster, both using Skylake class processors with similar clock speeds and vCPU count. The SQLServer database on Nutanix delivered almost 2X the transaction rate as the same workload running on Amazon RDS.

It turns out that migrating an existing SQLServer VM to RDS using the same vCPU count as on-prem may yield only half the expected performance for CPU heavy database workloads. The root cause is how Amazon thinks about vCPU compared to on-prem.

Benchmark Results

HammerDB results from RDS and Nutanix
Continue reading

Single threaded DB performance on Nutanix HCI

tl;dr

A Nutanix cluster can persist a replicated write across two nodes in around 250 uSec which is critical for single-threaded DB write workloads. The performance compares very well with hosted cloud database instances using the same class of processor (db.r5.4xlarge in the figure below). The metrics below are for SQL insert transactions not the underlying IO.

Single threaded commit heavy insert rates. Latency as seen from SQL insert statement.
Continue reading

AHV Tip: Shutdown multiple VMs in parallel

Often in my lab I want to shutdown a large number of VMs quickly. In the example below I submit the power-off command for a maximum of 50 VMs in parallel. Be aware that we’re using the command line, and in line with true Unix philosophy the OS will assume we know what we are doing and obey us completely and immediately. In other words pasting the below commands to your CVM will immediately shutdown all powered on VMs.

 for i in $(acli  vm.list power_state=on | awk '{ print $(NF) }' |tail -50); do acli vm.off $i &  done

How to deploy Ubuntu cloud images to Nutanix AHV

In this example we use the KVM cloud image from the Canonical Ubuntu image repository. More information on Ubuntu cloud images is on the canonical cloud image page. More detail on the cloud image boot process and cloud-init here: Ubuntu UEC/Imanges.

We can use the Ubuntu cloud image catalog, and specifically use one that has been built to run on KVM. Since AHV is based on KVM/QEMU Nutanix can use that image format directly without any further conversion.

Using a cloud image can be a quicker way to stand up a particular version of Linux without having to go through the Linux installation process (choosing usernames, keyboard types, timezones etc.). However, you will need to pass in a public key so that you can login to the instance once it has booted.

Continue reading

Nutanix Performance for Database Workloads

We’ve come a long way, baby.

Full disclosure. I have worked for Nutanix in the performance engineering group since 2013. My opinions are likely biased, but that also gives me a decent amount of context when it comes to the performance of Nutanix storage over time. We already have a lot of customers running database workloads on Nutanix. But what about those high-performance databases still running on traditional storage?

I dug out a chart that I presented at .Next in 2017 and added to it the performance of a modern platform (AOS 6.0 and an NVME+SSD platform). For this random read microbenchmark performance has more than doubled. If you took a look at a HCI system even a few years back and decided that performance wasn’t where you needed it – there’s a good chance that the HW+SW systems shipping today could meet your needs.

Much more detail below.

Continue reading

Using rwmixread and rate_iops in fio

Creating a mixed read/write workload with fio can be a bit confusing. Assume we want to create a fixed rate workload of 100 IOPS split 70:30 between reads and writes.

Don’t mix rwmixread and rate_iops
TL;DR

Specify the rate directly with rate_iops=<read-rate>,<write-rate> do not try to use rwmixread with rate_iops. For the example above use.

rate_iops=70,30 

Additionally older versions of fio exhibit problems when using rate_poisson with rate_iops . fio version 3.7 that I was using did not exhibit the problem.

Continue reading