Best practice for MSSQL on linux?

[killmasta93]

Hi,
I was wondering if someone could share some success stories or recommendation on what type of configuration i should put on proxmox? Currently i put disk cache write back, i saw that the SQL uses more reads then writes, i tried the write though but it was not as good as the write back. Not sure what else could influence? Currently running MSSQL on linux
Thank you

 

[LnxBil]

Depends on a lot of things:
- backend storage type
- settings of backend storage (e.g. blocksize)
- frontend storage type
- settings fronend storage (e.g. blocksize)
- number of virtual CPUs
- NUMA nodes
- overall memory settings
- linux distribution for MSSQL
- ...

 

[killmasta93]

Thanks for the reply, currently have a dell 620 with 24 cores and 48 gigs of ram, so how would you recommend?

 

[LnxBil]

Thanks for the reply, currently have a dell 620 with 24 cores and 48 gigs of ram, so how would you recommend?

I should have written my text as question. Just imagine my bullet points are questions and please answer them. Your physical machine is capable (please post also your hardware storage setup), but the settings in software are more important now.

 

[killmasta93]

Thanks for the reply, sorry will answer them

 backend storage type

ZFS

settings of backend storage (e.g. blocksize)

i installed the vms on the rpool

- frontend storage type
- settings fronend storage (e.g. blocksize)

not sure what you mean here?

number of virtual CPUs

12 cpu 2 sockets and 6 cores with numa=1

 overall memory settings

32 gigs of ram

linux distribution for MSSQL

ubuntu 16.04

Forgot to mention on the current machine its a dell r620 is on a raid 10 zfs 7200rpm
but were going to to put proxmox on a proliant dl380 gen9
with

600GBsas disk 15k rpm
600GBsas disk 15k rpm
600GBsas disk 15k rpm
600GBsas disk 15k rpm
600GBsas disk 15k rpm
2tb sas 512E disk  7200rpm
2tb sas 512E disk  7200rpm
2tb sas 512E disk  7200rpm
2tb sas 512E disk  7200rpm
1.8tb SAS 512E DISK 10k rpm
1.8tb SAS 512E DISK 10k rpm
1.8tb SAS 512E DISK 10k rpm
1.8tb SAS 512E DISK 10k rpm
1.8tb SAS 512E DISK 10k rpm

maybe might be an advantage on the 15krpm the mssql?

 

[LnxBil]

maybe might be an advantage on the 15krpm the mssql?

Sure. The faster, the better.

- frontend storage type
- settings fronend storage (e.g. blocksize)

not sure what you mean here?

The settings inside of your VM. Do you use VirtIO, SCSI with VirtIO, SATA, IDE. Also, what filesystem do you use inside of your guest. I suppose you're running a "real" VM and not a container?

 

[killmasta93]

Thanks for the reply, correct i use VirtlO and the filesystem inside the VM is ext4 with writeback cache

 

[LnxBil]

VirtlO

VirtIO or SCSI with VirtIO SCSI controller? The later is the preferred option.

 

[killmasta93]

Thanks for the reply VirtIO with VirtIO SCSI controller

 

[LnxBil]

Thanks for the reply VirtIO with VirtIO SCSI controller

So, only VirtIO. In order to use the VirtIO SCSI controller, you need to set your disks to SCSI.

 

[killmasta93]

Thanks for the reply, im pretty sure the disk needs to be VirtlO or am i wrong? see picture.
https://imgur.com/a/gnAG1TF

 

[udo]

Thanks for the reply, im pretty sure the disk needs to be VirtlO or am i wrong? see picture.
https://imgur.com/a/gnAG1TF

Hi,
no - the disk should be scsi but the scsi controller virtio scsi...

Udo

 

[killmasta93]

Hi,
no - the disk should be scsi but the scsi controller virtio scsi...

Udo

Thanks for the reply, so in theory the scsi is better then Virtio in the disk settings?

 

[LnxBil]

Thanks for the reply, so in theory the scsi is better then Virtio in the disk settings?

Not only in theory, also in practise (is it better) - but with the VirtIO SCSI controller.

 

[killmasta93]

Thanks for the reply, so howcome on the disk it also shows VirtlO so always choose SCSI?

 

[LnxBil]

Thanks for the reply, so howcome on the disk it also shows VirtlO so always choose SCSI?

If I create a new VM in PVE 6 and choose Linux as the OS, it'll automatically use VirtIO SCSI Controller and SCSI as the disk type:



Is this what you mean?

 

[killmasta93]

thanks for the reply, yes correct so by default i should leave it like this? always?

 

[LnxBil]

so by default i should leave it like this? always?

The system is designed with best practise in mind, so yes.

Otherwise, you know what you're doing :-D

 

[killmasta93]

Thanks for the reply, duly noted, but out of curiosty how come it also shows on the disk to use VirtiO?

 

[LnxBil]

it also shows on the disk to use VirtiO?

What do you mean? In my screenshot, it shows SCSI.