How to configure persistent memory for SQL Server on Linux
With the release of SQL Server 2019 on Linux, Microsoft introduced persistent memory (PMEM) support on Linux. This is an exciting development, as previous versions of SQL Server on Linux didn’t support PMEM. Let’s look at how to configure the PMEM for SQL Server on Linux.
SQL Server 2016 introduced support for non-volatile DIMMs and an optimization called Tail of the Log Caching on NVDIMM. These leveraged Windows Server direct access to a persistent memory device in DAX mode to reduce the number of operations needed to harden a log buffer to persistent storage.
SQL Server 2019 extends the support for PMEM devices to Linux, providing full enlightenment of data and transaction logs placed on PMEM. Enlightenment is a way to access the storage device using efficient user-space memcpy() operations. Rather than going through the file system and storage stack, SQL Server leverages DAX support on Linux to place data directly into the device. This helps to reduce latency.
Enable enlightenment of database files
The first step to enabling enlightenment of database files in SQL Server on Linux is to configure the devices. In Linux, use the ndctl utility to configure PMEM device and create a namespace.
ndctl create-namespace -f -e namespace0.0 –mode=fsdax* –map=mem
You can verify the namespace using ndctl, as shown by this sample output:
ndctl list
[
{
“dev”:”namespace0.0″,
“mode”:”memory”,
“size”:1099511627776,
“blockdev”:”pmem0″,
“numa_node”:0
}
]
Next, create and mount PMEM device.
With XFS:
mkfs.xfs -f /dev/pmem0
mount -o dax,noatime /dev/pmem0 /mnt/dax
xfs_io -c “extsize 2m” /mnt/dax
With EXT4:
mkfs.ext4 -b 4096 -E stride=512 -F /dev/pmem0
mount -o dax,noatime /dev/pmem0 /mnt/dax
Once your device has been configured, formatted, and mounted, you can place database files in it or create a new database.
Note that PMEM devices are O_DIRECT safe. Therefore, you must enable trace flag (TF) 3979 using mssql-conf utility to disable the forced flush mechanism. This is a server-wide configuration change, so don’t use this trace flag if you have any O_DIRECT non-compliant devices that require forced flush to ensure data integrity.
Now that you know how to use PMEM on SQL Server 2019 on Linux, you can reduce latency considerably. To learn more about what you can do with Microsoft SQL 19, check out the free Packt guide Introducing Microsoft SQL 19. If you’re ready to jump to a fully managed cloud solution, check out the Essential Guide to Data in the Cloud.
