Datawarehouse: Maximum CPU Consumption Rate (MCR)

Nella definizione e nel disegno di un’architettura SQL Server, per una corretta valutazione dell’hardware, ci sono molte metriche da tenere in considerazione.

Studiando un’architettura dedicata al datawarehouse (rispetto a sistemi dedicati ad applicazioni OLTP, OLAP, utilizzi “misti”, ecc.) una delle metriche che è utile tenere in considerazione è il valore MCR: Maximum CPU Consumption Rate.

Il valore, da intendersi come un punto di partenza (starting point for system design), rappresenta la richiesta massima (il valore di picco) che dovrà sostenere il sistema in termini di I/O e CPU secondo il carico di lavoro stimato.

In pratica si vuole calcolare il throughput massimo di CPU su un workload di riferimento.

Si tratta di un valore “per-core” e viene valutato tramite query, di complessità simile a quelle che saranno eseguite sul datawarehouse, che facciano la scansione dei dati dalla cache (e non dal disco).

Per ottenere dei dati corretti la tabella che verrà utilizzata per i calcoli deve essere interamente in memoria, in modo tale che non effettui letture fisiche (che fornirebbero risultati errati a causa del maggiore tempo di esecuzione dovuto al recupero da disco).

Una volta costruita una query di analisi, la andremo ad eseguire più volte con l’opzione MAXDOP = 4 (per far eseguire l’istruzione in parallelo sui 4 core disponibili sulla mia macchina).

Ad ogni esecuzione, lasciando sempre attive le direttive SET STATISTICS IO ON e SET STATISTICS TIME ON, andremo a recuperare sia il numero di letture logiche (che, se non si andrà a modificare il numero di righe, resterà sempre uguale) che il tempo di CPU.

Il calcolo del MCR, espresso in MB per secondo, verrà quindi fatto tramite la formula:

MCR = ([Logical reads] / [CPU time in seconds] ) * 8KB / 1024

Costruiamo un database ad-hoc per la nostra analisi, con un’unica tabella modellata a piacere:

 USE MASTER
go

CREATE DATABASE MCRDemo 
ON PRIMARY
( 
    NAME = N'MCRDemo_.mdf' , 
    FILENAME = N'C:\data\MCRDemo.mdf' , 
    SIZE = 5 GB , FILEGROWTH = 1 GB 
)
LOG ON
(
    NAME = N'MCRLOG.ldf' , 
    FILENAME = N'C:\data\MCRDemo.ldf', 
    SIZE = 5 GB , MAXSIZE = 50 GB , FILEGROWTH = 1 GB
);
GO

ALTER DATABASE [MCRDemo] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [MCRDemo] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [MCRDemo] SET AUTO_UPDATE_STATISTICS_ASYNC ON 
ALTER DATABASE [MCRDemo] SET RECOVERY SIMPLE 
GO

E la nostra tabella di test:

 USE MCRDemo;
GO

CREATE TABLE lineitem
  ( orderkey      bigint not null,
    partkey       integer not null,
    suppkey       integer not null,
    linenumber    integer not null,
    quantity      float not null,
    extendedprice float not null,
    discount      float not null,
    tax           float not null,
    returnflag    char(1) not null,
    linestatus    char(1) not null,
    shipdate      datetime not null,
    commitdate    datetime not null,
    receiptdate   datetime not null,
    shipinstruct  char(25) not null,
    shipmode      char(10) not null,
    comment       varchar(132) not null
  )
GO


CREATE CLUSTERED INDEX idxLineitem 
ON lineitem(shipdate ASC)
WITH
(  
    SORT_IN_TEMPDB = ON, 
    DATA_COMPRESSION = PAGE
)
GO

A questo punto popoliamo la tabella con un numero di righe consistente, ad esempio un numero che, quantomeno, si avvicini alla mole di dati che prevediamo in produzione.

Per farlo possiamo utilizzare la procedura descritta qui.

Ad esempio:

 EXEC dbo.up_insertDummyData @schemaName = 'dbo', @tableName = 'lineItem' , @nr = 50000;

Prima di eseguire la query che utilizzeremo per le nostre analisi e le nostre valutazioni, effettuiamo una completa lettura della tabella per potarla in cache (così da assicurarci di non passare per letture fisiche dal disco):

 USE MCRDemo;
GO

SELECT *
  FROM lineitem; 
GO

Di seguito, invece, la query che eseguiremo più volte per poter recuperare i valori necessari alle nostre analisi:

 --> standard SQL query 
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
    returnflag,
    linestatus,
    sum(quantity) as sum_qty,
    sum(extendedprice) as sum_base_price,
    sum(extendedprice*(1-discount)) as sum_disc_price,
    sum(extendedprice*(1-discount)*(1+tax)) as sum_charge,
    avg(quantity) as avg_qty,
    avg(extendedprice) as avg_price,
    avg(discount) as avg_disc,
    count_big(*) as count_order
FROM
    lineitem
WHERE
    shipdate <= dateadd(dd, -90, '20140101')
GROUP BY
    returnflag,
    linestatus
ORDER BY
    returnflag,
    linestatus
OPTION (maxdop 4)

Per comodità inserisco i valori (letture logiche e tempo di CPU – che viene espresso in millisecondi mentre, perl’espressione del calcolo, servirà in secondi) su un foglio excel e ricavo il valore di MCR.

Lancio la mia query, aggiungendo, dopo una serie di esecuzioni, alcune nuove centinaia di migliaia di righe (sulla prima colonna del foglio, Logical Reads, si possono notare i valori che corrispondono, di volta in volta, alla “mole” di dati letta):

Attenzione: un eventuale scostamento del +/-20% indica un potenziale sottodimensionamento hardware rispetto al lavoro che dovrà fare il database server.

Nota: il portatile che ho utilizzato non è progettato per applicazioni datawarehouse. Lo scostamento che si nota tra le tre esecuzioni lo evidenzia naturalmente.

Come posso calcolare il valore totale di MCR per l’intero server a disposizione?

Supponendo un valore MCR = 30 MB/s su una macchina con 2 processori con 6 core avremmo un Total MCR MB/s = 30 * 2 * 6 = 360 MB/s

Da tenere presente che:

• questo valore NON è da ritenersi definitivo rispetto al reale carico di lavoro che avverrà sul database server
• il valore è specifico per singola CPU (per intendersi: vedere il calcolo del Total MCR)
• il valore può essere comparato con i valori rilasciati dagli hardware vendor per una migliore scelta dei componenti (storage su tutti)

In presenza di sistemi NUMA (Non-Uniform Memory Access) può essere interessante ripetere l’esercizio su singolo nodo e poi su tutti i nodi (sfruttando l’affinity).

In questo modo si avrebbe anche la misura dell’impatto della foreign memory e, con più sistemi disponibili, si avrebbe un ulteriore fattore di scelta.

In’ultima analisi, potrebbe avere senso effettuare una misurazione anche di una query “secca”, che faccia soltanto la scansione della tabella (senza alcuna operazione di aggregazione, ordinamento, ecc…).

In questo modo avremmo il throughput massimo raggiungibile per quella tabella e si potrebbe costruire una baseline di riferimento per valutare il peso delle query aggregate (di riferimento).

 USE MCRDemo;
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT *
  FROM lineitem; 
GO

Il consiglio, comunque, è quello di fare una serie di test con una dimensione crescente (linearmente) della tabella.

In questo modo è possibile visualizzare l’andamendo del MCR rispetto al carico per poter avere un modello predittivo delle performance rispetto alla dimensione del dato trattato.