Data Warehouse Hardware - DW硬件需求计算

1. Disk I/O, 硬盘IO速度

　　硬盘的IO速度一直都是数据库的瓶颈，所以有条件的情况下尽可能的使用高IO的磁盘。

　　可以使用微软的工具SQLIO测试磁盘的IOPS

2. CPU的主频，

　　DW和传统的OLTP数据库在使用场景上不一样。

　　传统的OLTP数据库具有[事务小][并发多]的特点；而DW的数据库相比较具有[事务大][并发少]的特点。

　　所以对比起来，传统的OLTP数据库可以使用[低主频][多核]的硬件架构，而DW建议使用[高主频][少核]方案。

　　上述都是相对情况，对于不差钱的土豪，高主频，多核当然是更好的选择。

我们可以计算的是要满足具体的业务需求，需要多少CPU(Core)，多少内存。

MCR，Maximum Consumption Rate，这是一个Core的吞吐量指标

3. 计算MCR

　　可以使用下面的脚本计算出当前计算机的MCR

USE master;-- Create a database for benchmark queries
IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'BenchmarkDB')
DROP DATABASE BenchMarkDB;
GO
CREATE DATABASE BenchMarkDB;
GO
USE BenchMarkDB;-- Include a heap and a table with a clustered index
CREATE TABLE heap_table
(col1 integer identity,col2 integer,col3 varchar(50));CREATE TABLE clust_table
(col1 integer identity PRIMARY KEY CLUSTERED,col2 integer,col3 varchar(50));-- Insert 100 rows to start with
DECLARE @i integer = 0;
WHILE @i < 101
BEGINSET @i = @i + 1INSERT INTO heap_table VALUES (@i, CAST(@i%5 AS varchar))INSERT INTO clust_table VALUES (@i, CAST(@i%5 AS varchar))
END;-- Now keep reinserting exponentially until the tables each contain 2 million rows
WHILE (SELECT COUNT(*) FROM clust_table) < 2000000
BEGININSERT INTO heap_tableSELECT col2, col3 FROM clust_table;INSERT INTO clust_tableSELECT col2, col3 FROM clust_table;
END;

USE BenchmarkDB
GOSELECT SUM(Col2) FROM heap_table WHERE col1 % 3 = 1
GROUP BY col3;SELECT SUM(Col2) FROM clust_table WHERE col1 % 3 = 1
GROUP BY col3;SET STATISTICS IO ON;
SET STATISTICS TIME ON;-- run these muliple times and take an average of the logical reads and CPU time
SELECT SUM(Col2) FROM heap_table WHERE col1 % 3 = 1
GROUP BY col3
OPTION (MAXDOP 1);SELECT SUM(Col2) FROM clust_table WHERE col1 % 3 = 1
GROUP BY col3
OPTION (MAXDOP 1);/* Max Consumption Rate (MCR) is the average of (logical reads / CPU time in seconds) * 8 / 1024(or put another way, the size of the table in MB / CPU time in seconds)This gives us the throughput of a coreTo estimate the no. of cores required, use the following formula:(Amount of data scanned in an average query / MCR) * Concurrent Sessions / Target response timeFor example:(18000 MB/200 MBs) * 10 users / 60s response time = 15 cores (round up to 16)   */

4. Memory内存需求

　　最少1核对应4G内存，或者对每组CPU给64-128G内存

转载于:https://www.cnblogs.com/Niko12230/p/6114283.html

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！