Hi guys, we're planning to move/migrate my MS SQL workload from physical standalone host to a new Nutanix cluster. We already engaged with local Nutanix representative for this. However, we encountered a few hurdles in terms of performance during and we can't be sure whether we have the right sizing for our performance requirement. Current Production Physical host:- Processor: Intel Xeon E5-2690v3 @ 2.6GHz (single socket, 12C 24T) RAM: 96GB (However only 64GB usable for the MS SQL 2008R2 DB) Disks: Database MDF resides in a SSD RAID 5 diskgroup. (6 disks), OS and LDF etc resides in other disk groups, SAS disks 10K, 15K RAID 10, RAID 1 etc. Test Unit ( 3 nodes):- Processor: Intel Xeon E5-2650 v4 @ 2.2 GHz (dual socket, 24C 48T) per node RAM: 512GB per node Disks: 6 x 1.6TB Intel S3610 SATA (All Flash) Hypervisor: AHV 5.9.2.4 Network: 10GbE RJ45 SQL Test VM created in Nutanix test cluster:- 12 vCPU 96GB RAM CVM size: 12 vCPU 32GB RAM Test Result from MS SQL set statistics time on: Questions: We tested different type of queries and compared the results from both the physical host and test units. However, the results was not what we expected. As observed above, the initial run of the SQL in Nutanix took much longer compared to physical host. The initial run will always query from disks and the query will then "cached" in the RAM. Hence second run of the query for both Nutanix and Physical will have almost same result. We have roughly identified that the clock speed of the CPU could be the reason of such difference in terms of speed. But we do not have a very concrete proof to prove this. 1) How can we be sure that it's due to the lower clock speed of the CPU? 2) How do we know what's the maximum performance we can yield from the cluster?

So my first question here is:Are you running AHV or ESXi?If you're running ESXi, I would do to optimize CPU load balancing such as ensuring you have RSS (Receive Side Scaling turned on). If you are using AHV, there is no need for RSS as the VirtIO driver requires no such enhancement. We recently ran into this on our SharePoint farm we recently moved over to AHV. In terms of hardware the only difference was the CPU clock speed. Utilization was extremely nominal (never exceeding 60%) but the decrease in clock speed appeared to make a sizable difference. We decreased by 500MHz per vCPU. You're reduced by 400MHz per vCPU, so you've been reduced by effectively 4.8 GHz. Not to mention you're going from dedicated CPU threads to virtualized, so there is some overhead from the hypervisor for that. My recommendation. Take a look at my storage recommendations below and see if that helps. If you're unable to do that then I would increase the CPU count to at least 14 or 16 cores and split the CPU into 2 sockets and 7 or 8 cores per CPU. This should help you quite a bit with more CPU threads available and get you closer to your experience of what you had on dedicated hardware. Dedicated hardware will always beat virtual resources in a 1:1 comparison. The behavior is completely different from a CPU and storage utilization perspective. Second question:How many databases are on your data drive and how busy are they?One of the lesser know best practices, is to dedicate vdisks to your busier databases. Think of it this way. If you have 20 databases and you have queries constantly reading from disk to build views or really anything with a higher amount of IO, which would be more efficient, 20 databases sharing storage streams for a single vdisk, or 20 databases split between two vdisks, each with their own storage streams. All in all, If you have multiple high IO databases, I would highly recommend using dedicated vdisks for them. I know this may seem ridiculous given you're running on SSD's, but it's not always the speed of your disk, but the available streams that make your storage fast. Having vdisks that exist on different SSD drives will drive your speed up as well as help you take advantage of AHV turbo (If you're using AHV). This should also reduce CPU overhead overall. So you may not need to increase your cores. Separate your logs and tempdb to dedicated vdisks as well. You'll see great improvement if you follow these practices. I'm very fortunate to have one of the very best SQL DBAs on my team (Brad McGehee) https://bradmcgehee.com/We split the logs and tempdb as a part of our standard deployment. We've worked with him closely to improve our SQL environment over the years. And with his status with Microsoft, and in the SQL community I don't argue. I hope this helps some and gets you toward what you're looking for.

问题解答

Nutanix集群如何基于性能要求?

四年前
2019年4月22日
3个回复
4208视图

C级

昆延2020
旅行者
1回复

Hi伙计们,我们正计划移动/移植我MSSQL的工作量从物理独立主机到一个新的Nutanix集群我们已经与本地Nutanix代理无法确定我们是否适合性能需求

当前制作物理主机:-
处理器:Intel XeonE5-2690v3
RM:96GB(然而只有64GB可用于MSSQL2008R2DB)
磁盘:数据库MDF居于SSDRAID5盘组OS和LDF等存放在其他盘类中,SAS磁盘10k、15KRAID10、RAID1等

测试单元(3节点):-
处理器:Intel XeonE5-2650v4
内存:512GB
磁盘:6x1.6TB英特尔S3610SATA
超视像机:AHV5.9.2.4
网络:10GbERJ45

SQL测试VM
12vCPU
96GB内存

CVM大小
12vCPU
32GB内存

测试结果MSSQL设置统计时间

问题:
我们测试不同类型查询并比较物理宿主和测试单元的结果然而,结果并非我们所期望的
上文指出,努坦瓦SQL初始运行比物理宿主耗时长得多。
初始运行总从磁盘查询,然后查询在内存中“缓存”。
第二波Nutanix和Phystem查询结果几乎相同

大致识别出时钟速度可能是速度差异的原因但我们没有非常具体的证据证明这一点

1) 我们如何确定它归结于CPU较低的时钟速度
2) 我们怎么知道从集群中能产生最大性能

