SQL Optimizacija – vpliv diskovnega polja na hitrost

Prikazani so rezultatov testov na temo hitrosti delovanja SQL na različnih platformah. Predpostavka je da SQL dela s 64k blok size. Testirali pa smo s programom iometer-1.1.0-win64.x86_64, ki zna delati s 64k bloki. Testirali smo različne scenarije in kako se odziva hitrost branja in pisanja na te diske. Načeloma pa smo strmeli k naključnemu 100% branju ali naključnemu 100% pisanju na 8 GB veliki datoteki. Vsi testi so mišljeni za SMB spekter.

Test se je osredotočil na diskovno polje, predmet testiranja niso klasični SQL pohitritveni predlogi:
– da so log datoteke ločene od baznih,
– da so sistemske baze na SSD diskih,
– da je potrebno delati optimization in maintenance plane,
– da ima SQL dovolj RAM-a,
– da ima SQL dovolj procesorske moči,
– ipd.

Test 1:
V tem primeru je testiran HPE strežnik in hitrost branja/pisanja glede na disk (SAS/SSD) in pisanje na posamezno particijo (NTFS, BeFRS) ter variacije kako je particija formatirana. Testi varirajo, ker je bil test opravljen na produkcijskem strežniku, kjer je možna neenakomerna obremenitev diskov v času testiranja, zato je bilo opravljenih več testov in zapisan je najhitrejši test.
Zaključek je, da SSD disk pozitivno vplivajo na branje podatkov pri pisanju pa ni zaznati posebnih sprememb. Tudi način formatiranja particije ni pokazal bistvene razlike.

HPE DL 380 Gen9
VMWare ESXi 6.5.0 5310538
(HPE RAID5 – SSD  Mixed Use 800 GB (P440 RAID default settings (firmware 5.04) – NO Over Provisioning ))
sequential 100%    
(IOPs) (Speed/Hitrost)
Read 3.845 240.32 MB
Write: 593 37.09 MB
SSD 64k (NTFS Allocation unite size default):
Random 100%    
(IOPs) (Speed/Hitrost)
Read 2.285 142.86
Write: 792 49.51
SSD 64k (NTFS Allocation unite size 64K):
Random 100%    
(IOPs) (Speed/Hitrost)
Read 2603 162.69
Write: 825 51.60
SSD 64k (BeFRS Allocation unite size 64K):
Random 100%    
(IOPs) (Speed/Hitrost)
Read 2440 152.51
Write: 825 51.60
SSD 64k (BeFRS Allocation unite size default):
Random 100%    
(IOPs) (Speed/Hitrost)
Read 2538 158.67
Write: 815 50.98
VMWare ESXi 6.5.0 5310538
(HPE RAID10 – SAS disk 10K 1,2 TB(P440ar RAID (firmware 5.04) default settings))
SAS 64k (NTFS Allocation unite size default):
Random 100%    
(IOPs) (Speed/Hitrost)
Read 236 14.79
Write: 1027 64.19
SAS 64k (NTFS Allocation unite size 64K):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 218 13.68
Write: 975 60.97
SAS 64k (BeFRS Allocation unite size 64K):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 248 15.52
Write: 775 48.47
SAS 64k (BeFRS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 249 15.59
Write: 1023 63.97


Test 2:
HPE DL 380p Gen9
Hitrost smo testirali tudi na prostem HPE strežniku in ga postavili v različne načine delovanja.
Rezultati so, da HyperV vloga ne vpliva na hitrost delovanja branja na nivoju SQL-a.
Urejanje cache-a na RAID kontrolerju delno vpliva na rezultate ne pa bistveno.

Storage spaces ni bil postavljen v polni obliki zato so rezultati načeloma nepravilni  produkcijskim.
Storage Spaces direct:

HPE DL 380p Gen9
Storage Spaces direct
Host2
Windows Server 2016 host (300 SAS disk 10K + 1 SSD)
Random 100%    
(IOPs) (Speed/Hitrost)
Read 201  12.61
Write: 185 nov.59
Host1
Windows Server 2016 host (300 SAS disk 10K + 1 SSD)
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 200  12.61
Write: 183  11.48
Windows Server 2016 (HPE RAID10 – 4x SAS disk 10K 300 TB (P440ar (firmware 5.04) RAID default settings)) 10% read/90% write
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 273  17.09
Write: 1346  84.17
Windows Server 2016 (HPE RAID10 – 4x SAS disk 10K 300 TB (P440ar (firmware 5.04) RAID default settings)) 10% read/90% write
Installed HyperV role
Host
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 221 13.81
Write: 1341 83.836
VM (VM MS 2016 Std)
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 221 13.816
Write: 1359 84.97
Windows Server 2016 (HPE RAID10 – 4x SAS disk 10K 300 GB (P440ar (firmware 5.04) RAID default settings)) 90% read/10% write
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 219 13,698
Write: 943 58.97
Windows Server 2016 (HPE RAID10 – 4x SAS disk 10K 300 GB (P440ar (firmware 5.04) RAID default settings)) 50% read/50% write
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 255 15.97
Write: 1157 72.35
Windows Server 2016 (HPE RAID5 – 4x SAS disk 10K 300 GB (P440ar (firmware 5.04) RAID default settings)) 10% read/90% write
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 270 16.88
Write: 723 45.22
Windows Server 2016 (HPE RAID5 – 4x SAS disk 10K 300 GB (P440ar (firmware 5.04) RAID default settings)) 50% read/50% write
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 256  16.03
Write: 600  37.51
Windows Server 2016 (HPE RAID6 – 4x SAS disk 10K 300 GB (P440ar (firmware 5.04) RAID default settings)) 10% read/90% write
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 265 16.62
Write: 695 43.45
Windows Server 2016 (HPE RAID6 – 4x SAS disk 10K 300 GB (P440ar (firmware 5.04) RAID default settings)) 50% read/50% write
(NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 249 15.59
Write: 496  31.03


Test 3:
Hitrost teoretičnega branja in pisanja smo preverili na nekaj produkcijskih strežnikih.
Pozna hitrost delovanja glede na to kakšen je strežnik in njegova starost.

HPE ML350 Gen9
Windows Server 2012 VM on HyperV 2016 (HPE RAID1 – SAS disk 10K 1,2 TB(P440ar RAID default settings))
SAS 64k (NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 157  9.83
Write: 449  28.07
HPE ML350 Gen9
Windows Server 2012 (HPE RAID5 – 3x SAS disk 10K 1,2 TB (P440ar (firmware 3) RAID default settings))
SAS 64k (NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 198  12.37
Write: 498 31.16
HPE DL 380p Gen8
Windows Server 2016 host
Windows Server 2016 (HPE RAID5 – 8 x SAS disk 10K 300 GB(P420i RAID (firmware 8) default settings))
SAS 64k (NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 156  9.7
Write: 798 49.88
Windows Server 2012 VM on HyperV 2016
SAS 64k (NTFS Allocation unite size default):
Random 100%    
  (IOPs) (Speed/Hitrost)
Read 161  10.08
Write: 864 54.0

GregorO

Back to Top