HP ProLiant DL980 G7 SQL Server 2008 R2 NUMA 環境 ベンチマークテスト結果報告書

Presentation on theme: "HP ProLiant DL980 G7 SQL Server 2008 R2 NUMA 環境 ベンチマークテスト結果報告書"— Presentation transcript:

1 HP ProLiant DL980 G7 SQL Server 2008 R2 NUMA 環境 ベンチマークテスト結果報告書
3/17/2017 6:32 PM HP ProLiant DL980 G7 SQL Server 2008 R2 NUMA 環境 ベンチマークテスト結果報告書 マイクロソフト株式会社 技術顧問 熊澤　幸生 © 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

2 今回の検証環境 H/W 構成 S/W 構成 HP ProLiant DL980 G7
Intel Xeon GHz 8 ソケット 64 コア (Nehalem-EX NUMA アーキテクチャ) Memory 128GB SAS 15,000 rpm 146GB * 2 RAID 1 構成 System DB / tempdb を配置 HP StorageWorks P2000 G3 SAS 15,000 rpm 146GB * 24 RAID 構成 15,000 rpm 146GB * 12 RAID 構成 ユーザー DB データファイルを配置 ユーザー DB トランザクションログファイルを配置 S/W 構成 Windows Server 2008 R2 Datacenter x64 SQL Server 2008 R2 Datacenter x64

3 検証環境 データベースサーバー ストレージ インターネット VPNサーバー HP ProLiant DL980 G7
17 March 2017 検証環境 Gigabit Ethernet Fiber Channel データベースサーバー HP ProLiant DL980 G7 Intel XeonプロセッサーX7560 (2.26GHz、24MBキャッシュ)×8 (8P64C) 128GBメモリー、146GB 15krpm SAS HDD×2本 インターネット VPNサーバー Cドライブ 146GB RAID 1 Eドライブ 300GB RAID 1+0 Fドライブ 300GB RAID 1+0 ストレージ HP StorageWorks P2000 G3 デュアルコントローラー 146GB 15krpm 2.5型 6G SAS HDD×24 (RAID1+0) ©2010 Hewlett-Packard Development Company, L.P.

4 DL980 G7 4CPU モジュール

5 ProLiant DL980 G7

6 Windows Server 2008 R2

7 SQL Server 2008 R2

8 NUMA 環境の認識 Max Worker Threads パラメータの最適化 既定値 : 64 ビット 64 論理 CPU 1,492
今回 : 2,048 に設定

9 SQL Server と NUMA Node configuration: node 7: CPU mask: 0xffff :1 Active CPU mask: 0xffff :1. This message provides a description of the NUMA configuration for this computer. Node configuration: node 6: CPU mask: 0x0000ffff :1 Active CPU mask: 0x0000ffff :1. This message provides a description of the NUMA configuration for this computer. Node configuration: node 5: CPU mask: 0x ffff0000:1 Active CPU mask: 0x ffff0000:1. This message provides a description of the NUMA configuration for this computer. Node configuration: node 4: CPU mask: 0x ffff:1 Active CPU mask: 0x ffff:1. This message provides a description of the NUMA configuration for this computer. Node configuration: node 3: CPU mask: 0xffff :0 Active CPU mask: 0xffff :0. This message provides a description of the NUMA configuration for this computer. Node configuration: node 2: CPU mask: 0x0000ffff :0 Active CPU mask: 0x0000ffff :0. This message provides a description of the NUMA configuration for this computer. Node configuration: node 1: CPU mask: 0x ffff:0 Active CPU mask: 0x ffff:0. This message provides a description of the NUMA configuration for this computer. Node configuration: node 0: CPU mask: 0x ffff0000:0 Active CPU mask: 0x ffff0000:0. This message provides a description of the NUMA configuration for this computer. Lock partitioning is enabled. This is an informational message only. Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. Using locked pages for buffer pool. Large Page Allocated: 32MB Large Page Granularity: Large Page Extensions enabled. Detected 128 CPUs. SQL Server is starting at normal priority base (=7). Registry startup parameters: <nl/> -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf<nl/> -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG<nl/> -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf This instance of SQL Server last reported using a process ID of 5964 at 2010/08/23 11:21:05 (local) 2010/08/23 2:21:05 (UTC). Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Authentication mode is WINDOWS-ONLY. System Manufacturer: 'Hewlett-Packard'<c/> System Model: 'ProLiant DL980 G7'. Server process ID is 5856. All rights reserved. (c) Microsoft Corporation. Microsoft SQL Server 2008 R2 (RTM) (X64) <nl/> Apr :48:46 <nl/> Copyright (c) Microsoft Corporation<nl/> Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

10 MAX DOP (並列処理の最大限度) ○

11 tempdb データファイルの分割

12 リソースモニター 2,100 ユーザ接続 ○

13 NUMA 構成 (1,800 ユーザ接続)

14 NUMA SMT構成(2,100 ユーザ接続)

15 SQL Server 2008 R2 : Tran/5Sec

16 SQL Server 2008 R2 : 累積値

17 スレッド数増加による Scaleout Intel White Box Xeon GHz 4P32C Windows Server 2008 R2 x64 Enterprise / SQL Server 2008 R2 Enterprise 300 秒間の トランザクション処理件数 トランザクション数 / 秒 Ｚｚ 700 Thread NUMA SMT_off 129,913 433.04 N/A 700 Thread NUMA SMT_on 131,715 439.05 900 Thread NUMA SMT_off 152,741 509.14 900 Thread NUMA SMT_on 157,438 524.79 1,050 Thread NUMA SMT_off 155,936 519.79 1,050 Thread NUMA SMT_on 162,223 540.74 HP Proliant DL980G7 Xeon GHz 8P64C Windows Server 2008 R2 x64 Data Center / SQL Server 2008 R2 133,947 446.49 1.03 171,046 570.15 1.12 171,580 571.93 1.09 187,693 625.64 1.20 199,204 664.01 1.23 1,800 Thread NUMA SMT_off 248,306 827.69 1.63 1,800 Thread NUMA SMT_on 268,802 896.01 1.71 2,100 Thread NUMA SMT_off 240,063 800.21 1.54 2,100 Thread NUMA SMT_on 270,757 902.52 1.67

18 © 2010 Microsoft Corporation. All rights reserved
© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.