一号附加

\rn
rn当前制作物理主机
\r\n进程处理器:Intel XeonE5-2690v3
\r\nRAM:96GB
disks数据库MDF居于SSDRAID5盘组OS和LDF等存放在其他盘类中,SAS磁盘10k、15KRAID10、RAID1等
\rn
\rn
测试单元(3节点):-
\r\n进程处理器:Intel XeonE5-2650v4
\r\nRAM:512GB
\r\nDisks:6x1.6TB英特尔S3610SATA
r/nHypervisor:AHV 5.9.2.4
Network:10GbERJ45
\rn
r\nSQL测试Nutix测试集群创建VM
\r\n12vCPU
r/n96GB内存
\rn
r\nCVM大小
\r\n12vCPU
r/n32GB内存
\rn
msSQL设置统计时间
\rn
\rn

/a+/p
\rn
\r/n问题
r\nWe测试不同类型查询并比较物理主机和测试单元的结果然而,结果并非我们所期望的
\r\nAs上文显示,NutanixSQL初始运行比物理宿主耗时长得多
\r\n初始运行总会从磁盘查询,查询后内存中将
\r\nHence二次查询Nutanix和Chystem都几乎相同结果
\rn
\r\n 我们大致识别到时钟速度 CPU可能是速度差异的原因但我们没有非常具体的证据证明这一点
\rn
\r\n1,我们如何确定它归结为 CPU下钟速度
\r\n2,我们如何知道什么最大性能从集群中产生?,quote用户名:chunyen2020

仿佛
引文
共享

专题结束备注

3个回复

用户级3

+10

dubuque
外乘者
63回复
四年前
2019年4月22日

因此我的第一个问题就是

AHV或ESXI运行

运行ESXi时,我会优化CPU负载平衡,例如确保 RSS开机使用AHV时,不需要RSS,因为Virtio驱动程序不需要增强

最近我们在SharePoint农场碰到这个最近搬去AHV硬件方面唯一的差分是CPU时钟速度使用量极小(不超过60%),但时钟速度下降似乎大有不同500MHz/vCPU降400MHz/vCPU, 有效减4.8GHz

更别提你正从专用CPU线程虚拟化, 高端摄像头有一些高管

我推荐看一下我下面存储建议看那有帮助万一你无法做到这一点,我将把CPU计数增到至少14或16核心数,并分解CPU为2插接和7或8核心数/CPU帮助更多CPU线程并深入了解专用硬件经验

专用硬件总比虚拟资源高一比行为完全不同于 CPU存储使用角度

二题

数据驱动器上有多少数据库繁忙程度

少知最佳实践之一是把disks专用于您的忙数据库

以这种方式想20个数据库共享存储流单盘或20个数据库拆分二维磁盘,每个数据库有自己的存储流归根结底,如果你多高IO数据库,我强烈推荐使用专用disks我知道这看似荒谬,因为SSD运行, 但它并不总是速度磁盘, 而是可用流快速存储拥有分布在不同SSD驱动器上的disks会驱动你加速并帮助你利用AHVturbo并减少CPU总体管理费用免得增加核心

拆分日志并调试专用寄存器听从实践会大有改观最优秀SQLDBA
https://bradmcgehee.com/

分日志调试作为标准部署的一部分多年来我们同他密切合作改善SQL环境并有微软状态 SQL社区

希望这能帮助你实现你所期望的目标

David DubuqueNitix2019 & 2020Natanix技术咨询委成员

\rn
\r\n\t你运行AHV或ESXi
\rn
r\ni运行ESXi时,我会优化CPU负载平衡,例如确保 RSS(接收侧缩放开关)使用AHV时,不需要RSS,因为Virtio驱动程序不需要增强
\rn
\r\nWe最近碰上这个 SharePoint农场我们最近移到AHV硬件方面唯一的差分是CPU时钟速度使用量极小(不超过60%),但时钟速度下降似乎大有不同500MHz/vCPU降400MHz/vCPU, 有效减4.8GHz
\rn
更别提你正从专用CPU线程虚拟化, 超视像机上有一些高管
\rn
\r\nMy推荐看一下我下面存储建议看那有帮助万一你无法做到这一点,我将把CPU计数增到至少14或16核心数,并分解CPU为2插接和7或8核心数/CPU帮助更多CPU线程并深入了解专用硬件经验
\rn
\r\n专用硬件总比虚拟资源强1:1比较行为完全不同于 CPU存储使用角度
\rn
\r/n二题
\rn
\r\n\t
\rn
r/n少知最佳实践之一是为您的buser数据库投放disks
\rn
深思熟虑20个数据库共享存储流单盘或20个数据库拆分二维磁盘,每个数据库有自己的存储流归根结底,如果你多高IO数据库,我强烈推荐使用专用disks我知道这看似荒谬,因为SSD运行, 但它并不总是速度磁盘, 而是可用流快速存储拥有分布在不同SSD驱动器上的disks会驱动你加速并帮助你利用AHVturbo并减少CPU总体管理费用免得增加核心
\rn
\r\n分离日志并试探听从实践会大有改观最优秀SQLDBA
\r\nhttps:\/\/bradmcgehee.com\/
\rn
\r\nWe拆日志并调试多年来我们同他密切合作改善SQL环境并有微软状态 SQL社区
\rn
\r\nI hope this helps some and gets you toward what you're looking for.","quoteUsername":"ddubuque","translations":{"Common":{"like":"Like","unlike":"Unlike"},"Forum":{"Quote":"Quote","Share":"Share"}}}">

