Proxmox with SQL issue?

[killmasta93]

Hi,
I was wondering if someone else has had the same issue? Recently i did a p2v windows server 2012 to proxmox which has a sql server 2012. There is an accounting program which the users connect to RDP and work with it. The issue is that the users are blaming that its slow every since i did the p2v which i know its not the issue. Because i did a stress test on the disk max was 3.5gigs while the whole week max disk reads and writes were around 2.5mb. Is there something i missed on proxmox that may affect the sql?

Thank you

 

[dcsapak]

what exactly did you benchmark and how? also how does the config/storage setup look like?

sql servers usually need 4k sync write performance and depending on the storage, this can be tricky (e.g with lvmthin)

 

[WhiteStarEOF]

What is your underlying disk setup, and virtual disk type for the VM?

P2Vs are terrible, terrible beasts. Often times there can be services that are tied to hardware drivers that can slow the VM down, sometimes drastically. Check for services that are specific to the brand of server that it used to run on.

SQL Server also has an internal timer that drives up context switches on the CPU. Most of the time, this can safely be disabled using the T8038 flag.

 

[killmasta93]

what exactly did you benchmark and how? also how does the config/storage setup look like?

sql servers usually need 4k sync write performance and depending on the storage, this can be tricky (e.g with lvmthin)

Thanks for the reply, my VM is running zfs with writeback cache. I have 4 disks, 2 disks RAID 1 for the rpool only and the other 2 disks RAID 1 vmdata which are hosted the virtual machines. and my fsync is around 40,000 (yes I did zpool disable sync but I have a huge UPS and 3 types of backups) I did a benchmark testing with crystal disk mark. Thinking it was a read/write issue, when you say 4k sync write performance would it be configuration on the virtual disk?

Thank you

 

[killmasta93]

What is your underlying disk setup, and virtual disk type for the VM?

P2Vs are terrible, terrible beasts. Often times there can be services that are tied to hardware drivers that can slow the VM down, sometimes drastically. Check for services that are specific to the brand of server that it used to run on.

SQL Server also has an internal timer that drives up context switches on the CPU. Most of the time, this can safely be disabled using the T8038 flag.

Thanks for the reply, i know did not know how bad this could be, is there a way i can troubleshoot the issue? currently running a HP server HPE ProLiant ML310e Gen8 v2 Server. As for the safely be disabled using the T8038 where would i need to look to disable it?

Thank you

 

[WhiteStarEOF]

is there a way i can troubleshoot the issue? currently running a HP server HPE ProLiant ML310e Gen8 v2 Server.

I think the best way to troubleshoot P2V servers is just to go into the VM, to Services, and just go down the list of services looking for any service that is either specific to HP, or specific to a piece of hardware that was part of the physical server, like the RAID controller, or backup device. You might also check Add Remove Programs to see if applications for managing such hardware are installed. Uninstall them if you find any.

For safely disabling SQL Server's timer, this is harder to determine. You can look at the tables in your database, and see if there are any time fields that require time stamps to a finer detail than millisecond. I think Windows' timer goes to millisecond, and SQL Server does nanosecond. Or if it's a database that supports a specific piece of software, you can ask the vendor if disabling the timer will affect their application.

Also, the thing that can be very misleading about disk performance is that most benchmark software looks at MB/s and calls it performance. But latency also plays a big part in it. How long does it take for any given read/write request to complete.

Brent Ozar has a good article for testing disk performance.
https://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

 

[killmasta93]

I think the best way to troubleshoot P2V servers is just to go into the VM, to Services, and just go down the list of services looking for any service that is either specific to HP, or specific to a piece of hardware that was part of the physical server, like the RAID controller, or backup device. You might also check Add Remove Programs to see if applications for managing such hardware are installed. Uninstall them if you find any.

For safely disabling SQL Server's timer, this is harder to determine. You can look at the tables in your database, and see if there are any time fields that require time stamps to a finer detail than millisecond. I think Windows' timer goes to millisecond, and SQL Server does nanosecond. Or if it's a database that supports a specific piece of software, you can ask the vendor if disabling the timer will affect their application.

Also, the thing that can be very misleading about disk performance is that most benchmark software looks at MB/s and calls it performance. But latency also plays a big part in it. How long does it take for any given read/write request to complete.

Brent Ozar has a good article for testing disk performance.
https://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

Thanks for the reply, i will try to disable any service or driver that was previous installed lucky for me no RAID driver was installed as its was bios RAID. i saw the link that it uses Crystal disk, mentioned that the important part is the 4k? as for the virtual disk writeback is good enough for the cache?
I will ask my vendor if he can assist me on the sql timer, so in other words what could cause an issue that the timer of sql and the windows are conflicting?

Thank you