go backarticles

Articles of SQLschool.gr Team

Sizing SQL Server instance

Antonios Chatzipavlis

Overview

"Έχω ένα σύστημα με Χ Cores, Y RAM, Z storage είναι καλό για να βάλω SQL Server;"

Αυτή είναι η συνηθέστερη ερώτηση που πρέπει συχνά να απαντήσω και αφορά το sizing ενός SQL Server instance.

Αν κάποιος κάνει μια αναζήτηση στον ιστο θα βρει άπειρες καταχωρήσεις σχετικά με το θέμα αυτό.

Καταχωρήσεις πολλές αλλά απαντήσεις ξεκάθαρες δεν θα βρει και αυτό γιατί δεν υπάρχει deterministic μέθοδος που να υπολογίζει αυτό.

Ο βασικός λόγος είναι ότι υπάρχουν πάρα πολλές μεταβλητές που εμπλέκονται, που καθιστά αδύνατο να βγει ένα ασφαλές αποτέλεσμα.

Ενδεικτικά θα αναφέρω μερικές μεταβλητές που επηρεάζουν το τελικό αποτέλεσμα και είναι

  • Πόσοι είναι οι χρήστες που θα χρησιμοποιούν τον SQL Server;
  • Πόσες databases θα υπάρχουν στον SQL Server;
  • Τι μέγεθος ή πόσο μεγάλες θα είναι αυτές (μεμονωμένα και συνολικά);
  • Πόσο καλό είναι το database schema;
  • Πόσο καλοί είναι indexes;
  • Πόσο αυτές θα μεγαλώνουν και με ποιο ρυθμό;
  • Πόσα transaction/sec θα υπάρχουν;
  • Τι είδους queries (operational or analytics) θα εκτελούνται.
  • Τι είδους storage θα χρησιμοιηθεί και πως αυτό θα είναι συνδεδεμένο με το SQL Server;
  • Τι Disk IO θα έχω;
  • Τι Νetwork needs υπάρχουν;
  • Τα φορτία που θα έχω σε αυτόν τον SQL Server θα είναι CPU bound;
  • Τι services θα υπάρχουν σε αυτό (Database Engine, DW, OLAP, … )

Η λίστα αυτή είναι μακριά…

Μπορώ να σας δείξω μερικά από τα συστήματα που κατά καιρούς έχω χρησιμοποιήσει, είναι καλά;
System Image A
image

System Image B
image

Η απάντηση είναι it depends όσο παράδοξο και αν αυτό είναι με αυτά που δείχνω καθώς θέλουμε να κάνουμε οικονομία κλίμακος δηλαδή να πληρώσουμε για αυτό που πραγματικά χρειαζόμαστε. Το ιδανικό είναι να μπορείς να δοκιμάσεις πριν αγοράσεις αλλά αυτό δεν το μπορούν να το κάνουν όλοι, άρα κάπως πρέπει να ξεκινήσεις.

ATTENTION
Πριν συνεχίσω παρακάτω στο άρθρο αυτό θα πρέπει να κάνω σαφές ότι όλα τα παρακάτω σε καμία περίπτωση δεν δίνουν το τελικό αποτέλεσμα καθώς οι μεταβλητές όλες δεν περιλαμβάνονται. Είναι μια αρχή συζητήσεων και αναζήτησης, απλά έχεις κάπου να στηριχτείς.

Estimate CPU Requirements

Οι περισσότεροι ξεκινάμε με το να υπολογίζουμε τα CPU cores. Αυτό γίνεται για δύο ευνόητους λόγους ο πρώτος είναι το performance και ο δεύτερος είναι το licensing. Φυσικά το performance είναι ο κυρίαρχος λόγος για να έχουμε ένα σύστημα που να ανταποκρίνεται στις ανάγκες μας.

Maximum Consumption Rate (MCR)

Για αυτό το λόγο και υπολογίζουμε το MCR για να βρούμε το ποσό των δεδομένων που μπορεί να επεξεργαστεί ένα core σε ένα δευτερόλεπτο.

Φυσικά αυτό είναι για το συγκεκριμένο τύπο CPU και σύστημα (motherboard) στο οποίο θα εκτελέσουμε την διαδικασία που περιγράφεται παρακάτω. Παρόλα αυτά όμως οι διαφορές είναι ελάχιστες αν μιλάμε για παρεμφερείς συστήματα.

Αν δεν μπορούμε να κάνουμε τον υπολογισμό του MCR στο σύστημα που έχουμε επιλέξει ότι θα προμηθευτούμε θα πρέπει να κάνουμε μια υπόθεση σχετικά με την τιμή του. Σύμφωνα με τις μετρήσεις που έχουν γίνει αλλά και τους σημερινούς επεξεργαστές η τιμή αυτή είναι 300 ΜΒps.

Calculate MCR

Για τον υπολογισμό του χρειαζόμαστε μια database όπως για παράδειγμα στο script που ακολουθεί

SQL Script

USE master;

-- Create a database for benchmark queries
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
BEGIN
   SET @i = @i + 1
   INSERT 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
BEGIN
 INSERT INTO heap_table
 SELECT col2, col3 FROM clust_table;
 INSERT INTO clust_table
 SELECT col2, col3 FROM clust_table;
END;

Έπειτα χρειαζόμαστε το παρακάτω script στο οποίο θα δείτε ότι αρχικά "ζεσταίνουμε" την buffer cache ώστε να διασφαλίσουμε ότι δεν θα έχουμε disk IO.