C级

昆延2020
写者
旅行者
1回复
四年前
2019年4月23日

HV5.9.2.4测试集群

容我了解你搬进AHV时经历什么样的差异
慢CPU会波及 vdsk速度吗?

我们开始想慢CPU频率产生低量CVM

万一你无法做到这一点,我将把CPU计数增到至少14或16核心数,并分解CPU为2插接和7或8核心数/CPU

初创时已经做过

数据驱动器上有多少数据库繁忙程度

>当前测试集群仅运行我前面提到的单MSSQLVM我们一次只运行1查询

我甚至尝试提高CVM内存从32GB到48GB,但没有性能改善
CVM vCPU高载约50%+-

金题:我们如何知道新集群将购买,将实现我们所期望的速度/性能

\rn
\rnI知你搬进AHV时体验到什么样的差分
slow CPU影响 v磁盘速度
\rn
\r\n我们开始想慢CPU频率有可能产生较低的CVM输出量
\rn
\rn

无法实现时,我将把CPU计数增到至少14或16核心数,并按CPU划分为2插接和7或8核心数
\r\n> 开始已经做
\rn
\rn
\rn
数据驱动器上有多少数据库?
\r\n>当前测试集群仅运行我前面提到的单MSSQLVM我们一次只运行1查询
\rn
\r\nI甚至试图提高CVM内存从32GB到48GB,但没有性能改善
r\nvCPUCM高载约50%+-
\rn
\r\n金题:我们如何知道新集群将购买,将实现我们所期望的速度\/性能?",quoteUsername':chunyen2020

仿佛

引文

用户级3

+10

dubuque
外乘者
63回复
四年前
2019年4月23日

实事求是地说,当我们增加核心计数时,它帮助性能大有帮助if you're already done, I would say 你的问题不是CPU基础

sql服务器测试压力多资源可帮助你模拟Sql服务器加载

i.e.

https://www.brentozar.com/archive/2015/05/how-to-fake-load-tests-with-sqlquerystress/

模拟高存储I/O可使用测试工具调用IOMete
https://www.itprotoday.com/mobile-management-and-security/using-iometer-measure-disk-performance

不确定你用什么测量当前查询时间但我猜是SQL管理演播室自移位后查询有改变吗?

最重要的是,如果这是POC硬件,我会直接与您的销售工程师接触或支持理解可能发生的事情可能有一些优化建议适合你的情况

David DubuqueNitix2019 & 2020Natanix技术咨询委成员

\rn
sql服务器测试压力方面有数项互联网资源可帮助您模拟Sql服务器加载
\rn
i.e
\rn
\r\nhttps:\/\/www.brentozar.com\/archive\/2015\/05\/how-to-fake-load-tests-with-sqlquerystress\/
\rn
\rn
r\n模拟高存储I\O使用测试工具调用IOMete
\r\nhttps:\/\/www.itprotoday.com\/mobile-management-and-security\/using-iometer-measure-disk-performance
\rn
\r\nI'm不确定你用什么测量当前查询时间, 但我猜它SQL管理工作室自移位后查询有改变吗?
\rn
\r\nAbove所有,如果这是POC硬件,我会直接与您的销售工程师接触或支持理解可能发生的事情There may be some optimizations that they recommend specific to your situation.","quoteUsername":"ddubuque","translations":{"Common":{"like":"Like","unlike":"Unlike"},"Forum":{"Quote":"Quote","Share":"Share"}}}">

Learn more about our cookies.<\/a>","cookiepolicy.button":"Accept cookies","cookiepolicy.button.deny":"Deny all","cookiepolicy.link":"Cookie settings","cookiepolicy.modal.title":"Cookie settings","cookiepolicy.modal.content":"We use 3 different kinds of cookies.可选择要接受的cookie我们需要基本cookie使网站工作, 因此这是最小选择更多了解cookie.Q/a>
Functional","cookiepolicy.modal.level2":"Normal
Functional + analytics","cookiepolicy.modal.level3":"Complete
函数+分析+社交媒体+嵌入视频

一号附加

签名

登录社区

扫描文件病毒

无法下载此文件