How to configure persistent memory for SQL Server on Linux

With the release of SQL on , Microsoft introduced persistent memory (PMEM) support on Linux. This is an exciting development, as previous versions of on Linux didn't support PMEM. Let's look at how to configure the PMEM for on Linux.

SQL 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 .

SQL 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 device using efficient user-space memcpy() operations. Rather than going through the file system and 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 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.

The post How to configure persistent memory for SQL Server on Linux appeared first on SQL Server Blog.

 

This article was originally published by Microsoft's ITOps Talk Blog. You can find the original article here.