Στην συνέχεια εκτελούμε τα queries με MAXDOP=1 ώστε να εξασφαλίσουμε ότι θα χρησιμοποιηθεί ένα core και αφού φυσικά έχουμε ενεργοποιημένα τα statistics io & time.

SQL Script

USE BenchmarkDB
GO

SELECT 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);

Το αποτέλεσμα δεν μας ενδιαφέρει αλλά μας ενδιαφέρει το αποτέλεσμα στo Messages tab στο οποίο θα έχουμε κάτι αντίστοιχο με το παρακάτω image και στο οποίο φαίνονται τα σημεία που μας ενδιαφέρουν.


MCR Results
image

MCR Formula

Το MCR μπορούμε να το βρούμε με την εξής φόρμουλα στρογγυλοποιώντας το αποτέλεσμα σε επόμενη δεκάδα.

MCR = ( ( Logical Reads Average ÷ CPU time average in sec ) X 8 ) ÷ 1024

Αυτό σημαίνει ότι έχουμε

MCR = ( ( ( (A1+A2) ÷ 2 ) ÷ ( ( (B1+B2) ÷ 2 ) ÷ 1000 ) ) X 8 ) ÷ 1024 = 205.23 -> 210

Estimate the number of cores

Αφού έχουμε το MCR για να υπολογίσουμε τον αριθμό των cores αρκεί να χρησιμοποιήσουμε την εξής φόρμουλα

((Average query size in MB ÷ MCR) x Concurrent users) ÷ Target response time

Αυτό σημαίνει ότι θα πρέπει να ξέρουμε το Average query size in MB, το αριθμό των concurrent users που θέλουμε να υποστηρίξουμε κάθε στιγμή και φυσικά το target response time για τα περισσότερα queries που θα εκτελούνται.

Αν έχουμε ήδη ένα σύστημα που λειτουργεί και θέλουμε να κάνουμε αναβάθμιση αυτά τα ξέρουμε ή μπορούμε να τα βρούμε με τα εργαλεία που έχουμε στο SQL Server όπως DMVs, Query Store κλπ.

Αν όχι θα πρέπει να κάνουμε υποθέσεις εργασίας που να ανταποκρίνονται στην πραγματικότητα, όπως για παράδειγμα κανείς δεν θέλει σαν target response time μια τιμή μεγαλύτερη από 60 sec. Η δυσκολία είναι στο Average query size in MB αν δεν υπάρχει προγενέστερη εικόνα. Σε αυτή την περίπτωση είναι δύσκολη η υπόθεση και εξαρτάται από το αν μιλάμε για OLTP, DW ή OLAP καθώς θα πρέπει να γνωρίζουμε πολλές μεταβλητές.

Ο αριθμός των concurrent users είναι κάτι που μπορούμε να το υποθέσουμε σχετικά εύκολα αλλά και να το προβλέψουμε από την φύση της database και το ποια θα είναι η χρήση της στο οργανισμό.

Σε κάθε περίπτωση το τελικό αποτέλεσμα θα πρέπει να είναι ζυγός αριθμός. Αν για παράδειγμα είναι 15 θα πρέπει να το κάνουμε 16 και φυσικά θα πρέπει να το λάβουμε υπόψη στο τι CPUs θα πάρουμε και πόσες.


Αν για παράδειγμα έχουμε

MCR = 300 MBps

Average query size in MB = 20000 ΜΒ

Concurrent users = 20

Target response time = 60 sec

CPU cores = 22

Estimate RAM Requirements

To να υπολογίσουμε την RAM είναι ακόμα ένα δύσκολο σημείο.

Υπάρχει μια φόρμουλα που λέει ότι για κάθε core υπολογίζουμε το ελάχιστο 4 GB RAM (προσωπικά επιλέγω 8 GB / core).

Μια άλλη φόρμουλα λέει ότι πρέπει να έχουμε 64-128 GB / socket.

Τέλος άλλη μία προσέγγιση είναι να τα βάζουμε το 30% του μεγέθους των databases που θα έχουμε στο συγκεκριμένο instance αλλά και εδώ πρέπει να λάβουμε υπόψη το ρυθμό που αυτές μεγαλώνουν.

Σε κάθε περίπτωση πρέπει να λάβουμε υπόψη αν είναι OLTP, DW, OLAP, αν χρησιμοποιούνται in-memory objects αν έχω columnstore indexes κλπ.

Επίσης προσθέτουμε ακόμα 8 GB για το OS.

Με βάση το παραπάνω αποτέλεσμα των cores μιλάμε για 96GB RAM.

Storage Requirements

Δεν έχω να πω πολλά σε αυτό το σημείο καθώς θεωρώ ότι είναι εύκολο να το υπολογίσει κανείς με βάση τα δεδομένα που έχει για τα μεγέθη των databases.

Αυτό που θα επισημάνω είναι ότι χρειαζόμαστε ξεχωριστά storage volumes για τα data, logs, tempdb, backups, filestreams, files και πιθανότατα ιδιαίτερα όταν έχουμε πολλές βάσεις στο instance που είναι απαιτητικές σε Disk IO αυτές να είναι σε ξεχωριστά volumes.

Conclusion

Θα τονίζω για ακόμα μια φορά ότι όλα όσα έχω αναφέρει στο άρθρο αυτό δεν αποτελούν θέσφατα αλλά μια ένδειξη που χωράει αρκετές αλλαγές και συζητήσεις.



Antonios Chatzipavlis


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.